Het maken van rapportages kan de nodige tijd in beslag nemen. Naast de ad hoc-rapportages komen ook periodieke rapportages aan bod waarbij repetitieve handelingen aan te pas komen. Excel is een uitstekend hulpmiddel om op maat gesneden rapporten te vervaardigen. Cm: heeft een aantal handige tips voor u.

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?

Zie ook: De vijf meestgebruikte Excel-toepassingen voor financials

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

Zie ook: 11 valkuilen in Excel

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.

Auteur: Tony De Jonker

Lees hier de artikelen van Tony De Jonker uit de rubriek Excel Tips & Trucs

Volg CMWeb op LinkedIn!