Aggregaat



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!


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?


Geef een antwoord

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *