WebApp

【スプレッドシート 関数】vlookupの「できない」を解決!基本の使い方、複数条件指定、別ファイル参照、デメリットまで解説

TOP画像


今回はGoogleスプレッドシートの中でも利用頻度が高いvlookup関数について、基本的な使い方はもちろん、抑えておきたい応用編に関しても解説していきます!


スプレッドシート関数 vlookup とは


vlookup関数とは、「垂直方向の検索。 範囲の 1 列目で指定したキーを垂直方向に検索し、同じ行内にある指定したセルの値を返す」とヘルプページでは定義されています。

もう少し分かりやすく言うと、任意の範囲でキーに合致する値を見つけ、その行から他列の値を取得する関数です。

イメージとしては以下のようになります!

スプレッドシート 関数 使用例


データ量が少なければ関数をわざわざ組む必要もなく見えますが、これが大量のデータ同士を突合して別表をつくる等といった作業になると、手動でやるのはとても非効率です。

vlookup関数ならそんなときでも簡単に処理できますので、ぜひ使い方をマスターしておきましょう!


スプレッドシート関数 vlookup 使い方


基本構文と実際の使い方に分けてご説明します!

基本構文

基本構文

VLOOKUP(検索キー, 範囲, 番号, [検索の型])

検索キー
検索する際にキーとなる値です。値を直接入力しても、セル参照("A15")でもOKです。

 

範囲
検索対象とする範囲です。

実はここがvlookupのちょっとややこしい表現ではあるんですが、実際に検索対象となるのは範囲の先頭列、つまり選択した範囲の一番左列のみになります。

スプレッドシート 関数 使用例

このように、選択した範囲のうちキーによる検索は紫部分の列(検索列)のみを対象とします。ではなぜ検索列以外も選択するのかというと、次の「番号」で必要になるからです。

 

番号
値を返す列の番号です。範囲の先頭列を 1 とします。
番号が 1 と範囲内の列数の間に含まれない場合は、#VALUE! が返却されます。

そしてこの「範囲内」というのが、先ほど選択した範囲のことなんです。返却したい列が範囲に含まれていないとエラーになってしまうので、必ず返却する列まで含めて範囲を選択してください。

検索の型
検索したいデータが完全一致で見つからない場合の対処方法を指定します。
TRUEかFALSEで指定しますが、省略も可です。その場合はTRUE扱いになります。

FALSEの場合
一致する値が見つからない場合は「#N/A 」が返されます。一致する値が複数ある場合は、最初に見つかった値に対応するセルの内容が返却されます。

 

TRUEの場合
最も近い一致(検索キーより小さい値)が返されます。検索列のすべての値が検索キーよりも大きい場合は、「#N/A」が返却されます。


もっと簡単に言うと完全一致なら「FALSE」、部分一致なら「TRUE」を指定します。
基本的には完全一致で検索することがほとんどなので、FALSEを設定すると覚えておけば問題ないかと思います!

 

使い方


では、先ほどの例を使って実際に書いてみます!

C7に検索キーを入れると、C11に取得した値が返却されるように作成します。

=vlookup(C7,E7:H17,4,false)


スプレッドシート 関数 使用例

検索キー
C7を指定します。計算式に"りんご"と直接入力してもOKですが、なるべくセル参照に慣れておいたほうがベターです!

範囲
E7:H17を選択します。ここで注意すべき点は、キーで検索する列を一番左側にして範囲選択することです。今回は果物名を検索キーにしているので、果物名のカラムを一番左に持ってきます。

番号
4を設定します。一番左側のカラムである果物名を1と数えて、「料金」は4列目ですね。

検索の型
完全一致のみで検索したいので、falseを設定しています。


基本はたったこれだけです!上記の例のようにまず表の目的・使い方やレイアウトを決めてから関数を組むと必要なデータが明確になるのでおすすめです!




スプレッドシート関数 vlookup 複数条件


検索キーを2つ以上にしたいという時に行う方法です。

ちょっと下処理が必要ですが、難しくないのでご安心ください!
一つずつ手順をご説明します。


  • step.1

    以下のような表を作成しました。
    検索キーを二つ用意して、在庫リストから指定商品の在庫数を取得する感じイメージです。
    今回は商品名と色で指定します。

    スプレッドシート 関数 使用例


  • step.2

    まずは取得元の在庫リストで商品名(E4)と色(F4)を"&"で結合し、G列に出力します。
    "&"は文字列と文字列をくっつける演算子です!"_"が入ってますが、見栄えのためなのでなくても大丈夫です。

    スプレッドシート 関数 使用例


  • step.3

    関数を在庫数の部分に記載します。
    今回は「青色のパーカー」を取得してみます!

    =vlookup(B4&"_"&C4,G4:H14,2,false)


    スプレッドシート 関数 使用例

    検索キー
    B4&"_"&C4を指定します。取得元の形と合わせるため、検索キー①と検索キー②を結合させます。

    範囲
    G4:H14を選択します。検索列と取得したい列を含めた範囲です。


    "2"を設定します。

    検索の型
    falseを設定します。


  • step.4

    上でも表示されていましたが、正確に取得出来ていますね!

    スプレッドシート 関数 使用例


ご説明しておいてなんですが、正直僕はあまり使わないですね…
というのも取得元データは別シートで管理されていることが多く、基本的に直接いじることを禁止しているからです。

ただ検索キーを複数指定することでうまくデータを取得するケースはあるので、一つの手段として覚えておいて損はないと思います!



スプレッドシート関数 vlookup 別シート参照


意味合いとしては2つあります。
同じスプレッドシートの別シートか、他のスプレッドシートかです。

同じスプレッドシートの別シートを参照する


まず同じスプレッドシートの別シートにおけるセル参照と範囲参照ですが、以下のようになります。

参照

◆セル参照
‘別シート名’!参照するセル

◆範囲参照
{‘別シート名’!参照するセルの範囲}


この範囲参照をvlookupの基本構文に組み込むと…

基本構文(改)

VLOOKUP(検索キー,‘別シート名’!参照するセルの範囲,番号,検索の型)


先ほどの表を商品リストとして別シートに移し、同様の結果が得られるか試してみましょう!

=vlookup(B4&"_"&C4,'商品リスト'!D4:E14,2,false)

スプレッドシート 関数 使用例

ちゃんと取得できていますね!

前述のように集計シートと取得元データは別で管理するのが通例なので、別シートから参照して必要なデータを抽出する方がベターだと思います。

他のスプレッドシートを参照する


他のスプレッドシートを参照する場合は、importrange関数を使います。

基本構文

IMPORTRANGE(スプレッドシートキー, 'シート名'!参照するセルの範囲)


スプレッドシートキーとは、スプレッドシートのURLを確認すると、

例:https://docs.google.com/spreadsheets/d/◯◯◯◯◯◯/edit#gid=0

のようになっていると思いますが、この「◯」部分のことを指します。実際はスプレッドシートのURLをそのまま貼り付けても問題ありません。


これをvlookupの基本構文に組み込んでみます!

基本構文(改)

VLOOKUP(検索キー,IMPORTRANGE(スプレッドシートキー, 'シート名'!参照するセルの範囲),番号,検索の型)

シート名を囲む" ' "(シングルクォーテーション)ですが、省略しても問題ないです。


今度はスプレッドシートを別途作成し、取得できるか試してみましょう!

=vlookup(B4&"_"&C4,importrange("1GD0ezeXFLY1OaezJm2mGrYcLm5hFrv9xJ8hZHgNxfQw","'商品リスト'!D4:E14"),2,false)

スプレッドシート 関数 使用例

こちらもばっちり取得できています!




スプレッドシート関数 vlookup デメリット


デメリットというより欠点ですね。

  • 検索列における垂直方向への検索は可能ですが、水平方向への検索はできません。
  • 検索列の結果を元に、検索列より左列の値を取得できません。


出来ないことをちゃんと理解していれば、ある程度表レイアウトで調整したり、データの取得方法を工夫することでvlookupを上手に活用することができます!

そして実は上記のような欠点を補うことができる裏技があるので、こちらもぜひ参考にしてください!

【スプレッドシート 関数】vlookup以外の方法?index+matchで検索列の左側からも取得できる!

続きを見る



最後に


vlookupは手軽かつとても便利なので、非常に使用頻度の高い関数です。

基本的な使い方はもちろん、どのように取得するかで使い方のバリエーションが広がるので、ぜひ色んなことに応用してみてくださいね!


-WebApp

© 2022 tugublog Powered by AFFINGER5