Excel | Prüfen ob mind. ein Wort aus einer Liste im Fließtext einer Zelle vorkommt

[pprada]

Hallo, 

ich habe folgendes Problem. Ich habe Zellen mit längeren Fließtexten drin. Z.B.: 

Mama fährt Auto 
Papa fährt Auto 
Mama fährt mit Papa Auto 
Mama fährt Fahrrad 

Jetzt würde ich gerne mittels einer Formel neben diesen Zellen prüfen, ob bestimmte Worte aus einer Liste innerhalb des Textes vorkommen. Ich möchte nicht die Autofilter nutzen, da es auch mal sein kann, dass ich nach dem Vorkommen von z.b. 20 Worten abfragen muss. 

Also habe ich z.b. eine spalte mit folgenden Worten darin: 

Papa 
Auto 

Die Formel soll jetzt prüfen, ob mind. eines der Worte aus meiner Liste innerhalb der Fließtexte in den Zellen steht (z.B. indem ein Wert wie "Wahr" oder dergeleichen ausgegeben wird, den man dann Filtern kann). 

Mit der Liste müsste das Ergebnis also folgendes sein: 

Mama fährt Auto 
Papa fährt Auto 
Mama fährt mit Papa Auto 

Ich habe versucht, das ganze mit der SUCHEN Funktion in Kombination mit ISTZAHL 
(also z.B. =ISTZAHL(SUCHEN(L2:L7;I3)) 

Funktioniert auch. Ich habe nur das Problem, dass wenn ein Begriff oben in der Liste nicht vorkommt (die folgenden aber relevant sind) er mir einen #WERT Fehler schmeißt und den Rest der Liste ignoriert. 

Also wenn die Liste dann so aussähe: 

Flieger 
Papa 
Auto 

Dann gibts gar keine Ergebnisse, obwohl eigentlich das vorherige wieder kommen sollte (es soll ja mind. eines der Worte aus der Liste enthalten sein). 

Wie umgehe ich dieses Problem? 

Ich danke euch. Wenn möglich, würde ich die Formel gerne recht kompakt halten, da ich sie auf ein Dokument mit über 200.000 Zeilen anwenden muss  

Dank euch. 

[steve1da]

Hola,

Code:

=VERWEIS(2;1/SUCHEN($L$2:$L$7;I3);$L$2:$L$7)

Dazu müssen aber alle Zellen in L2:L7 gefüllt sein. Die Formel gibt einen Wert aus, wird keines der Wörter in der Zelle in L2:L7 gefunden, ergibt die Formel #NV.

Gruß,
steve1da

[BoskoBiati]

Hi,

z.B. so:

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B
1	Mama fährt Auto	WAHR
2	Papa fährt Auto	WAHR
3	Mama fährt mit Papa Auto	WAHR
4	Mama fährt Fahrrad	FALSCH

Zelle	Formel
B1	{=ODER(ISTZAHL(SUCHEN($F$15:$F$16;A1)))}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

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

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

[pprada]

Ok, danke.
Um zukunftssicher zu sein, würde ich tatsächlich einen längeren Bereich vordefinieren (z.B. L2:L100). Wenn ich dich richtig verstehe, würde das aber nicht gehen, wenn z.B. nur L2:L50 mit Inhalt tatsächlich gefüllt ist?

[pprada]

Hallo [b]BoskoBiati[/b]


Bei mir bleibt die vierte Zeile leider WAHR (obwohl falsch). Auch wenn ich die Listenelemente ändere, scheint sich gar nichts zu ändern. Was mache ich falsch?

[BoskoBiati]

Hi,

Was mache ich falsch?

Bildchen hier einstellen
Bereich zu groß wählen, Leerzeichen werden dann nämlich auch gezählt.

[pprada]

Hi,

ja ok. Ich habe jetzt ein Füllzeichenset (das regulär nicht im Fließtext vorkommen sollte) per Dropdown definiert, dass anstatt einer Leerzelle eingesetzt wird. Dadurch kann ich dann tatsächlich auch einen größeren Bereich definiert lassen.

Das klappt soweit auch ganz gut. Ich hab jetzt lediglich ein performance problem, wenn ich die Formel auf über 200.000 Zeilen anwende. Excel scheint da sehr viel zu rechnen. Gibt es irgendeinen Weg, das zu optimieren?

[BoskoBiati]

Hi,

Gibt es irgendeinen Weg, das zu optimieren?

Ja, versuche mal die Formel von steve1da

[GMG-CC]

... oder löse es per VBA.

[pprada]

Ok. danke