Excel(エクセル)で番号から関連するデータを表示する方法/Vlookup関数の使い方

discovery IT

Excelを使いこなす上で絶対に避けて通れない関数、それがVlookup関数です。一見難しそうですが、やってみるとそうでもないです。

Advertisements

Vlookupの概念

look upは「調べる」という意味で、vはvertical=「垂直」。よって垂直に調べる関数という意味になります。
たとえば、リストから特定の人の電話番号を調べる場合で考えてみます。

vlookup1
まずは垂直に名前を追っていき、目的の人物を探します。
vlookup2
目的の名前を見付けたら、そこから水平に視点をずらし、目的のデータ(電話番号)を探し当てます。

Vlookup関数がやってくれるのも、ちょうど上のような処理です。検索する範囲(名前)の中で目的の値を見付け、そこから右側にあるデータを表示する。
この挙動は、社員番号から社員の名前を調べたり、型番から商品名を調べたりと、仕事上の多様な場面で活用できます。

Vlookup関数の使い方

では、実際に使ってみたいと思います。書式は下記の通り。

データの垂直→水平検索
Vlookup(検索値,適用範囲,水平方向の移動距離,検索の型)
vlookup3
テスト用に、こんな感じの表を作ってみました。
vlookup4
型番検索に型番を打ちこむと、
vlookup5
型番に対応する商品名が自動的に表示される、というものを作ります。
vlookup6
商品名を表示するセルを選択し、=vlookup(と打ちこみます。するとVLOOKUP(検索値,範囲,列番号,[検索方法])とガイドが表示されます。
vlookup7
検索値は「何を検索するか」。電話番号の例で言えば斎藤さんです。今回はF2セルに打ちこんだ値を検索したいので、F2と入力(またはマウスでF2をクリック)します。
vlookup8
,を打ち次に進みます。範囲はVlookupを適用する範囲です。検索する型番、表示する商品名どちらも含む必要がありますので、A2:B14と入力(またはマウスで範囲選択)します。
検索はこの適用範囲のうち、一番左の列に対して行われます。それより右の列にあるデータは検索対象に含まれません。
vlookup9
,を打ち次に進みます。列番号は検索値が見つかった場合、どの列のデータを返せばいいかを選択します。一番左の列(検索した列)が1、その右の列が2、その右が3..と数えます。
今回は検索範囲のひとつ右の列のデータを表示したいので、2と入力します。
vlookup10
,を打ち次に進みます。最後に検索方法ですが、完全一致が部分一致かを選ぶものなので、完全一致を意味するfalseを入力しておけばOKです。
vlookup11
Enterを打って完成です。これで=vlookup(F2,A2:B14,2,FALSE)(A2~B14の中で一番左列を検索し、F2完全一致する値を探す。もし見付けたら同じ行ひとつ右にあるデータを表示するという式が完成しました。
vlookup12
型番検索に型番を打ちこむと、対応する商品名が返されることが確認できました。

価格も表示してみる

vlookup13
同様に価格も型番から表示されるようにしてみたいと思います。今回は適用範囲に価格を含む必要がありますので、C列を含むA2:C14を指定します。
vlookup14
列番号はA列からふたつ右なので、3を指定します。
vlookup15
あとは同様で、=vlookup(F2,A2:C14,3,FALSE)で完成です。これで型番を打つと、名称および価格が自動で表示されるようになりました。
vlookup16
念のため型番を変更すると、名称および価格も更新されることが確認できました。

Vlookup応用編

条件によって分岐し、複数の表を使い分けるVlookUp関数の例です。同じ席でも大人と子供でちがう料金を表示する表を作っています。

Excel(エクセル)で条件により複数の表を参照するVlookup関数の作り方/Indirect関数との組み合わせ方
VlookUp関数を使う時、前提条件によって参照する表を変えたい場合があります。 指定する表に名前をつけておき、式内で条件に組み込めばいいのですが、Indirect関数を噛ませないとエラーになってしまいます。

ワイルドカードを使い、文字列の一部を入力するだけで対応するデータが表示される検索窓の作成例です。

Excel(エクセル)で文字列の一部で検索する、データベースの検索欄を作る方法/ワイルドカード(*、?)の使い方
ワイルドカードとは、ポーカーでいうジョーカーのように「他のあらゆるカードに代替できる」ようなカードのことをいいます。これが転じて不特定の文字列を意味するIT用語となりました。 これを使い、GoogleやYahooの検索欄のようなデータベー...

その他実務で使うとどういう感じになるか、という例です。

Excel(エクセル)でリストにあとから条件・項目を追加し、フィルターをかける方法
Vlookup関数を使った応用編として、リストに後から条件を追加して、フィルターで表示項目を絞る方法をご紹介します。
excel(エクセル)で範囲内の頻出単語・最頻値を調べる方法/Mode・Mode.sngl・Countif関数の使い方
excelデータの中でどのデータが多く登場しているのかを検出する方法です。実行環境は2007でMode関数を使用していますが、2010以降ではMode.sngl関数に名前が変わっています。 下のほうでCountif関数の解説もしています。...

Vlookup関数については以上ですが、もし検索元のデータを拡張する予定がある場合は、構造化参照をしておくと便利です(やや難易度高め)。
なお、もし水平→垂直方向にデータ検索をしたい場合は、HlookUp関数というものもあります(縦と横変えるだけです)。

やりたいことから方法を探すエクセルExcel操作・関数・VBA(マクロ)逆引きまとめ
逆引き(やりたいことから探す)Excel記事まとめ
関数・演算子・メソッド・プロパティ名から探すExcel/VBA(マクロ)使い方・組み合わせ方まとめ
こちらはExcelやメソッドの諸機能を、機能の名称から探せるまとめ記事です。

コメント