10 posibile erori în funcțiile Excel și soluțiile lor

Iată cum să descoperiți automat aceste erori în Excel

Datorită varietății mari de opțiuni de calcul, erorile tipice în funcțiile Excel apar din nou și din nou cu foile de calcul. Nicio problemă - în majoritatea cazurilor acestea pot fi corectate cu doar câteva clicuri și puteți continua să utilizați tabelul. Aici veți găsi o prezentare generală a celor mai frecvente erori și sfaturi practice despre cum să le remediați. De asemenea, veți învăța posibile strategii de evitare.

Acestea sunt 10 greșeli frecvente în funcțiile Excel

Următoarele sunt cele mai frecvente zece coduri de eroare pentru posibile probleme și soluții Excel.

1. #NUME?

Apar erori de tastare - dacă acesta este cazul în formulă, apare mesajul de eroare #NAME? În locul rezultatului. Nu are rost să ascundem eroarea cu o funcție precum IFERROR. Trebuie să-l corectezi. Tot ce trebuie să faceți este să vă uitați cu atenție la numele pe care l-ați dat formula. Există o scrisoare răsucită? Ați uitat o scrisoare sau ați introdus-o de două ori? De exemplu, corectați = SUME (A3: A16) la = SUM (A3: A16) și veți obține rezultatul corect.

Utilizați expertul cu formule

Puteți evita aceste tipuri de greșeli folosind Formula Assistant. Funcționează așa:

Când începeți să tastați numele formulei, se deschide un meniu derulant cu numele care corespund valorilor introduse.

Dacă introduceți numele și parantezele de deschidere, ortografia corectă va fi dată în textul cu mouse-ul.

Expertul funcției vă va ajuta la intrarea în funcție. Dacă marcați celula cu formula și selectați „Inserați funcția” în fila „Formula”, Excel apelează expertul. Argumentele individuale sunt afișate aici - și, de asemenea, dacă există o eroare.

2. # NUL!

Acest mesaj de eroare poate indica două lucruri:

• Ați specificat un operator de interval incorect într-o formulă.
• Utilizați un operator de intersecție pe două zone care nu se suprapun.

În primul caz, vă referiți la un interval continuu de celule dintr-o formulă - de exemplu celulele B4 până la B12. Utilizați două puncte ca operator de gamă. De exemplu, dacă doriți să calculați un total, formula corectă este = SUM (B4: B12). Dacă vă referiți la două zone fără suprapunere, punctul și virgula este operatorul adecvat. Dacă doriți să calculați suma din zonele B4 până la B12 și C9 până la C23, această formulă este corectă: = SUM (B4: B12; C9: C23).

În al doilea caz, doriți să lucrați cu intervale de celule suprapuse, dar cele două intervale specificate nu se suprapun. De exemplu, primiți eroarea pentru formula = CELL („Adresă” (B4: B12 D4: D6)). Nu există suprapuneri între cele două zone. Dacă modificați zonele astfel încât acestea să se suprapună - de exemplu ca = CELL ("Adresă" (B4: B12 B5: D5)) - punctul de intersecție al ambelor zone este afișat ca rezultat. În acest caz, este celula B5.

3. #REFERINȚĂ!

Mesajul de eroare #REZUG! indică faptul că o zonă la care vă referiți nu există. Programul nu se poate referi la intervalul specificat pentru calcul. Acest lucru se întâmplă, de exemplu, când se șterge o foaie de lucru, un rând, o coloană sau o celulă la care faceți referire în formulă.

De exemplu, aveți o foaie de calcul Excel cu rândurile 1, 2 și 3 și coloanele A, B și C. Formula este = SUM (A2; B2; C2). Acum ștergeți linia 2. În loc de rezultat, veți vedea apoi valoarea de eroare #REFER, deoarece una dintre valorile pentru calcul nu există.

Dacă ați șters accidental partea care lipsește pentru calcul, puteți corecta eroarea direct cu comanda „Anulați”. Dacă sub-zona a fost ștearsă pe bună dreptate, reformulați formula. Dacă introduceți = SUM (A2: C2), vi se va afișa rezultatul corect, deoarece Excel elimină pur și simplu a doua coloană ștearsă.

4. #VALUARE!

Acest mesaj de eroare poate indica multe probleme Excel diferite. Una dintre valorile din tabel nu se potrivește cu informațiile din formulă, astfel încât programul nu poate efectua calculul. Acesta este cazul, de exemplu, dacă una dintre celulele utilizate pentru calcul conține un cuvânt în loc de un număr.

Deoarece există multe surse potențiale de eroare pentru #VALUE! există multe modalități de a-l corecta. Următoarele soluții vă pot ajuta:

• Puteți utiliza funcții în loc de operatori matematici. Deci, în loc de = B4 + B5 + B6 introduceți funcția = SUM (B4: B6).
• Verificați celulele în cauză pentru caracterele speciale. Dacă este necesar, utilizați funcția ISTTEXT într-o coloană separată pentru aceasta. Aceasta vă arată în ce celulă se află eroarea.
• Poate că valoarea erorii se datorează spațiilor dintr-o celulă care apare în funcție. Pentru a afla, marcați celulele corespunzătoare. Apoi accesați „Căutare și selectare” sub „Start” și faceți clic pe „Înlocuiți”. Introduceți un spațiu pentru „Căutare” și nimic pentru „Înlocuiți cu”. Acum puteți înlocui spațiile nedorite.
• Caracterele ascunse pot duce, de asemenea, la această eroare comună în funcțiile Excel. Pentru a le elimina, accesați „Start” și „Sortare și filtrare” din „Filtru”. Sub săgeata filtrului, dezactivați „Selectați tot” și bifați „Celule goale” și toate pozițiile în care nu există nimic. Dacă apăsați OK, Excel vă va arăta toate celulele goale presupuse care conțin simboluri ascunse. Evidențiați-l și apăsați „Eliminare”. Ștergeți din nou filtrul.

5. #####

Când o întreagă celulă Excel apare plină de diamante, arată mai rău decât este la prima vedere. Acest lucru înseamnă doar că coloana Excel este prea îngustă pentru a afișa întregul conținut al celulei. Pur și simplu trageți coloana mai lată cu mouse-ul sau selectați o dimensiune mai mică a fontului, astfel încât întregul conținut să poată fi afișat.

6. # DIV / 0!

De exemplu, să presupunem că introduceți o funcție pe care doriți să o împărțiți mai multe valori la alte valori. Atunci se poate întâmpla să existe un zero într-una dintre celulele ale căror valori doriți să le împărțiți - sau nimic. În acest caz, valoarea # DIV / 0! Eroare apare pentru a indica faptul că împărțirea la zero nu este permisă.

Soluția: asigurați-vă că nu există zero sau nimic în celulele Excel corespunzătoare sau modificați referința. Alternativ, puteți suprima afișarea erorii - și anume când așteptați încă valori pe care doriți să le inserați în celulele respective. Pentru aceasta puteți utiliza formula Excel IFERROR, de exemplu, care este prezentată mai detaliat mai jos.

7. #NV!

Cu această valoare de eroare, Excel indică faptul că ceea ce căutați nu a putut fi găsit. De exemplu, dacă ați atribuit valori anumitor termeni și doriți să le afișați, dar unul dintre termeni lipsește, apare acest mesaj de eroare în locul rezultatului. Acest lucru se face adesea împreună cu formulele LOOKUP, HLOOKUP, MATCH sau VLOOKUP. De exemplu, puteți afișa erori cu VLOOKUP, deoarece vă ajută să comparați tabele între ele sau să le rezumați.

Un exemplu: ați creat un tabel cu prețurile respective pentru șuruburi, cârlige, știfturi, șuruburi, piulițe etc. și ați uitat unul dintre termeni. Programul nu-l găsește. În consecință, nu poate exista preț, doar #NV! indica. Rezolvi această problemă adăugând termenul. Alternativ, puteți suprima afișarea erorii cu formula IFERROR (a se vedea mai jos).

8. #NUMBER

Dacă funcția conține o valoare numerică nevalidă, veți primi eroarea #NUMBER. Acest lucru se poate întâmpla, de exemplu, atunci când scrieți numere precum 1000 cu o perioadă după cea. Introduceți numerele fără nicio formatare. Poate că luați și o rădăcină pătrată a unui număr negativ undeva sau faceți alte calcule care nu funcționează matematic?

O altă situație care poate duce la valoarea de eroare #NUM este utilizarea funcțiilor de iterație, cum ar fi dobânda. Pentru a obține un rezultat în acest caz, pur și simplu modificați numărul de iterații pe care Excel le permite pentru calcul - adică utilizarea repetată a aceleiași metode de calcul. Acest lucru este necesar atunci când se calculează dobânda, de exemplu, dacă se adaugă trei procente din nou și din nou.

Cum să o facă:

1. Sub „Fișier” și „Opțiuni” selectați „Formule”. Aici, sub „Opțiuni de calcul”, bifați caseta de lângă „Activați calculul iterativ”.

2. Sub „Numărul maxim de iterații” puteți specifica numărul de calcule pe care Excel trebuie să le efectueze. Cu cât acest număr este mai mare, cu atât va dura mai mult să se calculeze.

3. În câmpul „Modificare maximă”, puteți specifica cât de mult poate fi valoarea modificării între două rezultate de facturare.

În cazuri rare, se poate întâmpla ca rezultatul unei formule să fie prea mare sau prea mic pentru un calcul în Excel. Pentru aceasta, însă, rezultatul trebuie să fie sub -1x10 (înalt) 307 sau peste 1x10 (înalt) 307. În acest caz, trebuie să editați formulele, astfel încât rezultatul să fie într-un interval pe care Excel îl poate calcula.

9. Referință circulară

Mesajul de eroare Referință circulară? Apoi ați introdus o formulă care se referă direct sau indirect la celula în care se află formula. Eroarea de referință circulară indică faptul că formula nu se poate calcula singură și, prin urmare, nu este posibil niciun rezultat. Dacă funcțiile se referă unele la altele, este o referință indirectă, incorectă.

Cum se rezolvă eroarea:

1. Dacă există o referință circulară, mesajul de eroare apare în punctul corespunzător. În acest caz, puteți schimba direct celula. Nu indentați celula curentă în timp ce introduceți formula.

2. Dacă doriți să găsiți referințe circulare nedescoperite, accesați „Formule”, „Monitorizare formulă” și „Referințe circulare”. Toate referințele circulare din document sunt acum afișate și le puteți rezolva ca în primul caz.

10. E +

E + nu este un bug tipic în funcțiile Excel, dar creează totuși confuzie pentru utilizatori din când în când. Dacă introduceți un număr foarte mare într-una dintre celule, Excel îl poate abrevia. Apoi E + intră în joc. De exemplu, 265000000000000 în celula Excel devine 2.65E + 14. Este un format exponențial. Excel îl folosește pentru a putea afișa chiar și numere mari, astfel încât acestea să fie complet vizibile în celule.

Acesta este modul în care puteți afișa numerele în întregime

Dacă doriți ca numerele să fie afișate integral, sunt necesari câțiva pași simpli:

1. Marcați pozițiile relevante din lista dvs.

2. Apăsați Ctrl și 1 pentru a putea formata celulele.

3. În caseta de dialog care se deschide, selectați fila „Numere” și categoria „Număr”.

4. Introduceți numărul mare și alegeți câte zecimale doriți să afișați și dacă doriți să fie afișat un separator de mii.

5. După ce faceți clic pe OK, Excel vă va arăta numărul complet din tabel.

Detectați automat erorile tipice în funcțiile Excel

Aveți opțiunea de a activa verificarea automată Excel. Dacă nu este încă activat pentru dvs., sunt suficiente câteva clicuri:

Sub „Fișier”, accesați „Opțiuni”.

Selectați „Formule”.

Bifați caseta de lângă „Activați verificarea erorilor de fundal”.

Dacă faceți clic pe una dintre celulele din registrul de lucru care conține un mesaj de eroare, un mic semn de exclamare va apărea lângă el. Dacă o selectați, va apărea o listă. Acesta explică ce mesaj de eroare este și oferă opțiuni de soluție, pași de calcul sau ajutor suplimentar cu privire la problemă. Alternativ, eroarea poate fi ignorată.

Aceste funcții identifică erorile din funcțiile Excel

Puteți utiliza aceste funcții pentru a verifica rapid erorile tipice din funcțiile Excel.

IFERROR

Este posibil ca unele formule să nu fie complete, așa cum stau lucrurile. Doriți să preveniți afișarea erorilor dvs.? Funcția IFERROR este potrivită pentru aceasta. Evaluează tipurile de erori #NV, #VALUE!, #REFERENCE!, # DIV / 0!, #NUMBER!, #NAME? sau # NUL! sfarsit. Are o structură simplă, deoarece conține doar valoarea și valoarea_if_error. Prima valoare verifică corectitudinea funcției. Programul ar trebui să afișeze ultima valoare din rezultat dacă există o eroare. Funcția este foarte utilă atunci când editați un tabel pentru o lungă perioadă de timp.

Un exemplu:

Introduceți = IFERROR (B3 * C3; "Introduceți valori") sau = IFERROR (B3 * C3; ""). În primul caz, vi se va reaminti ce mai aveți de făcut (dacă există o eroare) cu textul „Adăugați valori”. În al doilea caz, celula care ar conține în mod normal rezultatul rămâne pur și simplu goală.

Atenţie: Când introduceți text într-o formulă, trebuie să îl puneți întotdeauna între ghilimele. Altfel nu va funcționa. Deoarece fără ghilimele, Excel înțelege cuvintele ca parte a formulei.

ISERROR

În legătură cu funcția IFERROR, funcția ISERROR poate indica și erorile existente. În acest fel, acestea nu împiedică alte calcule din listă. Pentru a face acest lucru, introduceți, de exemplu: = IF (ISERROR (B3); "vă rugăm să verificați"; B3 / 6). Dacă calculul poate fi efectuat, veți primi rezultatul. Dacă Excel detectează o eroare în timpul verificării B3, cuvintele „vă rugăm să verificați” apar în celula corespunzătoare.

Concluzie: Remediați rapid și ușor erorile din funcțiile Excel

Fie că este o literă inversă, o formulă care nu poate fi calculată matematic, o referință greșită sau un spațiu pentru că lipsește încă o valoare - greșelile se întâmplă în munca de zi cu zi. Excel știe și asta. Verificarea automată indică erorile tipice în funcțiile Excel și explică despre ce este vorba. Există, de asemenea, funcții care verifică sau ascund erorile, astfel încât să puteți trece la restul foii de calcul.