vlookup関数は是非使いこなしてほしい関数の一つ。全４パターンの応用編を紹介しますが、４つ目はマニア度MAXなので3つ目まで読んでもらえれば十分と思って紹介します。「知識を技術に」をテーマに、Excel関数の使いこなし方法を紹介します。vlookup関数はデータサイエンティストとしてのデータ加工でかなり多用してきました。新入社員でデータを扱う仕事に就くこととなった人は、是非マスターしてください。

1.引数の効率的な設定

vlookupを使う箇所が1列だけの場合には、それほど効果を発揮することはありませんが、次のセクションで紹介する複数列、複数シートを参照する場合に大きな効果となります。

【おすすめ度】★☆☆
【効率化】　　★☆☆
【マニア度】　☆☆☆

　1-1.[範囲]は列指定

2つ目の引数で指定する[範囲]の指定は列での指定がよいです。
○　vlooup(C5,データ!A:H,7,false）
×　vlooup(C5,データ!A1:H31,7,false）

通常は、範囲はデータを参照する範囲を行と列で指定すると思いますが、行を指定すると参照先のデータ件数が増えた場合に取り込みモレてしまいます。それを解消できるのが、範囲の指定を列指定にすることです。

このケースの場合、A:Hと列指定しておくと、32行目以降にデータが追加された場合でもvlookupの参照範囲となるのです。

　1-2.[列番号]はセル参照

3つ目の引数で指定する[列番号]は行固定でのセル参照にすることです。
○　vlooup($C5,データ!$A:$H,E$2,false）
×　vlooup($C5,データ!$A:$H,8,false）

3つ目の引数もセル参照で指定できます。以下の左表は、D列に目標額、E列に売上をそれぞれvlookup関数で右のデータシートから引用しています。このとき3つ目の引数を以下のようにセル参照にしておくと、vlookup関数の中身を操作せずにE2の値を設定するだけで足りてしまいます。

2.複数列に設定する場合

[1]で紹介したような引数の設定をすると、vlookup関数を複数列に設定する場合もとても効率的です。

【おすすめ度】★★☆
【効率化】　　★★☆
【マニア度】　★☆☆

　STEP1.vlookupの1列目作成

まずはvlooupの1列目を作成します。この時のポイントは前述の2つのポイントに加えて、1つ目の引数を列固定にしておくことです。こうすることで、次のステップのコピーの際に、参照先がずれることが無く効率的です。

　STEP2.１列目をコピーし2列目作成

いま作成した1列目をコピーします。前述の通りの設定ができていると、コピーして作成した新しい列（下の左表の矢印列）は、1列目と同じ値になっているこはずです。

　STEP3.セル参照の列番号を指定

コピーしたそのままでは列番号が同じなので、表示したいデータシートの列番号をセルに入力します。あとは項目名など必要な個所を修正して1列追加できました。

vlookup関数の引数を毎回設定しなおすのではなく、汎用的に使えるvlookup関数の設定方法を覚えておいて、必要なパラメーターの箇所だけを入力しなおすことで、効率的に使えるようになります。

3.複数シートを参照する場合

[2]で紹介した複数列にvlookup関数を使用する場合の発展形です。指定する範囲が別シートにあるデータを使用する場合、シート名の工夫と、一斉置換で飛躍的に効率アップできます。

【おすすめ度】★★★
【効率化】　　★★★
【マニア度】　★★★

　STEP1.1列目を作成

vlookupを使う1列目を作成します。この場合は、シート「属」の3列目を参照しています。

　STEP2.1列目をコピーし2列目を作成

複数列を参照する時と同じように、1列目をコピーし2列目を作成します。ここまでは、複数列に設定する場合と同じ。

　STEP3.コピーした2列目を一斉置換

作成した2列目のデータ範囲を選択し、シート名を一斉置換します。置換する場合は、ショートカットキー[Ctrl]+[H]がおすすめ。この例の場合は、シート名[属]を、シート名[実]に一斉置換しています。

【ポイント】シート名は短い漢字名称がおすすめ。シート名をアルファベット"A"、"AX"や英数"D1"などにしてしまうと、一斉置換する場合にに関数内部にセルの値を置換してしまう可能性があります。特におすすめのシート名は【漢字】1文字です。名が体を表すような漢字がよいでしょう。上記の例の場合は、属性のデータシート[属]と、実績のデータシート[実]と表現しています。

 

シート名を一斉置換できるとこのように参照先も一斉に変更できます。

　STEP4.タイトルや列番号を修正

必要に応じてタイトルや列番号を修正したら完成です。以下は、目標と実績を追加して、それらを用いて達成率を計算したものです。

4.[検索値]のキーを2つにする

ちょっと複雑な運用を行う場合、このやり方を知っておくと楽な場合があります。

通常、検索値に2つ以上のデータを設定する場合、vlookupではなくsumifs関数を使うことが多いですが、sumifs関数は文字通りsum関数なので数値を合計する場合にのみ使用します。参照先の値が合計できない文字列などで、複数条件に該当するデータを参照する場合はvlookupの複数条件設定で対応できます。が、マニア度はMAっXです＞＜；

この使い方は本当にマニアックＭＡＸなので、伝わる人にだけ伝わればいいので。よくわからない、興味がない人はスルーして最後にまで読み飛ばしてください。

【おすすめ度】★☆☆
【効率化】　　★★★★
【マニア度】　★★★★★

vlookupの1つめの引数で複数条件としたい項目を&で結合します。以下の場合は、[社員番号]と[売上年月]を結合しています。同様に[範囲]として設定するデータも[社員番号]と[売上年月]を結合した項目を用意しておきます。前処理としてはこれだけ。

vlookup関数の1つ目の引数を、[社員番号]&[販売年月]としているので、範囲の一番左の列も[社員番号]&[販売年月]を結合したデータとなるように指定します。

上記のシート[実]のように年月の経過によってデータレコードが増えていくよう場合でも、予めシート[表]にあるような実績管理表を準備しておくと、シート[実]のデータを貼り付けるだけでデータが連動してくれます。この工夫を取り入れられれば、毎月・毎日管理用の集計表をメンテしなくてよい運用にできるでしょう。

5.vlookup関連記事

vlookupを覚えるにはまずこれ。基本中の基本です。
◇【Excel関数】vlookup関数の基本。応用編の前にまずはコレ！

vlookup関数は、上手に設定できずエラーになってしまうことが多い関数としても名が高いです。エラーとなってしまうケースと解消法もあわせて見ておいてください。
◇【Excel】vlookupが使えない時の解決方法