こんにちは。福田泰裕です。
教師にとって、成績処理にEXCELは欠かせないソフトですね。
みなさんはEXCELが得意ですか？
EXCELといえば関数です。
SUM、AVERAGE、MAX、MIN、IFといった関数は多くの人が使うと思います。
しかし、これだけの関数しか知らないのはもったいない！
VLOOKUP関数を知らないなんて、大損してますよ！
この超有能なVLOOKUP関数を知れば、EXCELの世界が大きく広がります！
今回はVLOOKUP関数の機能をご紹介します。
最後まで読んでいただけると嬉しいです！
目次
VLOOKUP関数の4つの引数
VLOOKUP関数ができることは表検索で、表の左の列から目的の値を見つけることができる関数です。
4つの引数
VLOOKUP関数の引数は4つです。
1つずつ紹介します。
| 検索値 | 表の中から検索したい値。 |
| 範囲 | 検索して、値を取得したい範囲。 |
| 列番号 | 検索値を見つけたとき、何列目のデータを取得するのか。 |
| [検索方法] | 検索の方法。 true…近似一致。 false…完全一致。 （省略可能。省略した場合は「true」になる。） |
具体例を見ながら、解説していきます。
VLOOKUP関数の最も簡単な使い方の例
このような表を用意しました。
セルA10に番号を入力すると、その番号の生徒の名前と得点を表示するようにしてみましょう。
いま、セルA10には「1」と書かれています。
つまり、番号1の岩鬼正美くんの名前と、得点を表示させたいのです。
セルB10に、
=VLOOKUP(A10,A2:E6,2)
と入力してみましょう。
すると…👇
ちゃんと1番の「岩鬼 正美」と表示されました！感動！
=VLOOKUP(A10,A2:E6,2)
検索値の「1」を、検索範囲「A2:E6」の左の列から検索し、2列目にある「岩鬼 正美」を取得したのです。
セルC10に、国語の得点を表示させるにはどうしたら良いでしょう。
国語の得点は表の3列目に入っているので、
=VLOOKUP(A10,A2:E6,3)
と入力すれば良いですね。
数学のセルD10は =VLOOKUP(A10,A2:E6,4)
英語のセルE10は =VLOOKUP(A10,A2:E6,5)
と入力すれば、
このような表が完成します！
番号を変えてみて、動作するかチェック！
試しに、セルA10の番号を「1」から「2」に変えてみましょう。
このように、番号「2」の殿馬一人くんの名前と得点に表示が変われば大成功です！
VLOOKUP関数の検索範囲を固定する（絶対参照）
次に、この表を縦にコピーしてみましょう。
セルB10～E10を選択し、オートフィルを使って14行目までコピーすると…
選択したセルの右下の■をクリックしたまま、下へドラッグします。
すると…👇
このように、検索範囲が下にずれていますね。
これだと結果が思い通りに表示されない可能性があります。
そこで、VLOOKUP関数をオートフィルでコピーする前に、検索範囲を絶対参照で固定しましょう。
このように検索範囲を指定したら、キーボードの F4 を押します。
すると…
検索範囲が「A2:E6」から「$A$2:$E$6」に変わりました。
この「$」は絶対参照といって、オートフィルを使っても移動しないようになります。
名前のセルB10は =VLOOKUP(A10,$A$2:$E$6,2)
国語のセルC10は =VLOOKUP(A10,$A$2:$E$6,3)
数学のセルD10は =VLOOKUP(A10,$A$2:$E$6,4)
英語のセルE10は =VLOOKUP(A10,$A$2:$E$6,5)
と入力して、この4つのセルを同じようにオートフィルでコピーしてみましょう。
すると…
今度は検索範囲が下にずれることなく、ちゃんと残っています！
これなら検索がうまくいくでしょう。
VLOOKUP関数の検索値の探し方の特徴
VLOOKUP関数の検索値の探し方には特徴があります。
例えば、「1,2,3,4,5」の順をバラバラにしてみると…
このように、ちゃんと検索してくれません。
結果を見ると、12行目の「3 岩鬼 正美」以外は正確ではありません。
VLOOKUP関数の検索値の探し方には特徴があります。
① 上から下に探す。
② 検索値と一致すればその値を行を取得する。
③ 一致しない場合は、その検索値未満の最大値を探す。
この3つの特徴を理解すれば、なぜ上のような結果になったのか分かります。
それでは、1つずつ解説していきます。
検索の特徴をブロック塀で考える
私はVLOOKUP関数の検索を、ブロック塀を超えていくイメージで考えています。
検索される上の表をブロック塀で表すと、このようになります👇
そして、VLOOKUP関数の検索値の探し方の特徴をブロック塀に置き換えると…
① 左から右に探す。
② 高さが一致すれば、その値の行を取得する。
③ ブロック塀が越えられない場合は、その前の行を取得する。
この ③ の考え方が重要になってきます。
なぜ「 5 → 里中 」なのか
左から探していき、超えられるブロック塀は超えていきます。
つまり、「 3 」と「 2 」のブロック塀は飛び越えるのです。
そして、「② 一致したらその行を読み取る」ので、「 5 → 里中」となります。
なぜ「 2 → #N/A 」なのか
「 2 」で検索すると、エラーになります。
この理由も、ブロック塀で説明します。
上から探していき、越えられるブロック塀は超えていき…たいのですが、「 2 」で検索すると越えられるブロック塀がありません。
そのため、「ブロック塀を越えられなかった」ということで、エラーとなるのです。
なぜ「 3 → 岩鬼」なのか
この「 3 」が岩鬼となったのは、「② 一致したらその行を読み取る」という特徴からです。
なぜ「 4 → 殿馬」なのか
検索される表を見ると、4は山田太郎となるはずなのに、なぜか殿馬一人となっています。
これは、「 4 」で検索すると、「 3 」と「 2 」は越えたのに「 5 」を越えられなかったため、1つ前の「殿馬」の行を取得してしまったのです。
なぜ「 6 → 微笑」なのか
検索される表に「 6 」はありませんが、なぜか微笑三太郎が表示されています。
実はこのブロック塀には、最後に越えられない壁があると思ってください。
「 6 」で検索すると、すべての壁を越えてしまい、最後の越えられない壁を越えられず、その1つ前の「微笑」を取得してしまうのです。
VLOOKUP関数の秘訣①：検索範囲の左の列は昇順にする
このように、VLOOKUP関数は意図しない結果を返してくる関数でもあります。
そういった不安を解消する秘訣を2つご紹介します。
まず、検索範囲の左の列を昇順に並べるということです。
こうすると、ブロック塀はこのようになります。
これならば、ブロック塀を越えられずに手前で落ちてしまうことがなくなります。
しかしこの場合、5以上の値 (6とか10とか)で検索すると、すべて「5 微笑三太郎」となってしまいます。
VLOOKUP関数の秘訣②：検索方法をfalse(完全一致)にする
最後の手段として、検索方法をfalse(完全一致)にするという方法があります。
このfalse(完全一致)は、次のように検索してくれます。
① 上から下に探す。
② 検索値と一致すればその値を行を取得する。
③ 一致しない場合はエラーを返す。
③ が大きく変更されています。
ブロック塀を越えるか越えないかではなく、一致するか一致しないかのみを判定していきます。
この検索方法 false(完全一致)で表検索すると…
このように、うまく取得できます。
（「 6 」は存在しないのでエラーになります。）
実際にVLOOKUP関数を使うとき、この完全一致で検索することが多いです。
まとめ：VLOOKUP関数でEXCELの世界が変わる
いかがでしたでしょうか。
EXCELは便利なソフトですが、このVLOOKUP関数を使うことで見える世界が大きく変わります。
今回はVLOOKUP関数の機能の説明でした。
具体的な使い方は、こちらの記事をご覧ください👇
最後まで読んでいただき、ありがとうございました。
質問やご意見、ご感想などがあればコメント欄にお願いします👇
コメント