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.