VLOOKUP関数のしくみ
はじめに
VLOOKUP関数の習得は、Excelの習熟をはかる一つの目安として語られる事があるように思いますが、それはおそらく、そのしくみがそこそこ複雑なのと、実務で多用される可能性があるから、なのでしょう。
何番煎じか判りませんが、この記事では、VLOOKUP関数のしくみについて解説します。単に関数の構造を見るだけで無く、そもそもどういう事をしようとしているか、から説明しますので、ちょっと長たらしいですが、ご了承ください。
注意事項
この記事では、Google Sheetsを使用しています。基本的な関数の構造はMicrosoft Excelと同じなので、そのまま役立ちます。
入力規則でリストを使っていますが、記事は入力規則の解説では無いので、ここでは省きます。
エラー処理は応用的な話なので省きます。
そもそも何をやりたいか
この関数はこういうしくみです、と説明する前に、そもそもどういう時にどんな事がやりたいか、を考えておく必要があります。そうで無くては、こんなの覚えてどうするのだろうとなって、しくみを理解するモチベーションに繋がりません。
ある回転寿司好きの人は、このように、店で食べた寿司について、家族が食べた分の料金を記録しています。食べた人、ネタ、一皿の単価と皿数、金額が横に並んでいます。金額は、$${単価 \times 皿数}$$です。
この表では、単価を手入力しています。うには290円、中トロは210円、というようにです。こういう表は、次のような特徴があります。
違う人が同じネタを選ぶ事がある
同じ単価のものが複数ある
このような時、いちいち単価を入力していくと、同じ価格のものはあるし、ネタは重複するので繰り返して入力する必要があって、とても面倒ですし、手入力だから、書き間違いも起こります。210と打とうとして201となってしまうかも知れません。皿数は、食べに行った時に誰が何皿取るか判らないので、その都度入れるしかありませんが、単価はしょっちゅうは変わりませんので、ネタを入力したら勝手に単価が出てくるしくみがあれば嬉しいです。
上の図をご覧ください。右側に、ネタと単価をセットにした表を用意しました。左側の表から右側の表を利用して、自動的に単価が引き出されるしくみが欲しいのです。どうすれば良いでしょうか。
まず、右側の表で、左端の所を縦に見ていきます。Aさんは、うにを食べたので、上からネタを見て行き、一番最後になったら、うにが見つかります。それが見つかったら、横にある、目的の単価が発見できるという寸法です。このように、別の場所にある表から目的のものを見つけ出すような処理を、ルックアップと言います。これを使えば、いちいち単価を手入力しなくても、ネタを入れるだけで自動的に表示させられます。
VLOOKUP関数
ある表を一つの方向に見て行って目的のものを見つけ出すのをルックアップと言うのでした。多くの表計算ソフトには、それを実現する機能が搭載されています。VLOOKUP関数もその一つです。
ここでおさらいです。関数というのは、
材料を渡して
何らかの処理をして
結果を返してもらう
機能の事です。最もシンプルで、よく使う有名な関数であるSUM関数の場合、
複数の範囲を渡して
その範囲にある数値を合計して
合計を返してもらう
という機能を持った関数です。では、VLOOKUP関数のしくみを見てみましょう。
=VLOOKUP(見つけたいもの, 見つけに行く範囲, 何番目の縦並びから取ってくるか, どのように見つけ出すか)Aさんが食べた、うにの単価を探し出す処理で考えましょう。材料は具体的に次のようになります。
見つけたいもの（"うに"）：B4
見つけに行く範囲：右側の表のG4:H11
何番目の縦並びから取ってくるか：2番目
どのように見つけ出すか：完全に一致させる
この材料をVLOOKUP関数に渡す事で、目的の単価を返してくれるのを期待します。実際の数式は下記の通りです。
=VLOOKUP(B4,$G$4:$H$11,2,0)処理を具体的に示すと、
B4にある文字列の"うに"を
$G$4:$H$11の表に見に行き
左端の縦と2番めの縦を下に探して行って
"うに"と全く同じものが見つかったら
一緒に見ている2番めのほうにある290を返す
こうです。実際に書いてみると、下のようになります。
上の、数式を入れる部分に実際にVLOOKUP関数を入れました。単価には290が入っていますね。これは、右側の表からルックアップして単価を返してくれた、という事です。
見つけたいもの
=VLOOKUP(B4,$G$4:$H$11,2,0)数式を詳しく検討します。
見つけたいものは"うに"ですが、"うに"と入れると、単価をいちいち入力するのと同じ事をしていて意味がありませんので、隣にあるB4に入っている値を指定しています。このように、見つけたいものは、直接入れても範囲を指定しても構いません。もっとも、よくあるシナリオは、場所をずらして行って探すものなので、直接に文字列や数値を指定する事は少ないです。
見つけに行く範囲
見に行く範囲は$G$4:$H$11です。$がついていますが、これは、場所をずらさないという意味です。最初の材料には、うにが入っているB4を指定しています。これをコピーするなどして下の数式に入れると、B5、B6…と、自動的にずらしてくれます。これはとても便利なのですが、見つけに行く範囲でG4:H11とやってしまうと、
このようになってしまいます。#N/Aとは該当なしを意味します。でもおかしいですね。右の表にはちゃんと、サーモンも中トロもあります。こうなった原因は、
ここです。別な所を見に行くのを参照と言いますが、参照して欲しいのはG4:H11なのに、G5:H12になってしまっているではありませんか。
先ほど書いたように、範囲の参照は、数式をコピーしたりする時に自動的にずらしてくれて便利なのですが、ずらして欲しく無い場合にも、ずれてしまいます。そういう所の判別はアプリケーション側ではやってくれないので、注意する必要があります。そして、この参照を固定するのが、$の役割という訳です。このように、参照を固定してずらさないようにするのを絶対参照と言います。じゃあ、ずらすのは何と言うでしょう。簡単ですね。相対参照と言います。
なお、Excelなどには、このような失敗を少なくし、数式を格段に読みやすくできる構造化参照なる強力な機能がありますが、それはまた、別のお話…。
何番目の縦並びから取ってくるか
=VLOOKUP(B4,$G$4:$H$11,2,0)材料：何番目の縦並びから取ってくるか は2です。いま考えているルックアップは縦に見て行きます。探し出したいのは単価なので、どこから取り出すかを指定する必要があります。見に行く範囲は、ネタと単価が左から並んでいる表だから、2番目から取り出すように指定する訳ですね。
ここでポイント。探すのはネタで取り出すのは単価なので、見に行く範囲は、ネタの縦並び、つまり$G$4:$G$11でも良さそうです。やってみましょう。
！ 見つかりません。なぜでしょう。これは、
材料として、取り出してくる縦並びも含めなくてはいけない
からです。実際、エラー#REF!の内容を見てみると…
エラーとして、
VLOOKUP は境界外の範囲を求めています。
と表示されています。どういう事かと言うと、見に行く範囲が1つの縦並びなのに、取り出して欲しい縦並びとして、2を指定しているから、関数が範囲外を見に行こうとして参照できないのを示しているのです。参照は英語でReferenceです。なるほど！
ここでのポイントは、
見に行く範囲には、取り出したい範囲が含まれなくてはならない
所です。
実務上だと、参照したい表の構成が変わる事があります。要するに、横に拡張される場合があります。その時、VLOOKUPで指定する範囲を変えないと、参照外になってエラーが発生します。結構あります。
ネタの範囲は指定しなくて良いの？
見に行く範囲は、表の内、ネタの範囲と単価の範囲を含む所を指定して、取り出す縦並びの範囲として2番目を指定しました。あれ？ 探す所そのものの縦並び、つまりネタの縦並びは指定してくて良いのでしょうか。
実はこれ、やらなくて良いと言うより、できないのです。つまり、"うに"を探す事で言えば、
左端の縦並びからしか探せない
のです。言いかたを変えると、探す所は左端にしか置けない、となります。
これでは柔軟性に欠けます。もちろん、それを常に左端に置くように綺麗にデザインすれば良いのですが、実務ではそうも行きません。業務上の要件が変わって、検討する項目が増えて行く場合もあります。自分が一から作るなら何とかなるかも知れませんが、他の人から引き継いだもので、柔軟に運用できない縛りもあったりします。その観点からは、結構困ったりするのです。
ここはVLOOKUPの欠点として挙げられる事があります。そして、それを解消するために別の方法を使ったり、新しい関数が生まれたりしたのですが、それはまた、別のお話…。
どのように見つけ出すか
=VLOOKUP(B4,$G$4:$H$11,2,0)一番最後の材料です。0が入っていますね。ここに入れる事ができるのは、
1：TRUE
0：FALSE
この2通りです。1とTRUEは同じ意味で、0とFALSEは同じ意味です。これらが意味するのは、
1：近いものを見つける
0：完全に同じものを見つける
このような事です。1はたとえば、数値を分類する時などに使います。
この例では、金額を、高さという観点で分類しています。0以上200未満は安い、200以上500未満はまあまあ…のように分類させています。この時、最後の材料は1にしています。これが0だと、
こうなります。0は完全一致を意味していますから、200以外は見つからないという結果を返しているのです。
なお、1を指定した場合、探す縦並び（今の例では範囲の所）は、アルファベットでも数値でも、小さい順に並んでいる必要があります。
実際のところ、あまりこういうシナリオは無く、使い勝手も良くないので、1が使われる事は少ないです。なにしろ、
FALSE = 完全一致。この値をおすすめします。
Googleもこう言っているくらいなのです。
縦
VLOOKUP関数は、縦にルックアップする関数でした。縦、つまり垂直方向は、英語でVerticalです。なるほど、VLOOKUPとは、Vertical-LOOKUPの意味だったのですね…！
まとめ
ここまで、VLOOKUP関数を使うようなシチュエーションと、関数のしくみを説明してきました。知っている人にとっては当たり前過ぎて、何を今更という内容でしょうけれど、知らないが知りたい人は、色々の実務に携わる人で次々に出てくる訳ですので、そういうかたに少しでも役立てて頂ければありがたいです。
あ、そうそう。回転寿司では、皿の色や柄と単価が対応していますね。じゃないと金額が簡単に計算できないので。でもさっきの右側の表では、違うネタでも同じ単価のものがいくつもあって重複しています。ですので、ちょっと工夫してみましょう。
このようにすれば、単価は3箇所入力するだけで済みます。工夫すれば、とても便利な関数ですね。
別の方法について
詳しいかたは、INDEX関数とMATCH関数を組み合わせたり、XLOOKUP関数を使えば良いのではないか、と感ずるかも知れません。そこについては、先日の記事で説明してあります。