Excel-rapportage: begin met het eind voor ogen
Voordat u begint is het handig om op een A4-blad een schets te maken hoe uw rapport eruit komt te zien. Hierbij kunt u aan het volgende denken:
- Wat is de titel van het rapport?
- Welke huisstijl wilt u toepassen?
- Hoe gaat de structuur van het rapport eruitzien?
- Welke kopteksten worden gebezigd?
- Dient het rapport dynamisch te zijn zodat selecties gemaakt kunnen worden met besturingselementen?
- Wilt u grafieken toevoegen en zo ja, welke en hoe gaan ze eruitzien?
- Wie is de doelgroep en welke boodschap wilt u overbrengen?
Maak qua opmaak een gebruikersvriendelijk rapport dat is ontdaan van onnodige zaken:
- Geen dikke randlijnen maken. Gebruik daarvoor een zeer lichte kleur
- Beperk het aantal kleuren
- Doseer het aantal gegevens dat u in een rapport wilt tonen
- Laat de rasterlijnen achterwege
- Gebruik waar nodig een voorwaardelijke opmaak
Inventariseer de bronnen voor u Excel-rapportage
Nadat u bepaald heeft hoe het rapport eruit komt te zien, dient u te inventariseren waar u de data kunt vinden om het rapport te kunnen vervaardigen. Mogelijke bronnen zijn:
- ERP
- CRM
- Personeelsinformatiesysteem
- Excelwerkmappen
- Database
- CSV-bestanden
- Internet
Bronnen koppelen met Excel en converteren naar tabellen
Om flexibele rapportages te kunnen maken is het van belang dat de data in tabelvorm wordt aangeleverd. Mocht dit niet het geval zijn dan biedt Excel een zeer handig hulpmiddel op dit gebied, namelijk Power Query oftewel Ophalen & Transformeren. Hiermee bent u in staat om ongeordende gegevens via interactieve stappen op te nemen en te converteren naar een Excel-tabel. Dat betekent dat u de volgende rapportageperiode de gegevens in een keer kunt verversen. Mogelijke transformaties die hiermee geautomatiseerd kunnen worden zijn:
- Kantelen van kruistabel data naar tabeldata
- Consolideren van bestanden
- Opvullen van lege regels
- Ingewikkelde omvormingen automatiseren door middel van de taal M
De voordelen van het werken met Excel-tabellen zijn velerlei zoals:
- Automatische aanpassing van draaitabellen als nieuwe gegevens worden toegevoegd
- Mogelijkheid om tabellen te koppelen via het Data Model waardoor onnodige opzoekformules achterwege kunnen blijven
- Inzichtelijk formules door het gebruik van gestructureerde verwijzingen
- Basis voor het bouwen van modellen met behulp van Power Pivot
Uitvoeren van aanvullende bewerkingen
Afhankelijk van de aard van de gewenste rapporten kunnen aanvullende bewerkingen noodzakelijk zijn, zoals het koppelen van bestanden en aanvullende berekeningen uitvoeren binnen de tabel. Excel biedt daartoe de volgende hulpmiddelen:
- Data omzetten naar draaitabellen
- Aanvullende berekeningen uitvoeren binnen de draaitabellen
- Tabellen koppelen via het Data Model en op basis daarvan draaitabellen maken
- Bijzondere berekeningen uitvoeren in Power Pivot door middel van DAX-functies\
- Custom reports voorzien van GetPivotData/ DraaiTabelOphalen functie of door middel van kubus-functies
- Custom reports voorzien van besturingselementen of slicers\
- Toevoegen van grafieken
Bedenk het volgende als u rapporten gestalte gaat geven:
- Is het mogelijk om het gewenste rapport te baseren op een draaitabel? Zo ja, dan bent u daarmee het snelst geholpen want daarvoor heeft u geen formules nodig.
- Voor speciale berekeningen kunt door middel van berekende velden/items virtuele dimensies toevoegen.
- In het geval van gekoppelde bestanden in Power Pivot kunt u aanvullende berekeningen uitvoeren via DAX-functies.
- In het geval het rapport een op maat gesneden lay-out dient te hebben, kunt u deze toch laten baseren op een draaitabel en met GetPivotData/ DraaiTabelOphalen functie of door middel van kubus-functies de gewenste waarden ophalen.
- Als u grafieken wilt toevoegen in het rapport dan is het aan te bevelen om de data te berekenen in een apart blad, welke fungeert als plaatshouder voor de grafiek(en).
- Slicers kunnen heel handig zijn om interactiviteit in te bouwen in het rapport. U kunt de slicers zodanig vormgeven dat ze op knoppen lijken.
