Herbers Excel/VBA-Archiv

SVerweis teilweise Übereinstimmung Kategorisierung

• SVerweis teilweise Übereinstimmung Kategorisierung von Re Ne vom 12.08.2014 15:03:47
• nachgefragt - von neopa C (paneo) am 12.08.2014 15:23:34
  • AW: nachgefragt - von Re Ne am 12.08.2014 15:34:44
    • so nicht möglich ... doch - von neopa C (paneo) am 12.08.2014 15:44:34
      • AW: so nicht möglich ... doch - von Re Ne am 12.08.2014 16:09:21
        • AW: so nicht möglich ... doch - von Bibo am 12.08.2014 16:55:39
          • diese greift nicht ... - von neopa C (paneo) am 12.08.2014 17:19:37
        • ich hätte VERGLEICH()e vorgenommen ... - von neopa C (paneo) am 12.08.2014 17:14:56
        • mit SUCHEN(), evtl. FINDEN() geht es auch ... - von neopa C (paneo) am 12.08.2014 18:41:21
          • AW: mit SUCHEN(), evtl. FINDEN() geht es auch ... - von Re ne am 12.08.2014 19:00:20
            • die gewünschte Erläuterung ... - von neopa C (paneo) am 13.08.2014 09:01:26
              • AW: die gewünschte Erläuterung ... - von Re Ne am 13.08.2014 13:23:22
                • und für die engl. Version ..... - von neopa C (paneo) am 13.08.2014 17:41:58
                  • AW: und für die engl. Version ..... - von Re Ne am 13.08.2014 17:54:43
              • AW: die gewünschte Erläuterung ... - von Re Ne am 13.08.2014 16:28:41

Betrifft: SVerweis teilweise Übereinstimmung Kategorisierung von: Re Ne
Geschrieben am: 12.08.2014 15:03:47

Hallo,

ich habe ein verzwicktes Problem mit der Kategorisierung von Buchungszeilen.

Beispiel:
ich exportiere meine Kontobewegungen vom Onlinebanking in eine Excel-Datei. Die Daten liegen dann bspw. wie folgt vor im Tabellenblatt 1:

in Spalte A ist das Datum, in Spalte B der Buchungstext und in Spalte C der Betrag

.......... .A.....................B.....................................C.....................Kategorie
1 ....01.01.2014.......LIDL dankt POS. 1.2.............-20............=SVERWEIS(...)
2
3

Jetzt lege ich mir zum automatischen Kategorisieren der Kontobewegungen folgende Tabelle in einem neuen Tabellenblatt 2 an:

in Spalte A der Suchbegriff, in Spalte B die Kategorie

..........A............................B
1......LIDL................Lebensmittel
2
3

Mein Vorhaben ist nun, die Suchbegriffe aus der Spalte A aus dem Tabellenblatt 2 in der Spalte B im Tabellenblatt 1 zu suchen und sofern der Suchbegriff im Text vorkommt, soll die Kategorie aus Spalte B aus dem Tabellenblatt 2 in Spalte D des Tabellenblattes 1 eingefügt werden.

Kann mir da jemand helfen?

Danke!

Betrifft: nachgefragt von: neopa C (paneo)
Geschrieben am: 12.08.2014 15:23:34

Hallo,

... steht immer das erste Wort in Spalte B für den Kunden, der in Tabelle2 Spalte A gelistet ist?


Gruß Werner
.. , - ...

Betrifft: AW: nachgefragt von: Re Ne
Geschrieben am: 12.08.2014 15:34:44

Hallo,

nein, es kann auch mitten im Text oder am Ende stehen...

.......... .A.....................B.....................................C.....................Kategorie
1.....01.01.2014.......LIDL dankt POS. 1.2.............-20............=SVERWEIS(...)
2.....02.01.2014.......Automat 1 EDEKA Süd.............-10............=SVERWEIS(...)
3.....02.01.2014.......Automat 2 KaDeWe................-10... ........=SVERWEIS(...)


Suchbegriffe wären nun: LIDL, EDEKA, KaDeWe
Kategorien: jeweils Lebensmittel

Betrifft: so nicht möglich ... doch von: neopa C (paneo)
Geschrieben am: 12.08.2014 15:44:34

Hallo,

... und zwar nicht mit SVERWEIS() doch möglicherweise eine MATRIXformelösung. Dazu stell doch mal eine kleine Beispielarbeitsmappe hier ein.


Gruß Werner
.. , - ...

Betrifft: AW: so nicht möglich ... doch von: Re Ne
Geschrieben am: 12.08.2014 16:09:21

https://www.herber.de/bbs/user/92050.xlsx

Beispieldatei

Betrifft: AW: so nicht möglich ... doch von: Bibo
Geschrieben am: 12.08.2014 16:55:39

Hallo Re Ne,

das könnte es sein.

https://www.herber.de/bbs/user/92051.xlsx

Gruß

Bibo

Betrifft: diese greift nicht ... von: neopa C (paneo)
Geschrieben am: 12.08.2014 17:19:37

Hallo Bibo,

... dazu kopiere z.B. einfach mal meine Beispieldaten aus Tabelle2 in Deine.

Gruß Werner
.. , - ...

Betrifft: ich hätte VERGLEICH()e vorgenommen ... von: neopa C (paneo)
Geschrieben am: 12.08.2014 17:14:56

Hallo,

... (Formel nach unten kopieren):

Tabelle1

	B	G
1	Buchungstext	Kategorie
2	DE 21,54 DEBIT POS 01.01.14 18.02S1 LIDL DANKT 1050456	L-Lebensmittel
3	MEDIA DANKT 5616 K1 10.01.UM 17:54 MEDIA MARKT 681897	Technik
4

Formeln der Tabelle

Zelle Formel G2 {=INDEX(Tabelle2!B:B;MIN(WENN(ISTZAHL(VERGLEICH("*"&Tabelle2!A$2:A$99&"*";Tabelle1!B2;)); ZEILE(A$2:A$99))))}

Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen!

Tabelle2

	A	B
1	Suchbegriff	Kategorie
2	Aldi	A-Lebensmittel
3	LIDL	L-Lebensmittel
4	Netto	N-Lebensmittel
5	MEDIA	Technik
6

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Werner
.. , - ...

Betrifft: mit SUCHEN(), evtl. FINDEN() geht es auch ... von: neopa C (paneo)
Geschrieben am: 12.08.2014 18:41:21

Hallo,

... und sogar kürzer als mit VERGLEICH(). Und zwar würde ich SUCHEN() nehmen, weil möglicherweise die Groß- und Kleinschreibung für die Suchbegriffe nicht immer wirklich identisch sind.

Tabelle1

	G
2	L-Lebensmittel

Formeln der Tabelle

Zelle Formel G2 {=INDEX(Tabelle2!B:B;MIN(WENN(ISTZAHL(SUCHEN(Tabelle2!A$2:A$9;B2)); ZEILE(A$2:A$9))))}

Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen!

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


Gruß Werner
.. , - ...

Betrifft: AW: mit SUCHEN(), evtl. FINDEN() geht es auch ... von: Re ne
Geschrieben am: 12.08.2014 19:00:20

Danke!

Neopa, kannst du auch beschreiben, wie die letzte von dir angegebene formel vorgeht bzw. was sie macht.... Ich wills gern verstehen und nicht blind kopieren ;-)

LG

Betrifft: die gewünschte Erläuterung ... von: neopa C (paneo)
Geschrieben am: 13.08.2014 09:01:26

Hallo,

... die Daten aus Tabelle2 im (momentan def.) Bereich A2:A9 werden als Suchwörter der Funktion SUCHEN() übergeben, damit diese in der jeweilige Zelle (Spalte B) der aktuellen Tabelle prüft ob diese dort auffindbar ist. Diese Teilformel ergibt eine Matrix aus Fehlerwerten und im "Erfolgsfalle" zumindest einer Zahl (Fundstelle). MIT ISTZAHL() wird die MATRIX in eine MATRIX von FALSCH und evtl. einem WAHR-Wert gewandelt. mit der Teilformel MIN(WENN(..., ZEILE(...))) wird nun die erste (MIN) Position des WAHR ermittelt und diese der Funktion INDEX() übergeben, damit diese von genau dieser (Position/Zeile) Stelle aus der Matrix Tabelle!2B:B den gesuchten Wert Dir übergibt.

Die vorliegende Formel sollte vielleicht noch mit WENNFEHLER(...; "nicht definiert in Tabelle2 SpalteA") geklammert werden, um auch diese Möglichkeit "sauber" zum Abschluß zu bringen.


Gruß Werner
.. , - ...

Betrifft: AW: die gewünschte Erläuterung ... von: Re Ne
Geschrieben am: 13.08.2014 13:23:22

Hallo Werner,

ich habe leider nochmal Probleme...

ich habe deine Formel übernommen... mein Excel ist allerdings Englisch. Ich habe die Formeln auf die Englischen Bezeichnungen umgeändert.

Jedoch funktionierts leider nicht...

Kannst du dir das mal anschauen?

https://www.herber.de/bbs/user/92072.xlsx

Betrifft: und für die engl. Version ..... von: neopa C (paneo)
Geschrieben am: 13.08.2014 17:41:58

Hallo,

... siehe untenstehend. Wobei ich meine Aussage von heute Morgen korrigieren muß. WENNFEHLER() ist gar nicht notwendig. Es reicht wenn Du in Sheet2 immer in der ersten Zelle nach den Kategorien einen entsprechende Textinfo zu stehen hast (ohne eine solche ergäbe sich da immer ein Ergebnis 0-Wert.

Sheet1

	A	B	C	D	E	F	G
1	Buchungsdatum	Buchungstext	Datum	Betrag	Währung	Zeit	Kategorie
2	09.07.2014	Text1	08.07.2014	-10	EUR	09.07.2014 00:38:06:084	noch nicht definiert
3	10.07.2014	text2	10.07.2014	10	EUR	09.07.2014 00:38:06:084	noch nicht definiert
4	10.07.2014	text3	08.07.2014	-10	EUR	09.07.2014 00:38:06:084	noch nicht definiert
5	11.07.2014	text LIDL text	11.07.2014	-10	EUR	09.07.2014 00:38:06:084	Lebensmittel
6	14.07.2014	text3	12.07.2014	-10	EUR	09.07.2014 00:38:06:084	noch nicht definiert
7	14.07.2014	text3	12.07.2014	-10	EUR	09.07.2014 00:38:06:084	noch nicht definiert
8	14.07.2014	text3	11.07.2014	-10	EUR	09.07.2014 00:38:06:084	noch nicht definiert
9	14.07.2014	text3	11.07.2014	-10	EUR	09.07.2014 00:38:06:084	noch nicht definiert
10	14.07.2014	text3	10.07.2014	-10	EUR	09.07.2014 00:38:06:084	noch nicht definiert
11	14.07.2014	ENI Text Eni	11.07.2014	-10	EUR	09.07.2014 00:38:06:084	Tanken
12	14.07.2014	text2	14.07.2014	-10	EUR	09.07.2014 00:38:06:084	noch nicht definiert
13	16.07.2014	text2	15.07.2014	-10	EUR	09.07.2014 00:38:06:084	noch nicht definiert
14	16.07.2014	text2	17.07.2014	-10	EUR	09.07.2014 00:38:06:084	noch nicht definiert
15	16.07.2014	text2	17.07.2014	-10	EUR	09.07.2014 00:38:06:084	noch nicht definiert
16

Formeln der Tabelle

Zelle Formel G2 {=INDEX(Sheet2!B:B,MIN(IF(ISNUMBER(SEARCH(Sheet2!A$1:A$9,B2)),ROW(A$1:A$9))))}

Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen!

Sheet2

	A	B
1	LIDL	Lebensmittel
2	ENI	Tanken
3		noch nicht definiert
4

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


Gruß Werner
.. , - ...

Betrifft: AW: und für die engl. Version ..... von: Re Ne
Geschrieben am: 13.08.2014 17:54:43

Hallo Werner,

vielen herzlichen Dank für deine Hilfe! Es funktioniert nun! Das mit dem "noch nicht definiert! habe ich auch gleich eingebaut!

LG

Betrifft: AW: die gewünschte Erläuterung ... von: Re Ne
Geschrieben am: 13.08.2014 16:28:41

Ich glaube ich habe die Verwendung des Forums noch nicht korrekt umgesetzt und jetzt den Haken im Kontrollkästchen bezügl. Beantwortung der Frage gesetzt.

LG

Beiträge aus den Excel-Beispielen zum Thema "SVerweis teilweise Übereinstimmung Kategorisierung"