Download eksemplet på projektmappe
Denne vejledning viser, hvordan du bruger SUMIFS-funktionen til at opsummere data relateret til ikke-tomme eller ikke-tomme celler i Excel og Google Sheets.
Sum hvis ikke blank
Først vil vi demonstrere, hvordan man summerer data vedrørende ikke-tomme celler.
Vi kan bruge SUMIFS -funktionen til at summere alt Resultater til Spillere med ikke-tomme navne.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
For at opsummere rækker med ikke-tomme celler ekskluderer vi Resultater med savnet Spiller navne. Vi bruger kriterierne "ikke lig med blank" ("") inde i SUMIFS -funktionen.
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:
| 1 | = SUMIFS (D3: D9, B3: B9, "") |
For at behandle en celle, der kun indeholder mellemrum, som om det var en tom celle, kan vi tilføje en hjælperkolonne ved hjælp af LEN- og TRIM -funktionerne til at identificere Spillere med navne.
TRIM -funktionen fjerner de ekstra mellemrum fra starten og slutningen af hver celles værdi, og LEN -funktionen tæller derefter antallet af resterende tegn. Hvis resultatet af LEN -funktionen er 0, så er Spiller navnet må have været tomt eller kun bestående af mellemrum:
| 1 | = LEN (TRIM (B3)) |
Vi anvender SUMIFS -funktionen på hjælperkolonnen (Summing hvis større end 0), og den beregner nu summen nøjagtigt.
| 1 | = SUMIFS (E3: E9, D3: D9, "> 0") |
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 det er påkrævet at behandle celler, der kun indeholder mellemrum på samme måde, som om de var tomme, men det er ikke hensigtsmæssigt at bruge en hjælperkolonne, kan vi bruge SUMPRODUCT -funktionen i kombination med LEN- og TRIM -funktionerne til at opsummere data vedrørende celler indeholder ikke-blank Spiller navne:
| 1 | = SUMPRODUCT (-(LENG (TRIM (B3: B9))> 0), D3: D9) |
I dette eksempel bruger vi SUMPRODUCT -funktionen til at udføre komplicerede "sum if" -beregninger. Lad os gå igennem formlen.
Dette er vores endelige formel:
| 1 | = SUMPRODUCT (-(LENGE (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 mere end 0 tegn ændres til SAND:
| 1 | = SUMPRODUCT (-({SAND; SAND; FALSK; SAND; FALSK; FALSK}), {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 ({1; 1; 0; 1; 0; 1; 0}, {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 ikke er tomme eller ikke kun er fremstillet af mellemrum:
| 1 | = SUMPRODUKT ({25; 10; 0; 5; 0; 17; 0) |
Endelig summeres tallene i arrayet sammen
| 1 | =57 |
Flere detaljer om brug af boolske udsagn og kommandoen “-” i en SUMPRODUCT-funktion findes her
Sum hvis ikke blank i Google Sheets
Disse formler fungerer nøjagtig det samme i Google Sheets som i Excel.
