Googleスプレッドで関数を使う(VLOOKUPで値を抽出)

Googleスプレッドで関数を使う(VLOOKUPで値を抽出)

VLOOKUP関数は、指定された範囲内のデータの 1 列目で指定したキーを垂直方向に検索し、同じ行内にある指定した列のセルの値を返す関数です。

VLOOKUPの使い方

サンプルデータを元に解説していきます。

例えばこのようなデータが「シート1」あるとします。

VLOOKUPの使い方

このデータから、「品種」の値が一致したらその品種の「単価」を表示する。

という動作をVLOOKUP関数を使って実現します。

完成イメージとしては、下図のようなものをイメージしています。

VLOOKUPの使い方

このシートはデータのあるシートとは別に、「シート2」に作っています。

「品種」のセルに品種名を入れると、単価のセルにその品種の単価が表示されるようにします。

ついでに、「個数」も入力することで、「個数」×「単価」の「価格」も自動的に表示される表にしています。

セルE2の計算式は、「=C2*D2」が入っています。

VLOOKUP関数の書式は以下の通りです

=VLOOKUP(検索キー,範囲,指数,並び替え済みか)

「単価」の部分に、一致した品種の金額を出したいので、関数はセルD2に入力します。

式を書式に合わせて書くと以下のようになります。

=VLOOKUP(B2,‘シート1’!B:C,2,FALSE)

「検索キー」は品種のセルに入力された値を参照したいので、同じ行のB2

「範囲」はデータの範囲なので、’シート1’B:C

VLOOKUPの使い方

この範囲を指定します。

重要

検索キーが検索するのは、範囲で指定した一番左側の列です。

そのため、例えばA列から範囲指定してしまうと、商品コードと品種を比較してしまうので、エラーとなります。

今回は、品種と比較して単価を表示したいので、B~C列を範囲としています。

「指数」は左から何番目の値を出力したいか?という数値なので、「範囲」で指定した中の2列目ということで「2」となります。

「範囲」で指定した一番左側の列を1列目として数えます。

「並び替え済みか」の部分は、シート1のデータが検索の対象(ここでは品種)でソートされているか?を指定しています。今回はソートしていないので「FALSE」としています。

ポイント

「並び替え済みか」の引数は、TRUE か FALSE の2択です。

未入力の場合はTRUE扱いとなります。検索範囲に同じ名称が複数ある場合は、最初に検索キーと一致した行が参照されます。

データの状態がソートされているか不明な場合は、FALSE とする方が良いです。

それでは実際にシート2のセルD2にVLOOKUP関数をしかけてみましょう。

VLOOKUPの使い方

#N/Aのエラー値が出ていますね。

セルB2の値を検索キーとしていますので、値が無いとエラーとなります。

ちゃんと機能するか確認のために品種を入れてみましょう。

VLOOKUPの使い方

品種に「王林」と入れたら、単価が表示されましたね。

せっかくなので、個数も入れてみましょう。

VLOOKUPの使い方

個数×単価=価格 としてちゃんと機能していますね。

他のD列のセルにもD2の式をコピーしましょう。

D2のセルを選択して、そのセルの右下にある●を下にドラッグすると、簡単にコピーできます。

VLOOKUPの使い方

セルB3,B4に「品種」が入っていないのでエラーが出ています。

機能はしているのを確認済みですので、このままでも問題ありませんが、見た目が美しくないので、ちょっとしたIF関数を使ったテクニックを紹介します。

IF関数を使ったVLOOKUPの応用

このままでは、エラーが表示されていて、いまいち美しくありません。

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(検索キー,範囲,指数,並び替え済みか)

●「範囲」の指定が間違っている。

上述でも解説していますが、データの元となる範囲で指定した一番左側の列が検索キーと比較されます。

データ量が多いと、元となるデータの全てを範囲としてしまって、検索キーと合わなくなるエラーがよくおきます。

必ず「範囲」の一番左側の列に、検索キーと比較したいデータの列がくるように「範囲」の指定をしてください。

データの状態によっては、検索したい列が表示したい列より右側にあることもあります。

その場合は列ごとコピーして左側にもってくるなどして対応してください。

●検索したい元のデータが重複している

同じ名称のデータがある場合は、先に検索キーと一致したものが表示されます。

そのため、思っていた結果と違ってしまうことも・・・

検索の元となるデータは必ず重複の無いユニークなデータを利用してください。

それが難しい場合は、他の列のデータを結合するなどして、ユニークな値を作るか、重複したデータは削除する、ユニークな名称に変更するなどして対応する必要があります。

●コピーしたら「範囲」が相対的にずれているのに気づかない

セルに関数を作成したらドラッグなどしてコピーをすると思います。

縦にコピーする分には、あまり影響がないですが、横にコピーをするとしていした「範囲」が相対的にずれてしまいます。

それを避けるためにも、「範囲」の指定は”$”をつけて絶対参照にすることも覚えておくと良いです。

まとめ

スプレッドを利用していると、VLOOKUP関数はかなり使う関数の一つです。

この関数を覚えておくことで、今回のような、自動的に計算するような仕組みも作れます。

何かと応用の利く関数なので、積極的に使って、使い慣れておくと良いでしょう。

コメント