Download het voorbeeldwerkboek
Deze zelfstudie laat zien hoe u subtotalen per groep kunt berekenen met behulp van de SUMIFS-functie in Excel en Google Spreadsheets.
Subtotaaltabel per categorie of groep
Eerst laten we zien hoe u een dynamische subtotaaloverzichtstabel kunt maken op basis van een gegevensbereik in Excel 365 en hoger of Google Spreadsheets.
We gebruiken de UNIQUE-functie en de SUMIFS-functie om automatisch het subtotaal van de Aantal producten door Productgroep:
| 1 | =SOMMEN.(C3:C11,B3:B11,E3) |
Om deze subtotaaltabel te maken, gebruiken we de standaardtoepassing van de SOMMEN-functie om de Aantal producten die bij elkaar passen Productgroep. Voordat dit echter mogelijk is, moeten we een lijst met unieke Productgroepen. Gebruikers van Microsoft Excel 365 en Google Spreadsheets hebben toegang tot de UNIEKE functie om een dynamische lijst met unieke waarden uit een celbereik te maken. In dit voorbeeld voegen we de volgende formule toe aan cel E3:
| 1 | =UNIEK(B3:B11) |
Wanneer deze formule wordt ingevoerd, wordt er automatisch een lijst gemaakt onder de cel om alle unieke waarden weer te geven die in de . zijn gevonden Productgroep data bereik. In dit voorbeeld breidde de lijst zichzelf uit tot E3:E5 om alle 3 unieke weer te geven Productgroep waarden.
Dit is een dynamische arrayfunctie waarbij de grootte van de resultatenlijst niet gedefinieerd hoeft te worden, en deze zal automatisch kleiner en groter worden als de invoergegevenswaarden veranderen.
Merk op dat in Excel 365 de UNIEKE Functie niet hoofdlettergevoelig is, maar in Google Spreadsheets wel. Beschouw de lijst {“A”; "een"; "B"; "C"}. De uitvoer van UNIQUE Function is afhankelijk van het programma:
- {"EEN"; "B"; "c"} in Excel 365
- {"EEN"; "een"; "B"; "c"} in Google Spreadsheets
Als u een Excel-versie vóór Excel 365 gebruikt, moet u een andere aanpak kiezen. Dit wordt besproken in de volgende sectie.
Subtotaaltabel per categorie of groep - Pre Excel 365
Als u een versie van Excel vóór Excel 365 gebruikt, is de UNIEKE Functie niet beschikbaar voor gebruik. Om hetzelfde gedrag te repliceren, kunt u de INDEX-functie en MATCH-functie combineren met een AANTAL.ALS-functie om een matrixformule te maken om een lijst met unieke waarden uit een celbereik te produceren:
| 1 | {=INDEX($B$3:$B$11,MATCH(0,COUNTIF($E$2:E2,$B$3:$B$11),0))} |
Om deze formule te laten functioneren, moeten de vaste celverwijzingen zorgvuldig worden geschreven, waarbij de AANTAL.ALS-functie verwijst naar het bereik $ E $ 2: E2, het bereik vanaf E2 tot de cel boven de cel met de formule.
De formule moet ook als matrixformule worden ingevoerd door op CTRL + SHIFT + ENTER te drukken nadat deze is geschreven. Deze formule is a 1-cel matrixformule, die vervolgens in de cellen E4, E5 enz. kan worden gekopieerd en geplakt. Voer dit niet in één handeling in als matrixformule voor het hele bereik E3:E5.
Op dezelfde manier als in het vorige voorbeeld, wordt dan een SOMMEN-functie gebruikt om de Aantal producten door Productgroep:
| 1 | =SOMMEN.(C3:C11,B3:B11,E3) |
Som per categorie of groep - Subtotalen in gegevenstabellen
Als alternatief voor de hierboven getoonde samenvattende tabelmethode kunnen we subtotalen rechtstreeks aan een gegevenstabel toevoegen. We zullen dit demonstreren door de ALS-functies samen met de SUMIFS-functie te gebruiken om a . toe te voegen Subtotaal per groep naar de oorspronkelijke gegevenstabel.
| 1 | =ALS(B3=B2,"",SOMMEN.(C3:C11,B3:B11,B3)) |
In dit voorbeeld wordt een SOMMEN-functie gebruikt die is genest in een ALS-functie. Laten we het voorbeeld in stappen opsplitsen:
Om samenvattingsstatistieken rechtstreeks aan een gegevenstabel toe te voegen, kunnen we de SUMIFS-functie gebruiken. We beginnen met het optellen van de Aantal producten die overeenkomen met de relevante Productgroep:
| 1 | =SOMMEN.(C3:C11,B3:B11,B3) |
Deze formule produceert een subtotaalwaarde voor elke gegevensrij. Om subtotalen alleen in de eerste gegevensrij van elk weer te geven Productgroep, gebruiken we de ALS-functie. Houd er rekening mee dat de gegevens al moeten zijn gesorteerd op: Productgroep om ervoor te zorgen dat de subtotalen correct worden weergegeven.
| 1 | =ALS(B3=B2,"",SOMMEN.(C3:C11,B3:B11,B3)) |
De IF-functie vergelijkt elke gegevensrij Productgroep waarde met de gegevensrij erboven, en als ze dezelfde waarde hebben, wordt een lege cel ("") uitgevoerd.
Als de Productgroep waarden verschillen, wordt de som weergegeven. Op deze manier kan elke Productgroep som wordt slechts één keer weergegeven (in de rij van de eerste instantie).
Gegevenssets sorteren op groep
Als de gegevens nog niet zijn gesorteerd, kunnen we nog steeds dezelfde formule gebruiken voor het subtotaal.
De bovenstaande dataset is niet gesorteerd op Productgroep, dus de Subtotaal per groep kolom geeft elk subtotaal meer dan één keer weer. Om de gegevens in het gewenste formaat te krijgen, kunnen we de gegevenstabel selecteren en op "Sorteren van A tot Z" klikken.
Celverwijzingen vergrendelen
Om onze formules leesbaarder te maken, hebben we enkele formules weergegeven zonder vergrendelde celverwijzingen:
| 1 | =ALS(B3=B2,"",SOMMEN.(C3:C11,B3:B11,B3)) |
Maar deze formules werken niet goed wanneer ze ergens anders in uw bestand worden gekopieerd en geplakt. Gebruik in plaats daarvan vergrendelde celverwijzingen zoals deze:
| 1 | =ALS(B3=B2,"",SOMMEN($C$3:$C$11,$B$3:$B$11,B3)) |
Lees ons artikel over het vergrendelen van celreferenties voor meer informatie.
Draaitabellen gebruiken om subtotalen weer te geven
Om de vereiste te verwijderen om de gegevens vooraf te sorteren op: Productgroep, kunnen we in plaats daarvan de kracht van draaitabellen gebruiken om de gegevens samen te vatten. Draaitabellen berekenen automatisch subtotalen en geven totalen en subtotalen weer in verschillende formaten.
Som per categorie of groep in Google Spreadsheets
Deze formules werken in Google Spreadsheets hetzelfde als in Excel. De UNIEKE Functie is echter hoofdlettergevoelig in Google Spreadsheets.
