Startseite

Outlook | Excel | Word | PowerPoint | Adobe Acrobat CD | Blog

XING | Facebook |


Pia Bork
Training, Support, Coaching für Office und Filesite


 

Impressum und Datenschutz

Creative Commons License  

Tipps und Tricks rund um Excel

A B C D E F G H I/J K
L M N O P/Q R S T U-W X-Z

 

Pivot-Bericht über mehrere Tabellen (1)

  1. Pivot: Start
  2. Mehrere Datenbereiche zusammenfassen
  3. Beispiel Rücksendungen zählen
  4. Beispiel Fragebogen auswerten
  5. Beispiel Datum gruppieren
  6. Pivot-Bericht aus mehreren Tabellenblättern (ab Excel 2013)
  7. Beispiel Textlisten auswerten (Blog) 1 | 2

 

Ab Excel 2013 können Sie Pivot-Berichte über mehrere Tabellen erstellen. In Teil 1 geht es darum, einen Bericht aus zwei Tabellen zu erstellen; Teil 2 zeigt, wie man mit mehreren Tabellen umgeht; Teil 3 erklärt, wie berechnete Felder für diese speziellen Pivot-Berichte erstellt werden.

In zwei Tabellenblättern werden Daten über die Umsätze von Außendienstmitarbeitern geführt.

Stammdaten mit der ID, Namen, Adresse und anderen festen Daten. Sie bekommt den Tabellennamen "Tab_Stammdaten".

 

 

Umsatz mit der ID, dem Umsatz und Angaben zum Auftrag. Sie heißt "Tab_Umsatz".

Aus beiden Blättern sollen die Daten zusammengefasst werden. Maßgeblich ist dabei die ID. Für die ID "2" soll aus dem Stammdaten-Blatt der Name des Mitarbeiters bezogen werden und aus dem Umsatz-Blatt sollen die Aufträge addiert werden. Im Pivot-Bericht soll also stehen: Maier - 8.100.

 

Voraussetzungen:

  1. Es gibt mindestens zwei Tabellen, alle Tabellen sind formatierte Tabellen und haben einen Tabellennamen (nicht den Blattnamen, sondern über "Tabellentools | Entwurf").
  2. In allen Tabellen gibt es das gleiche eindeutige Merkmal, anhand dessen jeder Datensatz zugeordnet werden kann - bei mir ist es die ID.
  3. In einer Tabelle gibt es eine eindeutige Zuordnung des Merkmales -bei mir ist das die Tabelle "Stammdaten", in der die ID nur jeweils einmal vorkommt. Es sind keine Duplikate für die ID erlaubt.
  4. In allen anderen Tabellen ist das eindeutige Merkmal vorhanden, darf dort auch mehrfach vorkommen - bei mir in der Tabelle "Umsatz", in der die ID mehrfach vorkommt.

Die Spaltennamen für das Merkmal müssen nicht identisch sein. Sie können zum Beispiel in der "Stammdaten"-Tabelle ID schreiben und in der "Umsatz"-Tabelle "Vertreter-Nr." verwenden. Klarer ist es natürlich, wenn Sie den gleichen Namen verwenden.

Man nennt so eine Beziehung 1:n, zu einer (1) ID gibt es viele (n) Umsätze. Erlaubt wäre für den Excel-Pivot-Bericht auch eine 1:1-Beziehung - zu einer ID gibt es nur einen Umsatz. Nicht möglich sind n:n Beziehungen - die IDs sind verschiedenen Mitarbeitern zugeordnet und jeder hat viele Aufträge.

In unserem Beispiel handelt es sich um eine 1:n-Beziehung. Tabelle "Stammdaten" ist die eindeutige Tabelle - also die "1"-Seite. Tabelle Umsatz führt mehrfache Werte pro ID - stellt also die "n"-Seite dar.

1. Schritt: Stellen Sie eine Beziehung her

  • Wählen Sie "Daten | Beziehungen | Neu"
  • Im Feld "Tabelle" wählen Sie die N-Tabelle. Im Beispiel "Tab_Umsatz".
  • Wählen als "Spalte (fremd)" das eindeutige Merkmal. Im Beispiel die Spalte "ID".
  • Im Feld "Verwandte Tabelle" wählen Sie die 1-Tabelle. Im Beispiel "Tab-Stammdaten".
  • Wählen Sie als "Verwandte Spalte (primär)" wieder das eindeutige Merkmal. Im Beispiel wieder die Spalte "ID".

Hinweis: Natürlich stehen die Tabellen nicht auf dem gleichen Blatt und sind auch viel größer. Ich habe hier nur der Deutlichkeit halber die jeweils ersten Spalten zusammengestellt, nebeneinander gesetzt und dann den Dialog darunter eingeblendet. So sehen Sie deutlich, welche Tabelle wo zugeordnet werden muss.

 

2. Schritt: Erstellen Sie den Pivot-Bericht

Klicken Sie in die Tabelle mit den Stammdaten und wählen Sie "Einfügen | PivotTable".

Als "Tabelle/Bereich" muss der Tabellenname aufgeführt werden.

Wählen Sie "Neues Arbeitsblatt".

Aktivieren Sie "Dem Datenmodell diese Daten hinzufügen".

Berstätigen Sie mit OK.

 

 

Sie befinden sich im aktiven Bereich - nur "Tab_Stammdaten" ist zu sehen. Nur diese Tabelle ist zur Zeit für einen Pivot-Bericht nutzbar.

Klicken Sie rechts im Pivot-Bereich auf "Alle".

 

 

 

Klicken Sie "Tab_Umsatz" mit rechter Maustaste an und wählen Sie "Auf der aktiven Registerkarte anzeigen" - damit "Tab_Umsatz" zu den nutzbaren Tabellen hinzugefügt.

Kehren Sie wieder zurück zu "Aktiv".

 

Auf der "Aktiv"-Ansicht sind jetzt beide Tabellen zu sehen.

Ziehen Sie "Name" in Zeilen und "Umsatz" in die Werte.

 

Das Ergebnis:

 

Wenn Sie die Datenbeziehung schon hergestellt haben und nachträglich in den formatierten Tabellen die Spaltennamen ändern, findet Excel die Werte nicht mehr! Im Datenmodell bleiben die anfangs gewählten Spaltennamen stehen. Sie müssen auf "Daten | Verbindungen | Alle aktualisieren" klicken, damit die Spaltennamen angepasst werden.

13.02.2016

Datenbeziehungen

Die Datenbeziehungen 1:n stammen aus dem Datenbankmodell. Mehr dazu lesen Sie in der Wikipedia