別のシートの表から条件に合うデータを参照する方法(VLOOKUP関数)

おそらく多くの方が最初につまづくであろう「VLOOKUP関数」。
ですが、マスターすれば業務効率は劇的に変わる関数でもあります。

今回は、GoogleスプレッドシートにおけるVLOOKUP関数の基本から応用、実例まで解説します。

こんな人におすすめ!
  • 注文表に商品データをひもづけて、売上を計算したい。
  • 勤務時間のデータに時給テーブルをひもづけて、給与計算したい。
  • セルに顧客名を入力したら、その顧客の基本情報が自動で表示されるようになってほしい。

今日の公式

=VLOOKUP(検索キー, 範囲, 番号, [並び替え済み])
項目説明
検索キー検索する値、または検索する値が含まれるセルを指定します。
範囲検索対象の範囲です。範囲の先頭列で [検索キー] で指定したキーを検索します。
番号先頭列から数えて、何列目の値を返すか指定します。
並び替え済み先頭列が並び替え済みかどうかを指定します。
多くの場合では「FALSE」を指定すれば問題ありません。

VLOOKUP関数の解説

今回は売上データと商品リストを突合させて、商品名と単価を表示するケースで解説していきます。

1検索キーを指定する

まず最初に検索キーを指定します。

今回は、メニュー番号をキーとして検索を行うので、B列の同じ行にあるセルを指定します。

=VLOOKUP(B5, 範囲, 番号, [並び替え済み])

2検索範囲を指定する

次に検索範囲を指定します。

商品リストを見てみましょう。

1列目から順に

  • メニュー番号(A列)
  • カテゴリ(B列)
  • 商品名(C列)
  • 単価(D列)

の順にならんでいますね。

先の手順でメニュー番号を検索キーとして指定したので、A列が先頭列となるように範囲指定します。

=VLOOKUP(B5, ‘商品リスト’!A1:D17, 番号, [並び替え済み])

なお、このままでも関数は成立するのですが、このあとD5セルをコピーしてD6~D10セルにもペーストしていくかと思います。
すると、検索キーだけでなく検索範囲も同時に一行ずつずれていってしまうため、正しい結果が返ってきません。

それを防ぐため、「$」をつかった絶対参照で範囲を固定しておくことをおすすめします。

=VLOOKUP(B5, ‘商品リスト’!$A$1:$D$17, 番号, [並び替え済み])

ポイント!
  • あとでVLOOKUP関数のセルをコピペするときに備えて、絶対参照で範囲を固定しておく。

3表示したい列の行番号を指定する

検索キーと範囲を指定しました。

つまり「検索範囲のなかで、どの行を表示するか」までは指定したことになります。
しかし、「検索範囲のなかで、どの行の、左から何番目のセルを表示するか」まで指定しないと、結果を表示することはできません。

次はその「左から何番目?」を指定します。

商品リストでは商品名はC列にありましたね。
範囲の先頭行(A列)から数えて、3番目の列なので、「3」を指定します。

=VLOOKUP(B5, ‘商品リスト’!$A$1:$D$17, 3, [並び替え済み])

4検索方法(完全一致検索かどうか)を指定する

最後に「先頭行が並び替え済みかどうか」を指定します。
ちょっと難しいですが、完全一致検索の場合は「FALSE」、そうでない場合は「TRUE」と覚えておけば問題ありません。

ここは通常は「FALSE」を指定しておきましょう。

=VLOOKUP(B5, ‘商品リスト’!$A$1:$D$17, 3, FALSE)

商品名「ドン・ペリニヨン ロゼ」が表示されました。

残りのセルも同様にVLOOKUP関数を組むことで、商品名と単価を表示することができます。

VLOOKUP関数の使用例

VLOOKUP関数は、様々なシーンで使用される関数です。

ここでは、VLOOKUP関数の具体的な使用例をご紹介します。

1二つの表を突合する

先の解説でも紹介した、「注文リストと、商品リストを、商品番号で突合して、売上を計算する」などのようなケースです。

ほかにも

  • 勤務時間のデータと、時給テーブルを、社員番号で突合して、給与計算する。
  • ユーザーのアクセスログと、年齢や性別といった属性データを、USER_IDで突合して、属性ごとのアクセス解析をする。

といったケースがあります。

2表から特定の行を抜き出す

検索キーで指定したセルに、社員番号や商品番号を入力することで、関連する情報を別表から抜き出す方法です。

  • 取引先IDを入力することで、売上データから、自動で請求書をつくる。
  • 顧客IDを入力することで、顧客情報データからメールアドレスと名前を引き出し、メール文をつくる。

などのケースがあります。

手打ちしていた作業が自動化されるので、業務効率化ではよく多用する使い方です。

練習問題

練習問題を解いてみましょう。

※「ファイル」>「コピーを作成」でスプレッドシートをコピーすることで、編集可能になります。

おすすめの記事