Wie kategorisiert man Banktransaktionen in Excel?
Die Verwaltung persönlicher oder geschäftlicher Finanzen erfordert oft die Überprüfung einer detaillierten Liste monatlicher Banktransaktionen. Diese Transaktionsdatensätze können vielfältige Beschreibungen enthalten – von Restaurants über Geschäfte bis hin zu Versorgungsleistungen oder Dienstleistungen. Die Ausgabenverfolgung und -analyse wird deutlich übersichtlicher, wenn jede Transaktion einer bestimmten Kategorie zugeordnet werden kann – beispielsweise „Takeout“, „Lebensmittel“, „Versorgung“ oder „Family fee“. Durch die Automatisierung der Klassifizierung in Excel anhand bestimmter Stichwörter in den Transaktionsbeschreibungen erhalten Sie monatlich klare Einblicke in Ihre Kosten und Ausgabenmuster.
Wie in der folgenden Abbildung dargestellt, nehmen wir an, Sie verfügen über Rohdaten mit Anbieter- oder Leistungsdetails in Spalte B und möchten eine vereinfachte Menge von Kategorien erhalten (z. B. soll jede Transaktion, die „)Mc Donalds“ enthält, als „Takeout“ identifiziert werden, während „Walmart“ als „Family fee“ klassifiziert wird), entsprechend Ihren persönlichen oder organisatorischen Regeln. Dieses Schritt-für-Schritt-Tutorial stellt mehrere praktische Lösungen vor – von formelbasierten Ansätzen bis hin zu fortschrittlichen Automatisierungsmethoden.
Inhalt:
Banktransaktionen mithilfe einer Formel in Excel kategorisieren
VBA-Code – Kategorisierung mithilfe eines Makros basierend auf einer vorgegebenen Liste automatisieren
Weitere integrierte Excel-Methoden – Power Query mit bedingter Spaltenlogik verwenden
Banktransaktionen mithilfe einer Formel in Excel kategorisieren
Wenn Sie einen einfachen, formelbasierten Ansatz zur Kategorisierung Ihrer Banktransaktionen in Excel bevorzugen, folgen Sie diesen Schritten. Diese Methode ist ideal für alle, die schnell Ergebnisse erzielen möchten und bereit sind, eine kleine Nachschlagetabelle mit Stichwörtern und zugehörigen Kategorien zu pflegen.
Erstellen Sie zunächst zwei Hilfsspalten außerhalb Ihrer eigentlichen Transaktionsliste: eine Spalte mit Stichwörtern (basierend auf möglichen Inhalten der Transaktionsbeschreibung) und eine zweite Spalte mit der jeweils zugeordneten Kategorie.
1. Listen Sie in diesem Beispiel alle abzugleichenden Stichwörter (wie „McDonald’s“, „Walmart“ usw.) in Spalte A von Zeile 30 bis 41 auf und tragen Sie die entsprechenden Kategorienamen („Takeout“, „Family fee“ usw.) in Spalte B von Zeile 30 bis 41 ein.
Sollte Ihr Satz an Stichwörtern oder Kategorien umfangreicher sein oder sich häufig ändern, passen Sie einfach die Bereiche so an, dass alle erforderlichen Einträge erfasst werden. Siehe Abbildung:
2. Klicken Sie als Nächstes auf die erste Zelle in Ihrer gewünschten Ausgabespalte (z. B. F3 neben der letzten Spalte Ihrer Daten) und geben Sie diese Matrixformel ein. Bestätigen Sie mit Strg + Umschalt + Enter (nicht nur mit Enter), da es sich um eine Matrixformel handelt. Dadurch wird das erste gefundene Stichwort in der Beschreibung gesucht und die zugehörige Kategorie zurückgegeben.
=IFERROR(INDEX(B$30:B$41,MATCH(TRUE,ISNUMBER(SEARCH($A$30:$A$41,B3)),0)),„Other")
Sobald die Formel für die erste Zeile funktioniert, ziehen Sie den AutoAusfüll-Griff die Spalte hinunter, um die Kategorisierungsformel auf alle weiteren Transaktionszeilen anzuwenden.
Parametererklärungen:
Vor- und Nachteile:Dieser formelbasierte Ansatz ermöglicht eine schnelle Einrichtung und geringen Wartungsaufwand – solange sich Ihre Kategorisierungsregeln nicht häufig ändern. Sobald Ihre Stichwortliste jedoch komplexer wird oder Sie Kategorien regelmäßig aktualisieren müssen, kann die Pflege der Hilfsspalten und Formeln schnell mühsam werden. In solchen Fällen sollten Sie eine Automatisierung per VBA oder Power Query in Betracht ziehen.
Praktischer Hinweis: Falls mehrere Stichwörter in einer Beschreibung übereinstimmen, bestimmt das erste gefundene Stichwort in Ihrer Liste die Kategorie. Um ein bestimmtes Stichwort zu priorisieren, platzieren Sie es weiter oben in Ihrer Nachschlagenspalte.
Häufige Fehlerbehebung:Sollten unerwartete Ergebnisse auftreten, überprüfen Sie sorgfältig den Nachschlagebereich und stellen Sie sicher, dass Ihre Stichwörter konsistent und vollständig geschrieben sind. Prüfen Sie außerdem auf zusätzliche Leerzeichen oder Formatierungsunterschiede in Ihren Transaktionsbeschreibungen.
VBA-Code – Automatisierte Kategorisierung mithilfe eines Makros, das Transaktionsbeschreibungen anhand einer vorgegebenen Liste Kategorien zuordnet
Diese Lösung nutzt ein VBA-Makro, um den Abgleich von Transaktionen mit Kategorien zu automatisieren – für mehr Kontrolle und bessere Skalierbarkeit. Sie eignet sich besonders für Anwender mit hohen Transaktionsvolumina oder wenn Sie die Stichwort-zu-Kategorie-Zuordnung dynamisch gestalten und manuelle Formelpflege auf ein Minimum reduzieren möchten.
Anwendungsszenario: Bei langen Transaktionslisten, häufigen Aktualisierungen oder wenn Sie manuelle Formeln vermeiden möchten, verarbeiten VBA-Makros jede Beschreibung effizienter und weisen mithilfe anpassbarer Logik und Eingabeaufforderungen automatisch die passende Kategorie zu.
Arbeitsschritte:
- Bereiten Sie eine Liste mit Stichwörtern und den zugehörigen Kategorien vor – ähnlich wie die Hilfsspalten bei der Formelmethode (z. B. ab Zeile 30 in den Spalten A und B nach unten).
- Drücken Sie Alt + F11, um den Visual Basic for Applications-Editor zu öffnen. Klicken Sie im VBA-Fenster auf Einfügen > Modul, um ein neues Modul hinzuzufügen.
Kopieren Sie den folgenden Code in das Modul:
Sub CategorizeTransactions()
Dim lastRow As Long
Dim i As Long
Dim descCell As Range
Dim kwRow As Long
Dim kwRange As Range
Dim catRange As Range
Dim kwCount As Long
Dim catResult As String
Dim matched As Boolean
On Error Resume Next
xTitleId = "KutoolsforExcel"
kwCount = Cells(Rows.Count, "A").End(xlUp).Row - 29
Set kwRange = Range("A30:A" & 29 + kwCount)
Set catRange = Range("B30:B" & 29 + kwCount)
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 3 To lastRow
Set descCell = Cells(i, "B")
catResult = "Other"
matched = False
For kwRow = 1 To kwCount
If InStr(1, descCell.Value, kwRange.Cells(kwRow, 1).Value, vbTextCompare) > 0 Then
catResult = catRange.Cells(kwRow, 1).Value
matched = True
Exit For
End If
Next kwRow
Cells(i, "F").Value = catResult
Next i
End SubVerwendung:
- Klicken Sie auf die Schaltfläche
Ausführen oder drücken Sie F5 im VBA-Editor, um das Makro auszuführen. Das Makro verarbeitet jede Transaktionsbeschreibung in Spalte B, vergleicht sie mit der Stichwortliste und trägt die passende Kategorie – oder „Other“, falls keine Übereinstimmung gefunden wird – in Spalte F derselben Zeile ein. - Sie können Anzahl der Zeilen festlegen/Bereiche innerhalb des Makros an Ihr Datenlayout anpassen – beispielsweise den Startpunkt der Transaktionsbeschreibungen oder den Speicherort der Hilfsliste ändern. Stellen Sie sicher, dass Ihre Stichwortliste keine leeren Zellen enthält und die Kategorien klar sowie eindeutig formuliert sind, um eine einfache Referenzierung zu ermöglichen.
Ausführen oder drücken Sie F5 im VBA-Editor, um das Makro auszuführen. Das Makro verarbeitet jede Transaktionsbeschreibung in Spalte B, vergleicht sie mit der Stichwortliste und trägt die passende Kategorie – oder „Other“, falls keine Übereinstimmung gefunden wird – in Spalte F derselben Zeile ein.Vor- und Nachteile:Die VBA-Lösung lässt sich an komplexere Regeln anpassen, kann jederzeit nach einer Aktualisierung der Stichwort- oder Kategorieliste erneut ausgeführt werden und macht Matrixformeln überflüssig. Allerdings setzen Makros die Aktivierung automatisierter Vorgänge in Excel voraus, was nicht für alle Benutzer oder Umgebungen geeignet ist.
Praktischer Hinweis: Speichern Sie Ihr VBA-Makro in einer wiederverwendbaren Datei und sichern Sie stets Ihre Daten, bevor Sie Makros ausführen – so vermeiden Sie versehentliches Überschreiben.
Fehlerbehebungshinweise:Sollten Kategorien nicht wie erwartet aktualisiert werden, prüfen Sie, ob Ihre Stichwort- und Kategorielisten übereinstimmen und ob sich keine versteckten Zeichen in den Zellen befinden. VBA ist bei Verwendung von „vbTextCompare“ nicht groß-/kleinschreibungsabhängig, doch Abweichungen können dennoch aufgrund von Formatierungsunterschieden auftreten.
Weitere integrierte Excel-Methoden – Power Query zur regelbasierten Kategorisierung mithilfe bedingter Spaltenlogik verwenden
Wenn Sie einen modernen, codefreien Automatisierungsansatz bevorzugen, bietet Power Query eine leistungsstarke Lösung zur Kategorisierung Ihrer Banktransaktionen. Diese Methode eignet sich ideal für den Import von Transaktionsdaten aus CSV-Dateien oder Onlinequellen sowie für die Verwaltung dynamischer und sich weiterentwickelnder Kategorisierungsregeln – denn sie zentralisiert die Logik und ermöglicht einfache Aktualisierungen ohne komplexe Formeln oder VBA-Skripte.
Arbeitsschritte:
- Stellen Sie zunächst sicher, dass Ihre Transaktionen als Tabelle oder klar strukturierter Datenbereich formatiert sind. Wählen Sie eine beliebige Zelle in Ihrer Tabelle aus und navigieren Sie dann zu Daten > Aus Tabelle/Bereich, um die Daten in Power Query zu laden.
- Klicken Sie im Power Query-Editor auf Spalte hinzufügen > Bedingte Spalte, um eine neue regelbasierte Spalte für Kategorien zu erstellen.
- Definieren Sie Ihre Abgleichregeln, z. B.:
- Wenn Beschreibung„Mc Donalds" enthält, dann geben Sie „Takeout" aus
- Wenn Beschreibung„Walmart" enthält, dann geben Sie „Family fee" aus
- Andernfalls geben Sie „Other" aus
- Klicken Sie auf OK, um die bedingte Spalte zu erstellen, und wählen Sie anschließend Schließen & Laden, um Ihre kategorisierten Ergebnisse nach Excel zurückzugeben.
Vor- und Nachteile:Power Query bietet maximale Flexibilität bei der Anpassung von Regeln, lässt sich nahtlos in externe Datenimporte integrieren und aktualisiert Ergebnisse blitzschnell, sobald sich Transaktionsdaten oder Regeln ändern. Zudem verarbeitet es sehr große Datensätze deutlich effizienter als manuelle Formeln. Allerdings erfordert Power Query eine grundlegende Einrichtung sowie Vertrautheit mit seiner Benutzeroberfläche – was für einige Anwender neu sein könnte.
Praktische Tipps:Ordnen Sie Ihre Regeln im Dialogfeld „Bedingte Spalte“ nach Priorität an (von oben nach unten); es wird stets die erste übereinstimmende Regel angewendet. Sie können Regeln in Power Query problemlos bearbeiten, hinzufügen oder löschen, ohne die Formeln Ihres eigentlichen Excel-Arbeitsblatts zu verändern.
Fehlerhinweise: Wenn Sie neue Buchungen importieren und sich das Ergebnis nicht wie erwartet aktualisiert, klicken Sie in der Registerkarte „Daten“ von Excel auf Aktualisieren. Achten Sie in Power Query auf die exakte Schreibweise und präzise Stichwortübereinstimmungen – bereits geringfügige Abweichungen können dazu führen, dass die richtige Kategorie nicht zugeordnet wird.
Fehlerbehebungstipp:Falls Ihre Kategorien nicht korrekt angezeigt werden, überprüfen Sie Ihre Regeln für bedingte Spalten auf Widersprüche oder fehlende Fälle. Es empfiehlt sich, einige Beispielbuchungen zu testen, bevor Sie Änderungen auf den gesamten Datensatz anwenden.
Zusammenfassend bietet Excel – unabhängig davon, ob Sie Arrayformeln, die Flexibilität eines VBA-Makros oder die strukturierte Automatisierung von Power Query wählen – mehrere praktikable Möglichkeiten, Ihre Bankbuchungen effizient zu kategorisieren. Überprüfen Sie stets Ihre Kategorisierungsregeln und die Konsistenz der Ergebnisse, sobald sich Ihre Buchungsdaten oder Kategorisierungskriterien ändern.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins