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.
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
Hallo Martin,
ich konnte die Vogehensweise super nach vollziehen. Alles OK. Aber wie bekomme ich selbst definierte Tabellenbereich [stg+T] in die Power Query hinein ? Bisher konnte ich nur eine Tabelle/-bereich eintragen.
Danke für Deine Superanleitungen.
Gruß Roland
Hallo Roland,
du hast dir bereits selbst die Antwort gegeben: Alles, was mit Strg+T (bzw. über das Menü) als Tabelle formatiert wurde, kann genau über „Daten | Aus Tabelle/Bereich“ nach Power Query übernommen werden. Du musst nur die aktive Zelle innerhalb der formatierten Tabelle platzieren, bevor du diese Funktion aufrufst. Dann erkennt Excel den Bereich selbst.
Schöne Grüße,
Martin
Hallo Martin,
ich danke dir sehr für den Beitrag.
Ich habe zwei Bücherlisten Tabellen mit alt: 11000Z23S und neu: 11500Z26S
In der alten habe ich farbliche Markierungen und Wertungen vorgenommen.
Ich möchte sehen, welche Zeilen in der neuen dazugekommen sind, um später auf die neue zu wechseln.
Aber selbst, wenn ich nur die ersten drei Spalten der jeweiligen Tabellen in den Editor als Tabstopp getrennten Text importiere, kommt ein
[Data.Format.Error] Die Formatierung in „Number“ war nicht möglich.
Habe die 32-bit Version von Office 2016 installiert.
*Nach langem probieren habe ich den Auslöser.
In einer der zum Abgleich irrelevanten nicht 1. Spalte habe ich zu 99% reine Zahlen und zu 1% Buchstaben. Sofern diese Zeile bei der Merge-Differenz neu-alt übrigbleibt, versucht er wohl diese Zelle in Zahlen umzuwandeln. Wie kann ich das unterbinden?
Hallo Horst,
Power Query fügt nach dem Import meist automatisch einen Schritt „Geänderter Typ“ ein. Du kannst diesen Schritt auch einfach manuell aus der Liste der angewendeten Schritte löschen, wenn die automatische Typerkennung nicht das gewünschte Ergebnis liefert.
Schöne Grüße,
Martin
Vielen Dank, jetzt habe ich endlich die Stelle im Funktionsfeld gefunden. Musste für eine Spalte „Int64.Type“ in „type text“ ändern.
Hallo! das Problem vo „Horst“ habe ich auch!
Unsere Artikel-Nummern bestehen aus Zahlen und Buchstaben… Wo genau kann und muss ich den Typ ändern? Kann`s leider nicht finden!
Übrigens Martin – vielen Dank für diese tolle „Anleitung für Dummies“ 🙂
Gruß
Dagmar
Hallo Dagmar,
wenn du in der Liste der angewendeten Schritte „Geänderter Typ“ auswählst, wird oberhalb der der Tabellenansicht in der Bearbeitungszeile der M-Code angezeigt. Da steht dann irgendetwas von =Table.TransformColumnTypes(….) und jeweils in geschweiften Klammern ein Pärchen aus Feldname und Datentyp. Dort musst du nach deinem Artikelfeld suchen und den Typ ändern, so wie Horst geschrieben hat.
Falls die Bearbeitungszeile nicht angezeigt werden sollte: Du kannst sie über das Menü „Ansicht“ mit einem Häkchen vor „Bearbeitungszeile“ einblenden lassen.
Schöne Grüße,
Martin
Hallo Martin,
ich habe zwei Tabellen die über 500.000 Einträge haben. Wie kann ich ein Power Query erstellen so dass es für die großen Tabellen auch funktioniert. Ich habe meine Tabellen importiert. Wie kann ich die importierte Tabelle als Tabelle bei Excel eingeben damit alle Einträge angezeigt werden? Bei mir werden nur die ersten 10.000 Einträge angezeigt.
Vielen Dank im Voraus
Nina
Hallo Nina,
es gibt in Power Query eigentlich keinen Vorgabewert, der die Anzahl der zu ladenden Einträge beschränkt. Wenn du nicht irgendwo in deiner Abfrage einen Filter gesetzt hast, sollten automatisch sämtliche Datensätze in Excel geladen werden.
Schöne Grüße,
Martin
Hallo Martin
Schöner Artikel, sehr hilfreich, Danke dafür.
Leider passt so ein Artikel nicht immer auf alle Excel-Versionen gleichermaßen, so auch bei meinem Excel 2013, da sind ein paar Bezeichnungen anders, aber sei’s drum: Als IT-ler weiß man sich da zu helfen, als Endanwender evtl. aber auch nicht.
Wichtig zu erwähnen scheint mir ein vergessener Hinweis: Das Öffnen bzw. Auswählen der einzulesenden / zu verbindenden Listen erolgt im Menü Power Query, nicht im Excel-Menü „Daten“ – der Satz fehlt in der Anleitung. (In meinem Excel 2013 gibt es im Excel-Menü „Daten“ nämlich den Punkt „Aus Datei“ gar nicht).
Mein Problem ist aber ein anderes: Der Vergleich liefert falsche Ergebnisse!! Es werden Datensätze angezeigt, die in der zweiten Tabelle sehr wohl vorhanden sind. Das ist wenig hilfreich. Noch schlimmer: Es wird ein fehlender Datensatz mit einem Feldeintrag angezeigt, den es so nicht in der ersten Tabelle gibt – wie geht das bitte? Und leider mir die fehlenden Datensätze nicht angezeigt – dass war aber das eigentliche Ziel der Übung! Kann es sein, dass Power-Query da noch so das ein oder andere Problemchen und /oder Fehlerchen besitzt? So kann ich meine Aufgabenstellung jedenfalls nicht erfüllen oder liegt es etwa an der Menge der Zeilen? 4132 wären es in meinem Fall??
Für einen dezenten Lösungshinweis wäre ich sehr dankbar, würde mir viel Zeit durch vermeiden des nochmaligen händischen Abgleichs ersparen.:-) Mit freundlichen Grüßen – Andreas Terveen
Ergänzung / Nachtrag
Hallo Martin
Ich war etwas voreilig – er hat tatsächlich 15 fehlende Datensätze gefunden – das ist gut so.
Leider hat er aber eben auch weitere 68 Datensätze gefunden, die in der Zieldatei schon vorhanden sind!
Das macht dann leider doch einen Nacharbeitungsaufwand mit händischem Suchen, den man ja eigentlich durch die Funktion vermeiden wollte!
Mit freundlichen Grüßen
Andreas Terveen
Hallo Andreas,
du hast Recht, in Excel 2013 (und 2010) war die Menüführung noch eine andere. In diesen Excel-Versionen musste Power Query noch als separates Add-In nachinstalliert werden und hatte dann einen eigenen Menüpunkt. Das habe ich in meiner Anleitung unterschlagen.
Was die von dir beschriebenen fehlerhaft gefundenen Datensätze angeht, bin ich allerdings etwas verwundert. Es liegt definitiv nicht an der Datenmenge, Power Query kann problemlos mit mehreren 100.000 Datensätzen umgehen. Ich tippe daher eher auf unsaubere Quelldaten, der Klassiker hier sind angehängte (oder vorausgestellte oder sonst unnötige) Leerzeichen. Für Power Query werden dann unterschiedliche Datensätze daraus, obwohl sie für den Anwender identisch aussehen.
Schöne Grüße,
Martin
Hallo Martin,
erstmal vielen Dank für deinen super Tipp, hat mir schon erheblich weiter geholfen.
Jetzt gibt es für meinen Fall noch einen Arbeitsschritt, denn ich in das Ganze gerne integrieren würde.
Bisher war ich leider nicht erfolgreich.
Gibt es einen Weg, diese fehlenden Einträge am Ende in die „Materialwirtschaft“ Liste aufzunehmen?
So, dass die Listen am Ende identisch aussehen, zumindest wenn es um die ID-Spalte geht.
Schöne Grüße
Andreas
Hallo Andreas,
klar geht das. Du musst nur im Power Query-Editor eine neue Anfüge-Abfrage erstellen, bei der die durch den Antijoin erzeugte Liste an die Liste der Materialwirtschaft angehängt wird:
– Abfrage Materialwirtschaft auswählen
– Menü „Abfragen anfügen | Abfragen als neu anfügen“
– Im nächsten Fenster die Antijoin-Abfrage auswählen (im Bild oben also „Merge1“)
Fertig.
Schöne Grüße,
Martin
Hallo Martin,
danke für deine Antwort!
Leider ist das nicht ganz was ich erzielen möchte.
Am Ende wird hierbei eine weitere Tabelle erzeugt, eben diese Abfrage.
Ich möchte jedoch, die erhaltenen „fehlenden Artikel“ in die Materialwirtschaft Tabelle (also eine der Quellen der Abfrage) einfügen, um damit direkt weiter zu arbeiten.
Wenn ich es richtig verstehe ist das aber nicht über eine Abfrage zu lösen, da diese mir immer eine neue Tabelle ausgibt.
Daher arbeite ich an einer VBA Lösung, die mir die Liste der fehlenden Artikel in die Materialwirtschaft-Tabelle einfügt und einsortiert.
Hallo Martin,
ein dickes „Danke“ für den Beitrag und die damit verschwundenen Kopfschmerzen!
VG
Anja
Hallo Anja,
dankeschön, freut mich, wenn der Artikel geholfen hat!
Schöne Grüße,
Martin
Hallo Martin, so genau erklärt einfach spitze. War schon verzweifelt da ich keine Hilfe fand.
Allerdings kommt mir nun eine Fehlermeldung
DataFormat.Error Die Konvertierung in Number war nicht möglich. Details: 32-369
Ich habe nun versucht die Nummern neu als Zahl zu formatieren aber das hat nichts geändert.
Hast du zufällig eine Erklärung wo da noch der Fehler bei mir liegen könnte?
Liebe Grüße Verena
Hallo Verena,
diese Meldung beim Aktualisieren weist auf ein Problem in den Quelldaten hin. Das heißt, die betreffende Spalte in der Datenquelle enthält einen Eintrag, der nicht in eine Zahl umgewandelt werden kann. Also vermutlich einen Texteintrag oder irgendein Sonderzeichen. Die Lösung liegt in der Prüfung und Korrektur in der Quelltabelle.
Das Format in der Ausgabetabelle zu ändern hilft hier leider nichts mehr, denn das Problem tritt ja schon vorher auf.
Schöne Grüße,
Martin