Saraksti un diapazoni (5)
Makro (VBA procedūras) (63)
Dažādi (39)
Excel kļūdas un kļūmes (3)

Kā parādīt 0, nevis kļūdu šūnā ar formulu

Ir situācijas, kad darbgrāmatā uz lapām ir izveidotas daudzas formulas, kas veic dažādus uzdevumus. Turklāt formulas tika izveidotas jau sen, iespējams, pat jūs. Un formulas atgriež kļūdas. Piemēram, #DIV/0! (#DIV/0!) . Šī kļūda rodas, ja dalījums ar nulli notiek formulā: = A1 / B1, kur B1 ir nulle vai tukšs. Taču var būt arī citas kļūdas (#N/A, #VALUE! utt.). Jūs varat mainīt formulu, pievienojot kļūdu pārbaudi:

= JA(ISERR(A1/B1),0, A1/B1)
argumenti:
=IF(EOSH(1 arguments), 2 arguments, 1 arguments)
Šīs formulas darbosies jebkurā Excel versijā. Tiesa, EOS funkcija neapstrādās kļūdu #N/A (#N/A). Lai tādā pašā veidā apstrādātu #N/A, ir jāizmanto funkcija ERROR:
=JA(KĻŪDA(A1/B1),0, A1/B1)
=JA(KĻŪDA(A1/B1),0, A1/B1)
Taču tālāk tekstā izmantošu EOSH (jo tas ir īsāks) un turklāt ne vienmēr ir nepieciešams “neredzēt” kļūdu #N/A.
Bet priekš Excel versijas 2007. gada un jaunākas versijas, varat izmantot nedaudz optimizētāku funkciju IFERROR:
= KĻŪDA(A1 / B1 ;0)
= KĻŪDA(A1/B1 ,0)
argumenti:
=IFERROR(1 arguments; 2 arguments)

1 arguments: izteiksme, kas jāaprēķina
2. arguments: vērtība vai izteiksme, kas jāatgriež šūnā, ja pirmajā argumentā ir kļūda.

Kāpēc IFERROR ir labāks un kāpēc es to saucu par optimizētāku? Apskatīsim pirmo formulu sīkāk:
=IF(EOSH(A1/B1),0, A1/B1)
Ja mēs aprēķināsim soli pa solim, mēs redzēsim, ka vispirms tiek aprēķināta izteiksme A1 / B1 (t.i., dalīšana). Un, ja tā rezultāts ir kļūda, tad EOSH atgriezīs TRUE, kas tiks nodota IF. Un tad funkcija IF atgriezīs vērtību no otrā argumenta 0.
Bet, ja rezultāts nav kļūdains un ISERR atgriež FALSE, funkcija pārrēķinās iepriekš aprēķināto izteiksmi: A1 / B1
Ar doto formulu tas nespēlē īpašu lomu. Bet, ja formula, piemēram, VLOOKUP, tiek izmantota, meklējot vairākus tūkstošus rindu, tad, to aprēķinot divreiz, var ievērojami palielināt laiku, kas nepieciešams formulu pārrēķināšanai.
Funkcija IFERROR vienreiz novērtē izteiksmi, atceras tās rezultātu un, ja tas ir nepareizs, atgriež to, kas uzrakstīts kā otrais arguments. Ja kļūdas nav, tas atgriež saglabāto izteiksmes aprēķina rezultātu no pirmā argumenta. Tie. faktiskais aprēķins tiek veikts vienu reizi, kas praktiski neietekmēs formulu kopējās pārrēķina ātrumu.
Tāpēc, ja jums ir Excel 2007 un jaunāka versija un fails netiks izmantots iepriekšējās versijās, ir lietderīgi izmantot IFERROR.

Kāpēc formulas ar kļūdām vispār būtu jālabo? Tas parasti tiek darīts, lai pārskatos datus attēlotu estētiskāk, it īpaši, ja atskaites pēc tam tiek nosūtītas vadībai.

Tātad uz lapas ir formulas, kuru kļūdas ir jāapstrādā. Ja korekcijai ir viena vai divas līdzīgas formulas (vai pat 10-15), tad ar to manuālu nomaiņu gandrīz nav problēmu. Bet, ja šādu formulu ir vairāki desmiti vai pat simti, problēma iegūst gandrīz universālas proporcijas :-). Tomēr procesu var vienkāršot, rakstot relatīvi vienkāršs kods Visual Basic Pieteikumam.
Visām Excel versijām:

Sub IfIsErrNull() Const sToReturnVal As String = "0" , vbInformation, "www.site" Iziet no apakšgala If Katram rc In rr If rc.HasFormula Tad s = rc.Formula s = Mid(s, 2) ss = " =" & "IF(ISERR(" & s & ")," & sToReturnVal & "," & s & ")" Ja pa kreisi(-i, 9)<>"IF(ISERR(" Tad If rc.HasArray Tad rc.FormulaArray = ss Else rc.Formula = ss Beigas If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc Ja Err .Number Tad MsgBox "Formula apstrādāta"

Sub IfIsErrNull() Const sToReturnVal As String = "0" "ja ir jāatgriež tukšs, nevis nulles "Const sToReturnVal As String = """""" Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox "Atlasītajā diapazonā nav datu", vbInformation, "www..HasFormula Then s = rc.Formula s = Mid(s, 2) ss = " =" & "IF(ISERR(" & s & ")," & sToReturnVal & "," & s & ")" Ja atstāj(s, 9)<>"IF(ISERR(" Tad If rc.HasArray Tad rc.FormulaArray = ss Else rc.Formula = ss Beigas If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc Ja Err .Number then MsgBox "Nevar konvertēt formulu šūnā: " & ss & vbNewLine & _ Err.Description, vbInformation, "www..site" End If End Sub

Versijām 2007 un jaunākām versijām

Sub IfErrorNull() Const sToReturnVal As String = "0" "ja nepieciešams, atgrieziet tukšu, nevis nulli "Const sToReturnVal As String = """""" Dim rr kā diapazons, rc kā diapazons Dim s kā virkne , ss kā virkne kļūdas gadījumā Atsākt nākamo komplektu rr = Intersect(Atlase, ActiveSheet.UsedRange) Ja rr nav nekas, tad MsgBox "Atlasītajā diapazonā nav datu", vbInformation, "www.site" Iziet no apakšgala If Katram rc In rr If rc.HasFormula Tad s = rc.Formula s = Mid(s, 2) ss = "=" & "IFERROR(" & s & ", " & sToReturnVal & ")" Ja pa kreisi(-i, 8)<>"IFERROR(" Then If rc.HasArray Tad rc.FormulaArray = ss Else rc.Formula = ss Beigas If If Err.Number Then ss = rc.Address rc.Atlasiet Exit For End If End If End If Next rc Ja Err.Number Pēc tam MsgBox "Nevar pārveidot formulu šūnā:"& ss & vbNewLine & _ Err.Description, vbInformation, "www.site" Else MsgBox "Formula apstrādāta", vbInformation, "www.site" End If End Sub

Sub IfErrorNull() Const sToReturnVal As String = "0" "ja ir jāatgriež tukšs, nevis nulles "Const sToReturnVal As String = """""" Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox "Atlasītajā diapazonā nav datu", vbInformation, "www..HasFormula Then s = rc.Formula s = Mid(s, 2) ss = " =" & "IFERROR(" & s & "," & sToReturnVal & ")" Ja atstāts(s, 8)<>"IFERROR(" Then If rc.HasArray Tad rc.FormulaArray = ss Else rc.Formula = ss Beigas If If Err.Number Then ss = rc.Address rc.Atlasiet Exit For End If End If End If Next rc Ja Err.Number Tad MsgBox "Šūnā esošo formulu nevar konvertēt: " & ss & vbNewLine & _ Err.Description, vbInformation, "www..site" End If End Sub

Kā tas strādā
Ja neesat pazīstams ar makro, tad vispirms labāk izlasīt, kā tos izveidot un izsaukt: Kas ir makro un kur to meklēt? , jo Var gadīties, ka darāt visu pareizi, bet aizmirstat iespējot makro un nekas nedarbosies.

Nokopējiet iepriekš minēto kodu un dodieties uz VBA redaktoru ( Alt+F11), izveidojiet standarta moduli ( Ievietot -Modulis) un vienkārši ielīmējiet tajā šo kodu. Dodieties uz vajadzīgo Excel darbgrāmatu un atlasiet visas šūnas, kuru formulas ir jāpārveido, lai kļūdas gadījumā tās atgrieztu nulli. Nospiediet Alt+F8, atlasiet kodu IfIsErrNull(vai IfErrorNull, atkarībā no tā, kuru nokopējāt) un nospiediet Izpildīt.
Visām formulām atlasītajās šūnās tiks pievienota kļūdu apstrādes funkcija. Dotie kodi ņem vērā arī:
-ja formula jau ir izmantojusi funkciju IFERROR vai IF(EOSH), tad šāda formula netiek apstrādāta;
-kods pareizi apstrādās arī masīva funkcijas;
-Jūs varat atlasīt šūnas, kas nav blakus esošas (izmantojot Ctrl).
Kāds ir trūkums: Sarežģītas un garas masīva formulas var izraisīt koda kļūdu šo formulu rakstura un to apstrādes no VBA dēļ. Šajā gadījumā kods rakstīs par neiespējamību turpināt darbu un izcels problemātisko šūnu. Tāpēc es ļoti iesaku aizstāt failu kopijas.
Ja kļūdas vērtība ir jāaizstāj ar tukšu, nevis nulli, jums ir nepieciešama virkne

"Const sToReturnVal As String = """"""

Noņemt apostrofu ( " )

Jūs varat arī šo kodu izsauciet to, nospiežot pogu (Kā izveidot pogu, lai darblapā izsauktu makro) vai ievietotu pievienojumprogrammā (Kā izveidot savu pievienojumprogrammu?), lai to varētu izsaukt no jebkura faila.

Un neliels papildinājums: mēģiniet izmantot kodu pārdomāti. Kļūdas atgriešana ne vienmēr ir problēma. Piemēram, izmantojot VLOOKUP, dažreiz ir noderīgi redzēt, kuras vērtības netika atrastas.
Es arī vēlos atzīmēt, ka tas ir jāpiemēro faktiski strādājošām formulām. Jo, ja formula atgriež #NAME!(#NAME!), tad tas nozīmē, ka kāds arguments formulā ir ierakstīts nepareizi, un tā ir kļūda formulas rakstīšanā, nevis kļūda aprēķina rezultātā. Labāk ir analizēt šādas formulas un atrast kļūdu, lai izvairītos no loģiskām kļūdām aprēķinos darblapā.

Vai raksts palīdzēja? Kopīgojiet saiti ar draugiem! Video nodarbības

("Apakšējā josla":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 "texteffectdelay1":1000,"texteffect2":"slaids","texteffectslidedirection2":"pa labi","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":15 textcss":"displejs:bloks; polsterējums:12px; text-align:left;","textbgcss":"displejs:bloķēt; pozīcija:absolūts; augšā: 0px; pa kreisi: 0px; platums:100%; augstums:100% ; fona krāsa:#333333; necaurredzamība:0,6; filtrs:alpha(necaurredzamība=60);","titlecss":"displejs:bloks; pozīcija: radinieks; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"displejs:bloks; pozīcija: radinieks; fonts: 12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; krāsa:#fff; margin-top:8px;","buttoncss":"displejs:bloks; pozīcija: radinieks; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

Laba diena, draugi!

Šajā rakstā mēs runāsim par to, kāda veida kļūdas formulāsExcel problēmas, ar kurām saskaramies, strādājot ar Excel izklājlapām. Esmu vairāk nekā pārliecināts, ka visi ir redzējuši kļūdas, bet mazāk zina, kā no tām pareizi atbrīvoties. Tomēr šīs zināšanas ir svarīgas, jo tās jūs apdrošinās pret tipiskas kļūdas vai palīdzēs jums ātri un bez panikas atbrīvoties vai labot saņemto kļūdas formulāsExcel.

Mēs varam daudz runāt par kļūdām programmā Excel, bet apskatīsim visbiežāk sastopamās, es jums pastāstīšu, kāpēc un kā tās notiek, kā arī to, kā tās novērst. kļūdas formulāsExcel, lai pareizi parādītu datus.

Nu, lūk, kā tas patiesībā ir kļūdas formulāsExcel:

1. Kļūda #####.Šis ir viens no visizplatītākajiem un vienkāršākajiem kļūdas iekšā Excel formulas . Tas nozīmē tikai vienu: kolonnas platums nav pietiekami plats, lai pilnībā parādītu jūsu datus. Šīs problēmas risinājums ir ļoti vienkāršs, pārvietojiet peles kursoru uz kolonnas apmali un, turot nospiestu kreiso pogu, palieliniet šūnu, līdz sāk parādīties dati, vai arī dubultklikšķis uz kolonnas apmales ļaus jums noklikšķināt kolonnas platākajā šūnā.
2. Kļūda #NAME?. Šis kļūda (#NAME?) rodas Excel formulās tikai tad, ja redaktors nevar atpazīt formulā esošo tekstu (piemēram, kļūda funkcijas nosaukumā drukas kļūdas dēļ =SUM(A1:A4). Lai to labotu kļūdas formulāsExcel, jums tas rūpīgi jāizlasa un jāizlabo kļūda (A1:A4).
3. Kļūda #VALUE!. Šis kļūda (#VALUE!)Šī problēma var rasties, ja formula satur argumentu, kura veids nav piemērots jūsu aprēķiniem. Piemēram, jūsu matemātiskajā formulā vai formulā ir ievietota teksta vērtība =A1+B1+C1, kur C1 ir teksts. Problēmas risinājums ir vienkāršs, izmantojiet formulu, kas ignorē šūnas, kurās ir teksts, vai vienkārši noņem dotā vērtība no šūnas C1.
4. Kļūda #BUSINESS/0. Kā redzams no kļūdas, kas parādījās formulā, jūs vienkārši reizinājāt savu argumentu ar skaitli 0, un to nevar izdarīt, pamatojoties uz matemātikas likumiem. Lai labotu šo kļūdu, jāmaina skaitlis, lai tas nebūtu vienāds ar 0, vai jāmaina formula, piemēram, loģiskā, kas ļaus izvairīties no kļūdas. =IF(A2=0;””;A1/A2)
5. Kļūda #LINK!. Šī ir viena no visizplatītākajām un mulsinošākajām kļūdām Excel funkcijas. Kad redzat šo kļūdu, tas nozīmē, ka formula atsaucas uz šūnu, kas vairs nepastāv. Tas ir īpaši problemātiski, ja strādājat ar lielu datu apjomu un liela summa. Kad rediģējat savas tabulas šādi kļūdas formulāsExcel Viņiem nevajadzētu jūs nobiedēt, tos ir ļoti viegli salabot, jums vienkārši jāatgriežas savās vietās vai, ja nepieciešams, manuāli jāpārraksta formula, izslēdzot no tās kļūdaino argumentu.

Es ceru, ka šis raksts ir par to, kas viņi ir kļūdas formulāsExcel un to labošana kļuva jums noderīga, un jūs uzzinājāt kaut ko jaunu un interesantu sev.

Tiekamies jaunos rakstos!

"Kāpēc pasaule ir tik sakārtota, ka cilvēkiem, kuri zina, kā dzīvot prieka pēc, nekad nav naudas, un tiem, kam ir nauda, ​​nav ne jausmas, ko nozīmē "izšķērdēt savu dzīvi"?
D.B. Rādīt

Ja Excel nevar pareizi novērtēt darblapas formulu vai funkciju; tas parādīs kļūdas vērtību, piemēram, #NAME?, #NUMBER!, #VALUE!, #N/A, #EMPTY!, #LINK! - šūnā, kurā atrodas formula. Apskatīsim veidus kļūdas programmā Excel, viņu iespējamie iemesli un kā tās novērst.

Kļūda #NAME?

Kļūda #NAME parādās, ja formulā izmantotais nosaukums ir noņemts vai nav iepriekš definēts.

Cēloņi kļūdas #NAME?:

1. Ja formula izmanto nosaukumu, kas ir noņemts vai nav definēts.

Excel kļūdas — vārda izmantošana formulā

Problēmu novēršana: definējiet vārdu. Kā to izdarīt, ir aprakstīts šajā.

1. Kļūda, rakstot funkcijas nosaukumu:

Kļūdas programmā Excel — kļūda, rakstot funkciju MATCH

Problēmu novēršana: pārbaudiet funkcijas pareizrakstību.

1. Atsaucē uz šūnu diapazonu trūkst kola (:).

Kļūdas programmā Excel — kļūda, rakstot šūnu diapazonu

Problēmu novēršana: izlabojiet formulu. Iepriekš minētajā piemērā tas ir =SUM(A1:A3).

1. Formulā tiek izmantots teksts, kas nav iekļauts dubultpēdiņas. Excel parāda kļūdu, jo tas uzskata šādu tekstu kā nosaukumu.

Excel kļūdas — kļūda, apvienojot tekstu ar skaitli

Problēmu novēršana: ievietojiet formulas tekstu dubultpēdiņās.

Excel kļūdas — pareiza teksta sapludināšana

Kļūda #NUMBER!

Kļūda #NUMBER! programmā Excel tiek parādīts, ja formulā ir nepareizs skaitlis. Piemēram:

1. Izmantojiet negatīvu skaitli, ja ir nepieciešama pozitīva vērtība.

Kļūdas programmā Excel — kļūda formulā, negatīva argumenta vērtība SQRT funkcijā

Problēmu novēršana: pārbaudiet, vai funkcijā ievadītie argumenti ir pareizi.

1. Formula atgriež skaitli, kas ir pārāk liels vai pārāk mazs, lai to attēlotu programmā Excel.

Excel kļūdas — formulas kļūda pārāk lielas vērtības dēļ

Problēmu novēršana: pielāgojiet formulu tā, lai rezultāts būtu skaitlis programmā Excel pieejamajā diapazonā.

Kļūda #VALUE!

Šis Excel kļūda rodas, ja formulā tiek ievadīts arguments ar nederīgu vērtību.

Kļūdas #VALUE! iemesli:

1. Formulā ir atstarpes, simboli vai teksts, taču tajā ir jābūt skaitlim. Piemēram:

Kļūdas programmā Excel - skaitļu summēšana un teksta vērtības

Problēmu novēršana: pārbaudiet, vai argumentu veidi formulā ir iestatīti pareizi.

1. Diapazons tiek ievadīts kā funkcijas arguments, un funkcija sagaida, ka tiks ievadīta viena vērtība.

Kļūdas programmā Excel — funkcija VLOOKUP izmanto diapazonu kā argumentu, nevis vienu vērtību

Problēmu novēršana: norādiet funkcijai derīgus argumentus.

1. Izmantojot masīva formulu, nospiediet taustiņu Enter, un programma Excel parāda kļūdu, jo tā to uzskata par parastu formulu.

Problēmu novēršana: lai pabeigtu formulas ievadīšanu, izmantojiet taustiņu kombināciju Ctrl+Shift+Enter.

Excel kļūdas — masīva formulas izmantošana

Kļūda #LINK

Kļūdas programmā Excel — kļūda formulā dzēstās A kolonnas dēļ

Problēmu novēršana: mainiet formulu.

Kļūda #DIV/0!

Šis kļūdaExcel notiek, dalot ar nulli, tas ir, ja šūnas atsauce, kas satur nulles vērtību, vai atsauce uz tukšu šūnu tiek izmantota kā dalītājs.

Kļūdas programmā Excel — kļūda #DIV/0!

Problēmu novēršana: izlabojiet formulu.

Kļūda # N/A

#N/A kļūda programmā Excel nozīmē, ka formula izmanto nepieejamu vērtību.

Kļūdas #N/A iemesli:

1. Izmantojot funkcijas VLOOKUP, GLOOKUP, VIEW, MATCH, tiek izmantots nepareizs arguments search_value:

Kļūdas programmā Excel — meklētā vērtība nav skatītajā masīvā

Problēmu novēršana: iestatiet pareizo argumentu uz vērtību, kuru meklējat.

1. Kļūdas, izmantojot funkcijas VLOOKUP vai GLOOKUP.

Problēmu novēršana: skatiet īpašu sadaļu

1. Kļūdas darbā ar masīviem: tiek izmantoti neatbilstoši diapazona izmēri. Piemēram, masīva argumentiem ir mazāks izmērs nekā iegūtais masīvs:

Excel kļūdas — masīva formulas kļūdas

Problēmu novēršana: pielāgojiet formulu atsauču diapazonu, lai tās atbilstu rindām un kolonnām, vai ievadiet masīva formulu trūkstošajās šūnās.

1. Funkcijā trūkst viena vai vairāku obligāto argumentu.

Kļūdas programmā Excel — kļūdas formulā, trūkst vajadzīgā argumenta

Problēmu novēršana: ievadiet visus nepieciešamos funkcijas argumentus.

Kļūda # EMPTY!

Kļūda # EMPTY! programmā Excel rodas, ja formulā tiek izmantoti diapazoni, kas nepārklājas.

Kļūdas programmā Excel — SUM formulā tiek izmantoti diapazoni, kas nepārklājas

Problēmu novēršana: pārbaudiet formulas pareizrakstību.

Kļūda ####

Kļūdas iemesli

1. Kolonnas platums nav pietiekams, lai parādītu šūnas saturu.

Excel kļūdas — kolonnas platuma palielināšana, lai šūnā parādītu vērtību

Problēmu novēršana: palielināt kolonnas/kolonnu platumu.

1. Šūnā ir formula, kas atgriež negatīvu vērtību, aprēķinot datumu vai laiku. Programmā Excel datumam un laikam jābūt pozitīvām vērtībām.

Excel kļūdas — datuma un stundu atšķirības nedrīkst būt negatīvas

Problēmu novēršana: pārbaudiet formulas pareizrakstību, dienu vai stundu skaits bija pozitīvs skaitlis.

» parādīs īpašu kļūdas ziņojumu. Turklāt katrs kļūdas veids tiek norādīts ar savu ziņojumu, to izraisa dažādi iemesli un attiecīgi ir nepieciešams dažādos veidos atļaujas.

##### — Ko tas nozīmē un kā to labot?

Šie simboli norāda, ka kolonna, kurā ir skaitļi, nav tiem pietiekami plata vai ka šīs kolonnas šūnās ievadītais datums un laiks satur negatīvus skaitļus.
Pirmajā gadījumā ir pietiekami vienkārši palielināt kolonnas platumu vai mainīt to skaitļa formāts datus (piemēram, samaziniet decimāldaļu skaitu).
Otrajā gadījumā jums ir nepieciešams:

• pārbaudiet formulu, ja tiek aprēķināts dienu skaits starp diviem datumiem;
• ja formula nesatur kļūdas, jums jāmaina šūnas formāts un, piemēram, jāpārslēdzas no formāta “Datums un laiks” uz formātu “Vispārīgi” vai “Numurs”.

#VĒRTĪBA! — Ko tas nozīmē un kā to labot?

Šie ziņojumi ir par teksta izmantošanu skaitļa vai Būla vērtības (TRUE vai FALSE) vietā. Tas nozīmē, ka Excel ir tāds prāts un nevar pārveidot šūnā doto tekstu pareizajā datu tipā.
Jums ir jānodrošina, lai formula vai funkcija atsaucas uz šūnām, kurās ir derīgas vērtības.
Piemēram, ja šūnā A2 ir skaitlis un šūnā A3 ir teksts, šūnā A1 ar formulu =A2+A3 tiks parādīta #VALUE! .

#DIV/0! — Ko tas nozīmē un kā to labot?

Šie ziņojumi norāda, ka šūna dala skaitli ar 0 (nulle) vai tiek izmantotas atsauces uz tukšu šūnu.

• Atvērtajā darblapas logā atlasiet šūnu ar šo kļūdu un nospiediet taustiņu F2.
• Kad šūnā tiek parādīta pati formula vai funkcija un ir atlasītas visas šūnas, kas saistītas ar saitēm uz šo formulu vai šūnu, rūpīgi pārbaudiet atlasīto šūnu vērtības un, ja nepieciešams, pielāgojiet formulu vai mainiet saites uz tukšām šūnām.
• Formulu joslā nospiediet taustiņu Enter vai Enter.

Ja tukša šūna tiek izmantota kā operands, tā automātiski tiek uzskatīta par vienādu ar nulli.

#NAME? — Ko tas nozīmē un kā to labot?

Šie simboli norāda, ka formula izmanto neesošu nosaukumu vai nepareizu operatoru.

1 variants

Ja tiek izmantots nosaukums, kas nav definēts, ir jāveic šādas darbības:

• Atvērtajā darblapas logā dodieties uz cilni Formulas un grupā Definētie nosaukumi noklikšķiniet uz pogas Name Manager.
• Logā Name Manager pārbaudiet, vai vārds sarakstā.

Ja šī nosaukuma trūkst, jums tas jāpievieno saskaņā ar instrukcijām “”.

2. iespēja

Ja vārda pareizrakstībā ir kļūda, jums jāpārbauda tā pareizrakstība.

• Atvērtajā darblapas logā nospiediet taustiņu F3.
• Logā “Ievietot vārdu” nosaukumu sarakstā atlasiet vajadzīgo nosaukumu un noklikšķiniet uz pogas “OK”.
• Veiciet labojumus (ja nepieciešams) formulā, kas parādās attiecīgajā šūnā.
• Lai piespraustu, nospiediet taustiņu Enter.

3. iespēja

Ja formula izmanto kļūdaini uzrakstītu funkciju.
Piemēram, SUM(A1:A10), nevis SUM(A1:A10) .

• Atvērtajā darblapas logā atlasiet šūnu ar kļūdaini uzrakstīto funkciju.
• Blakus šai šūnai izvērsiet pogas “Kļūdas avots” izvēlni.
• Komandu sarakstā atlasiet Rediģēt formulu joslā.
• Vārda lodziņā esošajā formulu joslā tiks parādīta pareizā vajadzīgās formulas pareizrakstība, saskaņā ar kuru jūs varat mainīt kļūdaino pareizrakstību.
• Saglabājiet rezultātu, nospiežot taustiņu Enter.

4. iespēja

Ja formulā ievadāt tekstu, kas nav ievietots dubultpēdiņās, jums ir jāpārbauda visi teksta ieraksti formulā un jāiekļauj dubultpēdiņās. Pretējā gadījumā programma Excel mēģinās atpazīt norādīto tekstu kā šūnu diapazona nosaukumu, lai gan tas nav paredzēts.

5. iespēja

Ja atsaucē uz šūnu diapazonu trūkst resnās zarnas, tad, lai to labotu, visās šādās atsaucēs ir jāpārbauda resnās zarnas zīme formulā un pēc vajadzības jālabo.
Piemēram, SUM(A1 A10), nevis SUM(A1:A10) .

6. iespēja

#N/A — ko tas nozīmē un kā to novērst?

Šie simboli norāda, ka funkcijai vai formulai vēlamā vērtība nav pieejama.

1 variants

Ja formulā tika ievadīti trūkstošie dati, kā arī #N/A vai ND(), tad #N/A jāaizstāj ar jauniem datiem.

Apzīmējums #N/A tiek ievadīts šūnās, par kurām dati vēl nav pieejami.

2. iespēja

Ja funkcijas LOOKUP, LOOKUP, MATCH vai VLOOKUP norāda nepareizu argumentu “lookup_value” (piemēram, atsauce uz šūnu diapazonu, kas nav atļauts), tad jums attiecīgi jānorāda atsauce tikai uz vēlamo. šūna.

3. iespēja

Ja standarta darblapas funkcijai nav nodrošināti nepieciešamie argumenti, jums jāievada visi nepieciešamie atbilstošās funkcijas argumenti.

4. iespēja

Ja formula izmanto formulu, kas nav pieejama Šis brīdis funkciju, jums ir jāpārbauda, ​​vai darbgrāmata, izmantojot darblapas funkciju, ir atvērta un vai funkcija darbojas pareizi.

5. iespēja

Ja izmantojat funkcijas VLOOKUP, GLOOKUP vai MATCH, lai skatītu vērtības nešķirotā tabulā, noklusējuma tabulas skata informācijai jābūt augošā secībā.
Funkcijas VLOOKUP un GLOOKUP satur argumentu "interval_lookup", kas ļauj nešķirotā tabulā meklēt noteiktu vērtību. Tomēr, lai atrastu konkrētu vērtību, argumentam "interval_lookup" ir jābūt FALSE.
Funkcija MATCH satur argumentu match_type, kas ļauj kārtot meklēšanas datus. Ja atbilstošo vērtību nevar atrast, argumentu “matching_type” ieteicams iestatīt uz 0.

6. iespēja

Ja masīva formulā tiek izmantots arguments, kas neatbilst masīva formulā norādītajam diapazonam, jums jāpārbauda formulas atsauces diapazons, lai nodrošinātu, ka tas atbilst rindu un kolonnu skaitam, vai arī ievadiet masīva formulu mazākā skaitā šūnu.

7. iespēja

Ja viens vai vairāki nepieciešamie argumenti standarta vai izveidotajai darblapas funkcijai nav norādīti, jums ir jāpārbauda un jāiestata visi nepieciešamie funkcijas argumenti.

#LINK! — Ko tas nozīmē un kā to labot?

1 variants

Ja šūna, uz kuru atsaucas formula, ir izdzēsta vai šī šūna tiek ievietota kopēto šūnu vērtība, jāmaina formula, lai ņemtu vērā jaunās atsauces.

2. iespēja

Ja izmantojat OLE funkciju, kas ir saistīta ar programmu, kas nedarbojas, jums ir jāstartē vajadzīgā programma.

OLE (Object Linking and Embedding) saskarni atbalsta daudzi dažādas programmas un tiek izmantots, lai vienā programmā izveidotu dokumentu ievietotu citā programmā. Piemēram, varat ievietot Word dokuments uz Excel darbgrāmatu un otrādi.

3. iespēja

4. iespēja

Ja izmantojat makro, kas izsauc makro funkciju, kas saskaņā ar noteiktām opcijām atgriež vērtību #LINK! . Ir jāpārbauda funkcijas arguments, lai pārliecinātos, ka tas attiecas uz derīgām šūnām vai šūnu diapazoniem.

#NUMURS! — Ko tas nozīmē un kā to labot?

Šis ziņojums ir par nepareizu skaitlisko vērtību izmantošanu formulā vai funkcijā.

1 variants

Ja funkcijā, kas izmanto ciparu argumentu, ir ievietota nepieņemama vērtība, ir jāpārbauda visi funkcijas argumenti un, ja nepieciešams, jālabo visu skaitļu pareizrakstība un atbilstošo šūnu formāts.

2. iespēja

Ja nav iespējams atrast rezultātu funkcijā ar iterāciju (parametru atlasi), piemēram, “VSD” vai “BET”, tad jāmēģina cita sākotnējā tuvināšana vai jāmaina iterāciju skaits.

3. iespēja

Ja formulas aprēķina rezultāts ir skaitlis, kas ir pārāk liels vai, gluži pretēji, pārāk mazs, lai to parādītu programmā Excel, jums ir jāmaina formula un jānodrošina, lai rezultāts būtu diapazonā no 1*10307 līdz 1*10307. .

#TUKŠS! — Ko tas nozīmē un kā to labot?

Šie ziņojumi norāda, ka nav koplietotu šūnu, kad ir norādīta novirzīšana.
divu reģionu šķērsgriezums.

1 variants

Ja tiek izmantots nepareizs diapazona operators, ir jāveic labojumi, proti:

• Lai norādītu atsauci uz blakus esošo šūnu diapazonu, izmantojiet kolu (:) kā atdalītāju starp diapazona sākuma un beigu šūnām. Piemēram, SUM(C1:C20) .
• Lai norādītu atsauci uz diviem nesavienotiem diapazoniem, tiek izmantots savienojuma operators - semikolu (;). Piemēram, SUM(C1:C20;D1:D20) .

2. iespēja

Ja norādītajos diapazonos nav kopīgu šūnu, tad, lai sasniegtu vēlamo krustojumu, ir jāmaina saites.

Pat nelielas izmaiņas Excel darblapā var radīt kļūdas citās šūnās. Piemēram, jūs varat nejauši ievadīt vērtību šūnā, kurā iepriekš bija formula. Šī vienkāršā kļūda var būtiski ietekmēt citas formulas, un jūs, iespējams, nevarēsit to atklāt, kamēr neveiksit dažas izmaiņas darblapā.

Kļūdas formulās iedala vairākās kategorijās:

Sintakses kļūdas: Rodas, ja formulas sintakse ir nepareiza. Piemēram, formulai ir nepareizas iekavas vai funkcijai ir nepareizs argumentu skaits.

Loģiskās kļūdas:Šajā gadījumā formula neatgriež kļūdu, bet tai ir loģiska kļūda, kuras dēļ aprēķins ir nepareizs.

Nederīgas saites kļūdas: Formulas loģika ir pareiza, bet formula izmanto nepareizu šūnas atsauci. Kā vienkāršu piemēru var minēt, ka SUM formulā summējamo datu diapazons var nesaturēt visus vienumus, kurus vēlaties summēt.

Semantiskās kļūdas: Piemēram, funkcijas nosaukums ir uzrakstīts nepareizi, un tādā gadījumā programma Excel atgriezīs kļūdu #NAME?

Kļūdas masīva formulās: Ievadot masīva formulu, jums ir jānospiež Ctrl + Sift + Enter, kad esat pabeidzis rakstīt. Ja to nedarīsit, programma Excel nesapratīs, ka tā ir masīva formula, un atgriezīs kļūdu vai nepareizu rezultātu.

Kļūdas nepilnīgos aprēķinos:Šajā gadījumā formulas nav pilnībā aprēķinātas. Lai pārliecinātos, ka visas formulas ir pārrēķinātas, ierakstiet Ctrl + Alt + Shift + F9.

Vienkāršākais veids ir atrast un labot sintakses kļūdas. Biežāk jūs zināt, kad formula satur sintakses kļūdu. Piemēram, programma Excel neļaus ievadīt formulu ar nekonsekventām iekavām. Citu sintakses kļūdu gadījumos darblapas šūnā tiek parādītas šādas kļūdas.

Kļūda #DIV/0!

Ja izveidojat formulu, kas dala ar nulli, programma Excel atgriezīs kļūdu #DIV/0!

Tā kā Excel tukšu šūnu apstrādā kā nulli, tad arī dalot ar tukšu šūnu tiks parādīta kļūda. Šī problēma bieži rodas, veidojot formulu datiem, kas vēl nav ievadīti. Formula šūnā D4 ir izstiepta visā diapazonā (=C4/B4).

Šī formula atgriež C un B kolonnu vērtību attiecību. Tā kā netika ievadīti visi dati par dienām, formula atgrieza kļūdu #DIV/0!

Lai izvairītos no kļūdas, varat izmantot , lai pārbaudītu, vai B kolonnas šūnas ir tukšas vai nav:

IF(B4=0;"";C4/B4)

Šī formula atgriezīs tukšu vērtību, ja šūna B4 ir tukša vai satur 0, pretējā gadījumā jūs redzēsit saskaitīto vērtību.

Vēl viena pieeja ir izmantot funkciju ISERROR, kas pārbauda kļūdu. Ja izteiksme C4/B4 atgriež kļūdu, šī formula atgriezīs tukšu virkni:

IFERROR(C4/B4;"")

Kļūda # N/A

Kļūda #N/A rodas, ja šūnā, uz kuru atsauce formula, ir #N/A.

Parasti kļūda #N/A tiek atgriezta palaišanas rezultātā. Gadījumā, ja atbilstība netika atrasta.

Lai uztvertu kļūdu un parādītu tukšu šūnu, izmantojiet funkciju =ESND().

ESND(VLOOKUP(A1;B1:D30;3;0);"")

Lūdzu, ņemiet vērā, ka ESND funkcija ir jauna funkcija programmā Excel 2013. Lai nodrošinātu saderību ar iepriekšējās versijas izmantojiet šīs funkcijas analogu:

IF(END(VLOOKUP(A1,B1:D30,3,0));"";VLOOKUP(A1,B1:D30,3,0))

Kļūda #NAME?

Vai Excel var atgriezt kļūdu #NAME? šādos gadījumos:

• Formulā ir nedefinēts nosaukts diapazons
• Formulā ir teksts, ko Excel interpretē kā nedefinētu nosauktu diapazonu. Piemēram, kļūdaini uzrakstīts funkcijas nosaukums atgriezīs kļūdu #NAME?
• Formulā ir teksts, kas nav likts pēdiņās
• Formulā ir atsauce uz diapazonu, kurā starp šūnu adresēm nav kola
• Formulā tiek izmantota darblapas funkcija, ko definēja pievienojumprogramma, bet pievienojumprogramma netika instalēta

Kļūda # EMPTY!

Kļūda # EMPTY! rodas, kad formula mēģina izmantot divu diapazonu krustpunktu, kas faktiski nekrustojas. Krustojuma operators programmā Excel ir telpa. Tālāk norādītā formula atgriezīs #TUKŠS!, jo diapazoni nepārklājas.

Kļūda #NUMBER!

Kļūda #NUMBER! tiks atmaksāta šādos gadījumos:

• Formulas skaitliskā argumentā tika ievadīta vērtība, kas nav skaitliska (piemēram, 1000 $, nevis 1000)
• Formulā tika ievadīts nederīgs arguments (piemēram, =ROOT(-12))
• Funkcija, kas izmanto iterāciju, nevar aprēķināt rezultātu. Funkciju piemēri, izmantojot iterāciju: VSD(), BET()
• Formula atgriež vērtību, kas ir pārāk liela vai pārāk maza. Programma Excel atbalsta vērtības starp -1E-307 un 1E-307.

Kļūda #LINK!

• Jūs izdzēsāt kolonnu vai rindu, uz kuru bija atsauce formulas šūnā. Piemēram, ja tiks dzēsta pirmā rinda vai kolonnas A vai B, šī formula atgriezīs kļūdu:

• Jūs izdzēsāt darblapu, uz kuru atsaucās formulas šūna. Piemēram, šī formula atgriezīs kļūdu, ja 1. lapa tika noņemts:

• Jūs nokopējāt formulu vietā, kur relatīvā atsauce kļūst nederīga. Piemēram, ja kopējat formulu no šūnas A2 uz šūnu A1, formula atgriezīs kļūdu #REF!, jo tā mēģina atsaukties uz šūnu, kas neeksistē.

• Jūs izgriežat šūnu un pēc tam ielīmējat to šūnā, uz kuru atsaucas formula. Šādā gadījumā tiks atgriezta kļūda #LINK!.

Kļūda #VALUE!

Kļūda #VALUE! ir visizplatītākā kļūda, un tā rodas šādās situācijās:

• Funkcijas argumentam ir nepareizs datu tips vai formula mēģina veikt darbību, izmantojot nepareizus datus. Piemēram, mēģinot teksta vērtībai pievienot skaitlisku vērtību, formula atgriezīs kļūdu
• Funkcijas arguments ir diapazons, kad tam ir jābūt vienai vērtībai
• Pielāgotās lapas funkcijas netiek aprēķinātas. Lai piespiestu veikt pārrēķinu, nospiediet Ctrl + Alt + F9
• Pielāgota darblapas funkcija mēģina veikt darbību, kas nav derīga. Piemēram, pielāgota funkcija nevar mainīt Excel vidi vai veikt izmaiņas citās šūnās
• Jūs aizmirsāt nospiest Ctrl + Shift + Enter, ievadot masīva formulu