1. Home
  2. Artikelen
  3. Excel: de kunst van rij in kolom berekeningen

Excel: de kunst van rij in kolom berekeningen


Ik heb Excel nog nooit op een rekenfoutje betrapt. Het is de onkunde van de gebruiker die zorgt voor onbetrouwbare resultaten.

Excel: de kunst van rij in kolom berekeningen

Een serie blogs over Excel en Power BI.

BLOG – In mijn vorige blog heb ik uitgelegd hoe tabellen werken. Nu neem ik je mee in de berekeningen. Vergelijk je berekeningen in Excel met die in Power Pivot of Power BI, dan lijken die hetzelfde. Maar schijn bedriegt: ze verschillen hemelsbreed.

Door Henk Vlootman, adviseur, trainer, spreker en auteur, en topspecialist op het gebied van Microsoft Power BI en Power Query.

Berekeningen vallen uiteen in twee smaken: rijberekeningen in een kolom (lekker verwarrende omschrijving, vind je ook niet?) en aggregaat berekeningen. Met een rijberekening in een kolom voeg je een kolom aan de tabel (of Excel bereik) toe, waarna je de berekenende informatie op rij niveau aanbrengt. Een aggregaat berekening berekent de (totale) waarden uit een kolom of tabel. Tel je bijvoorbeeld een kolom op met de SOM functie dan is het resultaat één getal, die geen onderdeel uitmaakt van een kolom of tabel. In deze blog neem ik je mee naar algemene principes van berekeningen en de rijberekeningen in een kolom. In de volgende blog ga ik verder in op de werking van aggregaat berekeningen. 

Power Pivot of Power BI maakt gebruik van DAX (Dynamic Analyses eXpression) berekeningen en wordt in principe altijd gevisualiseerd in de vorm van een draaitabel of -grafiek. Essentieel hierbij is het besef dat een berekening in een draaitabel los staat van de visualisatie. De berekening gebruik je om te filteren over de rij- en kolom gegevens. En daarmee raak je precies de kern van het verschil in werken tussen Excel en Power Pivot. De basis van het werken met Power Pivot en Power BI is filteren, terwijl de basis van Excel, niet vreemd denk ik zo, bestaat uit berekeningen.

De logische conclusie is dat een draaitabel niet tot het domein van de berekening behoort, maar in de vierde laag: de visualisatie. En nu hoor ik je al brommen: “Maar ik selecteer ook een berekening in mijn Excel draaitabel”. Dat klopt, maar dat komt alleen door de manier waarop klassieke Excel draaitabellen werken.


Training Excel voor Financials

U krijgt als financieel manager of accountant steeds meer gegevens te verwerken. Excel biedt uitmuntende mogelijkheden om in korte tijd relevante analyses te maken, een brij aan gegevens te ordenen en sluitende rapportages aan te leveren (inclusief 2 E-learning modules). 

Bekijk online training

In afbeelding 2 zie je een Excel draaitabel. Hier is gekozen om de som te gebruiken van de kolom Waarde. De klassieke Excel draaitabel kent 11 ingebouwde berekeningen. Deze aggregaat berekeningen gebruik je om vervolgens te filteren, in dit geval over de afdeling. Kijk je naar afbeelding 3, dan zie je dat je in Power Pivot eerst een aggregaat berekening maakt, die de som van de kolom Waarde berekent. Deze berekening gebruik je in de draaitabel (afbeelding 4), waarna je dezelfde filtering op afdeling toevoegt. 


 

In dit type draaitabel is de berekening gescheiden van de draaitabel. Het subtiele, maar indrukwekkende, verschil is dat je iedere aggregaat DAX berekening kan gebruiken in een Power Pivot of Power BI draaitabel. Ongeveer een oneindige plus één hoeveelheid soorten DAX berekeningen tegen de standaard 11 Excel berekeningen. Helaas, de wereld is ongelijk verdeeld. Maar misschien komt mijn ‘onpartijdige’ voorkeur voor een bepaald type draaitabel hier wel enigszins naar voren.

Terug naar de rijberekeningen in een kolom. Kijk eens mee naar de Excel functie =YEAR() in afbeelding 5 en de Power Pivot DAX functie = YEAR() in afbeelding 6. Ogenschijnlijk zien beide berekeningen er hetzelfde uit. Er is wel degelijk een verschil: let maar op de verwijzingen tussen de haakjes open en de haakjes sluiten. Je ziet bij de Excel functie een verwijzing naar cel A2: een kruispunt tussen een kolom en rij en daarmee een specifiek punt in de tabel. In de DAX functie is dit een verwijzing naar een kolom (Table1[Datum]), zonder rij aanduiding. Hiermee wordt meerdere, gelijkwaardige punten in de tabel aangeduid. Dit schijnbaar kleine verschil maakt een wereld van verschil.

Laten we iets dieper duiken in de verschillen tussen de rij in kolom berekeningen in Excel en Power Pivot of Power BI. In de klassieke manier van werken in Excel maak je voor iedere cel in de kolom van de tabel een berekening: is je tabel 5.000 rijen lang, dan maak je even zoveel berekeningen. Een van de meest voorkomende problemen van deze methode is wanneer de tabel meer rijen krijgt, bijvoorbeeld omdat je meer transacties toevoegt. Omdat Excel functies geen flauw benul hebben van de (kolom in de) tabel, worden formules niet automatisch toegevoegd. Dit soort problemen heeft Excel, overigens geheel ten onrechte, een onbetrouwbaar imago gegeven. Berekeningen in Excel op zichzelf zijn goed – ik heb Excel nog nooit op een rekenfoutje betrapt – maar is het de onkunde van de gebruiker, die zorgt voor onbetrouwbare resultaten. Hij of zij vergeet bijvoorbeeld de berekeningen aan te vullen, wanneer de tabel langer is geworden, of voegt extra berekeningen toe aan een individuele formule in de kolom.

Goed, nu de kant van de DAX berekeningen. De berekening gaat over een kolom, vandaar de naam ‘Berekende kolom’. In de formule zie je eerst de verwijzing naar een tabel. Dat betekent dat de DAX functie kennis heeft van de tabel! Groeit de tabel door toegevoegde transacties, dan wordt de berekening automatisch verlengt tot het einde van de kolom. Omdat een DAX functie geen kennis heeft van een cel positie, is het onmogelijk om een specifiek positie te voorzien van een aangepaste berekening. Deze eigenschappen werken gunstig op een consistente opbouw van jouw model.

Traditionele Excel rapportages worden vaak gebouwd in de uitvoer. Helaas is het ombouwen van dit soort rapportages naar Power Pivot of Power BI al snel een brug te ver. Een van de problemen is dat de kolom berekeningen in de uitvoertabel worden gecombineerd met aggregaat berekeningen, waardoor een hybride tabel ontstaat. En een hybride tabel, dat staat Power Pivot of Power BI niet toe. Een voorbeeld hiervan is de berekening van subtotalen. Subtotalen mag en kan je zeker gebruiken, zolang je de rij in kolom en de aggregaat berekeningen gescheiden houdt. Het betekent wel dat je dit soort rapportages van de grond af aan opnieuw moet definiëren en bouwen.

De vraag is niet of je dit soort Excel rapportages moet ombouwen, want dat moet je eigenlijk wel doen. Maar het houdt wel in dat jij moet weten hoe tabellen en berekeningen werken. En daar komt bijna zeker training om de hoek kijken! 


Gerelateerde artikelen voor financials

06-10-2020 14:26

Het waarderen van een bedrijf kan knap lastig zijn, zeker in een periode van crisis. We vroegen een...

30-09-2020 10:44

"Zorg dat je er heel snel bij bent." Ronald Molendijk, spreker op FM Dag 2020.

30-09-2020 10:40

Coronacrisis versnelt ontwikkeling online bedrijfsonderwijs met als voordeel maximale kennisoverdrac...

30-09-2020 10:31

Een geslaagde digitale transformatie is meer dan de digitalisering van bestaande processen, producte...

30-09-2020 08:49

Acht verschillende bedrijfsculturen: in welke werkt u?

08-09-2020 07:55

Sijthoff Media heeft opleidingsinstituut, community-bouwer en uitgeverij Alex van Groningen overgeno...

29-07-2020 07:46

Hoe zorg je ervoor dat jij en je team komen tot de best mogelijke beslissingen?

28-07-2020 08:15

Modelleren en berekenen, Power BI en DAX: webinar van Excel MVP Tony de Jonker.

27-07-2020 14:48

Wil je hufterproof Excel-modellen maken dan moet je een andere mindsetting van werken in Excel aanme...

27-07-2020 14:38

Data is het nieuwe goud, maar aggregaat berekeningen in Excel laten dat goud glanzen.

27-07-2020 14:24

Ik heb Excel nog nooit op een rekenfoutje betrapt. Het is de onkunde van de gebruiker die zorgt voor...

27-07-2020 13:27

Kijk voorbij de dagelijkse eisen en ontdek de onbekende meerwaarde van Excel.