Last ned eksemplet på arbeidsbok
Denne opplæringen vil demonstrere hvordan du bruker SUMIFS -funksjonen til å summere data som tilsvarer tomme eller tomme celler i Excel og Google Sheets.
Sum hvis Blank
Først vil vi demonstrere hvordan du summerer rader med tomme celler.
SUMIFS -funksjonen summerer data som oppfyller visse kriterier.
Vi kan bruke SUMIFS -funksjonen til å summere alt Poeng til Spillere uten navn i eksemplet nedenfor.
| 1 | = SUMMER (C3: C8, B3: B8, "") |
Vi bruker doble anførselstegn (“”) for å representere en tom celle i Excel. Vårt eksempel ignorerer Spillere A, B, C og D og summerer poengsummene for ukjent Spillere.
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 nedenfor (”“) for å være tom:
For å behandle en celle som bare inneholder mellomrom som om den var en tom celle, kan vi legge til en hjelperkolonne med TRIM -funksjonen for å fjerne de ekstra mellomrommene fra hver celles verdi:
| 1 | = TRIM (B3) |
Vi bruker SUMIFS -funksjonen til hjelperkolonnen, og den beregner nå summen nøyaktig.
| 1 | = SUMMER (E3: E9, D3: D9, "") |
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 en hjelperkolonne ikke passer for dine behov, kan du bruke SUMPRODUCT -funksjonen i kombinasjon med LEN- og TRIM -funksjonene for å summere tomme rader.
| 1 | = SUMPRODUCT (-(LENNE (TRIM (B3: B9)) = 0), D3: D9) |
I dette eksemplet bruker vi SUMPRODUCT -funksjonen til å utføre en komplisert "sum if" -beregning. 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) trimmes alle Spiller navn med 0 tegn endres til TRUE:
| 1 | = SUMPRODUCT (-({FALSK; FALSK; SANN; FALSK; FALSK; SANN}), {25; 10; 15; 5; 8; 17; 50) |
Deretter konverterer de doble bindestrekene (-) de Sanne og FALSKE verdiene til 1 og 0:
| 1 | = SUMPRODUKT ({0; 0; 1; 0; 1; 0; 1}, {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 er tomme eller bare er laget av mellomrom:
| 1 | = SUMPRODUKT ({0; 0; 15; 0; 8; 0; 50) |
Til slutt summeres tallene i matrisen sammen:
| 1 | =73 |
Flere detaljer om bruk av boolske setninger og kommandoen “-” i en SUMPRODUCT-funksjon finner du her.
Sum Hvis tomt i Google Regneark
Disse formlene fungerer nøyaktig det samme i Google Regneark som i Excel.
