Excel

Erste Übereinstimmungszelle abrufen enthält

Get First Match Cell Contains

Excel-Formel: Erste Übereinstimmungszelle abrufen enthältAllgemeine Formel |_+_| Zusammenfassung

Um eine Zelle auf eines von mehreren Dingen zu überprüfen und die erste in der Liste gefundene Übereinstimmung zurückzugeben, können Sie eine INDEX / MATCH-Formel verwenden, die SEARCH oder FIND verwendet, um eine Übereinstimmung zu finden. Im gezeigten Beispiel lautet die Formel in C5:



{= INDEX (things, MATCH (TRUE, ISNUMBER ( SEARCH (things,A1)),0))}

wo 'Dinge' sind benannter Bereich E5:E9.

Wenn eine Zelle einen Wert hat, dann

Hinweis: Dies ist ein Array-Formel und muss mit Strg + Umschalt + Eingabetaste eingegeben werden.





Erläuterung

In diesem Beispiel haben wir eine Liste von Farben in einem benannten Bereich namens 'Dinge' (E5:E9). Wir möchten den Text in Spalte B überprüfen, um zu sehen, ob er eine dieser Farben enthält. Wenn ja, möchten wir den Namen der ersten gefundenen Farbe zurückgeben.

Von innen nach außen arbeitend, verwendet diese Formel die ISNUMBER-Funktion und Suchfunktion um den Text in B5 nach jeder Farbe zu durchsuchen, die in 'Dinge' wie folgt aufgeführt ist:



 
{= INDEX (things, MATCH (TRUE, ISNUMBER ( SEARCH (things,B5)),0))}

Dieser Ausdruck basiert auf einer Formel ( hier ausführlich erklärt ), das eine Zelle auf eine einzelne Teilzeichenfolge überprüft. Wenn die Zelle die Teilzeichenfolge enthält, gibt der Ausdruck TRUE zurück. Wenn nicht, gibt der Ausdruck FALSE zurück.

Wenn wir diese SUCHE a . geben Liste der Dinge (statt einer Sache) wird uns eine Reihe von Ergebnissen zurückgeben. Jede gefundene Farbe generiert eine numerische Position, und nicht gefundene Farben erzeugen einen Fehler:

 
 ISNUMBER ( SEARCH (things,B5)

Die ISNUMBER-Funktion wandelt dann Ergebnisse in TRUE / FALSE-Werte um. Jede Zahl wird WAHR und jeder Fehler (nicht gefunden) wird FALSCH. Das Ergebnis ist ein Array wie dieses:

 
{#VALUE!#VALUE!20#VALUE!#VALUE!}

Dieses Array wird an den . zurückgegeben MATCH-Funktion als Array-Argument. Der Nachschlagewert ist WAHR und der Übereinstimmungstyp wird auf Null gesetzt, um eine genaue Übereinstimmung zu erzwingen. Wenn eine passende Farbe vorhanden ist, gibt MATCH die Position zurück, die als erstes TRUE gefunden wurde. Dieser Wert wird in die INDEX-Funktion als Zeilennummer, wobei der benannte Bereich 'things' als Array bereitgestellt wird. Wenn es mindestens eine Übereinstimmung gibt, gibt INDEX die Farbe an dieser Position zurück. Wenn keine Übereinstimmung gefunden wird, gibt diese Formel den Fehler #N/A zurück.

Mit hartcodierten Werten

Wenn Sie in diesem Beispiel keinen externen benannten Bereich wie 'things' einrichten möchten, können Sie Werte wie folgt als 'Array-Konstanten' in die Formel hartcodieren:

wie man Zielsuche in Excel macht
 
{FALSEFALSETRUEFALSEFALSE}

Erste Übereinstimmung in Zelle erhalten

Die Sprache hier ist ziemlich verwirrend, aber die obige Formel gibt die erste Übereinstimmung zurück, die in der gefunden wurde Liste der Dinge, nach denen man suchen sollte . Wenn Sie stattdessen die erste gefundene Übereinstimmung zurückgeben möchten in der zu testenden Zelle , können Sie eine Formel wie diese versuchen:

 
{= INDEX ({'red','green','blue'}, MATCH (TRUE, ISNUMBER ( SEARCH ({'red','green','blue'},B5)),0))}

In dieser Version der Formel ist die MATCH-Funktion so eingerichtet, dass sie nach dem Ergebnis dieses Snippets sucht:

 
= INDEX (things, MATCH ( AGGREGATE (15,6, SEARCH (things,A1),1), SEARCH (things,A1),0))

die nutzt die AGGREGATE-Funktion um den Mindestwert in den von SEARCH zurückgegebenen Ergebnissen zu erhalten. Wir brauchen hier AGGREGATE, da das eingehende Array wahrscheinlich Fehler enthält (wird von SEARCH zurückgegeben, wenn Dinge nicht gefunden werden), und wir brauchen eine Funktion, die diese Fehler ignoriert und uns trotzdem den minimalen numerischen Wert liefert.

Das Ergebnis von AGGREGATE wird als Suchwert direkt an MATCH zurückgegeben, zusammen mit demselben von SEARCH zurückgegebenen Array. Das Endergebnis ist die erste Übereinstimmung in der Zelle, nicht die erste Übereinstimmung in der Liste der Dinge.

Autor Dave Bruns


^