Cum să evitați formulele eronate

Formulele cu mai multe argumente utilizează separatori de listă pentru a separa argumentele. Separatorul utilizat poate varia în funcție de setările regionale și Excel ale sistemului de operare. Separatorii de listă cei mai comuni sunt virgulă „,” și punct și virgulă „;”.

O formulă se va întrerupe dacă oricare dintre funcțiile sale utilizează delimitatori inutilizați.

Pentru mai multe informații, consultați: Erori de formule atunci când separatorul de listă nu este setat corect 

Excel returnează o varietate de erori hash (#), cum ar fi #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? și #NULL, ntru a indica faptul că un element din formulă nu funcționează corect. și #NULL!, pentru a indica faptul că un element din formulă nu funcționează corect. eroarea este cauzată de formatări incorecte sau de tipuri de date neacceptate în argumente. Sau veți vedea #REF! Sau, dacă o formulă face referire la celule care au fost șterse sau înlocuite cu alte date, veți vedea eroarea #REF!. Instrucțiunile de depanare sunt diferite pentru fiecare eroare.

Consultați oricare dintre următoarele subiecte, în funcție de eroarea hash pe care o vedeți:

• Corectarea erorii #NUM! eroare

• Corectarea erorii #VALUE! eroare

• Corectarea unei erori #N/A

• Corectarea erorii #DIV/0! eroare

• Corectarea unei erori #REF! eroare

• Corectarea erorii #NAME? eroare

• Corectarea unei erori #NUL! eroare

De fiecare dată când deschideți o foaie de calcul ce conține formule care fac referire la valori din alte foi de calcul, vi se va solicita să actualizați referințele sau să le lăsați așa.

Excel afișează caseta de dialog de mai sus pentru a se asigura că formulele din foaia de calcul actuală indică întotdeauna spre valoarea actualizată, în cazul în care aceasta s-a modificat. Puteți să actualizați referințele sau să omiteți acest pas dacă nu doriți să le actualizați. Chiar dacă alegeți să nu actualizați referințele, puteți actualiza manual legăturile din foaia de calcul, ori de câte ori doriți.

Puteți dezactiva oricând apariția casetei de dialog la pornire. Pentru a face acest lucru, accesați Fișier > Opțiuni > Complex > General și debifați Se solicită actualizarea legăturilor automate.

Dacă formula nu afișează valoarea, urmați acești pași:

• Asigurați-vă că Excel este setat să afișeze formulele din foaia de calcul. Pentru a face acest lucru, faceți clic pe fila Formule, iar în grupul Audit de formule, faceți clic pe Afișarea formulelor.

  Sfat: Puteți utiliza și comanda rapidă de la tastatură Ctrl + ' (tasta de deasupra tastei Tab). Când faceți acest lucru, coloanele se vor extinde automat pentru a afișa formulele, dar nu vă faceți griji, ele se vor redimensiona când comutați înapoi la vizualizarea normală.

• Dacă nici pasul de mai sus nu rezolvă problema, este posibil ca respectiva celulă să fie formatată ca text. Puteți să faceți clic dreapta pe celulă și să selectați Formatare celule > General (sau Ctrl + 1), apoi să apăsați F2 > Enter pentru a modifica formatul.

• Dacă aveți o zonă mare de celule formatate ca text într-o coloană, puteți să selectați zona, să aplicați formatul de număr dorit și să accesați Date > Text în coloane > Terminare. Acest lucru va aplica formatarea la toate celulele selectate.

  

Când o formulă nu se calculează, trebuie să verificați dacă este activată calcularea automată în Excel. Formulele nu se vor calcula dacă este activată calcularea manuală. Urmați acești pași pentru a căuta Calcularea automată.

1. Faceți clic fila Fișier, pe Opțiuni, apoi faceți clic pe categoria Formule.

2. În secțiunea Opțiuni de calcul, sub Calculare registru de lucru, asigurați-vă că opțiunea Automată este selectată.

   

Pentru mai multe informații despre calcule, consultați Modificarea recalculării, iterării sau preciziei formulelor.

O referință circulară apare când o formulă face referire la celula în care se află. Soluția este fie să mutați formula în altă celulă, fie să modificați sintaxa formulei astfel încât să evite referințele circulare. Cu toate acestea, în unele situații puteți avea nevoie de referințe circulare, deoarece determină iterația funcțiilor: acestea se repetă până când este îndeplinită o anumită condiție numerică. În astfel de cazuri, va trebui să activați Eliminare sau să permiteți o referință circulară.

Pentru mai multe informații despre referințele circulare, consultați Eliminarea sau permiterea unei referințe circulare.

Dacă intrarea nu începe cu semnul egal, nu este o formulă și nu va fi calculată - o greșeală comună.

Atunci când tastați ceva de genul SUM(A1:A10), Excel arată șirul de text SUM(A1:A10) în locul rezultatului unei formule. Alternativ, dacă tastați 11/2, Excel afișează o dată, cum ar fi 2 noi. sau 02.11.2009, în loc să împartă 11 la 2.

Pentru a evita aceste rezultate neașteptate, întotdeauna începeți funcția cu semnul egal. De exemplu, tastați: =SUM(A1:A10) și =11/2.

Când utilizați o funcție într-o formulă, fiecare paranteză de deschidere necesită o paranteză de închidere pentru ca funcția să se comporte corect. Asigurați-vă că toate parantezele au pereche. De exemplu, formula =IF(B5<0)"Nu este valid";B5*1,05) nu va funcționa, deoarece există două paranteze de închidere și o singură paranteză de deschidere. Formula corectă este: =IF(B5<0;"Nu este valid";B5*1,05).

Funcțiile Excel au argumente - valori pe care trebuie să le furnizați pentru ca funcția să ruleze. Doar câteva funcții (cum ar fi PI sau TODAY) nu au niciun argument. Verificați sintaxa formulei care apare atunci când începeți tastarea funcției pentru a vă asigura aceasta are argumentele necesare.

De exemplu, funcția UPPER acceptă un singur șir text sau o singură referință de celulă ca argument: =UPPER("salut") sau =UPPER(C2)

De asemenea, unele funcții, cum ar fi SUM, au nevoie doar de argumente numerice, în timp ce alte funcții, cum ar fi REPLACE, necesită o valoare text pentru cel puțin unul dintre argumente. Dacă utilizați tipul greșit de date, unele funcții ar putea să returneze rezultate neașteptate sau să afișeze o eroare #VALUE!.

Dacă aveți nevoie să căutați rapid sintaxa unei anumite funcții, consultați lista de Funcții Excel (după categorie).

Nu introduceți numere formatate cu semne dolar ($) sau separatori zecimali (,) în formule, deoarece semnele dolar indică referințe absolute, iar virgulele sunt separatori de argumente. În loc de a introduce $1.000, introduceți 1000 în formulă.

Dacă utilizați numere formatate în argumente, veți obține rezultate neașteptate de calcul, dar, de asemenea, puteți vedea eroarea #NUM!. De exemplu, dacă introduceți formula =ABS(-2,134) pentru a găsi valoarea absolută a lui -2134, Excel afișează #NUM! deoarece funcția ABS acceptă un singur argument și vede -2 și 134 ca argumente separate.

Este posibil ca formula dvs. să nu returneze rezultatele așteptate dacă tipul de date al celulei nu poate fi utilizat în calcule. De exemplu, dacă introduceți o formulă simplă =2+3 într-o celulă care este formatată ca text, Excel nu poate calcula datele introduse. Tot ce veți vedea în celulă este =2+3. Pentru a rezolva această problemă, modificați tipul de date al celulei de la Text la General, astfel:

1. Selectați celula.

2. Selectați Pornire și selectați săgeata pentru a extinde grupul Număr sau Format de număr (sau apăsați Ctrl + 1). Apoi selectați General.

3. Apăsați F2 pentru a pune celula în modul de editare, apoi apăsați Enter pentru a accepta formula.

O dată calendaristică introdusă într-o celulă care utilizează tipul de date Număr ar putea fi afișată ca valoare de date numerice în loc de dată. Pentru a afișa acest număr ca dată, alegeți un format Dată din galeria Format de număr.

E un lucru comun să utilizați x ca operator de înmulțire într-o formulă, dar Excel poate accepta doar asteriscul (*). Dacă utilizați constante în formulă, Excel afișează un mesaj de eroare și poate remedia formula, înlocuind x cu simbolul asterisc (*).

Cu toate acestea, dacă utilizați referințe de celule, Excel va returna o eroare #NAME?. eroare.

Dacă creați o formulă care include text, încadrați textul între ghilimele.

De exemplu, formula = "Astăzi este " & TEXT((TODAY), "dddd, dd mmmm") combină textul "Astăzi este " cu rezultatele funcțiilor TEXT și TODAY și returnează ceva similar cu Astăzi este luni, 30 mai.

În formulă, "Astăzi este " are un spațiu înainte de ghilimelele de final pentru a asigura spațiul pe care îl doriți între cuvintele „Astăzi este” și „Luni, 30 mai”. Fără ghilimele în jurul textului, formula ar putea afișa eroarea #NAME?. Fără ghilimele în jurul textului, formula poate afișa eroarea #NAME?.

Puteți combina (sau imbrica) cel mult 64 de niveluri de funcții într-o formulă.

De exemplu, formula =IF(SQRT(PI())<2, "Mai mic decât doi!", "Mai mare decât doi!") are 3 niveluri de funcții: Funcția PI este imbricată în funcția SQRT, care, la rândul său, este imbricată în funcția IF.

Atunci când tastați o referință la valori sau celule din altă foaie de lucru și numele acelei foi are un caracter nealfabetic (cum ar fi un spațiu), încadrați numele între ghilimele simple (').

De exemplu, pentru a returna valoarea din celula D3 dintr-o foaie de lucru denumită Date trimestriale din registrul de lucru, tastați: ='Date Trimestriale'!D3. Fără ghilimele în jurul numelui foii, formula afișează eroarea #NAME?.

De asemenea, puteți face clic pe valorile sau celulele din altă foaie pentru a face referire la acestea în formulă. Excel adaugă apoi automat ghilimele în jurul numelor foii.

Atunci când tastați o referință la valori sau celule din alt registru de lucru, includeți numele registrului de lucru încadrat între paranteze drepte ([]), urmat de numele foii de lucru care conține valorile sau celulele.

De exemplu, pentru a face referire la celulele A1 - A8 din foaia Vânzări din registrul de lucru Operațiuni T2 deschis în Excel, tastați: =[Operațiuni T2.xlsx]Vânzări!A1:A8. Fără paranteze drepte, formula afișează eroarea #REF!.

Dacă registrul de lucru nu este deschis în Excel, tastați calea completă a fișierului.

De exemplu, =ROWS('C:\Documentele mele\[Operațiuni T2.xlsx]Vânzări'!A1:A8).

Împărțirea unei celule la o altă celulă cu rezultatul zero (0) sau fără valoare duce la o eroare #DIV/0!..

Pentru a evita această eroare, puteți să vă ocupați direct de ea și să testați dacă numitorul există. Puteți utiliza: 

=IF(B1,A1/B1,0)

Care spune că dacă B1 există, se împarte A1 la B1, altfel se returnează 0).

Verificați întotdeauna dacă aveți formule care fac referire la date din celule, la zone, nume definite, foi de lucru sau registre de lucru, înainte să ștergeți orice. Apoi puteți să înlocuiți aceste formule cu rezultatele lor înainte de a elimina datele la care fac referință.

Dacă nu puteți înlocui formulele cu rezultatele lor, revizuiți aceste informații despre erori și soluțiile posibile:

• Dacă o formulă se referă la celule care au fost șterse sau înlocuite cu alte date și dacă returnează o eroare #REF!, selectați celula cu #REF! eroare. În bara de formule, selectați #REF! și ștergeți-l. Apoi introduceți din nou zona pentru formulă.

• Dacă lipsește un nume definit și o formulă care face referire la acel nume returnează o eroare #NAME?, definiți un nume nou care face referire la zona dorită sau modificați formula pentru a face referire direct la zona de celule (de exemplu, A2:D8).

• Dacă lipsește o foaie de lucru și o formulă care face referire la aceasta returnează eroarea #REF!, nu există nicio modalitate de a remedia acest lucru, din păcate. O foaie de lucru ștearsă nu poate fi recuperată.

• Dacă lipsește un registru de lucru, o formulă care face referire la acesta rămâne intactă până la actualizarea formulei.

  De exemplu, dacă formula dvs. este =[Registru1.xlsx]Foaie1'!A1 și nu mai aveți Book1.xlsx, valorile la care se face referire în acel registru de lucru rămân disponibile. Totuși, dacă editați și salvați o formulă care face referire la acel registru de lucru, Excel afișează caseta de dialog Actualizarea valorilor și vă solicită să introduceți un nume de fișier. Selectați Anulați, apoi asigurați-vă că aceste date nu se pierd, înlocuind formulele care fac referire la registrul de lucru lipsă cu rezultatele formulei.

Uneori, atunci când copiați conținutul unei celule, doriți să lipiți doar valoarea și nu formula subiacentă care se afișează în bară de formule.

De exemplu, poate doriți să copiați valoarea rezultată a unei formule într-o celulă din altă foaie de lucru. Sau poate doriți să ștergeți valorile utilizate într-o formulă după ce ați copiat valoarea rezultată în altă celulă din foaia de lucru. Ambele acțiuni provoacă o eroare de referință de celulă nevalidă (#REF!) să apară în celula de destinație, deoarece nu se mai poate face referire la celulele care conțin valorile utilizate în formulă.

Puteți evita această eroare lipind valorile rezultate ale formulelor fără formulă în celulele de destinație.

1. Într-o foaie de lucru, selectați celulele care conțin valorile rezultate ale unei formule pe care doriți să le copiați.

2. Pe fila Pornire, în grupul Clipboard, selectați Copiere .

   

   Comandă rapidă de la tastatură: Apăsați CTRL+C.

3. Selectați celula din stânga-sus din zonă de lipire.

   Sfat: Pentru a muta sau copia o selecție într-o altă foaie sau registru de lucru, faceți clic pe altă filă de foaie sau comutați la alt registru de lucru, apoi selectați celula din stânga sus a zonei de lipire.

4. Pe fila Pornire, în grupul Clipboard, faceți clic pe Lipire , apoi pe Lipirea valorilor, sau apăsați Alt > E > S > V > Enter pentru Windows ori Option > Command > V > V > Enter pe un Mac.

Pentru a înțelege modul în care o formulă complexă sau imbricată calculează rezultatul final, puteți să evaluați acea formulă.

1. Selectați formula pe care doriți să o evaluați.

2. Faceți clic pe Formule > Evaluarea formulei.

   

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

   

4. Dacă partea subliniată a formulei este o referință la o altă formulă, faceți clic pe 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 Detaliați pas cu pas nu este disponibil a doua oară când apare referința în formulă sau dacă formula se referă la o celulă din alt registru de lucru.

5. Continuați până când a fost evaluată fiecare parte a formulei.

   Instrumentul Evaluarea formulei nu vă va spune neapărat de ce este eronată formula, dar vă poate ajuta să vedeți unde este problema. Acesta poate fi un instrument foarte util în formule mai mari, unde altfel ar putea fi dificil de depistat.

   Note: 

   • Unele părți ale funcțiilor IF și CHOOSE nu vor fi evaluate, iar eroarea #N/A ar putea apărea în caseta Evaluare.

   • Referințele necompletate sunt afișate ca valori zero (0) în caseta Evaluare.

   • Unele funcții sunt recalculate de fiecare dată când se modifică foaia de lucru. Aceste funcții, inclusiv funcțiile RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW, TODAY și RANDBETWEEN pot face ca în caseta de dialog Evaluare formulă să se afișeze rezultate diferite de rezultatele reale din celula din foaia de lucru.