Nie wieder manuell suchen: So erstellen Sie in Excel einen automatischen Changelog und können zwei Tabellen vergleichen

Kennen Sie das? Sie haben zwei Excel-Tabellen mit ähnlichen Daten, aber unterschiedlichen Versionen. Und Sie müssen herausfinden, was sich zwischen den beiden geändert hat. Die manuelle Suche ist zeitaufwändig, frustrierend und fehleranfällig. Aber keine Sorge, es gibt eine bessere Lösung! In diesem Artikel zeige ich Ihnen, wie Sie in Excel einen automatischen Changelog erstellen und zwei Tabellen effizient vergleichen können.

Warum ein automatischer Changelog in Excel?

Ein Changelog, auch Änderungsprotokoll genannt, ist eine chronologische Liste von Änderungen an einem Dokument oder Datensatz. In Excel hilft er Ihnen, nachzuverfolgen, welche Zeilen, Spalten oder Zellen sich zwischen zwei Versionen einer Tabelle geändert haben. Ein automatischer Changelog automatisiert diesen Prozess, wodurch Sie:

• Zeit sparen: Automatisieren Sie den Vergleich und die Protokollierung von Änderungen, anstatt sie manuell zu suchen.
• Fehler reduzieren: Vermeiden Sie menschliche Fehler, die beim manuellen Vergleich auftreten können.
• Bessere Nachvollziehbarkeit: Verfolgen Sie Änderungen im Zeitverlauf und verstehen Sie, wie sich Ihre Daten entwickelt haben.
• Effizientere Zusammenarbeit: Erleichtern Sie die Zusammenarbeit im Team, indem Sie klar dokumentieren, welche Änderungen vorgenommen wurden.

Voraussetzungen

Für die in diesem Artikel beschriebenen Methoden benötigen Sie:

• Microsoft Excel (Version 2010 oder höher, idealerweise eine neuere Version)
• Grundlegende Excel-Kenntnisse (Formeln, bedingte Formatierung)
• Die beiden Excel-Tabellen, die Sie vergleichen möchten.

Methode 1: Vergleich mit bedingter Formatierung und Hilfsspalten

Diese Methode ist relativ einfach und erfordert keine VBA-Programmierung. Sie verwendet bedingte Formatierung und Hilfsspalten, um Unterschiede hervorzuheben.

1. Tabellen vorbereiten: Stellen Sie sicher, dass beide Tabellen die gleichen Spaltenüberschriften und eine eindeutige Kennung (z.B. eine ID-Spalte) haben. Wenn eine der Tabellen neue Spalten hat, fügen Sie diese in die andere Tabelle ein und lassen Sie die entsprechenden Zellen leer. Die Reihenfolge der Spalten muss identisch sein.
2. Hilfsspalten erstellen: Fügen Sie in einer der Tabellen (z.B. der zweiten Version) für jede Spalte, die Sie vergleichen möchten, eine Hilfsspalte ein. Diese Hilfsspalte wird verwendet, um zu überprüfen, ob sich der Wert in der entsprechenden Zelle der ersten Tabelle geändert hat.
3. Formel für den Vergleich: Geben Sie in die erste Zelle der ersten Hilfsspalte folgende Formel ein (passen Sie die Zellbezüge an Ihre Tabellen an):

   =WENN(A2=Blatt1!A2,"","GEÄNDERT")

   Erklären wir die Formel:

   • `WENN(A2=Blatt1!A2,””,”GEÄNDERT”)`: Dies ist eine WENN-Funktion. Sie prüft, ob der Wert in Zelle A2 der aktuellen Tabelle gleich dem Wert in Zelle A2 der Tabelle „Blatt1” ist.
   • `A2=Blatt1!A2`: Dies ist die Vergleichsbedingung. Sie prüft, ob die beiden Werte gleich sind.
   • `””`: Wenn die Werte gleich sind, wird ein leerer String zurückgegeben (d.h. die Zelle bleibt leer).
   • `”GEÄNDERT”`: Wenn die Werte unterschiedlich sind, wird der Text „GEÄNDERT” zurückgegeben.
   • `Blatt1`: Ersetzen Sie „Blatt1” durch den tatsächlichen Namen des Tabellenblatts, das Sie vergleichen.
4. Formel kopieren: Ziehen Sie die Formel nach unten, um sie auf alle Zeilen anzuwenden. Ziehen Sie die Formel dann nach rechts, um sie auf alle Hilfsspalten anzuwenden.
5. Bedingte Formatierung anwenden: Wählen Sie alle Zellen in den Hilfsspalten aus. Gehen Sie zu „Start” -> „Bedingte Formatierung” -> „Neue Regel…”. Wählen Sie „Formel zur Ermittlung der zu formatierenden Zellen verwenden”. Geben Sie die Formel `=NICHT(ISTLEER(B2))` ein (ersetzen Sie „B2” durch die erste Zelle einer Hilfsspalte). Wählen Sie dann ein Format (z.B. eine Hintergrundfarbe), um die geänderten Zellen hervorzuheben.

Jetzt werden alle Zellen in den Hilfsspalten, die „GEÄNDERT” enthalten, hervorgehoben. Das zeigt Ihnen, welche Zellen sich im Vergleich zur ersten Tabelle geändert haben.

Methode 2: VBA-Skript für einen detaillierten Changelog

Für einen detaillierteren Changelog, der genau auflistet, welche Zeilen hinzugefügt, gelöscht oder geändert wurden, benötigen Sie ein VBA-Skript.

1. VBA-Editor öffnen: Drücken Sie `Alt + F11`, um den VBA-Editor zu öffnen.
2. Neues Modul einfügen: Gehen Sie zu „Einfügen” -> „Modul”.
3. VBA-Code einfügen: Kopieren Sie das folgende VBA-Skript in das Modul. Passen Sie die Variablen `sheetName1`, `sheetName2`, `keyColumn`, und `logSheetName` an Ihre spezifischen Tabellen und Spalten an.

Sub CompareSheets()

    Dim sheetName1 As String, sheetName2 As String, logSheetName As String
    Dim keyColumn As String
    Dim ws1 As Worksheet, ws2 As Worksheet, logWs As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long, logLastRow As Long
    Dim i As Long, j As Long, logRow As Long
    Dim diffFound As Boolean
    Dim keyVal As Variant

    ' *** KONFIGURATION ***
    sheetName1 = "Tabelle1" ' Name des ersten Tabellenblatts
    sheetName2 = "Tabelle2" ' Name des zweiten Tabellenblatts
    keyColumn = "A"       ' Spalte mit der eindeutigen Kennung (z.B. ID)
    logSheetName = "Changelog"  ' Name des Tabellenblatts für den Changelog
    ' *** ENDE KONFIGURATION ***

    Set ws1 = ThisWorkbook.Sheets(sheetName1)
    Set ws2 = ThisWorkbook.Sheets(sheetName2)

    ' Prüfen, ob Changelog-Blatt existiert, andernfalls erstellen
    On Error Resume Next
    Set logWs = ThisWorkbook.Sheets(logSheetName)
    On Error GoTo 0

    If logWs Is Nothing Then
        Set logWs = ThisWorkbook.Sheets.Add
        logWs.Name = logSheetName
        'Header-Zeile schreiben
        logWs.Cells(1, 1).Value = "Datum"
        logWs.Cells(1, 2).Value = "Aktion"
        logWs.Cells(1, 3).Value = "ID"
        logWs.Cells(1, 4).Value = "Spalte"
        logWs.Cells(1, 5).Value = "Alter Wert"
        logWs.Cells(1, 6).Value = "Neuer Wert"
    End If

    lastRow1 = ws1.Cells(Rows.Count, keyColumn).End(xlUp).Row
    lastRow2 = ws2.Cells(Rows.Count, keyColumn).End(xlUp).Row
    logLastRow = logWs.Cells(Rows.Count, 1).End(xlUp).Row + 1
    logRow = logLastRow

    ' *** GEÄNDERTE/HINZUGEFÜGTE ZEILEN FINDEN ***
    For i = 2 To lastRow2 'Start bei Zeile 2 (Header in Zeile 1)
        keyVal = ws2.Cells(i, keyColumn).Value
        diffFound = False

        ' Existiert der Key in Tabelle1?
        Dim foundRange As Range
        Set foundRange = ws1.Range(keyColumn & "2:" & keyColumn & lastRow1).Find(keyVal, LookIn:=xlValues, LookAt:=xlWhole)

        If foundRange Is Nothing Then
            'Zeile ist NEU
            logWs.Cells(logRow, 1).Value = Now()
            logWs.Cells(logRow, 2).Value = "Hinzugefügt"
            logWs.Cells(logRow, 3).Value = keyVal
            logWs.Cells(logRow, 4).Value = "-"
            logWs.Cells(logRow, 5).Value = "-"
            logWs.Cells(logRow, 6).Value = "-"
            logRow = logRow + 1
        Else
            'Zeile existiert, prüfe auf Änderungen
            For j = 1 To ws2.UsedRange.Columns.Count
                If ws2.Cells(i, j).Value <> ws1.Cells(foundRange.Row, j).Value Then
                    diffFound = True
                    logWs.Cells(logRow, 1).Value = Now()
                    logWs.Cells(logRow, 2).Value = "Geändert"
                    logWs.Cells(logRow, 3).Value = keyVal
                    logWs.Cells(logRow, 4).Value = ws2.Cells(1, j).Value 'Spaltenüberschrift
                    logWs.Cells(logRow, 5).Value = ws1.Cells(foundRange.Row, j).Value 'Alter Wert
                    logWs.Cells(logRow, 6).Value = ws2.Cells(i, j).Value 'Neuer Wert
                    logRow = logRow + 1
                End If
            Next j
        End If
    Next i

    ' *** GELÖSCHTE ZEILEN FINDEN ***
    For i = 2 To lastRow1
         keyVal = ws1.Cells(i, keyColumn).Value
        Dim foundRange As Range
        Set foundRange = ws2.Range(keyColumn & "2:" & keyColumn & lastRow2).Find(keyVal, LookIn:=xlValues, LookAt:=xlWhole)

        If foundRange Is Nothing Then
           'Zeile ist GELÖSCHT
            logWs.Cells(logRow, 1).Value = Now()
            logWs.Cells(logRow, 2).Value = "Gelöscht"
            logWs.Cells(logRow, 3).Value = keyVal
            logWs.Cells(logRow, 4).Value = "-"
            logWs.Cells(logRow, 5).Value = "-"
            logWs.Cells(logRow, 6).Value = "-"
            logRow = logRow + 1
        End If
    Next i

    MsgBox "Vergleich abgeschlossen! Changelog wurde im Blatt '" & logSheetName & "' erstellt."

End Sub

4. Code anpassen: Passen Sie die Variablen am Anfang des Codes an:
   • `sheetName1`: Der Name des ersten Tabellenblatts.
   • `sheetName2`: Der Name des zweiten Tabellenblatts.
   • `keyColumn`: Der Buchstabe der Spalte, die die eindeutige Kennung (ID) enthält.
   • `logSheetName`: Der Name des Tabellenblatts, in dem der Changelog erstellt werden soll. Wenn das Blatt nicht existiert, wird es erstellt.
5. Skript ausführen: Schließen Sie den VBA-Editor. Gehen Sie in Excel zu „Entwicklertools” -> „Makros”. Wählen Sie „CompareSheets” und klicken Sie auf „Ausführen”. (Falls „Entwicklertools” nicht sichtbar ist: „Datei” -> „Optionen” -> „Menüband anpassen” -> „Entwicklertools” aktivieren).

Das Skript erstellt ein neues Tabellenblatt namens „Changelog” (oder den Namen, den Sie in `logSheetName` angegeben haben). Dieser Changelog listet alle hinzugefügten, gelöschten und geänderten Zeilen auf, zusammen mit den alten und neuen Werten.

Tipps und Tricks

• Datenbereinigung: Stellen Sie sicher, dass Ihre Daten sauber und konsistent sind. Leere Zellen oder unterschiedliche Formatierungen können zu falschen Ergebnissen führen.
• Performance: Bei sehr großen Tabellen kann das VBA-Skript etwas Zeit in Anspruch nehmen.
• Fehlerbehandlung: Fügen Sie Fehlerbehandlungsroutinen in das VBA-Skript ein, um unerwartete Fehler abzufangen.
• Sicherung: Sichern Sie Ihre Excel-Datei, bevor Sie VBA-Skripte ausführen.

Fazit

Die Erstellung eines automatischen Changelogs in Excel ist eine unschätzbare Hilfe, um Änderungen in Ihren Daten effizient nachzuverfolgen. Ob Sie die einfache Methode mit bedingter Formatierung oder das detailliertere VBA-Skript verwenden, Sie werden wertvolle Zeit sparen und die Genauigkeit Ihrer Daten verbessern. Probieren Sie die Methoden aus und passen Sie sie an Ihre Bedürfnisse an!