Kategorisieren von Kontoauszügen

Forumthread: Kategorisieren von Kontoauszügen

21.10.2019 16:52:54

Kontoauszügen

Hallo zusammen,
nach vielen Hilfen, die ich hier fand, stehe ich gerade vor einem hoffentlich nicht zu schweren Rätsel.
Ich möchte meinen Kontoausgaben automatisch Klassen zuweisen. Diese Klassen erzeuge ich in einer Liste:
EDEKA LEBENSMITTEL
REWE LEBENSMITTEL
KAUFLAND LEBENSMITTEL
Kommunalbetriebe NEBENKOSTEN
Dekra VERSICHERUNG
AKTION MENSCH SPENDE
die Kontoauszüge haben lange Texte (gekürzt):
EDEKA FANDERL 18.10.2019 13.50.30 OFFLIN EUR
DEVK SEPA-BASISLASTSCHRIFTSVWZ+ OTHR sonstige
PAYMENT KAUFLAND 05.10.2019 13.10.46 OFFLIN EUR
Kasseler Kommunalbetriebe, Kommunalunternehmen (KU ) der Stadt
Ich möchte nun hinter die Kontozeilen automatisch die Kategorien schreiben lassen:
EDEKA FANDERL 18.10.2019 13.50.30 OFFLIN EUR LEBENSMITTEL
DEVK SEPA-BASISLASTSCHRIFTSVWZ+ OTHR sonstige VERSICHERUNG
PAYMENT KAUFLAND 05.10.2019 13.10.46 OFFLIN EUR LEBENSMITTEL
Kasseler Kommunalbetriebe, Kommunalunternehmen (KU ) der Stadt NEBENKOSTEN
Leider habe ich mit all meinem Wissen über SVERWEISE, INDEX, FINDEN etc, mit dem ich Dutzende Probleme im Handumdrehen gelöst habe nach vielen Stunden noch keine Lösung gefunden. Kann mir bitte jemand die Hand reichen?
Beim Hochladen der Demo-Datei bekomme ich immer Error "Hier ist ein 404-Fehler aufgetreten"

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Kategorisieren von Kontoauszügen

21.10.2019 17:00:05

Kontoauszügen
AW: mit einer Matrixfunktion(alität)sformel ...

21.10.2019 17:16:41

neopa
AW: mit einer Matrixfunktion(alität)sformel ...

21.10.2019 17:47:51

DrJedi
AW: nun dann ...

21.10.2019 17:52:58

neopa
AW: nun dann ...

21.10.2019 18:00:30

DrJedi
AW: bitteschön, gern owT

21.10.2019 18:19:15

neopa

AW: Kategorisieren von Kontoauszügen

21.10.2019 17:00:05

Kontoauszügen

Hier eine Demo-Datei:
https://www.herber.de/bbs/user/132638.xlsx

AW: mit einer Matrixfunktion(alität)sformel ...

21.10.2019 17:16:41

neopa

Hallo,
... die keines spez. Formelabschluss wie eine klassische Matrixformel benötigt und der Formatierung der Kategorieliste als "intelligente". Nachfolgend habe ich den von Excel dabei vergebenen automatisch Namen im Namensmanager umbenannt zu _Kateg_List. Diese Liste kannst Du nun beliebig ergänzen und oder verschieben. Die Formel berücksichtigt dann trotzdem alles korrekt.
In dieser Liste hab ich jedoch für Dein Beispiel mit der Versicherung ein anderes Stichwort zugewiesen, weil Deine Angabe mE nicht stimmig war.

Arbeitsblatt mit dem Namen 'Tabelle3'
	D	E	F	G	H	I	J	K
1							Stichwort	Kategorie
2	EDEKA FANDERL 18.10.2019 13.50.30 OFFLIN EUR	LEBENSMITTEL					EDEKA	LEBENSMITTEL
3	DEVK SEPA-BASISLASTSCHRIFTSVWZ+ OTHR sonstige DEKRA	VERSICHERUNG					REWE	LEBENSMITTEL
4	PAYMENT KAUFLAND 05.10.2019 13.10.46 OFFLIN EUR	LEBENSMITTEL					KAUFLAND	LEBENSMITTEL
5	Kasseler Kommunalbetriebe, Kommunalunternehmen (KU ) der Stadt	NEBENKOSTEN					Kommunalbetriebe	NEBENKOSTEN
6							OTHR	VERSICHERUNG
7							AKTION MENSCH	SPENDE
8

Zelle	Formel
E2	=INDEX(_Kateg_List[Kategorie];AGGREGAT(14;6;ISTZAHL(SUCHEN(_Kateg_List[Stichwort];D2))*ZEILE(_Kateg_List);1)-ZEILE(_Kateg_List[#Kopfzeilen]))

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...

AW: mit einer Matrixfunktion(alität)sformel ...

21.10.2019 17:47:51

DrJedi

Hallo Werner,
vielen herzlichen Dank für Deine schnelle und professionelle Antwort.
Leider muss ich zugeben, dass ich ein klein wenig mit der Namensgwebung überfordert bin und ersuche allerfreundlichst, ob es möglich wäre, dass Du die angepasste Excel mit den Namen hochladen könnstst, so dass ich im Namensmanager schauen kann, was welchen Namen trägt?
Ich bin beeindruckt von Deiner Formel und neige mein Haupt!

AW: nun dann ...

21.10.2019 17:52:58

neopa

Hallo,
... sieh mal hier: https://www.herber.de/bbs/user/132642.xlsx
Gruß Werner
.. , - ...

AW: nun dann ...

21.10.2019 18:00:30

DrJedi

Hallo Werner,
meinen allerherzlichsten Dank. Ich bin platt.
In den letzten Jahren habe ich sonstwas an Kombinationen aus Formeln, Bedingten Formatierungen, Pivot in Kombination entwickelt und bei uns die Kollegen sprachlos gemacht. Es ist schön, dass ich meinen Meister gefunden habe :-)
Jens

AW: bitteschön, gern owT

21.10.2019 18:19:15

neopa

Gruß Werner
.. , - ...

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Kategorieliste erstellen: Erstelle eine Liste in Excel mit den Kategorien und zugehörigen Stichworten. Zum Beispiel:

EDEKA    LEBENSMITTEL
REWE     LEBENSMITTEL
KAUFLAND LEBENSMITTEL
Kommunalbetriebe NEBENKOSTEN
Dekra    VERSICHERUNG
AKTION MENSCH SPENDE

Namensbereich definieren: Wähle die Liste aus und gehe zu "Formeln" > "Namensmanager" und erstelle einen neuen Namen, z.B. _Kateg_List.
Formel zum Kategorisieren: In der Zelle, in der du die Kategorie anzeigen möchtest, benutze die folgende Formel:
```
=INDEX(_Kateg_List[Kategorie];AGGREGAT(14;6;ISTZAHL(SUCHEN(_Kateg_List[Stichwort];D2))*ZEILE(_Kateg_List);1)-ZEILE(_Kateg_List[#Kopfzeilen]))
```
Diese Formel sucht in der Kategorieliste nach dem Stichwort, das in D2 steht.
Formatierung anpassen: Stelle sicher, dass die Spaltenbreite und das Format der Zellen so angepasst sind, dass die Ergebnisse klar sichtbar sind.

Häufige Fehler und Lösungen

404-Fehler beim Hochladen: Stelle sicher, dass die Datei korrekt gespeichert ist und der Link aktiv ist.
Falsche Kategorien: Überprüfe, ob die Stichworte in der Kategorieliste vollständig sind und keine Tippfehler enthalten.
Formel funktioniert nicht: Achte darauf, dass die Namen im Namensmanager korrekt definiert sind und die Formel in der richtigen Zelle eingegeben wurde.

Alternative Methoden

Bedingte Formatierung: Du kannst auch bedingte Formatierungen verwenden, um bestimmte Ausgaben farblich hervorzuheben, wenn sie zu einer bestimmten Kategorie gehören.
Pivot-Tabellen: Eine Pivot-Tabelle kann ebenfalls hilfreich sein, um Ausgaben zu summieren und nach Kategorie zu filtern.

Praktische Beispiele

Angenommen, du hast folgende Kontoauszüge:

EDEKA FANDERL 18.10.2019 13.50.30 OFFLIN EUR
DEVK SEPA-BASISLASTSCHRIFTSVWZ+ OTHR
PAYMENT KAUFLAND 05.10.2019 13.10.46 OFFLIN EUR
Kasseler Kommunalbetriebe, Kommunalunternehmen (KU ) der Stadt

Mit der oben beschriebenen Methode wirst du die Ausgaben wie folgt kategorisieren:

EDEKA FANDERL      LEBENSMITTEL
DEVK               VERSICHERUNG
PAYMENT KAUFLAND   LEBENSMITTEL
Kasseler Kommunalbetriebe NEBENKOSTEN

Tipps für Profis

Erweiterte Formeln: Nutze die Möglichkeit, mehrere Kriterien in einer Formel zu kombinieren, um noch spezifischere Kategorisierungen vorzunehmen.
Datenvalidierung: Verwende Datenvalidierung, um sicherzustellen, dass nur gültige Kategorien in deiner Kategorieliste verwendet werden.
Makros: Wenn du regelmäßig Kontoauszüge auswerten musst, könnte es sinnvoll sein, ein Makro zu erstellen, das die Kategorisierung automatisiert.

FAQ: Häufige Fragen

1. Wie kann ich neue Kategorien hinzufügen? Du kannst einfach die Kategorieliste erweitern und die Formel aktualisieren, um die neuen Kategorien zu berücksichtigen.

2. In welcher Excel-Version funktioniert das? Die beschriebenen Schritte funktionieren in Excel 2010 und neueren Versionen. Achte darauf, dass du die Funktionalitäten deiner Version nutzt.

Beliebteste Forumthreads (12 Monate)

Kategorisieren von Kontoauszügen

Forumthread: Kategorisieren von Kontoauszügen

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Infobox / Tutorial

Kontoauszüge in Excel kategorisieren

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread