ExcelのVLOOKUP関数を使いこなして初級者から中級者へ!

office関連

2017/09/12

office関連

Excelでデータを扱う際に、特定の検索値からデータを抽出したいと思ったことはありませんか。

データを抽出する関数の中でも、多く使われる関数がVLOOKUP関数です。

ただし、使い方がわかりづらい関数として有名なのも、このVLOOKUP関数です。

今回の記事では、そんなVLOOKUP関数の利用方法の基礎から応用、よくあるエラーへの対処や知っていると便利なExcelの使い方を紹介させていただきます。

Excel VLOOKUP関数とは?使い方が分かる!

VLOOKUP関数とは、検索条件に一致したデータを指定範囲の中から取り出す関数です。

例えば商品番号と商品の情報が一覧となった表があった場合、商品番号を指定しただけで商品の情報を取り出し表示することが可能です。

 

図1

vlookup関数のイメージ

 

図1の場合、[ 1 ]の列に商品番号を入力すると、[ 2 ]の商品一覧から商品名と単価を検索し、[ 3 ]にて商品名と売上の計算結果を表示しています。

関数の書式について

= VLOOKUP ( 検索値 , 検索範囲 , 戻り値を含む列番号 , 検索方法 )

検索値 … 入門商品番号など、検索する値を入力します。
検索範囲 … 検索値(データ)が含まれるセルの範囲を入力します。
戻り値を含む列番号 … 取り出したいデータが何列目にあるのかを指定します。
検索方法 … 完全一致か、近似一致かを選択できます。完全一致の場合「0またはFALSE」、近似一致の場合「 1またはTRUE」と指定します。

書式入力例

=VLOOKUP( C5 , $H$5:$J$11 , 2 , FALSE )

実際に 図1 の E5 にて入力した関数の書式入力例です。

C5 の商品番号「A0001」を検索値として設定し、 H5:J11 の範囲ないから検索を行います。

戻り値には、商品一覧の「商品名」がほしいので、「2」列目を指定します。

検索方法は、完全一致を利用したいため、「FALSE」を指定しました。

 

Excel VLOOKUP関数で複数条件を指定する方法

Excel VLOOKUP関数では、複数の条件をひとつのセルにまとめてしまうことで複数条件の指定を実現できます(通常は条件を1つしか条件を指定できません)。

 

図2

vlookup関数のイメージ

 

例えば図2のように、商品番号は同じでも、販売エリアによって単価が変わる商品の一覧があったとします。

 

図3

vlookup関数のイメージ

 

売上の記録をつけているエリアは西日本なので、「販売エリア」と「商品番号」の2つの条件で検索を行いたいとします。

 

図4

vlookup関数のイメージ

 

図4の場合、[ 1 ]の列に”西日本”と商品番号を連結した文字列を入力します。
(「=”西日本”&A5」で連結が可能です)

[ 2 ]の列も同様に、販売エリアと商品番号を連結した文字列を入力しておきます。

[ 3 ]の商品一覧全体から連結した文字列と単価を検索し、[ 4 ]にて商品名と売上の計算結果を表示しています。

 

Excel VLOOKUP関数で「#N/A」エラーを表示させない方法(iferror関数)

VLOOKUP関数を利用してデータを検索した場合、検索に失敗すると「#N/A」エラーが表示されてしまいます。

ご利用のExcelがExcel2007以降であれば、iferror関数を利用することで、「#N/A」エラーの表示を回避できます。

関数の書式について

= IFERROR ( 値 , エラー時に表示する値 )

書式入力例

=IFERROR( VLOOKUP( A5 , $H$5:$L$18 , 4 , FALSE ), “” )

上記を入力した場合、VLOOKUP関数が検索に失敗した場合、””(空白)が表示されます。

 

Excel VLOOKUP関数で別シートを参照する方法

VLOOKUP関数でデータを検索す際に、検索を行いたい検索範囲がExcelの別シートにある場合、別シートを参照することで検索範囲として指定することが可能です。

 

図5

vlookup関数のイメージ

 

 

図6

vlookup関数のイメージ

 

この場合、検索範囲の指定時に、シート名を記載することで参照が可能になります。

書式入力例

=IFERROR( VLOOKUP( A5 , Sheet2!$B$6:$F$19 , 4 , FALSE ), “” )

セルの値「$B$6:$F$19」の前に「Sheet2!」と記載があるのがわかります。上記の方法で、別シートの値を参照することが可能となります。

目的別エクセル人気講座

元モルガン・スタンレー証券投資銀行部門のプロが教えるExcel財務モデリング

【 初心者から財務プロまで 】エクセルで学ぶビジネス・シミュレーション講座 マスターコース
【 初心者から財務プロまで 】エクセルで学ぶビジネス・シミュレーション講座 マスターコース

エクセルを活用して、様々なシミュレーションができる財務モデルを作成し、ビジネスの意思決定力を高められる

劇的業務改善への道!Excelマクロをわかりやすく学べる

ここから始める!本気でExcelマクロ(VBA)を学びたい人のための基礎講座【超入門編】
ここから始める!本気でExcelマクロ(VBA)を学びたい人のための基礎講座【超入門編】

「事務作業効率を劇的に変化させるExcel術」 VBAの考え方からファイルの作成までが学べる。

Excel資格を取ってスキルを証明!

MOS試験Excel対策オンライン講座【2013スペシャリスト】エクセル兄さんのスピードMOS対策・合格コース
MOS試験Excel対策オンライン講座【2013スペシャリスト】エクセル兄さんのスピードMOS対策・合格コース

Microsoft資格「MOS」対応/人気講師・エクセル兄さんの分かりやすいビデオ講座で合格

  • シェア
  • ツイート
  • Poket
  • はてなブックマーク
  • フォロー
  • シェア
  • ツイート
  • Poket
  • はてなブックマーク
  • フォロー

関連記事