Însumarea și media aritmetică ignorând erorile din Excel

Cum se poate însuma și/sau se poate calcula media aritmetică în Excel ignorând erorile

Însumarea valorilor ignorând erorile

Dacă există o listă de coloane care conține și erori în loc de valori numerice, așa cum se arată mai jos, și se dorește însumarea întregii coloane, ignorând erorile, atunci există câteva trucuri pentru a rezolva această problemă în Excel.

Însumarea normală ar fi =SUM($E$2:$E$21), dar rezultatul ar fi o eroare deoarece precum se vede coloana conține erori, adică rezultatul ar fi acesta #DIV/0!.

Însumarea numerelor ignorând erorile cu ajutorul funcțiilor SUM și IFERROR

Cea mai rapidă și simplă variantă de formulă de a însuma coloana în cauză este:

{=SUM(IFERROR($E$2:$E$21;0))}

care  este de fapt o formulă matrice, verificând fiecare celulă înainte de a calcula dacă acesta conține o eroare sau nu și dacă da, înlocuiește eroarea cu valoarea 0 (IFERROR($E$2:$E$21;0)) și apoi adăugând valoarea la însumarea coloanei.

info Notă

Cu excepția Microsoft Excel 365, orice formulă matrice se validează prin tastarea simultană CTRL + SHIFT + ENTER și nu prin simplu ENTER.

Însumarea numerelor ignorând erorile cu ajutorul funcțiilor SUM și SUMIF

O altă formulă pentru însumarea valorilor ignorând erorile ar fi:

=SUM(SUMIF($E$2:$E$21;{"<0";">0"}))

unde, înainte de a însuma, SUMIF verifică condiția ca valoarea să fie diferit de zero, condiția fiind scrisă ca matrice, și dacă îndeplinește condiția, atunci valoarea este adăugată la însumare (SUMIF($E$2:$E$21;{"<0";">0"})).

Însumarea numerelor ignorând erorile cu ajutorul funcțiilor SUM, IF și ISERROR

Cu ajutorul funcțiilor SUM, IF și ISERROR, o altă formulă matrice, se poate însuma valorile ignorând erorile din coloană, astfel:

{=SUM(IF(ISERROR($E$2:$E$21);0;$E$2:$E$21))}

Având aceleași principii de verificare ca IFERROR($E$2:$E$21;0), combinația de funcții IF(ISERROR($E$2:$E$21);0;$E$2:$E$21) verifică mai întâi dacă valoarea este numerică sau este o eroare și în cazul în care este o eroare, pune valoarea 0 și apoi o adaugă la însumare.

info Notă

Formula matrice se validează prin tastarea simultană CTRL + SHIFT + ENTER și nu prin simplu ENTER.

Însumarea numerelor ignorând erorile cu ajutorul funcțiilor SUM, IF și ISERROR

O altă funcție Excel care poate însuma numerele dintr-o coloană ignorând erorile este AGGREGATE, cu următoarea formulă:

=AGGREGATE(9;6;$E$2:$E$21)

 unde:
– 9 reprezintă funcția SUM;
– 6 reprezintă opțiunea de a ignora erorile la însumare;
– $E$2:$E$21 reprezintă zona de celule pentru a fi însumate.

9 reprezintă funcția SUM

6 reprezintă opțiunea de a ignora erorile la însumare

$E$2:$E$21 reprezintă zona de celule pentru a fi însumate

Rezultatul:

Media aritmetică a valorilor ignorând erorile

Ca la  însumarea valorilor ignorând erorile și la calcularea mediei aritmetice se poate aplica principiul calculării cu ignorarea erorilor.

Iată câteva formule:

{=AVERAGE(IF(ISERROR($E$2:$E$21);"";$E$2:$E$21))}

=SUM(SUMIF($E$2:$E$21;{"<0";">0"}))/SUM(COUNTIF($E$2:$E$21;{"<0";">=0"}))

=AGGREGATE(1;6;$E$2:$E$21)

Explicațiile funcțiilor folosite pentru însumarea și calcularea medii aritmetice ale valorilor cu ignorarea erorilor în Excel:

1. SUM - adună valori;
2. SUMIF -  însumează valorile dintr-un interval care respectă criteriile specificate;
3. AVERAGE - returnează valoarea medie (media aritmetică) a valorilor;
4. AGGREGATE - returnează un agregat de calcule precum SUM, COUNT, SMALL, etc. cu opțiunea de a ignora erorile și rândurile ascunse;
5. COUNTIF - contorizează celulele care îndeplinesc un criteriu;
6. IF - efectuează comparații logice între o valoare și valoarea dorită;
7. IFERROR - returnează o valoare specificată dacă o formulă are ca răspuns la o eroare; altfel, returnează rezultatul formulei;
8. ISERROR - determină dacă o expresie numerică reprezintă o eroare sau nu.

Aici se poate descărca fișierul Însumare și medie aritmetică ignorând erorile_20230521.xlsx pentru exersare. Fișierul conține și un bonus.

info Notă

Toate articolele de pe acest site se pot distribui cu mențiunea sursei, punând linkul articolului sau al site-ului.