Het maken van custom reports kan de nodige tijd vergen als deze rapporten modeltechnisch niet goed worden opgezet. Het is mogelijk om dynamische rapporten te maken met behulp van draaitabellen, maar zijn er situaties te bedenken waarin deze niet gebruikt mogen worden. Deze omstandigheid doet zich voor wanneer het gewenste rapport een bepaalde indeling en opmaak dient te hebben. Daarnaast dient het rapport zodanig te worden gestileerd dat door toevoeging van een keuzelijst de cijfers automatisch mee veranderen.
Een extra moeilijkheid dient zich aan wanneer de gebruiker meerdere items uit de keuzelijst dient te kiezen, waarna de cijfers automatisch worden aangepast. Als voorbeeld kunt u uit een keuzelijst de landen Nederland, België en Luxemburg afzonderlijk kiezen, twee willekeurige landen of alle landen. De daarmee gepaard gaande formules kunnen dan al vrij lang en ingewikkeld worden. In dit artikel laat ik u een alternatief zien waarmee u korte dynamische formules kunt maken.
Opzet en werking van het model
Het uitgangspunt van elk dynamisch rapport is een Excel-tabel, dat wil zeggen een geordende structuur van bij elkaar behorende aaneengesloten records met unieke veldnamen zoals in dit voorbeeld getoond:
U dient de set van gegevens om te zetten naar een Excel-tabel. In het Lint kiest u: Invoegen > Tabel (Home > Insert > Table).
Gebaseerd op de tabel maakt u een draaitabel met een structuur waaruit u de gewenste totalen kunt berekenen. Vanuit het Lint kiest u: Invoegen > Draaitabel (Insert > Pivot Table). We berekenen de omzet per product en per periode met de volgende lay-out:
De draaitabel ziet er als volgt uit:
Plaats de cursor op een datum en klik met rechtermuisknop. Uit het verkorte menu kiest u Groeperen. Uit het dialoogvenster klikt u op Maanden om deze te de-selecteren en op Kwartalen en Jaren om deze te selecteren. Klik dan op OK. De draaitabel ziet er als volgt uit:
De indeling rechts onderaan toont een extra veld genaamd Datum.
Verschuif het Datumveld naar Kolommen.
De draaitabel ziet er als volgt uit:
Geef cel A3 de bereiknaam StartDT. Maak ten slotte een slicer via Hulpmiddelen voor Draaitabellen > Analyseren > Filter > Slicer invoegen > Land (Pivot Tools > Analyze > Filer > Insert slicer). Markeer de slicer en selecteer uit het Lint Hulpmiddelen voor Slicers > Knoppen > Kolommen > 3 (Design > Buttons > Columns).
De Slicer ziet er als volgt uit:
Plaats de cursor in de Draaitabel en kies uit het Lint: Hulpmiddelen voor Draaitabellen > Analyseren > Draaitabel > Opties > aanvinken DraaitabelOphalen genereren.
Deze slicer verplaatsen we naar het outputblad. Deze heeft de volgende indeling:
De cellen C5 tot en met F5 bevatten de getallen 1 tot en met 4. Via de aangepaste opmaak “Kw”0 ziet u KW1 tot en met KW 4 verschijnen.
Plaats de cursor in cel C6 en verwijs naar cel C6 in de draaitabel. De volgende formule wordt aangemaakt:
=DRAAITABEL.OPHALEN(“omzet”;DT!$A$3;”datum”;1;”product”;”audio”;”Jaren”;2016)
We gaan deze formule als volgt dynamiseren:
=DRAAITABEL.OPHALEN(“omzet”;StartDT;”datum”;C$5;”product”;$B6;”Jaren”;C$4)
Deze formule is dynamisch kopieerbaar naar de overige cellen.
U kunt nu in de slicer één land selecteren of meerdere landen door het indrukken van de control-toets tijdens het aanklikken. De cijfers zullen zich automatisch aanpassen.
Let op: U dient de draaitabel te verversen als de bron wijzigt in het bronblad. Daarnaast mag de structuur van de draaitabel niet meer worden aangepast. De functie DRAAITABEL.OPHALEN is daarop ingesteld. In de Engelse versie heet deze functie GETPIVOTDATA.
U heeft nu een dynamisch rapport aangemaakt waarvan u de indeling en opmaak naar eigen smaak kunt inrichten. Het rapport bevat slechts een unieke formule.
Tony de Jonker, De Jonker Consultancy
cm:EXCEL is dé nieuwsbrief voor de financial die meer wil weten over Excel
In de maandelijkse nieuwsbrief staat informatie over nieuwe toepassingen, handigheidjes, tips en trucs en opleidingen. Schrijf u vandaag nog in voor onze gratis nieuwsbrief door op de banner te klikken:
