VLOOKUP関数☆

2024年8月4日2024年8月5日

Excelができる人のイメージって、一番はVLOOKUP関数が使える人ってイメージが一番強いし、そう思ってる人も多いんですけど、じゃあVLOOKUP関数ってなんなの？っていうのが気になりました。

さとうせんせいの企業向けセミナーでもVLOOKUP関数のことを半日でしっかり教えてほしいっていうことが多いんですけど、さとうせんせいがそのときに教えてる、VLOOKUP関数の使い道とかどう使うとよくてどう使うとだめなのかって話が面白いので、丁寧に紹介しますね。

VLOOKUP関数はこういうもの

VLOOKUP関数で必要なのは、元になる一覧表です。これがないとVLOOKUP関数はできません。なによりもはじめに一覧表を用意してください。「商品名」と「単価」の商品一覧表、「社員番号」と「社員名」と「生年月日」と「入社年月日」の社員一覧表、「取引先名」と「取引先担当者」と「取引先住所」と「取引先電話番号」の取引先一覧表などです。

次の表は商品一覧表で、A3からA7までの範囲に商品名、それぞれの商品に対応する単価が、同じ行のB3からB7までに入ってる表です。

この一覧表でVLOOKUP関数では、１つの商品名を指定してその単価を答えてくれます。逆に単価を指定して商品名を答えてくれません。指定するものは表の一番左にあるものでしかできません。
※厳密にいうと、VLOOKUP関数で扱う一覧表は、求めるものの左側に探すものがある必要があります。上の表では単価の左に商品があるので、商品を探して単価を求めることができます。

VLOOKUP関数は、一覧表の中でほしいデータを何列目かで指定します。だから上の表で単価を求めたいときは２列目ってことになります。

ここまでで考えると、VLOOKUP関数で調べてもらうのに必要なものって、「指定するもの」「一覧表」「列番号」が必要なんですよね。上の表でぶどうの単価を求めるのであれば「ぶどうをA3からB7までの範囲にある商品一覧表の中で探して、その２列目を教えて」っていうことになりますよね。

だから次のように単価を調べるようなものをVLOOKUP関数で作れるんです。セルE3に商品名を入力したら、自動でセルF5に単価が出てくるようにするんです。

セルE3は入力するもので、セルF5が求めるものなので、VLOOKUP関数の単価を求める計算式はセルF5に入れます。

VLOOKUP関数の使い方

VLOOKUP関数は次のように指定をします。

=VLOOKUP(検索値,範囲,列番号,[検索方法])

検索値は「指定するもの」で、ぶどうやみかんといった何の単価を調べたいかというものが入るセルを指定します。セルじゃなく直接指定もできますが、指定するものが文字列の場合は””で囲みます。

範囲は一覧表の範囲です。

列番号は求めたいものが一覧表の中の何列目かを数字で指定します。Excelシートの何列目かではありません。あくまで範囲で指定した一覧表の中の何列目かです。

検索方法は、指定しても指定しなくてもいいものなんですが、指定しないと一番近いものを探し出します。そうするとあいまいな探し方になるかもしれないので、絶対それだ！っていうときはちゃんと指定します。
指定できるのは「TRUE」か「FALSE」の2通りのどちらかです。次の図はVLOOKUP関数を入力している途中の画像ですが、ここでもこの2通りから選ぶようになっていますね。

TRUEで指定すると近似一致といって一番近いもの、FALSEで指定すると完全一致といって絶対にそれを指定します。さとうせんせいの経験からだとTRUEや指定しない近似一致のケースは少なく、ほぼ完全一致だって言ってました。

完全一致はそれだって指定するのでわかりやすいんですけど、近似一致ってなんとなくモヤってしますよね。近似一致は、例えば1000円買ったらこの商品、3000円買ったらこの商品、みたいなキャンペーンセールみたいなのがあるじゃないですか。そういうときに使うものって考えるとわかりやすいです。

次のような感じで買った金額で景品がもらえるとします。

このとき999円買った人はなにももらえません。4999円だった人は1円足りなくて高級和菓子をもらえません。
そんな風にその金額よりも1円でも少ない場合は一つ下のランクのものになるんです。
これは一番近いものといっても、ただ近いだけじゃなくて、調べたい金額を超えないもので一番近いものっていうことになると思います。VLOOKUP関数の完全一致も、指定する数字を超えないで一番近いものを探します。

こんな風に、近似一致のときは、数字の大きさで探すので、主に探すのは文字ではなく数字の場合です。またその数字は小さい順に並べていないとできないっていう面倒な感じもあります。

文字でも近似一致ができます。たとえば商品名が「かつお」「こはだ」「すずき」とあって「さ」で検索すると、五十音で順に検索すると「さ」で検索されたのは「かつお」の次に「こはだ」です。この最後の「こはだ」が検索の結果になるんですね。ただこの五十音って、「い」の次は「あ」じゃなくて小文字の「ぃ」だし、「か」の次は「き」じゃなくて「ぎ」ってなってて想像と違うかもしれません。文字には番号がついていて、調べてみたんですけど、ひらがな、カタカナ、漢字はその番号の順番になってるんですね。でもひらがなの「あ」「い」の間にカタカナの「ア」が検索されたりするので、わりと沼です。そういったことで思い通りにならないこともあるので、近似一致では文字を扱わない方がよいのかもしれません。

近似一致はいろいろ面倒ですが、たまにこういうのもあるので、そのとき使えないといけないので、覚えておいてくださいね。

じゃ、さっきの商品名を入れて単価を調べるものに戻りますね。

この場合のセルF5に入るVLOOKUPの計算式は「セルE3の値をA3からC7のセル範囲から完全一致で探してその2列目を出す」ってことになります。計算式にすると次の通りです。

=VLOOKUP(E3,B3:C7,2,FALSE)

みかんの単価100が出ますね。

さとうせんせいはVLOOKUP関数を作ったら必ずちゃんとできてるか確認しなさいって言ってました。MOSとかの試験だと確認すると余計な操作をしてるって判断されちゃうかもしれないので試験の時にはできないですけど、実際にVLOOKUP関数を使うときはちゃんと動作をチェックしましょう。
チェックは一覧表の一番上と一番下を指定して確認すれば、その間は動作はちゃんとしてるはずですよね。今、みかんで作って100って正しく出たので、今度は一番下のなしを入れて400って出ればいいですよね。

なしの単価400が出ましたので大丈夫ですね。

じゃあ、もうひとつ。一覧表にないものを入れたらどうなるでしょう。
メロンを探してみます。

「#N/A」っていうのになります。これは見つからないよっていうエラーです。
VLOOKUP関数は、見つからないときは空欄になるんじゃなくて、エラーになるっていうのを注意してほしいんです。
エラーで困るときもあるので、そのときはなんとかエラーって表示しないようにしなきゃなんです。

VLOOKUP関数のよく使う例

さとうせんせいが大好きなパターンなんですけど、こういう表で売り上げの合計金額を出せっていうんですよ。

左の表は何月何日に何の商品が何個売れたかって書いてあって、右の表にはそれぞれの商品の単価が書いてあります。左の表だけじゃ売上金額を計算できないんですよ。
合計金額を計算するためには、左の表の一個一個に右の表から単価を調べて販売個数の隣に全部書き込んで、販売個数と単価を1行1行計算してそれを合計するんです。

はい、電卓を使って手作業でやってみましょう。
っていうわけにはいかないですよね。かなり時間かかります。
そこでVLOOKUP関数を使えばいいんですよ！

この表のE列とF列に列を挿入して2行目に項目名「単価」と「販売金額」と入力し、それぞれの項目を計算してください。
なんかMOSの問題みたいですね。

列を挿入するってところまでだと、次のようになります。罫線は自動でつかないかもしれないので、追加してます。

そうなると、VLOOKUP関数はセルE3に入ることになります。
セルC3が検索値になって、範囲はH3からH7まで、範囲の中の2列目が単価で、完全一致で探します。
なので、計算式は次の通りですね。

=VLOOKUP(C3,H3:I7,2,FALSE)

そしてセルF2の販売金額は販売個数と単価のかけ算なので、次の計算式。

=D3*E3

はい、これで下にオートフィルしてみましょう。

みつからないエラーの＃N/Aがたくさん出ちゃいました！！
こういうときはオートフィルの最後のセルを確認すればわかるので、セルE14の計算式を確認します。

あれ？範囲がずれてる。

はい、これなんでかわかりますか？
そうです！オートフィルしたので検索値は下にどんどんずれてよかったんですけど、範囲も下にどんどんずれちゃったんですね。それで範囲が外れて変なところをみたので見つからなくなっちゃったんですよ。VLOOKUP関数で#N/Aエラーが出ちゃったので、それを使って計算しているF列も同じ#N/Aエラーになってるんですね。

なので、VLOOKUP関数の一番上のセルE3の計算式で範囲を絶対参照にして、それを下にオートフィルすれば全部なおりそうです。その時のセルE3の計算式は次の通りです。

=VLOOKUP(C3,$H$3:$I$7,2,FALSE)

一応オートフィルした一番下の計算式もちゃんとしてるか確認しましょう。

大丈夫でした。

VLOOKUP関数でコピーするのであれば、よっぽどじゃない限り、範囲を絶対参照にするのをお約束にしたほうがいいって、さとうせんせいも言ってます。ほとんど絶対参照にしていけないケースはないみたいです。

ここで合計が出せます。販売金額の合計をオートSUMで求めればいいんですよね。

VLOOKUP関数はテーブルで本気出す

Excelのテーブル機能って一覧表をつかいやすくするためのものなんですけど、計算式を作るときもいろんなメリットがあって、計算式をテーブルの名前や項目名で指定できるとか、項目に1つ計算式入れたらその項目は全部計算式が入るとかで、それってVLOOKUP関数を作るときもめっちゃ役立つんですよね。
それにVLOOKUP関数って一覧表を使う関数でしょ。テーブル機能との相性ばっちりなんですよ！

テーブル機能はこちらの記事で詳しくかいています！
テーブルって使わなきゃExcelの魅力半減だよ☆

さっきと同じ表なんですけど、左の表には「販売」っていうテーブル名を付けて、右の表には「商品」っていうテーブル名でテーブルを設定してます。

セルE3にVLOOKUP関数を入れてみますね。
そうすると次の計算式になるんです。

=VLOOKUP([@販売商品],商品,2,FALSE)

＠がついた項目名は同じテーブルの計算式と同じ行のその項目を表しているので、同じ行のセルC3を指しているんですよね。ただの文字列で書いてあるのはテーブル名で範囲は商品テーブルを指しています。

そして、お気づきになりましたでしょうか。
絶対参照の指定がありません。
テーブルで設定すればテーブル名で指定できるので、セルがずれるってことがなくなるんです！！
これはおおきなメリットですね。

計算式が簡単にわかりやすくなる、絶対参照がいらない、オートフィルがいらない、テーブル最高です！！

あとは販売個数×単価を計算して、最後にオートSUMで合計を求めると、テーブルに集計行が加わって合計金額が求められるんです。

テーブルの集計行になってるので▼の中で平均とかに集計方法を変更するのも簡単なんですよね！

HLOOKUP関数☆

VLOOKUP関数のふたご関数にHLOOKUP関数があります。こっちはちょっと目立たない感じがします。本当に似てて動作もほとんど同じです。実際に今はあまり使わないので一つ記事を書くというより、VLOOKUP関数の延長で覚えられるのでここで紹介しますね。

さて、もとになる一覧表ですが、今回は、こんな表でした。

HLOOKUP関数で使うのは、次のような表の場合です。

何が違うでしょう？
縦か横かっていう違いですね。

VLOOKUP関数で扱う表は縦方向にデータが積み重なるんですけど、HLOOKUP関数で扱う表は横方向に積み重なってるんです。
横方向に積み重なる表は新たにはあまり仕事では作られなくなってきてますね。横スクロールするのもたいへんなんで。それに横方向だとめっちゃ便利になるテーブルが設定できません。
ということで使うケースはあまりないと思いますが、もしかしたらこういった表が前に作られててそれを使って調べなきゃいけないときがあるかもしれません。
その時のためにHLOOKUP関数があることだけでも覚えておきましょう。

VLOOKUP関数のVはvertical、英語では縦を表す言葉の略です。HLOOKUP関数のHはhorizontal、英語では横を表す言葉の略です。この英語のどっちかだけ覚えておけば混乱しないでいいでしょう。
なんかアニメで縦一文字切りみたいなのでバーチカルなんとかって必殺技ありそう。そんな感じで覚えてもらっても全然OKです！

VLOOKUP関数に対してHLOOKUP関数はもとになる範囲が縦か横かの違いだけです。それだけです。

ただ、次のような勘違いがよくあるので紹介します。
こんな表です。単価を右の評価から求めて、最終的に販売金額の合計を求めたいときです。

左の表が横方向に積み重なってるんですね。
この場合、範囲となるのはあくまで右の縦方向に積み重なる表ですので、VLOOKUP関数を使います。
計算式を入れるほうの表の縦横はまったく関係ありません。VLOOKUP関数にするのかHLOOKUP関数にするのかは計算式の中で使う範囲の表が縦か横かで判断してくださいね！これほんと注意ですよ！

VLOOKUP関数のトラブル

VLOOKUP関数でトラブルになることって、まあまああるんですけど、いろんなミスが原因です。

全く同じものをさがすので、元の表で「いちご」って入ってたら、カタカナで「イチゴ」っていれても出てきません。ちゃんとひらがなで全く同じく「いちご」って入れてくださいね。

ちゃんとまったく同じ「いちご」って入ってても見つからないってなってるのは、「いちご」じゃなくて「いちご　」って入ってるからです。余計なスペースは見えないんですけど入ってたら「いちごスペース」を探すのでないって出るんですよね。スペース1個で検索するとこうなってるセルを見つけることができます。

次の表は上の表とはちょっと変わってて、商品を番号で指定すると商品名と単価が求まるようになってます。こういう番号で指定するときよくあるんですけど、2だから一覧表の中だったらばななで200円って出るはずなのに出ないですよね。この2、よく見るとセルの左側にあるので、これは数字の2じゃなくて文字の2だってことがわかります。2を数字じゃなくて文字として入れらる方法はいくつかあるので、それらの方法で入れたんだと思います。

このとき、左に寄ってるのかっこ悪いっていって誰かが右揃えに設定すると、まるで分らなくなって一日それだけで解けちゃいます。ものすごくもったいない！！
これはセルの配置の横位置のボタンってクリックするたびに濃い灰色になったりするので、それが全部設定されていないで濃い灰色に状態にならないようにしてみれば、標準の設定の横位置になるので、数字なら右にそろうし文字なら左にそろうし、それで判断できます！

次が右揃えになってる状態

次が標準の状態

今は計算式の入る表の検索値でお話しましたが、元の表の範囲の検索されるほうでも同じようなことがあったら検索できなくなるので注意してくださいね！

まとめ

Excel最強関数って言われているVLOOKUP関数ですが、ほかの表からデータを持ってくる必要がない仕事であれば実はそんなに重要じゃないかもしれません。
でも、この記事のようにVLOOKUP関数の意味や仕組みを理解していれば関数全部に共通する基本がわかってるような気がします。関数を読み解く力だったり、関数の入力方法だったり、関数の選び方だったり。だからこの記事のレベルで理解したほうがいいって思うんですよね。

最近、VLOOKUP関数に代わってXLOOKUP関数が出てきてものすごく注目されて、VLOOKUP関数はオワコンだなんて言われてますけど、そうは思えなくて。
VLOOKUP関数って範囲の一番左の列を探して出すものを列番号で指定するものなんですよね。XLOOKUP関数って探す範囲と出すものの範囲を範囲で指定するものなんですよ。
だから列番号で指定したいときはVLOOKUP関数、左の列で探せない表や範囲で指定したいときはXLOOKUP関数って選んで使うといいと思うんです。
さとうせんせいは、元の表を作るときからExcelは大事だって思って、元の表はデータを縦に積み上げるVLOOKUP関数で扱える形にしなきゃいけないっていつも言ってます。たしかにそれを徹底してたらXLOOKUP関数必要ないですねって言ったら、XLOOKUP関数はXLOOKUP関数でもっといろんなことができるようになってるから必要あるんだよって怒られました。

こんな感じで別の表からなんかを持ってこれるVLOOKUPの解説でした。