Zwei Excel-Listen mit KI abgleichen: Schlüssel und Dubletten zuerst klären

Zwei Excel-Listen lassen sich erst zuverlässig abgleichen, wenn klar ist, was eine Zeile beschreibt und wodurch sie eindeutig identifiziert wird. Eine KI kann Formeln oder einen Power-Query-Schritt vorschlagen. Sie kann aber nicht aus ähnlich klingenden Namen ableiten, ob zwei Zeilen tatsächlich denselben Geschäftsvorgang meinen.

Dieser Artikel zeigt einen vollständigen Abgleich mit fehlenden Schlüsseln und Dubletten. Die Listen und Beträge sind fiktiv. Der Schwerpunkt liegt auf der fachlichen Zuordnung und ihrer Prüfung. Die Werkzeugwahl folgt danach. Für einen kleinen einmaligen Abgleich reicht häufig eine Formel; für wiederkehrende Importe ist ein dokumentierter Transformationsablauf oft leichter nachzuvollziehen.

Was eine Zeile bedeutet

Vor dem Vergleich musst du die Körnung der Daten klären. Enthält die erste Liste einen Auftrag pro Zeile und die zweite eine Auftragsposition pro Zeile, ist eine direkte Eins-zu-eins-Zuordnung falsch. Dass beide eine Spalte "Auftragsnummer" besitzen, löst den Unterschied nicht.

Schreibe für beide Listen einen Satz: "Eine Zeile entspricht ...". Ergänze den betrachteten Zeitraum und den Statusfilter. Eine Liste mit offenen Aufträgen und eine Liste mit sämtlichen Rechnungen haben absichtlich unterschiedliche Inhalte. Ein fehlender Treffer ist dann möglicherweise korrekt und kein Datenfehler.

Der Schlüssel muss zur Körnung passen. Eine Auftragsnummer identifiziert einen Auftrag, aber nicht zwingend eine Position. Für Positionen brauchst du möglicherweise Auftragsnummer plus Positionsnummer. Kundennamen sind als Schlüssel besonders anfällig für Schreibvarianten und Mehrdeutigkeit. Die Entscheidung über die Identität darf nicht still durch eine Ähnlichkeitssuche ersetzt werden.

Zwei künstliche Listen

Liste A enthält fünf offene Vorgänge. Die Kennungen sind als Text gespeichert, damit führende Nullen erhalten bleiben. Die Beträge sind EUR netto. Liste B enthält vier Rückmeldungen aus einem zweiten System.

Die Aufgabe lautet: Ergänze für die fünf Vorgänge aus A den eindeutigen Status aus B. Fehlende und mehrdeutige Zuordnungen müssen sichtbar bleiben. Die Beträge aus A dürfen sich durch den Abgleich nicht verändern. Zusätzliche Kennungen aus B müssen in einer separaten Ausnahmeliste erscheinen.

Die erwartete Lösung vor der Formel festlegen

Für 0012 gibt es genau einen Treffer: geprüft. Für 0013 existieren zwei widersprüchliche Rückmeldungen. Ohne eine dokumentierte Regel ist kein einzelner Status zulässig. Für 0014 und 0015 fehlt eine Rückmeldung. A4 besitzt keinen Schlüssel und darf nicht mit einem anderen leeren Feld verbunden werden. 0016 existiert nur in B.

Die fünf Beträge aus A ergeben 650 EUR. Diese Summe muss nach dem Ergänzen des Status weiterhin 650 EUR betragen. Die Ausnahmeliste enthält die zusätzliche Kennung 0016 sowie den Konflikt zu 0013. Damit ist der Soll-Zustand bekannt, bevor eine KI einen Lösungsvorschlag erzeugt.

Warum ein einzelner XVERWEIS die Dublette versteckt

Microsoft dokumentiert für XLOOKUP beziehungsweise XVERWEIS, dass standardmässig exakt gesucht und der erste gefundene Treffer zurückgegeben wird. Bei mehreren Treffern liefert die Funktion damit nicht automatisch einen Dublettenhinweis. Sie ist laut Dokumentation in Excel 2016 und 2019 nicht verfügbar; prüfe die tatsächlich verwendete Version.

Im Beispiel kann eine einfache Suche für 0013 den Status "offen" aus B2 liefern. Steht B3 davor, erscheint stattdessen "geprüft". Die Formel funktioniert technisch in beiden Fällen, aber das fachliche Ergebnis hängt von der Reihenfolge ab. Genau das darf beim geforderten eindeutigen Abgleich nicht passieren.

Prüfe deshalb zuerst die Anzahl passender Schlüssel. In einer deutschen Excel-Installation kann eine Hilfsspalte mit ZÄHLENWENN die Treffer zählen. Nur bei genau einem Treffer erfolgt die Übernahme. Bei null Treffern steht "kein Treffer", bei mehr als einem "mehrdeutig". Die konkrete Formel muss zu den tatsächlichen Tabellenbezeichnungen und Trennzeichen passen.

Eine nachvollziehbare Formellogik

Angenommen, die Kennung steht in A2 und die Referenzschlüssel in H2:H5. Die Trefferzahl lässt sich mit =ZÄHLENWENN($H$2:$H$5;A2) prüfen. Eine leere Kennung muss davor separat abgefangen werden. Ein leerer Suchwert ist kein gültiger Geschäftsschlüssel, selbst wenn in der Referenzliste ebenfalls eine leere Zelle existiert.

Der fachliche Ablauf lautet: Zuerst Eingabe prüfen, danach Treffer zählen, erst dann Status übernehmen. Die KI erhält diesen Ablauf als Anforderung. Ein Auftrag "Mach einen XVERWEIS" überspringt dagegen die wichtigste Entscheidung. Die Formel ist das Umsetzungsmittel, nicht die Spezifikation.

Nutze für den ersten Durchlauf Hilfsspalten statt einer schwer lesbaren verschachtelten Formel. Eine Spalte zeigt den bereinigten Schlüssel, eine die Trefferzahl, eine den Prüfstatus und eine den übernommenen Wert. Nach bestandener Prüfung kann das Team entscheiden, ob eine kompaktere Fassung die Wartung tatsächlich erleichtert.

Power Query und die Zeilenvermehrung

Microsoft beschreibt beim Zusammenführen von Power-Query-Abfragen die Verbindung über eine oder mehrere Spalten sowie unterschiedliche Join-Arten. Die Schlüsseldatentypen müssen zusammenpassen. Mehrere zusammengehörige Schlüsselspalten müssen in beiden Tabellen in entsprechender Reihenfolge gewählt werden.

Bei einer Eins-zu-viele-Verknüpfung kann das Erweitern der Treffer mehrere Ergebniszeilen pro Ausgangszeile erzeugen. Im Beispiel erhält A2 zwei Treffer aus B. Bei einer vollständigen Erweiterung eines linken Abgleichs entstehen dadurch sechs statt fünf Zeilen. Wenn der Betrag von A2 in beiden Zeilen steht, steigt die sichtbare Summe von 650 auf 850 EUR. Die zusätzlichen 200 EUR sind kein Geschäftsvorfall, sondern ein Zuordnungsfehler für diese Aufgabenstellung.

Ein nachträgliches Entfernen identischer Zeilen löst das Problem nicht zuverlässig, weil die Statuswerte unterschiedlich sind. Die Mehrdeutigkeit muss vor der Übernahme geklärt oder als Ausnahme geführt werden. Ein gültiger Eins-zu-viele-Anwendungsfall benötigt dagegen eine andere Auswertung, die Beträge nicht mehrfach zählt. Die gewünschte Kardinalität ist deshalb Teil der Spezifikation.

Normalisierung mit fachlichen Grenzen

Führende und nachgestellte Leerzeichen lassen sich oft vereinheitlichen. Eine Umwandlung von Text in Zahlen kann dagegen Kennungen zerstören: Aus 0012 wird 12. Wenn das Quellsystem beide Darstellungen als dieselbe Kennung definiert, ist eine dokumentierte Normalisierung möglich. Ohne diese Regel darf die KI sie nicht einfach gleichsetzen.

Auch Gross- und Kleinschreibung, Bindestriche oder Ländercodes können fachlich relevant sein. Lege fest, welche Unterschiede ignoriert werden dürfen. Bewahre den Originalwert neben dem normalisierten Schlüssel auf. Nur so lässt sich später nachvollziehen, warum zwei ursprünglich unterschiedliche Werte zusammengeführt wurden.

Unscharfe Zuordnung nach Namen ist ein eigener Prozess. Sie kann Kandidaten für eine manuelle Prüfung liefern, aber keine sichere Identität allein aus einem Ähnlichkeitsscore beweisen. Ein hoher Score bedeutet nicht automatisch, dass es sich um denselben Kunden handelt. Für automatische Änderungen brauchst du ein passendes fachliches Identitätsmerkmal und eine dafür freigegebene Regel.

Ein guter KI-Arbeitsauftrag

Formuliere: "Liste A enthält einen offenen Vorgang pro Zeile, Liste B enthält Statusmeldungen. Ergänze nur bei eindeutigem Schlüssel einen Status. Leere Schlüssel sind ungültig. Mehrere Treffer bleiben als Ausnahme sichtbar. Erhalte alle fünf Ausgangszeilen und die Summe von 650 EUR. Zeige zusätzliche Schlüssel aus B getrennt. Erzeuge zunächst einen nachvollziehbaren Lösungsplan und eine Prüftabelle."

Dieser Auftrag benennt den erwarteten Effekt und die Invarianten. Die KI muss danach erklären, wie sie Dubletten erkennt und warum keine Zeile verschwindet. Eine fertige Datei ohne diese Erklärung ist schwer abzunehmen. Für wiederkehrende Importe muss zusätzlich feststehen, wie neue Zeilen und geänderte Spalten behandelt werden.

Die Anleitung zu KI-generierten Excel-Formeln vertieft die Funktionsprüfung. Der Beitrag zur Prüfung von Excel-Daten vor einer KI-Auswertung behandelt die Bilanz von Zeilen und Summen über den Abgleich hinaus.

Den Abgleich in beide Richtungen lesen

Eine linke Verknüpfung beantwortet die Frage, welche Informationen aus B zu den Zeilen in A vorliegen. Sie zeigt nicht automatisch alle Zeilen, die nur in B existieren. Im Beispiel bleibt 0016 ausserhalb des Hauptergebnisses. Wenn diese Kennung auf einen fehlenden Vorgang in A hinweist, ist sie fachlich wichtig und braucht eine separate Auswertung.

Lege deshalb vorab fest, welche Richtung zur Aufgabe gehört. Für die Ergänzung offener Vorgänge bleibt A die führende Liste. Für einen vollständigen Bestandsvergleich brauchst du zusätzlich "nur in A", "nur in B" und "in beiden". Ein innerer Join zeigt ausschliesslich gemeinsame Treffer und kann dadurch fehlende Vorgänge unsichtbar machen. Die Wahl der Verknüpfung ist eine inhaltliche Entscheidung.

Auch die Zahl der Schlüssel und die Zahl der Zeilen müssen getrennt betrachtet werden. B besitzt vier Zeilen, aber nur drei unterschiedliche nicht leere Kennungen. Die doppelte 0013 erklärt diese Differenz. Wenn eine Prüfung nur die Zeilenzahl meldet, bleibt die Mehrdeutigkeit verborgen. Das Protokoll muss die für die Aufgabe nötigen Zählungen ausweisen.

Den Dublettenfall fachlich lösen

Eine Dublette ist nicht automatisch ein überflüssiger Datensatz. Die beiden Statusmeldungen zu 0013 können zwei Ereignisse zu verschiedenen Zeiten beschreiben. Wenn B ein Ereignisprotokoll ist, entspricht seine Körnung einer Meldung und nicht einem Vorgang. Dann braucht der Abgleich eine ausdrücklich definierte Regel für den gültigen Status, etwa die letzte bestätigte Meldung anhand eines verlässlichen Zeitstempels.

Im vorliegenden Beispiel fehlt dieser Zeitstempel. Deshalb ist die Auswahl des letzten Tabellenplatzes unzulässig. Die Reihenfolge in einer exportierten Datei muss keine zeitliche Bedeutung besitzen. Die richtige Lösung ist eine Rückfrage an die Datenverantwortung oder ein erneuter Export mit den benötigten Feldern.

Wenn dagegen beide Zeilen tatsächlich denselben fehlerhaft doppelt erfassten Datensatz darstellen, muss die Bereinigung mit Nachweis erfolgen. Bewahre die Zuordnung zur entfernten oder zusammengeführten Zeile auf. Ein stilles "Duplikate entfernen" kann sonst einen Konflikt verdecken, statt ihn zu lösen.

Nach der Klärung wird derselbe Test erneut ausgeführt. Die Kontrollsumme und die Ausgangszeilenzahl bleiben weiterhin gültige Prüfungen. Eine fachlich bestätigte Auflösung der Dublette verändert die erlaubte Statusübernahme, aber nicht automatisch den Betrag des Vorgangs. Jede zusätzliche Änderung braucht ihren eigenen Grund.

Abnahme und Wiederholung

Prüfe den Normalfall 0012, den Dublettenfall 0013, die fehlenden Treffer und den leeren Schlüssel einzeln. Vertausche danach die Reihenfolge der beiden 0013-Zeilen. Das Ergebnis muss weiterhin "mehrdeutig" lauten. Ändert es sich zu einem anderen Status, versteckt die Lösung einen Reihenfolgeeffekt.

Füge eine neue Zeile hinzu und aktualisiere den Abgleich. Prüfe, ob der verwendete Bereich sie erfasst. Ein fester Zellbereich kann bei neuen Daten unbemerkt zu kurz werden. Strukturierte Tabellen oder ein kontrollierter Import helfen, aber auch deren Aktualisierung muss geprüft werden.

Die Abnahme ist bestanden, wenn alle Ausgangszeilen erklärbar erhalten bleiben, keine mehrdeutige Zuordnung übernommen wird und die Summe unverändert ist. Die Ausnahmen müssen eine zuständige Person und einen nächsten Klärungsschritt erhalten. Für den Einsatz im Kundenbestand ergänzt die CRM-Pflege mit KI die organisatorische Perspektive.

Quellenstand: 22.10.2026. Microsoft-Quellen belegen das beschriebene Funktionsverhalten. Listen, Beträge, Kontrollsummen und Abnahmeregeln sind eigene fiktive Beispiele. Die Musterlogik muss in der tatsächlich verwendeten Excel-Umgebung geprüft werden.