Download het voorbeeldwerkboek
Deze zelfstudie laat zien hoe u de SUMIFS-functie kunt gebruiken om gegevens met betrekking tot niet-lege of niet-lege cellen in Excel en Google Spreadsheets op te tellen.
Som indien niet leeg
Eerst zullen we laten zien hoe gegevens met betrekking tot niet-lege cellen kunnen worden opgeteld.
We kunnen de SOMMEN-functie gebruiken om alles op te tellen Scores voor Spelers met niet-lege namen.
| 1 | =SOMMEN.(C3:C8,B3:B8,"") |
Om rijen met niet-lege cellen op te tellen, sluiten we uit Scores met ontbrekende Speler namen. We gebruiken de criteria "niet gelijk aan blanco" ("") in de SUMIFS-functie.
Spaties behandelen als lege cellen - met hulpkolom
U moet voorzichtig zijn bij interactie met lege cellen in Excel. Cellen kunnen voor u leeg lijken, maar Excel behandelt ze niet als leeg. Dit kan gebeuren als de cel spaties, regeleinden of andere onzichtbare tekens bevat. Dit is een veelvoorkomend probleem bij het importeren van gegevens in Excel uit andere bronnen.
Als we cellen die alleen spaties bevatten, op dezelfde manier moeten behandelen alsof ze leeg waren, werkt de formule in het vorige voorbeeld niet. Merk op hoe de SUMIFS-formule cel B9 hieronder ("") niet als leeg beschouwt:
| 1 | =SOMMEN.(D3:D9,B3:B9,"") |
Om een cel die alleen spaties bevat te behandelen alsof het een lege cel is, kunnen we een hulpkolom toevoegen met behulp van de LEN- en TRIM-functies om te identificeren Spelers met namen.
De TRIM-functie verwijdert de extra spaties aan het begin en einde van de waarde van elke cel en de LEN-functie telt vervolgens het aantal resterende tekens. Als het resultaat van de LEN-functie 0 is, dan is de Speler naam moet leeg zijn of alleen uit spaties bestaan:
| 1 | =LENGTE(TRIM(B3)) |
We passen de SOMMEN-functie toe op de hulpkolom (optellen indien groter dan 0), en het berekent nu de som nauwkeurig.
| 1 | =SOMMEN.(E3:E9,D3:D9,">0") |
De helperkolom is gemakkelijk te maken en gemakkelijk te lezen, maar misschien wilt u een enkele formule hebben om de taak te volbrengen. Dit wordt behandeld in de volgende sectie.
Spaties behandelen als lege cellen - zonder helperkolom
Als het nodig is om cellen die alleen spaties bevatten op dezelfde manier te behandelen alsof ze leeg waren, maar het gebruik van een hulpkolom niet geschikt is, dan kunnen we de SOMPRODUCT-functie gebruiken in combinatie met de LEN- en TRIM-functies om gegevens met betrekking tot cellen op te tellen met niet-blanco Speler namen:
| 1 | =SOMPRODUCT(--(LENGTE(TRIM(B3:B9))>0),D3:D9) |
In dit voorbeeld gebruiken we de SOMPRODUCT-functie om gecompliceerde 'som als'-berekeningen uit te voeren. Laten we de formule doornemen.
Dit is onze definitieve formule:
| 1 | =SOMPRODUCT(--(LENGTE(TRIM(B3:B9))>0),D3:D9) |
Ten eerste geeft de SOMPRODUCT-functie een lijst met waarden uit de twee celbereiken:
| 1 | =SOMPRODUCT(--(LENGTE(TRIM({"A"; "B"; ""; "C"; ""; "XX"; " "}))>0),{25; 10; 15; 5 ; 8; 17; 50) |
Vervolgens verwijdert de TRIM-functie voorloop- en volgspaties uit Speler namen:
| 1 | =SOMPRODUCT(--(LENGTE({"A"; "B"; ""; "C"; ""; "XX"; ""})>0),{25; 10; 15; 5; 8; 17; 50) |
De LEN-functie berekent de lengte van de getrimde Speler namen:
| 1 | =SOMPRODUCT(--({1; 1; 0; 1; 0; 2; 0}>0),{25; 10; 15; 5; 8; 17; 50) |
Met de logische test (>0), elke getrimd Speler namen met meer dan 0 tekens worden gewijzigd in TRUE:
| 1 | =SOMPRODUCT(--({TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}),{25; 10; 15; 5; 8; 17; 50) |
Vervolgens zetten de dubbele streepjes (-) de TRUE en FALSE-waarden om in 1s en 0s:
| 1 | =SOMPRODUCT({1; 1; 0; 1; 0; 1; 0},{25; 10; 15; 5; 8; 17; 50) |
De SOMPRODUCT-functie vermenigvuldigt vervolgens elk paar vermeldingen in de arrays om een array te produceren van Scores alleen voor Speler namen die niet leeg zijn of niet alleen uit spaties bestaan:
| 1 | =SOMPRODUCT({25; 10; 0; 5; 0; 17; 0) |
Ten slotte worden de getallen in de array bij elkaar opgeteld
| 1 | =57 |
Meer details over het gebruik van Booleaanse instructies en de opdracht "-" in een SUMPRODUCT-functie vindt u hier
Som indien niet leeg in Google Spreadsheets
Deze formules werken in Google Spreadsheets precies hetzelfde als in Excel.
