vlookup応用

Excel

【Excel関数】vlookup関数の使い方<応用編>

vlookup関数は是非使いこなしてほしい関数の一つ。全4パターンの応用編を紹介しますが、4つ目はマニア度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の参照範囲となるのです。
vlookup応用1-2

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

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

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

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

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

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

 STEP1.vlookupの1列目作成

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

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

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

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

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

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

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

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

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

 STEP1.1列目を作成

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

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

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

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

5.vlookup関連記事

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

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

スポンサーリンク
スポンサーリンク

関連記事

  1. 折れ線グラフ点線サムネ (2)

    Excel

    【Excel】実線の一部を点線に替える「折れ線グラフ」の作り方

    実績と将来予測をグラフで示したい場合など、一本の折れ線グラフの中を実線…

  2. Excel

    【Excel】年齢を年代表示にする方法

    年齢を年代に変換(29→20)して、さらに”20代”や”20~29歳”…

  3. Excel

    【Excel】重複を抽出して削除する方法(Countif 他全4種)

    "重複チェック"や"重複削除"の方法は、ひとつではありません。その方法…

  4. Excel

    【Excel関数】住所情報から都道府県を抽出する方法

    "東京都千代田区1-1-1"のように連結した住所情報から都道府県だけを…

  5. カレンダー

    Excel

    マクロを使わない自動罫線のExcelカレンダー(2018年度/2019年度)

    個人での予定管理から、プロジェクトのタスク管理まで、カレンダーはあらゆ…

  6. サムネイル折線

    Excel

    【Excel】折れ線グラフを塗りつぶして見やすくする作り方

    サムネイルのグラフのような、折れ線グラフを塗りつぶして効果的に魅せる方…

スポンサーリンク




特集

  1. 折れ線グラフ特集1
  2. 特集働き方改革サムネ
  3. SC特集サムネ

スポンサーリンク




仕事効率アップ系記事

  1. 折れ線グラフ点線サムネ (2)
  2. vlookupErrorサムネ
  3. Altキーサムネ
  4. illust_biz_filng
  5. 働き方改革-職場

スキルアップ系記事

  1. 白黒折線サムネ
  2. SCAMPER
  3. enphasis_top
  4. 円グラフ
  5. not_dateadd

スポンサーリンク




ピックアップ記事

  1. 管理職マネジャー
  2. プレミアムフライデー
  3. プレミアムフライデー
  4. 愚痴-サムネ
  5. ボスマネ-サムネ
  1. 特集働き方改革サムネ

    Column

    【特集】「働き方改革」を独り歩きさせない!「働き方改革」の実現に向けて
  2. SC特集サムネ

    PC

    【特集】ショートカットキーで仕事効率化!働き方改革の小さな一歩👣…
  3. 折れ線グラフ特集1

    PC

    【特集】"使い方"では足りない!「折れ線グラフ」の"使いこなし方"
PAGE TOP