VLOOKUP 関数
書式 ：VLOOKUP(検索値,範囲,列番号,検索の型)
機能 ：検索値を範囲の左端で探し、見つかった行の指定列を返します
解説 ：表引きするときに使います
VLOOKUP関数の基本
VLOOKUP関数は、今ではすっかりポピュラーな関数になりました。雑誌やマニュアル本の関数解説では、まず基本中の基本としてSUM関数、便利そうだけど実際は使う機会の少ないAVERAGE関数、MAX/MIN関数に続いて「これを使いこなせれば中級者」的な扱いでVLOOKUP関数が登場します。確かに、目の前に表示されているセルの合計や平均を計算するより、どこか別のセルに入力したデータを、指定した検索値をキーに表示できるのですから、何となく"パソコンを使っている"と実感するのかもしれません。
VLOOKUP関数を使うポイントは次の3つです。
まず、VLOOKUP関数の基本を理解しましょう。VLOOKUP関数の動作イメージは次の通りです。
実際の使用例は次の通りです。上のイメージ図と対応させながら見てください。
セルG5に入力したVLOOKUP関数は「=VLOOKUP(G1,A1:D5,3)」です。引数[検索の型]を指定していませんが、それは後で解説します。
引数[検索値]には、セルG1を指定しました。実際にデータが入力されている範囲は引数[範囲]に指定します。ここではセル範囲A1:D5です。次の引数[列番号]は、検索値が見つかった場合に結果として返す列の位置を指定します。引数[範囲]で指定したセル範囲のうち、検索に使用した一番左を 1 として数えます。ここでは3を指定しましたので、地名が入力されているC列を返します。
検索値が昇順に並んでいるケース
上の使用例は、データの左端に入力されている検索値が「A-001」「A-002」…と昇順に並んでいます。このようなケースで問題になるのは、検索値が見つからなかったときです。分かりやすく次の例で解説しましょう。
セルF2に入力した検索値を、セル範囲A2:D6の左端列で探して、見つかったら3列目のC列を返すという式です。VLOOKUP関数はセルF5に入れてあります。先ほどの例と大きな違いはありません。注意力に自信のない方でしたら、何も問題を感じないかもしれません。しかし、そこがVLOOKUP関数の恐ろしさなのです。
セルF5のVLOOKUP関数は、「地域」として「大阪」を返しています。検索値「1003」の「地域」は「大阪」だと言っているのです。はたしてそうでしょうか。表をよく見ると、検索値「1003」というのは表に存在しません。存在しない検索値を探しているのに、VLOOKUP関数は「見つかりました」という結果を返しています。
これがVLOOKUP関数の1つ目の特徴です。VLOOKUP関数は、検索値が見つからないとき、検索値を超えない範囲で最大の値を見つかったものとします。「1003」は存在しません。「1005」は「1003」を超えてしまいます。「1003」を超えないのは「1001」と「1002」だけです。この「1001」と「1002」の中で最も大きいのは「1002」です。そこでVLOOKUP関数は「1002」が見つかったものとして結果を返します。
不親切な！と思いますか？実はこの仕様は次のケースで役立つのです。下は料金を表引きするサンプルです。料金は重さによって異なります。100gまでが150円、100g超から200gまでが180円…となっています。
セルE2で指定した検索値(重さ)は「158」です。表引きに使う表の範囲はセル範囲A2:C5です。A列に入力されている数値から、検索値「158」を探します。ところが、A列に「158」は存在しません。そこでVLOOKUP関数は、「158」を超えない範囲で最大の値である「101」を見つかったものとします。結果として正しい料金を表引きできます。
VLOOKUP関数は非常に用途の広い関数です。このように、検索値が見つからないとき「超えない最大値」を返すという仕様は、ケースによってとても助かるのです。
なお、上の表は解説のために分かりやすく作りました。実際には、A列にユーザー定義書式で「g」を表示するなどの工夫が必要でしょう。ちなみに、上の表では302gより重い料金はすべて230円と表示されます。ここでは、VLOOKUP関数の特徴を理解してくれればいいです。
検索値がランダムに並んでいるケース
VLOOKUP関数で検索できるのは数値だけではありません。たとえば次のような使い方も可能です。
簡単な例ですが、ここでは「名前」を検索キーとして「住所」を表引きしています。図のように「鈴木」を検索した結果は正しく「東京」が返ります。では「山本」を検索キーとした場合はどうでしょう。結果はエラーです。これは、昇順に並んでいない左端列で検索キーを探したのが原因です。
このような文字列は、そもそも昇順に並んでいる方が希です。あるいは商品コードのように、昇順に並べることができないリストなどもあります。こんなとき、山田が見つからないから山田を超えない最大の値…などと言われても困ります。このように、表の左端がランダムに並んでいるケース、言い換えれば、結果として唯一の値を返して欲しいケースでは、VLOOKUP関数の「超えない最大値」機能を無効にしなければなりません。そこで使うのが4番目の引数[検索の型]です。
[検索の型]には TRUE または FALSE のどちらかを指定します。また、引数[検索の型]は省略することも可能です。省略すると TRUE を指定したものとみなされます。
[検索の型]を省略するか TRUE を指定すると、「超えない最大値」機能が有効になります。先の料金表などで便利ですね。
[検索の型]に FALSE を指定すると、「超えない最大値」機能は無効になります。左端列が昇順に並んでいなくてもかまいませんが、検索値が見つからないとエラーになります。
では、今までの例を使って、[検索の型]の動きを見てみましょう。
上図のように、[検索の型]に TRUE を指定するか省略すると、検索キーが見つからないとき「超えない最大値」が見つかったものとします。
[検索の型]に FALSE を指定すると、検索キーが見つからないときエラー(#N/A)を返します。
次に、昇順に並んでいないケースです。
さっきはエラーになった「山本」ですが、[検索の型]に FALSE を指定することで、「超えない最大値」機能が無効になり、そのものズバリを検索するようになります。
エラーを表示させない工夫
ここまで解説したように、VLOOKUP関数はエラーを表示しやすい関数です。特に注意したいのは、引数[検索値]に指定するセルが空欄の場合です。VLOOKUP関数はここでもエラーを表示します。
セルF5に入力したVLOOKUP関数に間違いはありません。しかしVLOOKUP関数は、引数[検索値]が空欄だとエラーになるのです。VLOOKUP関数だけでこのエラーを回避することはできません。
ではどうするかというと、IF関数でエラーの原因を判定して、エラーになりそうだったらVLOOKUP関数を計算しないようにします。入力する式と考え方は次の通りです。
VLOOKUP関数のエラー対策はもう1つあります。思い出してください。引数[検索の型]に FALSE を指定すると「超えない最大値」機能が無効になって、存在しない検索値を指定するとエラーになるのです。
同じように、このエラーもIF関数を組み合わせて回避します。IF関数の考え方は次のようになります。
ポイントはIF関数の判定です。どんなときに空欄を表示すればいいのでしょう。上の例で言えば「検索値「1003」がA列に存在しなかったら」となります。しかし、それをどうやって判定すればいいのでしょうか。まあ、COUNTIF関数やMATCH関数を使う方法も考えられますが、ここでは発想を変えて「VLOOKUP関数の結果がエラーだったら」をIF関数の判定にしてやります。
ある数式がエラーになるかどうかは、ISERROR関数で調べられます。ISERROR関数は、引数に指定した数式がエラーになる場合に TRUE を返します。
ずいぶん長い式になってしまいました。見ただけでクラクラする人もいるかもしれませんが、関数をマスターしようと思うのでしたら、がんばって理解してくださいね。
VLOOKUP関数は応用範囲の広い関数です。工夫次第でさまざまな用途に活用できます。本当はもっと書きたいことがあるのですが、長くなったのでこのへんにしておきます。