【Excel Tips】VLOOKUP関数:商品マスターに商品を追加しても、引数とDropDown リストの範囲変更は不要に!

この記事のポイント

VLOOKUP関数の引数として、通常は検索表の「範囲」をセル番地の絶対参照として設定します。

しかし、商品が増えて検索表にデータを追加した場合、VLOOKUP関数の引数のセル範囲を変更することが必要になります。表をテーブル形式にしておくと、データが増えてもVLOOKUP関数を変更しなくてすみます。

ここまではよく知られた手順ですが、同じ表を使ってDropDownリストを設定している場合、DropDownリストの再設定が必要になるのを省略したい、その方法が書かれたサイトは見当たりません。

DropDownのリスト範囲にも「名前」を設定しておくと、検索キーのDropDownリストも変更が不要になります。

この説明に使用するファイルはこちらからダウンロードできます。

表をテーブルに変更する

Step1. 「商品マスター」シートを開き、商品マスターを表示する。
商品マスターが表示されたら、表の中の任意のセルをアクティブにします。

Step2. 「挿入」タブから「テーブル」をクリックします。
「テーブルの作成」ダイアログボックスが表示されるので、確認して「OK」をクリックします。
表(範囲)がテーブルに変換され、項目名のセルにはオートフィルターボタンが表示されます。

Step3. 「テーブル名」を変更する。
「テーブルデザイン」タブをクリックして、テーブル名を「商品マスター」に変更する。

Step4. DropDownリスト用に「商品CD」の範囲に名前をつける。

ここがこの記事のポイントです!

セルA4:A15を範囲選択する。
名前ボックスに「商品CD」と入力してEnterキーを押す。(Enterキーを押さないと登録されない)
名前ボックスの右🔽をクリックして、テーブル名「商品マスター」と「商品CD」が登録されていることを確認する。

DropDownリストを登録する

Step5. 「請求書」シートのB列「商品CD」にDropDownリストを設定する。
セルB16:B30を範囲選択する。
「データ」タブの「データの入力規則」をクリックする。
「入力値の種類」で「リスト」を選択する。
「元の値」ボックスをクリックしてマウスカーソルを置く。

Step6. 「数式」タブをクリックし、「数式で使用」から「商品CD」を選択する。
「元の値」ボックスに「=商品CD」と入力されるのでOKボタンをクリックする。

これでB列にDropDownリストが登録されました。

VLOOKUP関数を設定する

Step7. VLOOKUP関数を使って、商品CDから商品名を取得する。
「請求書」シートに移動する。

セルC14のデータを削除し、アクティブにする。
「数式」タブからVLOOKUP関数を指定し、関数の引数ダイアログボックスを開く。
「検索値」にセル$B16を指定し、複合参照にしておく。

「範囲」ボックスをクリックする。
「商品マスター」シートに切り替え、見出し行を除いたセルA4:E15を範囲選択する。
「範囲」ボックスに「商品マスターと入力される。
「列番号」に2、「検索方法」にFalseまたは「0」を入力して、OKボタンをクリックする。
オートフィルボタンでC30までコピーをする。

説明は省略するが、IF関数(またはIFERROR関数)を使ってエラー表示を消しておく。IFERROR関数を使えば、

C16セルの数式は「=IFERROR(VLOOKUP(B16,商品マスター,2,0),"") 」となる。

商品マスターに商品を追加する

Step8. 「商品マスター」シートに切り替え、テーブルの最終行16行目に以下の商品を追加する。

A16に入力すると、テーブル範囲が拡張し、罫線も自動で引かれます。

「商品CD」のオートフフィルターボタンをクリックして「昇順」で並び替えておきます。

Step9. 「請求書」シートに切り替え、
商品CDで「BR500003」がDropDownリストに登録されていることを確認します。
(DropDownリストの再設定はしておりません)

VLOOKUP関数の引数を変更することもなく、商品「一番搾り」を請求書に追加することできました。

この方法は、XLOOKUP関数にも応用できます。

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です