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"})).
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.
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.
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:
- SUM - adună valori;
- SUMIF - însumează valorile dintr-un interval care respectă criteriile specificate;
- AVERAGE - returnează valoarea medie (media aritmetică) a valorilor;
- AGGREGATE - returnează un agregat de calcule precum SUM, COUNT, SMALL, etc. cu opțiunea de a ignora erorile și rândurile ascunse;
- COUNTIF - contorizează celulele care îndeplinesc un criteriu;
- IF - efectuează comparații logice între o valoare și valoarea dorită;
- IFERROR - returnează o valoare specificată dacă o formulă are ca răspuns la o eroare; altfel, returnează rezultatul formulei;
- ISERROR - determină dacă o expresie numerică reprezintă o eroare sau nu.
info Notă
Toate articolele de pe acest site se pot distribui cu mențiunea sursei, punând linkul articolului sau al site-ului.
Alte subiecte pe aceeași temă:
-
Clarificări cu privire la comentarii
Toate comentariile de pe acest blog sunt moderate.
Comentariile care vor încălca Regulamentul U.E. nr. 679/2016 al Parlamentului European și al Consiliului din 27 aprilie 2016 privind protecția persoanelor fizice în ceea ce privește prelucrarea datelor cu caracter personal și privind libera circulație a acestor date și de abrogare a Directivei 95/46/CE (Regulamentul general privind protecția datelor), zis și RGPD, chiar dacă este vorba de consimțământul propriu, adică care conțin adresă de poștă electronică (e-mail) sau/și număr de telefon, de exemplu; care vor încălca buna conduită prin comportament necivilizat și printr-un limbaj agresiv, injurios, xenofob, rasist, care incită la ură și violență; care vor conține anunțuri publicitare sau care vor conține cu o tentă publicitară; care nu corespund tematicii blogului sau sunt irelevante, nu vor fi aprobate spre publicare sau/și vor fi șterse.
Utilizatorii sunt responsabili pentru propriu conținut din comentarii.
Comentarii
Trimiteți un comentariu