Formulu kļūdas programmā Excel un risinājumi

"Kļūdas ir iespēja uzlabot". Jebkurā uzdevumā vai sistēmā rodas kļūdas. Excel nav izņēmums. Mēģinot kaut ko darīt ar formulu, jūs saskarsities ar dažāda veida Excel kļūdām. Šīs kļūdas var padarīt jūsu formulu/informācijas paneli/pārskatus par absolūtu atkritumu. Un, ja jūs nezināt, kāpēc radās noteikta veida kļūda, iespējams, jums būs jāpavada stundas, lai tās novērstu.

Strādājot pie Excel, esmu saskāries ar daudzām Excel kļūdām. Ar zināmu cīņu un Google meklēšanu es novērsu šīs kļūdas. Šajā rakstā es izskaidrošu dažas izplatītas un kaitinošas Excel kļūdas, kas rodas programmā Excel. Mēs apspriedīsim, kāpēc šīs kļūdas rodas un kā tās novērst.

Kas ir Excel formulas kļūdas
Piemērojot formulu, kuras rezultātā rodas Excel noteiktas kļūdas (#NA, #VALUE, #NAME utt.), To sauc par Excel formulas kļūdām. Šīs kļūdas tiek pamanītas programmā Excel un izdrukātas uz lapām. Šo kļūdu iemesli var būt nepieejamas vērtības, nepareizi argumentu veidi, dalīšana ar 0 utt. Tos ir viegli uztvert un labot.

Loģiskās kļūdas neuztver programma Excel, un tās ir visgrūtāk novērst. Datu neatbilstība, nepareiza datu ievadīšana, cilvēku kļūdas utt. Ir bieži sastopami šo kļūdu iemesli. Tos var arī labot, taču tie prasa laiku un pūles. Pirms datu apstrādes labāk ir sagatavot savus datus perfekti.
Excel formulas kļūdu uztveršana:

Programmā Excel ir dažas īpašas funkcijas, lai noķertu un apstrādātu noteikta veida kļūdas (piemēram, ISNA funkcija). Bet funkcija ISERROR un funkcija IFERROR ir divas funkcijas, kas var noķert jebkura veida Excel kļūdu (izņemot loģisku).

Excel #NA kļūda
#NA kļūda rodas programmā Excel, ja vērtība netiek atrasta. Tas vienkārši nozīmē NAV PIEEJAMS. Izmantojot funkciju Excel VLOOKUP, bieži rodas kļūda #NA.

Iepriekš redzamajā attēlā tiek parādīta kļūda #NA, kolonnā A meklējot "Divya". Tas ir tāpēc, ka "Divja nav sarakstā.

Atrisiniet #NA kļūdu

Ja esat pārliecināts, ka uzmeklēšanas vērtībai ir jābūt uzmeklēšanas sarakstā, vispirms jums jāpārbauda uzmeklēšanas vērtība. Pārbaudiet, vai uzmeklēšanas vērtības ir uzrakstītas pareizi. Ja nē, tad izlabojiet to.

Otrkārt, jūs varat veikt daļēju atbilstību ar VLOOKUP vai jebkuru uzmeklēšanas funkciju. Ja esat pārliecināts, ka dažām teksta daļām ir jāatbilst, izmantojiet šo.
Ja neesat pārliecināts, vai vērtība pastāv vai nav, to var izmantot, lai pārbaudītu, vai sarakstā ir kāda vērtība. Iepriekš redzamajā attēlā mēs varam teikt, ka Divja nav sarakstā.

Ja vēlaties noķert #NA kļūdu un izdrukāt vai darīt kaut ko citu, nevis drukāt #NA kļūdu, varat izmantot Excel ISNA funkciju. Funkcija ISNA atgriež TRUE, ja funkcija atgriež #NA kļūdu. Izmantojot to, mēs varam izvairīties no #NA kļūdas. ISNA pārsteidzoši darbojas ar funkciju VLOOKUP. Pārbaudiet to šeit.

Excel #VALUE kļūda
#VALUE rodas, ja iesniegtais arguments nav atbalstīta tipa. Piemēram, ja mēģināsit pievienot divus tekstus, izmantojot aritmētisko plus operatoru (+), tiks parādīta kļūda #VALUE. Tas pats notiks, ja, izmantojot funkciju GADS, mēģināsiet iegūt datumu, kurā datums ir nederīgs.

Kā novērst #VALUE kļūdu?

Vispirms apstipriniet datu tipu, uz kuru atsaucaties. Ja jūsu funkcijai ir nepieciešams skaitlis, pārliecinieties, vai atsaucaties uz numuru. Ja skaitlis ir formatēts kā teksts, izmantojiet funkciju VALUE, lai to pārvērstu par skaitli. Ja funkcijai ir nepieciešams teksts (piemēram, funkcija DATEVALUE) un jūs atsaucaties uz skaitli vai datuma veidu, pārveidojiet tos par tekstu.

Ja jūs domājat, ka var būt kļūda #VALUE, un vēlaties tos noķert, varat izmantot ISERR, ISERROR vai IFERROR, lai noķertu un darītu kaut ko citu.

Excel #REF kļūda
Atsauce #REF ir atsauce. Šī kļūda rodas, ja formula attiecas uz atrašanās vietu, kas neeksistē. Tas notiek, ja mēs izdzēšam šūnas no diapazoniem, uz kuriem attiecas arī formula.

Zemāk esošajā gifā summas formula attiecas uz A2 un B2. Izdzēšot A2, formula pārvēršas par #REF kļūdu.

Atrisiniet Excel #REF kļūdu:
Vislabāk ir būt uzmanīgiem pirms datu šūnu dzēšanas. Pārliecinieties, ka neviena formula neattiecas uz šo šūnu.

Ja jums jau ir kļūda #REF, izsekojiet un izdzēsiet to no formulas.

Piemēram, ja saņemat kļūdu #REF, jūsu formula izskatīsies šādi.

= A2+#REF!

Jūs varat vienkārši noņemt #REF! No formulas, lai iegūtu formulu bez kļūdām. Ja vēlaties to darīt vairumā, izmantojiet funkciju Atrast un aizstāt. Nospiediet CTRL+H, lai atvērtu atrašanu un nomaiņu. Meklēšanas lodziņā ierakstiet #REF. Atstājiet nomaiņas kasti tukšu. Nospiediet pogu Aizstāt visu.

Ja vēlaties no jauna pielāgot atsauci uz jaunu šūnu, dariet to manuāli un nomainiet #REF! Ar šo derīgo atsauci.

Excel #NAME kļūda
#NAME rodas programmā Excel, ja tā nevar identificēt tekstu formulā. Piemēram, ja nepareizi uzrakstāt kādas funkcijas nosaukumu, Excel parādīs kļūdu #NAME. Ja formula atsaucas uz nosaukumu, kura lapā nav, tas parādīs kļūdu #NAME.

Iepriekš redzamajā attēlā šūnai B2 ir formula = POWERS (A2,2). POWERS nav derīga funkcija programmā Excel, tāpēc tā atgriež kļūdu #NAME.

Šūnā B3 mums ir = SUM (skaitļi). SUM ir derīga Excel funkcija, bet "skaitļi" nosauktajā diapazonā uz lapas neeksistē. Tādējādi Excel atgriež #NAME? Kļūda.

Kā izvairīties no #NAME kļūdas programmā Excel?

Lai izvairītos no #NAME kļūdas programmā Excel, vienmēr pareizi uzrakstiet funkciju nosaukumus. Varat izmantot Excel ieteikumus, lai pārliecinātos, ka izmantojat derīgu funkciju. Ikreiz, kad rakstām rakstzīmes aiz vienādības zīmes, Excel parāda lapas funkcijas un nosauktos diapazonus, sākot ar šo rakstzīmi. Ritiniet uz leju līdz funkcijas nosaukumam vai diapazona nosaukumam ieteikumu sarakstā, nospiediet cilni, lai izmantotu šo funkciju.

Excel #DIV/0! Kļūda
Kā norāda nosaukums, šī kļūda rodas, ja formulas rezultāts ir dalījums ar nulli. Šī kļūda var rasties arī tad, ja dzēšat kādu vērtību no šūnas, no kuras ir atkarīga dalīšanas formula.

Kā atrisināt #DIV/0! Kļūda.

To var viegli atrisināt, uzmanīgi rīkojoties ar datiem un pārbaudot, vai formulas rezultāts būs #DIV/0! kļūda. Šeit ir formulas paraugs, kā to izdarīt.

= IF (B2 = 0, A2, A2/B2)

DIV/0 kļūda mums būs tikai tad, ja dalītājs ir 0 vai tukšs. Tāpēc mēs pārbaudām dalītāju (B2), ja tas ir nulle, tad izdrukājiet A2, sadaliet A2 ar B2. Tas darbosies arī tukšām šūnām.

Excel #NUM kļūda

Šī kļūda rodas, ja numuru nevar parādīt ekrānā. Iemesls var būt tas, ka numurs ir pārāk mazs vai pārāk liels, lai to parādītu. Vēl viens iemesls varētu būt tas, ka aprēķinu nevar veikt ar norādīto skaitli.

= SQRT (ABS (A3))

Tas atgriezīs 4. Ja vēlaties iegūt negatīvo vērtību, pirms funkcijas izmantojiet negatīvo zīmi. Protams, jūs, protams, varat izmantot kļūdu apstrādes funkciju.
Par #NUM kļūdas risināšanu mums ir īpašs raksts. To varat pārbaudīt šeit.

#NULL kļūda programmā Excel
Šī ir reta veida kļūda. #NULL kļūda, ko izraisījusi nepareiza šūnu atsauce. Piemēram, ja SUM funkcijā vēlaties norādīt diapazonu A2: A5, bet kļūdas gadījumā ierakstāt A2 A5. Tas radīs #NULL kļūdu. Kā redzat attēlā zemāk, formula atgriež kļūdu #NULL.
Ja jūs aizstājat atstarpi ar kolonnu (:), tad kļūda #NULL vairs nebūs, un jūs saņemsiet A2: A5 summu. Aizstājot atstarpi ar komatu (,), jūs saņemsiet summu A2 un A5.

Kā atrisināt #NULL kļūdu?

Kā mēs zinām, #NULL kļūdu izraisa drukas kļūda. Lai no tā izvairītos, mēģiniet atlasīt diapazonus, izmantojot kursoru, nevis rakstīt to manuāli.

Dažreiz diapazona adrese būs jāievada no tastatūras. Vienmēr rūpējieties par savienojošo simbolu kolonnu (:) vai komatu (,), lai izvairītos.

Ja jums ir kļūda #NULL, pārbaudiet atsauces. Visticamāk, starp divām šūnu atsaucēm esat nokavējis kolonnu (:) vai komatu (,). Nomainiet tos ar atbilstošu simbolu, un jūs varat doties.
###### Kļūda programmā Excel
Dažreiz mēs domājam, ka šī kļūda ir radusies nepietiekamas vietas dēļ, lai parādītu vērtību, taču tas tā nav. Šajā gadījumā jūs varat vienkārši paplašināt šūnas platumu, lai apskatītu šūnas vērtību. Es to nesaukšu par kļūdu.
Faktiski šī kļūda radās, mēģinot parādīt negatīvu laika vērtību (nav tādas lietas kā negatīvs laiks). Piemēram, ja mēs mēģinām atņemt 1 no 12:21:00 PM, tas atgriezīsies ######. Programmā Excel pirmais datums ir 1/1/1900 00:00. Ja mēģināt atņemt laiku, kas pagājis pirms tam, Excel parādīs ###### kļūdu. Jo vairāk jūs paplašināsit platumu, jo vairāk jūs iegūsit #. Es to esmu izstrādājis šajā rakstā.

Kā izvairīties no ###### Excel kļūdas?
Pirms aritmētisko aprēķinu veikšanas programmā Excel ar laika vērtību, paturiet prātā šīs lietas.

Minimālā laika vērtība ir 1/1/1900 00:00. Jūs nevarat norādīt derīgu datumu programmā Excel
1 ir vienāds ar 24 stundām (1 diena) programmā Excel. Atņemot stundas, minūtes un sekundes, pārvērtiet tās līdzvērtīgās vērtībās. Piemēram, lai atņemtu 1 stundu no pulksten 12:21, no tās jāatņem 1/24.

Kļūdu izsekošana programmā Excel
Tagad mēs zinām, kādas ir kopējās Excel formulas un kāpēc tās rodas. Mēs arī apspriedām iespējamo risinājumu katram Excel kļūdu veidam.
Dažreiz Excel pārskatos mēs saņemam kļūdas, un mēs nevarējām zināt, no kurienes kļūda patiesībā rodas. Šādas kļūdas ir grūti atrisināt. Lai izsekotu šīs kļūdas, programma Excel nodrošina kļūdu izsekošanas funkcionalitāti.

Esmu detalizēti apspriedis kļūdu izsekošanu programmā Excel.

Loģisko kļūdu risināšana formulā

Loģiskās kļūdas ir grūti atrast un novērst. Ja jums ir loģiska kļūda jūsu funkcijā, Excel nerāda masāžu. Viss izskatās labi. Bet tikai jūs zināt, ka tur kaut kas nav kārtībā. Piemēram, iegūstot procentus no veseluma daļas, jūs to dala ar kopējo summu (= (daļa/kopā)*100). Tam vienmēr jābūt vienādam vai mazākam par 100%. Kad iegūstat vairāk nekā 100%, jūs zināt, ka kaut kas nav kārtībā.

Tā bija vienkārša loģiska kļūda. Bet dažreiz jūsu dati nāk no vairākiem avotiem, tādā gadījumā kļūst grūti atrisināt loģiskas problēmas. Viens veids ir novērtēt savu formulu.

Cilnē Formula ir pieejama formulu novērtēšanas opcija. Izvēlieties formulu un noklikšķiniet uz tās. Katrs jūsu aprēķina solis jums parādīs, kas novedīs pie gala rezultāta. Šeit jūs varat pārbaudīt, kur radusies problēma vai kur aprēķins ir nogājis greizi.

Varat arī izsekot apgādājamajiem un precedentiem, lai noskaidrotu, no kādām atsaucēm jūsu formula ir atkarīga un kuras formulas ir atkarīgas no pertikulāras šūnas.

Tātad, jā, puiši, tie bija daži izplatīti kļūdu veidi, ar kuriem saskaras katrs Excel lietotājs. Mēs uzzinājām, kāpēc rodas katrs kļūdu veids un kā mēs varam no tām izvairīties. Mēs uzzinājām arī par īpašu kļūdu apstrādes Excel funkciju. Šajā rakstā mums ir saites uz saistītām lapām, kurās detalizēti tiek apspriesta problēma. Jūs varat tos pārbaudīt. Ja jums ir kāda veida kļūda, kas jūs kaitina, miniet to tālāk komentāru sadaļā. Jūs saņemsiet risinājumu kārtīgi.