VLOOKUP関数の弱点克服！ リスト内に同姓同名の人がいるときに正しく区別する方法

リスト内で同姓同名の人がいるときに正確な値を出す方法

おさ氏：こんにちは、みなさん。「おさとエクセル」のおさです。今日は、VLOOKUP関数、第9弾をやっていきましょう。

本日のテーマは、VLOOKUP関数で引っ張ってくるデータ範囲のなかに、例えば、複数同姓同名の人がいるとしましょう。このときに、例えばタナカコウヘイさんが3人いるとして。

1番目の田中さんはお寿司が好きです。2番目の田中さんは焼き肉が好きです。3番目の田中さんは天ぷらが好きですと、それぞれ個人なので、好きなものが違うんですよ。

でも、VLOOKUP関数の第1引数で検索してタナカコウヘイさんというデータを指定してしまうと、どれを引っ張ってきたらいいかわからないですよね、Excel的には。

なので、こういったときに僕らはどういうふうにExcelを使いこなせば、関連するデータを正しく抽出してくれるかということを今日は見ていきます。

では、さっそくワークシートに移ってやっていきましょう。よろしくお願いいたします。

第2引数の範囲のなかに、複数個同じものが存在すると？

こちらのワークシートをご覧ください。

今日は、VLOOKUP関数の第1引数、検索値として指定した文字列が、VLOOKUP関数の第2引数の範囲のなかに複数個同じものが存在するときに、どういった現象が表れるのかというのを、まず見ていきましょう。

そのなかで、VLOOKUP関数の弱点があります。その弱点をまず見て、どうやったらそれを克服できるのかというものを、最終的には見ていきます。よろしくお願いします。

まず、下のほうを見ていきましょう。

第2引数に指定したい文字はこちら（名前）になるわけなんですが、名前と好きな食べ物が書かれていますね。しかし、名前のなかには、同一の値が複数個示されています。長内さんという人が4名いて、田中さんが3名いますね。これ、合計7名違う人がいるんですが、Excelのなかでは別の人ですよ、という判断がなかなかつきにくいという弱点が、実はあるんです。

では、ここ（上の名前セル）に「長内」さんとしておいて、いつも通りにVLOOKUP関数で指定していきます。第1引数は長内さん「$B$3」、第2引数はこちら（下のテーブル）を絶対参照で範囲指定の2列目、好きな食べ物を返してほしいので、完全一致で0として、そしてENTERを押してあげると。

はい、返ってきました。

焼き肉が好きな長内さんが返ってきました。これは、4名いる長内さんのうち、一番上の長内さんのデータを引っ張っていることになります。

VLOOKUP関数の弱点とは？

こういった感じで、VLOOKUP関数は、第2引数の範囲のなかに複数個同じものがあるとき、一番上のデータしか引っ張ってこないという弱点があります。ですので、ここ（上の名前セル）を田中さんに変えてみましょう。

田中と日本語で打って下のほうを見ていくと、田中さんのなかで一番上にあるのは和風サラダ好きな田中さんですので、ここ（上の好きな食べ物セル）には和風サラダと帰ってくるわけですね。

カレー好きな田中さん、焼き鳥好きな田中さんは完全に無視されてしまうわけです。

どうすれば2つ目以降に出てくるデータを、VLOOKUP関数で抽出できるのかということを、頭を使ってひねり出すんですよ。そこで考えられるのが、それぞれに違うIDを付与してあげましょうというのが解決策です。

具体的には、一番上に出てくる長内さんだったら「長内1」、2番目だったら「長内2」、「長内3」みたいな感じで、1つのデータにプラスアルファして作ってあげちゃうわけです。このオレンジで示されている（名前の左の）列にね。

これを作るために、フォーマットを整えていきましょう。

ここ（名前と好きな食べ物の間）に新しい列を挿入して、この列（上の名前と好きな食べ物の間にあるセル）は消します。それで、（新しく挿入した列は）IDと。イメージとしては、こういう（順番に番号を振る）イメージですね。1、2、3……と。

あとで、（IDの）1、2、3を関数でやる方法をご紹介します。今はイメージを掴んでください。

「＆」で結んであげるのが大事なポイント

これを「＆」で結んであげると、 長内1というのが出てきます。

これを下にコピペしてあげましょう。形式を選択して貼り付けで、数式を選んであげると長内1、2、3、4、田中1、2、3というデータが作れました。

こうすることによって、別々のデータとExcelが解釈することができます。

では、やってみましょう。ここ（上の名前セル）に新たなリストを作ります。リストの作り方は以前、勉強しました。

データのなかのツールグループで、データの入力規則。

これ、ショートカットもWindowsだとあるんですけど、Macだとやりにくいので、こんな感じで手動でやっていきます。

これで作ってあげることができました。こういう感じで選ぶことができます。

VLOOKUP関数で対応する方法

こちら（好きな食べ物セル）のVLOOKUP関数を適切に整えていきましょう。まずデータ範囲を間違えているので、ここの範囲を変えてあげます。

こっち（左に新たにつくったオレンジの欄）まで引っ張って…………これでいいですね。今度は好きな食べ物は、4列目に属しているので4に数字を変えてあげます。

そうすると、長内4というデータを持つ人はピザが好きですよ、と返してくれます。

ほかのも選んでみましょう。（下の表では）田中2という人はカレー好きですね。

（上でも）田中2はカレー好きですよ、と返ってきてます。

こういった感じで、新しい作業列と呼ばれるものを作ってあげることによって、同一の値を持つ文字列とかが複数個データ範囲のなかにある場合にも、VLOOKUP関数で対応できるようになります。これをしっかり覚えておきましょう。

関数で連番を振るには

さらに、ここ（ID欄）手動でIDを振っていったんですが、関数でこういった数値を出せるスキルを身に着けておくと、とても便利です。最後にその使い方だけ教えて終わりにしましょう。

COUNTIF関数というものを用います。これ以前勉強してますので、そちらの動画もチェックしてみてください。指定するものは2つです。第１引数は範囲、第2引数は検索条件ですね。

ここで肝になるのは、範囲指定のほうです。ここで、さらに絶対参照と相対参照の違いを理解していると、一発でわかると思います。

B6を指定したときに、ここを絶対参照にしてあげるんです。さらにコロン（：）。今度は相対参照のB6を指定してあげます。

関数ですので、コピペするだけでデータ範囲が変わっていくように指定してあげたというわけです。ここでコンマ（，）で第2引数、検索条件を指定してあげます。検索条件として、これもまたB6なんです。

絶対参照B6からB6、相対参照B6のなかから見つけてきてね、と指定をしました。

これをカッコで閉じてENTERを押すと、1という値が返ってきたわけです。数式をコピペしてあげると、（長内）1、2、3、4、（田中）1、2、3とデータが返ってきました。

これで、ここの関数を見てみますと、COUNTIF関数がコピペされて、ここの選択範囲がいい感じで、1つずつズレてきているわけなんです。B9も検索条件として指定してくれていますね。

こういった感じで、絶対参照、相対参照の使いこなしをしっかり覚えておくと、COUNTIF関数が便利な感じになってきますね。下のほうも見ていきましょう。

田中さんの場合も同様です。B6からB12のなかで、B12の田中さんというデータが何件あるかを探してくれています。この範囲のなかだと、3件ございますので、3という値が返ってきているわけですね。

こういう感じで、COUNTIF関数とVLOOKUP関数を組み合わせて、かつ、こちら（オレンジ色の欄）の「＆」で結んだ作業列を設けてあげると、こういったことにも対応できるようになるというわけです。複雑だったかもしれないんですけども。何回も見直してやってみてください。

みなさん、お疲れ様でした。今日はVLOOKUP関数の検索値に指定した固有名詞が、データ範囲のなかに複数個表れる場合に、どう対応したら、それぞれにリンク、対応させて自分の抽出したいデータを引っ張ってこれるかというものを学習していきました。

具体的な解決策は、新しい作業列を設けて、被ってる名前＋ID番号を「＆」で結んであげるということをしたわけです。そうすることで、新しい検索値を指定することができるようになります。また、データ範囲や列範囲などVLOOKUP関数をいじってあげることで、求めたい値を引っ張ってくることができるようになりました。

VLOOKUP関数も9回目になると、細かい細かいところまで進んできました。でも、もうすぐ終わります。ここまでやれたら十分です。来週も頑張っていきましょう。みなさん、お仕事お疲れ様です。また来週もよろしくお願いします。ではでは、バイバイ。おさでした。