スプレッドシートを使っている中でもあまり活用頻度が少ない関数や少し複雑な関数というのはなかなか覚えづらいものです。VLOOKUP関数もその1つでしょう。sumなどと比べると利用するための条件が複雑だったり、入力する項目が多かったりします。今回はそんなVLOOKUP関数について紹介します。
VLOOKUP関数ってなんか覚えづらくていつも調べないとわからないだよね。
確かに少し複雑な印象で苦手意識を持っている人が多いよね。
でも原理を理解しちゃえば大丈夫！
おさらい：Googleスプレッドシートとは？
スプレッドシートは大丈夫という方は読み飛ばしてOKです。スプレッドシートはGoogleが提供しているクラウド型の表作成・表計算ツールのことでGoogleアカウントを持っていれば誰でも利用することができます。詳しくはこちらの記事を参考ください！
【初心者向け・入門編】やさしいスプレッドシートのはじめ方！Excelとの違いから活用方法まで
VLOOKUP関数とは”Google検索の簡易版”のこと！
VLOOKUP関数は「条件に合致した値を参照すること」なんて少し小難しい印象をお持ちではありませんか？簡単にいくと、データベースから条件に合致した回答を表示してくれる関数のことで、みんながよく使っている「Google先生」のすごく簡単なものと覚えていただければ分かりやすいと思います。
よく利用するシーンとしては、商品リストの整理や顧客リストの整理、管理している案件の整理など様々に活用することが可能です。他の関数と異なり、計算することはないので、”テキストデータ”での活用が多いです。
VLOOKUP関数は”共通のモノ”さえあれば利用ができる！
例えば「学校」をイメージしてみましょう。学校では学生1人1人に「学籍番号」が付与されています。学籍番号はその学校の中で同じ番号の人はおらずユニークな番号です。また、学籍番号はその人個人のIDでもあり、氏名や住所、部活動、学校の席席など様々な情報が紐づいています。
このように「学籍番号」がキーとなって様々な情報が紐づいているような状態をマスタと言います。
学校では”学籍番号”に様々な情報が付与されており、これを”マスタ”と呼ぶ
＜学校では学籍番号をキーとして様々な情報が紐づいている＞
- 名前
- 部活
- 体重
- 成績
- 性別
- 住所
など
VLOOKUP関数だと”共通のモノ”が”学籍番号”で検索データベースが”マスタ”のこと！
このようなマスターがあれば、「学籍番号」がわかるだけで様々な情報を自由自在に把握することができます。VLOOKUP関数を活用すると、学籍番号をキーとして体重だけを一覧化したり、成績を一覧化することができます。
VLOOKUP関数の仕組み！
基本の公式は少し慣れが必要！
VLOOKUP関数のイメージができたところで、具体的な数式の意味について理解をしていきましょう。数式では下記のような項目を入力していきます。少し慣れが必要なので、先程の学籍番号の例を用いるとよりイメージがしやすいでしょう。
VLOOKUP(検索キー, 範囲, 指数,[並べ替え済み]))
検索キー：学籍番号のこと
範囲：マスタのこと
指数：マスタから何の情報を取るか
[並べ替え済み]：基本は「0」と入力でOK
実際に学籍番号から成績を取得してみる！
下記のような学籍番号に様々な情報が紐づいている場合を想定します。VLOOKUP関数では学籍番号さえわかれば、紐づいている情報を自由に取得することができます。今回は学籍番号に紐づく”成績”を取得してみましょう。
下記の数式を入れれば取得ができる！
今回は学籍番号がキーとなりますので、VLOOKUP関数のセルではA1を指定、マスタのセルではA2からC6までを指定します。マスタの表では学籍番号から数えて３列目に成績がありますので、「3」と入力します。また学籍番号が完全に一致した場合だけ成績を表示させたいので、その後は「0」と入力します。
B11に下記を入力
=VLOOKUP(A11,$A$2:$C$6,3,0)翻訳
A11：学籍番号”A1“をキーにする
$A$2:$C$6：マスタで学籍番号から成績までの範囲を指定
3：学籍番号から数えて”成績”は3列目
0：完全一致の場合だけ表示
上記をB11のセルに入力してB14セルまでコピーすると学籍番号に紐づく成績が抽出できました。範囲での指定した部分ですが「$A$2:$C$6」のように＄(ドル)マークが付いています。これは”絶対参照”といってコピーした時に選択した範囲がズレないようにするためのものです。
絶対参照をしていない場合は、B12セルでは指定した範囲が1つずれて「A3からC7」となり、B13セルでは「A4からC8」と1つずつズレてしまいます。そのように数式のコピーをしてもズレないようにするために”絶対参照”を活用します。
覚えておくと便利な”小技集！”
VLOOKUPを活用する前に番号を振っておくと便利！
VLOOK関数を活用する際に、すごく広い範囲を”マスタ”として選択しなければいけない場合があります。今回の成績のように、学籍番号から数えて”何列目”ということ指定しなければいけないので、数えるのがすごく大変です。そのような場合は、どこか利用しないセルに番号を振ってしまうと数えなくてもよいので楽になります！
下記のように指定したい項目の上に番号を振ってしまえば、間違って異なる項目を指定するということが少なくなります。
異なるスプレッドシート同士でVLOOKを活用したい時はIMPORTRANGE関数がおすすめ！
IMPORTRANGE関数は別のスプレッドシートの情報をリアルタイムで参照してくれる関数のことです。コピペをしてしまうと元の関数が変更された時に、”ズレ”が発生してしまいますが、IMPORTRANGEの関数はリアルタイムで反映してくれるのでズレることがないのが特徴です。
IMPORTRANGE関数はすごく便利なのですが、一度使ってみないとイメージがつかないと思うので、何度か利用していく中で覚えましょう！
IMPORTRANGE(スプレッドシートのURL, 範囲の文字列)
翻訳
スプレッドシートURL：参照したいURL
範囲の文字列：どのシートのどの範囲のデータを指定するか例
IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1Hh2grfB6rp”, “World Cup!A1:D21”)
iferror関数を活用することで”スマート”に見せよう！
vlookup関数を活用すると該当しないデータについては「#N/A」と表示されてしまいます。該当するものがないので、それ自体は間違ってはいないのですが、なんだか見栄えが悪かったり、その後の関数で利用しづらい場合があります。
そういった時はiferror関数を活用しましょう。
iferror関数とは「エラーとなった場合」に表示させるものを予め指定することができる関数です。
IFERROR(値, [エラー値])
エラーの場合に表示させるものを指定できる例
=IFERROR(VLOOKUP(A17,$A$3:$C$7,3,0),”該当なし”)
上記の例のようにエラーとなった場合は「該当なし」と表示させるように指定すると、学籍番号「E5」がエラー表示だったものが「該当なし」へと変わりました。このようにエラーをなくしてスマートに見せることができるので、ぜひ活用してみましょう。
スプレッドシート関連の記事一覧
まとめ
いかがでしたでしょうか。今回はなかなか覚えづらいVLOOKUP関数について紹介しました。関数の説明だけだとあまり理解しづらいという経験がありましたので、「学校」でのイメージを用いてみました。少しでも分かりやすいと感じていただけたらまとめてよかったと思います。ぜひ、小技集なども参考にしてみてください！
[DISPLAY_ULTIMATE_SOCIAL_ICONS]
