【スプレッドシート】VLOOKUP関数の基礎から応用まで
こんにちは、たま(@closuppo)です！
今回は、スプレッドシートの「VLOOKUP関数」の上級編をお伝えします。
初級編に続いてのマスター編となります。復習しながら今回の記事の内容を実践して、VLOOKUP関数の理解を深めましょう。
特定のセル範囲から、条件に一致する単語や数値を調べる方法について、詳しく解説していきます。
初級編も復習しましょう
※目次をクリックすると
タイトルの先頭まで移動します（↓）
まずはじめに
VLOOKUP(ブイルックアップ)関数とは参照関数の１つで、表の垂直方向にデータを検索し、一致した値と同じ行にある指定したセルの値を返します。
例えば、下図のように「りんご」の売上を下の表から抽出したい時などに使います。
サンプルシートを用意しているので
コピーしてご活用ください
はじめてのVLOOKUP関数
VLOOKUP関数の構文は下記の通りです。
VLOOKUP関数
=VLOOKUP(❶検索値, ❷検索範囲, ❸列番号, ❹[検索の型])
この数式に条件を指定することで必要なデータを返してくれます。
- ❹[検索の型]については、いったん何も考えず「0」と指定してOKです。
セルC3にVLOOKUP関数を入れて「りんご」の売上を返すまでを順番に入力してみましょう
結果を表示させたい販売個数合計のセルC3に=VLOOKUP( と入力
❶検索値
=VLOOKUP(B3, 検索範囲, 列番号, [検索方法])
❶検索値に何を探したいのかを指定します。
今回は「りんご」の売上を調べたいので、検索値に「りんご」と入力されているB3をクリックして指定します。
「 , (カンマ) 」を入力
検索値は範囲の左端のみ指定できます
検索値が検索範囲の左端にない場合は正しく検索できませんので、必ず左端に検索値を置くようにしましょう。
- 後の応用編で、左側を検索する方法も解説します。
検索値はテキストを直接入力することもできます！
数式の中で文字を指定するときは「 " (ダブルクォーテーション) 」で囲います。
直接文字を入力する場合は、「 " 」、りんご、「 " 」と順番に入力して文字を囲みます。
❷検索範囲
=VLOOKUP(B3,B6:E10, 列番号, [検索方法])
次に❷検索範囲を指定します。
「りんご」の「売上」を求めるために検索する範囲を指定します。この指定した範囲の中でしか検索はできないので、今回は「B6:E10」を指定します。
「 , (カンマ) 」を入力
実務では、絶対参照で検索範囲を固定することが多いです
下図の表は商品名を入力すると単価が別の価格表から反映されるようになっています。
このように数式を他のセルに複数コピペするときには検索範囲を固定しておく必要があります。
- 後の応用編でセルを固定する絶対参照について解説しますので、ぜひ読み進めてください。
❸列番号
=VLOOKUP(B3,B6:E10, 4, [検索方法])
次に❸列番号を指定します。
「りんご」を検索する列を1列目として 参照したいデータ列を指定 します。
1列目は商品名、2列目は単価、3列目は販売個数、4列目は売上となっていますね。
今回は「売上」を参照したいので4列目の「4」を指定します。
「 , (カンマ) 」を入力
❹検索の型
最後に❹検索の型を指定します。
=VLOOKUP(B3,B6:E10, 4, 0)
検索の型では、 完全一致で検索をする場合は「0もしくはFALSE」、近似一致で検索する場合は「1もしくはTRUE」を指定 します。VLOOKUPでは完全一致で検索するのがほとんどなので、基本的には「０」を入力しておけば問題ないでしょう。
実務で使用するのは、ほとんどが「0もしくはFALSE」です。
検索範囲B6:E10のりんごの売上は1500円と表示されました。
引数がたくさんあって、わかりづらい…
引数は❶検索値, ❷検索範囲, ❸列番号の3つを押さえていればOKです！
❹検索の型は0にしておけばいいんですね
VLOOKUP関数を日本語で読んでみましょう
C3のセル=
セルB3のりんごをセル範囲B6:E10から検索し、りんごが見つかったら4列目の値を参照する。
VLOOKUP関数応用編
ここからは、VLOOKUPの応用編として５つのテクニックを解説します。
VLOOKUP関数を様々なシーンで活用できるよう、応用編もマスターしましょう！
- 別シートからデータを参照する
- セルを固定
- 一つのセルでまとめて参照する
- 複数条件（セルを組み合わせる）
- 左側にある値を参照したい
順番に実践していきましょう
別シートからデータを参照する
VLOOKUP関数では、別シートからデータを参照することができます。
例えば、下図の表のように価格表を別シートに作成しておいて、検索範囲として参照することも可能です。
上の表では、「'価格表'!」が検索範囲の前についていますね。
別シートからデータを参照するとき
テキストで入力する場合 「 シート名 」を「 ' (シングルクォーテーション) 」で囲んで「 ! 」をくっつけて「 'シート名'! 」と入力する
直接シートを選択する場合 =VLOOKUP(❶検索値, まで入力し、シート名のタブをクリックして検索範囲を選択すると、「 'シート名'! 」と入力される
セルC3にVLOOKUP関数を入れて「メロン」の単価を返すまでを順番に入力してみましょう。
サンプルシートはシート2と価格表をお使いください
単価のセルC2に入力する数式はこうなります。
操作の手順はこちらです。
- シート2のセルC3に「 =VLOOKUP( 」と入力
- 検索値が入力されたセルB3 をクリック
- 「 , 」を入力
- 価格表シートのタブをクリックして開く
- 価格表シートのセル範囲B3:C12を選択
- 「 , 」を入力
- 2列目の2と入力
- 「 , 」を入力
- 0を入力
- 「 ) 」で閉じる
検索範囲価格表シート B3:C12のメロンの単価は1200円と表示されました。
日本語で読むように数式を書くと、こうなります。
C3のセル=
セルB3のメロンを価格表シートのセル範囲B3:C12から検索し、メロンが見つかったら2列目の値を参照する。
販売個数を入力したら売上が反映されるよう、売上のセルにも数式を入れておきましょう
売上のセルE3に、単価×販売個数の数式を入力しましょう。
×には算術演算子「 * 」を使って、C3*D3と入力します。
ためしに、販売個数に2と入力してみます。
単価×販売個数の売上金額が反映されました。
メロンの下にも商品名を追加したときに反映されるよう、数式をコピペしましょう。次の項目で説明します。
セルを固定
続いて、商品名に「バナナ」と入力すると単価が価格表から反映されるように、先ほどの項目で入力したセルC3の数式を1行下のセルC4にコピペしてみましょう。
上の図のように、数式をコピペすることで参照先のセル範囲が移動します。
セル範囲も1行下に移動されてしまうため、商品名セルC4の「バナナ」が検索範囲から外れてしまい、このままでは正しく集計することができません。
コピペをすると参照先がずれてしまうんですよね…
絶対参照を使って、参照先を固定しましょう
コピペすることで参照するセルが動かないよう固定することを絶対参照といいます。
セルの固定方法
固定したいセル番地の列番号(アルファベット)と行番号(数字)の左横にそれぞれ「 $(ドル記号) 」を付けることで、コピペしてもセルが常に固定されるようになります。
絶対参照で使用する「 $ (ドル記号) 」は固定を意味します
「 $ 」はキーボードのF4キーを使って一瞬で付けることができます
検索範囲を選択し、キーボードのF4キーを押すと選択したセル範囲の列番号と行番号すべてに「 $ 」が付きます。
F4キーを押す回数で「 $ 」の付く位置が変わり、参照方法も変わります。参照方法は他にもありますので、ぜひ参照についての記事もご覧ください。
実際に絶対参照を使って数式を入力してみましょう
単価のセルC3に入力する数式はこうなります。
操作手順はこちらです。
- シート2のセルC3に「 =VLOOKUP( 」と入力
- 検索値が入力されたセルB3 をクリック
- 「 , 」を入力
- 価格表シートのタブをクリックして開く
- 価格表シートのセル範囲B3:C12を選択
- 列番号と行番号の各左横に「 $ 」を入力
- 「 , 」を入力
- 2列目の2と入力
- 「 , 」を入力
- 0を入力
- 「 ) 」で閉じる
絶対参照で検索範囲を固定したセルC3の数式を1行下のセルC4にコピペしてみましょう。
セル範囲が固定され、価格表から正しく単価を反映することができました。
続けて数式をコピーして、商品名を追加すると単価が反映される表にしましょう
商品名のセルB5が空白のため、検索値が見つからずエラーになっています。
セルB5に商品名「りんご」と入力してみましょう。
正しく単価が反映され、表示されました。
販売個数を入力し、売上のセルE3に入力した数式を下の行へコピペしてみましょう。
販売個数を入力し、セルE3に入力した数式をセルE4、E5にコピペします。
検索範囲がない数式は固定せずそのままコピペできました
絶対参照を理解して使い分けると作業効率がアップしますね
一つのセルでまとめて参照する
一つ前の項目のように、入力したVLOOKUP関数を1つずつコピペすることもできますが、コピペが必要なセルが数十・数百とある場合には手間がかかってしまいます。
オートフィルを使って手早くコピペすることもできますが、大量にあるセルに1つずつ数式を入力するとデータが重くなってスプレッドシートの動きが遅くなるということにもなりかねません。
ARRAYFORMULA(アレイフォーミュラ)関数を使うと、1つのセルにだけ数式を入力して複数のセルに値を反映させることができます。
1つのセルに数式を入れて範囲を指定することで一斉にデータを返すことができますので、セルに1つずつ関数を入力する手間が省けるだけでなく、ブラウザへの負担も軽減されるのでスプレッドシートの表示速度が速くなるとても便利な関数です。
ARRAYFORMULA関数は、Googleスプレッドシートのオリジナル関数です
ARRAYFORMULA関数の構文はこちらです。
ARRAYFORMULA関数
＝ARRAYFORMULA（配列数式）
配列数式？
配列とは、複数のセルの集まりのことを指し、配列数式とは複数のセルを対象に1つの数式を作成する式です。
- 配列については後の項目でも解説しますので、ぜひ読み進めてください。
ARRAYFORMULA関数とVLOOKUP関数を組み合わせる時、VLOOKUP関数の検索値には必ず複数のセル(範囲)を指定します
単一セルの指定だと、繰り返し結果を表示できませんので、必ず複数のセルを指定しましょう。
それでは実践してみましょう。サンプルシートはシート3と価格表をお使いください。
単価のセルC3に入力する数式はこうなります。
操作手順はこちらです。
- シート2のセルC3に「 =ARRAYFORMULA( 」と入力
- VLOOKUPと入力
- 検索値が入力されたセル範囲B3:B12を選択
- 「 , 」を入力
- 価格表シートのタブをクリックして開く
- 価格表シートのセル範囲B3:C12を選択
- 「 , 」を入力
- 2列目の2と入力
- 「 , 」を入力
- 0を入力
- 「 ) 」で閉じる
正しく単価が反映され、コピペしなくても一気に下まで計算結果が表示されました。
簡単に言うと、数式を1つ入れるだけで、検索値として選択した複数のセル(範囲)は結果をくり返してくれるということです
複数条件（セルを組み合わせる）
ここまでの項目では、調べたい検索値がある場合で解説しましたが、調べたい項目が適切な検索値とは限りません。
下図の表ではエリアごとに定期購入・新規・リピーターの売上が記載されています。
関東の新規の売上を調べたいので、VLOOKUP関数の構文=VLOOKUP(❶検索値, ❷検索範囲, ❸列番号, ❹[検索の型])の❶検索値に関東と指定すると、先に定期購入の行がヒットしてしまい、調べたい結果を返すことができません。
検索値を見つけると次に列番号の値を探しに行くので、同じ検索値の文字がいくつかある場合などは正しく結果を返せないという弱点があります。
関東の新規を調べたいとなると、条件が2つになってしまいます
文字列をくっつけて1つの文字列にしましょう
文字列を結合する
「 & ( アンド・アンパサンド ) 」を使って、別々のセルの文字列を1つに結合して文字列をくっつけることができます。
関東の新規を調べるために、エリアと種別の文字列を結合して関東新規という文字列にしましょう。
サンプルシートはシート4をお使いください
後にVLOOKUP関数で検索値に指定したいので、左端に結合した文字列を作ります。
まずはセルA7に文字列を結合するための数式を入力しましょう。
セルA7に入力する数式はこうなります。
セルB7の北海道とセルC7の定期購入を「 & 」でくっつけます。
セルB7とセルC7の文字列が結合され、北海道定期購入とくっつけることができました。オートフィルで下まで数式をコピペしましょう。
下まで正しくコピペができました。
結合を下まで繰り返す作業は、先ほど学んだARRAYFORMULA関数も使えます
セルA7に入力する数式はこうなります。
正しく文字列を結合することができました。結合する範囲が多い場合はARRAYFORMULA関数を活用しましょう。
それでは、VLOOKUP関数の数式を入力しましょう
複数条件を1つの文字列に結合できたので、VLOOKUP関数検索値として指定します。
セルC3に入力する数式はこうなります。
操作の手順はこちらです。
- セルC3に「 =VLOOKUP( 」と入力
- 検索値が入力されたセルB3 をクリック
- 「 , 」を入力
- セル範囲A7:F30を選択
- 「 , 」を入力
- 6列目の6と入力
- 「 , 」を入力
- 0を入力
- 「 ) 」で閉じる
検索範囲A7:F30のセルB3の売上は9100円と表示されました。
日本語で読むように数式を書くと、こうなります。
C3のセル=
2つの条件を1つの文字列にして、正しく結果を出すことができました
工夫することにより、機能を使いこなせるようになりますね
左側にある値を参照したい
はじめてのVLOOKUP関数の項目で、検索値は範囲の左端のみ指定できると説明しましたね。
VLOOKUP関数は基本的に検索範囲の左端の列に検索値があることが条件に使える関数ですが、表によっては検索範囲の左端に検索値があるとは限りません。
せっかく覚えたのにこのままだと使えない…
検索値が左端になくてもVLOOKUP関数を使える方法を解説します
配列を作成する
スプレッドシートの数式内に「 {} (波かっこ) 」を使って独自の配列を作成することができます。
「 {} 」の中で値と句読点を使って、値の表示順序を指定します。
{}内で使用する句読点
「 , (カンマ) 」で区切って横並び
「 ; (セミコロン) 」で区切って縦並び
「 , (カンマ) 」で横並びにする場合は、={1,2,3}と記述すると1が最初のセル、2が右隣のセル(新しい列)、3…と横に続いて配置されます。
「 ; (セミコロン) 」で区切って縦並びにする場合は、={1;2;3}と記述すると1が最初のセル、2が下のセル(新しい行)、3…と縦に続いて配置されます。
列の順番を変えて参照する
表の列の順番を変えて検索値を検索範囲の左端に配置します。
少々の範囲であればコピペで移動させるのが一番簡単ですが、配列操作でシート5のB6:B16とC6:C16を、シート6で順番を入れ替えて配列してみましょう。
サンプルシートはシート5とシート6をお使いください
シート6のセルB6に入力する数式はこうなります。
シート6のセルB6に 「 ={ 」まで入力してからシート5を開き、最初の列に伝票番号を配置したいので1つ目の範囲にC6:C16を選択、「 , 」で区切ります。
次の列に品名を配置したいので、2つ目の範囲にB6:B16を選択、「 } 」で閉じると、指定したとおりに配列されました。
それでは、VLOOKUP関数の数式を入力しましょう
シート5のセルC3に入力する数式はこうなります。
検索範囲は配列を作成した、シート6のB6:C16を指定します。
操作の手順はこちらです。
- シート2のセルC3に「 =VLOOKUP( 」と入力
- 検索値が入力されたセルB3 をクリック
- 「 , 」を入力
- シート6のタブをクリックして開く
- シート6のセル範囲B6:C16を選択
- 「 , 」を入力
- 2列目の2と入力
- 「 , 」を入力
- 0を入力
- 「 ) 」で閉じる
検索範囲シート6 B6:C16の6344の品名は桃と表示されました。
日本語で読むように数式を書くと、こうなります。
C3のセル=
検索値が左端になくても、値を返すことができました
数式内で直接配列操作する
VLOOKUP関数の数式内で「 {} 」を使って直接配列操作をすることもできます。
検索範囲を指定するときに「 {} 」で直接列を入れ替えます
セルC3に入力する数式はこうなります。
検索範囲は数式内で直接配列を作成し、C6:C16を最初のセル、B6:B16を右隣のセルに配置して指定します。
操作の手順はこちらです。
- シート2のセルC3に「 =VLOOKUP( 」と入力
- 検索値が入力されたセルB3 をクリック
- 「 , 」を入力
- 「 { 」を入力
- セル範囲C6:C16を選択
- 「 , 」を入力
- セル範囲B6:B16を選択
- 「 } 」で閉じる
- 2列目の2と入力
- 「 , 」を入力
- 0を入力
- 「 ) 」で閉じる
B6:B16とC6:C16の配置を替えて検索範囲に指定し、6344の品名は桃と表示されました。
日本語で読むように数式を書くと、こうなります。
C3のセル=
数式内で順番を指定できると、検索範囲用に表を新しく作らなくてもVLOOKUP関数が使えますね
工夫次第でいろいろなパターンで活用できます
まとめ
Googleスプレッドシートの「VLOOKUP関数」マスター編としてお伝えしました。
VLOOKUP関数は実務に必須の関数ともいわれています。
関数や機能の組み合わせ次第でさらに便利になりますので、いろいろなパターンを試して活用しましょう。
