Sonntag, 19. September 2021
14:33 Uhr
14:33 Uhr
SVERWEIS in Pivot-Tabellen dank Power Pivot zur Darstellung der Beziehungen von Datentabellen in Excel
Für einen einfachen Finanzbericht haben wir eine Liste mit gebuchten Beträgen auf Kostenstellen und Projekten. Diese sollen mit einer Pivot-Tabelle zusammengefasst werden.
In der Tabelle sind Art (Projekt oder Kostenstelle), Kostenart, Kostenstelle, Innenauftrag und Betrag ausgewiesen.
Eine Pivot-Tabelle ist über den Ribbon (Symbolleiste/Menüleiste) Einfügen und hier die Schaltfläche PivotTable schnell erstellt.
Als Zeilen in der Pivot-Tabelle sind die Spalten Art, Kostenstelle und Innenauftrag aufgenommen worden und als Wert wird die Summe der Spalte Betrag ausgewiesen. Allerdings sollen zu beiden CO-Objekten jeweils auch Stammdaten ergänzt werden.
Vor Excel 2013 war es hier erforderlich in der Grundtabelle die Stammdaten zum Beispiel durch SVERWEIS oder einer anderen im Artikel "Grundlagen in Excel Verweisfunktionen SVERWEIS WVERWEIS und VERWEIS" vorgestellten Verweisfunktionen zu ergänzen oder alternativ wie im Artikel "Index und Vergleich statt SVERWEIS endlich verstanden und Suche über Verweis nur, wenn es auch etwas zu finden gibt" beschrieben andere Formeln zur Zuordnung von Daten zu verwenden.
Mit Excel 2013 sind jedoch Beziehungen innerhalb der "intelligenten" Tabellen hinzugekommen. Diese ist möglich mit wenigen Schritten durchzuführen und das Ergebnis ist eine erhebliche Erleichterung für das Berichtswesen.
Die einzelnen Schritte mag ich, auch für Kolleginnen und Kollegen im Controlling, etwas ausführlicher beschreiben, da ich die Funktionsweise selbst erst vor wenigen Tagen für mich entdeckt habe.
Für unseren Fall habe ich neben der obigen Berichtstabelle noch zwei weitere Tabellen, die Stammdaten zu den CO Objekten Kostenstelle und Innenauftrag enthalten.
Im Ribbon (Symbolleiste) Start kann über die Schaltfläche "Als Tabelle formatieren" Daten in eine Datentabelle formatiert werden und einen passenden Tabellennamen zugewiesen werden.
Ich gebe den einzelnen Tabellen meist einen passenden Kurznamen mit vorangestellten "T_".
Im Beispiel sind das folgende drei Tabellen, die ich nun zur Verfügung habe.
1.1. Tabelle T_Bericht
Hier sind die einzelnen Daten unseres Berichtes vorhanden.
1.2. Tabelle T_Auftrag
Hier sind die Stammdaten zum Feld Auftrag zu finden.
1.3. Tabelle T_Kostenstelle
In dieser Tabelle sind die Stammdaten der Kostenstelle enthalten.
Nun haben wir also insgesamt drei Tabellen mit eigenen Namen:
Danach können Beziehungen gepflegt werden.
Nun kann die Berichtstabelle mit den einzelnen Stammdatentabellen verknüpft werden. In der folgenden Abbildung sind beide Tabellen mit der Tabelle T_Bericht verknüpft.
Es handelt sich hier um eine N:1 Beziehung,
Während in der Tabelle T_Bericht Kostenstelle und Innenauftrag mehrmals vorkommen (N) können, sind diese in der Stammdatentabelle T_Auftrag oder T_Kostenstelle jeweils nur einmal vorhanden (1).
Als Verknüpfung wird daher die Tabelle T_Bericht gewählt mit der Spalte Innenauftrag (im Beispiel 1) oder Kostenstelle (Beispiel 2) als Fremdschlüssel (Spalte (fremd) und die Verwandte Tabelle ist dann die Tabelle in der die Daten jeweils einmalig vorkommen. Hier sind als Verwandte Tabellen T_Auftrag und die Verwandte Spalte (primär) Auftrag (Beispiel 1) sowie T_Kostenstelle und die verwandte Spalte (primär) Kostenstelle (Beispiel 2).
Dabei muss die Beschriftung der Spalte nicht identisch sein, aber es bedarf einer Übereinstimmung der beiden Daten.
Im Ergebnis sind also zwei N:1 Verknüpfungen zwischen T_Bericht und den Stammdatentabellen als verwandte Nachschlagetabellen unter Beziehungen verwalten gepflegt und werden so auch in der folgenden Abbildung dargestellt.
Im Ergebnis haben Sie nun intern ein Datenmodell mit Verknüpfungen in der Arbeitsmappe erstellt, auf das sie nun auch zugreifen können.
Innerhalb der Feldauswahl der PivtotTable-Felder erscheinen nun nicht nur die Felder der Berichtstabelle, sondern auch die Stammdatenfelder zur Auswahl.
Für unsere Pivot-Tabelle kann ich nun folgende Felder übernehmen
Zeilen:
Als Werte wir Betrag (T_Bericht) genommen und eine Summe gebildet.
Meine fertige Auswertung sieht dabei wie folgt aus:
Hier sind nun zu den Kostenstellen und Aufträgen auch die jeweiligen Stammdaten mit aufgeführt.
a) Kostenstelle mit Stammdaten:
b) Auftrag mit Stammdaten:
Im Ergebnis entspricht dieses einer Grundtabelle, in der ich vorher mühsam die Stammdaten hätte ergänzen müssen.
Dieser kann einfach mit einer Leertaste überschrieben werden
Hier können neue Berechnungen durchgeführt werden, Spalten eingefügt werden und weitere Bearbeitungen des Datenmodells vorgenommen werden.
In der Standardansicht sind hier alle im Datenmodell vorhandenen Tabellen aufgeführt und es können auch noch externe Daten hinzugefügt werden.
Über die Diagrammansicht ist auch die Verknüpfung der gewählten Tabellen ersichtlich.
Hier ist auch die 1:N Verknüpfung direkt ersichtlich.
Dank der Neuauflage von "Excel Pivot-Tabellen für dummies" von Martin Weiß (www.tabellenexperte.de) habe ich mich nun auch endlich einmal intensiver mit der Thematik beschäftigt.
(17. Februar 2021) Paperback ISBN: 9783527718214
u.a. bei Amazon für 20,00 € *
Eine ausführliche Vorstellung des Buches folgt hier noch im Blogartikel. Dennoch mag ich an dieser Stelle das Buch schon einmal als Empfehlung weiter geben.
Insgesamt bin ich der Meinung, dass damit das Berichtswesen mit Excel auch noch ein Stück vereinfacht werden kann und die Frage einer Kollegin, wie SVERWEIS in Pivot Tabellen genutzt werden können hoffentlich dank der Beschreibung hier im Artikel auch schon ein Stück weiter geholfen hat.
Meiner Erfahrung nach lohnt es sich immer sich auch in scheinbar vertrauter Software, wie eben auch Excel, auch die Zeit zu nehmen um Dashboards oder auch neue Techniken, wie hier bei Pivot Tabellen zu lernen und in der Praxis einzusetzen.
Beispiele für ein erweitertes Berichtswesen auch in Richtung Dashboards sind auch in den Artikeln "Pivottabellen ab Excel 2010 dynamischer filtern mit Datenschnitten am Beispiel Hochschulfinanzstatistik" und besonders "Datentrends für Drittmittelstatistik mit Sparklines ab Excel 2010 darstellen durch Liniendiagramme in Zellen" festgehalten.
Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionen und Bestellmöglichkeit zu finden.
In der Tabelle sind Art (Projekt oder Kostenstelle), Kostenart, Kostenstelle, Innenauftrag und Betrag ausgewiesen.
Eine Pivot-Tabelle ist über den Ribbon (Symbolleiste/Menüleiste) Einfügen und hier die Schaltfläche PivotTable schnell erstellt.
Als Zeilen in der Pivot-Tabelle sind die Spalten Art, Kostenstelle und Innenauftrag aufgenommen worden und als Wert wird die Summe der Spalte Betrag ausgewiesen. Allerdings sollen zu beiden CO-Objekten jeweils auch Stammdaten ergänzt werden.
Vor Excel 2013 war es hier erforderlich in der Grundtabelle die Stammdaten zum Beispiel durch SVERWEIS oder einer anderen im Artikel "Grundlagen in Excel Verweisfunktionen SVERWEIS WVERWEIS und VERWEIS" vorgestellten Verweisfunktionen zu ergänzen oder alternativ wie im Artikel "Index und Vergleich statt SVERWEIS endlich verstanden und Suche über Verweis nur, wenn es auch etwas zu finden gibt" beschrieben andere Formeln zur Zuordnung von Daten zu verwenden.
Mit Excel 2013 sind jedoch Beziehungen innerhalb der "intelligenten" Tabellen hinzugekommen. Diese ist möglich mit wenigen Schritten durchzuführen und das Ergebnis ist eine erhebliche Erleichterung für das Berichtswesen.
Die einzelnen Schritte mag ich, auch für Kolleginnen und Kollegen im Controlling, etwas ausführlicher beschreiben, da ich die Funktionsweise selbst erst vor wenigen Tagen für mich entdeckt habe.
Schritt 1: Grundtabellen als "intelligente" Tabelle zu formatieren.
Im Artikel ""Als Tabelle formatieren" um eine dynamische Datenquelle für Pivot-Tabellen zu erhalten" bin ich schon auf Vorzüge vom Formatieren von Tabellen eingegangen und hier ist dieses besonders praktisch.Für unseren Fall habe ich neben der obigen Berichtstabelle noch zwei weitere Tabellen, die Stammdaten zu den CO Objekten Kostenstelle und Innenauftrag enthalten.
Im Ribbon (Symbolleiste) Start kann über die Schaltfläche "Als Tabelle formatieren" Daten in eine Datentabelle formatiert werden und einen passenden Tabellennamen zugewiesen werden.
Ich gebe den einzelnen Tabellen meist einen passenden Kurznamen mit vorangestellten "T_".
Im Beispiel sind das folgende drei Tabellen, die ich nun zur Verfügung habe.
1.1. Tabelle T_Bericht
Hier sind die einzelnen Daten unseres Berichtes vorhanden.
1.2. Tabelle T_Auftrag
Hier sind die Stammdaten zum Feld Auftrag zu finden.
1.3. Tabelle T_Kostenstelle
In dieser Tabelle sind die Stammdaten der Kostenstelle enthalten.
Stammdatenlisten in SAP ERP System
Im SAP Umfeld können solche Stammdatenlisten (für Innenauftrag und Kostenstellen) entweder einfache Stammdatenlisten wie KS13 oder KOK5 sein oder auch Eigenentwicklungen wie SAP Query. Ein Beispiel ist im Artikel "SAP Query Stammdaten PSM / CO Innenauftrag" beschrieben, aber auch sonst finden sich im Blog und meinem Buch zum Berichtswesen im SAP Controlling einige Beispiele für das Erstellen geeigneter Berichte.
Nun haben wir also insgesamt drei Tabellen mit eigenen Namen:
- T_Bericht
- T_Innenauftrag
- T_Kostenstelle
Schritt 2: Daten - Datentools - Beziehungen
Nachdem die relevanten Tabellen als Tabelle formatiert wurden (ich bezeichne diese nun als Datentabellen) aktiviert sich die Schaltfläche "Beziehungen" im Ribbon "Daten" unter Datentools.
Danach können Beziehungen gepflegt werden.
Nun kann die Berichtstabelle mit den einzelnen Stammdatentabellen verknüpft werden. In der folgenden Abbildung sind beide Tabellen mit der Tabelle T_Bericht verknüpft.
Es handelt sich hier um eine N:1 Beziehung,
Während in der Tabelle T_Bericht Kostenstelle und Innenauftrag mehrmals vorkommen (N) können, sind diese in der Stammdatentabelle T_Auftrag oder T_Kostenstelle jeweils nur einmal vorhanden (1).
Als Verknüpfung wird daher die Tabelle T_Bericht gewählt mit der Spalte Innenauftrag (im Beispiel 1) oder Kostenstelle (Beispiel 2) als Fremdschlüssel (Spalte (fremd) und die Verwandte Tabelle ist dann die Tabelle in der die Daten jeweils einmalig vorkommen. Hier sind als Verwandte Tabellen T_Auftrag und die Verwandte Spalte (primär) Auftrag (Beispiel 1) sowie T_Kostenstelle und die verwandte Spalte (primär) Kostenstelle (Beispiel 2).
Dabei muss die Beschriftung der Spalte nicht identisch sein, aber es bedarf einer Übereinstimmung der beiden Daten.
Im Ergebnis sind also zwei N:1 Verknüpfungen zwischen T_Bericht und den Stammdatentabellen als verwandte Nachschlagetabellen unter Beziehungen verwalten gepflegt und werden so auch in der folgenden Abbildung dargestellt.
Im Ergebnis haben Sie nun intern ein Datenmodell mit Verknüpfungen in der Arbeitsmappe erstellt, auf das sie nun auch zugreifen können.
Schritt 3 PivotTable mit Datenmodell der Arbeitsmappe erstellen
Bei der Erstellung eines Datenmodells mit der Schaltfläche PivotTabele in der Symbolleiste Einfügen unter Tabellen gibt es nun nicht nur Tabelle oder Bereiche als zu analysierende Daten, sondern auch den Punkt "Das Datenmodell dieser Arbeitsmappe verwenden", welcher vorher noch nicht vorhanden war.
Innerhalb der Feldauswahl der PivtotTable-Felder erscheinen nun nicht nur die Felder der Berichtstabelle, sondern auch die Stammdatenfelder zur Auswahl.
Für unsere Pivot-Tabelle kann ich nun folgende Felder übernehmen
Zeilen:
- Art (T_Bericht)
- Kostenstelle (T_Bericht)
- Kurztext (T_Kostenstelle)
- Verantwortlich (T_Kostenstelle)
- Innenauftrag (T_Bericht)
- Kurztext (T_Auftrag)
- Arbeitsbeginn (T_Auftrag)
- Arbeitsende (T_Auftrag)
Als Werte wir Betrag (T_Bericht) genommen und eine Summe gebildet.
Meine fertige Auswertung sieht dabei wie folgt aus:
Hier sind nun zu den Kostenstellen und Aufträgen auch die jeweiligen Stammdaten mit aufgeführt.
a) Kostenstelle mit Stammdaten:
b) Auftrag mit Stammdaten:
Im Ergebnis entspricht dieses einer Grundtabelle, in der ich vorher mühsam die Stammdaten hätte ergänzen müssen.
Anmerkung 1: (Leer) in Pivot Tabelle (Zelle) entfernen
Da ich zur Kostenstelle in der Pivot-Tabelle verständlicherweise keine Daten zum Feld Innenauftrag habe, erscheint hier in der Pivot der Hinweis (Leer).
Dieser kann einfach mit einer Leertaste überschrieben werden
Anmerkung 2: Datenmodell verwalten unter Power Pivot
Unter der Schaltfläche "Datenmodell verwalten" unter Datentools in der Symbolleiste Daten können die im Datenmodell vorhandenen Daten auch noch weiter bearbeitet werden.
Hier können neue Berechnungen durchgeführt werden, Spalten eingefügt werden und weitere Bearbeitungen des Datenmodells vorgenommen werden.
In der Standardansicht sind hier alle im Datenmodell vorhandenen Tabellen aufgeführt und es können auch noch externe Daten hinzugefügt werden.
Über die Diagrammansicht ist auch die Verknüpfung der gewählten Tabellen ersichtlich.
Hier ist auch die 1:N Verknüpfung direkt ersichtlich.
Fazit
Die Verknüpfung von Daten mit Beziehungen innerhalb einer Arbeitsmappe und der damit verbundene Vorteil von Datentabellen ist nur ein Vorteil, den Power Pivot und Power Query für Datenmodelle in neueren Excelversionen anbietet.Dank der Neuauflage von "Excel Pivot-Tabellen für dummies" von Martin Weiß (www.tabellenexperte.de) habe ich mich nun auch endlich einmal intensiver mit der Thematik beschäftigt.
Excel Pivot-Tabellen für Dummies
Autor: Martin Weiß
Verlag: Wiley-VCH
2. Auflage(17. Februar 2021) Paperback ISBN: 9783527718214
u.a. bei Amazon für 20,00 € *
Eine ausführliche Vorstellung des Buches folgt hier noch im Blogartikel. Dennoch mag ich an dieser Stelle das Buch schon einmal als Empfehlung weiter geben.
Insgesamt bin ich der Meinung, dass damit das Berichtswesen mit Excel auch noch ein Stück vereinfacht werden kann und die Frage einer Kollegin, wie SVERWEIS in Pivot Tabellen genutzt werden können hoffentlich dank der Beschreibung hier im Artikel auch schon ein Stück weiter geholfen hat.
Berichtswesen nicht nur mit Excel
Beruflich ist ein Schwerpunkt meiner Arbeit das Controlling und Berichtswesen. Neben Excel arbeite ich hier auch besonders gerne mit SAP. Schon bei der Konzeption eines umfangreichen Berichtes und etwaiger Dashboards ist es hier hilfreich sich im Vorfeld passende Gedanken zu machen. Hier habe ich im Buch »Berichtswesen im SAP®-Controlling« (Buchvorstellung, für 19,95 EUR bestellen) einige Punkte festgehalten.
Im Blog finden Sie aber auch regelmäßig Praxisbeispiele rund um die Themen SAP, Berichtswesen und Controlling. Viele Beispiele sind dabei mit Bezug zur Hochschule, aber können, wie der Artikel "Statistische Kennzahlen für Verrechnung in SAP - Umlage und Verteilung nicht nur im Hochschulcontrolling und Hochschulberichtswesen" auch für andere Branchen genutzt und als Grundlage zum Aufbau eines eigenen Berichtswesens genutzt werden. Aber auch sonst finden sich hier immer wieder Artikel, die nicht nur im Bereich Controlling und Berichtswesen an Hochschulen von Bedeutung sind.
Ich würde mich freuen, wenn meine Bücher (Publikationen) aber auch Schulungen (Workshop & Seminare) auch für Sie interessant wären. Weitere Partnerangebote, wie auch eine Excel Schulung zu Pivot finden Sie ebenfalls unter der Rubrik Onlineshop.
Meiner Erfahrung nach lohnt es sich immer sich auch in scheinbar vertrauter Software, wie eben auch Excel, auch die Zeit zu nehmen um Dashboards oder auch neue Techniken, wie hier bei Pivot Tabellen zu lernen und in der Praxis einzusetzen.
Beispiele für ein erweitertes Berichtswesen auch in Richtung Dashboards sind auch in den Artikeln "Pivottabellen ab Excel 2010 dynamischer filtern mit Datenschnitten am Beispiel Hochschulfinanzstatistik" und besonders "Datentrends für Drittmittelstatistik mit Sparklines ab Excel 2010 darstellen durch Liniendiagramme in Zellen" festgehalten.
ein Angebot von Espresso Tutorials
unkelbach.link/et.books/
unkelbach.link/et.reportpainter/
unkelbach.link/et.migrationscockpit/
Diesen Artikel zitieren:
Unkelbach, Andreas: »SVERWEIS in Pivot-Tabellen dank Power Pivot zur Darstellung der Beziehungen von Datentabellen in Excel« in Andreas Unkelbach Blog (ISSN: 2701-6242) vom 19.9.2021, Online-Publikation: https://www.andreas-unkelbach.de/blog/?go=show&id=1234 (Abgerufen am 17.4.2024)
Keine Kommentare - Permalink - Office
Artikel datenschutzfreundlich teilen
🌎 Facebook 🌎 Twitter 🌎 LinkedIn