☰

Überprüfen Sie, mit welchem ​​Schlüsselwort aus einer Liste eine Zelle beginnt, und geben Sie das passende Schlüsselwort zurück.

tag-icon • tag-icon microsoft-excel worksheet-function
Überprüfen Sie, mit welchem ​​Schlüsselwort aus einer Liste eine Zelle beginnt, und geben Sie das passende Schlüsselwort zurück.

Ich versuche, aus einer Liste von Produkten den Hersteller aus dem Anfang des Produktnamens zu extrahieren. Jeder Produktname beginnt mit dem Hersteller. Dies wird dadurch erschwert, dass einige Artikel andere Hersteller im Hauptteil des Namens haben; ich muss sehen, womit der Artikel beginnt. Ich beschäftige mich mit über 50.000 Artikeln und über 3.000 Herstellern. Die Formel, die ich bisher habe, lautet:

=LOOKUP(1,1/(FIND($C$2:$C$5,B2)),$C$2:$C$5)

Dies funktioniert manchmal, manchmal aber nicht. Im folgenden Arbeitsblatt sind beispielsweise die Zeilen 2 und 3 korrekt, Zeile 4 jedoch nicht. Das Ergebnis in der Zelle  A4für „Mikes lustige Spielzeuge“ (in Zelle B4) sollte „Mike’s“ sein, wird aber als „Fun“ angezeigt.

Arbeitsblatt mit Herstellern in umgekehrter alphabetischer Reihenfolge

(Hier sind die Daten im Textformat, die Sie kopieren und einfügen können:

+---+---------+-----------------+---------------+
|   |    A    |        B        |       C       |
+---+---------+-----------------+---------------+
| 1 | Formula | Items           | Manufacturers |
+---+---------+-----------------+---------------+
| 2 | Brown   | Brown Cat Toys  | Mike's        |
| 3 | Cat     | Cat Fun Toys    | Fun           |
| 4 | Fun     | Mike's Fun Toys | Cat           |
| 5 |         |                 | Brown         |
+---+---------+-----------------+---------------+

)

Aber wenn ich die Reihenfolge der Spalte  C(Hersteller) ändere:

Arbeitsblatt mit Herstellern in (normaler) alphabetischer Reihenfolge

Zeile 4 ist richtig („Mike‘s“), aber Zeile 2 ist falsch.

In Spalte A ist die Formel vollständig enthalten. Erwartete Ergebnisse:

A2 - Brown
A3 - Cat
A4 - Mike's

Wie kann ich erreichen, dass die Formel unabhängig von der Spaltenreihenfolge funktioniert  C?

Antwort1

Für Leser, die RyanMarks Formel nicht verstehen, ist es

  • Finden Sie die Position jedes Herstellernamens in einem gegebenen Produktnamen. Dies ergibt
    • ein Wert von 1für den Herstellernamen, der den Produktnamen beginnt (weil er am11. Zeichen),
    • höhere Nummern für Namen anderer Hersteller, die im Produktnamen vorkommen (weil sie später, an höheren Positionsnummern erscheinen) und
    • ein #VALUE!Fehlercode für Herstellernamen, die nicht im Produktnamen vorkommen.
  • Wenn man die obigen Gleichungen umkehrt (1 durch 1 dividiert), erhält man
    • 1für den Herstellernamen, der mit dem Produktnamen beginnt (und den wir suchen),
    • niedrigere positive Zahlen für Namen anderer Hersteller, die im Produktnamen vorkommen (denn 1 geteilt durch eine Zahl, die größer als 1 ist, ergibt ein Verhältnis, das kleiner als 1 ist) und
    • ein #VALUE!Fehlercode für Herstellernamen, die nicht im Produktnamen vorkommen.
  • Verwenden Sie es , um das Obige LOOKUPzu finden .1

Beispielsweise erhalten wir für die Zelle A4(entspricht „Mike's Fun Toys“ in Zelle B4) im ersten Bild in der Reihenfolge

  • 1, weil „Mike's“ ( C2) mit „Mike's Fun Toys“ beginnt,
  • 8, weil „Fun“ ( C3) als 8. Zeichen in „Mike's Fun Toys“ vorkommt und
  • #VALUE!und #VALUE!, weil „Cat“ ( C4) und „Brown“ ( C5) nicht in „Mike's Fun Toys“ vorkommen.

Das Invertieren ergibt 1,  0.125( 1/8)  #VALUE!und #VALUE!. Dann wird 1in diesem Array nach gesucht. Dies „sollte“ funktionieren, da 1das erste Ergebnis ist und „Mikes“ der erste Name in Spalte ist  C.

Das Problem wird auf der Hilfeseite für angezeigt LOOKUP:

Für dieNACHSCHLAGENDamit die Funktion ordnungsgemäß funktioniert, müssen die gesuchten Daten in aufsteigender Reihenfolge sortiert sein.

und eindeutig 1gefolgt von 0.125ist nicht aufsteigend sortiert.

Wie der LOOKUPvorschlägt, können wir dies mit lösen MATCH. Die gewünschte Formel, die denselben grundlegenden Ansatz wie Ihre Formel verwendet (außer ohne die Invertierung, die unnötig ist), lautet

=INDEX($C$2:$C$5, MATCH(1, FIND($C$2:$C$5,$B2), 0))

Das dritte Argument MATCHheißt „match_type“. Ich habe es 0hier auf gesetzt, was bedeutet, dass MATCHnach dem ersten Element im Array gesucht wird, das genau ist 1, und nicht davon ausgegangen wird, dass das Array sortiert ist.

Dies ist eine Array-Formel, Sie müssen also beim Eingeben Ctrl+ Shift+ drücken.Enter

verwandte Informationen