売上集計にVLOOKUP関数で表引きの技

VLOOKUP関数を使う準備、マスタの並び替え、マスタに名前付け、エラー回避

EXCEL技BEST　33回

コードから商品や単価探すのは大変だ～VLOOKUP関数

売上表や見積書の作成時に、商品コードから商品名や単価を自動入力できるVLOOKUP関数について詳しく説明します

売上集計表で商品コードを入力すると商品名と単価を自動表示してくれると、入力ミスもなくなり、仕事が非常にはかどります。今回はその式を作成します。

VLOOKUP関数を使用する前に準備としては商品コード、商品名、商品単価からなる商品マスターを作成しますが、その際に注意しなければならないことがあります。

それはどのようなことかと言うと、商品コードをキーにして昇順に並べ替えておく必要があるということです。次にはVLOOKUP 関数で参照しやすいように商品マスターには名前を付けるということです。

さらに完全一致の場合に、商品が無いとエラーが表示されますけれども、エラーが表示されるのを回避するには IFERORー関数を使って空欄にしたりメッセージを表示するとするなどの対処をすることができます。

Excelバージョン：Excel20192016201320102007（サンプルファイルは、こちらから中級技33回サンプルデータ）

VLOOKUP関数を使う前の準備

（1）VLOOKUP関数を使う準備として以下のような商品コードと商品名、単価からなる商品マスター（商品リスト）を作成します。

VLOOKUP関数は縦に検索、データは昇順に

商品マスター作成のポイント

1.商品コードをキーに昇順に並べ替えておく必要があります。
2.VLOOKUP関数で参照しやすいように商品マスターには名前をつける。

並べ替えの手順
1. 商品マスター内のセルをクリックし、「データ」タブから「並べ替えとフィルター」グループにある昇順に並べ替えをクリックします。

2. 「並べ替え」ボタンをクリックした場合は、「並び替え」ダイアログボックスが表示され、詳細な設定ができるようになります。例では、「商品コード」をキーに昇順に並べ替えるように設定します。

VLOOKUP関数で参照する商品マスターに名前をつけておく

商品マスターはVLOOKUP関数で参照しますので、わかりやすいように名前を付けておきましょう。範囲に名前を付けるには、対象となるセル範囲を選択してから、名前ボックスに名前を入力し、 Enterキーを押して確定します。

前ページでは、VLOOKUP関数を使う準備として表引きに使う商品マスターを作成するポイントを説明しました。ここではVLOOKUP関数の数式を入力します。

商品コードから商品名や単価を自動入力

VLOOKUP関数の式を入力してみます。
VLOOKUP関数は、
指定された配列の左端の列で特定の値を検索し、範囲内の対応するセルの値を返します。
VLOOKUP関数の意味と書式

2Pに続きます。

VLOOKUP関数を入力する関数の挿入

C列の商品名のセルに数式を入力します。C2セルに式を入力しますので、C2セルを選択します。

リボンの数式タブから関数ライブライの検索行列ボタンを押します。表示される関数の一覧から「VLOOKUP」関数を選択してください。

関数の引数ダイアログボックスが表示されます。
これから、VLOOKUP関数の引数を入力していきます。

VLOOKUP関数の引数の入力～入力項目

では、次の図のようにVLOOKUP関数の引数を入力していきましょう。

検索値　：　商品コードが入力されている「B2」セル
範囲　：　商品コードと商品名、単価からなる商品コード表のデータ範囲。はじめにわかりやすくするためにつけた名前の「商品マスター」を指定しました。

列番号　：　商品コード表の何列目に表示させたいデータが入っているかを指定。

商品コード表の、左から2番目の『商品名』を表示させるので『2』と指定。
『単価』を表示させたいときは3列目なので『3』を指定しする。

検索の型　：　完全に一致するデータを検索したいときには「FALSE」を指定します。
「TRUE」を指定すると、「検索値」が見つからない場合に、「検索値」未満で最も大きい値が使用されます。以上でC2セルに商品名を表示させる式が入力できました。

次に、D2に単価を表示させる式を入力します。
「単価」の欄に入力する式を商品名と同様に入力します。

「範囲」に入力する「商品マスター」は、そのまま手入力でもできますが、対象範囲を選択しても自動的に名前が表示されます。

商品コードを参照して、商品名と単価が自動的に表示されるようになりました。
「C2」「D2」に入力した計算式を、必要な行数分だけオートフィルでコピーしましょう。

VLOOKUP関数の完全一致のエラー回避 IFERROR

商品コードを参照して、商品名と単価が自動的に表示されるようになりました。「C2」「D2」に入力した計算式を、必要な行数分だけオートフィルでコピーしましょう。
しかし、数式だけコピーしても検索値を入力しておかないと「#N/A」エラー表示されます。エラーであっても実は未入力というだけのことですから、気にする必要はありませんが、非表示にしておきたい場合は、IFERROR関数を使って空欄にしたり、「---」とするなどの対処をすることができます。
=IFERROR(VLOOKUP(B3,商品マスター,2,FALSE),"---")