【Excel】重複のあるデータでVLOOKUPを行う際の方法
こんにちは。
すごい改善の吉田です。
【関数名】
VLOOKUP関数
【書式】
=VLOOKUP(検索値,検索範囲,列数,0)
【機能】
検索範囲の一番左の列から検索値と同値のセルを探し、そのセルから第三引数で指定した列数目にあるセルの値を返す。
…という関数でした。
この関数の使用において、データの検索値が重複している場合、注意することが一点あります。
1．こちらのデータをコピーして、エクセルシートのA1セルに貼って下さい。
No,KEY,取引先名,担当者名,,取引先名,担当者名,No,KEY
,,アラバスタ王国,ネフェルタリ・コブラ,,アラバスタ王国
,,アラバスタ王国,ペル,,アラバスタ王国
,,アラバスタ王国,コーザ,,アラバスタ王国
,,ウォーターセブン,アイスバーグ,,ウォーターセブン
,,ウォーターセブン,パウリ―,,ウォーターセブン
,,ウォーターセブン,ロブ・ルッチ,,ウォーターセブン
,,ウォーターセブン,フランキー,,ウォーターセブン
,,バラティエ,ゼフ,,王下七武海
,,バラティエ,パティ,,王下七武海
,,バラティエ,カルネ,,王下七武海
,,王下七武海,ジュラキュール・ミホーク,,王下七武海
,,王下七武海,ドンキホーテ・ドフラミンゴ,,王下七武海
,,王下七武海,バーソロミュー・くま,,王下七武海
,,王下七武海,ボア・ハンコック,,王下七武海
,,王下七武海,サー・クロコダイル,,王下七武海
,,王下七武海,ジンベエ,,バラティエ
,,王下七武海,マーシャル・D・ティーチ,,バラティエ
,,王下七武海,ゲッコー・モリア,,バラティエ
2.そのまま、「データ」タブから「データ区切り」をクリックします。
3．「元のデータの形式」が「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」が選択されていることを確認し、「次へ」をクリック
4．「区切り文字」で「カンマ」にチェックを入れ、「完了」をクリック
これで、上記のデータがエクセルで使えるようになったと思います。
データをエクセルに取り込めたら、次のような状態になっていることを確認してください。
A1→No
B1→KEY
C列→取引先名
D列→担当者名
F列→取引先名
G1→担当者名
H1→No
I1→KEY
取引先名のデータが重複しているパターンで、かなり頻繁にご相談頂くパターンです。
このデータにおいて、セル範囲[C:D]のデータを元に、G列に取引先ごとの担当者名を入れたいという場合。
普通にVLOOKUP関数を使うだけではうまく行きません。
実際に入力してみます。
1．G1セルに次の式を入力
=VLOOKUP(F2,C:D,2,0)
2．これを19行目までコピー
すると、G列には、取引先ごとに全て同じ担当者名が入力されてしまいます。
アラバスタ王国の場合であれば、ネフェルタリ・コブラ、ペル、コーザの3人の担当者名を入力したいわけですが、すべて「ネフェルタリ・コブラになってしまっています。
このように、VLOOKUPの検索値に重複がある場合、この関数は最初に一致した検索値のセルを対象として処理します。
結果、G3セルもG4セルもG5セルも、同じ「アラバスタ王国」を検索値としていますので、検索範囲であるC列で最初に登場したC2セルを対象にVLOOKUPの処理を行い。「ネフェルタリ・コブラ」という値を返しているということになります。
この問題を解決するには、重複データがあるC列、F列のデータをそれぞれユニークの状態に加工する、つまり重複がない状態に加工する方法が考えられます。ここでは、作業列を追加して行います。
考え方としては、重複する取引先名データのそれぞれに固有の番号を振ることによって固有化していきます。
1.A2セルに次の式を入力し、19行目までコピー
=COUNTIF($C$2:C2,C2)
2.H2セルに次の式を入力し、19行目までコピー
=COUNTIF($F$2:F2,F2)
3.B2セルに次の式を入力し、19行目までコピー
=C2&A2
4.I2セルに次の式を入力し、19行目までコピー
=F2&H2
A列とH列のCOUNTIF関数で、重複する取引先名それぞれに固有の番号を振ってあげました。
次に、B列とI列でそれぞれの取引先名と番号を結合し、それぞれが固有のデータとなるように加工しました。
この上で、再度G列のVLOOKUP関数を入力し直します。
今度は検索値をI列で指定し、範囲を[B:C]に設定します。
G2セルに次の式を入力し、19行目までコピーします。
=VLOOKUP(I2,B:D,3,0)
今後は、取引先名ごとにそれぞれ個別の担当者名が入力されました。
検索値が重複しているデータがVLOOKUP関数を使用するには、COUNTIF関数で各データの番号（登場回数）を設定し、その番号と検索値を結合することによってできた新たな固有の検索値をKEYにしてVLOOKUP関数を使用することで正確な結果を得ることができるようになる、というこれはエクセルの機能というよりは発想のお話ですね。