Tips om jouw Excel-model hufterproof te maken

Lees ook: Het nieuwe rapporteren in Excel

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.

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.

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.