Listenabgleich: Finde die fehlenden Einträge! 6

Der Abgleich von zwei Listen ist eine häufige Anwendung in Excel. Oft geht es dabei darum, die Daten der einen Tabelle mit weiteren Informationen aus der anderen Tabelle anzureichern:
Artikellisten mit den Preisen, Verkäufer mit den Regionen, Mitarbeiter mit Abteilungen und so weiter. SVERWEIS lässt grüßen!

Heute geht es jedoch darum, aus zwei annähernd gleichen Listen diejenigen Einträge zu identifizieren, die nur in einer der beiden Listen enthalten sind und in der anderen Liste fehlen. Eine Paradeanwendung für “Daten abrufen und transformieren”, besser bekannt als Power Query.

Und so wird’s gemacht:

Ausgangslage: Zwei Artikellisten

Nehmen wir an, wir bekommen von einem unserer Lieferanten regelmäßig eine Komplettliste aller seiner Artikel. Wir wollen nun diejenigen identifizieren, die noch nicht in unserer Materialwirtschaft enthalten sind, damit wir sie dort ergänzen können.

Ich gehe in meinem Beispiel davon aus, dass beide Listen jeweils als eigene Excel-Arbeitsmappen vorliegen. Bei Bedarf kannst du dir die beiden Dateien hier herunterladen: Lieferantenartikel, Materialwirtschaft.
Im folgenden Bild sieht man links die Artikelliste aus unserer Materialwirtschaft und rechts die Liste unseres Lieferanten:

Die beiden Beispieldateien

Beide Dateien sollen nun über die in Excel 2016 / Office 365 enthaltene Funktion “Daten abrufen und transformieren” eingelesen und gegeneinander abgeglichen werden.
(Wenn du noch mit Excel 2010 oder 2013 arbeitest, musst du dazu erst das kostenlose Add-In “Power Query” von Microsoft herunterladen: https://www.microsoft.com/de-de/download/details.aspx?id=39379)

Vorbereitungsarbeiten

Um den Abgleich vorzubereiten öffnen wir eine neue leere Arbeitsmappe und rufen dort das folgende Menü auf:
“Daten | Daten abrufen | Aus Datei | Aus Arbeitsmappe”

Daten aus Arbeitsmappe laden

Wähle die Lieferantendatei aus und klicke auf die Importieren-Schaltfläche. Damit wird eine Datenverbindung hergestellt und ein neues Navigatorfenster geöffnet. Im linken Teil sieht man die Struktur der Arbeitsmappe, also den Namen der Arbeitsmappe und die vorhandenen Arbeitsblätter. Bei mir gibt es nur ein Blatt, ein Klick darauf zeigt eine Vorschau der Daten.

Das Navigator-Fenster

Am rechten unteren Fensterrand befindet sich die Schaltfläche “Laden”. Klicke NICHT auf diese Schaltfläche, sondern auf den kleinen Pfeil daneben. In der Auswahlliste wählst du jetzt den Eintrag “Laden in…”.

Wichtig: “Laden in…”

Ein neues Dialogfenster fordert dich auf, das Ziel der Datenverbindung auszuwählen. Da wir im Moment noch nicht an den Daten interessiert sind, wählen wir hier die Option “Nur Verbindung erstellen” und bestätigen die Auswahl mit OK.

Nur eine Verbindung erstellen

Damit bleibt das Arbeitsblatt noch leer, am rechten Bildschirmrand wird aber jetzt die Liste der vorhandenen Datenverbindungen eingeblendet, in der unsere eben erstellte Verbindung auftaucht.

Jetzt wiederholen wir die oben beschriebenen Schritte und erstellen eine zweite Verbindung zu der Datei aus unserer Materialwirtschaft, so dass am Ende auch diese Verbindung im Fenster rechts erscheint:
“Daten | Daten abrufen | Aus Datei | Aus Arbeitsmappe” -> Datei auswählen -> Links das richtige Arbeitsblatt markieren -> Laden in -> Nur Verbindung erstellen

Übersicht der Abfragen und Verbindungen

Damit man später nicht den Überblick verliert, empfehle ich, die beiden Abfragen/Verbindungen mit einem aussagekräftigen Namen zu versehen. Führe dazu einen Rechtsklick auf die erste Verbindung aus und wähle aus dem Kontextmenü den Eintrag “Umbenennen”

Verbindung umbenennen

Ich habe hier den Namen “Lieferantenliste” vergeben. Wiederhole den Schritt auch für die zweite Verbindung und nenne sie z.B. “Materialwirtschaft”. Jetzt sind alle Vorarbeiten abgeschlossen und wir können uns endlich an den Datenabgleich machen.

Der Datenabgleich

Öffne jetzt den Power Query-Editor:
“Daten | Daten abrufen | Power Query-Editor starten…”

Den Power Query-Editor starten

Markiere links in der Liste die Abfrage mit den Lieferantenartikeln. Dann geht es über das Menüband zur Erstellung einer neuen Abfrage:
“Start | Kombinieren | Abfragen zusammenführen (auf den Pfeil klicken) | Abfragen als neue Abfrage zusammenführen”:

Als neue Abfrage zusammenführen

Im sich nun öffnenden Fenster ist im oberen Bereich die Lieferantenartikelliste eingetragen. Klicke in der Vorschau in die Spalte mit den Artikelnummern. Damit wird festgelegt, dass diese Spalte für den Abgleich verwendet werden soll.

Im unteren Fensterteil wählst du aus dem Dropdown-Feld die Liste aus der Materialwirtschaft und markierst auch hier die Spalte mit den Artikelnummern. Es spielt dabei keine Rolle, dass die Spaltenüberschriften in den beiden Dateien unterschiedlich heißen. Wichtig ist nur, dass es sich bei den Feldern um vergleichbare Inhalte handelt.

Nun kommt der entscheidende Schritt:
Im unteren Teil des Fensters ist ein Auswahlfeld, in dem festgelegt wird, auf welche Weise die beiden Listen miteinander verknüpft werden sollen, die sogenannte Join-Art. Dabei gibt es sechs verschiedene Varianten. Für unseren Fall ist der Eintrag “Linker Anti-Join (Zeilen nur in erster)” relevant. Damit sollte das Fenster so aussehen:

Join-Art: Linker Anti-Join

Zur Erklärung:
Während mit einem “normalen” Join die gemeinsamen Datensätze der Tabellen zusammengeführt werden können, wirft der Anti-Join die Unterschiede aus. In unserem Beispiel also die Datensätze, die nur in der linken (also der ersten ausgewählten) Tabelle enthalten sind, in der rechten (also der zweiten Tabelle) jedoch fehlen.

Nachdem wir alle Eingaben mit OK bestätigt haben, wird eine neue Abfrage (Merge1) erstellt, die alle gewünschten Datensätze enthält:

Die Ergebnisse der neuen Abfrage

Nun sind nur noch zwei Kleinigkeiten notwendig:
Über das Menü “Start | Erste Zeile als Überschriften verwenden” rutschen die eigentlichen Spaltenbezeichnungen nach oben.
Und die dritte Spalte, in der nur die Einträge “Table” zu sehen ist, brauchen wir nicht und entfernen diese mit einem Klick auf die Schaltfläche “Spalten entfernen”, nachdem wir sie zuvor markiert haben.

Damit sind wir fertig und können die Ergebnisse nach Excel übernehmen:
“Start | Schließen & Laden”

Der letzte Feinschliff

In einem neuen Tabellenblatt werden nun alle Artikel aufgelistet, die noch nicht in unserer Materialwirtschaft enthalten sind und die wir jetzt dort nachpflegen können.

Und das Allerschönste kommt noch:
Sobald unser Lieferant eine neue Artikelliste liefert, kann ein neuer Abgleich praktisch auf Knopfdruck durchgeführt werden. Dazu musst du nur in die vorhandene Tabelle mit den letzten Abgleichergebnissen klicken und im Menü “Daten | Alle aktualisieren” auswählen und schon werden im Hintergrund die beiden Listen eingelesen und abgeglichen:

Die Abfrage einfach aktualisieren

Voraussetzung ist lediglich, dass sich der Aufbau der Listen nicht ändert und diese wieder unter dem gleichen Namen am gleichen Speicherort abgelegt werden.

Ich hoffe, spätestens jetzt bist du auf den Geschmack gekommen, um dich ein wenig mehr mit Daten abrufen und transformieren / Power Query zu beschäftigen.