Tagarchief: Subtotaal

Aggregaat

Excel,
Voorbeeldbestand: Aggregaat.xlsx

Regelmatig voorziet Microsoft zijn software van nieuwe functies of andere nuttige (?) uitbreidingen.
Al een poosje hebben Excel-gebruikers de beschikking over de functie AGGREGAAT; de opvolger van Subtotaal.

Na een korte toelichting op deze “nieuwe” functie zullen we inzoomen op één van de belangrijkste pluspunten hiervan: het kunnen omgaan met foutwaarden.

Inleiding

In het Voorbeeldbestand staat in het tabblad Data een kleine tabel met datums, regio’s en bedragen. Zoals te zien is staat er bij 6 januari een foutmelding in de kolom Bedrag.

Wanneer we op basis van deze tabel een draaitabel maken, dan zien we dat Excel automatisch als Waardeinstelling de optie Aantal heeft gekozen (en dat ie de datums automatisch tot maanden heeft gegroepeerd); zie bovenste draaitabel.

Uiteraard kunnen we handmatig de Waardeinstelling veranderen; in de 2e draaitabel is deze gewijzigd in Som. Helaas: niet ieder vakje is nu gevuld met een bedrag. De foutmelding in de bron verhindert dat. Ditzelfde geldt als we de instelling veranderen in bijvoorbeeld Max of Min (zie het Voorbeeldbestand).

Maar misschien kunnen we een andere Excel-optie of -functie gebruiken, zodat we dit probleem kunnen omzeilen?

Subtotaal

Aan deze functie hebben we al eens eerder een artikel gewijd.

Deze Excel-functie bestaat eigenlijk uit 11 verschillende functies (waarvan Gemiddelde, Som, Max, Min en Aantalarg het meest gebruikt worden). Ook kun je door het juiste gebruik van Functie_getallen verborgen waarden al dan niet meenemen (zie het betreffende artikel).

In het tabblad Subtotaal van het Voorbeeldbestand kun je door het aanpassen van cel F3 zien wat het resultaat van de functie Subtotaal is.
Ook hier zullen Som, Min etc foutmeldingen genereren.

Bekijk wat het resultaat is als je in het tabblad Data de groepering sluit ( of een filtering aanbrengt in de tabel met gegevens.
Wijzig ook cel D19 in =115/1 en bekijk de consequenties voor de functie Subtotaal.

Aggregaat

De functie Aggregaat wordt door Microsoft als vervanger voor Subtotaal gepositioneerd. Deze laatste is alleen vanwege compatibiliteit in de gereedschapskist blijven zitten.

In principe kent Aggregaat 2 verschijningsvormen () waarbij we ons hier op de eerste zullen concentreren.

Aan het overzicht op het tabblad Aggregaat van het Voorbeeldbestand is te zien dat het aantal Functies (de eerste parameter) is uitgebreid tot 19.

Dus bijvoorbeeld ook mediaan- en percentiel-berekeningen zijn nu met behulp van deze functie mogelijk.

Maar de 2e parameter is zeker zo interessant: hiermee kunnen we bijvoorbeeld aangeven of foutwaardes en/of verborgen rijen moeten worden genegeerd et cetera.

Als derde parameter geven we het bereik mee, waarop de berekening moet worden uitgevoerd. De vierde parameter wordt alleen gebruikt vanaf de Functie_getallen 14 en hoger.

In het eerste voorbeeld wordt de Som van de diverse kolommen bepaald, waarbij wel of niet de Foutwaarden worden genegeerd.

NB in de formule in cel F4 is de 2e parameter leeg gelaten; dit is hetzelfde als wanneer je daar een 0 als optie zou gebruiken.

LET OP LET OP LET OP LET OP de formules in regel 7 bevatten een 4e parameter. Maar wanneer het Functie_getal kleiner is dan 14 dan wordt deze parameter gezien als een extra bereik; dit bereik wordt dan bij de berekeningen van bijvoorbeeld de Som meegenomen!

In het tweede voorbeeld zal Excel de Grootste waarde in de kolommen opzoeken; dit kan natuurlijk alleen voor getallen (en dus ook datums). Vul je in cel E7 de waarde 2 in dan wordt de één na grootste waarde opgehaald.

Audit

De Aggregaat-functie kan ook goed gebruikt worden bij audit-werkzaamheden of bij de beveiliging van je Excel-systemen. In cel E12 wordt gecontroleerd of de kolom uit E11 foutwaardes bevat of niet:

=ALS(AGGREGAAT(3;0;INDIRECT(“tblData[“&E11&”]”))=AGGREGAAT(3;6;INDIRECT(“tblData[“&E11&”]”));”Geen fouten”;”Fouten gevonden!”)

Cel E12 heeft een voorwaardelijke opmaak.

Aggregaat met complexe bereiken

Op het tabblad Aggr2 van het Voorbeeldbestand staan wat voorbeelden waarmee (in dit geval) het maximale bedrag wordt bepaald.

Zolang er in de kolom Bedrag van de data-tabel fouten voor komen zal de formule in C4 geen resultaat weergeven. Wijzig ter controle cel D19 in het tabblad Data in bijvoorbeeld =115/1.

NB de formule in C4 is niet met Enter afgesloten maar met Ctrl-Shift-Enter; Excel ‘weet’ dan dat het een matrix- of CSE-berekening betreft. Excel plaatst zelf de accolades rond de formule.

In cel C5 wordt het maximum-onder-voorwaarden berekent met de fiormule:

=MAX.ALS.VOORWAARDEN(tblData[Bedrag];tblData[Regio];C2)

Zolang er als Regio niet Oost wordt gekozen levert deze formule een maximum-bedrag op.

De formule in cel C6:

=AGGREGAAT(14;3;tblData[Bedrag]*(tblData[Regio]=C2);1)

berekent altijd een maximale waarde.

LET OP voor het bepalen van het minimum-bedrag moet in de Aggregaat-functie niet alleen de 14 in 15 veranderd worden, ook de 3e parameter heeft een aanpassing ondergaan:
=AGGREGAAT(15;3;tblData[Bedrag]/
(tblData[Regio]=C2);1)

Met behulp van Formule evalueren kun je waarschijnlijk zelf bedenken waarom dat nodig is.

LET OP het gebruik van complexe bereiken is alleen toegestaan bij Functie_getallen 14 en hoger; anders krijgt u de foutmelding #Waarde!

Subtotaal

Excel, , , ,
Voorbeeldbestand: Subtotaal.xlsxDownload

Subtotalen: in de dagelijkse praktijk zijn we meestal niet alleen geïnteresseerd in totalen, maar willen we ook aantallen, bedragen etc. zien per jaar of per maand, per afdeling of per soort of ….
Subtotalen dus.

Eigenlijk vind ik, dat er maar één goede methode is om subtotalen te bepalen en dat is met behulp van draaitabellen. Bij de meeste andere methodes worden namelijk de basis-gegevens aangepast en dat druist in tegen goed Excel-gebruik.

Wanneer een (sub)totaal moet wijzigen, als er regels in de bron-data worden verborgen (maar wie wil dat nou?), alleen dan zul je de functie SUBTOTAAL moeten gebruiken.

Maar omdat het altijd goed is om meerdere alternatieve methodes te kennen, volgt hieronder een uitleg van verschillende manieren om subtotalen te genereren.

Brongegevens

In het Voorbeeldbestand op het tabblad Data staat een serie bedragen (200 regels), waarbij ieder Bedrag drie kenmerken heeft: het Jaar, de Maand en een Regio.

Het totaal van de bedragen kunnen we snel vinden door de gehele kolom te selecteren (klik op de betreffende kolom-letter, in dit geval E) en kijk rechtsonder in de statusbalk:
Afhankelijk van de Excel-versie zie je tegelijkertijd ook het gemiddelde, aantal enz. of je kunt deze oproepen door op het vinkje te klikken.

Maar wanneer je alleen het totaal van 2015 wilt weten of van de regio noord dan wordt het wat ingewikkelder: eerst sorteren op de betreffende kolom, dan alle bedragen van het jaar of regio selecteren en dan onderaan het subtotaal aflezen. Maar ondertussen hebben we iets met onze brongegevens gedaan (namelijk gesorteerd) en dat willen we niet; er kan altijd iets mis gaan bij zo’n activiteit.

En willen we het totaal van een ander jaar of andere regio dan moeten we opnieuw beginnen. Dat moet dus anders kunnen.

Draaitabel

Wat te doen?

  1. selecteer één van de cellen van de brongegevens
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. kies in het vervolgscherm OK
  4. sleep het veld Jaar naar het Rijlabels-gebied
  5. sleep het veld Bedrag naar het Waardegebied

En we hebben een overzicht van het totaalbedrag en subtotalen per jaar.

Maar als we nu toch bezig zijn, dan kunnen we dit overzicht nog wel wat aanpassen:

  1. sleep het Jaar naar het Kolomslabels-gebied
  2. sleep het veld Maand naar het Rijlabels-gebied
  3. sleep het veld Bedrag voor de tweede keer naar het Waardegebied
  4. klik op de 2e Som van Bedrag en wijzig bij Waardeveldinstellingen de Som in Gemiddeld
  5. sleep Waarden naar het Rijlabels-gebied

Dus door het simpel verslepen van velden kunnen we Excel snel diverse totalen en subtotalen laten bepalen.

Filter

De eerste alternatieve methode, die me invalt, is het gebruik van filters:

  1. selecteer een cel in het bronbestand
  2. kies in de menutab Gegevens in het blok Sorteren en filteren de optie Filter
  3. maak via de ‘vinkjes’ in de koppen de gewenste selectie
  4. selecteer alle overblijvende bedragen en onder in de statusbalk komt de som (en/of gemiddelde enzovoort) tevoorschijn

Zie het tabblad Filter in het Voorbeeldbestand; zoals daar te zien is trekken de functies SOM, GEMIDDELDE etc. zich niets aan van een filtering.
Dus dit is geen structurele oplossing.

SOM.ALS

Met behulp van ALS-formules kunnen wel subtotalen bepaald worden. In cel E3 in het tabblad Als van het Voorbeeldbestand staat de volgende formule: =SOM.ALS(B7:B206;E2;E7:E206)

Dit betekent, dat als er in het bereik B7:B206 de waarde uit cel E2 staat (in dit geval 2015) dan mag de overeenkomende waarde uit kolom E meegeteld worden.

Hetzelfde idee gaat op voor Aantal, Gemiddelde etc.

LET OP ook deze formules trekken zich niets aan van een mogelijke filtering van de brongegevens.

Wil je een iets complexer subtotaal (bijvoorbeeld van 1 jaar slechts het totaal van 1 maand) dan komt de volgende formule in beeld: =SOMMEN.ALS(E7:E206;B7:B206;E2;C7:C206;F2)

NB misschien wat verwarrend, maar de volgorde van de parameters is net wat anders; zie ook het artikel Tellen-met-voorwaarden.

Excel-tabel

De vorige ALS-formules werken nog makkelijker met een Excel-tabel (zie het tabblad AlsTabel in het Voorbeeldbestand):

  1. selecteer een cel in het bronbestand
  2. kies in de menutab Invoegen in het blok Tabellen de optie Tabel
  3. zorg dat het vinkje aanstaat bij kopregel en klik op OK

De formule om het subtotaal voor 2015/feb te bepalen wordt dan:

=SOMMEN.ALS(tblData[Bedrag];tblData[Jaar];JrSel;tblData[Maand];MndSel)

Hierbij hebben de invoercellen voor het jaar en de maand de namen JrSel, respectievelijk MndSel gekregen.

Ook hier geldt weer, dat filteren in de tabel geen invloed heeft op de formules (al zou het voorbeeld hierboven anders doen vermoeden; wijzig de filtering maar eens!).

Maar de Excel-tabel kent wel een totaliseer-optie, die rekening houdt met filtering:

  1. selecteer een cel in de Excel-tabel
  2. kies in de nieuwe menutab Hulpmiddelen voor tabellen/Ontwerpen  in het blok Opties voor tabelstijlen de optie Totaalrij.

In de cel in de onderste regel in de Bedrag-kolom komt nu automatisch de formule =SUBTOTAAL(109;[Bedrag]); het totaal van de gefilterde bedragen.

In de cel daarvoor is ‘handmatig’ de formule =SUBTOTAAL(101;[Bedrag]) geplaatst; deze zorgt voor het gemiddelde van de zichtbare bedragen (zie hierna voor de betekenis van de codes 109 en 101).

NB probeer het effect uit van de dubbele vinkjes in de onderste regel van de tabel.

SUBTOTAAL

De subtotaal-functionaliteit hoeft niet beperkt te blijven tot Excel-tabellen.
Nee, hebt u een database met gegevens dan kan Excel ook op de volgende manier een subtotalen-overzicht genereren:

  1. selecteer een cel in het bronbestand
  2. kies in de menutab Gegevens in het blok Overzicht de optie Subtotaal
  3. in het vervolgscherm kan worden aangegeven voor welk veld er subtotalen moeten komen (Bij iedere wijziging in), u kunt aangeven of u de som of gemiddelde wilt zien (of nog wat anders) en van welk veld u de som wilt zien.
  4. de drie onderste vinkjes spreken voor zich
  5. en klik op OK

Het resultaat is een brij van details en totalen (zie het tabblad Subtotaal in het Voorbeeldbestand).

LET OP Bij het gebruik van deze functionaliteit is het van groot belang, dat de bron-gegevens gesorteerd staan! En wel op de kolom, waarvan de subtotalen bepaald moeten worden.

In het tabblad SubTot2 zijn de gegevens eerste gesorteerd op Jaar en pas toen zijn op bovenstaande manier subtotalen bepaald.

Excel groepeert automatisch alle bij elkaar behorende regels; gebruik de + en – knoppen om meer of minder details te zien. Ook kun je gebruik maken van de cijfers linksboven (in dit geval 1, 2 en 3; er zijn drie niveau’s: totaal, subtotaal per jaar en detail).

NB als de sortering op meer dan 1 kolom is doorgevoerd dan kunnen ook op meerdere niveaus subtotalen worden bepaald. Als bijvoorbeeld binnen het jaar ook op de maand is gesorteerd, dan kunnen eerst subtotalen voor het maandniveau bepaald worden en daarna op jaar-niveau (vergeet niet het vinkje uit te zetten bij Huidige subtotalen vervangen!).

In cel E71 heeft Excel automatisch de formule =SUBTOTAAL(9;E3:E70) geplaatst. De formules voor de andere jaren zijn vergelijkbaar. Voor het totaal in E206 staat echter de formule =SUBTOTAAL(9;E3:E204).
Excel laat tussenliggende subtotalen dus automatisch buiten de berekening!

Ook kunnen meerdere soorten subtotalen onder elkaar geplaatst worden; zie het tabblad SubTot3 in het Voorbeeldbestand.

Ook nu is het zaak om niet te vergeten het vinkje weg te halen bij Huidige subtotalen vervangen.

SUBTOTAAL 2

Maar de functie Subtotaal kunt u ook zelf overal in een sheet plaatsen.
De functie kent in principe 2 parameters: de eerste (het functiegetal) geeft aan welke bewerking moet worden uitgevoerd (som, gemiddelde, aantal) en de tweede welk bereik bij de berekening moet worden meegenomen.
Excel kent 11 soorten berekeningen (zie het tabblad SubTot4a in het Voorbeeldbestand), waarvan het bepalen van het gemiddelde (functiegetal=1), aantal (functiegetal=2) en som (functiegetal=9) de meest gebruikte zijn.

Het functiegetal kan ook 100 groter gekozen worden; het verschil zit hem er in, dat in het tweede geval verborgen regels niet meetellen!

Bekijk op het tabblad SubTot4a het effect van het wijzigen van het functiegetal in regel 40.

Filteren op een of meerdere kolommen heeft op allebei de soorten functiegetallen hetzelfde effect: uitgefilterde waarden worden niet meegeteld.
Het groeperen van regels heeft voor de subtotalen hetzelfde resultaat als het verbergen van regels.
Bekijk het effect van de 3 bewerkingen (filteren, groeperen en verbergen) in het voorbeeld op het tabblad SubTot4b.

LET OP Blijkbaar is dit zo ingewikkeld dat Excel bij het aan- en uitzetten van bewerkingen af en toe de verkeerde resultaten oplevert!!

NB het groeperen van regels heeft verreweg de voorkeur boven het verbergen van regels. In het tweede geval komt het nogal eens voor dat je niet in de gaten hebt dat er regels ‘weg zijn’.