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:
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“
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.
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…“.
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.
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
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“
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…“
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“:
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:
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:
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“
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:
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.
Das könnte dich auch interessieren:
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
vielen Dank für den Tipp – das funktioniert mit den Beispieltabellen sehr gut.
Ich hatte gehofft, mit der Abfrage eine zusätzliche Absicherung für eine Neuheitenliste einführen zu können.
Allerdings stößt sich die Power Query in meiner Tabelle an einem Grenzwert von 1000 Artikeln. (Win10, Office Professional 2013)
Somit wär das leider hinfällig.
Und was passiert, wenn Artikelnummern häufiger (und an unterschiedlichen Stellen) aufgeführt sind?
Gruß
Marco
Hallo Marco,
ich vermute einmal, das Problem liegt woanders. Power Query kann grundsätzlich mit mehreren 10.000 / mehreren 100.000 Datensätzen umgehen.
Könnte es sein, dass in deiner Tabelle zwischendrin leere Zeilen enthalten sind, so dass die Abfrage alles ignoriert, was danach kommt?
Ich empfehle, die Quelltabelle als „intelligente“ Tabelle zu formatieren (Start | Als Tabelle formatieren). Dann sind leere Zeilen kein Problem und auch neu hinzugekommene Datensätze werden sofort erkannt.
Und wenn Artikelnummern häufiger vorkommen, werden diese eben mehrmals ausgegeben (wenn sie in der ersten Tabelle enthalten sind, und in der zweiten nicht).
Schöne Grüße,
Martin
Hallo Martin, coole Sache, habe öfter Excel Sheets die ich miteinander vergleichen muß, da sich monatlich die Zahlen ändern.
LG Holger
Hallo Holger,
ja, gerade für wiederkehrende Abgleiche ist Power Query hervorragend geeignet.
Schöne Grüße,
Martin
Hallo Martin,
es ist sehr schon, ich habe danach gesucht.
Wie kann ich diese Artikel vernünftig ausdrucken ?
Gruß
Irina
Hallo Irina,
über die Druckfunktion Deines Browsers 🙂
Nein, mal im Ernst: Ich schreibe und veröffentliche die Artikel hier zum Lesen auf dem Blog. Wie und womit die Leser dann die Artikel zu Papier bringen, speichern oder sonstwie weiterverarbeiten, bleibt jedem selbst überlassen. Hierfür kann (und will) ich keine Tipps geben.
Schöne Grüße,
Martin