Last ned eksemplet på arbeidsbok
Denne opplæringen vil demonstrere hvordan du bruker SUMIFS-funksjonen til å summere data relatert til ikke-tomme eller ikke-tomme celler i Excel og Google Sheets.
Sum hvis ikke tomt
Først vil vi demonstrere hvordan vi summerer data knyttet til ikke-tomme celler.
Vi kan bruke SUMIFS -funksjonen til å summere alt Poeng til Spillere med ikke-blanke navn.
| 1 | = SUMMER (C3: C8, B3: B8, "") |
For å summere rader med ikke-tomme celler, ekskluderer vi Poeng med savnet Spiller navn. Vi bruker kriteriene "ikke lik blank" ("") inne i SUMIFS -funksjonen.
Behandle mellomrom som tomme celler - med hjelpekolonne
Du må være forsiktig når du samhandler med tomme celler i Excel. Celler kan vises tomme for deg, men Excel vil ikke behandle dem som tomme. Dette kan skje hvis cellen inneholder mellomrom, linjeskift eller andre usynlige tegn. Dette er et vanlig problem når du importerer data til Excel fra andre kilder.
Hvis vi trenger å behandle celler som bare inneholder mellomrom på samme måte som om de var tomme, vil ikke formelen i forrige eksempel ikke fungere. Legg merke til hvordan SUMIFS -formelen ikke anser celle B9 under (”“) for å være tom:
| 1 | = SUMIFS (D3: D9, B3: B9, "") |
For å behandle en celle som bare inneholder mellomrom som om den var en tom celle, kan vi legge til en hjelperkolonne ved hjelp av LEN- og TRIM -funksjonene for å identifisere Spillere med navn.
TRIM -funksjonen fjerner de ekstra mellomrommene fra begynnelsen og slutten av hver celles verdi, og LEN -funksjonen teller deretter antall gjenværende tegn. Hvis resultatet av LEN -funksjonen er 0, så er Spiller navnet må ha vært tomt eller bare laget av mellomrom:
| 1 | = LENNE (TRIM (B3)) |
Vi bruker SUMIFS -funksjonen til hjelperkolonnen (Summing hvis større enn 0), og den beregner nå summen nøyaktig.
| 1 | = SUMIFS (E3: E9, D3: D9, "> 0") |
Hjelpekolonnen er lett å lage og lett å lese, men du vil kanskje ha en enkelt formel for å utføre oppgaven. Dette dekkes i neste avsnitt.
Behandle mellomrom som tomme celler - Uten hjelperkolonne
Hvis det er nødvendig å behandle celler som bare inneholder mellomrom på samme måte som om de var tomme, men det er ikke hensiktsmessig å bruke en hjelperkolonne, kan vi bruke SUMPRODUCT -funksjonen i kombinasjon med LEN- og TRIM -funksjonene for å summere data knyttet til celler inneholder ikke-blank Spiller navn:
| 1 | = SUMPRODUCT (-(LENNE (TRIM (B3: B9))> 0), D3: D9) |
I dette eksemplet bruker vi SUMPRODUCT -funksjonen til å utføre kompliserte "sum if" -beregninger. La oss gå gjennom formelen.
Dette er vår siste formel:
| 1 | = SUMPRODUCT (-(LENNE (TRIM (B3: B9))> 0), D3: D9) |
For det første viser SUMPRODUCT -funksjonen en rekke verdier fra de to celleområdene:
| 1 | = SUMPRODUCT (-(LENG (TRIM ({"A"; "B"; ""; "C"; ""; "XX"; ""}))> 0), {25; 10; 15; 5 ; 8; 17; 50) |
TRIM -funksjonen fjerner deretter ledende og etterfølgende mellomrom fra Spiller navn:
| 1 | = SUMPRODUCT (-(LENG ({"A"; "B"; ""; "C"; ""; "XX"; ""})> 0), {25; 10; 15; 5; 8; 17; 50) |
LEN -funksjonen beregner lengden på trimmet Spiller navn:
| 1 | = SUMPRODUKT (-({1; 1; 0; 1; 0; 2; 0}> 0), {25; 10; 15; 5; 8; 17; 50) |
Med den logiske testen (> 0) kan enhver trimmes Spiller navn med mer enn 0 tegn endres til TRUE:
| 1 | = SUMPRODUCT (-({SANN; SANN; FALSK; SANN; FALSK; FALSK}), {25; 10; 15; 5; 8; 17; 50) |
Deretter konverterer de doble bindestrekene (-) de Sanne og FALSKE verdiene til 1 og 0:
| 1 | = SUMPRODUKT ({1; 1; 0; 1; 0; 1; 0}, {25; 10; 15; 5; 8; 17; 50) |
SUMPRODUCT -funksjonen multipliserer deretter hvert oppføringspar i matrisene for å produsere en rekke Poeng bare for Spiller navn som ikke er tomme eller ikke bare er laget av mellomrom:
| 1 | = SUMPRODUKT ({25; 10; 0; 5; 0; 17; 0) |
Til slutt summeres tallene i rekken sammen
| 1 | =57 |
Flere detaljer om bruk av boolske setninger og kommandoen “-” i en SUMPRODUCT-funksjon finner du her
Sum hvis ikke tomt i Google Regneark
Disse formlene fungerer nøyaktig det samme i Google Regneark som i Excel.
