今日は、vlookupという関数をご紹介します。かなり便利な関数ですが、少しだけ覚えるまでにコツがいるので初心者の方には鬼門となっていることがあるのではないでしょうか。
vlookupとは？
vlookupのvは、「vertical」つまり垂直、「lookup」は探すという意味なので、vlookupは「縦に探す」という意味の関数です。
これだけ聞いてもイメージが付きにくいかと思いますが、簡単に言うとあるセルの内容をキーにして、別の表の中から同じ内容があるセルに対応する値を縦(列)に探してくれる関数です。
例えば「この人(名前)の電話番号を、名前と電話番号が一覧で並んでる中から探そう」と思った時に自動で探してくれるのです。
vlookupの使用例
細かい説明の前に、感覚で理解してもらうために実際に簡単な例を打ってみます。
先程記載した通り、基本的に使う場面は、特定の値に合致する値を他のテーブル(表)から取ってくる、というのが一般的です。
例えばこのように名前の一覧とそれとは別の表を用意します。
この表の右側の名前だけの列の佐藤さんのクラスを、左側の表の中から参照してみましょう。
このように、佐藤さんの右側に「vlookup」の数式を組んでみると、
vlookupの各引数の説明
関数の、()に入れる値の事を「引数(ひきすう)」と言います。vlookupは引数が4つあるので難しいように思われますが、それぞれ理解してしまえばそんなに難しくありません。順番に見ていきましょう。
①検索値
最初に入力する一番左側の引数は検索値です。これは「別の表に数値を取りに行く際の、共通したキーとなる値」を入力します。
②範囲
次に、範囲です。範囲は、取りに行く表の範囲を指定します。指定の方法はルールがあり、「検索値で指定した値を含む列を一番左」に選択し、「引っ張りたい指標（例でいうクラス）の列まで」を範囲として選択します。
③列番号
次は、列番号を指定します。これは引っ張りたい指標の列を、範囲指定内の列の左から何番目という形で指定します。例でいうと、クラスになりますが、これは左から2列目にあるので、「2」と指定します。
※範囲指定した範囲の一番左列を起点にするので、表自体の一番左列が起点ではないことに要注意
④検索方法
ここでは検索の型を指定しますが、この引数だけは入力は任意です。検索値に一致しなかったデータが見つからなかった場合に、どのように探しに行くかを指定する引数です。指定方法は2つあります。
- 「0」または「false」の指定 => 完全一致
- 「1」または「true」の指定 => 近似値（検索値未満の最大値）を範囲の中から検索
上記2つの指定方法がありますが、実務上においては検索方法の指定は使う必要がありません。なぜかというと近似値という機能が曖昧なので、実質的に完全一致に近い働きをするためです。何年も実務上でvlookupは使用しておりますが、検索の型を指定する必要があった場面は一度もありませんでした。ちなみにこの引数を省略するとtrue扱いになります。
vlookupの応用
ここまでで簡単な仕組みは理解出来たかと思います。
ただし、実務で使うにはもう少し覚えるべきExcelの知識があります。具体的には以下の2点です。
- 絶対参照、相対参照
- ワイルドカード
絶対参照、相対参照
vlookupで引っ張りたい値が1つの時は良いのですが、同じ数式をコピペして2つ以上引っ張りたい時があるはずです。そんな時、この絶対参照、相対参照が役に立ちます。
※絶対参照、相対参照の詳細な説明は別途記事化予定です。
まずはさきほどの例を再掲します。
この状態で、先程使用した例の佐藤さんクラスを引っ張った数式を、下のそれぞれの名前にもvlookupをコピー&ペーストしてみましょう。
※同じ数式の効率的なコピー方法は別途記事化予定
何が起きたのでしょうか？コピーしてコピー元の位置からズレた分だけ、vlookupの参照先もズレてしまったのです。
このコピー元から相対的にズレてしまうことを、Excelの「相対参照」という機能と呼びます。
そんな時、数式に「$」を付けると、$を付けた列、行、または両方が固定された形になります。絶対に同じ参照元を見に行くため、この形を「絶対参照」と呼ぶのです。
実際に絶対参照を使用して、先程の例と見比べてみましょう。上記で作成した数式を、先ほどと同じようにコピーしてみます。
すると今度はずれることなく表示されました。数式も見てみましょう。
この絶対参照、相対参照を使えるようになると一気に効率があがるので、合わせて覚えておきましょう。
ワイルドカードについて
次に、ワイルドカードというものについて見てみましょう。ワイルドカードというのは、「＊」のことで、「何でも当てはめる事が出来る値」の事です。
なんのこっちゃかと思いますので、これまた試しに先程の例を改良してみます。
※ワイルドカードについて詳細な記事を別途作成予定です。
例えば上記の様に、参照したいキーの値が、完全に一致しない場合もあります。そんな時にこれまでと同じ数式で参照しようとすると、上手く引っ張る事が出来ません。（検索の型の指定は数値に対してのみ使用できる機能です）
しかし、以下のように数式を修正してみます。
すると、正しく参照して引っ張る事が出来ました。上記は１つ新たなテクニックを加えておりますが、「&」で複数の数式の中身をつないだ値が指定でき、文字列を直接数式内に指定する方法は「”」で文字を囲います。検索値を直接指定する形に変換すると、以下のような形になっていることになります。
つまり、ワイルドカードを使用すると、
第一引数 = 「佐藤」
だったものが、
第一引数 = 「佐藤」とその後には何があっても良い
という形になったのです。
ワイルドカードは値の前後どちらにでも付ける事が出来ますので、「参照先の値が完全に一致せず困った！」なんて場合にぜひ使ってみてください。
vlookupに似た関数
最後に、vlookupに似た関数を2つご紹介します。
- hlookup
- index関数 × match関数
hlookupについて
vlookupが、「vertical(垂直)」の略だったので縦に参照していましたが、hlookupは「horizon(水平)」、つまり横方向に参照する関数なのです。
ただし、表データの扱いにおいては、横に項目を並べてみる事が基本のため、実務において使用する事はほぼ無いため、「そんなものもあるんだ」程度に覚えておくと良いでしょう。
index関数 × match関数について
この関数はvlookupの上位互換の関数ですが、その分少し複雑になります。
何故上位互換なのかというと、vlookupは、参照先のキーとなる値が、引っ張りたい列よりも右側に無いと行けないという弱点があります。
列を移動したり、参照先のデータを整形すればある程度は対応できるのですが、複雑なレポートや表などを作成すると、見た目や関数の関係上そうもいかない場合も出てきます。
そうした時に、特に何の制限もなく引っ張ることの出来る関数がindex関数とmatch関数の組み合わせになります。
実際にやってみましょう。
上位互換と言った理由が伝わりましたでしょうか？
※index関数×match関数の詳しい解説は別途記事にします。
応用などは基本を押さえてからで十分ですので、まずはvlookupの基本的な使い方を理解してみてください。基本的な使い方をマスターしたら、応用やindex×match関数など、応用に挑戦してみてくださいね！
