excel: wie kann ich identifizieren die Zeilen, die text enthalten Schlüsselwörter aus einer Liste von Schlüsselwörtern

Ich habe eine Spalte (nennen wir es A) von Daten, wobei jede Zelle enthält eine lange Reihe von Wörtern, z.B.:

COLUMN A 
HORNBACH BAUMARKT ETOY, ETOY 
ALIGRO, CHAVANNES-PR 
DIPL. ING. FUST AG,ETO, ETOY 
AGIP SUISSE SA 224, LAUSANNE 
AMAZON MEDIA EU, LUXEMBOURG 
MIGROS M EPALINGES, EPALINGES 
HORNBACH BAUMARKT ETOY, ETOY 
MANOR AG - 390, BASEL 
MANOR AG - 390, BASEL 
GLOBUS LAUSANNE, LAUSANNE

Ich habe auch eine andere Liste von Schlüsselwörtern in einer anderen Spalte (nennen wir es B) z.B.

COLUMN B 
MSFT 
Amazon 
Hornbach 
Jumbo 
OBI 
Lipo 
Ikea 
Coop 
Migros 
Casino

Dies ist, was ich tun möchte:

Für jedes Schlüsselwort K in Spalte B
Überprüfen Sie jede Zelle in der Col zu sehen, ob der Eintrag vorhanden ist, als ein sub-string
Wenn es funktioniert, dann geben Sie das Schlüsselwort K in eine angrenzende Zelle in Spalte C
Wenn nicht, dann lassen Sie die angrenzende Zelle in Spalte C unberührt
Wiederholen Sie für das nächste Schlüsselwort K

Sollte das Ergebnis sein:

COLUMN A ----------------------------------> COLUMN C 
HORNBACH BAUMARKT ETOY, ETOY --------------> Hornbach 
ALIGRO, CHAVANNES-PR 
DIPL. ING. FUST AG,ETO, ETOY 
AGIP SUISSE SA 224, LAUSANNE 
AMAZON MEDIA EU, LUXEMBOURG ---------------> Amazon 
MIGROS M EPALINGES, EPALINGES -------------> Migros 
HORNBACH BAUMARKT ETOY, ETOY --------------> Hornbach 
MANOR AG - 390, BASEL 
MANOR AG - 390, BASEL 
GLOBUS LAUSANNE, LAUSANNE

Ich kann sehen, wie dies zu tun ist mithilfe von VBA-Typ-Strukturen... aber sicherlich muss es einen Weg geben, dies zu tun mit eingebauten Excel-Funktionen - INDEX, HLOOKUP, SUCHEN... usw.

Habe ich versucht, aber nicht geschafft. Wenn jemand eine bessere Idee hat, lasst es mich bitte wissen.

Ist es möglich, mehrere Ergebnisse in einer Zelle, getrennt durch ein Zeichen? Beispiel: Ergebnis sein würde "Ikea|Lipo|Coop" in einer Zelle.

InformationsquelleAutor Kunjan | 2012-09-12

  1. 4

    Vorausgesetzt, nur 1 passendes Wort pro Zeile höchstens könnte man diese Formel in C1 nach unten kopiert

    =IFERROR(LOOKUP(2^15,SEARCH(B$1:B$10,A1),B$1:B$10),"")

    WENNFEHLER-Funktion ist in Excel 2007 oder spätere Versionen nur für frühere Excel-Versionen versuchen, diese änderung

    =LOOKUP("zzz",IF({1,0},"",LOOKUP(2^15,SEARCH(B$1:B$10,A1),B$1:B$10)))

    Wenn Sie möchten, um mehrere übereinstimmungen, die in separaten Zellen, können Sie mit diesem "array-Formel" in C1, bestätigt mit CTRL+SHIFT+ENTER und nach unten kopiert werden und über so weit wie Sie benötigen könnte (entsprechend der maximal möglichen entspricht)

    =IFERROR(INDEX($B$1:$B$10,SMALL(IF(COUNTIF($A1,"*"&$B$1:$B$10&"*"),ROW($B$1:$B$10)-ROW($B$1)+1),COLUMNS($C1:C1))),"")

    Wenn A1 enthält 3 Wörter auf der Liste, dann werden diese automatisch in C1, D1 und E1 und F1 usw. bleiben leer,

    Revidiert, da die pro-Kommentare:

    Den ersten Palette in der Formel (erste argument von INDEX), definiert den Bereich, aus dem das Ergebnis ist, so zu ändern, dass in Spalte Z ändern Sie einfach diesen Teil, d.h.

    =IFERROR(INDEX($Z$1:$Z$10,SMALL(IF(COUNTIF($A1,"*"&$B$1:$B$10&"*"),ROW($B$1:$B$10)-ROW($B$1)+1),COLUMNS($C1:C1))),"")

    Das ist die richtige Lösung.
    Nice one barry.
    Barry... ich danke Ihnen sehr. Die array-Formel, die arbeitet ein Genuss-obwohl mein Kopf spinnt noch versuchen zu verstehen, die der Algorithmus. Nicht sicher, was ein exponent in die 1. beiden Formeln (2^15) - ich denke, hier wird das caret-Zeichen bedeutet etwas anderes als erhöhen, um die macht. Ich habe eine zusätzliche Klärung, gibt es eine Möglichkeit zum ändern der array-Formel, so dass Sie abholen können ein Ergebnis aus einer entsprechenden Spalte z.B. Spalte Z enthält eine unterschiedliche keyword-Liste. In anderen Worten, überprüfen Sie die gegen die keyword-Liste in Spalte B, aber wählen Sie den Wert aus der übereinstimmenden Zeile in Spalte Z. Vielen Dank.
    Ja, 2^15 ist 2 hoch 15 = 32768, wie Sie hier verwendet, weil es ist garantiert größer als jede Zahl zurückgegeben, die von der SUCHE-Funktion (da die max Anzahl von Zeichen in einer Zelle 32767). Es könnte leicht sein "Bignum" z.B. 100000. Die Rückkehr aus einer anderen Spalte die erste Reihe in der Formel - überarbeitete ich meine Antwort entsprechend, dass
    Die SUCHE ist sicherlich nicht die einzige Funktion, die sich so verhält, der Art und Weise, können Sie ersetzen Sie alle 1 von den drei Argumenten, der SUCHE mit einem array oder Bereich und das Ergebnis wird ein array - Sie können auch das gleiche tun mit FINDEN, oder ERSETZEN, oder viele andere

    InformationsquelleAutor barry houdini

Schreibe einen Kommentar