• ホーム >
• 特集 >
• 【PC活用講座 - Excel】「Excelの便利機能活用術」「VLOOKUP関数応用」～元データの範囲変更に自動対応～

特集

PC活用講座 - Excel

ExcelのVLOOKUP関数を利用していると、検索先となる元のデータが増減し、表のセル範囲が行方向に増減することがしばしばある。今回はそのように元データのセル範囲が変更されても、自動的に対応可能とするテクニックを紹介する。

通常は検索先のセル範囲の増減に応じて式の修正が必要

前回解説したVLOOKUP関数の例と同じく、元データとして商品一覧の表があり、列は商品コード、商品名、価格という構成とする。そして、別表として売上データの表があり、列は日付、商品コード、商品名、価格、数量とする。

売上データの表に商品のデータを入力する場合、VLOOKUP関数を利用すれば、売り上げがあった商品コードを入力すると、その商品コードに該当する商品名と価格のデータを商品一覧の表から検索し、自動でコピーできるようになることは前回解説した通りだ。

もし、元データである商品が1点増え、商品一覧の表のセル範囲が1行増えたとする。その場合、VLOOKUP関数の第2引数「範囲」は、1行増えた商品一覧の表を示すように1行増えたセル範囲に修正しなければならない。

例えば、商品一覧の表のセル範囲が元々はG3～I6セルであったが、商品が1点増えて1行増えG3～I7セルになったとする。その場合、売上データの表のC列およびD列のVLOOKUP関数では、第2引数のセル範囲が元のG3～I6のままでは、新たに増えた商品が検索対象から外れるのでエラーとなってしまう。

（画像をクリックすると拡大表示します）

検索先のセル範囲を列のみで指定すれば自動対応

通常は元データの件数が増減するたびに、そのような修正作業が発生するが、第2引数「範囲」の指定方法を工夫することで、自動対応可能となり、修正は一切不要となる。

その具体的な実現方法は何通りかあるが、最も簡単なのは、第2引数「範囲」を列のみで指定する方法だ。例えば、増える前の元データのセル範囲がG3～I6であったなら、列のみを記述して「$G:$I」と指定する。

例えば、売上データの表のC11セルなら、次のように第2引数「範囲」を改善する。

改善前
-------------------------------
=VLOOKUP(B11,$G$3:$I$6,2,FALSE)
-------------------------------
↓
改善後
-------------------------------
=VLOOKUP(B11,$G:$I,2,FALSE)
-------------------------------
※改善したのは第2引数（太字）のみ

これで、G～I列のすべての行が検索対象になるため、元データの行数が増減しても、第2引数「範囲」を修正する必要はなくなる。あとはD列も含め、すべてのVLOOKUP関数の第2引数「範囲」を同様に設定しておけばよい。

（画像をクリックすると拡大表示します）

これで、もし元データの商品がさらに1点増えても、売上データの表のVLOOKUP関数の第2引数「範囲」は一切修正することなく、そのままオートフィルなどでコピーするだけで対応できる。

（画像をクリックすると拡大表示します）

このようにVLOOKUP関数の第2引数「範囲」を列のみで指定すれば、元データの行数が増減しようと、式のセル範囲の修正は不要になるため、より作業を効率化できるようになる。さらに修正し忘れのミスも防げるため、業務精度をより向上できるだろう。

その他の【Excel】を見る

• Excelの便利機能活用術　バックナンバー

PC活用講座　一覧へ

（監修：日経BPコンサルティング）

• ページTOPへ戻る