Het is natuurlijk mogelijk om hiervoor een VBA-macro te maken, maar met behulp van Power Query en het gebruik van dynamische matrixformules kun je het doel ook bereiken. In deze bijdrage zie je een voorbeeld hoe dit stand komt.
Uitgangspunt
We ontvangen de volgende input met de volgende indeling:
De dataset is al een tabel opgemaakt met de tabelnaam: Detail_BV.
Het is de bedoeling dat de output er als volgt komt uit te ziet:
Per GEB-naam worden de kolommen naar rechts gevuld met Werknemer en Type. Afhankelijk van het maximumaantal aanwezige werknemers worden volgnummers toegekend. Bij 3 werknemers worden de kolommen achtereenvolgend: WN-1-Type-1/WN-2-Type-2/WN-3-Type-3.
Transformaties
De input wordt via Power Query ingelezen. Vervolgens worden de volgende transformatiestappen uitgevoerd:
- Niet relevante kolommen worden verwijderd.
- Kolommen worden op de juiste volgorde geplaatst.
- GEB-Naam wordt op alfabet gesorteerd.
- Het resultaat wordt als tabel afgebeeld in een apart blad (Detail).
- Een kolom B wordt toegevoegd met de formule: =AANTAL.ALS([GEB - Naam];[@[GEB - Naam]])
- Een kolom Nr wordt toegevoegd met de formule: =ALS([@[GEB - Naam]]=VERSCHUIVING([@[GEB - Naam]];-1;0);VERSCHUIVING([@Nr];-1;0)+1;1)
- Een kolom WN wordt toegevoegd met de formule: ="WN-"&[@Nr]
- Een kolom Type wordt toegevoegd met de formule: ="Type-"&[@Nr]
- Vervolgens wordt deze tabel ingelezen in Power Query.
- Daaruit genereren we 2 aparte tabellen met unieke waarden van WN en Type die opgestapeld worden. Dat leidt tot de volgende tabel:
In het blad Parameters worden variabelen bijgehouden met de volgende formules:
- C3=MAX(DetailOutput[Aantal])
- C4=HoogsteAantal*2
Rapport
Het rapport bevat de volgende dynamische matrixformules:
- B4=UNIEK(DetailOutput[GEB - Naam])
- C4 =ALS.FOUT(ALS.FOUT(X.ZOEKEN(B4#&C3#;DetailOutput[GEB - Naam]&DetailOutput[WN];DetailOutput[WKN - Naam]);X.ZOEKEN(B4#&C3#;DetailOutput[GEB - Naam]&DetailOutput[Type];DetailOutput[Type vermindering]));"")
Deze formule vult automatisch de dataset naar rechts en naar beneden.
Zodra de input is aangepast, kun je de Power Query-stappen verversen door uit het menu te kiezen: Gegevens > Query’s en verbindingen > Alles vernieuwen. Daarna worden de twee tabellen in het Detail-blad bijgewerkt alsmede de dynamische formules.
