Una dintre cele mai solicitate caracteristici ale Excel este lucrul cu formule. Datorită acestei funcții, programul efectuează în mod independent diferite tipuri de calcule în tabele. Dar uneori se întâmplă ca utilizatorul să introducă o formulă într-o celulă, dar aceasta nu își îndeplinește scopul propus - calcularea rezultatului. Să vedem cu ce se poate conecta acest lucru și cum să rezolvăm această problemă.

Cauzele problemelor cu calculul formulelor în Excel pot fi complet diferite. Acestea pot fi cauzate atât de setările unei anumite cărți sau chiar de o gamă separată de celule, fie de diverse erori de sintaxă.

Metoda 1: Modificarea formatului celulelor

Unul dintre cele mai comune motive pentru care Excel nu numără formulele deloc sau nu numără formulele corect este un format de celule setat incorect. Dacă intervalul are un format text, atunci expresiile din acesta nu sunt calculate deloc, adică sunt afișate ca text simplu. În alte cazuri, dacă formatul nu se potrivește cu esența datelor calculate, este posibil ca rezultatul afișat în celulă să nu fie afișat corect. Să aflăm cum să rezolvăm această problemă.

Acum formula va fi calculată în ordinea standard cu rezultatul în celula specificată.

Metoda 2: Dezactivează modul „Afișează formule”.

Dar poate motivul pentru care în loc de rezultatele calculului aveți expresii este că programul are modul „Afișați formulele”.

Metoda 3: Remedierea unei erori de sintaxă

O formulă poate fi afișată și ca text dacă există erori în sintaxa ei, cum ar fi lipsa sau schimbarea unei litere. Dacă l-ai introdus manual, și nu prin Expertul de funcții, atunci acest lucru este foarte probabil. O eroare foarte frecventă asociată cu afișarea unei expresii ca text este prezența unui spațiu înaintea semnului «=» .

În astfel de cazuri, trebuie să revizuiți cu atenție sintaxa acelor formule care sunt afișate incorect și să le faceți ajustările corespunzătoare.

Metoda 4: Activați recalcularea formulei

Există, de asemenea, o astfel de situație încât formula pare să afișeze valoarea, dar atunci când celulele asociate cu ea se modifică, aceasta nu se schimbă singură, adică rezultatul nu este recalculat. Aceasta înseamnă că opțiunile de calcul din acest registru de lucru nu sunt configurate corect.

Acum toate expresiile din acest registru de lucru vor fi recalculate automat atunci când orice valoare asociată se schimbă.

Metoda 5: o eroare în formulă

Dacă programul încă efectuează calculul, dar ca rezultat arată o eroare, atunci este probabil ca utilizatorul să facă pur și simplu o greșeală la introducerea expresiei. Formulele incorecte sunt cele în calculul cărora apar următoarele valori în celulă:

• #NUMĂR!;
• #VALOARE!;
• #GOL!;
• #DIV/0!;
• #N / A.

În acest caz, trebuie să verificați dacă datele sunt scrise corect în celulele la care se face referire de expresie, dacă conțin erori de sintaxă sau dacă formula în sine conține vreo acțiune incorectă (de exemplu, împărțirea cu 0).

Dacă funcția este complexă, cu un număr mare de celule conectate, atunci este mai ușor să urmăriți calculele folosind un instrument special.

După cum puteți vedea, motivele pentru care Excel nu calculează sau nu calculează corect formulele pot fi complet diferite. Dacă, în loc să calculeze, utilizatorul afișează funcția în sine, atunci în acest caz, cel mai probabil, fie celula este formatată pentru text, fie modul de vizualizare a expresiei este activat. De asemenea, este posibilă o eroare de sintaxă (de exemplu, prezența unui spațiu înaintea semnului «=» ). Dacă după modificarea datelor din celulele aferente rezultatul nu este actualizat, atunci trebuie să vă uitați la modul în care este configurată actualizarea automată în setările cărții. De asemenea, destul de des, în loc de rezultatul corect, în celulă este afișată o eroare. Aici trebuie să vedeți toate valorile la care se referă funcția. Dacă se găsește o eroare, aceasta ar trebui corectată.

Pentru calculele eronate, formulele afișează mai multe tipuri de erori în loc de valori. Să le luăm în considerare pe exemple practice în procesul de lucru a formulelor care au dat rezultate de calcul eronate.

Erori de formulă Excel afișate în celule

Această lecție va descrie valorile de eroare ale formulelor pe care celulele le pot conține. Cunoscând semnificația fiecărui cod (de exemplu: #VALOARE!, #DIV/0!, #NUMĂR!, #N/A!, #NUME!, #GOL!, #REFERINȚĂ!) vă puteți da seama cu ușurință cum să găsiți o eroare în formulă și elimină-o.

Cum să eliminați #DIV/0 în Excel

După cum puteți vedea, la împărțirea la o celulă cu o valoare goală, programul o percepe ca împărțire cu 0. Ca urmare, dă valoarea: #DIV/0! Acest lucru poate fi verificat cu ajutorul unui indiciu.

În alte calcule aritmetice (înmulțire, însumare, scădere), o celulă goală este, de asemenea, o valoare zero.

Rezultatul calculului eronat este #NUMĂR!

Număr greșit: #NUMĂR! este o eroare de imposibilitate de a executa calculul în formulă.

Câteva exemple practice:

Eroare: #NUMĂR! apare atunci când o valoare numerică este prea mare sau prea mică. De asemenea, această eroare poate apărea atunci când încercați să obțineți rădăcina unui număr negativ. De exemplu, =ROOT(-25).

În celula A1, numărul este prea mare (10^1000). Excel nu poate funcționa cu numere atât de mari.

Celula A2 are aceeași problemă cu numerele mari. S-ar părea că 1000 este un număr mic, dar când îi returnați factorialul, se dovedește a fi o valoare numerică prea mare pe care Excel nu o poate gestiona.

În celula A3, rădăcina pătrată nu poate fi dintr-un număr negativ, iar programul a afișat acest rezultat cu aceeași eroare.

Cum să eliminați ND în Excel

Valoarea nu este disponibilă: #N/A! - înseamnă că valoarea nu este disponibilă pentru formula:

Formula scrisă în B1: =MATCH(„Maxim”, A1:A4) caută conținutul text „Maxim” în intervalul de celule A1:A4. Conținut găsit în a doua celulă A2. Prin urmare, funcția returnează rezultatul 2. A doua formulă caută conținutul text „Andrey”, apoi intervalul A1:A4 nu conține astfel de valori. Prin urmare, funcția returnează o eroare #N/A (fără date).

Eroare #NAME! în Excel

Fii clasificat ca o greșeală în funcțiile de scriere. Nume nevalid: #NAME! - înseamnă că Excel nu a recunoscut textul scris în formulă (numele funcției = SUM () îi este necunoscut, este scris cu o eroare). Acesta este rezultatul unei erori de sintaxă la scrierea numelui funcției. De exemplu:

Eroare #BLANK! în Excel

Set gol: #GOL! sunt erorile operatorului de intersecție setat. În Excel, există un astfel de lucru precum intersecția mulțimilor. Este folosit pentru a prelua rapid date din tabele mari prin interogarea punctului de intersecție al unui interval vertical și orizontal de celule. Dacă intervalele nu se intersectează, programul afișează o valoare eronată - #GOL! Operatorul de intersecție set este un singur spațiu. Separă intervalele verticale și orizontale specificate în argumentele funcției.

În acest caz, intersecția intervalelor este celula C3 și funcția își afișează valoarea.

Argumentele date în funcție: =SUM(B4:D4 B2:B3) – nu formează o intersecție. Prin urmare, funcția dă o valoare cu o eroare - #EMPTY!

#LEGĂTURĂ! - Eroare de referință a celulei Excel

În acest exemplu, a apărut o eroare când formula a fost copiată incorect. Avem 3 intervale de celule: A1:A3, B1:B4, C1:C2.

Sub primul interval, în celula A4, introduceți formula de însumare: =SUM(A1:A3). Și apoi copiați aceeași formulă sub al doilea interval, în celula B5. Formula, ca și mai înainte, însumează doar 3 celule B2:B4, ocolind valoarea primului B1.

Când aceeași formulă a fost copiată sub al treilea interval, funcția a returnat eroarea #REF! în celula C3. Deoarece pot exista doar 2 celule deasupra celulei C3 și nu 3 (așa cum este cerut de formula originală).

Notă. În acest caz, cel mai convenabil este să apăsați ALT + = combinația de taste rapide sub fiecare interval înainte de a începe să intrați. Apoi introduceți funcția de însumare și determinați automat numărul de celule de însumare.

Cum se remediază VALUE în Excel

#VALOARE! - o eroare de sens. Dacă încercăm să adăugăm un număr și un cuvânt în Excel, vom obține #VALOARE! Un fapt interesant este că dacă am încercat să adăugăm două celule în care valoarea primei este un număr și a doua este text folosind funcția =SUM(), atunci nu va apărea nicio eroare, iar textul va lua valoarea 0. când se calculează. De exemplu:

Grile într-o celulă Excel

Rând de bare în loc de valoarea celulei ###### - această valoare nu este o eroare. Sunt doar informații că lățimea coloanei este prea îngustă pentru a permite afișarea corectă a conținutului celulei. Trebuie doar să extindeți coloana. De exemplu, faceți dublu clic cu butonul stâng al mouse-ului pe marginea titlurilor coloanei unei anumite celule.

Deci, zăbrelele (######) în loc de valorile celulei pot fi văzute cu o dată negativă. De exemplu, încercăm să scădem data nouă din data veche. Și ca rezultat al calculului, formatul celulelor este „Dată” (și nu „General”).

Un format de celulă incorect poate afișa și o serie de semne hash (######) în loc de valori.

Puteți personaliza afișarea valorilor zero într-o celulă sau puteți utiliza un set de standarde de formatare într-un tabel care vă solicită să ascundeți valorile zero. Puteți afișa și ascunde valorile nule într-o varietate de moduri.

Ascunderea și afișarea tuturor valorilor nule într-o foaie de lucru

Selectați Fişier > Opțiuni > În plus.

Într-un grup

• Afișați zerouri în celulele care conțin valori zero.

Ascunderea valorilor nule în celulele selectate

Urmați acești pași pentru a ascunde valorile nule în celulele selectate folosind un format numeric. Dacă valoarea dintr-una dintre aceste celule se schimbă la o valoare diferită de zero, aceasta va fi afișată în celulă, iar formatul său va fi similar cu formatul general al numărului.

Afișați valorile ascunse.

Selectați celule cu valori zero ascunse.

Puteți apăsa tastele CTRL+1 sau pe filă Acasă clic Formatare > Formatare celule.

Pentru a aplica formatul de număr implicit, selectați Număr > Generalși apăsați butonul Bine.

Ascunderea valorilor nule returnate de o formulă

Pe fila Acasă Formatarea condiționalăși selectați Reguli de selecție a celulelor > Egal.

In campul din stanga intra 0 .

În câmpul din dreapta selectați Format personalizat.

În câmp Format de celule deschide fila Font.

Listată Culoare selectați culoarea albă și apăsați butonul Bine.

Afișați zerourile ca spații sau liniuțe

Pentru a rezolva această problemă, utilizați funcția IF.

Dacă celula conține valori nule, utilizați o formulă ca aceasta pentru a returna o celulă goală:

IF(A2-A3=0;"";A2-A3)

Iată cum să citiți formula. Dacă rezultatul calculului (A2-A3) este „0”, nu este afișat nimic, inclusiv „0” (acesta este indicat prin ghilimele duble „”). În caz contrar, este afișat rezultatul calculului A2-A3. Dacă trebuie să nu lăsați celulele goale, dar să afișați altceva decât „0”, introduceți o cratimă „-” sau un alt caracter între ghilimele duble.

Selectați un raport PivotTable.

Pe fila Analizăîntr-un grup masă rotativă Opțiuniși selectați elementul Opțiuni.

Accesați fila Marcare și format

• În câmp Format bifeaza casuta Pentru afisarea erorilor

  Caseta de bifat Pentru celulele goale, afișați

Necesitatea de a afișa valori zero (0) pe foi nu apare întotdeauna. Indiferent dacă standardele de formatare necesită ca dvs. sau propriile preferințe să afișați sau să ascundeți valorile nule, există mai multe moduri de a implementa toate aceste cerințe.

Selectați Fişier > Opțiuni > În plus.

Într-un grup Afișați opțiunile pentru foaia următoare selectați o foaie, apoi efectuați una dintre următoarele:

• Pentru a afișa valorile zero (0) în celule, bifați caseta de selectare Afișați zerouri în celulele care conțin valori zero.

  Pentru a afișa valorile nule ca celule goale, debifați Afișați zerouri în celulele care conțin valori zero.

Puteți apăsa tastele CTRL+1 sau pe filă Acasă clic Formatare > Formatare celule.

În câmp Tip introduce 0;-0;;@

Note:

Valorile ascunse apar numai în bara de formule sau într-o celulă dacă editați conținutul acesteia. Aceste valori nu sunt tipărite.

Pentru a afișa din nou valorile ascunse, selectați celulele, apoi apăsați tastele CTRL+1 sau pe filă Acasăîntr-un grup celule plasați cursorul peste element Formatși alegeți Format de celule. Pentru a aplica formatul numeric implicit, în listă Categorie Selectați General. Pentru a afișa din nou data și ora, selectați formatul potrivit pentru dată și oră din filă Număr.

Selectați celula care conține valoarea zero (0).

Pe fila Acasăîntr-un grup Stiluri faceți clic pe săgeata de lângă element Formatarea condițională, plasați cursorul peste element Reguli de selecție a celulelorși selectați o opțiune Egal.

In campul din stanga intra 0 .

În câmpul din dreapta selectați Format personalizat.

În caseta de dialog Format de celule deschide fila Font.

În câmp Culoare alege albul.

Pentru a îndeplini această sarcină, utilizați funcția DACĂ.

Exemplu

Copierea unui exemplu

Funcția IF.

Ascunderea valorilor nule într-un raport PivotTable

Pe fila Opțiuniîntr-un grup Opțiuni pentru tabel pivot faceți clic pe săgeata de lângă comandă Opțiuniși selectați elementul Opțiuni.

Accesați fila Marcare și format, apoi faceți următoarele:

Modificarea modului în care sunt afișate erorile.În câmp Format bifeaza casuta Pentru afisarea erorilor. Introduceți în câmp valoarea pe care doriți să o afișați în loc de erori. Pentru a afișa erorile ca celule goale, eliminați tot textul din câmp.

Modificați modul în care sunt afișate celulele goale. Caseta de bifat Pentru celulele goale, afișați. Introduceți în câmp valoarea pe care doriți să o afișați în celulele goale. Pentru a le păstra goale, eliminați tot textul din câmp. Pentru a afișa valori nule, debifați această casetă de validare.

Necesitatea de a afișa valori zero (0) pe foi nu apare întotdeauna. Indiferent dacă standardele de formatare necesită ca dvs. sau propriile preferințe să afișați sau să ascundeți valorile nule, există mai multe moduri de a implementa toate aceste cerințe.

Afișați sau ascundeți toate valorile zero într-o foaie de lucru

Ascunderea valorilor nule în celulele selectate folosind formatul numeric

Aceste acțiuni vă permit să ascundeți valorile nule în celulele selectate. Dacă valoarea dintr-una dintre celule devine diferită de zero, formatul acesteia va fi similar cu formatul general al numărului.

Selectați celulele care conțin valori nule (0) pe care doriți să le ascundeți.

Puteți apăsa tastele CTRL+1 sau pe filă Acasăîntr-un grup celule clic Formatare > Formatare celule.

În câmp Tip introduce 0;-0;;@

Note:

Ascunderea valorilor nule returnate de o formulă folosind formatarea condiționată

Selectați celula care conține valoarea zero (0).

Pe fila Acasăîntr-un grup Stiluri faceți clic pe săgeata de lângă buton Formatarea condiționalăși selectați Reguli de selecție a celulelor > Egal.

In campul din stanga intra 0 .

În câmpul din dreapta selectați Format personalizat.

În caseta de dialog Format de celule deschide fila Font.

În câmp Culoare alege albul.

Folosind o formulă pentru a afișa zerouri ca spații sau liniuțe

Pentru a îndeplini această sarcină, utilizați funcția IF.

Exemplu

Pentru a face acest exemplu mai ușor de înțeles, copiați-l pe o foaie goală.

Copierea unui exemplu

Evidențiați exemplul dat în acest articol.

Important: Nu evidențiați titlurile de rând sau de coloană.

Evidențiați un exemplu în ajutor

Apăsați CTRL+C.

În Excel, creați un registru de lucru sau o foaie goală.

Selectați celula A1 din foaia de lucru și apăsați CTRL+V.

Important: Pentru ca exemplul să funcționeze corect, acesta trebuie introdus în celula A1.

Pentru a comuta între vizualizarea rezultatelor și vizualizarea formulelor care returnează acele rezultate, apăsați CTRL+` (semn de accent) sau pe filă Formuleîn grupul Dependențe de formule, faceți clic Afișați formule.

Copiind exemplul pe o foaie goală, îl puteți personaliza așa cum doriți.

Pentru mai multe informații despre utilizarea acestei funcții, consultați articolul privind funcția IF.

Ascunderea valorilor nule într-un raport PivotTable

Faceți clic pe raportul PivotTable.

Pe fila Opțiuniîntr-un grup Opțiuni pentru tabel pivot faceți clic pe săgeata de lângă comandă Opțiuniși selectați elementul Opțiuni.

Accesați fila Marcare și format, apoi faceți următoarele:

Modificarea modului în care sunt afișate erorile.În câmp Format bifeaza casuta Pentru afisarea erorilor. Introduceți în câmp valoarea pe care doriți să o afișați în loc de erori. Pentru a afișa erorile ca celule goale, eliminați tot textul din câmp.

Modificați modul în care sunt afișate celulele goale. Caseta de bifat Pentru celulele goale, afișați. Introduceți în câmp valoarea pe care doriți să o afișați în celulele goale. Pentru a le păstra goale, eliminați tot textul din câmp. Pentru a afișa valori nule, debifați această casetă de validare.

Cum se calculează suma valorilor în Excel

Să presupunem că aveți o coloană cu valori și trebuie să știți suma acestora. Cel mai simplu mod - puteți selecta celulele dorite și în colțul din dreapta jos al Excel, în bara de stare, veți vedea suma valorilor, numărul acestora și chiar media aritmetică.

Dacă trebuie să folosiți suma primită undeva sau pur și simplu să o afișați într-o celulă, vom folosi funcția SUM. Poate funcționa atât în ​​modul manual, cât și în modul automat. Selectați celulele dorite și faceți clic pe butonul „Suma automată”, acesta se află în fila „Acasă”. Formula cu calculul sumei va apărea în celula următoare sub cele selectate de tine. Pentru a introduce suma într-un loc arbitrar, selectați orice celulă liberă și tastați „=SUM(” fără ghilimele în ea, apoi selectați intervalul dorit cu mouse-ul și apăsați Enter de pe tastatură.

De ce suma nu poate fi luată în considerare în Excel

Există două motive cel mai probabil.

1. Separator zecimal greșit. De exemplu, în Windows există o virgulă, iar în tabel aveți un punct. Încercați să înlocuiți semnul separator pentru o pereche de valori și să le adăugați. Pentru a vedea ce semn se află în sistem, accesați „Panou de control”, selectați „Opțiuni regionale și de limbă” -> „Opțiuni avansate”:

2. Celulele tale sunt în format text și, pe lângă numere, au caractere suplimentare, de exemplu, spații. Selectați-le și apăsați butonul dreapta al mouse-ului. Selectați „Format celule” din meniu și setați formatul la „Număr”, dacă nu este. Verificați dacă în celule nu există nimic în afară de numere.

Dacă aveți întrebări sau aveți nevoie de clarificări - o întrebare, experții noștri vă vor răspunde prompt solicitării.

Funcția principală a programului Excel este lucrul cu formule. Pentru asta este folosit cel mai des. Uneori se întâmplă ca formulele în Excel să nu funcționeze, caz în care trebuie să faceți ceva. Acesta este ceea ce se va discuta în continuare.

Remediem problema

Există multe motive pentru care formula nu funcționează în Excel. Poate că ați setat incorect setările programului sau un anumit interval, sau formula este introdusă incorect.

Formulele nu funcționează când trageți

Esența erorii este că sunt create mai multe coloane, de exemplu, 2, iar rezultatul ar trebui să fie afișat în a treia. Formula este specificată acolo și trasă în jos pentru a acoperi întregul interval, dar în final valoarea din prima celulă este pur și simplu transferată la următoarea. În acest caz, există mai multe modalități de a remedia totul:

1. Apăsați butonul F9 pentru a începe recalcularea foii.
2. Schimbați formatul celulei la generic. Pentru a face acest lucru, selectați-l și faceți clic dreapta. Se va deschide un meniu unde ar trebui să selectați „Format celulă” și să schimbați la „General”.
3. Deschideți opțiunile de calcul al formulei și verificați dacă semnele sunt aceleași ca pe captură de ecran.

Formula nu contează

Există mai multe motive pentru care formula Excel nu contează:

• Format de celulă nevalid. Pentru a o schimba, trebuie să selectați intervalul dorit de celule și să selectați „Format celulă”.

• Modul „Afișare formule” este activat. Pentru a o dezactiva, accesați secțiunea „Formule” din panoul de sus, faceți clic pe „Dependențe de formule” și dezactivați „Afișați formulele”.

• Formula sau sintaxa acesteia au fost introduse incorect. În acest caz, formula este afișată în locul valorii. Trebuie să-l verificați cu atenție.

Acestea sunt câteva dintre principalele motive pentru care Excel nu numără formule.

Programul nu ia în considerare formula introdusă în celulă

Acest lucru se întâmplă adesea deoarece formatul celulei este setat la text. Cum să îl schimbați cu altul a fost deja descris mai devreme.

Suma nu este calculată

Există două opțiuni pentru care aplicația Excel nu calculează suma:

1. Formatul celulei este de testare.
2. Calculul sumei este dezactivat. Este necesar, folosind butonul din dreapta al mouse-ului, să faceți clic pe un panou special și să setați marcajul la „Suma”.

De aceea suma nu este luată în considerare.

Datele celulei nu sunt însumate corect

Unii utilizatori au întâlnit când Excel calculează incorect suma celulelor selectate. Adesea, acest lucru se datorează faptului că unele celule, sau chiar majoritatea dintre ele, sunt în format text. Din acest motiv, nu toate datele sunt rezumate și rezultatul este incorect. Trebuie să schimbați formatul în „General” pentru a remedia problema.

Recalcularea a fost dezactivată

Se întâmplă ca utilizatorul să fi calculat toate datele, dar atunci când acestea sunt corectate în celulele asociate cu formula, rezultatul nu se schimbă. Aceasta înseamnă că parametrii setați incorect:

1. Deschideți setările programului.
2. Acolo ar trebui să mergeți la secțiunea „Formule”. În partea de sus va fi un bloc „Parametri de calcul”, iar acolo parametrul „Calculații în carte”, unde ar trebui să fie selectată poziția „Automat”. Dacă nu, setați-l corect și faceți clic pe OK.

Probleme cu formulele în Microsoft Excel

Acum, dacă se modifică vreo valoare, rezultatul va fi recalculat.

Din toate acestea se poate observa că există multe motive diferite pentru care Excel calculează incorect formula sau refuză deloc să le calculeze. Dacă utilizatorul are o formulă afișată acolo unde ar trebui să fie rezultatul, atunci formatul celulei este setat la text sau s-a făcut o eroare în formula însăși. Este posibil ca modul de vizualizare să fi fost activat. De asemenea, utilizatorii uită adesea să pună „=" înainte de începutul formulei. Dacă valorile se modifică în celulele asociate, dar rezultatul rămâne același, trebuie să verificați dacă actualizarea automată este activată. În unele cazuri, în celulele cu totaluri este afișată o eroare, atunci ar trebui să verificați toate valorile.