エクセル VLOOKUP関数

VLOOKUP関数 はエクセル初心者のうちはあまりなじみがないかもしれません。
しかし、初級者を脱した後、非常によく使うのがこの VLOOKUP関数 です。

いろいろな場面で大活躍するので、ぜひモノにしておきましょう。
難しい・・と感じる人もいるかもしれませんが、慣れてしまえば、どうということはありません。

VLOOKUP関数

基本的に関数はツールバーで入力することもできますが、私としては、関数をキーボードから直接手で入力することをおススメしています。

その方が第一に忘れませんし、さらに構造を深く理解でき、複雑な関数の組み合わせを考えるときに有利です。

INDEX と MATCHを使ったさらに高度な検索はこちら

エクセル マクロ/ユーザ定義関数を作る! INDEX&MATCH 超便利!
エクセルではさまざまな関数が用意されていますが、自分でも好きな関数を作ることができます。 それが マクロ画面で作る ユーザ定義関数 です。Function として関数を記述します。 今回はユーザ定義関数を一つ作ってみましょう。 ここで作る LOOKUPS関数は超便利です。 VLOOKUPやHLOOKUPはもう不要ともいえるかもしれません。

書式

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

注意 : 検索値は重複しないようにしてください。

例えば上の図で、C3 セルに以下のように入力した場合

=VLOOKUP($\$$B3,$\$$H$\$$3:$\$$J$\$$11,2,FALSE)

引数について、一つずつ見ていきましょう。

  • $\$$B3 セルB3の値は 1・・1(検索値)に合致するものを検索範囲から探す
  • $\$$H$\$$3:$\$$J$\$$11 検索範囲 引っ張ってくるデータの範囲
  • 2 横へ2つ目のデータを取得
  • FALSE  検索値が完全一致  TRUE 近似一致

となります。

上の図では、例えば、

  • セルのB3に入っている数値 1 を見て、検索範囲の一番左端の値チェックします。
  • 数値が一致したところで、検索値を1番目として、横に2つ進みます。
    そして、そこにあるデータ(mouse)を取得して表示します。
    3つ進めれば (2000)という値を取得することになります。
  • FALSEは検索値 1 と引数が完全一致する場合だけ値を表示します。
    TRUEは検索値が完全に一致しない場合、近い引数を採用します。

注意

絶対参照

絶対参照 $\$$をうまく使えば、コピーが楽々になります。

たとえば、=VLOOKUP($\$$B3,$\$$H$\$$3:$\$$J$\$$11,2,FALSE) において

検索値 B3 に $\$$ をつけて、$\$$B3 としておけば、$\$$ マークがついた B はコピーしても変化しません。
セルC3を縦方向にコピーすると $\$$B3 $\$$B4 $\$$B5 というように変化します。しかし、横方向にコピーした場合、通常なら B3 C3 D3 と変化しますが、$\$$ を付けることで $\$$B3 $\$$B3 $\$$B3 となります。

それと、データ範囲の $\$$H$\$$3:$\$$J$\$$11 は変化しては困りますから、すべて $\$$ を付けておきます。
これにより、どのようにコピーしても検索範囲の $\$$H$\$$3:$\$$J$\$$11 はそのままになります。

これで B3 セルを一気に他のセルにコピーできます。

ちょっとしたテクニック

検索値に文字列を使う

検索値に文字列を使うこともできます。(下図)

絶対参照のテクニック

この場合、C3セルに =VLOOKUP($\$$B3,$\$$H$\$$3:$\$$J$\$$11,2,FALSE) と入力したとき、横方向(D3 セル)へコピーした場合、全く同じ、

=VLOOKUP($\$$B3,$\$$H$\$$3:$\$$J$\$$11,2,FALSE)

となります。

D3 へは、列番号として 2 の代わりに 3 を入力したいところですが、自動で 3 にはなりません。そこで、F2 で編集して =VLOOKUP($\$$B3,$\$$H$\$$3:$\$$J$\$$11,3,FALSE) と書き換える必要があります。

これを避けるには、表の一番上か目立たないセルに列番号の 1 2 3 ・・・ を入力しておきます。(番号が目立って嫌な場合は、見えないところに書く、行を非表示にする、あるいは数字のフォント色を白にすればわかりません)

そのうえで、C3 セルの内容を =VLOOKUP($\$$B3,$\$$H$\$$3:$\$$J$\$$11,C$\$$1,FALSE) とします。
(列番号を絶対参照を使って、 C$\$$1 にします。こうしておくと縦に数式をコピーしても、1行目を示す 1 は変化しません。)

よって、一つのセル(C3セル)の内容をすべてのセルに一気にコピーしても正しく機能します。

#N/A を表示しない

検索値になにも入っていないと、#N/A が表示され見苦しく見えます。(下図)

いろいろな対処方法がありますが、まずは IF関数と組み合わせてみました。
次の図のように入力します。

=IF($\$$B3=””,””,VLOOKUP($\$$B3,$\$$H$\$$3:$\$$J$\$$11,C$\$$1,FALSE))

こうしておくと、B3 のセルが空白の時に ”” となり、なにも表示しません。
(=IF($\$$B3=””,”×”,VLOOKUP・・・、 とすれば、「×」が表示されます)

あるいは IFERROR関数を使って(2007以降)次のようにもできます。

=IFERROR(VLOOKUP($\$$B3,$\$$H$\$$3:$\$$J$\$$11,C$\$$1,FALSE),””)

こちらの方がシンプルですが、EXCELのバージョンによっては対応していないので注意が必要です。

エクセル ISERROR IFERROR関数
知っておくと便利な関数に エラー処理をしてくれる関数があります。 ここではポピュラーな、2種類のエラー処理関数 ISERROR と IFERROR関数 について解説します。

しかし、空白でないとき、たとえば検索値にない数字が入力された場合は、やはり #N/A が表記されます。(これはこれで使い道がありますが)

ISNA関数を使えば、このような場合も対処できます。

=IF(ISNA(VLOOKUP($\$$B3,$\$$H$\$$3:$\$$J$\$$11,C$\$$1,FALSE)),””,VLOOKUP($\$$B3,$\$$H$\$$3:$\$$J$\$$11,C$\$$1,FALSE))

とでもすればOKです。意味は、VLOOKUPで検索した値に #N/A がでれば非表示 ”” にする、という命令を関数で書いています。

コード入力で情報を取り出す

応用の一例ですが、コード番号から名前や住所電話番号などの情報を取り出します。
図では「k111」というコードでデータから情報を取得しています。

このとき、図の例では 、FからI列の1行目に書いた列番号 1,  2, 3, 4 をD列にコピーします。

その際、「形式を選択して貼り付け」を選び、詳細で「行列の入れ替え」にチェックを入れます。

 

そうすると、横の行にあったデータを縦の列にコピーすることができます。

そのうえで、 C3 セルの内容を =VLOOKUP(C$\$$2,$\$$F$\$$3:$\$$I$\$$8,$\$$D3,FALSE) と表記してやれば何行あろうと一気にコピーが可能です。

INDEX関数とMATCH関数

INDEX関数とMATCH関数を使うともっと便利なことができます。

エクセル マクロ/ユーザ定義関数を作る! INDEX&MATCH 超便利!
エクセルではさまざまな関数が用意されていますが、自分でも好きな関数を作ることができます。 それが マクロ画面で作る ユーザ定義関数 です。Function として関数を記述します。 今回はユーザ定義関数を一つ作ってみましょう。 ここで作る LOOKUPS関数は超便利です。 VLOOKUPやHLOOKUPはもう不要ともいえるかもしれません。

これらを使用すると、縦横からマッチするものを選んでくることが可能になります。

コメント