Zwei Excel Tabellenblätter auf Unterschiede vergleichen

Zwei Auftragsexporte können gleich viele Zeilen enthalten und trotzdem unterschiedliche Aufträge abbilden. Ein Vergleich von A2 mit A2 reicht nicht aus, wenn jemand eines der Blätter sortiert oder einen Datensatz eingefügt hat.

Ordnen Sie Geschäftsdaten zunächst über eine stabile Kennung zu und vergleichen Sie anschließend die zugehörigen Felder. Unterscheiden Sie drei Ergebnisse: geänderte Datensätze, nur im alten Blatt vorhandene Datensätze und nur im neuen Blatt vorhandene Datensätze. Lassen Sie beide Quellen unverändert und erstellen Sie den Bericht auf einem separaten Blatt.

Den Umfang des Vergleichs festlegen

Fragestellung	Geeignetes Vorgehen
Hat sich der Betrag eines Auftrags geändert?	Auftrags-ID zuordnen und dann den Betrag vergleichen
Welche Aufträge sind hinzugekommen oder fehlen?	Das Vorkommen der IDs in beide Richtungen prüfen
Hat sich eine Formel oder ein Zellformat geändert?	Arbeitsmappenversionen vergleichen, nicht nur Ergebniswerte
Sehen zwei kleine Tabellenblätter unterschiedlich aus?	Nebeneinander anzeigen und die Unterschiede untersuchen

Die Ansicht von Arbeitsblättern nebeneinander von Microsoft erleichtert die visuelle Prüfung. Sie ordnet Datensätze jedoch nicht anhand ihrer fachlichen Kennungen zu. Das folgende Beispiel vergleicht Werte, keine Formatierungen oder Formeltexte.

Mit einem eindeutigen Schlüssel beginnen

Verwenden Sie eine Auftrags-ID, Personalnummer oder eine andere Kennung, die in jeder Quelle nur einmal vorkommen sollte. Namen allein sind häufig ungeeignet. Hat ein Auftrag mehrere Positionen, ist die Auftrags-ID allein nicht eindeutig. Verwenden Sie dann eine dokumentierte Kombination, etwa Auftrags-ID und Positionsnummer.

Prüfen Sie vor dem Abgleich leere IDs, Dubletten, führende Nullen sowie Unterschiede zwischen Text und Zahlen. Bewahren Sie die ursprüngliche ID neben einer gegebenenfalls bereinigten Fassung auf. Entfernen Sie keine bedeutungsvollen Leerzeichen oder führenden Nullen, nur um eine Übereinstimmung zu erzwingen. Unsere Anleitung zum Abgleichen von Spalten behandelt die verwandte Aufgabe, Felder in eine vorhandene Tabelle zu übernehmen.

Die folgenden Formeln verwenden deutsche Funktionsnamen und Semikolons. Je nach Excel-Sprache und Regionseinstellungen müssen Sie die Funktionsnamen oder Trennzeichen anpassen. Das Beispiel verwendet einfache IDs ohne Platzhalterzeichen und nicht leere numerische Beträge.

Einen kleinen Vergleich nachvollziehen

Erstellen Sie in einer Kopie der Arbeitsmappe zwei Blätter mit den Namen Old und New. Tragen Sie diese fiktiven Datensätze in die Spalten A und B ein, mit Überschriften in Zeile 1. Die Zeilenfolge ist bewusst unterschiedlich.

ID in Old	Betrag in Old	ID in New	Betrag in New
A101	120	A103	75
A102	80	A101	120
A103	75	A105	60
A104	50	A102	95

Die ersten beiden Spalten gehören auf Old, die letzten beiden auf New. Es handelt sich um Beispieldaten, nicht um ein Kundenergebnis oder einen Geschwindigkeitsvergleich.

Zählen Sie zunächst auf jedem Blatt in einer Hilfsspalte, wie oft die ID der jeweiligen Zeile in derselben Quelle vorkommt:

=ZÄHLENWENN($A$2:$A$5;A2)

Für jede nicht leere ID dieses Beispiels sollte das Ergebnis 1 sein. Untersuchen Sie Werte über 1, bevor Sie einen Suchverweis ausführen. ZÄHLENWENN unterscheidet nicht zwischen Groß- und Kleinschreibung und erkennt Platzhalter in Suchkriterien. Wenn sich Ihre IDs durch die Schreibweise unterscheiden oder *, ? beziehungsweise ~ enthalten, benötigen diese einfachen Formeln eine andere Abgleichregel. Verwenden Sie sie dann nicht unverändert.

Zählen Sie anschließend in Old!D2 die Treffer in der neuen Quelle und füllen Sie die Formel nach unten aus:

=ZÄHLENWENN(New!$A$2:$A$5;A2)

0 bedeutet, dass die ID nur in Old vorkommt. 1 steht für einen eindeutigen Kandidaten, ein Wert über 1 für eine mehrdeutige Zuordnung. Rufen Sie in Old!E2 den neuen Betrag ab:

=XVERWEIS(A2;New!$A$2:$A$5;New!$B$2:$B$5;"Not found";0)

Vergleichen Sie E nur dann mit B, wenn D den Wert 1 enthält und beide Beträge gültige Zahlen sind. Ein fehlender Datensatz, ein leerer Betrag und eine echte Null sind nicht dasselbe. Legen Sie bei berechneten Beträgen eine passende Rundungs- oder Toleranzregel fest, bevor Sie Unterschiede einstufen.

XVERWEIS liefert den ersten passenden Eintrag und löst keine Dubletten auf. Außerdem ist die Funktion in Excel 2016 und 2019 nicht verfügbar. Verwenden Sie dort nach denselben Eindeutigkeitsprüfungen diese Alternative mit exakter Übereinstimmung in E2:

=WENNNV(INDEX(New!$B$2:$B$5;VERGLEICH(A2;New!$A$2:$A$5;0));"Not found")

Weitere Hinweise zum Verhalten und zur Kompatibilität finden Sie bei Microsoft in der Dokumentation zu XVERWEIS und der Anleitung zu INDEX und VERGLEICH.

Zählen Sie abschließend auf New jede ID in Old, um neue Datensätze zu finden:

=ZÄHLENWENN(Old!$A$2:$A$5;A2)

Wenn Sie diese Spalte nach 0 filtern, finden Sie A105. Ein ausschließlich von Old nach New durchgeführter Vergleich würde diesen Eintrag übersehen.

Jeden Unterschied im Bericht nachvollziehbar machen

Für das Beispiel sollte ein separater Bericht Folgendes enthalten:

ID	Betrag in Old	Betrag in New	Status
A101	120	120	Unverändert
A102	80	95	Geändert
A103	75	75	Unverändert
A104	50	—	Nur in Old
A105	—	60	Nur in New

Es gibt fünf verschiedene IDs: zwei unveränderte, eine geänderte, eine nur in Old und eine nur in New. Beide Quellen enthalten vier Zeilen. Ein Vergleich nach Zeilenposition würde ein irreführendes Bild ergeben.

Halten Sie bei mehreren Feldern für jede Änderung den Feldnamen sowie den alten und neuen Wert fest. Bewahren Sie neben den IDs auch die Quellzeilennummern auf. Führen Sie leere oder doppelte Schlüssel in einer gesonderten Prüfliste, statt sie stillschweigend zu löschen. Lesen Sie vor Änderungen an den Quellen, wie Sie Dubletten sicher entfernen.

GetSheetAI einen klar abgegrenzten Vergleich auftragen

Geben Sie in der GetSheetAI Seitenleiste für Excel Schlüssel, Felder, Ausgabeort und Regeln an, statt nur „Vergleiche diese Blätter“ zu schreiben. Ersetzen Sie die Feldnamen in dieser Anfrage durch Ihre tatsächlichen Spaltenüberschriften. Lassen Sie Status weg, wenn Ihre Quelle nur eine Betragsspalte enthält:

Vergleiche Old und New anhand der Auftrags-ID und berücksichtige die tatsächlich befüllten Bereiche. Melde zuerst leere und doppelte IDs in jeder Quelle. Wenn IDs keine eindeutige Zuordnung erlauben, halte an und frage mich nach der Abgleichregel. Vergleiche nur Betrag und Status. Unterscheide fehlende Datensätze, leere Werte und Nullwerte. Erstelle ein neues Blatt Comparison mit ID, geändertem Feld, altem Wert, neuem Wert, Ergebniskategorie und Quellzeilennummern. Berücksichtige auch Datensätze, die nur auf einer Seite vorkommen. Verändere keines der Quellblätter. Fasse die Anzahlen zusammen und zeige alle ungeklärten Zeilen.

Prüfen Sie die vorgeschlagene Zuordnungsregel, bevor Sie Änderungen übernehmen. KI-Unterstützung kann den Vergleich strukturieren. Ohne eine Regel von Ihnen kann sie jedoch nicht entscheiden, ob zwei unterschiedliche Kennungen denselben Auftrag bezeichnen.

Den Bericht vor der Verwendung prüfen

Prüfen Sie einen unveränderten, einen geänderten sowie je einen nur in einer Quelle vorhandenen Datensatz.
Gleichen Sie die Anzahl verschiedener IDs über alle Ergebniskategorien ab und zählen Sie ungeklärte IDs gesondert.
Stellen Sie sicher, dass eine Sortierung der Quellen die Einstufung nicht verändert.
Prüfen Sie, ob die vollständigen befüllten Bereiche berücksichtigt wurden, nicht nur sichtbare oder gefilterte Zeilen.
Bewahren Sie die ursprünglichen Exporte auf, damit eine andere Person den Vergleich nachvollziehen kann.

Wenn Sie als Nächstes Rechnungen mit Zahlungen abgleichen möchten, statt Versionen zu vergleichen, verwenden Sie den Arbeitsablauf zum Rechnungsabgleich. Mehrere Zahlungen je Rechnung erfordern andere Regeln als dieses Beispiel mit einem Datensatz pro ID.