中級

エクセルで指定範囲から
条件に一致したデータを取り出す
(VLOOKUP関数)

この記事は約7分で読むことができます。

2018-09-17

  • キーワード
  • Excel
  • 関数
  • VLOOKUP
  • 使い方
  • 初心者
  • 別シート
  • 複数条件
  • エラー処理
  • 別ファイル

エクセルで指定範囲から条件一致したデータを取り出す「VLOOPUP関数」を解説。

この記事では、エクセルで指定した範囲の中から、条件に一致したデータを取り出すことが出来る「VLOOKUP関数」の使い方の基礎から、発生しやすいエラーへの対応方法、別シートへの抽出(別シートからの抽出)、複数条件での抽出、についての初心者さんにもわかりやすく、詳しく解説します。

1.VLOOKUP関数を例えるなら

VLOOKUP関数は、他の表から条件に一致した情報を、別の場所(セル)に表示する事が出来る、データ抽出に便利な関数です。 ただ、引数を指定するのがちょっと難しく、ほとんどの初心者がつまずく、悩める関数でもあります。(私も初めて使おうとした時に、頭を悩ませ脱毛しそうになりながら、なんとか使えた記憶がありますw) そこで、VLOOKUP関数とはどんな事が出来るのか概念を知りましょう。何事も自分でわかっている事に紐づけられれば、理解が進みます。

VLOOKUP関数を例えるなら「電話帳から、名前を知っている相手の、電話番号を調べ、メモに書き出す」そんな関数です。と何処かの資料かWebサイトで見た記憶があります。 最近では知り合いの電話番号はスマホに入っていますし、オレオレ詐欺を警戒して電話帳に自宅の電話番号を掲載しない方も多いかもしれませんが^^;

2.VLOOKUP関数で出来る事

VLOOKUP関数の具体的な機能は、VLOOKUP 関数が設定された売上集計表で商品コードを入力すると、商品マスタから品名と単価が自動で表示する事が出来ます。 もしこれを人手で行うと、ヒューマンエラーが発生し間違えた資料が出来上がる可能性が高いので、月末の集計で毎月大変な目に会うこと間違いなしですw

3.VLOOKUP関数を使う前の事前準備

VLOOKUP関数を使う前に大きく分けて二つの事前準備が必要です。

準備が必要な物 ①商品マスタ

これはVLOOKUP関数から参照する表で、以下の様な構成になっているとします。

商品コード品名単価内容
S0001Office2016 プロフェッショナル(買い切り)58,703円W/E/O/P/A
S0002Office2016 ホーム&ビジネス(買い切り)34,160円W/E/O/P/A
S0003Office2016 パーソナル(買い切り)29,252円W/E/O/P/A
S0004Office365Solo(年額)12,744円W/E/O/P/A
S0005Office365Solo(月額)1,274円W/E/O/P/A

これはひとつの例で、列方向(横方向に)にもっと長くても問題ないです。行方向(縦方向)も何行でも問題ないです。

準備が必要な物 ②売上集計表

これはVLOOKUP関数を使った様式で、売上実績を集計する為の表で、以下の構成になっているとします。

日付商品コード品名単価数量売上額
 
 
 

この商品コードを入力すると、品名と単価が商品マスタの表から自動で引用して表示されるものを作ってみましょう。

4.VLOOKUP関数の書式

4つの引数を指定する必要がある面倒な関数です。

=VLOOKUP(引数1,引数2,引数3,引数4)

5.VLOOKUP関数の引数に指定する内容

以下の順番で、引数をカンマで区切って指定します。

=VLOOKUP(検索値, 検索範囲, 戻り値の列番号, 検索条件)

引数1:検索値

検索範囲の中から検索したい値を指定します。数値、計算式、文字列、セル番号を指定できます。 品名の場合、単価の場合共に②売上集計表の商品コードを指定します。

引数2:検索範囲

検索値を検索する範囲を指定します。普通は表を指定します。検索範囲を指定するのにはポイントが二つあります。 ひとつ目のポイントは、指定した範囲を絶対参照で指定する事です。具体的には始点と終点のセル番地を$A$1と指定します。

絶対参照については”関数コピーのセル番地ズレを回避”の記事で詳しく解説していますので、良ければそちらを参照してみてください。

ふたつ目のポイントは、引数1で指定する検索値が入ったセルが一番左になる様に検索範囲を指定します。一番左に検索値が無いと、意図した結果が求められません。 品名の場合、単価の場合共に①商品マスタを指定します。

引数3:戻り値の列番号

検索範囲で指定した表の左から何列目を戻り値(答え)として表示させるのかの数値を正の数で指定します。

この場合、品名は「2」を指定します。単価は「3」を指定します。

ちなみにマイナスの値を指定して左側のセルの内容を抽出する事はできません。

引数4:検索条件

これは「FALSE」又は「TRUE」の二択です。基本的には「FALSE」と指定しましょう。 「FALSE」と指定は完全に一致した場合で検索されます。 「TRUE」と指定は近似一致した場合に参照され、検索範囲が昇順でならんでいる場合、検索値を超えない最大値を返します。 もし、検索条件(引数4)を指定しない場合は「TRUE」と同じ動きになります。

6.引数2の検索範囲を別シートにする方法

引数2の検索範囲(今回の場合は商品マスタ)を別のエクセルシートにすることができます。というより別シートにした方がわかりやすいかもしれません。別シートにした場合は検索範囲(引数2)を以下の様に指定します。

シート名!セル番地

とシート名に!記号(ビックリマーク)を付けて指定するだけです。

ちなみに別のブック(エクセルファイル)にする事もできますが、間違えてブックを消したり、ファイルを移動したりしてしまうリスクが高いので、やめた方がいいでしょう。

7.引数1の検索値を2つ以上の複数条件で抽出する

検索値を複数の条件にする事もできます。わかりやすいのは、複数の検索条件を繋げたセルを作る方法です。 条件がA1とB1の二つにマッチした条件にしたい場合は、C1セルに以下のように指定してこれをVLOOKUP関数の引数1検索値にすると複数条件の指定ができます。

=A1&B1

セル番地を&(アンド記号)で繋げる事で、内容が繋がります。セル内容が数値だとしても繋がります。

8.#N/Aエラーへの対処

VLOOKUP関数で、#N/Aエラーが発生した場合は、IFERROR関数でエラーを回避できます。

=IFERROR(VLOOKUP(検索値,検索範囲,戻り値の列番号,検索条件),"一致するデータがありません")

この様に指定すると、エラーが無ければVLOOKUP を実行した結果が表示できます。 エラーがあれば一致するデータがありませんと表示されます。 IFERROR関数の引数2を変えればメッセージを好きな内容に表示可能です。

#N/Aエラーについては”#N/Aの意味と回避方法は?”の記事で詳しく解説していますので、良ければそちらを参照してみてください。

9.スペルと意味

関数のスペルですがvllokup関数ではありません。V方向にLOOKUP(見上げる)する関数です。 V方向とはVERTICAL方向(バーチカル)という意味で、垂直方向に検索して条件にマッチしたものを抽出するという意味を持っています。

ちなみにHLOOKUP関数というのもあり、こちらはH方向にLOOKUPする関数です。 H方向とはHORIZONTAL方向(ホリゾンタル)という意味で、水平方向に検索して抽出するという意味を持っています。

更に、”V”も”H”も付かないLOOKUP関数もありますが、ここでは割愛します。

10.検索値よりも左側の内容を抽出するには

INDEX関数とMATCH関数を組み合わせると表の真ん中の内容を検索して検索値よりも左側の内容を抽出する事も可能です。 初心者には難しいテクニックなので覚える必要は全くありませんが参考に紹介しました。

11.まとめ

  • VLOOKUP関数使う前に、参照先とするデータの入った「表」を準備する。
  • 第2引数(検索範囲)は一番左に検索値がくる様に範囲を指定する。
  • 第4引数(検索条件)はFALSEを指定すれば、とりあえず問題はおきない。

この記事は以上です。最後までご覧頂き、ありがとうございました。

シェア

\ Let's Share !! /

この記事が参考になったら共有して下さい。

同じカテゴリの記事