Descărcați exemplul de registru de lucru
Acest tutorial va arăta cum să utilizați funcția SUMIFS pentru a însuma datele legate de celulele necompletate sau necompletate în Excel și Foi de calcul Google.
Suma dacă nu este gol
În primul rând, vom demonstra cum să însumăm datele referitoare la celulele care nu sunt goale.
Putem utiliza funcția SUMIFS pentru a rezuma toate Scoruri pentru Jucători cu nume care nu sunt goale.
| 1 | = SUME (C3: C8, B3: B8, "") |
Pentru a însuma rânduri cu celule care nu sunt goale, excludem Scoruri cu lipsă Jucător nume. Folosim criteriile „nu egal cu golul” („”) în cadrul funcției SUMIFS.
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ă:
| 1 | = SUME (D3: D9, B3: B9, "") |
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 folosind funcțiile LEN și TRIM pentru a identifica Jucători cu nume.
Funcția TRIM elimină spațiile suplimentare de la începutul și sfârșitul valorii fiecărei celule, iar funcția LEN numără apoi numărul de caractere rămase. Dacă rezultatul funcției LEN este 0, atunci Jucător numele trebuie să fi fost gol sau făcut numai din spații:
| 1 | = LEN (TRIM (B3)) |
Aplicăm funcția SUMIFS coloanei de ajutor (Sumând dacă este mai mare de 0) și acum calculează suma cu precizie.
| 1 | = SUME (E3: E9, D3: D9, "> 0") |
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ă este necesar să se trateze orice celule care conțin doar spații în același mod ca și cum ar fi goale, dar utilizarea unei coloane de ajutor nu este adecvată, atunci putem utiliza funcția SUMPRODUCT în combinație cu funcțiile LEN și TRIM pentru a însuma datele referitoare la celule care conține non-blank Jucător nume:
| 1 | = SUMPRODUCT (- (LEN (TRIM (B3: B9))> 0), D3: D9) |
În acest exemplu, folosim funcția SUMPRODUCT pentru a efectua calcule „sum dacă” complicate. 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 mai mult de 0 caractere sunt schimbate în ADEVĂRAT:
| 1 | = SUMPRODUCT (- ({TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}), {25; 10; 15; 5; 8; 17; 50) |
Apoi liniuțele duble (-) convertesc valorile TRUE și FALSE în 1s și 0s:
| 1 | = SUMPRODUCT ({1; 1; 0; 1; 0; 1; 0}, {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 nu sunt necompletate sau care nu sunt făcute numai din spații:
| 1 | = SUMPRODUCT ({25; 10; 0; 5; 0; 17; 0) |
În cele din urmă, numerele din matrice sunt însumate împreună
| 1 | =57 |
Mai multe detalii despre utilizarea instrucțiunilor booleene și comanda „-” într-o funcție SUMPRODUCT pot fi găsite aici
Suma dacă nu este necompletată în Foi de calcul Google
Aceste formule funcționează exact la fel în Foi de calcul Google ca în Excel.
