今回は VLOOKUP関数 の基本的な使い方から、この関数でできることを網羅した解説をしていきたいと思います。
画像を使い、分かりやすく解説していくので、どうぞよろしくお願いします！！
VLOOKUP関数の概要
まずはVLOOKUP関数の概要です。
VLOOKUP関数とは
VOOKUPは「Vertical」 (垂直) を意味する「V」と「LOOKUP」 (探す) を合わせた意味で、表の縦方向にデータを検索し、一致した値と同じ行にあるデータを返す関数のことです。
特定の値で表を検索し、表の中の必要な情報を抽出することができます。
具体例で見ていきましょう。
例えばこんな表があります。
表の先頭列を縦方向にデータを検索し
一致した値と同じ行にあるデータを返す。
これがVLOOKUP関数で検索をおこなう流れになります。
VLOOKUP関数の構造
関数の構造は次の通りです。
VLOOKUP(検索キー, 範囲, 指数, [並べ替え済み])検索キー ：範囲の一番左列の中で、検索する数値やテキストなどの値を指定します。
範囲 ：データの範囲を指します。検索キーで検索する列から出力する列までをおおって指定します。
指数 ：範囲の中の何番目の列を結果として返すかを指定します。
並び替え済み：データが昇順に並び替えられているかどうかを指定します。並び替えられていれば「TRUE」を並び替えられていなければ「FALSE」を指定します。
=VLOOKUP(E2,A$2:C$24,3,TRUE)例えば「検索キー」をE2セルに置いた場合、F2セルには次のような数式を入力します。
※G2セルにはF2セルの内容を表示しています。
=VLOOKUP(E2,A$2:C$24,3,TRUE)範囲には「A$2:C$24」を指定しています。
ちなみに、この「$」は絶対参照の印です。
数式をどこかに移動しても指定範囲がずれないように指定をしています。
ここではセルの数字＝行数部分にだけ「$」が付いているので、行数を固定しているという感じです。
列も固定する場合は「$A$2:$C$24」と入力します。
この範囲の中の一番左の列を検索キーで検索します。
検索キーと一致した行の・・・
=VLOOKUP(E2,A$2:C$24,3,TRUE)「3」列目を出力を指定しています。
=VLOOKUP(E2,A$2:C$24,3,TRUE)最後に、この検索列が「並び替え済み」かどうかを指定します。
昇順で並んでいる場合は「TRUE」を、昇順ではない場合は「FALSE」を指定します。
昇順に並べられたリストから検索する
先ほどの例と同様ですが、検索列すなわち範囲の一番左の列が昇順で並べられている場合、「[並べ替え済み]」には「TRUE」を指定します。
このスクリーンショットの例でいうと以下の数式になります。
=VLOOKUP(E2,A$2:C$24,3,TRUE)昇順に並べられたリストから”完全一致”で検索した場合
「[並べ替え済み]」に「FALSE」を指定した場合も出力結果は同じになります。
並び替えられていない範囲での検索=完全一致での検索は、範囲の全てを検証するためやや処理が遅くなると考えられます（体感では違いは全くわかりません）。
=VLOOKUP(E2,A$2:C$24,3,FALSE)昇順に並べられてないリストから”完全一致”で検索する
表・リストは検索列＝範囲の一番左の列が昇順で並んでいない場合も多くあると思います。
例えば上のスクリーンショットの例で考えると、「検索キー」として単語を指定したい場合、範囲はB列とC列になります。
B列は昇順には並んでいないので、「[並べ替え済み]」で「TRUE」は使えません。
このような場合には「[並べ替え済み]」に「FALSE」を指定しましょう。
=VLOOKUP(E2,B$2:C$24,2,FALSE)昇順に並べられてないリストから”完全一致”でなく検索した場合
仮に「[並べ替え済み]」に「TRUE」を指定した場合は、このスクリーンショットのような結果が返されます。
=VLOOKUP(E2,B$2:C$24,2,TRUE)「検索キー」からは全く一致しない結果が返されるので、並び替えがされていない表・リストに対しては必ず「FALSE」を指定してください。
検索列が昇順に並んでいるケースが多くなかったり、編集によって昇順ではなくなる場合も考えると、この「[並べ替え済み]」には「FALSE」を指定しておけば間違いないと思います。
別シート、別のスプレッドシートから範囲を参照する
VLOOKUP関数で”別シートを範囲”に指定する方法
「[閲覧]出力」シートに作成したVLOOKUP関数の範囲を「[元データ]表」シートから参照したいとします。
「[閲覧]出力」シートのB2セルには以下の数式を入力します。
=VLOOKUP(A2,'[元データ]表'!A2:C24,3,FALSE)
数式を書いたシートとは別のシートを参照する場合、この2つの「‘」で囲み、その後ろに「!」をつけてシート名を指定します。
その後ろは通常のセルの範囲を指定します。
ちなみに、不要ではありますが、数式が書かれているシート名を記載しても動作に問題はありません。
=VLOOKUP('[閲覧]出力'!A2,'[元データ]表'!A2:C24,3,FALSE)VLOOKUP関数で”別のスプレッドシートを範囲”に指定する方法
数式が書かれたスプレッドシートから、別のスプレッドシート（別ファイル）を参照する場合は、以下内容の数式で指定します。
=VLOOKUP(A3,IMPORTRANGE("参照したいスプレッドシートのURLを入力","そのスプレッドシートの'シート名'!とセル範囲を指定"),3,FALSE)IMPORTRANGE関数の構造は、以下のようになっています。
IMPORTRANGE("参照したいスプレッドシートのURLを入力","そのスプレッドシートの'シート名'!とセル範囲を指定")このURL部分は、ご自身が参照したいスプレッドシートのURLを指定します。
スプレッドシートのURLをそのままコピーして貼り付けてください。
IMPORTRANGE("参照したいスプレッドシートのURLを入力","そのスプレッドシートの'シート名'!とセル範囲を指定")
シート名と範囲については、URLをコピーしたスプレッドシートの内、参照したいシート名をコピーし、そのセル範囲を指定します。
=VLOOKUP(A3,IMPORTRANGE("https://docs.google.com/spreadsheets/d/18pAS_sH1RWegPFQ84edYsNzN6ddEW/edit#gid=290006634","'[検証]VLOOKUP'!A2:C23"),3,FALSE)実際にスプレッドシートのURLとシート名とセル範囲を入力した数式はこのようになっています。
IMPORTRANGE関数で初めてのURLを参照する際は「アクセスを許可」アラートが表示されます。
「アクセスを許可」をクリックすると、無事関数が動き結果が出力されました。
VLOOKUP関数の範囲の”入れ替え”右側の列で検索し、左側の列を出力する
実は、VLOOKUP関数の範囲は列を入れ替えることが出来ます。
どういうことかというと、通常、範囲には「A1:B20」のように指定して、このうちの一番左の列「A1:A20」列が「検索キー」の検索対象となります。
しかし「{B1:B20,A1:A20}」のように指定すると、範囲の入れ替えをおこなうことが出来、「検索キー」の検索対象が「B1:B20」となります。
=VLOOKUP(E2,{C$2:C$24,A$2:A$24},2,FALSE)このスクリーンショットの例ではF2セルに入力している数式の範囲に「{C$2:C$24,A$2:A$24}」を指定しています。
E2セルの値をC列から検索し、A列を結果として返すという内容になります。
=VLOOKUP(E2,{C$2:C$24,B$2:B$24},2,FALSE)結果の列について、自由に指定することができます。
このスクリーンショットはB列を結果として返す列に変更した例になります。
ワイルドカード・あいまい検索
ワイルドカード？？
ってなんでしょうか？
ワイルドカードとは、値の中の不明な文字の代わりに利用する代替文字のことです。
数式の中で使用する場合や、フィルタであいまい検索をするときに使用します。
このワイルドカードは「*」で指定します。
次の項で具体的な使用方法を見ていきましょう。
VLOOKUP関数で 〇〇から”始まる”要素を検索する
例えば、表・リストの中から「〇〇」という文字から始まる要素を検索したい場合にこのワイルドカード「*」を使用します。
=VLOOKUP(E2&"*",{C$2:C$24,B$2:B$24},2,FALSE)「「地図」という文字から始まる要素を検索キーにしたい場合、「「地図*」といった書き方をします。
この例のように、検索キーでセルを参照している場合は、「E2&”*”」と指定します。
E2セルの内容から始まって、その後は不明=なんでも良いので、それと一致する要素を検索するわけです。
一つ疑問があるんですが・・・
もしいくつも一致するものがあった場合はどうなるんでしょうか？
この例でいうと、「「地図」なら一つしかありませんが、
例えば「「」とだけだと、複数の行が一致してしまいますよね？
そうですね。
「「*」を検索キーにした場合は複数の行が対象となります。
その場合、一番最初（一致した一番上）の行が出力結果として表示されます。
VLOOKUP関数で 〇〇で”終わる”要素を検索する
もちろん、表・リストの中から「〇〇」という文字で終わる要素の検索も可能です。
=VLOOKUP("*"&E2,{C$2:C$24,B$2:B$24},2,FALSE)検索キーの前に「*」をつけて指定します。
セルの参照と合わせて指定する場合は「“*”&E2」といった書き方をします。
VLOOKUP関数で 〇〇が”含まれる”要素を検索する
「〇〇」が含まれる要素という形で検索キーを作ることも出来ます。
=VLOOKUP("*"&E4&"*",{C$2:C$24,B$2:B$24},2,FALSE)その場合の書き方はこうなります。
テキストで直接指定する場合は「“*地理的*”」といった書き方をし、セル参照で指定する場合は「“*”&E4&”*”」といった指示をします。
またまた質問ですが・・・
”含まれる”ってことは、〇〇から”始まる”も〇〇で”終わる”も含むのでしょうか？？
その通りです。
ワイルドカードで挟むと、〇〇から”始まる”も〇〇で”終わる”も含んで出力されます。
VLOOKUP関数の発展した使用方法
MATCH関数との組み合わせ例
MATCH関数との組み合わせとしては、出力する列を動的に選択できるようにする使い方があります。
例えば以下のようにF2セルに「単語」と「意味」どちらの列を出力するか指定できるような使い方です。
G2セルには以下の数式を入力しています。
=VLOOKUP(E2,A2:C24,MATCH(F2,A1:C1,0),TRUE)MATCH関数では、F2セルに入力された内容を「A1:C1」の中から探しています。
「A1:C1」うち、何番目に一致したかを数値で返します。
F2セルが「単語」の場合、MATCH関数の結果は「2」となるので・・・
=VLOOKUP(E2,A2:C24,2,TRUE)「単語」＝B列の値が出力結果として返されるわけです。
同様に、F2セルを「意味」にすると、
=VLOOKUP(E2,A2:C24,3,TRUE)となり、「意味」列＝C列の値が返されます。
以下にMATCH関数の概要を記載しておきます。
MATCH
MATCH関数は、指定した値が配列内で最初に現れる位置（インデックス）を返します。配列内の項目を検索し、その位置を知ることが目的です。この関数は、INDEX関数と組み合わせて使われることが多いです。
MATCH(検索値, 検索範囲, [検索タイプ])
- 検索値: 検索する値を指定します。
- 検索範囲: 検索を行う範囲を指定します。
- 検索タイプ (オプション): 検索の種類を指定します（1は昇順で最も近い値、0は完全一致、-1は降順で最も近い値）。
ARRAYFORMULA関数との組み合わせ例
複数行分、VLOOKUP関数での出力をおこないたい場合は、ARRAYFORMULA関数と組み合わせて使います。
例えば以下のように複数の「番号」分の「単語」を出力したい場合、VLOOKUP関数をF2セル〜F6セルに書き込むのと同じ出力結果を、ARRAYFORMULA関数を使えばF2セルに書く数式のみで完結させることができます。
F2セルには以下の数式を入力します。
=ARRAYFORMULA(VLOOKUP(E2:E6,A2:B24,2,FALSE))
ARRAYFORMULA関数では、「E2:E6」セル分のVLOOKUP関数自動で処理してくれています。
この組み合わせを「名前付き関数」に登録して活用の幅を広げる、という内容について記事を書いています。
よろしければ見てみてください。
ARRAYFORMULA
ARRAYFORMULA関数は、範囲や配列に対して一括で式を適用します。通常の関数がセルごとに適用されるのに対し、ARRAYFORMULAを使うと、一度に複数のセルに同じ操作を適用できます。
ARRAYFORMULA(式)
まとめ
今回は VLOOKUP関数 の基本的な使い方から、この関数でできることを紹介してきました。
・VLOOKUP関数の概要
・昇順に並べられたリストから検索する
・昇順に並べられてないリストから”完全一致”で検索する
・別シート、別のスプレッドシートから範囲を参照する
・VLOOKUP関数の範囲の”入れ替え”右側の列で検索し、左側の列を出力する
・ワイルドカード・あいまい検索
・VLOOKUP関数の発展した使用方法
といった内容で解説しました。
検索する列が昇順で並べられていない場合は、「並び替え済み」に「FALSE」を指定するんですよね！
範囲内の列の入れ替え方法や、ワイルドカード・あいまい検索の使い方を把握すれば、より幅広い運用ができそうですね！
コメント