Het nieuwe rapporteren in Excel

Lees ook: 3 nieuwe gereedschappen in Excel

Gemiddeld 80 procent van alle Excel modellen wordt gebruikt voor rapportages. Voordat een misverstand ontstaat over wat een rapportage is, onthul ik je mijn definitie. Het belangrijkste kenmerk van een rapportage is dat deze met een regelmatige tijdsinterval wordt vernieuwd. Daarnaast bevat een rapportage voor de gebruiker een (min of meer) constant en herkenbaar beeld. Dus een maandelijks overzicht hoe de afdeling heeft gepresteerd is een rapportage. Maar ook een jaarlijks budgetmodel, een voorraadstaat of een KPI-dashboard behoren daartoe.

Rapportages in Excel hebben, naast veel voordelen, zo hun nadelen. Zo heb je altijd Excel nodig om het model te zien. Sluit je (de rapportage in) Excel af en je model is per direct ‘dood’. Je hebt Excel nodig om de rapportage weer tot leven te brengen. Een ander nadeel is dat je, naast de rapportage, ook alle invoer en berekeningen aan de gebruiker ter beschikbaar stelt. En dat laatste maakt een Excel model kwetsbaar voor ongewenste intimiteiten. 

Binnen het nieuwe rapporteren ontloop je beide genoemde nadelen. Daarvoor stap je over naar de Self-service Business Intelligence omgeving. Door Power Query en Power Pivot maakt Excel deel uit van die Microsoft Self-service BI familie. Daartoe behoren ook Power BI desktop en PowerBI.com. In het nieuwe rapporteren maak je gebruik van PowerBI.com om jouw rapport te publiceren in de cloud. Voor de Self-service BI omgeving hebben Michiel Rozema en de auteur van deze blog vijf stappen gedefinieerd. In figuur 1 zie een overzicht zie je de vijf stappen van dat proces.

Zo, zonder toelichting, is dit stappenplan redelijk abstract, vandaar dat een korte uitleg op zijn plaats is. De eerste stap bestaat uit de opslag van data, in wat vaak databases wordt genoemd. Dat zijn vaak bedrijfsdatabases, maar bedenk wel dat een Excel bereik of een internet tabel in die zin ook een database is! In stap 2 pas je de inhoud van de database aan naar de behoefte van de rapportage. Je gebruikt in Excel hiervoor Power Query of, zoals het tegenwoordig in Excel 2016 en Power BI desktop heet, Ophalen en transformeren.

De derde stap, de berekeningen, is onderdeel van Power Pivot. Stap 4, de uitvoer, zie je in Excel vaak terug in de vorm van draaitabellen, maar je kunt hiervoor ook Power BI desktop gebruiken. In stap 5, het delen – wat steeds vaker in de cloud gebeurt – gebruik je   PowerBI.com. Ik hoor je denken: ik deel mijn Excel rapportages per mail of ik deel het op een algemene server. Ja, dat mag je van mij best delen noemen, maar dat is zo van de vorige eeuw en o zo kwetsbaar en oncontroleerbaar.

De nieuwe manier van rapporteren binnen Excel bestaat uit het publiceren op de PowerBI.com website. Daarvoor download je de Power BI publisher for Excel, een gratis COM-invoegtoepassing van Microsoft voor Excel 2010 en hoger. Wil je de publisher gebruiken, dan is een Microsoft account verplicht. Na de installatie krijg je een extra tabblad in het lint (zie figuur 2). 

Je publiceert de draaitabel die je in figuur 3 ziet in de cloud en daarmee stel je de rapportage ‘twenty four seven’ (zoals de Amerikanen dat zou mooi uitdrukken) beschikbaar. Je selecteert eerst de draaitabel en vervolgens kies je voor de knop Vastmaken (figuur 4). In het venster Aan dashboard vastmaken kies je voor een Nieuw dashboard en je geeft deze de naam Blog Rapporteren (figuur 5). Kijk je vervolgens in een browser (of in je mobiel of tablet op de Power BI app), dan zie je dat de tabel netjes is gepubliceerd (figuur 6). Dit dashboard deel je met genodigden volgens de (licentie) spelregels, die Microsoft voor Power BI heeft opgesteld.

Wat ik persoonlijk ook een mooie optie vind is de omgekeerde weg. In PowerBI.com staat een rapport over het resultaat van een fietsenwinkel. Je ziet het rapport in figuur 7. De basis voor dit rapport zijn tabellen en berekeningen in Power Pivot. De tabellen zie je in figuur 8. Je gaat terug naar Excel en kies je in het tabblad Power BI in het lint, in de sectie Power BI, voor de knop Verbinding maken met gegevens. In het venster Verbinding maken met gegevens in Power BI kies je voor de optie Gegevensset en de Fietsenwinkel. Het venster zie je in figuur 9. Kies je voor de knop Verbinding en log je indien noodzakelijk in, dan krijg je op basis van het extern opgeslagen Power Pivot model, de draaitabelvelden en kan je een prima Excel rapportage maken, zoals je ziet in figuur 10.

Wat is nu het belang van het nieuwe rapporteren? Het belangrijkste is dat de gebruiker alleen het dashboard ziet en geen ingang heeft op de invoer en de berekeningen. Je bent eindelijk in staat jouw rapportages te beschermen tegen ongewenste aanpassingen. En daar heb ik zo lang op moeten wachten! Om maar eens een oude bekende spreuk uit de kast te halen: je hebt echt ‘a single version of the truth’, die ook nog eens betrouwbaar is. Omdat dit soort rapportages zich uitermate lenen om efficiënt en agile te bouwen, sla je zomaar met het overgaan naar het nieuwe rapporteren drie vliegen in een klap!

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.