他のサービスIDでログイン

メールアドレスでログイン

ID:

password:

無料会員登録

パスワードを忘れた方

会員登録時のメールアドレスを入力してください。
パスワード再設定するメールを送信します。

2019/1/23 updated

Excel関数活用術⑤請求担当者必携!データ抽出に便利なVLOOKUP関数

Excel関数活用術⑤請求担当者必携!データ抽出に便利なVLOOKUP関数

連載「仕事がサクサク進む!Excel関数活用術」、前回は本日の日付を出すTODAY関数、日数計算を行うDATEDIF関数、Excelの相対参照と絶対参照を紹介しました。

5回目の今回は、検索条件に一致したデータを抽出するVLOOKUP関数を詳しく解説していきます。

VLOOKUP関数は、たくさんあるExcelの関数の中でも使用頻度の高い関数です。しかし、「使い方が難しすぎる…」という声もしばしば。

1つひとつ手順を詳しく説明しますので、この機会にVLOOKUP関数の使い方を覚えてくださいね。

広告

検索条件に一致したデータを抽出するVLOOKUP関数

VLOOKUP関数は、指定したセルの範囲から、検索条件に一致したデータのみを抽出したいときに使います。

あなたが経理業務または営業事務を担当していて、請求書を毎月作成しているとします。
「A社はりんごが50個、みかん100個だから○円…」と、1社ずつ請求書に商品名と単価、個数を記入して、売上を計算するのはとても手間がかかる仕事。

しかし、VLOOKUP関数をセルに記述した請求書を使えば、商品番号を入力するだけで「商品名」と「単価」の自動入力が実現します。
入力の手間を大幅に減らせるので、VLOOKUP関数は総務や経理、人事、営業と様々な部門の人が仕事で使う、Excelで人気の高い関数なのです。

VLOOKUP関数の4つの引数

VLOOKUP関数は仕事の効率アップに便利な関数です。しかし、指定する引数が4つもあってちょっと使い方が複雑。VLOOKUP関数の基本構文は以下のようになっています。

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

引数1「検索値」

データ(表)の中から、検索対象となるコードや番号が入力されたセル(B1など)を指定。

引数2「範囲」

抽出したいデータ(値)を検索するセルの範囲(A3:F12など)を指定。

引数3「列番号」

範囲に指定したデータ(表)を左から数えて何番目の列を参照するか(「1」「2」などの数値)を指定。

引数4「検索方法」

引数に指定した範囲を検索して、値が見つからなかったときにどう処理するかを指定。
指定した範囲から検索値に完全に一致する値を抽出するときは「FALSE」を、検索値に一致するデータがなく、検索値を超えない最大値(近似値)を抽出するときは「TRUE」※と指定。

※ 仕事でVLOOKUP関数を使う場合、引数に指定した範囲の中から一致したデータを抽出するケースがほとんど。そのため、使い始めのうちは、検索方法で指定する引数は「FALSE」と覚えておけばよいでしょう。成績や予算など基準値を超えない値から最大値を抽出するといった特殊な事情を除き、「TRUE」を使うことはまずありません。

VLOOKUP関数で請求書を自動作成してみよう

請求書の自動作成(商品番号を入力しただけの請求書に商品名と単価を自動入力させる)を例に、VLOOKUP関数の使い方を一緒に確認してみましょう。

STEP1:元データとなる商品一覧表を作成

以下の図を参考にして、元データとなる商品一覧表を作成してみてください。

元データとなる商品一覧表

STEP2:請求書を作成

VLOOKUP関数を入力する請求書を作成します。「単価×数量」の計算結果を表示するセル(E3~E11)と、請求金額の合計を表示するセル(E12)には、下の図のように数式を入れておきましょう。

請求書を作成

STEP3:VLOOKUP関数で商品名・単価を自動表示

請求書に「商品コード」を入力すると、「商品名」を自動表示するよう、VLOOKUP関数を入力します。

商品名のセルに「=VLOOKUP()」と入力

① A3からA6セルに商品コードを入力。
② 商品名のB3セルに「=VLOOKUP()」と入力。
③ 商品コードを使って表Bの商品一覧から商品名を検索するので、引数の「検索値」はA3セルを指定。

引数の「範囲」は「$G$3:$I$6」と指定

④ 表Bの商品一覧から商品名を検索するので、引数の「範囲」は「$G$3:$I$6」と指定(抽出するデータを検索する表の範囲は固定なので、列と行に「$」で絶対参照のマークをつける)。

「列番号」は「2」と指定

⑤ 表Bの範囲のうち、商品名が書かれた左から2番目の列をB3セルに抽出するので、引数のうち「列番号」は「2」と指定。

「検索方法」は「FALSE」と指定

⑥ 表Aと表Bの商品コードが完全に一致するデータを抽出するので、引数のうち「検索方法」は「FALSE」と指定。
⑦ Enterキーを押す。B3セルに「りんご」と表示されます。
⑧ B3セルの右下にカーソルを持っていき、入力した数式をオートフィルでB6セルまでコピーします。
⑨ C列の単価(C3からC6セル)についても、手順②~⑧と同じ要領で、表Bから単価のデータを抽出します。表Bから抽出する単価の列番号は「3」です。引数「列番号」指定に注意してください。
⑩ 最後にD列の数量を手入力すれば、商品ごとの金額とすべての請求金額が自動計算されます。

今回はVLOOKUP関数の使い方を説明しました。慣れないうちは引数の指定がちょっと複雑なので「自分には無理かも」と思うかもしれません。しかし、使い方をマスターすれば、何万件もある膨大なExcelデータの中から必要なものだけを効率よくピックアップできて仕事がラクになりますよ。

次回は連載最終回。条件から必要な処理を返すIF関数とCOUNTIF関数をご紹介します。ではまた!

※画像はすべてExcel2013を使用

関連する記事が他にもあります

広告

お友達紹介特典URL発行

ログインが必要です

ページトップへ