Excel。VLOOKUP関数を実務で使っていく上での拡張性の問題を解決する方法
VLOOKUP関数と入力規則と名前の定義とテーブル
様々な所でVLOOKUP関数に関しての作り方やテクニックなどをご紹介しておりますが、
実務において必ず出てくる、【拡張性】という問題。
その問題の解決方法をご紹介すると、多くの方から、喜んでもらいますので、
今回改めて、この拡張性の問題に関して書いてみようと思います。
まずは、下記の表があります。
そして、別の商品マスターというシートにVLOOKUP関数でいうこところの、
範囲になる、リストがあります。
すでに、C4には、商品コードが入力されていて、
商品名のD列にはIF+VLOOKUP関数の数式が作成できております。
ちなみに、D4には、
=IF(C4="","",VLOOKUP(C4,商品マスター!$A$2:$C$6,2,FALSE))
という数式が設定されています。今回は、IF+VLOOKUP関数の作り方ではなくて、【拡張性】についてご説明していきます。
通常のテキストなどでは、このIF+VLOOKUP関数でおおむね説明が終わります。
が、しかし、現場では、これを使っていくわけですね。
要するに、商品が増えたらどうするの？という訳です。
今回の納品書は、D列とE列にあるIF+VLOOKUP関数の範囲を修正すればいいわけですが、
まず、
商品コードも増えて入力するのが大変になる。
範囲がアイテムが増えるたびに修正しないといけないし、
VLOOKUP関数をすべて修正する必要があるので、今回は2か所。
しかし仮に10か所あったら、1アイテムでも増えたら、数式を直す必要が発生するわけです。
そこで、まずは、名前の定義を使って、使用しやすくするところからやってみましょう。
C列の商品コードは、自分で入力するには、大変ですし、ミスもしやすいので、
入力規則のリストを使ってみたいと思いますが、
ここで、【名前の定義】を設定しておきます。
A2:A6に商品コードという名前を定義します。
名前ボックスをクリックして、名前を入力してあげます。これで設定完了。
では、入力規則を設定していきます。
納品書のC4:C9までを範囲選択をして、
データタブのデータの入力規則をクリックして、
データの入力規則ダイアログボックスを表示します。
入力値の種類をリストにして、元の値には、数式タブの数式で使用から、
先程名前を定義した、「商品コード」を選択します。
そして、OKボタンをクリックしましょう。
まずは、入力はよくなりましたが、アイテムを1件増やしてみましょう。
BR-600でスイカを追加してみましたが、リストは増えていません。
この名前の定義は準備でしかありません。
では、いよいよ、本番ですが、BR-600の行は削除しておいて、
商品マスターのリストをテーブルに設定していきます。
リストの中でアクティブにして、挿入タブのテーブルをクリックします。
範囲はそのままで大丈夫ですから、OKボタンをクリックします。
テーブルになりました。そして、テーブル名に商品リストと入力しておきます。
まずは、VLOOKUP関数から修正していきましょう。
VLOOKUPの範囲を改めて範囲選択してみると、商品リストと入力されました。
これは、テーブル名ですね。これでOKボタンをクリックしましょう。
では、BR-600でスイカを追加してみます。
商品コード。なんと、先程と異なり、BR-600が追加されていますよね。
テーブルにすると名前の定義の範囲も自動的に拡張されます。
そして、BR-600を選択してみると、
ちゃんと、VLOOKUP関数も動いていますね。スイカと表示されました。
このように、テーブルを使うことで、拡張性が確保されましたね。
