Betere modellen in Excel

Bekijk het onderzoek van Market Watch hier…

Kleine foutjes in een Excelmodel kunnen enorme gevolgen hebben. Sommigen vinden dat een reden om Excel te willen uitbannen. Terecht? Ik denk van niet. De oplossing ligt enerzijds in een goede structuur en goede discipline die worden ondersteund door nieuwe mogelijkheden in Excel. Anderzijds moet duidelijk zijn wanneer Excel niet meer de juiste tool is om het werk te doen, en moet gemakkelijk de stap kunnen worden gezet naar een professioneel beheerde omgeving. In twee artikelen zal ik deze twee kanten van de oplossing bespreken.

In het algemeen bestaat een Excelmodel uit vier processtappen: gegevens invoeren of inladen, berekeningen doen over die gegevens, de resultaten weergeven, en tenslotte die uitvoer delen met anderen. De eerste twee stappen zijn het domein van de ontwerper en bouwer van het model, en de andere twee vallen onder het gebruiken van het model. Fouten ontstaan vaak doordat deze rollen door elkaar gaan lopen.

Zo kan het in een slecht ontworpen Excelmodel voorkomen dat een gebruiker denkt aanpassingen te doen in de uitvoer, maar ongemerkt brongegevens aanpast. Wat ik vaak mis zie gaan, is het verversen van de brondata. Dit is in feite een activiteit voor de gebruiker van het model, maar vaak moet die gebruiker zich als bouwer gaan gedragen en ‘onder de motorkap’ van het model aan de slag gaan. Vergelijk het met een auto: ik kan als bestuurder nieuwe benzine tanken, maar het zou niet best zijn als ik daarvoor de motorkap moest openen, slangen loskoppelen en bougies verwijderen (ik heb weinig verstand van auto’s, behalve als bestuurder)…

Professionele Excel-consultants houden de vier stappen in het leven van een Excel-model strikt gescheiden, bijvoorbeeld door ze in aparte werkbladen op te nemen. Dit is te doen, maar het vraagt wel een gedisciplineerde manier van werken. Excel dwingt de scheiding tussen invoer, berekeningen, uitvoer en delen niet af. Eén bron van fouten is in Excel moeilijk op te lossen: voor elke cel waar het resultaat van een berekening moet staan, is een eigen kopie van een formule nodig. Dit betekent dat een complex Excel-model al snel heel veel formules bevat, en daarmee veel plekken waar fouten kunnen optreden.

De nieuwe onderdelen in Excel helpen je wel om een goede scheiding aan te brengen tussen de verschillende stappen. PowerPivot in Excel (vanaf Excel 2010) is typisch het domein van de ontwerper en bouwer van een model, en gebruik je voor het inladen van gegevens en het definiëren van berekeningen. Kenmerkend voor het inladen van data in PowerPivot is dat je binnen het model de gegevens niet individueel kunt aanpassen. Er kunnen dus geen fouten in de gegevens sluipen, uiteindelijk zijn de gegevens altijd herleidbaar naar de bron.

Berekeningen in PowerPivot worden gedefinieerd met behulp van de DAX-functietaal, en hoeven maar één keer gemaakt te worden voor het hele model. Hiermee breng je zo een berekening van soms duizenden kopieën van een formule terug naar één. De kans op fouten is hierdoor een stuk kleiner, en bovendien is het veel gemakkelijker om te controleren hoe een bepaald resultaat is ontstaan. Zo creëer je vertrouwen in de uitkomsten van je model.

Voor de uitvoer van een PowerPivot-model heb je verschillende mogelijkheden, zoals draaitabellen en grafieken in Excel, maar ook Power View waarmee je heel gemakkelijk interactieve rapporten en dashboards kunt maken. Bij het delen van een model met anderen binnen de organisatie is het belangrijkste principe dat je geen kopieën van het model creëert. Dit doe je bijvoorbeeld wanneer je een model via een email verstuurt, of aan iemand doorgeeft met een USB-stick. De goede manier is om het model op een plek te zetten waar iedereen bij kan die het model nodig heeft. Dit kan simpelweg een netwerkschijf zijn, maar de betere optie is om gebruik te maken van een SharePoint website, Skydrive of Office 365. Hiermee kunnen anderen het model raadplegen via een webbrowser, zodat ze het model niet eerst hoeven te downloaden. Dat downloaden creëert namelijk ook weer een kopie!

Is het nieuwe Excel het einde van alle problemen? Natuurlijk niet. Je kunt nog steeds slechte modellen maken in Excel. Maar het is nu een stuk eenvoudiger om een goed model te maken. In een volgend artikel bespreek ik de grenzen van deze aanpak.

Michiel Rozema (michielr@microsoft.com) is bij Microsoft Nederland verantwoordelijk voor Business Intelligence.