1. Home
  2. Artikelen
  3. Transformeer jouw Excel model

Transformeer jouw Excel model


Excel modellen ontstaan organisch. Ik ken bijna geen model, dat in een keer is gemaakt. De foutgevoeligheid ligt hierdoor hoog.

Transformeer jouw Excel model

Lees ook: Het nieuwe rapporteren in Excel

Je begin een model met een vraagstelling en in de loop van de tijd wordt het model alleen maar meer uitgebreid met toegevoegde of aangepaste vraagstellingen. De kunst van Excel is dat je dat proces beheerst. En daar schort het nog weleens aan. Het gevolg is dat modellen traag en onbeheersbaar worden. De oplossing van dat probleem is overigens niet altijd eenvoudig.
Wil je snelle en beheersbare Excel modellen bouwen? Een startpunt vind je mogelijk in de volgende tips:

1. Excel is een rekenprogramma. Toch maken veel Excel ontwerpers de fout om te werken vanuit de uitvoer. Wil je echt de kracht van Excel gebruiken, dan werk je vanuit de berekeningen. 

2. Weet wat voor soort informatie je hebt. Dat klinkt misschien cryptisch, maar als je in een cel een tekst of een getal ziet, dan behoort dat getal bij een soort informatie. De belangrijkste drie soorten zijn invoer, berekeningen en uitvoer.

3. Probeer meer met minder berekeningen

Laten we eerst tip 1 bekijken. De klassieke valkuil is dat een mix van soorten informatie gebruikt wordt in de uitvoer. Dat lijkt handig voor controles en correcties. Maar doordat invoer en berekeningen door elkaar heen lopen, ben je gedwongen de controles handmatig te doen. Dat kost (heel) veel tijd en staat ook niet altijd voor het juiste resultaat. De maand daarna wordt het blad gekopieerd en het nieuwe blad wordt het resultaat voor Februari. De ontwerper beseft niet dat het aantal berekeningen nu verdubbeld is. Dus in een jaar heb je al 11 x zo veel berekeningen, dan noodzakelijk is. Daar wordt Excel traag van, worden ‘cross month’ analyses moeilijk en zijn de controles lastiger.

Werk je vanuit de berekeningen, dan staat alle informatie in een bereik. Met een draaitabel en een slicer kiest de gebruiker de gewenste maand en klaar ben je. Wil je baas toch een statisch overzicht, dan gebruik je technieken zoals besturingselementen om de gewenste maand te selecteren. Je houdt uiteindelijk, maar een berekening over, die de gewenste maandcijfers toont. Dat betekent wel dat je jouw mindsetting ten aanzien van het modelleren in Excel moet bijstellen!

Tip 2 is bedoeld om structuur te geven aan jouw model. Doordat je de een strikte scheiding tussen de verschillende soorten data hanteert, krijg je meer grip op het model. De controles richtten zich dan op de invoer en de uitvoer: Excel heeft namelijk nog nooit een rekenfout gemaakt! Ook hier moet je stil staan bij jouw mindsetting over hoe je modelleert in Excel.

Tip 3 probeert je duidelijk te verschaffen over hoe je berekening inzet in jouw Excel model. Wist je dat Excel standaard alles doorrekent?  Dat wil zeggen, zodra je de invoer in een cel verlaat worden de berekeningen opnieuw berekent. En dat maakt een (uitgebreid) model traag. Je versnelt jouw model door kritisch naar de berekeningen te kijken en te proberen met minder berekeningen hetzelfde resultaat te behalen. Het nieuwe gereedschap in Excel, Gegevens ophalen en transformeren, is daar heel geschikt voor. 

Bekijk het volgende voorbeeld. 
Je wilt de datums vanaf 1-1-1990 tot en met 1-1-2018 gebruiken. Die datums staan in een bereik dat loopt vanaf cel A2 (cel A1 bevat de koptekst Datum) tot en met cel A10593 (afbeelding 1). 

Je hebt daarvan in de kolommen B en C de maanden en de jaren nodig. Dus maakt je in cel B2 de formule = MAAND(A2) en in cel C2 de formule JAAR(A2). Je selecteer beide formules en je dubbelklikt op de vulgreep. De formules worden naar het eind van het bereik gekopieerd. Snel geregeld, nietwaar. Maar bedenk dat je nu 21.184 berekeningen hebt gemaakt! Voeg je een jaar aan datums toe, dan krijg je niet alleen meer berekeningen, maar moet je ook opletten dat de formules aangevuld worden tot het eind van het nieuwe bereik. De tabelfunctionaliteit helpt je daar wel bij.

Wil je minder formules, dan maak je gebruik van de Excel gereedschap Gegevens ophalen en transformeren. Je onderneemt de volgende stappen. 
● Je klik, terwijl de actieve cel in het bereik staat, in het tabblad Gegevens, sectie Gegevens ophalen en transformeren, op de knop Van tabelbereik (afbeelding 2).
● Je kiest voor de knop OK in het venster Tabel maken (afbeelding 3)
● Daarna kies je in de Query editor voor het tabblad Kolom toevoegen, sectie Datum en tijd, de knop Datum, de optie Maand en vervolgens opnieuw maand (afbeelding 4)
● Je selecteert opnieuw de kolom Datum en je herhaalt de stappen, maar je kiest nu voor Jaar en de Jaar (afbeelding 5).



Je hebt nu dezelfde tabel die je hebt gemaakt met de Excel berekeningen, maar deze staat nog niet in Excel. Daarvoor klik je in het tabblad Start, sectie Sluiten op de bovenkant van de knop Sluiten en laden (afbeelding 6).

Klaar! Kijk je in de formule balk naar nieuwe tabel, dan zie je dat de kolommen B en C geen berekeningen bevat! Geloof me, dat versnelt echt jouw model.

Voeg je nu jaar toe, dan hoef je, nadat je de bron tabel hebt bijgewerkt, alleen maar in het tabblad Gegevens, sectie Query en verbindingen op de knop Alles vernieuwen te klikken (afbeelding 7) en je uitvoer tabel wordt gelijk aangepast. Veel minder berekeningen en minder controles op de werking, want geen formules kopiëren. 

Wil je echt een Excel professional worden, investeer dan in trainingen Ophalen en transformeren. Dat is voor mij echt de beste investering, die ik mij momenteel op het gebied van Excel kan bedenken.

Henk Vlootman geeft sinds 1994 Excel en Power BI trainingen aan managers over de gehele wereld. Vanaf 2013 ontving Henk jaarlijks de Microsoft MVP (Most Valuable Professional) award. Henk is de auteur van ‘Excelmodellen voor financiële economische informatie’ en ‘ExcelLeren, de Vlootman methode in de praktijk’ en medeauteur van het ‘Praktijkboek Powerpivot in Excel’ en ‘Handboek Power pivot’. Tijdens Excel en Power BI congressen en conferenties is hij een gewaardeerd spreker. Henk is voorzitter van de stichting Power BI gebruikersgroep Nederland.


Gerelateerde artikelen

Vier pijlers voor betere besluiten

29-07-2020 07:46

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

5 stappen in Power BI: Stap 2 Modelleren en bereke...

28-07-2020 08:15

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

Tips om jouw Excel-model hufterproof te maken

27-07-2020 14:48

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

Explosieve kracht in een cel: aggregaat berekening...

27-07-2020 14:38

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

Excel: de kunst van rij in kolom berekeningen

27-07-2020 14:24

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

Hoe laat je Excel leven?

27-07-2020 13:27

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

Hoe Power BI je helpt op koers te blijven

27-07-2020 13:12

Hoe Power BI je helpt op koers te blijven

Overstappen van Excel naar Power BI?

27-07-2020 13:05

Power BI is hot. Voor nog geen tientje per gebruiker per maand, state of the art rapportage software...

De zeven pijlers van control voor morgen

23-07-2020 13:29

Wat moet je echt beheersen om als controller mee te kunnen blijven doen? Digitalisering, globaliser...

Hoe krijg je grip op verbonden partijen?

10-07-2020 08:14

Nieuwe methode om tijdig te signaleren hoe ernstig de situatie is: risicogestuurd grip. Een serie...

Veerkracht control, voor echte meerwaarde

10-07-2020 07:58

Als wáre businesspartner van het management biedt de controller veerkracht: wendbaarheid plus weerba...

Overname? Emoties kosten geld

08-07-2020 08:48

"Na de transactie moet er worden gewerkt om de krachten te bundelen." Evert Oosterhuis, partner Inte...