Subtotalen berekenen in Excel; er leiden vele wegen naar Rome…

Gepubliceerd op: 8 mei 2017Geschreven door: Michiel van der Arend

Er zijn in Excel verschillende manieren om in een lijst met data subtotalen te berekenen.

Subtotalen zijn te berekenen met formules (functies) of via ingebouwde knoppen in Excel. Ons uitgangspunt voor de berekeningen is de lijst zoals hieronder is weergegeven:

Subtotalen via ingebouwde Excel knoppen

Een elegante methode is het gebruik van de optie Subtotalen. Hiervoor sorteren we de lijst op de kolom die we als groepeerkolom willen gebruiken (Merk). We klikken in de lijst en gaan naar het menu Gegevens en klikken op de knop Subtotaal. We geven aan welke kolom de groepering bevat, welke berekening we willen uitvoeren en in welke kolom de te berekenen waarden staan.

Dit is dan het resultaat:

Een andere optie is het gebruik van de Tabel. Een Tabel is een object in een Excel blad dat een aantal fijne eigenschappen bezit. Een ervan is dat we erin kunnen filteren en kunnen rekenen.

Klik in de lijst en ga naar menu Invoegen
Klik op de knop Tabel
Controleer of alle gegevens binnen de tabel vallen en klik op OK

Er verschijnt bovenaan in het lintmenu een extra tabblad: Hulpmiddelen voor tabellen. Hierop staat de optie Totaalrij. Vink deze aan.

Klik nu in de totaalrij op de cel onder de kolom die berekent moet worden en kies in de dropdownlijst de gewenste functie. Gebruik het autofilter dat automatisch in de kolomkoppenrij verschijnt om te filteren, en de totaalrij laat de berekende waarde zien.

Subtotalen berekenen met functies

De eerste functie die we gaan bekijken is =SOM.ALS. In deze functie gebruik je drie argumenten: de kolom waarin de gegevens staan waarop je wilt groeperen, het criterium waarop je wilt filteren en tot slot het bereik waarin de op te tellen waarden staan. In onze lijst willen we het totale verkoopbedrag weten van alle Cadillacs. De functie ziet er dan zo uit: =SOM.ALS(A2:A39;”cadillac”;E2:E39).

Lastiger wordt het wanneer we niet alleen op Merk maar ook op Kleur willen filteren: dus het totale verkoopbedrag van alle zwarte Cadillacs. Hier biedt de functie =SOMMEN.ALS uitkomst.

Deze functie ziet er als volgt uit: =SOMMEN.ALS(E2:E39;A2:A39;”cadillac”;C2:C39;”black”).

Zoals je ziet kunnen we nog meer criteria toevoegen: alle zwarte cadillacs met een V8, enz.

Iets omslachtiger is het gebruik van de functie =DBSOM. Deze functie maakt gebruik van een criteriumgebied waarin de kolomkoppen staan van de te filteren kolommen, en daaronder de criteriawaarden. Hier berekenen we het verkoopbedrag van alle zwarte Cadillacs. De naam Database in het eerste argument is een naam die de gehele lijst omvat, kolomkoppen incluis! =DBSOM(Database;E1;M1:N1).

Zetten we de criteria in verschillende rijen, dan berekenen we het verkoopbedrag van alle Cadillacs en van alle andere auto’s, zolang deze maar zwart zijn.

Bekijk ook onze Tips & Tricks trainingen voor MS Office: www.avk.nl/officetips

Het ‘Venster controle’ is met name handig als de formule gebruik maakt van cellen van meerdere werkbladen. Nadat je van die cellen controle hebt toegevoegd, kun je de voortgang van al deze cellen blijven volgen in dit venster.