1. Home
  2. Artikelen
  3. Tips om jouw Excel-model hufterproof te maken

Tips om jouw Excel-model hufterproof te maken


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

Tips om jouw Excel-model hufterproof te maken

Zoals je ondertussen vast wel weet ben ik nogal betrokken bij de nieuwe gereedschappen in Excel, ook wel “New school Excel” genoemd. Dat wil niet zeggen dat ik niet bezig ben met de “Old School Excel” gereedschappen. Vandaag wil ik je, via die “Old school” gereedschappen, meenemen naar wat je nodig hebt om een hufterproof Excel model te maken. 

Bereid je maar voor: wil je modellen hufterproof maken, dan heb je een andere mindset nodig van werken in Excel. Eerst gaan we de theorie bekijken en daarna maken we een klein model. Voor deze blog gebruik je de volgende Excel wetten:
-1. Los alles -maar dan ook alles- op met berekeningen,
2. Hanteer de Excel gebruiker en ontwerper/bouwer rollen,
3. Gebruik namen in formules en
4. Redundantie is nooit toegestaan.

Maar wat betekenen deze wetten?  
De 1e Wet gaat over hoe je Excel voor jouw laat werken. Dit is de meest simpele wet, die je kunt bedenken: Excel is een rekenprogramma. Alles wat je berekent oplost, doet Excel voor jou. Alles wat niet berekent oplost, doe jij voor Excel! Zorg er dan ook voor dat je alles, maar dan ook echt alles, berekent oplost. 

De 2e wet gaat over de twee verschillende rollen, die je als Excel enthousiast en beoefenaar bezit. Ben je ontwerper/ bouwer, dan ben je geïnteresseerd in hoe je tot het resultaat komt, je houdt je bezig met de ketting van invoer, via berekeningen naar de uitvoer. Jij garandeert, als ontwerper/ bouwer dat de gebruiker het juiste resultaat ziet! Zit je in de rol van gebruiker, dan beoordeel jij het resultaat. Je analyseert bijvoorbeeld of het getoonde bedrag past in het budget. Bedenk wel dat je bijna altijd beide rollen bezit. De echte kunst van Excel is dat jij, in je rol als ontwerper/ bouwer, ervoor zorgt dat de gebruiker zo min mogelijk hoeft te doen. 

De 3e Wet heeft te maken met de hoe je jouw model structureert. Namen is een van de minst gebruikte functionaliteit in Excel, maar zonder het gebruik van namen ben ik niet in staat om een fatsoenlijk model af te leveren! In het onderstaande voorbeeld maak je een aantal namen aan en gebruik je die in formules.

De 4e wet zorgt voor efficiëntie in jouw model. Redundantie is het dubbel voorkomen van gegevens. Heb je een dashboard gemaakt over Januari en kopieer je het werkblad met alle formules naar een volgend werkblad met de naam Februari, dan heb je redundantie. Het lijkt een snelle en handige methode, maar je hebt op twee plaatsen identieke formules. Het model wordt trager, omdat Excel dubbel rekent. Daarnaast heb je meer kans op fouten, omdat je meerdere malen dezelfde formules gebruikt. In December heb je 12 maal redundantie! Probeer je redundantie te voorkomen, dan maak je compacte en overzichtelijke modellen.


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 ons voorbeeld ga je een uitvoer sjabloon maken die de waarde van de gekozen maand toont. Geen 12 bladen, maar in plaats daarvan een cel, waarmee je de gewenste maand kiest. Je bent, als ontwerper/ bouwer, geïnteresseerd in de ketting van berekeningen. Doordat de gebruiker alleen de maand kiest, is het maken van fouten niet mogelijk. 

In afbeelding 1 zie je de twee bereiken, waarmee we in dit voorbeeld gaan werken. Wat je wilt is dat je uit de lijst van maanden een maand selecteert en dat vervolgens Excel het totaal van het bedrag in die maand berekent. 

Aan de slag: je hebt nog een paar extra cel posities nodig. Om te beginnen leg je de keuze van de gebruiker vast. Daarvoor kies je cel J4. Daarna sla je het resultaat van de keuze van de gebruiker op: cel G18 is daarvoor jouw keuze. Cel J7 toont het resultaat van de berekeningen aan de gebruiker. Maak duidelijke kopteksten, zodat je weet waarvoor de diverse cellen dienen. Het resultaat zie je in afbeelding 2.

De 1e bouwstap is het definiëren van de namen. Je gaat de diverse cellen voorzien van namen. Namen hebben veel voordelen. Het belangrijkste voordeel is dat namen jouw formules beter leesbaar maken. Selecteer het bereik G4 tot en met G15 en klik in het naamvak (afbeelding 3). Typ de naam LstMaanden (Lst staat voor lijst). Vergeet niet op de Entertoets te drukken, anders houdt Excel de naam niet vast. Nu maak je cel J4 actief en herhaal je de voorafgaande handelingen. Je geeft deze cel de naam VarMaand (Var staat voor Variabele).  Cel G18 geef je op dezelfde manier de naam ResMaand (waar Res staat voor Resultaat). Als laatste stap geef je twee bereiken een naam: de cellen C4 tot en met C14 geef je de naam LstMaandNr en het bereik D4 tot en met D14krijgt de naam LstBedrag. In afbeelding 4 zie je de namen met de bijbehorende bereiken, zoals je die hebt gedefinieerd.

In de 2e bouwstap stel je de gebruikersinvoer vast. Je gebruikt cel J2, met de naam VarMaand. Je kiest in het tabblad Gegevens, de sectie Hulpmiddelen voor gegevens voor de knop Gegevensvalidatie. Je vult het tabblad Instellingen in zoals je ziet in afbeelding 5. Je maakt de cel actief en je kiest een maand uit de lijst met de naam LstMaand. Je selecteert bijvoorbeeld de maand Maart. 

In de 3e bouwstap wil je berekenen wat het resultaat van de keuze van de gebruiker is. Krijg je het resultaat terug als een (maand)nummer, dan ben je in een keer klaar. Je hebt hiervoor cel G18 uitgekozen. In deze cel gebruik je de functie VERGELIJKEN. In afbeelding 6 zie je de argumenten van deze functie. De zoekwaarde is de VarMaand, de functie zoekt in de LstMaanden en je wilt een exacte match (criteriumtype_getal = 0). Wat doet nu deze functie? Zij geeft de positie van de gekozen maand uit de lijst terug. De maand Maart heeft de derde positie in de lijsten geeft dus het maandnummer 3 terug. Zolang de lijst in de juiste volgorde blijft, geeft iedere keuze van de gebruiker het juiste maand nummer terug. 

De 4e bouwstap is tevens de afronding van het model. In cel J7 maak je de functie SOMMEN.ALS. Hoe je deze functie invult zie je in abeelding 7. Je gebruikt hier weer de waarde uit de cel LstMaandNr op de bedragen uit LstBedrag op te tellen, op basis van het, door de gebruiker aan te passen, maandnummer.

Het leuke van deze constructie is dat je de gebruiker alleen de mogelijkheid geeft om een maand te kiezen. Vervolgens wordt het gehele model doorgerekend. Het model gebruikt twee keer dezelfde lijst (LstMaand); de eerste keer om de gebruiker de keuze van de maand te geven en de tweede keer om de gebruikerskeuze om te zetten in het maand nummer. Daarmee vermijdt je redundantie. Omdat het resultaat wordt berekend kan het resultaat nooit fout zijn. En eigenlijk leidt dit weer tot de laatste twee Excel wetten:

Denk vanuit berekeningen, niet vanuit wat je wilt zien in het dashboard. Bedenk dat je vanuit berekeningen ieder gewenst dashboard maakt, zonder dat Excel  in het dashboard rekent. 

Excel is naar mijn weten nog nooit betrapt op een foute berekening. Fouten in Excel modellen ontstaan door menselijk handelen, zoals knippen en plakken, formules aanpassen enzovoorts. Hoe minder handelingen je in je rol als gebruiker doet, hoe betrouwbaarder het resultaat van jouw model is.


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...