Formulele Excel nu funcționează: motive posibile și cum să o remediați!

Dacă lucrați cu formule în Excel, mai devreme sau mai târziu veți întâlni problema în care formulele Excel nu funcționează deloc (sau dau rezultatul greșit).

Deși ar fi fost grozav dacă ar fi existat doar câteva motive posibile pentru funcționarea defectuoasă a formulelor. Din păcate, există prea multe lucruri care pot merge prost (și de multe ori se întâmplă).

Dar, din moment ce trăim într-o lume care respectă principiul Pareto, dacă verificați anumite probleme comune, este posibil să rezolve 80% (sau poate chiar 90% sau 95% din problemele în care formulele nu funcționează în Excel).

Și în acest articol, voi evidenția acele probleme comune care sunt probabil cauza problemei dvs. Formulele Excel nu funcționează.

Asadar, haideti sa începem!

Sintaxa incorectă a funcției

Permiteți-mi să încep prin a arăta evidentul.

Fiecare funcție din Excel are o sintaxă specifică - cum ar fi numărul de argumente pe care le poate lua sau tipul de argumente pe care le poate accepta.

Și, în multe cazuri, motivul pentru care formulele dvs. Excel nu funcționează sau dau un rezultat greșit ar putea fi un argument incorect (sau lipsă de argumente).

De exemplu, funcția VLOOKUP ia trei argumente obligatorii și un argument opțional.

Dacă furnizați un argument greșit sau nu specificați argumentul opțional (unde este necesar pentru ca formula să funcționeze), acesta vă va da un rezultat greșit.

De exemplu, să presupunem că aveți un set de date așa cum se arată mai jos, în care trebuie să cunoașteți scorul Mark în examenul 2 (în celula F2).

Dacă folosesc formula de mai jos, voi obține rezultatul greșit, deoarece folosesc valoarea greșită în al treilea argument (unul care cere numărul indexului coloanei).

= VLOOKUP (A2, A2: C6,2, FALSE)

În acest caz, formula se calculează (întrucât returnează o valoare), dar rezultatul este incorect (în loc de scor la examenul 2, dă scorul la examenul 1).

Un alt exemplu în care trebuie să fii precaut cu privire la argumente este atunci când folosești VLOOKUP cu potrivirea aproximativă.

Deoarece trebuie să utilizați un argument opțional pentru a specifica unde doriți ca VLOOKUP să facă o potrivire exactă sau o potrivire aproximativă, nespecificarea acestui lucru (sau utilizarea unui argument greșit) poate cauza probleme.

Mai jos este un exemplu în care am datele notelor pentru unii studenți și vreau să extrag notele din examenul 1 pentru elevii din tabelul din dreapta.

Când folosesc formula VLOOKUP de mai jos, îmi dă o eroare pentru unele nume.

= VLOOKUP (E2, $ A $ 2: $ C $ 6,2)

Acest lucru se întâmplă deoarece nu am specificat ultimul argument (care este folosit pentru a determina dacă să fac o potrivire exactă sau o potrivire aproximativă). Când nu specificați ultimul argument, acesta face automat o potrivire aproximativă în mod implicit.

Întrucât a trebuit să facem o potrivire exactă în acest caz, formula returnează o eroare pentru unele nume.

Deși am luat exemplul funcției VLOOKUP, în acest caz, acest lucru se poate aplica pentru multe formule Excel care au și argumente opționale.

De asemenea, citiți: Identificați erorile folosind depanarea formulelor Excel

Spații suplimentare care provoacă rezultate neașteptate

Spațiile de frunte, de ultimă oră, sunt greu de aflat și pot cauza probleme atunci când utilizați o celulă care le are în formule.

De exemplu, în exemplul de mai jos, dacă încerc să folosesc VLOOKUP pentru a obține scorul pentru Mark, îmi dă eroarea # N / A (eroarea nedisponibilă).

Deși pot vedea că formula este corectă și numele „Mark” este clar, există lista, ceea ce nu pot vedea este că există un spațiu final în celulă care are numele (în celula D2).

Excel nu consideră conținutul acestor două celule la fel și, prin urmare, consideră că este o nepotrivire atunci când preia valoarea folosind VLOOKUP (sau ar putea fi orice altă formulă de căutare).

Pentru a remedia această problemă, trebuie să eliminați aceste caractere de spațiu suplimentar.

Puteți face acest lucru utilizând oricare dintre următoarele metode:

1. Curățați celula și îndepărtați orice spațiu principal / final înainte de ao utiliza în formule
2. Utilizați funcția TRIM din formulă pentru a vă asigura că orice spațiu principal / final / dublu este ignorat.

În exemplul de mai sus, puteți folosi formula de mai jos pentru a vă asigura că funcționează.

= VLOOKUP (TRIM (D2), $ A $ 2: $ B $ 6,2,0)

Deși am luat exemplul VLOOKUP, aceasta este, de asemenea, o problemă obișnuită atunci când lucrează cu funcții TEXT.

De exemplu, dacă folosesc funcția LEN pentru a număra numărul total de caractere dintr-o celulă, dacă există spații principale sau finale, acestea ar fi, de asemenea, numărate și ar da rezultatul greșit.

Take away / How to Fix: Dacă este posibil, curățați-vă datele eliminând orice spațiu principal / final sau dublu înainte de a le utiliza în formule. Dacă nu puteți schimba datele originale, utilizați funcția TRIM în formule pentru a avea grijă de acest lucru.

Utilizarea calculului manual în loc de automat

Această setare vă poate înnebuni (dacă nu știți, este ceea ce cauzează toate problemele).

Excel are două moduri de calcul - Automat și Manual.

În mod implicit, modul automat este activat, ceea ce înseamnă că, în cazul în care folosesc o formulă sau fac modificări în formulele existente, face automat (și instantaneu) calculul și îmi dă rezultatul.

Aceasta este setarea cu care suntem obișnuiți cu toții.

În setarea automată, ori de câte ori faceți orice modificare în foaia de lucru (cum ar fi introducerea unei noi formule chiar și pentru un text dintr-o celulă), Excel recalculează automat totul (da, Tot).

Dar, în unele cazuri, oamenii activează setarea de calcul manual.

Acest lucru se face mai ales atunci când aveți un fișier Excel greu, cu o mulțime de date și formule. În astfel de cazuri, este posibil să nu doriți ca Excel să recalculeze totul atunci când efectuați mici modificări (deoarece poate dura câteva secunde sau chiar minute) pentru finalizarea acestui recalcul.

Dacă activați calculul manual, Excel nu va calcula decât dacă îl forțați.

Și acest lucru vă poate face să credeți că formula dvs. nu calculează.

Tot ce trebuie să faceți în acest caz este fie să setați calculul înapoi la automat, fie să forțați un recalcul prin apăsarea tastei F9.

Mai jos sunt pașii pentru a schimba calculul din manual în automat:

1. Faceți clic pe fila Formula
2. Faceți clic pe Opțiuni de calcul
3. Selectați Automat

Important: în cazul în care schimbați calculul de la manual la automat, este o idee bună să creați o copie de rezervă a registrului de lucru (doar în cazul în care acest lucru face ca registrul de lucru să se blocheze sau să facă Excel să se blocheze)

Take away / How to Fix: Dacă observați că formulele dvs. nu dau rezultatul scontat, încercați ceva simplu în orice celulă (cum ar fi adăugarea 1 la o formulă existentă. Odată ce identificați problema ca cea în care modul de calcul trebuie schimbat, efectuați un calcul de forță prin utilizarea F9.

Ștergerea rândurilor / coloanei / celulelor care duc la #REF! Eroare

Unul dintre lucrurile care pot avea un efect devastator asupra formulelor existente în Excel este atunci când ștergeți orice rând / coloană care a fost utilizată în calcule.

Când se întâmplă acest lucru, uneori, Excel ajustează referința în sine și se asigură că formulele funcționează bine.

Si cateodata… nu poate.

Din fericire, o indicație clară pe care o obțineți atunci când formulele se întrerup la ștergerea celulelor / rândurilor / coloanelor este #REF! eroare în celule. Aceasta este o eroare de referință care vă spune că există o problemă cu referințele din formulă.

Permiteți-mi să vă arăt ce vreau să spun folosind un exemplu.

Mai jos am folosit formula SUMA pentru a adăuga celulele A2: A6.

Acum, dacă șterg oricare dintre aceste celule / rânduri, formula SUM va returna un #REF! eroare. Acest lucru se întâmplă deoarece atunci când am șters rândul, formula nu știe ce să facem referire acum.

Puteți vedea că al treilea argument din formulă a devenit #REF! (care se referea anterior la celula pe care am șters-o).

Take away / How to Fix: Înainte de a șterge orice date care sunt utilizate în formule, asigurați-vă că nu există erori după ștergere. De asemenea, vă recomandăm să creați în mod regulat o copie de rezervă a muncii dvs. pentru a vă asigura că aveți întotdeauna ceva de care să vă întoarceți.

Plasarea incorectă a parantezei (BODMAS)

Pe măsură ce formulele dvs. încep să devină mai mari și mai complexe, este o idee bună să folosiți paranteze pentru a fi absolut clar ce parte aparține împreună.

În unele cazuri, este posibil să aveți o paranteză într-un loc greșit, ceea ce vă poate oferi fie un rezultat greșit, fie o eroare.

Și, în unele cazuri, este recomandat să folosiți paranteze pentru a vă asigura că formula înțelege ceea ce trebuie grupat și calculat mai întâi.

De exemplu, să presupunem că aveți următoarea formulă:

=5+10*50

În formula de mai sus, rezultatul este 505, deoarece Excel face mai întâi multiplicarea și apoi adăugarea (deoarece există o ordine de prioritate când vine vorba de operatori).

Dacă doriți să facă mai întâi adunarea și apoi multiplicarea, trebuie să utilizați formula de mai jos:

=(5+10)*50

În unele cazuri, ordinea de prioritate poate funcționa pentru dvs., dar este totuși recomandat să utilizați paranteze pentru a evita orice confuzie.

De asemenea, în cazul în care sunteți interesat, mai jos este ordinea de prioritate pentru diferiți operatori folosiți adesea în formule:

Operator	Descriere	Ordinea Precedenței
: (colon)	Gamă	1
(spațiu unic)	Intersecție	2
, (virgulă)	uniune	3
-	Negare (ca la -1)	4
%	Procent	5
^	Exponențierea	6
* și /	Înmulțirea și divizarea	7
+ și -	Adunare și scădere	8
&	Concatenare	9
= =	Comparaţie	10

Take away / How to Fix: Folosiți întotdeauna paranteze pentru a evita orice confuzie, chiar dacă cunoașteți ordinea priorității și o utilizați corect. Dacă aveți paranteză, este mai ușor să auditați formulele.

Utilizarea incorectă a referințelor celulare absolute / relative

Când copiați și lipiți formule în Excel, acesta ajustează automat referințele. Uneori, este exact ceea ce doriți (mai ales atunci când copiați formule pe coloană) și uneori nu doriți să se întâmple acest lucru.

O referință absolută este atunci când fixați o referință de celulă (sau o referință de interval), astfel încât să nu se schimbe atunci când copiați și lipiți formule, iar o referință relativă este una care se modifică.

Puteți citi mai multe despre referințe absolute, relative și mixte aici.

Este posibil să obțineți un rezultat incorect în cazul în care uitați să schimbați referința la unul absolut (sau invers). Acest lucru mi se întâmplă destul de des când folosesc formule de căutare.

Permiteți-mi să vă arăt un exemplu.

Mai jos am un set de date în care vreau să obțin scorul la examenul 1 pentru numele din coloana E (un caz simplu de utilizare VLOOKUP)

Mai jos este formula pe care o folosesc în celula F2 și apoi copiez în toate celulele de sub ea:

= VLOOKUP (E2, A2: B6,2,0)

După cum puteți vedea, această formulă dă o eroare în unele cazuri.

Acest lucru se întâmplă pentru că nu am blocat argumentul matricei de tabele - este A2: B6 în celula F2, în timp ce ar fi trebuit să fie $ A $ 2: $ B $ 6 $

Având aceste semne de dolar înainte de numărul rândului și alfabetul coloanei într-o referință de celulă, forțez Excel să păstreze aceste referințe de celulă fixe. Deci, chiar și atunci când copiez această formulă în jos, tabloul de tabele va continua să se refere la A2: B6

Sfat Pro: Pentru a converti o referință relativă la una absolută, selectați acea referință de celulă din celulă și apăsați tasta F4. Ați observa că se schimbă prin adăugarea semnelor de dolar. Puteți continua să apăsați F4 până când obțineți referința dorită.

Referință incorectă la numele foilor / registrului de lucru

Când vă referiți la alte foi sau registre de lucru într-o formulă, trebuie să urmați un anumit format. Și în cazul în care formatul este incorect, veți primi o eroare.

De exemplu, dacă vreau să mă refer la celula A1 din Sheet2, referința ar fi = Sheet2! A1 (unde există un semn de exclamare după numele foii)

Și în cazul în care există mai multe cuvinte în numele foii (să presupunem că este un exemplu de date), referința ar fi = „Date de exemplu”! A1 (unde numele este inclus între ghilimele unice urmate de un semn de exclamare).

În cazul în care vă referiți la un registru de lucru extern (să presupunem că vă referiți la celula A1 din „Foaie de exemplu” din registrul de lucru numit „Exemplu de registru de lucru”), referința va fi după cum se arată mai jos:

= '[Exemplu de carte de lucru.xlsx] Foaie de exemplu'! $ A $ 1

Și în cazul în care închideți registrul de lucru, referința se va modifica pentru a include întreaga cale a registrului de lucru (așa cum se arată mai jos):

= 'C: \ Users \ sumit \ Desktop \ [Example Workbook.xlsx] Foaie de exemplu'! $ A $ 1

În cazul în care ajungeți să schimbați numele registrului de lucru sau al foii de lucru la care se referă formula, vă va oferi un #REF! eroare.

De asemenea, citiți: Cum să găsiți legături externe și referințe în Excel

Referințe circulare

O referință circulară este atunci când vă referiți (direct sau indirect) la aceeași celulă în care se calculează formula.

Mai jos este un exemplu simplu, unde folosesc formula SUM în celula A4 în timp ce o folosesc în calculul în sine.

= SUMĂ (A1: A4)

Deși Excel vă arată un prompt care vă informează despre referința circulară, nu o va face pentru fiecare instanță. Și acest lucru vă poate da rezultatul greșit (fără niciun avertisment).

În cazul în care suspectați o referință circulară în joc, puteți verifica ce celule o au.

Pentru a face acest lucru, faceți clic pe fila Formula și, în grupul „Formula Auditing”, faceți clic pe pictograma derulantă Verificare erori (săgeata mică orientată în jos).

Plasați cursorul peste opțiunea de referință circulară și vă va arăta celula care are problema referinței circulare.

Celule formatate ca text

Dacă vă aflați într-o situație în care, de îndată ce introduceți formula ca apăsat pe Enter, vedeți formula în loc de valoare, este un caz clar al celulei formatate ca text.

Când o celulă este formatată ca text, consideră formula ca un șir de text și o afișează așa cum este.

Nu-l obligă să calculeze și să dea rezultatul.

Și are o soluție ușoară.

1. Schimbați formatul la „General” din „Text” (se află în fila Acasă din grupul Numere)
2. Mergeți la celula care are formula, intrați în modul de editare (utilizați F2 sau faceți dublu clic pe celulă) și apăsați Enter

În cazul în care pașii de mai sus nu rezolvă problema, un alt lucru de verificat este dacă celula are apostrof la început. Mulți oameni adaugă un apostrof pentru a converti formule și numere în text.

Dacă există un apostrof, îl puteți elimina pur și simplu.

Textul se transformă automat în date

Excel are acest obicei prost de a converti care arată ca o dată într-o dată reală. De exemplu, dacă introduceți 1/1, Excel îl va converti în 01 ianuarie al anului curent.

În unele cazuri, acest lucru poate fi exact ceea ce doriți, iar în unele cazuri, acest lucru poate funcționa împotriva dvs.

Și din moment ce Excel stochează valorile datei și orei ca numere, imediat ce introduceți 1/1, îl convertește într-un număr reprezentând 1 ianuarie al anului curent. În cazul meu, când fac acest lucru, îl convertește în numărul 43831 (pentru 01-01-2020).

Acest lucru ar putea face probleme cu formulele dvs. dacă utilizați aceste celule ca argument într-o formulă.

Cum se remediază acest lucru?

Din nou, nu vrem ca Excel să aleagă automat formatul pentru noi, așa că trebuie să specificăm noi înșine formatul.

Mai jos sunt pașii pentru a schimba formatul în text, astfel încât acesta să nu convertească automat textul în date:

1. Selectați celulele / intervalul în care doriți să modificați formatul
2. Faceți clic pe fila Acasă
3. În grupul Număr, faceți clic pe meniul derulant Format
4. Faceți clic pe Text

Acum, ori de câte ori introduceți ceva în celulele selectate, acesta va fi considerat text și nu va fi modificat automat.

Notă: Pașii de mai sus ar funcționa numai pentru datele introduse după modificarea formatării. Nu va modifica niciun text care a fost convertit la data înainte de a efectua această modificare de formatare.

Un alt exemplu în care acest lucru poate fi cu adevărat frustrant este atunci când introduceți un text / număr care are zerouri din partea de sus. Excel elimină automat aceste zerouri principale, deoarece le consideră inutile. De exemplu, dacă introduceți 0001 într-o celulă, Excel îl va schimba la 1. În cazul în care doriți să păstrați aceste zerouri de început, utilizați pașii de mai sus.

Rândurile / coloanele ascunse pot da rezultate neașteptate

Acesta nu este un caz de formulă care să vă dea un rezultat greșit, ci de a utiliza o formulă greșită.

De exemplu, să presupunem că aveți un set de date așa cum se arată mai jos și vreau să obțin suma tuturor celulelor vizibile din coloana C.

În celula C12, am folosit funcția SUMA pentru a obține valoarea totală de vânzare pentru toate aceste înregistrări date.

Până acum, bine!

Acum, aplic un filtru coloanei articolului pentru a afișa numai înregistrările pentru vânzările de imprimante.

Și aici este problema - formula din celula C12 arată încă același rezultat - adică suma tuturor înregistrărilor.

După cum am spus, formula nu dă rezultate greșite. De fapt, funcția SUM funcționează foarte bine.

Problema este că am folosit formula greșită aici.

Funcția SUM nu poate ține cont de datele filtrate și vă oferă rezultatul pentru toate celulele (ascunse sau vizibile). Dacă doriți doar să obțineți suma / numărul / media celulelor vizibile, utilizați funcțiile SUBTOTAL sau AGREGATE.

Cheie de luat masa - Înțelegeți utilizarea corectă și limitările unei funcții în Excel.

Acestea sunt câteva dintre cauzele comune pe care le-am văzut unde vă aflați Este posibil ca formulele Excel să nu funcționeze sau să dea rezultate neașteptate sau greșite. Sunt sigur că există multe alte motive pentru care o formulă Excel nu funcționează sau nu se actualizează.

În cazul în care cunoașteți orice alt motiv (poate ceva care vă enervează des), anunțați-mă în secțiunea de comentarii.

Sper că ați găsit util acest tutorial!