Microsoft Excel

Herbers Excel/VBA-Archiv

SVerweis teilweise Übereinstimmung Kategorisierung

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

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

Formeln der Tabelle
ZelleFormel
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

 AB
1SuchbegriffKategorie
2AldiA-Lebensmittel
3LIDLL-Lebensmittel
4NettoN-Lebensmittel
5MEDIATechnik
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
2L-Lebensmittel

Formeln der Tabelle
ZelleFormel
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

 ABCDEFG
1BuchungsdatumBuchungstextDatumBetragWährungZeitKategorie
209.07.2014Text108.07.2014-10EUR09.07.2014 00:38:06:084noch nicht definiert
310.07.2014text210.07.201410EUR09.07.2014 00:38:06:084noch nicht definiert
410.07.2014text308.07.2014-10EUR09.07.2014 00:38:06:084noch nicht definiert
511.07.2014text LIDL text11.07.2014-10EUR09.07.2014 00:38:06:084Lebensmittel
614.07.2014text312.07.2014-10EUR09.07.2014 00:38:06:084noch nicht definiert
714.07.2014text312.07.2014-10EUR09.07.2014 00:38:06:084noch nicht definiert
814.07.2014text311.07.2014-10EUR09.07.2014 00:38:06:084noch nicht definiert
914.07.2014text311.07.2014-10EUR09.07.2014 00:38:06:084noch nicht definiert
1014.07.2014text310.07.2014-10EUR09.07.2014 00:38:06:084noch nicht definiert
1114.07.2014ENI Text Eni11.07.2014-10EUR09.07.2014 00:38:06:084Tanken
1214.07.2014text214.07.2014-10EUR09.07.2014 00:38:06:084noch nicht definiert
1316.07.2014text215.07.2014-10EUR09.07.2014 00:38:06:084noch nicht definiert
1416.07.2014text217.07.2014-10EUR09.07.2014 00:38:06:084noch nicht definiert
1516.07.2014text217.07.2014-10EUR09.07.2014 00:38:06:084noch nicht definiert
16       

Formeln der Tabelle
ZelleFormel
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

 AB
1LIDLLebensmittel
2ENITanken
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"