☰

リストからセルがどのキーワードで始まるかを確認し、一致するキーワードを返します。

tag-icon • tag-icon microsoft-excel worksheet-function
リストからセルがどのキーワードで始まるかを確認し、一致するキーワードを返します。

私は製品リストを取得し、製品名の先頭から製造元を抽出しようとしています。すべての製品名は製造元で始まります。一部のアイテムの名前の本体に他の製造元が含まれているため、これは複雑です。アイテムが何で始まるかを確認する必要があります。私は 50,000 以上のアイテムと 3,000 以上の製造元を扱っています。これまでの式は次のとおりです。

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

これはうまくいく場合もありますが、うまくいかない場合もあります。たとえば、下のワークシートでは、行 2 と 3 は正しいのですが、行 4 は正しくありません。セル「  A4Mike's Fun Toys」(セルB4) の結果は「Mike's」であるはずですが、「Fun」と表示されます。

メーカー名を逆アルファベット順に並べたワークシート

(コピーして貼り付けることができるテキスト形式のデータは次のとおりです:

+---+---------+-----------------+---------------+
|   |    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         |
+---+---------+-----------------+---------------+

）

Cしかし、列（メーカー）の順序を変更すると、次のようになります 。

メーカー名を（通常の）アルファベット順にリストしたワークシート

4 行目は正解（「Mike's」）になりますが、2 行目は間違いです。

列 A には下まで数式が入っています。期待される結果:

A2 - Brown
A3 - Cat
A4 - Mike's

列の順序に関係なく数式を機能させるにはどうすればよいですか C?

答え1

ライアンマークの公式を理解していない読者のために、

  • 与えられた製品名における各メーカー名の位置を見つける。その結果は
    • 1製品名の先頭にメーカー名がある場合は、1st 文字)、
    • 製品名に他のメーカー名が表示される場合は、その番号が大きい（後から上位の番号で表示されるため）
    • #VALUE!製品名に製造元名が表示されていない場合のエラーコード。
  • 上記の各式を反転（1を割る）すると、
    • 1製品名の先頭にある製造元の名前（これが検索対象です）
    • 製品名に表示されている他のメーカー名については、正の数字を小さくする（1を1より大きい数字で割ると、比率が1未満になるため）。
    • #VALUE!製品名に製造元名が表示されていない場合のエラーコード。
  • 上記の をLOOKUP見つけるために使用します。1

例えば、セルA4（セル内の「Mike's Fun Toys」に対応B4）の場合、最初の画像では、

  • 1なぜなら、「マイクの」（C2）は「マイクの楽しいおもちゃ」で始まるからです。
  • 8C3なぜなら、「マイクの楽しいおもちゃ」の8番目の文字に「楽しい」（ ）が現れるからであり、
  • #VALUE!そして#VALUE!、「Cat」（C4）と「Brown」（C5）は「Mike's Fun Toys」には登場しないからです。

それを反転すると1、、  0.125( 1/8)、 #VALUE!およびになります#VALUE!。次に、その配列内で を検索します。が最初の結果であり、「Mike's」が列 の最初の名前である 1ため、これは「機能するはず」です。1C

問題は次のヘルプページで確認できますLOOKUP:

のために見上げる関数が正しく動作するには、検索するデータを昇順で並べ替える必要があります。

明らかに昇順でソートされていない1後に続きます。0.125

がLOOKUP示唆するように、これは を使って解くことができますMATCH。あなたが望む式は、あなたの式と同じ基本的なアプローチ（ただし、不必要な逆数を除く）を使用して、次のようになります。

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

の 3 番目の引数はMATCH“match_type” と呼ばれます。ここでは に設定しましたが0、これは がMATCHとまったく同じ配列の最初の要素を探し1、配列がソートされているとは想定しないことを意味します。

これは配列数式なので、 入力するときにCtrl+ Shift+を押す必要があります。Enter

関連情報