Liste mit Suchbegriffen abgleichen & andere Werte ausgeben

[Taboule]

Hallo allerseits,

ich habe folgende Fragestellung:

in einer Calc-Tabelle habe ich in Spalte A verschiedene Textstrings (Mehrere Wörter). Diese Textstrings möchte ich nun mit einer Liste von Suchbegriffen in Spalte B abgleichen. Wenn eine Übereinstimmung auftritt, soll in Spalte D ("Resultat") in derselben Zeile, in der eine Übereinstimmung auftritt ein anderer Textstring/Wort/Zahlenkombination, die sich in Spalte C befinden, ausgegeben werden. Also, wenn der Suchbegriff "Service" (B2) irgendwo in Spalte A gefunden wird, wird das Wort "Dienstleistungen" in Spalte D ausgegeben, und zwar in der Zeile, in der der Suchbegriff in Spalte A gefunden wurde (Zeile 2), wenn "Miete" (B3) in Spalte A gefunden wird, wird "Mietaufwand" in Spalte D ausgegeben (und zwar in Zeile 2 und 4) usw.

Beispiel:
Spalte A
Kunde Hans Müller Service
Müller AG: Miete November
Malerei Stalder: Malereiarbeit
Friedrich Ulrich Miete

Spalte B (Suchbegriffe)
Service
Miete
Malerarbeit

Spalte C (Text, bei Übereinstimmung eingesetzt werden soll)
(Bei Übereinstimmung mit "Miete") = Mietaufwand
(Bei Übereinstimmung mit "Malerarbeit") = Raumaufwand
(Bei Übereinstimmung mit "Service") = Dienstleistungen

Spalte D (Resultat)
Dienstleistungen
Mietaufwand
Raumaufwand
Mietaufwand

Ist dies mit Open Calc überhaupt lösbar, oder müsste ich hierzu eine Datenbanklösung verwenden?

Gruss - Mike

[mikeleb]

Hallo,

das Problem ist per Matrixformel lösbar. Für eine vernünftige Darstellung ist es wichtig, als letzte Ersetzung eine zelle mit Leerzeichen zu haben.

[Taboule]

Hallo Mikeleb

Ganz herzlichen Dank für deine Arbeit! Die Formel macht genau das, was ich wollte.

Folgende Anschlussfragen:

- Ich verstehe sie noch (nicht) vollständig, kann mir eventuell jemand die Funktionsweise näherbringen?
- Könnte die Formal auch dahingehen abgeändert werden, dass die Suchbegriffe nicht 100% übereinstimmen müssen, d.h. das reguläre Ausdrücke verwendet werden?

Gruss - Mike

[mikeleb]

Hallo Mike,

die Formel ist schon verschachtelt:

Code: Alles auswählen

=VERSCHIEBUNG($C$1;MIN(WENN(ISTFEHLER(SUCHEN(($B$2:$B$4);A2));4;ZEILE($B$2:$B$4)-1));0)

1. Schritt:

Code: Alles auswählen

SUCHEN(($B$2:$B$4);A2))

In der Zelle A2 wird nach einander jeder Begriff aus dem Bereichs B2:B4 gesucht.

2. Schritt:

Code: Alles auswählen

WENN(ISTFEHLER(SUCHEN(($B$2:$B$4);A2));4;ZEILE($B$2:$B$4)-1)

Wenn der Begriff nicht gefunden wird ist das Ergebnis 4, wenn er jedoch gefunden wird ist das Ergebnis die Zeile des gefundenen Begriffs vermindert um 1

3. Schritt:

Code: Alles auswählen

MIN(WENN(ISTFEHLER(SUCHEN(($B$2:$B$4);A2));4;ZEILE($B$2:$B$4)-1))

Es wird das Minimum der gefundenen Zeilennummern gesucht.

4. Schritt:

Code: Alles auswählen

VERSCHIEBUNG($C$1;MIN(WENN(ISTFEHLER(SUCHEN(($B$2:$B$4);A2));4;ZEILE($B$2:$B$4)-1));0)

Ausgehend von C1 gehen wir um die entsprechende Anzahl nach unten und landen bei dem (gefundenen) Begriff (oder eben beim Leerzeichen).

Ich habe die Funktion in der Datei (Anhang) mal Schritt für Schritt auseinandergenommen.
Da die Funktion Suchen() reguläre Ausdrücke unterstützt, lässt sich die Suche auch noch umfangreicher gestalten.

[Taboule]

Hallo mikeleb,

Danke für die ausführliche Beschreibung!

Wenn noch weitere Verständnisfragen anbringen dürfte:
- wenn ein Suchbegriff nicht gefunden wurde, gibt die Formel "leer" bzw. nichts aus. Wäre es möglich, anstelle einer leeren Zelle einen Buchstaben/Ziffer auszugeben?
- die "4" in der Formel: "WENN(ISTFEHLER(SUCHEN(($B$2:$B$4);A2));4;ZEILE($B$2:$B$4)-1)". Ist die 4 gleichzusetzen mit der Anzahl zu untersuchenden Begriff (Spalte A)? Muss diese 4 im Voraus festgelegt werden, oder liesse sich diese Zahl auch durch eine Formel festlegen - wenn meine Annahme mit der Spalte A stimmt - falls die Anzahl zu untersuchender Begriffe "dynamisch" ist?

Danke und Gruss - Mike

[mikeleb]

Hallo Mike,

die 4 verweist im Prinzip auf den 4. Begriff bei den Ersetzungen. Dort habe ich ein Leerzeichen eingetragen, kann natürlich durch einen beliebigen Wert ausgetauscht werden.
Wichtig ist vielleicht noch, dass die 4 korrigiert werden muss, wenn die Anzahl der Ersetzungen sich ändert.
Das Ganze geht auch dynamsich. In der Anhangsvariante habe ich die Kategorien auf ein extra Tabellenblatt ausgelagert und die Stichwortliste mit Namen (hier: kategorie) versehen. So kann die Liste ergänzt und erweitert werden, ohne dass die Formeln geändert werden müssen. Wichtig bleibt, dass die letzte Zelle der Ersetzungen den gewünschten Eintrag enthält, der angezeigt wird, wenn kein Stichwort gefunden wird.

[Taboule]

Hallo mikeleb,

Sehr gut, danke zum wiederholten Male für deine Erläuterungen!

Es wird immer ein und nur ein vorher festgelegter Wert, der in letzten Zelle definiert wurde, angezeigt (z.B. "ohne Kategorie")...
Wenn ich dich richtig verstanden habe, wäre es also nicht möglich, dass wenn kein Stichwort gefunden wird, den ganzen ursprünglichen Text der entsprechenden Zelle aus Spalte A anzuzeigen (z.B. "Kunde Hans Müller Essen")?

Grüsse - Mike

[mikeleb]

Hallo Mike,

Wenn ich dich richtig verstanden habe, wäre es also nicht möglich, dass wenn kein Stichwort gefunden wird, den ganzen ursprünglichen Text der entsprechenden Zelle aus Spalte A anzuzeigen (z.B. "Kunde Hans Müller Essen")?

doch es geht - siehe Anhang.

[Taboule]

Hallo mikeleb,

Super, toll, funktioniert wie gewünscht! Tausend Dank!

Die zusätzliche Bedingung wird also mit &WENN(AKTUELL()=0;C““ angehängt.

Ich benötige die Formel für eine automatische Bearbeitung von Bankkontoauszügen. Für die Buchungsbeschreibung ist die Formel jetzt bereits hervorragend geeignet.

Für eine weitere Bearbeitung müsste ich jetzt noch eine automatische Zuweisung von Buchhaltungskonten haben: in der Spalte C oder D ist jeweils ein Betrag ausgewiesen. Spalte C = Gutschrift, Spalte D = Belastung. Wenn ein Betrag in einer der beiden Spalten ausgewiesen wird, soll in E oder F - ist der Betrag in der Spalte Gutschrift, dann in Spalte E (Soll), wenn der Betrag in der Spalte Belastung ist, dann in Spalte F (Haben) - immer eine bestimmte Kontonummer - das Buchungskonto des Bankkontos - erscheinen (zB. 1010). Ich habe dies bisher mit der einfachen Formel

Code: Alles auswählen

=WENN(ISTZAHL(C2)=0;"";1010)

für Soll bzw.

Code: Alles auswählen

=WENN(ISTZAHL(D2)=0;"";1010)

für Haben hingekriegt. Allerdings bleibt das Gegenkonto jeweils leer. Jetzt möchte ich die leere Zeile (in E oder F) jeweils mit einem entsprechenden Kontonummer füllen und hierzu deine Formel verwenden und dementsprechend ergänzen.
D.h.: wenn C oder D ein Betrag ausweist, dann = 1010 in E (Soll) oder F (Haben), wenn E oder F leer, dann nehme die Begriffe in Spalte A der Tabelle Liste, suche diese Begriffe in der Spalte A der Tabelle1 an und fülle in Spalte E oder F die entsprechende Kontonummer aus Spalte Ersetzungen2 aus Tabelle Liste ein, falls keine Entsprechung gefunden wird, soll die Zeile leer bleiben.

Die Anpassung der Bezüge in der Formel bereitet mir keine Probleme, aber die zusätzliche Wenn…Dann-Bedingung kriege ich nicht hin (was darauf zurückzuführen ist, dass ich die Formel immer noch nicht ganz verstehe).

Gruss - Mike

[mikeleb]

Hallo Mike,

formeltechnisch am einfachsten ist es,das Ergebnis der ersten Ersetzung (Kategorie) zu benutzen. Die Variante habe ich mal für Soll benutzt (hierbei muss für die dynamische Erweiterbarkeit der Liste ein Name für die Ersetzungslisten eingeführt werden).
Umfangreicher wird die Formel, wenn die Suche mit dem Anfangsproblem startet. Die Variante habe ich für Haben benutzt.

[Taboule]

Hallo mikeleb,

einfach sauber gelöst, HERZLICHEN DANK für deine Arbeit! Für mich sind momentan alle Fragen zu diesem Thema beantwortet.

Gruss - Mike