Budgets werden auf höchster Ebene erstellt - Umsatz nach Produktlinie nach Region nach Monat. Die tatsächlichen Werte häufen sich im Laufe der Zeit langsam an - Rechnung für Rechnung, Werbebuchung für Werbebuchung. Der Vergleich der kleinen Budgetdatei mit den umfangreichen tatsächlichen Daten war für immer ein Problem. Ich liebe diesen Trick von Rob Collie, auch bekannt als PowerPivotPro.com.
Um das Beispiel einzurichten, haben Sie eine Budget-Tabelle mit 54 Zeilen: 1 Zeile pro Monat pro Region pro Produkt.
Die Rechnungsdatei befindet sich auf Detailebene: Bisher 422 Zeilen in diesem Jahr.
Es gibt keinen VLOOKUP auf der Welt, mit dem Sie jemals diese beiden Datensätze abgleichen können. Dank Power Pivot (auch bekannt als Datenmodell in Excel 2013+) wird dies jedoch einfach.
Sie müssen winzige kleine Tabellen erstellen, die ich "Joiner" nenne, um die beiden größeren Datensätze zu verknüpfen.
In meinem Fall haben Produkt, Region und Datum die beiden Tabellen gemeinsam. Die Produkttabelle ist eine winzige Tabelle mit vier Zellen. Das Gleiche gilt für die Region. Erstellen Sie diese, indem Sie Daten aus einer Tabelle kopieren und Duplikate entfernen verwenden.
Die Kalendertabelle auf der rechten Seite war tatsächlich schwieriger zu erstellen. Die Budgetdaten haben eine Zeile pro Monat und fallen immer auf das Monatsende. Die Rechnungsdaten zeigen tägliche Daten, normalerweise wochentags. Daher musste ich das Feld Datum aus beiden Datensätzen in eine einzelne Spalte kopieren und dann Duplikate entfernen, um sicherzustellen, dass alle Daten dargestellt werden. Ich habe dann =TEXT(J4,"YYYY-MM")eine Monatsspalte aus den täglichen Daten erstellt.
Wenn Sie nicht über das vollständige Power Pivot-Add-In verfügen, müssen Sie eine Pivot-Tabelle aus der Budget-Tabelle erstellen und das Kontrollkästchen Diese Daten zum Datenmodell hinzufügen aktivieren.
Wie im vorherigen Tipp erläutert, müssen Sie beim Hinzufügen von Feldern zur Pivot-Tabelle sechs Beziehungen definieren. Während Sie dies mit sechs Besuchen im Dialogfeld "Beziehung erstellen" tun konnten, habe ich mein Power Pivot-Add-In gestartet und die sechs Beziehungen in der Diagrammansicht definiert.
Hier ist der Schlüssel, damit all dies funktioniert: Sie können die numerischen Felder von Budget und Actual verwenden. Wenn Sie jedoch Region, Produkt oder Monat in der Pivot-Tabelle anzeigen möchten, müssen diese aus den Joiner-Tabellen stammen!
Hier ist eine Pivot-Tabelle mit Daten aus fünf Tabellen. Spalte A stammt vom Region Joiner. Zeile 2 kommt vom Kalender-Joiner. Der Produktschneider stammt vom Produktschreiner. Die Budgetnummern stammen aus der Budgettabelle, und die tatsächlichen Nummern stammen aus der Rechnungstabelle.
Dies funktioniert, weil die Joiner-Tabellen Filter auf die Tabelle Budget und Ist anwenden. Es ist eine schöne Technik und zeigt, dass Power Pivot nicht nur für Big Data geeignet ist.
