Budgetten worden op het hoogste niveau opgesteld: omzet per productlijn per regio per maand. Werkelijke waarden stapelen zich langzaam op in de loop van de tijd - factuur per factuur, regelitem voor regelitem. Het vergelijken van het kleine budgetbestand met de omvangrijke feitelijke gegevens is voor altijd lastig geweest. Ik ben dol op deze truc van Rob Collie, ook bekend als PowerPivotPro.com.
Om het voorbeeld op te zetten, heb je een budgettabel met 54 rijen: 1 rij per maand per regio per product.
Het factuurbestand bevindt zich op detailniveau: tot dusver dit jaar 422 rijen.
Er is geen VERT.ZOEKEN in de wereld waarmee u deze twee gegevenssets ooit kunt matchen. Maar dankzij Power Pivot (ook bekend als het gegevensmodel in Excel 2013+) wordt dit eenvoudig.
U moet piepkleine tabellen maken die ik "schrijnwerkers" noem om de twee grotere gegevenssets te koppelen.
In mijn geval zijn Product, Regio en Datum gemeenschappelijk tussen de twee tabellen. De Producttafel is een kleine tafel met vier cellen. Idem voor regio. Maak elk van deze door gegevens uit één tabel te kopiëren en Duplicaten verwijderen te gebruiken.
De kalendertabel aan de rechterkant was eigenlijk moeilijker te maken. De budgetgegevens hebben één rij per maand en vallen altijd aan het einde van de maand. De factuurgegevens tonen dagelijkse datums, meestal weekdagen. Dus ik moest het veld Datum van beide gegevenssets naar een enkele kolom kopiëren en vervolgens duplicaten verwijderen om ervoor te zorgen dat alle datums worden weergegeven. Ik maakte toen =TEXT(J4,"YYYY-MM")een maandkolom op basis van de dagelijkse datums.
Als u niet over de volledige Power Pivot-invoegtoepassing beschikt, moet u een draaitabel maken van de Budget-tabel en het selectievakje voor Deze gegevens toevoegen aan het gegevensmodel inschakelen.
Zoals besproken in de vorige tip, moet u bij het toevoegen van velden aan de draaitabel zes relaties definiëren. Hoewel je dit zou kunnen doen met zes bezoeken aan het dialoogvenster Relatie maken, heb ik mijn Power Pivot-invoegtoepassing geactiveerd en de diagramweergave gebruikt om de zes relaties te definiëren.
Hier is de sleutel om dit allemaal te laten werken: U bent vrij om de numerieke velden uit Budget en uit Werkelijk te gebruiken. Maar als u regio, product of maand in de draaitabel wilt weergeven, moeten ze afkomstig zijn van de joiner-tabellen!
Hier is een draaitabel met gegevens uit vijf tabellen. Kolom A is afkomstig van de regioconstructeur. Rij 2 komt van de Agenda-joiner. De productsnijder is van de productschrijnwerker. De budgetcijfers zijn afkomstig van de budgettabel en de werkelijke cijfers zijn afkomstig van de factuurtabel.
Dit werkt omdat de joiner-tabellen filters toepassen op de tabel Budget en Werkelijk. Het is een mooie techniek en laat zien dat Power Pivot niet alleen voor big data is.
