「VLOOKUP関数の使い方を教えてほしい」というご要望が、定期的に入ります。
職場で教えてもらったり、テキストを見たり、ググったりしてみて、それでも分からない、という方が結構いらっしゃいます。
その都度、30分くらいでお伝えするのですが、うちでやっている教え方どおりに書いてあるテキストは一つもないので、毎度サンプルを手作りしてご指導してきました。
そこで今回、改めて、当教室でそのまま使える教材として、「VLOOKUP関数の使い方」を記事にしてみます。
VLOOKUP関数は、何をする関数なのか?
VLOOKUP関数は、いったい何をする関数なのか?
例えばコンビニのレジで商品をスキャンするシーンを思い浮かべてください。
「ピッ」とバーコードをスキャンすると、13桁のコードがレジに読み込まれます。
すると、一瞬で、そのコードに対応する商品名・価格・値引き有無などの情報が、レジの画面に表示されます。
VLOOKUP関数は、そういうことをやってくれる関数です。
VLOOKUP関数は、具体的にどう書けばいいのか?
次に、VLOOKUP関数の具体的な書き方です。
VLOOKUP関数の「形」(引数の書き方)
マイクロソフトのサポート記事では、次のようにまとめられています。
=VLOOKUP(検索値、参照値を含む範囲、戻り値を含む範囲内の列番号、近似一致 (TRUE) または完全一致 (FALSE))
ですが、この記事はこれでは分からない方むけの専用記事です。もう少し掘り下げてまとめてみたいと思います。
こんな感じです。
「検索値」とは
「検索値」とは、コンビニのレジで言えばバーコードの数字のことです。
検索するのは、あなたではありません。VLOOKUPに検索させます
よくある勘違いで、「検索値」というと、一生懸命、目でデータを追って探し始める方がいらっしゃいます。
そうではありません。なにか数字でも文字でもいいんですけど、欲しいデータの「鍵」または「目印」をVLOOKUP関数にポンと渡して「探してきてくれ!」と依頼する感じでお願いします。
「独特の範囲指定」とは
第2引数の範囲指定が、VLOOKUP関数の一番「まかふしぎな」なところです。
「検索値」といっても不正確だし、なに範囲と言っていいのか一言では表現しきれません。独特です。
正直、この関数、普通ではありませんので、関数はみんなこんなのだ、と思わないでください。
ひじょうに特殊な書き方です。
「$」は、いるのか、いらないのか?
よくあるご質問で、範囲指定のさいに「$」はつけなきゃいけないんですか? というのがあります。
よく分からなかったら、基本、「$」をつけてください。
さきほどのサンプルでは「$」をつけていました。
実は、このサンプルでは、結果を表示する場所が1行しかなく、オートフィルをおこなっていないため、「$」はあってもなくても同じ結果になります。
「$」がないとダメなのは次のような場合です。
このように、結果を表示する範囲が複数行ある場合は、オートフィルによって数式を作ります。そのさい、「$」がついてないと、範囲がオートフィルした行数分ずれてしまって、結果が正しく返ってきません。
「欲しいのは何列目」について
第3引数の「列番号」、この記事ではくだいて「欲しいのは何列目」と書いていますが、
これは、「エクセルのシート全体の何列目」ではなく、「第2引数で与えた範囲の中の何列目」です。
VLOOKUPを「横向きにオートフィルする場合」の注意点
この「欲しいのは何列目」は、VLOOKUPを横向きにオートフィルする場合に注意が必要です。
横向きにオートフィルする際、この「列番号」は自動調整されませんので、手動で「4」→「5」などと変更してやる必要があります。
価格のらんにも商品名が表示されてしまっている
なお、手動で列番号を変更するのがめんどくさいので、次のように工夫した数式を用いて、オートフィルのみで完成させる方法もあります。
COLUMN()は、自分自身の列番号を返す関数です。ここでは「C列」のため「3」が返ってくるので、これに1を足して「4」列目を示すように調整しています。
これを右にオートフィルして「価格」の列にコピーすると、「価格」の列は「D列」のため、COLUMNS()の結果は「4」となり、1を足して、正しく「5」列目を示してくれる、という工夫です。
FALSE (たまにTRUE)について
VLOOKUP関数を使い始めて間もない方は、ほぼほぼ「FALSE」でOK、と覚えていただいて構わないと思います。
「TRUE」にするのは、検索値ぴったりの値がない場合でも結果が返ってきて欲しい場合です。
価格表とか運賃表を検索する場合に使います。
詳しくは別記事を参照してください。
Excel 2021以降では「XLOOKUP関数」を使ってください
VLOOKUP関数は、クセが強く、特に範囲指定が独特すぎて、たいへん使いにくい関数です。
ほかの関数とルールが違い過ぎて、普通にExcel上手な人でも、使う時にちょっと戸惑う関数です。
世の中ではだいたい「VLOOKUP使える方」が、「Excel上級者」と同じ意味で使われますが、そんな変な習慣はいますぐやめて欲しい、と当教室ではいつも思っています。オートフィルや並べ替え、SUMIF、IFなど、基本から応用までExcelのスキルはもっと幅広いものです。
Excel 2021で登場した「XLOOKUP関数」に置き換わっていきます
さて、そんなクセつよなVLOOKUP関数への苦情が世界中から寄せられたためか、Microsoftは、Excel 2021で新しい関数を導入しました。
XLOOKUP関数です。
「検索範囲」「戻り範囲」が明確で、適切に「$」を配置することによってオートフィルもできます。
VLOOKUPのように検索範囲が１列目でなくても大丈夫、「検索範囲」を正しく指定すれば対応できます。
VLOOKUP関数では、検索値が存在しなかった場合に「#N/A」というエラーになってしまうのですが、XLOOKUP関数は「見つからない場合」の引数を指定することで、空白や「見つかりません」など好きな文字を表示させることができます。
また、タテでもヨコでも対応可能なので、VLOOKUP、HLOOKUPと2個の関数を使い分ける必要がありません。
こんな素晴らしい関数ができてしまったので、VLOOKUP、HLOOKUPは、かなり先ではあると思いますが廃止されていきます。
全員がExcel 2021以降を使える職場では、できるかぎり今後はXLOOKUP関数を使うことをおすすめします。
詳しくは別記事で解説しています。
コメント