Met Excel-gegevensmodel (Power Pivot) kunt u een grote, gedetailleerde gegevensset van werkelijke waarden koppelen aan een budget op het hoogste niveau door joiner-tabellen te gebruiken.
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 Budget-bestand met de omvangrijke Actuals-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: één 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 op basis van de Budget-tabel en het selectievakje Deze gegevens aan het gegevensmodel toevoegen aanvinken.
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.
Bekijk video
- U heeft een kleine top-down budgetgegevensset
- U wilt vergelijken met een bottom-up actuele dataset
- De werkelijke waarden kunnen afkomstig zijn van een factuurregister
- Met het datamodel kunt u deze datasets van verschillende grootte vergelijken
- Maak van beide datasets een Ctrl + T-tabel
- Maak een joiner-tabel voor elk tekstveld waarvoor u wilt rapporteren
- Kopieer de waarden en verwijder duplicaten
- Voor datums kunt u datums uit beide tabellen opnemen en converteren naar het einde van de maand
- Zorg dat de schrijnwerkers Ctrl + T-tabellen zijn
- Optioneel maar handig om alle vijf de tabellen een naam te geven
- Maak een draaitabel vanuit Budget en kies het gegevensmodel
- Bouw een draaitabel met Budget en Werkelijk van de originele tabellen
- Alle andere velden moeten afkomstig zijn uit de joiner-tabellen
- Voeg snijmachines toe per product
- Creëer drie relaties van budget tot schrijnwerkers
- Creëer drie relaties van Actual tot Joiners
- Morgen: hoe het opbouwen van relaties eenvoudiger wordt met Power Pivot en DAX Formulas
Videotranscriptie
Leer Excel van podcast, aflevering 2016 - Top-down budget versus bottom-up werkelijke waarden!
Hé, ik podcast dit hele boek, klik op die "i" in de rechterbovenhoek en volg de afspeellijst.
Hé, ik ga dit onderbreken, dit is Bill Jelen over 15 minuten. Ik realiseer me nu dat dit een ongelooflijk lange podcast is, en je komt in de verleiding om er gewoon doorheen te klikken, maar laat me je dit kort vertellen. Als je in Excel 2013 zit, en je hebt ooit een kleine budgettabel en een enorme tabel met werkelijke waarden gehad, en je moet ze samen in kaart brengen, dan is dit een verbazingwekkende nieuwe mogelijkheid die we hebben in Excel 2013, die niet veel mensen hebben uitgelegd , en u weet er waarschijnlijk niets van. Als jij dit bent, ben je in 2013, en je moet deze twee datasets in kaart brengen, de tijd nemen, misschien vandaag, misschien morgen, misschien toevoegen aan de watchlist, het is het waard, het is een geweldige techniek.
Oké, dit is wat we hebben, aan de linkerkant hebben we een budget, dit budget, het wordt gedaan op het hoogste niveau, van bovenaf, goed voor elke productlijn, voor elke regio, voor elke maand is er een budget . Er zijn niet veel records hier, telling van 55, aan de rechterkant proberen we dit te vergelijken met werkelijke waarden. De werkelijke cijfers zijn afkomstig van een factuurregister, dus we hebben Regio, Product en Omzet, maar het zijn individuele facturen, veel meer gegevens hier, we zijn al halverwege het jaar en ik heb al 423 records. Oké, dus hoe wijs je deze 55 toe aan deze 423? Het kan moeilijk zijn om te doen met VERT.ZOEKEN, je zou eerst moeten samenvatten, maar gelukkig maakt het gegevensmodel dit in Excel 2013 heel, heel gemakkelijk. Wat we nodig hebben om deze grote massieve tafel te laten communiceren met deze kleine tafel, zijn tussenpersonen, ik noem ze schrijnwerkers.Kleine tafeltjes, Product, Regio en Kalender, we gaan het budget bij deze drie tafels voegen, we gaan de feitelijke bij deze drie tafels voegen, en wonderbaarlijk genoeg zal de draaitabel werken. Oké, dus hier is hoe we dat doen.
Eerst moet ik de schrijnwerkers maken, dus ik neem dit productveld uit kolom A, en ik kopieer het naar kolom F, en dan Gegevens, Duplicaten verwijderen, klik op OK, en we houden een klein tafeltje over, 1 kop 3 rijen. Hetzelfde voor Regio, neem de regio's, Ctrl + C, ga naar kolom G, Plakken, Duplicaten verwijderen, klik op OK, 3 rijen 1 koptekst, oké. Wat betreft de datums, de datums zijn niet hetzelfde, dit zijn de einddatums van de maand, ze zijn feitelijk opgeslagen als de einddatums van de maand, en dit zijn weekdagen. Ik neem beide lijsten, Ctrl + C de tweede lijst en plak deze hier, Ctrl + V, dan neem ik de kortere lijst, kopieer die en plak deze hieronder, oké. En het is echt vervelend dat, hoewel deze als datums zijn opgeslagen, ze als maanden worden weergegeven en dat Duplicaten verwijderen ze niet als hetzelfde zal zien.Dus voordat ik Duplicaten verwijderen gebruik, moet ik het naar een korte datum wijzigen. Kies die gegevens, Gegevens, Duplicaten verwijderen, klik op OK en dan een beetje hier sorteren om het te laten werken.
Oké, nu wil ik niet rapporteren op dagelijkse datum, dus ik ga hier een kolom toevoegen, een opzoekkolom met de tekst Maand, en dit zal gelijk zijn aan EOMONTH die datum, 0, wat ons naar het einde van de maand. Het zal dat opmaken als een korte datum, en dat kopiëren, oké. Nu moeten we van elk van deze een Ctrl + T-tabel maken, dus vanaf hier Ctrl + T, Mijn tabel heeft kopteksten, prachtig. De kleintjes, het beseft niet dat dit kopteksten daarboven zijn, dus we moeten ervoor zorgen dat je dat aanvinkt en Ctrl + T, oké, en ze noemen deze tabellen Table1, Table2, Table3, echt saaie namen, toch? Dus ik ga deze hernoemen en noem het de BudTable, de ProdTable, RegTable, mijn CalTable en dan de ActTable, oké.
We beginnen bij de allereerste tabel, en trouwens, we gaan PowerPivot vandaag niet gebruiken, we gaan dit allemaal doen met het datamodel. Dus, Excel 2013 of nieuwer, je hebt deze Insert, PivotTable, we gaan het vakje aanvinken voor 'Voeg deze gegevens toe aan het gegevensmodel', klikken op OK en we krijgen onze lijst met velden met de magische knop Alles, waarmee ik kies uit alle vijf de tabellen in de werkmap: Werkelijk, Budget, Kalender, Product, Regio. Oké, dus de cijfers komen van de Budgettabel, ik zet het budget erin, en van de Actual-tabel zet ik de feitelijke daar in, maar dan is hier het ding voor de rest van de draaitabel. Alle andere tekstvelden die we in het rijgebied of het kolomgebied gaan plaatsen of als slicers, ze moeten van de joiners komen, ze moeten van die tabellen tussen de tabellen komen.
Oké, dus uit de kalendertabel nemen we dat maandveld en plaatsen het bovenaan, we gaan nu andere relaties negeren. Ik ga de relaties maken, maar ik wil ze allemaal tegelijk maken. En de regiotabel, leg de regio's aan de zijkant. Ik zou producten langs de zijkant kunnen zetten, maar ik ga de Producttabel eigenlijk als een snijmachine gebruiken, dus Analyseer, Plaats snijmachine, je moet weer naar Alles gaan als je de Producttabel nog niet hebt gebruikt. Dus ga naar Alles en je zult zien dat het Product beschikbaar is om als slicer van de producten te maken, zo. Oké, op dit moment hebben we geen relaties gecreëerd, dus al deze cijfers kloppen niet. En de relaties die we moeten creëren, we moeten 3 tabellen maken van deze kleine budgettabel, één voor de producten, één voor regio's, één voor kalender,dat zijn 3 relaties. En dan moeten we relaties maken van de tabel Werkelijk naar de productregio in Agenda, dus in totaal 6 tabellen. En ja, dit zou zeker gemakkelijker zijn als we PowerPivot hadden, maar dat doen we niet, of laten we aannemen dat we dat niet doen.
En dus ga ik de ouderwetse manier gebruiken, de Create dialoog hier, waar we de Budget tabel aan de linkerkant hebben, en we gaan het Region veld gebruiken en dat relateren aan de Region tabel, het Region veld . Oké, 1/6 wordt gemaakt. Ik kies Maken, opnieuw vanuit de Budget-tabel gaan we naar het Product, en dan link dat naar de Product-tabel, naar het Product, klik op OK. Vanuit de Budget-tabel, het Datum-veld, gaan we naar de Kalender-tabel, en het Fate-veld, klik op OK, we zijn halverwege, oké. Van de tabel Werkelijke waarden gaan we naar Regio, naar de tabel Regio, klik op OK, van de tabel Werkelijke waarden naar het Product en van de tabel Werkelijke waarden naar de Kalender. Ik ga eigenlijk de Waarden nemen en het naar beneden halen, oké. Ontwerp, rapportlay-out, toon in tabelvorm om een weergave te krijgen die ik verkies, herhaal alle itemlabels, oké,dit is absoluut geweldig! Nu hebben we dit piepkleine tafeltje, 50 records in deze tabel met honderden records, en dankzij het gegevensmodel hebben we één draaitabel gemaakt. Voor elk waar we het budget kunnen zien, kunnen we de inkomsten zien, deze is uitgesplitst per regio, is uitgesplitst per maand en kan worden onderverdeeld op product.
Dit concept kwam tot mij door Rob Collie die Power Pivot Pro runt, en Rob heeft veel boeken gemaakt, zijn nieuwste is "Power Pivot en Power BI". Ik denk dat deze eigenlijk in het 'Power Pivot Alchemy'-boek stond, ik zag dit en ik zei:' Nou, dit, ook al heb ik geen miljoenen rijen om te rapporteren via Power Pivot, dit is er een die hebben een ENORM verschil gemaakt in mijn leven, omdat ik twee datasets van niet-overeenkomende grootten heb en van beide moet rapporteren. " Welnu, dit voorbeeld en vele anderen staan in dit boek, ik zal uiteindelijk de hele boekpodcast krijgen, dat lijkt erop dat het twee en een halve maand gaat duren. Maar je kunt vandaag het hele boek krijgen, op dezelfde tijd, daarheen gaan, het boek kopen, $ 10 voor het e-boek, $ 25 voor het gedrukte boek, en je kunt al die tips tegelijk krijgen.
Oké, een hele lange aflevering hier: we hebben een klein top-down budget en een bottoms up Actual, het zijn verschillende formaten, maar als je het datamodel in Excel 2013 gebruikt … En trouwens, als je in 2010 bent, zou je dat kunnen , doe dit in theorie door de Power Pivot-invoegtoepassing op te halen en doorloop al deze stappen in 2010. Maak van beide gegevenssets een Ctrl + T-tabel en voeg vervolgens uw tabellen samen voor alles waarover u wilt rapporteren, in de rijlabel, of het kolomlabel, of de slicers, dus kopieer die waarden over en Verwijder duplicaten voor de datums. Ik nam eigenlijk waarden uit beide tabellen, omdat er enkele unieke waarden in elk waren, en toen gebruikte ik de EOMONTH om daar weg te komen, om van die joiner-tabellen gecontroleerde tabellen te maken. Het is optioneel, maar ik heb alle 5 de tabellen een naam gegeven, omdat het gemakkelijker is om die relaties op te zetten, in plaats van dat ze Tabel1 heten,Tabel2, Tabel3.
Begin dus met de Budget-tabel, Invoegen, PivotTable, vink het vakje aan voor Gegevensmodel en bouw vervolgens een draaitabel met Budget en Werkelijk. Al het andere komt van de joiner-tabellen, dus Regio en Maand in het rij- en kolomgebied, slicers kwamen uit de Producttabel. En toen moesten we 3 relaties creëren van het budget naar de schrijnwerkers, 3 relaties van de werkelijke tot de schrijnwerkers, en we hebben een geweldige draaitabel. Morgen gaan we kijken hoe we het Power Pivot-tabblad kunnen gebruiken en wat aanvullende berekeningen maken. Dus dit is allemaal mogelijk, het is wanneer we een berekend veld willen invoegen, dat is wanneer u de extra $ 2 per maand moet betalen om de Pro Plus-versie van Office 365 te krijgen.
Nou hey, dankzij Rob Collie van Power Pivot Pro voor deze tip, en bedankt voor het langskomen, we zien je de volgende keer voor een nieuwe netcast van!
Download bestand
Download het voorbeeldbestand hier: Podcast2016.xlsx
