VLOOKUP関数は、指定された範囲内のデータの 1 列目で指定したキーを垂直方向に検索し、同じ行内にある指定した列のセルの値を返す関数です。
VLOOKUPの使い方
サンプルデータを元に解説していきます。
例えばこのようなデータが「シート１」あるとします。
このデータから、「品種」の値が一致したらその品種の「単価」を表示する。
という動作をVLOOKUP関数を使って実現します。
完成イメージとしては、下図のようなものをイメージしています。
このシートはデータのあるシートとは別に、「シート２」に作っています。
「品種」のセルに品種名を入れると、単価のセルにその品種の単価が表示されるようにします。
ついでに、「個数」も入力することで、「個数」×「単価」の「価格」も自動的に表示される表にしています。
セルE2の計算式は、「=C2*D2」が入っています。
VLOOKUP関数の書式は以下の通りです
=VLOOKUP(検索キー,範囲,指数,並び替え済みか)
「単価」の部分に、一致した品種の金額を出したいので、関数はセルD2に入力します。
式を書式に合わせて書くと以下のようになります。
=VLOOKUP(B2,‘シート1’!B:C,2,FALSE)
「検索キー」は品種のセルに入力された値を参照したいので、同じ行のB2
「範囲」はデータの範囲なので、’シート1’B:C
この範囲を指定します。
検索キーが検索するのは、範囲で指定した一番左側の列です。
そのため、例えばA列から範囲指定してしまうと、商品コードと品種を比較してしまうので、エラーとなります。
今回は、品種と比較して単価を表示したいので、B～C列を範囲としています。
「指数」は左から何番目の値を出力したいか？という数値なので、「範囲」で指定した中の２列目ということで「２」となります。
「範囲」で指定した一番左側の列を１列目として数えます。
「並び替え済みか」の部分は、シート１のデータが検索の対象（ここでは品種）でソートされているか？を指定しています。今回はソートしていないので「FALSE」としています。
「並び替え済みか」の引数は、TRUE か FALSE の２択です。
未入力の場合はTRUE扱いとなります。検索範囲に同じ名称が複数ある場合は、最初に検索キーと一致した行が参照されます。
データの状態がソートされているか不明な場合は、FALSE とする方が良いです。
それでは実際にシート２のセルD2にVLOOKUP関数をしかけてみましょう。
＃N/Aのエラー値が出ていますね。
セルB2の値を検索キーとしていますので、値が無いとエラーとなります。
ちゃんと機能するか確認のために品種を入れてみましょう。
品種に「王林」と入れたら、単価が表示されましたね。
せっかくなので、個数も入れてみましょう。
個数×単価＝価格 としてちゃんと機能していますね。
他のD列のセルにもD2の式をコピーしましょう。
D2のセルを選択して、そのセルの右下にある●を下にドラッグすると、簡単にコピーできます。
セルB3,B4に「品種」が入っていないのでエラーが出ています。
機能はしているのを確認済みですので、このままでも問題ありませんが、見た目が美しくないので、ちょっとしたIF関数を使ったテクニックを紹介します。
IF関数を使ったVLOOKUPの応用
このままでは、エラーが表示されていて、いまいち美しくありません。
エラーの原因はB列の「品種」に値が入っていないこと。
なので、IF関数を使って、B列に値があるときだけ、VLOOKUP関数を実行するようにすれば、エラー表示を回避できます。
関数の書き方としてはこのようになります。
=IF(B2<>””,VLOOKUP(B2,’シート1′!B:C,2,FALSE),””)
IF関数の書き方は以下の通り。
=IF(論理式 , TRUE（真）の場合 , FALSE（偽）の場合)
IF関数の論理式でセルB2（品種の列）が空白じゃなかったら → B2<>””
TRUE（真）の処理【VLOOKUPの式】を実行する。
FALSE（偽）だったら（空白だったら）何も表示しない → ””
という式です。
こうすることで、下図のようにエラーが表示されなくなります。
これで、「品種」と「個数」を入力するだけで、自動的に「価格」が表示される仕組みが出来上がりました。
よくあるエラーの原因と対処法
VLOOKUP関数を利用していると、よくあるエラーをまとめておきます。
=VLOOKUP(検索キー,範囲,指数,並び替え済みか)
●「範囲」の指定が間違っている。
上述でも解説していますが、データの元となる範囲で指定した一番左側の列が検索キーと比較されます。
データ量が多いと、元となるデータの全てを範囲としてしまって、検索キーと合わなくなるエラーがよくおきます。
必ず「範囲」の一番左側の列に、検索キーと比較したいデータの列がくるように「範囲」の指定をしてください。
データの状態によっては、検索したい列が表示したい列より右側にあることもあります。
その場合は列ごとコピーして左側にもってくるなどして対応してください。
●検索したい元のデータが重複している
同じ名称のデータがある場合は、先に検索キーと一致したものが表示されます。
そのため、思っていた結果と違ってしまうことも・・・
検索の元となるデータは必ず重複の無いユニークなデータを利用してください。
それが難しい場合は、他の列のデータを結合するなどして、ユニークな値を作るか、重複したデータは削除する、ユニークな名称に変更するなどして対応する必要があります。
●コピーしたら「範囲」が相対的にずれているのに気づかない
セルに関数を作成したらドラッグなどしてコピーをすると思います。
縦にコピーする分には、あまり影響がないですが、横にコピーをするとしていした「範囲」が相対的にずれてしまいます。
それを避けるためにも、「範囲」の指定は”$”をつけて絶対参照にすることも覚えておくと良いです。
まとめ
スプレッドを利用していると、VLOOKUP関数はかなり使う関数の一つです。
この関数を覚えておくことで、今回のような、自動的に計算するような仕組みも作れます。
何かと応用の利く関数なので、積極的に使って、使い慣れておくと良いでしょう。
コメント