Zelle enthält eines von vielen Dingen

Um zu testen, ob eine Zelle eine von vielen Zeichenfolgen enthält, können Sie eine Formel verwenden, die auf der SUCHE , IST NUMMER und SUMMENPRODUKT Funktionen. Die nach unten kopierte Formel in C5 lautet:

= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,A1)))>0

wo Dinge ist der benannter Bereich E5:E7.

Das Ziel dieses Beispiels besteht darin, jede Zelle in B5:B14 zu testen, um zu sehen, ob sie eine der Zeichenfolgen in der enthält benannter Bereich Dinge (E5:E7). Diese Zeichenfolgen können vorkommen irgendwo in der Zelle, also ist dies ein wörtliches 'enthält'-Problem. Die nach unten kopierte Formel in C5 lautet:

 
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

Diese Formel basiert auf eine andere formel die eine Zelle auf eine einzelne Teilzeichenfolge überprüft. Wenn die Zelle die Teilzeichenfolge enthält, gibt die Formel TRUE zurück. Wenn nicht, gibt die Formel FALSE zurück:

 
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

Wenn das Suchfunktion einen String findet, gibt es die Position dieses Strings als Zahl zurück. Wenn SUCHE nicht Wenn Sie einen String finden, wird ein #VALUE zurückgegeben! Error. Dies bedeutet, dass ISNUMBER TRUE zurückgibt, wenn eine Übereinstimmung vorliegt und FALSE, wenn nicht.

In diesem Beispiel besteht das Ziel darin, nach mehr als einem String zu suchen, daher geben wir der SEARCH-Funktion a Liste der Zeichenfolgen in dem benannter Bereich Dinge . Da es 3 Saiten in . gibt Dinge ('rot', 'grün' und 'blau'), SEARCH gibt 3 Ergebnisse in an . zurück Array so was:

Woher ruft die Excel-Funktion jetzt das Datum ab?

 
 ISNUMBER ( SEARCH (substring,B5)) // test for substring

Da 'red' und 'blue' nicht gefunden werden, gibt die SEARCH einen #VALUE! Error. Da jedoch 'grün' nahe dem Ende des Textes in Zelle B5 erscheint, gibt SEARCH 23 zurück (d. h. 'grün' beginnt am 23. Zeichen).

Dieses Array wird direkt an die . zurückgegeben ISNUMBER-Funktion , die die Elemente im Array entweder in TRUE oder FALSE konvertiert:

 
{#VALUE!#VALUE!23}

Wenn wir nur ein TRUE im Array haben, wissen wir logischerweise, dass eine Zelle mindestens einen der gesuchten Strings enthält. Der einfachste Weg, um auf TRUE zu prüfen, besteht darin, alle Werte zusammenzuzählen. Das können wir mit SUMMENPRODUKT-Funktion , aber zuerst müssen wir die TRUE / FALSE-Werte mit a . auf 1s und 0s erzwingen Doppel negativ (--) so was:

 
 ISNUMBER ({#VALUE!#VALUE!23}) // returns {FALSEFALSETRUE}

Dies ergibt ein neues Array, das nur 1s und 0s enthält:

Wie fügen Sie die Registerkarte Entwickler zum Menüband hinzu?

 
--{FALSEFALSETRUE} // coerce to 1s and 0s

die direkt an SUMPRODUCT geliefert wird:

 
{001}

Mit nur einem zu verarbeitenden Array summiert SUMPRODUCT die Elemente im Array und gibt ein Ergebnis zurück. Jedes Ergebnis ungleich Null bedeutet, dass wir einen 'Treffer' haben, also addieren wir >0, um ein Endergebnis von TRUE oder FALSE zu erzwingen:

 
= SUMPRODUCT ({001}) // returns 1

Beachten Sie, dass jede Kombination von Übereinstimmungen eine Zahl größer als Null zurückgibt und dazu führt, dass die Formel TRUE zurückgibt.

Mit einer hartcodierten Liste

Es ist nicht erforderlich, einen Bereich für die Liste der zu suchenden Zeichenfolgen zu verwenden. Sie können auch eine Array-Konstante . Um beispielsweise nach „Rot“, „Blau“ oder „Grün“ zu suchen, können Sie eine Formel wie diese verwenden:

 
= SUMPRODUCT ({001})>0 // returns TRUE

Summenfunktion

Historisch, SUMMENPRODUKT erscheint oft in Array-Formeln , da es Arrays nativ verarbeiten kann, ohne Steuerung + Umschalt + Eingabetaste . Dies macht die Formel für die meisten Benutzer 'freundlicher'. In Excel 365 , die Arrays behandelt nativ , das Summenfunktion kann anstelle von SUMPRODUCT ohne Control + Shift + Enter verwendet werden:

wie man Zahlen vom Text in Excel trennt

 
= SUMPRODUCT (-- ISNUMBER ( SEARCH ({'red','blue','green'},B5)))>0

Verhindern von falschen Übereinstimmungen

Ein Problem bei diesem Ansatz besteht darin, dass Sie möglicherweise falsche Übereinstimmungen von Teilzeichenfolgen erhalten, die in längeren Wörtern vorkommen. Wenn Sie beispielsweise versuchen, „dr“ zu finden, finden Sie möglicherweise auch „Andrea“, „drink“, „dry“ usw., da in diesen Wörtern „dr“ vorkommt. Dies geschieht, weil SEARCH automatisch eine Übereinstimmung mit 'enthält' durchführt.

Für einen schnellen Hack können Sie Leerzeichen um die Suchbegriffe (z. Dies schlägt jedoch fehl, wenn 'dr' als erstes oder letztes in einer Zelle oder mit Satzzeichen erscheint.

Wenn Sie eine genauere Lösung benötigen, können Sie den Text normalisieren zuerst in a Helferspalte , wobei darauf zu achten ist, dass auch ein führendes und ein nachgestelltes Leerzeichen hinzugefügt wird. Dann verwenden Sie die Formel auf dieser Seite auf den resultierenden Text.

Zelle enthält eines von vielen Dingen.xlsx