Detectarea erorilor din formule

Următorul tabel rezumă unele dintre cele mai comune erori pe care le poate face un utilizator atunci când introduce o formulă și vă arată cum să le remediați.

Asigurați-vă că	Mai multe informații
Începeți fiecare funcție cu semnul egal (=)	Dacă omiteți semnul egal, ceea ce tastați poate fi afișat ca text sau ca dată. De exemplu, dacă tastați SUM(A1:A10), Excel afișează șirul de text SUM(A1:A10) și nu efectuează calculul. Dacă tastați 11/2, Excel afișează data 2-Nov (presupunând că formatul celulei este General), în loc să împartă 11 la 2.
Închideți toate parantezele	Asigurați-vă că toate parantezele au pereche (deschise și închise). Atunci când utilizați o funcție într-o formulă, este important ca fiecare paranteză să fie în poziția corectă pentru ca funcția să funcționeze corect. De exemplu, formula =IF(B5<0); "Nu este valid";B5*1,05) nu va funcționa, deoarece există două paranteze închise și o singură paranteză deschisă, deși ar trebui să existe o singură paranteză deschisă și una închisă. Formula ar trebui să arate astfel: =IF(B5<0;"Nu este valid";B5*1,05).
Utilizați două puncte pentru a indica o zonă	Atunci când faceți referire la o zonă de celule, utilizați două puncte (:) pentru a separa referința la prima celulă din zonă de referința la ultima celulă din zonă. De exemplu, =SUM(A1:A5), nu =SUM(A1 A5), care ar returna o #NULL! Eroare.
Introduceți toate argumentele necesare	Unele funcții au argumente necesare. De asemenea, asigurați-vă că nu ați introdus prea multe argumente.
Introduceți tipul corect de argumente	Unele funcții, cum ar fi SUM, au nevoie de argumente numerice. Alte funcții, cum ar fi REPLACE, necesită o valoare text pentru cel puțin unul dintre argumente. Dacă utilizați un tip greșit de date ca argument, Excel ar putea să returneze rezultate neașteptate sau să afișeze o eroare.
Nu imbricați mai mult de 64 de funcții	Puteți să introduceți sau să imbricați maximum 64 de niveluri de funcții într-o funcție.
Alte nume de foi de lucru trebuie închise în ghilimele simple	Dacă formula se referă la valori sau celule din alte foi de lucru sau registre de lucru și numele altui registru de lucru sau altei foi de lucru conține un caracter nealfabetic, trebuie să-i încadrați numele în ghilimele simple ( ' ), de exemplu ='Date trimestriale'!D3 sau =‘123’!A1.
Plasați un semn de exclamare (!) după numele unei foi de lucru atunci când faceți referire la ea într-o formulă	De exemplu, pentru a returna valoarea din celula D3 dintr-o foaie de lucru denumită Date Trimestriale din același registru de lucru, utilizați această formulă: ='Date Trimestriale'!D3.
Includeți și calea la registrele de lucru externe	Asigurați-vă că fiecare referință externă conține un nume de registru de lucru și calea la registrul de lucru. O referință la un registru de lucru conține numele registrului de lucru și trebuie să fie încadrată în paranteze drepte ([Workbookname.xlsx]). Referința trebuie, de asemenea, să conțină numele foii de lucru din registrul de lucru. Dacă registrul de lucru la care doriți să faceți referire nu este deschis în Excel, puteți adăuga o referință la el într-o formulă. Furnizați calea completă la fișier, cum ar fi în următorul exemplu: =ROWS('C:\Documentele mele\[Operațiuni Q2.xlsx]Vânzări'!A1:A8). Această formulă returnează numărul de rânduri din zona care conține celulele A1 până la A8 din celălalt registru de lucru (8). : În cazul în care calea conține spații, cum este cazul în exemplul anterior, trebuie să o încadrați în ghilimele simple (la începutul căii și după numele foii de lucru, înainte de semnul de exclamare).
Introduceți numerele fără formatare	Nu formatați numerele atunci când le introduceți în formule. De exemplu, dacă valoarea pe care doriți s-o introduceți este 1.000 de lei, introduceți 1000 în formulă. Dacă introduceți o virgulă ca parte a unui număr, Excel o tratează ca pe un caracter separator. Dacă doriți ca numerele să fie afișate astfel încât să indice separatori de mii sau milioane sau simboluri monetare, formatați celulele după ce introduceți numerele. De exemplu, dacă doriți să adunați 3100 la valoarea din celula A3 și introduceți formula =SUM(3;100;A3),Excel adună numerele 3 și 100, apoi adună totalul la valoarea din A3, în loc să adune 3100 la A3, care ar fi =SUM(3100,A3). Sau, dacă introduceți formula =ABS(-2,134), Excel afișează o eroare, deoarece funcția ABS acceptă un singur argument: =ABS(-2134).

Puteți implementa anumite reguli pentru a verifica erorile din formule. Aceste reguli nu garantează că foaia de lucru nu conține nicio eroare, dar pot ajuta la găsirea greșelilor comune. Puteți să activați sau să dezactivați aceste reguli în mod individual.

Erorile pot fi marcate și corectate în două moduri: câte o eroare pe rând (ca un verificator ortografic) sau imediat ce apar în foaia de lucru, pe măsură ce introduceți date.

Puteți rezolva o eroare utilizând opțiunile pe care le afișează Excel sau puteți ignora eroarea selectând Ignorare eroare. Dacă ignorați o eroare într-o anumită celulă, eroarea din celula respectivă nu apare în verificările de erori ulterioare. Totuși, puteți reseta toate erorile ignorate anterior, astfel încât să apară din nou.

1. Pentru Excel în Windows, accesați Opțiuni > fișier > Formule sau
   pentru Excel pe Mac, selectați meniul Excel > Preferințe > Verificare erori.

2. Sub Verificare erori, bifați caseta Activare verificare erori în fundal. Orice eroare găsită este marcată cu un triunghi în colțul din stânga sus al celulei.

   

3. Pentru a modifica culoarea triunghiului care marchează locația de apariție a unei erori, selectați culoarea dorită în caseta Se indică erorile utilizând această culoare.

4. Sub Reguli de verificare a erorilor, bifați sau debifați casetele de selectare pentru oricare dintre următoarele reguli:

   • Celule care conțin formule care au ca rezultat o eroare: O formulă nu utilizează sintaxa așteptată, argumentele sau tipurile de date așteptate. Valorile de eroare includ #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! și #VALUE!. Fiecare dintre aceste valori de eroare are cauze diferite și este rezolvată în moduri diferite.

     : Dacă introduceți o valoare de eroare direct într-o celulă, aceasta este stocată ca acea valoare de eroare, dar nu este marcată ca eroare. Totuși, dacă o formulă dintr-o altă celulă face referire la celula respectivă, formula returnează valoarea de eroare din celula respectivă.

   • Formulă coloană calculată inconsistentă în tabele: O coloană calculată este posibil să includă formule individuale care sunt diferite de formula din coloana principală, ceea ce creează o excepție. Excepțiile coloanelor calculate se creează când efectuați una dintre acțiunile următoare:

     • Tastați date diferite de o formulă într-o celulă de coloană calculată.

     • Tastați o formulă într-o celulă de coloană calculată, apoi utilizați Ctrl +Z sau selectați Anulare pe bara de instrumente Acces rapid.

     • Tastați o formulă nouă într-o coloană calculată care conține deja una sau mai multe excepții.

     • Copiați datele în coloana calculată care nu se potrivește cu formula din coloană calculată. Dacă datele copiate conțin o formulă, această formulă suprascrie datele din coloana calculată.

     • Mutați sau ștergeți o celulă din altă zonă a foii de lucru la care se face referire într-unul din rândurile din coloana calculată.

   • Celule care conțin ani reprezentați prin două cifre: Celula conține o dată text care poate fi interpretată ca fiind în secolul greșit atunci când se utilizează în formule. De exemplu, anul din formula =YEAR("1.1.31") poate fi 1931 sau 2031. Utilizați această regulă pentru a verifica prezența datelor text ambigue.

   • Numere formatate ca text sau precedate de un apostrof: Celula conține numerele stocate ca text. Acest lucru apare de obicei atunci când se importă date din alte surse. Numerele stocate ca text pot provoca rezultate de sortare neașteptate, deci cel mai bine este să le efectuați conversia în numere. ‘=SUM(A1:A10) este afișată ca text.

   • Formulele nu corespund cu alte formule din zonă: Formula nu se potrivește cu modelul celorlalte formule de lângă ea. În multe cazuri, formulele adiacente altor formule diferă doar în referințele utilizate. În următorul exemplu de patru formule adiacente, Excel afișează o eroare lângă formula =SUM(A10:C10) din celula D4, deoarece formulele adiacente se incrementează cu un rând și aceasta se incrementează cu 8 rânduri, iar Excel așteaptă formula =SUM(A4:C4).

     

     Dacă referințele utilizate într-o formulă nu sunt în concordanță cu cele din formulele adiacente, Excel afișează o eroare.

   • Formule care omit celule dintr-o regiune: O formulă poate să nu includă automat referințe la datele pe care le introduceți între zona originală de date și celula care conține formula. Această regulă compară referința dintr-o formulă cu zona efectivă de celule adiacentă celulei care conține formula. În cazul în care celulele adiacente conțin valori suplimentare și nu sunt necompletate, Excel afișează o eroare lângă formulă.

     De exemplu, Excel inserează o eroare lângă formula =SUM(D2:D4) atunci când se aplică această regulă, deoarece celulele D5, D6 și D7 sunt adiacente celulelor la care se face referire în formulă și celulei care conține formula (D8), iar acele celule conțin date la care ar fi trebuit să se facă referire în formulă.

     

   • Celule neblocate care conțin formule: Formula nu este blocată pentru protecție. În mod implicit, toate celulele dintr-o foaie de lucru sunt blocate, astfel încât să nu poată fi modificate atunci când foaia de lucru este protejată. Acest lucru poate ajuta la prevenirea greșelilor din neatenție, cum ar fi ștergerea sau modificarea accidentală a formulelor. Această eroare indică faptul că celula a fost setată să fie deblocată, dar foaia nu a fost protejată. Asigurați-vă că nu doriți să fie blocată celula.

   • Formule care fac referire la celule necompletate: Formula conține o referință la o celulă necompletată. Aceasta poate determina rezultate neintenționate, ca în exemplul următor.

     Să presupunem că doriți să calculați media numerelor din următoarea coloană de celule. Dacă a treia celulă este necompletată, nu este inclusă în calcul și rezultatul este 22,75. Dacă a treia celulă conține 0, rezultatul este 18,2.

     

   • Datele introduse în tabel nu sunt valide: Există o eroare de validare într-un tabel. Verificați setările de validare pentru celulă accesând fila Date > grupul Instrumente de date > Validare date.

1. Selectați foaia de lucru în care doriți să verificați dacă există erori.

2. Dacă foaia de lucru se calculează manual, apăsați F9 pentru a se relua calculul.

   Dacă nu se afișează caseta de dialog Verificare erori , selectați Formule > Audit formule > Verificare erori.

3. Dacă anterior ați ignorat erori, puteți verifica aceste erori din nou, efectuând următoarele: accesați Fișier > Opțiuni > Formule. Pentru Excel pe Mac, selectați meniul Excel > Preferințe > Verificare erori.

   În secțiunea Verificare erori , selectați Reinițializare erori ignorate > OK.

   

   : Resetarea erorilor ignorate va reseta toate erorile din toate foile registrului de lucru activ.

   : Poate fi util să poziționați caseta de dialog Verificare erori chiar sub bara de formule.

   

4. Selectați unul dintre butoanele de acțiune din partea dreaptă a casetei de dialog. Acțiunile disponibile sunt diferite în funcție de tipul de eroare.

5. Selectați Următorul.

1. Lângă celulă, selectați Verificare erori , apoi selectați opțiunea dorită. Comenzile disponibile sunt diferite în funcție de tipul de eroare, iar prima intrare descrie eroarea.

   Dacă selectați Ignorare eroare, eroarea este marcată pentru a fi ignorată pentru fiecare verificare consecutivă.

   

Dacă o formulă nu poate evalua corect un rezultat, Excel afișează o valoare de eroare, cum ar fi #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!. Fiecare tip de eroare are cauze diferite și soluții diferite.

Următorul tabel conține linkuri către articole ce descriu în detaliu aceste erori, precum și o descriere scurtă ca să puteți începe lucrul.

Subiect	Descriere
Corectarea unei erori ####	Excel afișează această eroare atunci când o coloană nu este suficient de lată pentru a afișa toate caracterele dintr-o celulă sau o celulă conține valori dată sau oră negative. De exemplu, o formulă care scade o dată din viitor dintr-o dată din trecut, cum ar fi =15.06.2008-01.07.2008, are ca rezultat o valoare dată negativă. : Încercați să potriviți automat celula făcând dublu clic între anteturile de coloană. Dacă se afișează ### deoarece Excel nu poate afișa toate caracterele, acest lucru le corectează.
Corectarea erorii #DIV/0! eroare	Excel afișează această eroare atunci când un număr este împărțit fie la zero (0), fie la o celulă care nu conține nicio valoare. : Adăugați o rutină de tratare a erorilor ca în exemplul următor, care este =IF(C2,B2/C2,0)
Corectarea unei erori #N/A	Excel afișează această eroare atunci când o valoare nu este disponibilă pentru o funcție sau o formulă. Dacă utilizați o funcție precum VLOOKUP, ceea ce încercați să căutați are o potrivire în zona de căutare? De cele mai multe ori nu. Încercați să utilizați IFERROR pentru a suprima #N/A. În acest caz, puteți să utilizați: =IFERROR(VLOOKUP(D2,$D$6:$E$8,2,TRUE),0)
Corectarea erorii #NAME? eroare	Această eroare se afișează atunci când Excel nu recunoaște textul dintr-o formulă. De exemplu, un nume de zonă sau numele unei funcții poate fi ortografiat incorect. : Dacă utilizați o funcție, asigurați-vă că numele funcției este scris corect. În acest caz, SUM este scris incorect. Eliminați "e" și Excel îl corectează.
Corectarea unei erori #NUL! eroare	Excel afișează această eroare atunci când specificați o intersecție între două zone care nu se intersectează (încrucișează). Operatorul de intersecție este un caracter spațiu ce separă referințele dintr-o formulă. : Asigurați-vă că zonele sunt separate corect - zonele C2:C3 și E4:E6 nu se intersectează, așa că introducerea formulei =SUM(C2:C3 E4:E6) returnează #NULL! eroare. Plasarea unei virgule între zonele C și E o corectează =SUM(C2:C3,E4:E6)
Corectarea erorii #NUM! eroare	Excel afișează această eroare atunci când o formulă sau o funcție conține valori numerice nevalide. Utilizați o funcție care iterează, cum ar fi IRR sau RATE? Dacă da, eroarea #NUM! se datorează probabil faptului că funcția nu găsește un rezultat. Consultați subiectul de ajutor pentru pașii de rezolvare.
Corectarea unei erori #REF! eroare	Excel afișează această eroare atunci când o referință de celulă nu este validă. De exemplu, este posibil să fi șters celule la care făceau referire alte formule sau să fi lipit celule mutate deasupra celulelor la care făceau referire alte formule. Ați șters din greșeală un rând sau o coloană? Am șters coloana B din această formulă, =SUM(A2,B2,C2) și iată ce s-a întâmplat. Utilizați Anulare (Ctrl+Z) pentru a anula ștergerea, reconstruiți formula sau utilizați o referință la o zonă continuă astfel: =SUM(A2:C2), care s-a actualizat automat când a fost ștearsă coloana B.
Corectarea erorii #VALUE! eroare	Excel poate afișa această eroare dacă formula include celule cu tipuri diferite de date. Utilizați operatori matematici (+, -, *, /, ^) cu tipuri diferite de date? Dacă da, încercați să utilizați o funcție în locul lor. În acest caz, =SUM(F2:F5) va corecta problema.

Atunci când celulele nu sunt vizibile într-o foaie de lucru, puteți urmări acele celule și formulele lor în bara de instrumente Fereastră supraveghere. Fereastra supraveghere face comode inspectarea, auditarea sau confirmarea calculelor și rezultatelor de formule din foi de lucru mari. Dacă utilizați Fereastra supraveghere, nu trebuie să parcurgeți sau să accesați în mod repetat diferite părți ale foii de lucru.

Această bară de instrumente poate fi mutată sau andocată la fel ca orice altă bară de instrumente. De exemplu, o puteți andoca în partea de jos a ferestrei. Bara de instrumente urmărește următoarele proprietăți ale celulei: 1) Registru de lucru, 2) Foaie, 3) Nume (dacă celula are o zonă denumită corespunzătoare), 4) Adresă celulă, 5) Valoare și 6) Formulă.

Adăugarea celulelor la Fereastra supraveghere

1. Selectați celulele pe care doriți să le supravegheați.

   Pentru a selecta toate celulele dintr-o foaie de lucru cu formule, accesați Pornire > Editare > selectați Găsire & Selectare (sau puteți utiliza Ctrl+G sau Control+G pe Mac)> Salt la formulespeciale >.

   

2. Accesați Formule > Audit formule > selectați Fereastră supraveghere.

3. Selectați Adăugare supraveghere.

   

4. Confirmați că ați selectat toate celulele pe care doriți să le urmăriți și selectați Adăugare.

   

5. Pentru a modifica lățimea unei coloane Fereastră supraveghere, glisați marginea pe partea dreaptă a titlului de coloană.

6. Pentru a afișa celula la care face referire o intrare din bara de instrumente Fereastră supraveghere, faceți dublu clic pe intrare.

   : Celulele care au referințe externe la alte registre de lucru sunt afișate în bara de instrumente Fereastră supraveghere doar atunci când sunt deschise celelalte registre de lucru.

Eliminarea celulelor din Fereastra supraveghere

1. Dacă bara de instrumente Fereastră supraveghere nu este afișată, accesați Formule > Audit formule > selectați Fereastră supraveghere.

2. Selectați celulele pe care doriți să le eliminați.

   Pentru a selecta mai multe celule, apăsați CTRL, apoi selectați celulele.

3. Selectați Ștergere supraveghere.

   

Uneori, înțelegerea modului în care o formulă imbricată calculează rezultatul final este dificilă, deoarece există mai multe calcule și teste logice intermediare. Cu toate acestea, prin utilizarea casetei de dialog Evaluare formulă, puteți vedea diferitele părți ale unei formule imbricate evaluate în ordinea în care se calculează formula. De exemplu, formula =IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0) este mai ușor de înțeles când puteți vedea următoarele rezultate intermediare:

În caseta de dialog Evaluare formulă	Descriere
=IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0)	Formula imbricată se afișează inițial. Funcțiile AVERAGE și SUM sunt imbricate în funcția IF. Zona de celule D2:D5 conține valorile 55, 35, 45 și 25, așadar, rezultatul funcției AVERAGE(D2:D5) este 40.
=IF(40>50,SUM(E2:E5),0)	Zona de celule D2:D5 conține valorile 55, 35, 45 și 25, așadar, rezultatul funcției AVERAGE(D2:D5) este 40.
=IF(False,SUM(E2:E5),0)	Pentru că 40 nu este mai mare decât 50, expresia din primul argument al funcției IF (argumentul test_logic) este False. Funcția IF returnează valoarea celui de-al treilea argument (argumentul valoare_dacă_fals). Funcția SUM nu se evaluează, deoarece este al doilea argument al funcției IF (argumentul valoare_dacă_adevărat) și se returnează numai atunci când expresia este True.

1. Selectați celula pe care doriți s-o evaluați. O singură celulă poate fi evaluată la un moment dat.

2. Accesați Formule > Audit formule > Evaluare formulă.

3. Faceți clic pe Evaluare pentru a examina valoarea referinței subliniate. Rezultatul evaluării este afișat în format cursiv.

   Dacă partea subliniată a formulei este o referință la o altă formulă, selectați Detaliere pas cu pas pentru a afișa cealaltă formulă în caseta Evaluare . Faceți clic pe Ieșire din pentru a reveni la celula anterioară și la formulă.

   Butonul Detaliere pas cu pas nu este disponibil pentru o referință a doua oară când aceasta apare în formulă sau dacă formula face referire la o celulă din alt registru de lucru.

4. Continuați să selectați Evaluare până când fiecare parte a formulei a fost evaluată.

5. Pentru a vedea din nou evaluarea, selectați Repornire.

6. Pentru a încheia evaluarea, selectați Închidere.