Excel。VLOOKUP関数。商品数が増えるたびに数式を修正するのは面倒なのでどうにかしたい。
＜VLOOKUP関数・テーブル＞
納品書や請求書などでお馴染みの「VLOOKUP関数」は確かに便利なのですが、ちょっと困ったことがありまして、取扱商品が増えるたびに、数式を修正しないといけないという問題が発生します。
どういうことかというと、
C2には、VLOOKUP関数の数式が設定しています。
=VLOOKUP(B2,$E$2:$F$6,2,FALSE)
なお、今回は「#N/A」エラーに対応する、IF+VLOOKUP関数やIFERROR+VLOOKUP関数の形での数式にはしておりません。
「範囲」が問題の原因でして、$E$2:$F$6を設定しています。
これは、商品リストに該当する場所を設定しているわけですが、商品が増えれば、範囲で設定した$E$2:$F$6も範囲選択を拡張しなければなりません。
試しに、新しく「C001 ボールペン」を追加してみて確認すると、
「#N/A」エラーが表示されました。範囲は自動的に拡張されるわけではないので、当然、新しく追加したものは、範囲外になりますので、エラーになってしまうわけですね。
「範囲」で設定した、$E$2:$F$6を商品が追加されたら、修正すればいいのですが、追加のたびに修正するのは、面倒ですし、ミスを起こす可能性も発生します。
なおかつ、単価など複数個所にVLOOKUP関数をつかっていたら、すべてのVLOOKUP関数を修正しなければなりません。
そこで、ちょっとしたことを追加するだけで、この問題から解放されます。
範囲に該当する場所を【テーブル】に設定してVLOOKUP関数を作るだけです。
最初に、今回は商品リストに当たる場所をテーブルにしますので、E1をクリックしておいて、挿入タブの「テーブル」をクリックします。
テーブルの作成ダイアログボックスが表示されますので、範囲を確認して、OKボタンをクリックします。
テーブルに設定されました。
このままでもいいのですが、テーブル名を設定しておくと何かと便利なので、テーブルデザインタブの「テーブル名」に任意で名前を設定します。
今回は、「商品マスタ」としました。
あとは、VLOOKUP関数をいつものように作成するだけです。
C2にVLOOKUP関数を設定してきます。
検索値には、B2。
範囲ですが、E2:F6を範囲選択すると、先程設定したテーブル名に変更されます。
列番号は、2
検索方法は、FALSE
あとは、OKボタンをクリックします。
ここからが「本番」。
改めて、「C001 ボールペン」を追加してどうなるのか確認してみましょう。
今回は、きちんと商品名が検索抽出されました。
追加した時点でわかるのですが、テーブルは自動拡張されますので、テーブルの範囲が広がりました。
このテーブルの自動拡張機能というのがポイントで、数式で使用すると、セル番地で設定するのと異なり、自動的に範囲を拡張して算出することができます。
テーブルというと、どうしてもオートフィルターなどのデータベース機能が中心と思ってしまいがちですが、色々組み合わせみると、作業を効率化することができたりするかもしれませんね。
