どうも！シャチクショーです！
検索方法については
- 商品名と連番を ＆(アンパサンド) で繋ぎ、一意（ユニーク）な値を作る
- 連番を昇順で表示させる(カウントされている分だけ)
- INDEX MATCHで検索を行う
この3ステップで2番目以降の該当する値も検索が可能です。
下記で手順を説明致します。
ExcelのINDEX MATCH関数で複数該当の2番目以降を抽出する方法
INDEX MATCH関数やVLOOKUP関数では基本的には「上から検索して１番最初に一致した行のみ」しか検索することが出来ません。一工程で完結させようと思うとそれは難しいのですが、少し工程を加えれば2番目、3番目、4番目・・・と最初の行以外も好きなだけ検索して引っ張ってくることが可能です！いくつかの関数を駆使しながら、下記表のように2番目以降も検索が出来るやり方を解説していきます。 今回は重複した商品名の情報を元に値段を検索します。
商品名と連番を ＆(アンパサンド) で繋ぎ、一意（ユニーク）な値を作る
まず最初にやることは複数該当している値がある場合は、一意（ユニーク）な値を生成する必要があります。やり方としては商品名と 連番 を＆(アンパサンド)で繋ぎます。
番号の割り当てる方法としてはCOUNTIF関数を使います。
=COUNTIF（範囲,検索条件）
『E2』セルに「=B2&COUNTIF(B$1:B2,B2)」と記入します。
範囲を『B$1:B2』と先頭のセルを絶対参照にしているのは、『E2』セルで入力した式をオートフィルで下に引っ張った時に先頭のセルが固定されて選択範囲が行に合わせて広がるようにする為です。
一番下のセルを確認してみると『B$1:B13』と選択範囲が広がっているのが確認出来るかと思います。
また、番号が上から順にそれぞれの商品名の右側に連番が振られているのも確認できるかと思います。
『 $ 』絶対参照 を付けるショートカットキーはF4
連番を昇順で表示させる(カウントされている分だけ)
次はちょっと式が長くなりますが、『G4』セルに下記数式を記入します。
=IF(OR(G3=COUNTIF($B$2:$B$13,$G$3),G3=””,0=COUNTIF($B$2:$B$13,$G$3)),“”,ROW(A1))
ここでは新たにIF関数、OR関数、ROW関数を使用しています。
＝IF(論理式,[値が真の場合],[値が偽の場合]) /論理式の結果に応じて指定された値を返す/
＝OR(論理式1, 論理式2,…) /いずれかの引数がTRUEならTRUE。すべてがFALSEならFALSEを返す/
=ROW(参照) /参照の行番号を返す/
この式の中にはOR関数を使っているので論理式が3つあります。
- G3＝COUNTIF($B$2:$B$13,$G$3) →『B2からB13』の間に『G3（りんご）』カウントし、『G3』と同じだったら～
- G3＝””→『G3』セルが空欄だったら～
- 0=COUNTIF($B$2:$B$13,$G$3) → 『B2からB13』の間に『G3（りんご）』をカウントし、 『０』と同じだったら～
「”〇〇〇”」ダブルクォーテーションで囲われたものは文字列と認識されます。また「””」ダブルクォーテーションを2つ書くと空欄と認識されます。
この論理式でいずれかに該当した場合は、『””』空欄を返し(真の場合)、どれも該当しない場合は『A1』の行番号を返す (偽の場合) という内容になっています。
この3つの論理式を使うことでカウントした最大数より大きい番号や、カウントした結果が０だった場合にセルが空欄になるように書いています。
また、ROW関数を使う理由は昇順で連番を付けれるようにするため今回はROW関数を使用しました。
検査値を商品名と連番を＆(アンパサンド)で繋いで検索を行い、値段の値を取り出す
この後は取り出したい値(今回は値段)をよく使うINDEX MATCH関数を用いて取り出すだけです。知らない人もいるかと思いますが、検査値は＆(アンパサンド)を使うことも可能です。
『H4』セルに「=IFERROR(INDEX($D$2:$D$13,MATCH($G$3&G4,$E$2:$E$13,0)),””)」と記入します。
「$G$3&G4」は『りんご１』同じ意味になります。記入した数式を下の行まで入れたら完成です！
まとめ
INDEX MATCH関数で2番目以降の該当する値も全て検索する方法は以下の手順で可能です。
- 商品名と連番を ＆(アンパサンド) で繋ぎ、一意（ユニーク）な値を作る
- 連番を昇順で表示させる(カウントされている分だけ)
- 検査値を商品名と連番を＆(アンパサンド)で繋いで検索を行い、値段の値を取り出す
エクセルの関数は様々な関数を組み合わせることで、色んなことが出来まっす！これからも実践で使える応用技をお伝えしていけたらと思います。
VLOOKUP関数で2番目以降の該当する値も全て検索する方法は以下に貼っておきます。
コメント