Download eksemplet på projektmappe
Denne vejledning viser, hvordan du bruger SUMIFS -funktionen til at opsummere data, der svarer til tomme eller tomme celler i Excel og Google Sheets.
Sum hvis Blank
Først vil vi demonstrere, hvordan man summer summerer med tomme celler.
SUMIFS -funktionen summerer data, der opfylder visse kriterier.
Vi kan bruge SUMIFS -funktionen til at summere alt Resultater til Spillere uden navne i eksemplet herunder.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Vi bruger dobbelt anførselstegn (“”) til at repræsentere en tom celle i Excel. Vores eksempel ignorerer Spillere A, B, C og D og summer summeringen for ukendt Spillere.
Behandling af rum som tomme celler - med hjælpekolonne
Du skal være forsigtig, når du interagerer med tomme celler i Excel. Celler kan forekomme tomme for dig, men Excel behandler dem ikke som tomme. Dette kan forekomme, hvis cellen indeholder mellemrum, liniebrud eller andre usynlige tegn. Dette er et almindeligt problem ved import af data til Excel fra andre kilder.
Hvis vi skal behandle celler, der kun indeholder mellemrum på samme måde, som hvis de var tomme, fungerer formlen i det foregående eksempel ikke. Læg mærke til, hvordan SUMIFS -formlen ikke anser celle B9 nedenfor (”“) for at være tom:
For at behandle en celle, der kun indeholder mellemrum, som om det var en tom celle, kan vi tilføje en hjælperkolonne med TRIM -funktionen for at fjerne de ekstra mellemrum fra hver celles værdi:
| 1 | = TRIM (B3) |
Vi anvender SUMIFS -funktionen på hjælperkolonnen, og den beregner nu summen nøjagtigt.
| 1 | = SUMIFS (E3: E9, D3: D9, "") |
Hjælperkolonnen er let at oprette og let at læse, men du vil måske have en enkelt formel til at udføre opgaven. Dette er dækket i det næste afsnit.
Behandling af rum som tomme celler - Uden hjælpekolonne
Hvis en hjælperkolonne ikke passer til dine behov, kan du bruge SUMPRODUCT -funktionen i kombination med LEN- og TRIM -funktionerne til at summere tomme rækker.
| 1 | = SUMPRODUCT (-(LENNE (TRIM (B3: B9)) = 0), D3: D9) |
I dette eksempel bruger vi SUMPRODUCT -funktionen til at udføre en kompliceret "sum if" -beregning. Lad os gå igennem formlen.
Dette er vores endelige formel:
| 1 | = SUMPRODUCT (-(LENNE (TRIM (B3: B9)) = 0), D3: D9) |
For det første lister SUMPRODUCT -funktionen matrixværdierne fra de to celleområder:
| 1 | = SUMPRODUCT (-(LEN (TRIM ({"A"; "B"; ""; "C"; ""; "XX"; ""})) = 0), {25; 10; 15; 5 ; 8; 17; 50) |
TRIM -funktionen fjerner derefter ledende og bageste mellemrum fra Spiller navne:
| 1 | = SUMPRODUCT (-(LEN ({"A"; "B"; ""; "C"; ""; "XX"; ""}) = 0), {25; 10; 15; 5; 8; 17; 50) |
LEN -funktionen beregner længden af den trimmede Spiller navne:
| 1 | = SUMPRODUKT (-({1; 1; 0; 1; 0; 2; 0} = 0), {25; 10; 15; 5; 8; 17; 50) |
Med den logiske test (= 0) trimmes enhver Spiller navne med 0 tegn ændres til SAND:
| 1 | = SUMPRODUCT (-({FALSK; FALSK; SAND; FALSK; FALSK; SAND}), {25; 10; 15; 5; 8; 17; 50) |
Dernæst konverterer de dobbelte bindestreger (-) værdierne SAND og FALSK til 1'er og 0'er:
| 1 | = SUMPRODUKT ({0; 0; 1; 0; 1; 0; 1}, {25; 10; 15; 5; 8; 17; 50) |
SUMPRODUCT -funktionen multiplicerer derefter hvert par af poster i arraysne for at producere en matrix af Resultater kun for Spiller navne, der er tomme eller kun er fremstillet af mellemrum:
| 1 | = SUMPRODUKT ({0; 0; 15; 0; 8; 0; 50) |
Endelig summeres tallene i arrayet sammen:
| 1 | =73 |
Flere detaljer om brug af boolske udsagn og kommandoen “-” i en SUMPRODUCT-funktion findes her.
Sum Hvis Blank i Google Sheets
Disse formler fungerer nøjagtig det samme i Google Sheets som i Excel.
