Wie Sie Transaktionen in einer Tabellenkalkulation abgleichen (ohne Zeilen manuell zuzuordnen)
Der Abgleich zweier Transaktionslisten bedeutet, vier Arten von Differenzen zu finden: Zeilen, die auf einer Seite fehlen, Zeilen, die auf der anderen Seite doppelt vorhanden sind, Beträge, die nicht übereinstimmen, und dieselbe Zahlung, die in unterschiedlichen Perioden erfasst wurde. Alles andere ist Buchhaltung um diese vier herum.
Der erste Impuls ist meist, die beiden Dateien nebeneinanderzulegen und Zeilen abzugleichen. Das funktioniert bis zu etwa zweihundert Zeilen – danach wird es zu einer Nachmittagsbeschäftigung, die am Ende eine Zahl liefert, die niemand mehr prüfen kann.
Dieser Leitfaden erklärt, warum Tabellenkalkulationen mit dieser speziellen Aufgabe überfordert sind, welche drei Workarounds am häufigsten genutzt werden und wo diese jeweils an ihre Grenzen stoßen. Es handelt sich um einen Daten-Workflow, nicht um eine Steuer- oder Buchhaltungsberatung.
Warum der Abgleich in einer Tabellenkalkulation schwieriger ist, als es aussieht
Eine Tabellenkalkulation vergleicht Zellen. Ein Abgleich vergleicht Ereignisse – und dasselbe Ereignis sieht auf beiden Seiten selten identisch aus.
Eine Kartenzahlung wird in Ihrem Hauptbuch vielleicht einmal aufgeführt, im Export des Zahlungsabwicklers jedoch zweimal – aufgeteilt in Gebühr und Betrag. Eine Lieferantenrechnung trägt im einen System die Referenz INV-0042 und im anderen INV42. Eine am 31. initiierte Überweisung wird erst am 1. verbucht, was den gesamten Monat durcheinanderbringt.
Nichts davon sind Fehler in den Daten. Es ist die ganz normale Art und Weise, wie zwei verschiedene Systeme dieselbe Realität erfassen – und keine Verweis-Formel kann das von sich aus lösen.
Zudem gibt es eine Rundungsfalle, die jeden Monat aufs Neue zuschnappt. Währungswerte, die mit voller Gleitkommapräzision gespeichert sind, können sich in der vierten Nachkommastelle unterscheiden. Zwei Beträge, die als 1,204.50 angezeigt werden, bestehen daher oft keine exakte Gleichheitsprüfung.
Auch das Datenvolumen verändert den Charakter des Problems. Bei fünfzig Zeilen kann ein Mensch noch beide Listen im Kopf behalten. Bei fünftausend Zeilen wird die Aufgabe zur Suche nach der Nadel im Heuhaufen – einer Handvoll Ausnahmen, die sich in einer Masse von Übereinstimmungen verbergen. Dafür ist die menschliche Aufmerksamkeit denkbar ungeeignet.
Was Sie das kostet
Der zähe Monatsabschluss. Die ersten neunzig Prozent der Zeilen sind in wenigen Minuten abgeglichen. Die verbleibende Handvoll dauert Stunden, weil bei jeder einzelnen ein Mensch herausfinden muss, um welche der vier Differenzarten es sich handelt.
Nicht nachvollziehbare Ergebnisse. Wenn der Abgleich nach Augenmaß erfolgt, ist der Rechenweg in dem Moment verloren, in dem die Datei geschlossen wird. Sechs Wochen später kann niemand mehr rekonstruieren, warum zwei Zeilen als dieselbe Zahlung behandelt wurden.
Unentdeckte Fehler. Ein Duplikat, das dem falschen Gegenpart zugeordnet wird, gleicht sich in der Gesamtsumme aus und sieht wie ein sauberer Abgleich aus. Dass die Summen übereinstimmen, ist kein Beweis dafür, dass auch die Zeilen übereinstimmen.
Diese drei Kostenfaktoren verstärken sich gegenseitig. Ein zäher Abschluss führt zu Ermüdung, Ermüdung führt zu Abkürzungen, und durch Abkürzungen wird ein falscher Abgleich als korrekt verbucht.
Die Workarounds, die ausprobiert werden
Option 1: Vergleichen Sie die Summen, bevor Sie irgendetwas abgleichen
Vergleichen Sie zunächst Gruppensummen statt einzelner Zeilen. Verwenden Sie SUMIFS, um jede Seite nach Monat, Konto oder Vertragspartner zu summieren, und stellen Sie die beiden Spalten nebeneinander.
So lokalisieren Sie die Differenz, bevor Sie Zeit investieren. Wenn elf von zwölf Monaten auf den Cent genau übereinstimmen, müssen Sie nur noch einen Monat abgleichen statt eines ganzen Jahres.
Das ist durchaus nützlich, stößt aber schnell an Grenzen. Gruppensummen verraten Ihnen zwar, wo die Abweichung liegt, aber nie, welche Zeilen sie verursacht haben – und zwei sich gegenseitig aufhebende Fehler innerhalb derselben Gruppe bleiben unsichtbar.
Option 2: Erstellen Sie einen Match-Key und nutzen Sie den Verweis
Verknüpfen Sie die Felder, die ein Ereignis identifizieren, zu einem einzigen Schlüssel (Key) – typischerweise Datum plus Betrag plus eine bereinigte Referenz. Verwenden Sie dann XLOOKUP in beide Richtungen, um Zeilen zu finden, die auf der einen Seite vorhanden und auf der anderen abwesend sind.
Fügen Sie COUNTIFS für denselben Schlüssel hinzu, um Duplikate zu finden, da ein Verweis nur den ersten Treffer zurückgibt und den zweiten stillschweigend ignoriert. Runden Sie Beträge mit ROUND auf zwei Dezimalstellen, bevor sie in den Schlüssel einfließen, um die oben beschriebene Gleitkomma-Abweichung zu vermeiden.
Dies ist die Standardmethode, die für die meisten Monate ausreicht. Ihre Grenze ist struktureller Natur: Sie benötigt einen Schlüssel, der auf beiden Seiten exakt dasselbe bedeutet. Unterschiedliche Referenzformate oder eine auf zwei Zeilen aufgeteilte Gebühr bringen diese Methode sofort zu Fall.
Option 3: Gehen Sie die vier Differenzarten gezielt an
Führen Sie statt eines einzigen Abgleichsdurchlaufs vier präzisere Prüfungen durch. Fehlende Zeilen ermitteln Sie über den bidirektionalen Verweis. Duplikate finden Sie über eine Zählung des Schlüssels. Betragsabweichungen erkennen Sie, indem Sie nur nach der Referenz abgleichen und dann die Werte vergleichen. Zeitliche Verschiebungen finden Sie durch den Abgleich innerhalb eines Datumsfensters statt eines exakten Datums.
Richtig durchgeführt ist dies die ehrlichste Methode, da jede nicht zugeordnete Zeile in einer definierten Kategorie landet und nicht auf einem unbestimmten Restposten-Haufen.
Es ist allerdings auch die arbeitsintensivste Methode. Vier Durchläufe bedeuten vier Hilfsspalten pro Seite, und das gesamte System muss neu aufgebaut werden, sobald sich die Spaltenreihenfolge in einem der Exporte ändert. Unser Leitfaden zum Bereinigen und Deduplizieren von Daten beschreibt den Vorbereitungsschritt, auf dem diese Methode aufbaut.
Die gemeinsame Grenze. Alle drei Methoden setzen voraus, dass eine Zeile auf der einen Seite genau einer Zeile auf der anderen Seite entspricht. Eine einzige Abrechnung kann jedoch vierzig Transaktionen umfassen. Eine Zahlung kann als Belastung plus Gebühr plus Rückerstattung eingehen. In beiden Fällen greift der Schlüsselabgleich ins Leere. Und genau dabei geht der Nachmittag verloren.
So gleichen Sie Transaktionen mit Powerdrill Bloom ab
Step 1: Beide Dateien hochladen
Laden Sie den Hauptbuchexport und den Kontoauszug des Gegenparts zusammen hoch. Powerdrill Bloom analysiert beide Dateien, sodass unpassende Spaltennamen, unterschiedliche Datumsformate und uneinheitliche Referenzstile bereits vor dem eigentlichen Abgleich sichtbar werden.
Step 2: Beschreiben Sie den Abgleich in natürlicher Sprache
Fragen Sie gezielt nach den vier Kategorien. Fordern Sie die Zeilen an, die in einer Datei vorhanden und in der anderen abwesend sind, sowie die doppelten Referenzen. Fragen Sie dann nach Betragsabweichungen außerhalb einer bestimmten Toleranzgrenze und nach Einträgen, deren Daten um einige Tage voneinander abweichen.
Stellen Sie anschließend die Frage, die die schwierigen Fälle löst: Fragen Sie, welche Gruppen von Zeilen auf der einen Seite sich zu einer einzelnen Zeile auf der anderen Seite aufsummieren. Das ist der n:1-Fall (Many-to-One), den ein einfacher Schlüsselabgleich nicht erfassen kann.
Step 3: Exportieren Sie das Diagramm, den Bericht oder die Präsentation
Exportieren Sie die Ausnahmeliste, eine Zusammenfassung der nicht abgeglichenen Werte nach Kategorie oder eine kurze schriftliche Notiz für Ihre Abschlussdokumentation.
Warum das besser ist, als den Abgleich jeden Monat neu aufzubauen
| Manuelle Methode | Powerdrill Bloom | |
|---|---|---|
| Unterschiedliche Referenzformate | Zuerst beide Seiten manuell bereinigen | Unterschied beschreiben und nachfragen |
| Mehrere Zeilen zu einer Zeile (n:1) | Manuelle Gruppierung | Fragen, welche Zeilen sich zum Gegenwert aufsummieren |
| Duplikate | Zusätzliche Zählspalte pro Seite | In der Ausnahmeliste enthalten |
| Nächster Monat | Jede Hilfsspalte neu aufbauen | Einfach die neuen Exporte hochladen |
In der ersten Zeile geht die meiste Zeit verloren. Das Bereinigen von Referenzen, damit zwei Systeme übereinstimmen, ist reine Vorbereitungsarbeit, die an sich noch keinen Mehrwert schafft. Zudem muss sie jedes Mal neu gemacht werden, wenn sich ein Exportformat ändert.
Häufige Fehler
Eine übereinstimmende Gesamtsumme als fertigen Abgleich betrachten. Zwei gleich große Fehler in entgegengesetzte Richtungen ergeben eine perfekte Gesamtsumme. Prüfen Sie die Zeilenanzahl und die nicht abgeglichenen Werte, nicht nur die Summe.
Nur nach dem Betrag abgleichen. In jedem echten Hauptbuch haben mehrere Transaktionen denselben Wert. Ein Verweis, der nur auf dem Betrag basiert, wird falsche Paare bilden und dabei täuschend plausibel wirken.
Rundungsdifferenzen ignorieren. Werte, die identisch angezeigt werden, können dennoch bei einer Gleichheitsprüfung durchfallen. Runden Sie beide Seiten auf dieselbe Genauigkeit, bevor Sie sie vergleichen.
Die Richtung der Prüfung vergessen. Ein einseitiger Verweis findet Zeilen, die in der zweiten Datei fehlen, übersieht aber Zeilen, die in der ersten fehlen. Führen Sie die Prüfung immer in beide Richtungen durch.
Abgeglichene Zeilen laufend löschen. Das fühlt sich effizient an, zerstört aber den Audit-Trail. Kennzeichnen Sie Zeilen stattdessen mit einer Statusspalte und lassen Sie die Originaldaten unberührt.
Abgleichen, bevor die Periode geschlossen ist. Spät eingehende Buchungen führen zu zeitlichen Verschiebungen, die sich von selbst lösen. Diesen mitten in der Periode hinterherzujagen, ist verschwendete Liebesmüh.
Fazit
Der Abgleich ist ein Klassifizierungsproblem, kein Zuordnungsproblem. Sortieren Sie jede nicht zugeordnete Zeile in die Kategorien „fehlend“, „doppelt“, „falscher Betrag“ oder „falsche Periode“ ein – dann ist die verbleibende Arbeit minimal und leicht erklärbar.
Teuer wird es vor allem dadurch, dass das System jeden Monat neu aufgebaut werden muss – insbesondere wenn Referenzen nicht übereinstimmen oder eine Zahlung auf mehrere Zeilen aufgeteilt ist. Wenn Ihr Monatsabschluss genau daran scheitert, testen Sie Powerdrill Bloom für beide Exporte. Lesen Sie auch unsere Leitfäden zum Zusammenführen zweier Excel-Dateien ohne VLOOKUP und zum Erstellen eines Soll-Ist-Vergleichs. Die Seiten zu Spesenabrechnungen und Cashflow-Analysen decken die angrenzenden Workflows ab.
Häufig gestellte Fragen
Was bedeutet es, Transaktionen abzugleichen?
Es bedeutet zu bestätigen, dass zwei Aufzeichnungen derselben Aktivität übereinstimmen, und jede verbleibende Differenz zu erklären. Die Erklärungen lassen sich in vier Gruppen einteilen: fehlende Zeilen, Duplikate, Betragsabweichungen und zeitliche Verschiebungen.
Kann Excel zwei Listen automatisch abgleichen?
Nicht von sich aus. Excel stellt Ihnen zwar die Bausteine zur Verfügung – hauptsächlich Verweise, Zählungen und bedingte Summen –, aber Sie müssen die Abgleichslogik selbst erstellen und jedes Mal neu anpassen, wenn sich einer der Exporte ändert.
Warum schlägt der Abgleich bei zwei Beträgen fehl, die gleich aussehen?
Meist liegt es an der gespeicherten Genauigkeit. Ein Wert, der mit zwei Dezimalstellen angezeigt wird, kann im Hintergrund weitere Nachkommastellen haben, sodass ein exakter Vergleich fehlschlägt. Das Runden beider Seiten auf dieselbe Genauigkeit behbt das Problem.
Wie gehe ich mit einer Zahlung um, die als mehrere Zeilen dargestellt wird?
Gruppieren Sie die kleineren Zeilen und vergleichen Sie die Gruppensumme mit dem einzelnen Gegenwert. Ein schlüsselbasierter Abgleich auf Zeilenebene kann diesen Fall nicht abbilden, weshalb er die häufigste Quelle für manuelle Arbeit ist.
Sollten nicht abgeglichene Zeilen gelöscht werden?
Nein. Behalten Sie sie und fügen Sie eine Statusspalte hinzu, in der Sie die Kategorie und den Grund festhalten. Das Löschen zerstört den Audit-Trail, der den Abgleich im Nachhinein nachvollziehbar und rechtssicher macht.