Index-Verweis-Formel verallgemeinern?

[Rabe]

Hi,

ich habe in Spalte N und O eine intelligente Tabelle von Zeile 1 bis 56, mit der folgenden Formel ziehe ich die Kategorie zum in F9 geschriebenen Empfänger:

Code:

=INDEX(O$2:O$57;SUMMENPRODUKT(ZÄHLENWENN(F9;"*"&N$3:N$56&"*")*ZEILE($1:$54))+1)

Wie kann ich das verallgemeinern, damit ich bei Hinzufügen neuer Empfänger (Verlängerung der intelligenten Tabelle) nicht jedes mal die End-Zeilennnummern in der Formel anpassen muß?

Arbeitsblatt mit dem Namen 'PB_Kontoauszug_Giro'
	N	O
1	Empfänger	Kategorie
2		unklar
3	1u1	Handy
4	ADAC	ADAC
5	Aldi	Ernährung
6	ARAL AG	Tanken

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2013

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

[Der Steuerfuzzi]

Hallo,

Ich würde das so machen (wobei Tabelle1 der Name der Intelligenten Tabelle ist):
=SVERWEIS("*"&F9&"*";Tabelle1;2;FALSCH)

[Ego]

Hallo Rabe,

das hätte ich nicht von dir erwartet:

1. dass du keine Beispieldatei anhängst,
2. dass du dieses Konstrukt genutzt hast anstatt eines einfachen SVERWEIS,
3. dass du die Vorteile der intelligenten Tabelle nicht nutzt.


Hier zwei Vorschläge:
Auf jeden Fall empfehle ich der Tabelle über den Namensmanager einen sprechenden Namen zu geben, damit die Formeln besser zu lesen sind (ich habe sie Kategorie genannt).

1. =SVERWEIS(G3;Kategorie;2;FALSCH)

und falls du bei Erweiterung der Tabelle um zusätzliche Spalten vor der Kategorie die Formeln nicht mehr anpassen möchtest geht auch:

2. =INDEX(Kategorie[Kategorie];VERGLEICH(G3;Kategorie[Empfänger];0))


ps. sorry, F9 statt G3 oder auch "*"&F9&"*"

[Der Steuerfuzzi]

Ich vermute mal, dass dieses "Konstrukt" verwendet wurde, um die Wildcards zu nutzen. Wobei ZÄHLENWENN hier mE nicht geeignet ist, da bei einem mehrfach vorkommenden Begriff keine sinnvollen Ergebnisse herauskommen. (Wenn es noch einen Emüfänger namens "Zabadack" geben würde, wäre dieser bei der Eingabe ADAC im ZÄHLENWENN enhalten und damit würden die beiden Zeilen addiert).

Es ist sowieso die Frage, ob man das mit den Wildcards umsetzen sollte, wenn nur ein Ergebnis gezeigt werden soll. Denn wenn "Zabadack" vor "ADAC" stehen würde, würde fälschlicherweise die zum ersten Lieferanten gehörende Kategorie als Ergebnis geliefert.

Hier könnte man die genaue Übereinstimmung suchen und falls diese nicht gefunden wird, die erste ungefähre ausgeben:
=WENNFEHLER(INDEX(Tabelle1[Kategorie];VERGLEICH(F9;Tabelle1[Empfänger];0));SVERWEIS("*"&F9&"*";Tabelle1;2;FALSCH))

Gegebenenfalls sollte man dazu noch den gefundenen Empfänger ausgeben.

[Jockel]

Hi,

ich habe in Spalte N und O eine intelligente Tabelle von Zeile 1 bis 56, mit der folgenden Formel ziehe ich die Kategorie zum in F9 geschriebenen Empfänger:

Code:

=INDEX(O$2:O$57;SUMMENPRODUKT(ZÄHLENWENN(F9;"*"&N$3:N$56&"*")*ZEILE($1:$54))+1)

...

Hallo Ralf, die Formel ist mir vor Kurzem doch hier über den Weg gelaufen. Hilf mir doch mal. Wo war das denn? Oder irre ich mich..?

[BoskoBiati]

Hi,

Da aus dem bisschen Tabelle nur wenig nachvollziehbares ersichtlich ist und die Formel bei mir nur Fehler produziert, wäre ein vernünftiges Muster sinnvoll.

[schauan]

Hallöchen,

Meine Stimme bekommt Der Steuerfuzzi Michael

Arbeitsblatt mit dem Namen 'Tabelle1'
	F	G	N	O
1			Empfänger	Kategorie
2				unklar
3			1u1	Handy
4			ADAC	ADAC
5			Aldi	Ernährung
6			ARAL AG	Tanken
7			Otto	Car
8
9	Otto	Car

Zelle	Formel
G9	=WENNFEHLER(INDEX(Tabelle1[Kategorie];VERGLEICH(F9;Tabelle1[Empfänger];0));SVERWEIS("*"&F9&"*";Tabelle1;2;FALSCH))

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

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

Ich denke, der gepostete Tabellenausschnitt war doch ausreichend - eine Beispieldatei nicht nötig

[Rabe]

Hallo zusammen,
danke für die Ideen und Vorschläge.

Da ich im Ausland war und die Datei nicht dabei hatte, konnte ich keine Beispieldatei mehr zeigen.

das hätte ich nicht von dir erwartet:
1. dass du keine Beispieldatei anhängst,
2. dass du dieses Konstrukt genutzt hast anstatt eines einfachen SVERWEIS,
3. dass du die Vorteile der intelligenten Tabelle nicht nutzt.

Vorschlag
2. =INDEX(Kategorie[Kategorie];VERGLEICH(G3;Kategorie[Empfänger];0))

So, nun im Einzelnen:
1) ich dachte, der Ausschnitt reicht.
2) ich wollte, wie schon Michael sagte, auch nur Teile des Textes im Datenfeld erkennen, wobei ich das Risiko von "Zabadack" vs. "ADAC" eingehen würde.
3) die intelligente Tabelle habe ich auch erst vor kurzem eingefügt, aber nicht gedacht, daß ich sie wegen der Formel mit den unterschiedlichen Zeilenzahlen (55, 57, 58) verwenden kann.

Die Formel:
=INDEX(Kategorie[Kategorie];VERGLEICH("*"&F9&"*";Kategorie[Empfänger];0))
geht nicht so richtig, da ich in Spalte F ja die langen Bezeichnungen aus der Umsatzauskunft stehen habe und in der intelligenten Kategorie-Tabelle die Texte, die nur Teil des langen Begriffs sind.
Wenn also der Begriff in der intelligenten Kategorie-Spalte ein Ober-/Teil-Begriff des Inhaltes von Spalte F ist.
Da funktionieren auch die Formeln von Michael und André nicht richtig.

Anbei nun eine Beispieldatei mit allen 4 Formeln, wobei die alte als einzige richtig funktioniert, aber eben wegen den unterschiedlichen Zeilenzahlen (55, 57, 58) umständlich zu erweitern ist.

  Kontoumsätze_2018-01-15.xlsb (Größe: 28,43 KB / Downloads: 10)

Erweiterung der Frage:
Wie kann ich nun noch die Spalte Auftraggeber als weiteres Unterscheidungskriterium verwenden:
2. Möglichkeit:
Auftraggeber: Netze BW, Empfänger: Ralf Krause, Kategorie: PV-Einspeisung
Auftraggeber: Tom Kohl, Empfänger: Ralf Krause, Kategorie: Rückzahlung

Möglichkeit seither (soll immer noch funktionieren):
Empfänger: IKEA 328 ULM, Kategorie: Möbel
Empfänger: IKEA xxx Ludwigsburg, Kategorie: Möbel

[Rabe]

Hi Jörg,

Wo war das denn? Oder irre ich mich..?

ich habe die Formel mit Sicherheit hier aus dem Forum, denn da käme ich nicht alleine drauf.

[Der Steuerfuzzi]

Hier noch ein Vorschlag:
=INDEX(Kategorie[Kategorie];AGGREGAT(14;6;ZEILE(Kategorie[Empfänger])/(SUCHEN(Kategorie[Empfänger];F9)>0)-ZEILE(Kategorie[[#Kopfzeilen];[Empfänger]]);1))

Ich habe bei AGGREGAT den Parameter 14 (=KGRÖSSTE) gewählt, da der erste Eintrag (Unklar) ein Leerstring ist, der überall vorkommen würde und mit 15 (=KKLEINSTE) damit immer die erste Zeile zurückgegeben würde.

Wenn Du bei fehlender EIngabe in F15 den Eintrag unklar haben willst, kannst Du das z. B. mit vorgeschaltener WENNFEHLER-Funktion machen (Dann könntest Du den ersten Eintrag in der Empfänger-Tabelle auch weglassen und 15 als Parameter verwenden):
=WENNFEHLER(INDEX(Kategorie[Kategorie];AGGREGAT(14;6;ZEILE(Kategorie[Empfänger])/(SUCHEN(Kategorie[Empfänger];F9)>0)-ZEILE(Kategorie[[#Kopfzeilen];[Empfänger]]);1));"unklar")