Sie haben keinen Power Pivot? Ist egal. Der größte Teil von Power Pivot ist in Excel 2013 und noch mehr in Excel 2016 integriert. Heute ist unser Tipp von Ash, Tabellen in einer Pivot-Tabelle zu verbinden.
Sieben Wochen lang präsentiere ich jeden Mittwoch einen der Lieblingstipps von Ash Sharma. Ash ist Produktmanager im Excel-Team. Sein Team bringt Ihnen Pivot-Tische und viele andere gute Dinge. Heute verbindet Ash am liebsten mehrere Datensätze mithilfe von Beziehungen und dem Datenmodell.
Angenommen, Ihre IT-Abteilung gibt Ihnen den in den Spalten A: D gezeigten Datensatz. Es gibt Felder für Kunden und Markt. Sie müssen bestimmte Märkte in Regionen zusammenfassen. Jeder Kunde gehört einer Branche an. Region und Sektor sind nicht in den Originaldaten enthalten, aber Sie haben Nachschlagetabellen, um diese Informationen bereitzustellen.
Normalerweise würden Sie die Daten reduzieren, indem Sie VLOOKUP verwenden, um Daten aus den orangefarbenen und gelben Tabellen in die blaue Tabelle zu ziehen. Da sich das Schlüsselfeld jedoch nicht auf der linken Seite jeder Tabelle befindet, müssen Sie entweder zu INDEX und MATCH wechseln oder die Nachschlagetabellen neu anordnen.
Ab Excel 2013 können Sie die Nachschlagetabellen dort belassen, wo sie sich befinden, und sie im Pivot-Tabellenbericht selbst kombinieren.
Damit diese Technik funktioniert, müssen alle drei Tabellen als Tabelle formatiert sein. Wählen Sie eine Zelle in jedem Datensatz aus und wählen Sie Startseite, Als Tabelle formatieren oder drücken Sie Strg + T. Die drei Tabellen heißen zunächst Tabelle1, Tabelle2 und Tabelle3. Ich verwende die Registerkarte Design der Tabellentools der Multifunktionsleiste und benenne jede Tabelle neu. Ich ändere auch die Farbe jeder Tabelle. In diesem Beispiel heißt die blaue Tabelle Daten. Die orangefarbene Tabelle lautet RegionTable. Die gelbe Tabelle ist SectorTable.
Hinweis
Einige werden Ihnen sagen, dass Sie geeky Namen wie Fact, TblSector und TblRegion verwenden sollten. Wenn jemand Sie so belästigt, stehlen Sie einfach seinen Taschenschutz und lassen Sie ihn wissen, dass Sie englisch klingende Namen bevorzugen.
Um eine Tabelle umzubenennen, geben Sie einen neuen Namen in das Feld links auf der Registerkarte Design der Tabellentools ein. Tabellennamen sollten keine Leerzeichen enthalten.
Sobald die drei Tabellen definiert sind, wechseln Sie zur Registerkarte Daten und klicken Sie auf Beziehungen.
Klicken Sie im Dialogfeld Beziehungen verwalten auf Neu. Geben Sie im Dialogfeld Beziehung erstellen an, dass das Kundenfeld der Datentabelle mit dem Kundenfeld der SectorTable verknüpft ist. OK klicken.
Definieren Sie eine weitere neue Beziehung zwischen dem Feld Markt in den Feldern Daten und RegionTabelle. Nachdem Sie beide Beziehungen definiert haben, werden sie im Dialogfeld "Beziehungen verwalten" angezeigt.
Herzlichen Glückwunsch: Sie haben gerade ein Datenmodell in Ihre Arbeitsmappe aufgenommen. Es ist Zeit, eine Pivot-Tabelle zu erstellen.
Wählen Sie die leere Zelle aus, in der Ihre Pivot-Tabelle angezeigt werden soll. Standardmäßig wird im Dialogfeld "PivotTable erstellen" das Datenmodell dieser Arbeitsmappe verwenden ausgewählt. Die Position der Pivot-Tabelle wird standardmäßig auf die von Ihnen ausgewählte Zelle festgelegt. OK klicken.
In der Liste Pivot-Tabellenfelder werden alle drei Tabellen aufgelistet. Verwenden Sie das Dreieck links neben einer Tabelle, um den Tabellennamen zu erweitern und die Felder anzuzeigen.
Erweitern Sie die Datentabelle. Wählen Sie das Feld Umsatz. Es wird automatisch in den Bereich Werte verschoben. Erweitern Sie die SectorTable. Wählen Sie das Feld Sektor. Es wird in den Zeilenbereich verschoben. Erweitern Sie die RegionTabelle. Ziehen Sie das Feld Region in den Bereich Spalten. Sie haben jetzt eine Pivot-Tabelle, in der die Daten aus den drei Tabellen zusammengefasst sind.
Hinweis
In jedem Buch, das ich heute geschrieben habe, verwende ich eine andere Technik, um diesen Bericht zu erstellen. Nachdem ich die drei Tabellen definiert habe, wähle ich Zelle A1 und Einfügen, Pivot-Tabelle. Ich aktiviere das Kontrollkästchen Diese Daten zum Datenmodell hinzufügen. Wählen Sie in der Liste Pivot-Tabellenfelder oben in der Liste die Option Alle aus. Wählen Sie Felder für den Bericht aus und definieren Sie die Beziehungen nachträglich. Die oben beschriebene Technik scheint reibungsloser zu sein und erfordert tatsächlich ein wenig Vorausplanung. Die Leute, die Option Explicit in ihrem VBA-Code verwenden, würden diese Methode definitiv mögen.
Durch die Beziehungen im Datenmodell fühlt sich Excel eher wie Access oder SQL Server an, aber mit der ganzen Güte von Excel.
Ich frage das Excel-Team gerne nach ihren Lieblingsfunktionen. Jeden Mittwoch werde ich eine ihrer Antworten teilen. Vielen Dank an Ash Sharma für die Bereitstellung dieser Idee.
Excel-Gedanke des Tages
Ich habe meine Excel Master-Freunde um Rat zu Excel gebeten. Der heutige Gedanke zum Nachdenken:
"Schau nicht nach, wenn du in einer Beziehung bist"
John Michaloudis
