Het verkrijgen van subtotalen voor groepen gerelateerde items is eenvoudig in Microsoft Excel. In feite heb je twee manieren om dit te doen. U kunt de functie SUBTOTAAL of de functie Subtotaal gebruiken, afhankelijk van wat het beste werkt. We laten je zien hoe.
Wat is een subtotaal in Excel?
Gebruik de Excel SUBTOTAAL-functie
Opmerkingen over de functie SUBTOTAAL
Gebruik de subtotaalfunctie
Wat is een subtotaal in Excel?
Zoals vermeld, kunt u met SUBTOTAAL in Excel dezelfde of gerelateerde items in een lijst groeperen en een functie gebruiken om de waarden te berekenen. U kunt het bijvoorbeeld gebruiken om verkopen per maand of gemiddelde cijfers per leerling op te tellen. U kunt ook voorraad per product toevoegen of het aantal rekeningen tellen dat volgende week moet worden betaald.
Door de functie SUBTOTAAL te gebruiken door een formule te maken of door de functie Subtotaal te gebruiken, kunt u in slechts enkele stappen de berekeningen krijgen die u nodig hebt.
Gebruik de Excel SUBTOTAAL-functie
U kunt een formule maken met behulp van de Excel-functie SUBTOTAAL met de flexibiliteit om rijen die u hebt verborgen op te nemen of uit te sluiten.
De syntaxis voor de formule is SUBTOTAL(function_number, reference 1, reference 2,...) waarbij de eerste twee argumenten vereist zijn. U kunt indien nodig extra celverwijzingen of benoemde bereiken gebruiken voor de overige argumenten.
De function_number argument stelt u in staat om een van de 11 functies in te voegen met behulp van het overeenkomstige nummer. De eerste 11 bevatten verborgen rijen, terwijl de tweede 11 deze uitsluiten. Voer dus gewoon het nummer in voor de functie die u nodig hebt en hoe u met de verborgen rijen wilt omgaan.
| Functie | Inclusief verborgen rijen | Sluit verborgen rijen uit |
| GEMIDDELD | 1 | 101 |
| GRAAF | 2 | 102 |
| AANTAL | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SOM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Als voorbeeld van de functie SUBTOTAAL tellen we het totaal van de verkopen in het celbereik B2 tot en met B4 op met behulp van deze formule:
=SUBTOTAL(9,B2:B4)
Voor dit volgende voorbeeld hebben we rijen 4 en 5 verborgen. Met behulp van deze eerste formule kunnen we onze som verkrijgen door het getal 9 te gebruiken voor het eerste argument om de verborgen gegevens op te nemen.
=SUBTOTAL(9,B2:B6)
Nu sluiten we die verborgen rijen uit van ons totaal, waarbij we het getal 109 gebruiken voor ons eerste argument.
=SUBTOTAL(109,B2:B6)
Als je eenmaal je subtotalen hebt, kun je de functie SUBTOTAAL nog een keer gebruiken voor een eindtotaal onderaan. U kunt ook de SOM-functie gebruiken om de subtotalen op te tellen.
Voor een ander voorbeeld gebruiken we de gemiddelde functie om verborgen rijen 3 en 4 op te nemen met deze formule:
=SUBTOTAL(1,C2:C6)
En vervolgens sluiten we de verborgen rijen uit met deze formule:
=SUBTOTAL(101,C2:C6)
Opmerkingen over de functie SUBTOTAAL
Hier zijn een paar dingen waarmee u rekening moet houden bij het gebruik van de functie SUBTOTAAL in Excel:
- De functie werkt voor kolommen of verticale bereiken, niet voor rijen of horizontale bereiken.
- Als u subtotalen hebt genest in het referentieargumentgebied, negeert Excel deze in het subtotaalresultaat.
- Wanneer u een filter gebruikt, worden de uitgefilterde gegevens uitgesloten van het subtotaalresultaat.
Gebruik de subtotaalfunctie
Een andere manier om de functie SUBTOTAAL in Excel te gebruiken, is door de functie Subtotaal te gebruiken. Zo kun je automatisch berekeningen toevoegen en groepeer de items tegelijkertijd. Excel gebruikt de functie SUBTOTAAL om dit voor u te doen. Laten we een paar voorbeelden bekijken.
Met behulp van de functie Subtotaal voor onze verkopen per maand, kunnen we de gegevens per maand groeperen en elke groep optellen met een eindtotaal onderaan.
Selecteer alle rijen die u wilt groeperen en subtotalen. Ga naar het tabblad Gegevens en kies “Subtotaal” in het vervolgkeuzemenu Overzicht.
Wanneer het vak Subtotaal wordt geopend, kiest u hoe u de subtotalen wilt toevoegen.
- Bij elke verandering in: Selecteer de kolom die u wilt gebruiken voor de groepering. Hier hebben we Maand gekozen, zodat er een nieuwe groep wordt gemaakt wanneer de Maand in het blad verandert.
- Gebruik Functie: Selecteer de functie die u wilt gebruiken, zoals som, gemiddelde, minimum, maximum of een andere optie. Voor ons voorbeeld hebben we Som geselecteerd.
- Subtotaal toevoegen aan: Vink het vakje aan waar u het subtotaal wilt berekenen. Voor ons voorbeeld hebben we Sales gekozen.
Vink eventueel de vakjes onderaan aan voor de extra items zoals je wilt. Klik OK.”
Je ziet dan dat je gegevens worden bijgewerkt naar de groep en subtotaal de rijen en maak onderaan een eindtotaal. Gebruik de plus-, min- en cijferknoppen om de groepen samen te vouwen of uit te vouwen voor een betere weergave.
Als een ander voorbeeld gebruiken we cijfers van studenten. We gebruiken de functies Subtotaal en Groep om een gemiddeld cijfer voor elke student weer te geven. Dit is het proces.
Selecteer de rijen, ga naar Gegevens en kies “Subtotaal” in het vervolgkeuzemenu Overzicht.
In het vak Subtotaal kiezen we Student in de vervolgkeuzelijst Wijzigen en Gemiddelde in de functielijst. We zullen dan het vakje voor Grade aanvinken. Klik OK.”
We hebben nu onze rijen gegroepeerd op student met een gemiddeld cijfer voor elk en een algemeen gemiddelde onderaan. Nogmaals, je kunt de knoppen aan de linkerkant gebruiken om de groepen samen te vouwen en uit te vouwen.
Als u besluit de rijen te degroeperen nadat u de functie Subtotaal hebt gebruikt, worden de rijen weer normaal. Degenen die de SUBTOTAAL-functie bevatten die door Excel wordt gebruikt, blijven echter voor u om te blijven gebruiken of gewoon te verwijderen als u dat wilt.
De functie Subtotaal kan ingewikkeld worden als u van plan bent veel groepen te gebruiken. Deze basisvoorbeelden zouden je echter op weg moeten helpen als je geïnteresseerd bent in deze functie.
Een ding om op te merken is dat u met deze functie geen subtotaal van een Excel-tabel kunt toevoegen. Als u uw gegevens in een tabel heeft, kunt u de Excel-formule voor SUBTOTAAL invoegen zoals eerder beschreven of uw tabel converteren naar een celbereik om de functie te gebruiken. Kies je voor het laatste, dan verlies je de tabelfunctionaliteit.
Nu u weet hoe u subtotalen in Excel invoegt, kunt u bekijken hoe u dubbele rijen verwijdert.