こんにちは。
今日はVLOOKUP関数の練習問題をアップしてみようと思います。
「VLOOKUP 練習問題」で検索してサイトを訪問してくれる方がいるようなので、問題を作ってみました♪
問題だけアップして、解答を載せるのを忘れていたので、解答のファイルを追加しています。
この記事の一番最後からダウンロードできますので、必要な方はご利用ください。
また、ダウンロードが不安な方のために、式も記載しておきます。
先日もエクセルのセミナーを担当していて、テキストにVLOOKUP関数が出てきました。
受講された皆さん、習った瞬間は理解できている様子でした。
でも少し時間が経ったり、問題が変わったりすると、急にわからなくなってしまいました。
これは誰が悪い訳でもなく、単に「慣れ」の問題です。
当サイトを見て勉強している方にも、VLOOKUP関数に慣れて欲しいなと思ったので、練習問題を作ってみました。
テキストの練習問題をちょっとやったくらいでは、いまいちピンと来ませんよね。
提供データをダウンロードできるようにしていますので、必要な方はご利用ください。
ダウンロードデータを利用される方は、下記「ダウンロード」をクリックしてご利用ください。「VLOOKUP練習問題①」というファイル名のエクセルデータが、各パソコンの[ダウンロード]フォルダに入ります。
エクセルデータを開いた際、「保護ビュー」が表示された場合は下の記事を参考にご対応ください。
※スマートフォンやタブレットからのダウンロードは操作保証致しかねますので、パソコンからご利用いただくようお願いします。
※提供データのご利用は自己責任でお願いします。
データの確認
それではまず、データの確認をしてみましょう。
シートが
- 第1四半期
- 第2四半期
- マスタ
と3つあります。
第1四半期シートを完成させよう
それでは第1四半期シートを完成させていきましょう。
連番を振る
VLOOKUP関数とは関係ありませんが、№欄に連続番号を振りましょう。
オートフィルを使うと簡単です。
VLOOKUP関数で担当者・販売店を表示する
次に、VLOOKUP関数を使って、担当者欄と販売店欄を完成させましょう。
まずは担当者欄からやっていきます。
VLOOKUP関数の手順
1. セルD4を選択する
2. VLOOKUP関数のボックスを出す
半角英数の状態（入力モードが[A]の状態）で
＝vl
と入力すると、上の図のようにVLOOKUP関数の候補が表示されます。
この状態で[Tab]キーを押すか、青い部分をマウスでダブルクリックするとVLOOKUP関数が確定されます。
いつも自分がやっている方法で構いません。
いつものやり方がやりにくいと感じている方や、あまりやり慣れていない方はご紹介している手順でやってみましょう。
VLOOKUPの文字が大文字になったら、
- [fx]をクリックする
- [Shift]+[F3]を選択する
のどちらかを使い、
[関数の引数]ボックスを表示しましょう。
3. 引数を設定する
【検索値】
検索値は、データのキーとなる値です。
今回でいうと、
「担当者コードを入力すると、担当者と店舗が自動的に表示されるようにしたい」
ので、担当者コードが検索値になります。
【範囲】
範囲は、データを探しに行く先の表です。
今回でいうと、マスタというシートに「担当者マスタ」という表があります。
ここに、担当者コード[501]を持って探しに行きます。
担当者マスタの表全体を選択し、絶対参照を設定しましょう。
VLOOKUP関数で範囲を指定する際は、「絶対参照」にしてください。
この後、下方向に式をコピーするので、表の範囲選択を絶対参照にしておかないと、結果が正しく表示されません。
【列番号】
今は、担当者について調べているので、担当者マスタの表を見て、左から何列目に担当者のデータがあるかを確認し、その数字を入力しましょう。
もし、何列目か確認し忘れた場合は、いったん「2」と入力して式を確定させてください。
あとでゆっくり確認して、正しい列数に修正すればOKです。
確認のためにシートをあちこち移動すると、すべて式の中に入ってしまうので、わからなくなったら適当な数字で式を確定させるようにしてください。
【検索方法】
テキストによっては、「検索方法は省略可能」と記載されている場合がありますが、いつも大丈夫というわけではありません。
マスタの状態によっては、正しい答えが返って来ない場合がありますので、入力するようにしたほうが間違いないです。
範囲として指定した表の「検索値」にあたる項目が、昇順になっていれば省略しても問題ありません。
「検索値部分が昇順かどうか」をわざわざ確認して、「検索方法」を省略したりしなかったりするのは手間がかかります。
今回は、検索値とまったく同じ数値を探したいので、「0」と入力してください。
検索方法に入力する数字は
- 完全一致であれば「0」
- 不完全一致であれば「1」
です。
不完全一致は、検索値とまったく同じ数字を探すのではなく、近い数字を探す時に使うもので、普段あまり使うことはありません。
4. 結果を確認する
引数がすべて埋まったら、「OK」を押して関数の引数ボックスを終了しましょう。
そして、出た答えを必ず確認してください。
5. オートフィルで5行目以降に式をコピーする
結果が間違いなければ、オートフィル機能を使って式を最終行までコピーしましょう。
販売店を表示する
同じ手順で、販売店も表示してみましょう。
担当者の式とほぼ同じですが、列番号のみ変わりますので注意してください。
販売単価を表示する
同じ手順で、販売単価を求めてみましょう。
販売単価の検索値・範囲
販売単価を求める際は、
- 検索値は「コード」
- 範囲は「商品マスタ」
に変わります。
それ以外は今までと同じですので、ゆっくり確認しながら式を入力してください。
売上額を計算する
販売単価まで表示できたら、数量と掛け算をして、売上額を計算しましょう。
第2四半期シートを完成させよう
第1四半期シートは出来上がったでしょうか？
流れが確認できたら、同じ手順で、第2四半期シートも練習してみてください。
問題の解答例
解答を確認したい方は、下記「ダウンロード」より解答ファイルをダウンロードしてご利用ください。
ファイルのダウンロードに抵抗がある方、スマホやタブレット等、ダウンロードできない環境の方は、式を記載しておきますのでご確認ください。
解答のダウンロード
練習問題をダウンロードした時と同じ要領で、下記よりダウンロードしてください。
「VLOOKUP練習問題①解答」というファイル名のデータが、各PCのダウンロードフォルダに入ります。
解答例の確認
解答例を記載しておきますので、問題を解き終わったらご確認ください。
全部の式を表示すると大変なので、上から5つだけ記載しておきます。
解答例、と書いているように、式の正解は1つではありません。色々な式の立て方がありますので、解答例と式が違う＝間違いとは限りません。
違う式を立てた、という方は、表示結果が同じかどうか確認してみましょう。
担当者の式
セルD4：=VLOOKUP(C4,マスタ!$B$3:$D$12,2,0)
セルD5：=VLOOKUP(C5,マスタ!$B$3:$D$12,2,0)
セルD6：=VLOOKUP(C6,マスタ!$B$3:$D$12,2,0)
セルD7：=VLOOKUP(C7,マスタ!$B$3:$D$12,2,0)
セルD8：=VLOOKUP(C8,マスタ!$B$3:$D$12,2,0)
販売店の式
セルE4：=VLOOKUP(C4,マスタ!$B$3:$D$12,3,0)
セルE5：=VLOOKUP(C5,マスタ!$B$3:$D$12,3,0)
セルE6：=VLOOKUP(C6,マスタ!$B$3:$D$12,3,0)
セルE7：=VLOOKUP(C7,マスタ!$B$3:$D$12,3,0)
セルE8：=VLOOKUP(C8,マスタ!$B$3:$D$12,3,0)
販売単価の式
セルG4：=VLOOKUP(F4,マスタ!$F$3:$G$8,2,0)
セルG5：=VLOOKUP(F5,マスタ!$F$3:$G$8,2,0)
セルG6：=VLOOKUP(F6,マスタ!$F$3:$G$8,2,0)
セルG7：=VLOOKUP(F7,マスタ!$F$3:$G$8,2,0)
セルG8：=VLOOKUP(F8,マスタ!$F$3:$G$8,2,0)
まとめ
VLOOKUP関数の練習問題はどうでしたか？
実務でのVLOOKUP関数は、このような形でマスタが別シートにある場合が多いので、この練習問題で慣れて貰えたらなと思って作ってみました。
VLOOKUP関数練習の応用編が出来ました。
この記事の問題がバッチリできるようになったら、ぜひこちらも覗いてみてください。
何度も練習していくうちに、スムーズに設定できるようになりますよ。
それではまた。
