Mit dem Excel-Datenmodell (Power Pivot) können Sie mithilfe von Joiner-Tabellen einen großen detaillierten Datensatz mit Istdaten mit einem Budget der obersten Ebene verbinden.
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 Istdaten 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: eine 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.
Schau Video
- Sie haben einen kleinen Top-Down-Budgetdatensatz
- Sie möchten mit einem Bottom-Up-Ist-Datensatz vergleichen
- Die tatsächlichen Daten stammen möglicherweise aus einem Rechnungsregister
- Mit dem Datenmodell können Sie diese Datensätze unterschiedlicher Größe vergleichen
- Machen Sie beide Datensätze zu einer Strg + T-Tabelle
- Erstellen Sie für jedes Textfeld, nach dem Sie Bericht erstatten möchten, eine Joiner-Tabelle
- Kopieren Sie die Werte und entfernen Sie Duplikate
- Für Datumsangaben können Sie Datumsangaben aus beiden Tabellen einschließen und in das Monatsende konvertieren
- Machen Sie die Joiner zu Strg + T-Tabellen
- Optional, aber hilfreich, um alle fünf Tabellen zu benennen
- Erstellen Sie eine Pivot-Tabelle aus Budget und wählen Sie das Datenmodell
- Erstellen Sie eine Pivot-Tabelle mit Budget und Ist aus den ursprünglichen Tabellen
- Alle anderen Felder müssen aus den Joiner-Tabellen stammen
- Fügen Sie Slicer nach Produkt hinzu
- Erstellen Sie drei Beziehungen von Budget zu Joiners
- Erstellen Sie drei Beziehungen von "Ist" zu "Tischler"
- Morgen: Wie der Aufbau von Beziehungen mit Power Pivot- und DAX-Formeln einfacher wird
Video-Transkript
Lernen Sie Excel aus dem Podcast, Folge 2016 - Top-Down-Budget vs. Bottom-Up-Istwerte!
Hey, ich mache Podcasts für das gesamte Buch, klicke auf das "i" in der oberen rechten Ecke und folge der Wiedergabeliste.
Hey, ich werde das unterbrechen, das ist Bill Jelen in 15 Minuten. Mir ist jetzt klar, dass dies ein unglaublich langer Podcast ist, und Sie sind versucht, einfach durchzuklicken, aber ich möchte Ihnen nur kurz darauf eingehen. Wenn Sie in Excel 2013 sind und jemals eine kleine Budget-Tabelle und eine massive Ist-Tabelle hatten und diese zusammen zuordnen müssen, ist dies eine erstaunliche neue Funktion, die wir in Excel 2013 haben und die nicht viele Leute erklärt haben und Sie wissen wahrscheinlich nichts davon. Wenn Sie es sind, sind Sie im Jahr 2013 und müssen diese beiden Datensätze abbilden, sich die Zeit nehmen, vielleicht heute, vielleicht morgen, vielleicht zur Beobachtungsliste hinzufügen, es lohnt sich, es ist eine erstaunliche Technik.
Okay, hier ist was wir haben, auf der linken Seite haben wir ein Budget, dieses Budget, es wird auf oberster Ebene, von oben nach unten, rechts für jede Produktlinie, für jede Region, für jeden Monat, es gibt ein Budget . Nicht viele Datensätze hier, Anzahl 55, auf der rechten Seite versuchen wir, dies mit den tatsächlichen Werten zu vergleichen. Die tatsächlichen Daten stammen aus einem Rechnungsregister, daher haben wir Region, Produkt und Umsatz, aber es handelt sich um Einzelrechnungen, viel mehr Daten hier, wir sind bereits in der Mitte des Jahres und ich habe bereits 423 Datensätze. Okay, wie ordnen Sie diese 55 diesen 423 zu? Es könnte schwierig sein, mit VLOOKUP zu arbeiten, Sie müssten es zuerst zusammenfassen, aber dankenswerterweise macht das Datenmodell dies in Excel 2013 wirklich sehr, sehr einfach. Was wir brauchen, damit dieser große, massive Tisch mit diesem winzigen Tisch kommunizieren kann, sind Vermittler, ich nenne sie Tischler.Winzige kleine Tabellen, Produkt, Region und Kalender, wir werden das Budget mit diesen drei Tabellen verbinden, wir werden das tatsächliche mit diesen drei Tabellen verbinden, und auf wundersame Weise wird die Pivot-Tabelle funktionieren. Okay, so machen wir das.
Zuerst muss ich die Joiner erstellen, also nehme ich dieses Produktfeld aus Spalte A und kopiere es in Spalte F und dann auf Daten, Duplikate entfernen, auf OK, und wir haben eine kleine Tabelle 1 übrig Überschrift 3 Zeilen. Das Gleiche gilt für Region. Nehmen Sie die Regionen, Strg + C, gehen Sie zu Spalte G, Einfügen, Duplikate entfernen, klicken Sie auf OK, 3 Zeilen 1 Überschrift, in Ordnung. Nun, für die Daten sind die Daten nicht dieselben, dies sind Monatsenddaten, sie werden tatsächlich als Monatsenddaten gespeichert und dies sind Wochentage. Ich nehme beide Listen, Strg + C die zweite Liste und füge sie hier ein, Strg + V, dann nehme ich die kürzere Liste, kopiere sie und füge sie unten ein, okay. Und es ist wirklich ärgerlich, dass diese, obwohl sie als Datumsangaben gespeichert sind, als Monate angezeigt werden und Duplikate entfernen sie nicht als gleich ansehen.Bevor ich "Duplikate entfernen" verwende, muss ich es auf ein kurzes Datum ändern. Wählen Sie diese Daten aus, Daten, Duplikate entfernen, klicken Sie auf OK und sortieren Sie sie hier ein wenig, damit sie funktionieren.
Okay, jetzt möchte ich nicht nach Tagesdatum berichten, also werde ich hier eine Spalte hinzufügen, eine Nachschlagespalte mit der Aufschrift "Monat", und dies entspricht EOMONTH an diesem Datum, 0, was uns dazu bringt das Ende des Monats. Es wird das als kurzes Datum formatieren und das in Ordnung kopieren. Jetzt müssen wir jede davon zu einer Strg + T-Tabelle machen, also von hier aus Strg + T, Meine Tabelle hat Überschriften, wunderschön. Die kleinen, es ist nicht klar, dass es sich dort oben um Header handelt, also müssen wir das und Strg + T ankreuzen, okay, und sie nennen diese Tabellen Tabelle1, Tabelle2, Tabelle3, wirklich langweilige Namen, oder? Also werde ich diese umbenennen und es die BudTable, die ProdTable, die RegTable, meine CalTable und dann die ActTable nennen, in Ordnung.
Wir beginnen mit der ersten Tabelle und werden PowerPivot heute übrigens nicht mit dem Datenmodell verwenden. Ab Excel 2013 oder neuer haben Sie diese Einfügung, PivotTable. Wir aktivieren das Kontrollkästchen für "Diese Daten zum Datenmodell hinzufügen", klicken auf "OK" und erhalten unsere Feldliste mit der magischen Schaltfläche "Alle" Ich wähle aus allen fünf Tabellen in der Arbeitsmappe: Ist, Budget, Kalender, Produkt, Region. Okay, also die Zahlen werden aus der Budget-Tabelle kommen, ich werde das Budget dort einfügen, und aus der tatsächlichen Tabelle werde ich das tatsächliche dort einfügen, aber dann ist hier die Sache für den Rest der Pivot-Tabelle. Alle anderen Textfelder, die wir in den Zeilen- oder Spaltenbereich oder als Slicer einfügen, müssen von den Joinern stammen, sie müssen von diesen Tabellen zwischen den Tabellen stammen.
Okay, aus der Kalendertabelle nehmen wir das Feld "Monat" und setzen es ganz oben. Wir werden jetzt andere Beziehungen ignorieren. Ich werde die Beziehungen erstellen, aber ich möchte sie alle auf einmal erstellen. Und in der Region-Tabelle legen Sie die Regionen auf die Seite. Ich könnte Produkte auf die Seite legen, aber ich werde die Produkttabelle tatsächlich als Slicer verwenden. Analysieren, Slicer einfügen, müssen Sie erneut zu Alle gehen, wenn Sie die Produkttabelle noch nicht verwendet haben. Gehen Sie also zu Alle, und Sie werden sehen, dass das Produkt verfügbar ist, um es als Slicer aus den Produkten zu erstellen. Okay, zu diesem Zeitpunkt haben wir noch keine Beziehungen erstellt, daher sind alle diese Zahlen falsch. Und die Beziehungen, die wir erstellen müssen, müssen wir 3 Tabellen aus dieser kleinen Budget-Tabelle erstellen, eine zu den Produkten, eine zu Regionen, eine zu Kalender,Das sind 3 Beziehungen. Und dann müssen wir Beziehungen von der tatsächlichen Tabelle zur Produktregion im Kalender erstellen, also insgesamt 6 Tabellen. Und ja, das wäre definitiv einfacher, wenn wir PowerPivot hätten, aber wir tun es nicht oder nehmen wir an, wir tun es nicht.
Also werde ich den altmodischen Weg verwenden, den Dialog "Erstellen" hier, wo wir die Budget-Tabelle auf der linken Seite haben, und wir werden das Feld Region verwenden und das mit der Region-Tabelle, dem Region-Feld, verknüpfen . Okay, 1/6 werden erstellt. Ich wähle Erstellen, wieder aus der Budget-Tabelle gehen wir zum Produkt und verknüpfen das dann mit der Produkttabelle, mit dem Produkt, klicken Sie auf OK. Von der Budget-Tabelle im Feld Datum gehen wir zur Kalendertabelle, und im Feld Schicksal klicken Sie auf OK. Wir sind auf halbem Weg, in Ordnung. Von der Tabelle "Ist" gehen wir zur Region, zur Tabelle "Region", klicken auf "OK", von der Tabelle "Ist" zum Produkt und von der Tabelle "Ist" zum Kalender. Ich werde tatsächlich die Werte nehmen und sie zur Seite gehen lassen, in Ordnung. Design, Berichtslayout, In tabellarischer Form anzeigen, um eine von mir bevorzugte Ansicht zu erhalten. Alle Artikelbezeichnungen wiederholen, in Ordnung,das ist absolut erstaunlich! Jetzt haben wir diese winzige kleine Tabelle, 50 Datensätze in dieser Tabelle mit Hunderten von Datensätzen, und wir haben dank des Datenmodells eine einzige Pivot-Tabelle erstellt. Für jeden, in dem wir das Budget sehen können, können wir die Einnahmen sehen, es ist nach Regionen aufgeschlüsselt, es ist nach Monaten aufgeschlüsselt und es ist nach Produkten aufgeschlüsselt.
Dieses Konzept kam jetzt von Rob Collie, der Power Pivot Pro ausführt, und Rob hat viele Bücher erstellt. Sein neuestes ist „Power Pivot and Power BI“. Ich denke, dieses Buch war tatsächlich im Buch „Power Pivot Alchemy“ enthalten. Ich habe es gesehen und gesagt: „Nun, obwohl ich nicht Millionen von Zeilen über Power Pivot zu berichten habe, ist dies eines, das dies tun würde Ich habe einen RIESIGEN Unterschied in meinem Leben gemacht, weil ich zwei Datensätze mit nicht übereinstimmenden Größen hatte und von beiden berichten musste. “ Nun, dieses Beispiel und viele andere sind in diesem Buch enthalten. Ich werde irgendwann den gesamten Buch-Podcast erhalten, der anscheinend zweieinhalb Monate dauern wird. Aber Sie können heute das ganze Buch bekommen, zur gleichen Zeit dorthin gehen, das Buch kaufen, 10 US-Dollar für das E-Book, 25 US-Dollar für das gedruckte Buch, und Sie können all diese Tipps auf einmal haben.
Okay, eine wirklich lange Episode hier: Wir haben ein kleines Top-Down-Budget und ein Bottom-Up-Budget. Sie sind unterschiedlich groß, aber mit dem Datenmodell in Excel 2013… Und übrigens, wenn Sie 2010 sind, könnten Sie es Theoretisch können Sie dazu das Power Pivot-Add-In herunterladen und alle diese Schritte im Jahr 2010 ausführen. Machen Sie beide Datensätze zu einer Strg + T-Tabelle und verbinden Sie Ihre Tabellen für alles, worüber Sie berichten möchten Zeilenbeschriftung oder Spaltenbeschriftung oder Slicer. Kopieren Sie diese Werte über und entfernen Sie Duplikate für die Daten. Ich habe tatsächlich Werte aus beiden Tabellen genommen, weil es in jeder Tabelle einige eindeutige Werte gab, und dann habe ich EOMONTH verwendet, um herauszukommen und diese Joiner-Tabellen zu kontrollierten Tabellen zu machen. Es ist optional, aber ich habe alle 5 Tabellen benannt, weil es einfacher ist, diese Beziehungen einzurichten, als Tabelle1 genannt zu werden.Tabelle 2, Tabelle 3.
Beginnen Sie also mit der Budget-Tabelle, Einfügen, PivotTable, aktivieren Sie das Kontrollkästchen für Datenmodell und erstellen Sie dann eine Pivot-Tabelle mit Budget und Ist. Alles andere stammt aus den Joiner-Tabellen, sodass Region und Monat im Zeilen- und Spaltenbereich Slicer aus der Produkttabelle stammen. Und dann mussten wir 3 Beziehungen vom Budget zu den Tischlern, 3 Beziehungen vom Ist zu den Tischlern erstellen, und wir haben eine erstaunliche Pivot-Tabelle. Morgen schauen wir uns an, wie Sie die Registerkarte Power Pivot verwenden und einige zusätzliche Berechnungen erstellen. All dies ist also möglich. Wenn wir ein berechnetes Feld einfügen möchten, müssen Sie die zusätzlichen 2 US-Dollar pro Monat bezahlen, um die Pro Plus-Version von Office 365 zu erhalten.
Hey, danke an Rob Collie von Power Pivot Pro für diesen Tipp und danke an dich für deinen Besuch, wir sehen uns beim nächsten Mal für einen weiteren Netcast von!
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2016.xlsx
