Excel – Namenslisten mit Pivot zusammenfassen

Die Listen sind die ähnlich wie gestern: drei Listen auf drei Tabellenblättern als formatierte Tabellen angelegt. Im Unterschied zu gestern ist Liste 1 vollständig, die anderen beiden sind Teilmengen daraus. Heute ermittle ich nicht eine Zusammenstellung, sondern eine Übersicht, welcher Name in welcher Liste auftaucht.

Mein Anwendungsfall sind sehr große Einladungslisten. Es werden 5.000 Personen mit einer Vorankündigung angeschrieben. Die Interessierten melden sich via Internetformular; dabei werden die Zusagen automatisch gesammelt und die Liste als Excel-Tabelle erzeugt – das ist eine Teilmenge aus den anfangs Angeschriebenen. Die Interessierten erhalten irgendwann eine weitere Mail mit der Einladung und entscheiden dann endgültig, ob Sie teilnehmen – wieder eine automatisch erstellte Excel-Tabelle. Sie ist erneut reduziert.

 

Es ist wichtig, dass die Listen  formatierte Tabellen sind! Die Tabellen sind benannt als tbl_Liste1, tbl_Liste2 und tbl_Liste3.

 

Achtung! Das beschriebene Vorgehen funktioniert erst ab Excel 2013.

 

Es werden drei Schritte benötigt:

  1. Zwischen den Tabellen werden Beziehungen hergestellt.
  2. Eine Pivot auf die erste Liste tbl_Liste1 wird erstellt und dem Datenmodell hinzugefügt.
  3. Alle Listen werden in die Pivot integriert.

 

Schritt 1: Beziehungen herstellen

Ich werde gleich die Anfangs-Pivot auf die tbl_Liste1 aufsetzen – Liste 1 fungiert damit als meine Referenzliste. Die beiden anderen Listen müssen zu Liste 1 eine Beziehung herstellen. Es muss also eine Beziehung von Liste 2 auf Liste 1 und von Liste 3 auf Liste 1 erstellt werden.

  • Klicken Sie auf “Daten | Datentools | Beziehungen” und dann auf “Neu”.
  • Füllen Sie aus “Tabelle: tbl_Liste2” und “Spalte (fremd): NameGesamt”
    ”Verwandte Tabelle: tbl_Liste1” und “Verwandte Spalte (primär): NameGesamt”27.02.2018_0007
  • Bestätigen Sie mit OK.
  • Klicken Sie erneut auf “Neu” und stellen die Beziehung her von “Tabelle: tbl_Liste3” auf “Verwandte Tabelle: tbl_Liste1” und wieder mit der  Spalte “NameGesamt”.
  • Sie haben jetzt zwei Beziehungen:
    27.02.2018_0008

 

Schließen Sie das Fenster.

 

Schritt 2: Dem Datenmodell hinzufügen

Die aktive Zelle muss in tbl_Liste1 stehen, dann auf “Einfügen | Pivot-Table” klicken. Eingestellt wird, dass die Pivot auf einem neuen Datenblatt erstellt wird und die Daten dem Datenmodell hinzugefügt werden.

27.02.2018_0009

 

Schritt 3: Integration der übrigen Listen

In der Pivot-Tabelle haben Sie am rechten Rand zwei Anzeigen: Aktiv und Alle. Klicken Sie auf “Alle”. Damit werden die drei Listen eingeblendet. Wenn das nicht geschieht, haben Sie Ihre Listen nicht als formatierte Tabellen angelegt.

27.02.2018_0010

 

Richten Sie zuerst die Grundpivot ein, indem Sie die Felder für tbl_Liste1 aufklappen und das Feld “NameGesamt” in die Zeilen ziehen. Ziehen Sie anschließend das gleiche Feld in die Werte.

 

27.02.2018_0012

 

Klicken Sie in der Pivot-Tabelle auf die Spaltenbeschriftung “Anzahl von Name” und benennen Sie sie um in “Liste 1”.

Klappen Sie die Felder von tbl_Liste2 auf, ziehen Sie das Feld “NameGesamt” in die Werte; benennen Sie die Spalte  “Liste 2”. Dann die Felder von tbl_Liste3 aufklappen, das Feld “NameGesamt” in die Werte ziehen und die Spalte “Liste 3” nennen.

Im Pivot-Bereich sieht das so aus:

27.02.2018_0013

 

Und die Pivot-Tabelle sieht so aus:

 

27.02.2018_0014

 

Liste 1 ist vollständig und hat die mit 63 die meisten  Einträge. Liste 2 hat Lücken – nicht alle Namen tauchen in der zweiten Liste auf. Die meisten Einträge fehlen in der dritten Liste.

 

Wichtig ist dabei:

  1. Die Referenzliste (Liste 1) ist vollständig.
  2. In den weiteren Listen fehlen Einträge, es kommen aber keine neuen dazu.

Wollen Sie gucken, wie ich es gemacht habe? Hier gibt es die Beispieldatei Konsolidieren mit Pivot.

Werbeanzeigen

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google Foto

Du kommentierst mit Deinem Google-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s