Descărcați exemplul de registru de lucru
Acest tutorial va arăta cum să utilizați funcția SUMIFS pentru a însuma datele corespunzătoare celulelor goale sau goale din Excel și Foi de calcul Google.
Suma dacă este gol
În primul rând, vom demonstra cum să însumăm rânduri cu celule goale.
Funcția SUMIFS însumează date care îndeplinesc anumite criterii.
Putem utiliza funcția SUMIFS pentru a rezuma toate Scoruri pentru Jucători fără nume în exemplul de mai jos.
| 1 | = SUME (C3: C8, B3: B8, "") |
Folosim ghilimele duble („”) pentru a reprezenta o celulă necompletată în Excel. Exemplul nostru ignoră Jucători A, B, C și D și însumează scorurile pentru necunoscut Jucători.
Tratarea spațiilor ca celule goale - Cu coloana Helper
Trebuie să aveți grijă atunci când interacționați cu celule goale din Excel. Celulele vă pot apărea necompletate, dar Excel nu le va trata ca necompletate. Acest lucru se poate întâmpla dacă celula conține spații, linii sau alte caractere invizibile. Aceasta este o problemă obișnuită atunci când importați date în Excel din alte surse.
Dacă trebuie să tratăm orice celule care conțin doar spații în același mod ca și cum ar fi goale, atunci formula din exemplul anterior nu va funcționa. Observați cum formula SUMIFS nu consideră că celula B9 de mai jos ("") este necompletată:
Pentru a trata o celulă care conține doar spații ca și cum ar fi o celulă goală, putem adăuga o coloană de ajutor cu funcția TRIM pentru a elimina spațiile suplimentare din valoarea fiecărei celule:
| 1 | = TRIM (B3) |
Aplicăm funcția SUMIFS coloanei de ajutor și acum calculează suma cu precizie.
| 1 | = SUME (E3: E9, D3: D9, "") |
Coloana de ajutor este ușor de creat și ușor de citit, dar este posibil să doriți să aveți o singură formulă pentru a îndeplini sarcina. Acest lucru este prezentat în secțiunea următoare.
Tratarea spațiilor ca celule goale - fără coloană de ajutor
Dacă o coloană de ajutor nu este adecvată nevoilor dvs., puteți utiliza funcția SUMPRODUCT în combinație cu funcțiile LEN și TRIM pentru a însuma rânduri goale.
| 1 | = SUMPRODUCT (- (LEN (TRIM (B3: B9)) = 0), D3: D9) |
În acest exemplu, folosim funcția SUMPRODUCT pentru a efectua un calcul complicat „sum dacă”. Să trecem prin formulă.
Aceasta este formula noastră finală:
| 1 | = SUMPRODUCT (- (LEN (TRIM (B3: B9)) = 0), D3: D9) |
În primul rând, funcția SUMPRODUCT listează matricea de valori din cele două intervale de celule:
| 1 | = SUMPRODUCT (- (LEN (TRIM ({"A"; "B"; ""; "C"; ""; "XX"; ""}))) = 0), {25; 10; 15; 5 ; 8; 17; 50) |
Apoi, funcția TRIM elimină spațiile de conducere și de urmărire Jucător nume:
| 1 | = SUMPRODUCT (- (LEN ({"A"; "B"; ""; "C"; ""; "XX"; ""}) = 0), {25; 10; 15; 5; 8; 17; 50) |
Funcția LEN calculează lungimile tăiate Jucător nume:
| 1 | = SUMPRODUCT (- ({1; 1; 0; 1; 0; 2; 0} = 0), {25; 10; 15; 5; 8; 17; 50) |
Cu testul logic (= 0), orice decupat Jucător numele cu 0 caractere sunt schimbate în ADEVĂRAT:
| 1 | = SUMPRODUCT (- ({FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}), {25; 10; 15; 5; 8; 17; 50) |
Apoi liniuțele duble (-) convertesc valorile TRUE și FALSE în 1s și 0s:
| 1 | = SUMPRODUCT ({0; 0; 1; 0; 1; 0; 1}, {25; 10; 15; 5; 8; 17; 50) |
Funcția SUMPRODUCT multiplică apoi fiecare pereche de intrări în tablouri pentru a produce o serie de Scoruri doar pentru Jucător nume care sunt goale sau făcute numai din spații:
| 1 | = SUMPRODUCT ({0; 0; 15; 0; 8; 0; 50) |
În cele din urmă, numerele din matrice sunt însumate împreună:
| 1 | =73 |
Mai multe detalii despre utilizarea instrucțiunilor booleene și comanda „-” într-o funcție SUMPRODUCT pot fi găsite aici.
Suma dacă este goală în Foi de calcul Google
Aceste formule funcționează exact la fel în Foi de calcul Google ca în Excel.
