Website-Suche

Du vergleichst Excel-Dateien auf die harte Art - hier sind 2 bessere Methoden

Es ist Montagmorgen, und eine „aktualisierte“ Kopie einer Excel-Arbeitsmappe liegt in Ihrem Posteingang. Aber wenn Sie sie öffnen, ist es oft unmöglich zu erkennen, was sich geändert hat. Anstatt ein Spiel „Finde den Unterschied“ zu spielen, nutzen Sie diese integrierten Excel-Tools, um Unterschiede in Sekunden hervorzuheben.

Wenn Sie Office Professional Plus oder Microsoft 365 Enterprise verwenden, haben Sie möglicherweise bereits ein spezielles Tool namens Spreadsheet Compare. Es ist ein eigenständiges Dienstprogramm, das die schwere Arbeit für Sie erledigt, aber da es in den Standardversionen für Zuhause oder Business nicht enthalten ist, sind die unten beschriebenen Methoden Ihre beste Option für universelle Kompatibilität.

Methode 1: Hebe nicht übereinstimmende Werte mit bedingter Formatierung hervor

Verwenden Sie visuelle Warnungen, um kleinere Datensätze zu überprüfen

Bedingte Formatierung ist eine schnelle, visuelle Methode, um Unterschiede zwischen zwei Datensätzen zu erkennen. Sie funktioniert jedoch nur, wenn beide Versionen in derselben Arbeitsmappe enthalten sind - Excel erlaubt es nicht, dass Formeln für die bedingte Formatierung auf eine andere Arbeitsmappe verweisen. Wenn sie sich in verschiedenen Arbeitsmappen befinden, öffnen Sie beide Arbeitsmappen und folgen dann diesen Schritten, um sie zusammenzuführen:

  1. Klicken Sie mit der rechten Maustaste auf die Registerkarte des aktualisierten Blatts und dann auf Verschieben oder Kopieren.
  2. Wählen Sie im Dropdown-Menü Zu Buch die originale Arbeitsmappe aus.
  3. Wählen Sie Ans Ende verschieben, damit das aktualisierte Blatt rechts neben dem Originalblatt angezeigt wird. Aktivieren Sie außerdem Kopie erstellen, wenn Sie dieses Blatt nur in der anderen Arbeitsmappe duplizieren möchten, oder deaktivieren Sie diese Option, wenn Sie es dauerhaft verschieben möchten.
  4. Klicken Sie auf OK.

Wenn sich beide Tabellenblätter jetzt in einer einzigen Datei befinden, klicken Sie im Reiter Ansicht auf Neues Fenster, um eine zweite Instanz Ihrer Datei zu öffnen, und klicken Sie dann auf Alle anordnen > Vertikal, um sie nebeneinander anzuordnen. Jetzt können Sie beide Arbeitsblätter gleichzeitig nebeneinander geöffnet haben.

Sie sind nun bereit, Excel anzuweisen, die Unterschiede zwischen den beiden Versionen hervorzuheben:

  1. Wählen Sie den gesamten Datenbereich in Ihrem ursprünglichen Blatt aus.
  2. Klicken Sie auf der Start-Registerkarte auf Bedingte Formatierung > Neue Regel.
  3. Klicken Sie auf Eine Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen.
  4. Klicken Sie im Dialog auf Format und wählen Sie dann eine Hervorhebungsfarbe wie Hellrot aus.
  5. Erstellen Sie die Formel, indem Sie die erste Zelle im ursprünglichen Datensatz auswählen, <> eingeben und die entsprechende Zelle im aktualisierten Blatt auswählen. Drücken Sie bei jeder Referenz dreimal F4, um die absolute Sperre aufzuheben.

Hier ist die Formel, die ich in meinem Beispiel verwendet habe:

=A2<>Sales_Updated!A2

Diese Methode funktioniert gut für kleine, saubere Datensätze, hat aber eine große Schwäche: Sie hängt von einer perfekten Zeilenausrichtung ab. Wenn jemand Zeilen in einem der Blätter einfügt, löscht oder umsortiert, wird Excel weiterhin positionsbasiert vergleichen, was zu weit verbreiteten falschen Nichtübereinstimmungen führt.

Wenn Excel eine Zelle hervorhebt, die zu passen scheint, haben Sie wahrscheinlich versteckte Zeichen oder Formatierungsmismatches. Entfernen Sie zuerst überflüssige Leerzeichen mit Suchen und Ersetzen (Strg+H) oder der GLÄTTEN-Funktion und überprüfen Sie dann auf Formatierungsmismatches, indem Sie auf das grüne Dreieck in einer Zelle klicken und In Zahl konvertieren auswählen.

Methode 2: Zeilendaten mit Power Query-Join vergleichen

Erstellen Sie eine dauerhafte Prüfspur für große Arbeitsblätter

Wenn Sie Versionen größerer Datensätze vergleichen müssen, ist Power Query die robusteste Methode. Anstatt Zellen nach Position zu vergleichen, stimmt es Daten basierend auf von Ihnen definierten Werten ab—was es resistent gegen Zeilenverschiebungen und strukturelle Änderungen macht.

Stellen Sie zunächst sicher, dass beide Datensätze als Excel-Tabellen formatiert sind (Strg+T), und laden Sie sie dann beide als Verbindungen in den Power Query-Editor:

  1. Laden Sie die erste Tabelle in Power Query, indem Sie eine ihrer Zellen auswählen und zu Daten > Aus Tabelle/Bereich gehen.
  2. Klicken Sie im Editor auf Schließen & Laden nach.
  3. Wählen Sie Nur Verbindung erstellen und klicken Sie auf OK.
  4. Wiederholen Sie diese Schritte für den zweiten Tisch.

Mit beiden in Power Query geladenen Tabellen können Sie mit dem Vergleich beginnen. Beginnen Sie damit, die ursprüngliche Tabelle mit der aktualisierten Version zu vergleichen:

  1. Doppelklicken Sie auf eine der Abfragen im Bereich Abfragen & Verbindungen, um den Editor erneut zu öffnen.
  2. Klicken Sie dann im Start-Tab auf Abfragen zusammenführen > Abfragen als neu zusammenführen.
  3. Wählen Sie im Zusammenführen-Dialog die ursprüngliche Tabelle oben und die aktualisierte Tabelle unten aus.
  4. Klicken Sie auf die erste Spalte in der oberen Tabelle und dann auf dieselbe Spalte in der unteren Tabelle. Halten Sie dann Strg gedrückt, während Sie diesen Vorgang für alle anderen Spalten wiederholen. Beachten Sie, dass jede Spaltenpaarung nummeriert ist, um die passende Reihenfolge anzuzeigen.
  5. Wählen Sie Left Anti als Verbindungstyp und klicken Sie auf OK. Dies gibt Zeilen zurück, die in der Originalversion existieren, aber keine exakte Übereinstimmung in der aktualisierten Version haben - das bedeutet, dass sie entweder entfernt oder so verändert wurden, dass die Übereinstimmung nicht mehr zutrifft.

Machen Sie nun ein paar kleine Anpassungen an der neu zusammengeführten Abfrage:

  1. Entfernen Sie die Spalte, die die zusammengeführte zweite Tabelle (die verschachtelte Tabellenspalte) enthält.
  2. Bennen Sie die Abfrage in etwas wie v1_Changed um.

Sie müssen nun den Vorgang wiederholen, aber die Tabellenreihenfolge im Merge-Dialog umkehren - wählen Sie die aktualisierte Tabelle oben und die ursprüngliche Tabelle unten aus, führen Sie dann denselben Left Anti-Join aus und benennen Sie die Abfrage um (zum Beispiel v2_Changed). Das Ausführen des Merges in beide Richtungen liefert Ihnen zwei Ausgaben: eine zeigt Zeilen, die nur im ursprünglichen Datensatz existieren (entfernt oder in der Aktualisierung geändert), und die andere zeigt Zeilen, die nur im aktualisierten Datensatz existieren (neue oder geänderte Zeilen).

Sobald Sie dies getan haben, klicken Sie auf Schließen & Laden in, wählen Sie Tabelle aus und klicken Sie auf OK, um diese Abfragen in zwei neuen Arbeitsblättern zu laden.

Die Schönheit dieser Methode besteht darin, dass, wenn Sie eine neue Zeile zu einem der beiden Quell-Datensätze hinzufügen und auf Alle aktualisieren im Daten-Tab klicken, der neue Datensatz automatisch in der "Geändert"-Abfrage enthalten ist, wodurch Ihre Informationen auf dem neuesten Stand bleiben.

Das nächste Mal, wenn eine „aktualisierte“ Arbeitsmappe in Ihrem Posteingang landet, wählen Sie Ihr Werkzeug basierend auf den Daten. Für ein schnelles fünfminütiges Audit einer kleinen Liste ist der Trick mit der bedingten Formatierung Ihr bester Freund. Bei großen Datensätzen, bei denen die Reihenfolge der Zeilen nicht vertrauenswürdig ist, ist Power Query der beste Weg, um sicherzustellen, dass nichts durch die Lappen geht. So oder so haben Sie eine mühsame manuelle Aufgabe in einen wiederholbaren, automatisierten Prozess verwandelt.

Verwandte Artikel