【Excel初心者向け】VLOOKUP関数を基本から応用まで使いこなそう

エクセルを使いこなしている人の多くは「VLOOKUP関数」を上手く活用しています。しかし、「何となくVLOOKUP関数に苦手意識を持っている」という方もいらっしゃるのではないでしょうか?そこで今回は、データ入力が“飛躍的に”早くなるVLOOKUP関数について、初心者にもわかりやすく解説します。

*この記事ではOffice2013を例に解説しています

【EXCEL初心者向け】VLOOKUP関数を基本から応用まで使いこなそう_OGP

VLOOKUP関数~基本~

エクセルのVLOOKUP関数とは、ビジネスではよく使うことの多い関数のひとつで、商品Noや顧客No、学籍番号を入力すると、別に作成した一覧表から、指定した行の値を探して、表示させるときなどに使います。

VLOOKUP関数の構文は「=VLOOKUP(検索値,範囲,列番号,検索方法)」です。

事務用品の請求書の例で考えてみましょう。

【イメージ】※左は「商品一覧表」、右は「請求書」というシートです

【EXCEL初心者向け】VLOOKUP関数を基本から応用まで使いこなそう(Excel・エクセル)_VLOOKUP関数~基本~

まず、VLOOKUP関数を使用する場合には、VLOOKUP関数で求めたい表以外に、表示させるデータの元となる一覧表が必要になります。この場合、表示させるデータの元となる一覧表は左の「商品一覧表」です。

VLOOKUP関数の構文は、「どんな値を、どの範囲の何列目から、どのような方法で検索して表示させるか?」という意味合いと考えると分かりやすいと思います。今回求めようとしているのは、画像右側にある「請求書」のB3の商品名です。「請求書」の商品No.に「商品一覧表」のナンバーを入力すると、自動的に対応する商品名が表示されるようにしたいとします。

構文を当てはめると画像右側の数式バーに入力された式「=VLOOKUP(A3,商品一覧表!$A$3:$C$10,2,0」になります。

・検索値(どんな値を)

「検索値」はデータの一覧(商品一覧表)から商品名を探す手がかりになるセル番地を指定します。商品名を求めるには商品No.を元に検索しなければならないので「請求書」で商品No.が入力されているセルA3を選択します。

・範囲(どの範囲の)

「範囲」はデータの一覧表を指定します。「商品一覧表」から商品名を探してくるという意味で「商品一覧表」のA3からC10までを範囲指定します。見出しは範囲に含めなくても構いません。

ここで注意しておきたいことは、範囲指定したら絶対参照(ドルマークを付ける)にすることです。エクセルでは数式をコピーするとコピーした位置に応じてセル番地がずれていきます。これを「相対参照」といいます。今回の場合、相対参照のままでコピーをすると一覧表の範囲がずれていき、エラーが出てしまいます。そこで利用するのが「絶対参照」です。絶対参照にするには、構文に手動でドルマークを追加するか、範囲指定してF4キーを押すことです。このいずれかでセルを絶対参照にできます。ドルマークが付くことで列、行ともに固定されるので、数式をコピーしても範囲がずれることはありません。

・列番号(何列目から)

「列番号」には、自動表示したい値が「範囲」の何列目にあるのかを指定します。自動表示したい商品名は「商品一覧表」の2列目にあるので2を入力します。「商品一覧表」と「請求書」のどちらの2列目を選択すればいいのか混同しやすいので注意しましょう。

・検索方法(どんな方法で検索するか?)

「検索方法」には0(ゼロ)を入力します。0を入力すると「検索値」と完全に一致するデータのみを探し出します。今回は「検索値」の100に完全一致するデータを探したいので0を入力することにしましょう。なお、1を入力した場合や未入力の場合は、指定したデータの範囲内に完全一致するデータがないときに最も近い値を探します。

初めは複雑な式に苦労することもあるかもしれませんが、商品Noを入力するだけで長い商品名を自動表示したり、学籍Noを入力するだけで間違えやすい名前を自動表示でき、大変便利です。

VLOOKUP関数~応用~

・「#N/A」などのエラーを表示させたくない場合

基本編では「請求書」の商品No.にあらかじめナンバーが入っていましたが、商品No.を入力する前に数式だけ完成させておきたい、という場面があるかもしれません。VLOOKUP関数で「検索値」にあたるセルを空白にしておくと通常はエラーが出てしまいますが、このときIF関数を併用するとエラーが出なくなります。

【EXCEL初心者向け】VLOOKUP関数を基本から応用まで使いこなそう(Excel・エクセル)

画像上部は、IF関数を使わずにコピーしたものです。「検索値」が空白のためエラーが出てしまっています。一方、下部は空白がありますがエラーは出ていません。下の数式ではIF関数にVLOOKUP関数を挿入し、「もし検索値のセルが空欄だった場合は何も入力しない、そうでなければ(商品No.が入力されていれば)商品一覧表を用いて商品名を検索する」という指示を入力しています。そして、商品No.を入力すると商品名が表示されます。

構文は、「=IF(検索値= “”, “”,VLOOKUP(検索値,範囲,列番号,0))」です。

ここまで、1つの項目についての数値・文字列を求めてきました。しかし、今回の例で使用したような請求書を作成する場合、求めるものは商品名だけではありません。商品単価、数量、各商品の金額、そして合計金額を求める必要があります。そこで複数の項目について、それぞれVLOOKUP関数で数値を求め、最終的にSUM関数を用いて合計金額を算出するという使い方もできます。

【EXCEL初心者向け】VLOOKUP関数を基本から応用まで使いこなそう(Excel・エクセル)_SUM関数も使い、合計値を出す

各商品No.から商品名を求めたときと同様に、商品単価も「商品一覧表」から検索するVLOOKUPを入力します。数量に関しては、適当な個数を入力しましょう。商品ごとの金額は「=商品単価*数量」で算出します。そして、最後に合計金額のセルE9を選択し、「関数の挿入」ボタンからSUMを選びます。SUM関数は範囲指定した数値を全て足してくれるので、自動的に合計金額が入力される請求書の完成です。

最後に

このVLOOKUP関数の自動入力というシステムは、時間短縮はもちろん、手入力で生じる転記ミスを防ぐという点でも非常に便利な関数です。ぜひ、VLOOKUP関数をマスターして作業効率アップを実感してみてください。

監修者:たくさがわつねあき(著者・インストラクター)

たくさがわつねあき氏プロフィール画像

パソコン教室を運営する傍ら、初心者への直接の指導経験を元に執筆活動を行う。2008年「これからはじめるパソコン超入門の本」で著書デビュー。代表作に「これからはじめるエクセル超入門の本」の他、「たくさがわ先生が教える」シリーズ(技術評論社)、「大きな字だからスグ分かる」シリーズ(マイナビ)がある。指導経験と自筆の漫画を活かした執筆が得意で、「たくさがわ先生が教えるパソコン超入門の本 Windows10 & Excel & Word対応版」など、初心者向け入門書やビジネス向け文庫本、計20冊を刊行。内容はiPad、Excel、ショートカット、困ったを解決、デジカメ、安全対策など多岐にわたる。いずれもメディアで紹介され、好調に売上を伸ばしている。寄稿に、「孫育てのツボ – デジタル機器を使う」(毎日新聞)、「どうしてる?パスワード&暗証番号」(女性セブン)などもある。

文:リクナビネクストジャーナル編集部

あわせて読みたい

Pagetop