Rufen Sie den ersten übereinstimmenden Wert in einer Zelle gegen eine Liste ab
Angenommen, Sie haben eine Liste von Schlüsselwörtern, um das erste Schlüsselwort abzurufen, das in einer bestimmten Zelle vorkommt, während die Zelle mehrere andere Werte enthält, müssen Sie ein . verwenden INDEX und PASSEN ZU Formel mit Hilfe von AGGREGAT und SUCHE Funktionen.
Wie rufe ich den ersten übereinstimmenden Wert in einer Zelle gegen eine Liste ab?
Um die erstes übereinstimmendes Keyword in einer Zelle mit der Keywords-Liste Wie in der obigen Tabelle gezeigt, bedeutet dies, dass wir eine enthaltene Übereinstimmung statt einer genauen Übereinstimmung durchführen müssen. Dazu können Sie mit der Funktion SUCHEN die Positionen der in der Zelle in numerischen Zahlen erscheinenden Schlüsselwörter in die Funktion AGGREGIEREN einspeisen. Dann erhält AGGREGATE die kleinste Zahl, indem es seine function_num auf 15 und das ref2-Argument auf 1. Verwenden Sie dann MATCH, um den ersten kleinsten Wert zu finden, und geben Sie die Positionsnummer an INDEX ein, um den Wert an dieser Position abzurufen.
Generische Syntax
=INDEX(keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))
√ Hinweis: Dies ist eine Matrixformel, die Sie mit eingeben müssen Ctrl + Shift + Enter.
- Schlüsselwort_rng: Der Zellbereich mit Schlüsselwörtern.
- Suchzelle: Die Zelle, die gesucht werden soll, wenn sie die Schlüsselwörter enthält.
Um die abzurufen Das erste übereinstimmende Keyword wurde in Zelle B5 in der Spalte "Keywords" angezeigt, bitte kopieren oder geben Sie die Formel unten in die Zelle C5 ein und drücken Sie Ctrl + Shift + Enter um das Ergebnis zu erhalten:
=INDEX($ E $ 5: $ E $ 7,VERGLEICH(AGGREGIEREN(15,6,SUCHEN($ E $ 5: $ E $ 7,B5),1),SUCHE($ E $ 5: $ E $ 7,B5),0))
√ Hinweis: Die Dollarzeichen ($) oben stehen für absolute Referenzen, d.h. die Schlüsselwort_rng in der Formel ändert sich nicht, wenn Sie die Formel in andere Zellen verschieben oder kopieren. Es werden jedoch keine Dollarzeichen hinzugefügt lookup_cell weil es dynamisch sein soll. Ziehen Sie nach der Eingabe der Formel den Ausfüllpunkt nach unten, um die Formel auf die unteren Zellen anzuwenden.
Erklärung der Formel
=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))
- SUCHEN($E$5:$E$7,B5): Die SEARCH-Funktion gibt die Position jedes Schlüsselworts aus dem Bereich zurück $ E $ 5: $ E $ 7 wenn in numerischen Zahlen gefunden, und die #WERT! Fehler, wenn nicht gefunden. Das Ergebnis wäre in einem Array wie folgt: {15;11;#WERT!}.
- AGGREGAT(15,6,SUCHEN($E$5:$E$7,B5), 1) = AGGREGAT(15,6,{15;11;#WERT!}, 1): Die AGGREGATE-Funktion mit a function_num von 15 und Option von 6 gibt den kleinsten Wert im Array gemäß der zurück ref2 Argument 1, während Fehlerwerte ignoriert werden. Der Ausschnitt würde also zurückkehren 11.
- SPIEL(AGGREGAT(15,6,SUCHEN($E$5:$E$7,B5), 1),SUCHEN($E$5:$E$7,B5), 0) = SPIEL(11,{15;11;#WERT!}, 0): Das Übereinstimmungstyp 0 zwingt die MATCH-Funktion, eine genaue Übereinstimmung durchzuführen und die Position von . zurückzugeben 11 im Array {15;11;#WERT!}. Die Funktion wird also zurückkehren 2.
- INDEX($E$5:$E$7,SPIEL(AGGREGAT(15,6,SUCHEN($E$5:$E$7,B5), 1),SUCHEN($E$5:$E$7,B5), 0)) = INDEX($E$5:$E$7,2): Die INDEX-Funktion gibt dann die 2nd Wert im Bereich $ E $ 5: $ E $ 7, Das ist bbb.
Anmerkung
- Wenn eine Zelle keine Schlüsselwörter enthält, a #NUM! Fehler wird zurückgegeben.
- Bei der Formel wird die Groß-/Kleinschreibung nicht beachtet. Um eine Übereinstimmung mit Groß-/Kleinschreibung durchzuführen, können Sie die SUCHEN-Funktion einfach durch ersetzen FINDEN.
Verwandte Funktionen
Die Excel-INDEX-Funktion gibt den angezeigten Wert basierend auf einer bestimmten Position aus einem Bereich oder einem Array zurück.
Die Excel MATCH-Funktion sucht in einem Zellbereich nach einem bestimmten Wert und gibt die relative Position des Werts zurück.
In Excel kann Ihnen die SEARCH-Funktion dabei helfen, die Position eines bestimmten Zeichens oder Teilstrings aus der angegebenen Textzeichenfolge zu ermitteln, wie im folgenden Screenshot gezeigt. In diesem Tutorial werde ich die Verwendung dieser SEARCH-Funktion in Excel vorstellen.
Die Excel-Funktion AGGREGATE gibt ein Aggregat von Berechnungen wie SUM, COUNT, SMALLL usw. mit der Option zurück, Fehler und ausgeblendete Zeilen zu ignorieren.
Verwandte Formeln
Rufen Sie den ersten Listenwert aus einer Zelle ab
Um das erste Schlüsselwort, das in einem Bereich aufgelistet ist, aus einer bestimmten Zelle abzurufen, während die Zelle einen von mehreren Werten enthält, müssen Sie eine ziemlich komplexe Matrixformel mit den Funktionen INDEX, MATCH, ISNUMBER und SEARCH verwenden.
Exakte Übereinstimmung mit INDEX und MATCH
Wenn Sie die in Excel aufgelisteten Informationen zu einem bestimmten Produkt, Film oder einer Person usw. herausfinden möchten, sollten Sie die Kombination aus INDEX- und MATCH-Funktionen sinnvoll nutzen.
Überprüfen Sie, ob eine Zelle einen bestimmten Text enthält
Hier enthält das Lernprogramm einige Formeln, mit denen Sie überprüfen können, ob eine Zelle einen bestimmten Text enthält, und TRUE und FALSE zurückgeben (siehe Abbildung unten). Außerdem werden die Argumente und die Funktionsweise der Formeln erläutert.
Überprüfen Sie, ob die Zelle viele Dinge enthält
Angenommen, in Excel befindet sich in Spalte E eine Liste mit Werten. Sie möchten überprüfen, ob die Zellen in Spalte B alle Werte in Spalte E enthalten, und TRUE oder FALSE zurückgeben (siehe Abbildung unten). Hier in diesem Tutorial finden Sie eine Formel zur Lösung dieser Aufgabe.
Überprüfen Sie, ob die Zelle eines von vielen Dingen enthält
Dieses Lernprogramm enthält eine Formel zum Überprüfen, ob eine Zelle einen von mehreren Werten in Excel enthält, und erläutert die Argumente in der Formel und die Funktionsweise der Formel.
Die besten Tools für die Office-Produktivität
Kutools für Excel - Hilft Ihnen, sich von der Masse abzuheben
Kutools für Excel bietet über 300 Funktionen, Stellen Sie sicher, dass das, was Sie brauchen, nur einen Klick entfernt ist ...
Office-Registerkarte - Aktivieren Sie das Lesen und Bearbeiten von Registerkarten in Microsoft Office (einschließlich Excel).
- Eine Sekunde, um zwischen Dutzenden offener Dokumente zu wechseln!
- Reduzieren Sie jeden Tag Hunderte von Mausklicks für Sie und verabschieden Sie sich von der Maushand.
- Erhöht Ihre Produktivität um 50 % beim Anzeigen und Bearbeiten mehrerer Dokumente.
- Bringt effiziente Tabs in Office (einschließlich Excel), genau wie Chrome, Edge und Firefox.