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.

Excelleer met Excel

Je 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 het programma

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.

Excelleer met Excel

Je 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 het programma

Gerelateerde artikelen voor financials

07-07-2021 13:44

Wat houdt je als leider staande in alle hectiek van vandaag? Moed en lef zijn essentieel, bleek tijd...

07-07-2021 11:54

Waarom floreren sommige ideeën van meet af aan, terwijl andere razendsnel ter ziele gaan? Verbeter d...

07-07-2021 11:23

Zes tips om je invloed te vergroten door effectief stakeholdermanagement.

01-07-2021 14:31

Sijthoff Media heeft het cursusaanbod NBA Opleidingen overgenomen van de Koninklijke Nederlandse Ber...

11-06-2021 10:02

De rol van Power BI, nieuwe features en andere ontwikkelingen in het favoriete gereedschap van finan...

11-06-2021 09:56

De NBA heeft het onderwerp continuïteit verplicht gesteld als onderdeel van de Permanente Educatie i...

03-06-2021 11:12

Als je de gelegenheid krijgt om ergens te spreken, pak die kans dan. Spreken is één van de meest kra...

25-05-2021 10:25

Volgens Susan Swarte (oud-CFO Olympia) is een goed presterend bedrijf nog geen bedrijf dat genoeg op...

25-05-2021 09:59

‘M&A-kanonnen’ Ton van Veen (Jumbo), Brent Wissink (Just Eat TakeAway.com) en Stefan Weda (Aon) over...

25-05-2021 09:03

Op overnamepad? Wees je bewust van deze fiscale aspecten. Het kan je onderneming veel geld schelen.

25-05-2021 08:54

Ondernemers komen vaak in aanraking met de vraag: 'wat is de economische waarde?' Zonder antwoord op...

28-04-2021 14:50

Hoe zorg je er als controller voor dat het management in control is van het proces van waardecreatie...