いまさら聞けないExcelの使い方講座

【Excel】データが増えてもいちいち数式を修正しないでOK！エクセルのVLOOKUP関数で「範囲」をテーブルに変換するべき理由

  • トップスタジオ

2019年11月27日 06:55

データが増えるたびに数式を修正しないといけないの？

　普段、Excelを使用する業務の中で、VLOOKUP関数を使ったことのある読者は多いと思います。指定したデータのリストから検索条件を満たすデータを探し出して表示してくれる関数で、データ入力の強い味方ですよね。

　例えば、次のような納品管理表（［納品管理表］シート）と、商品情報が記載されているシート（［商品カタログ表］シート）を別々のシートで管理している業務があるとします。VLOOKUP関数を使うと、納品管理表に入力した「商品番号」をもとに［商品カタログ表］シートの「商品カタログ表」を検索（①）し、「商品名」などの情報を自動的に抜き出して表示（②）することができます。

　VLOOKUP関数は、「=VLOOKUP(検索値,範囲,列番号,検索方法)」という書式で使います（VLOOKUP関数について詳しく知りたい読者は、以前の記事を読んでみてください）。

　この例では、［納品管理表］シートのセルC3に次のような数式が入力されています。この数式は、セルB3に入力された商品番号を［商品カタログ表］シートのセル範囲A4:C12から検索し、それに対応する商品名を検索するという意味です。

　2つ目の引数（③）で、検索する範囲（ここでは［商品カタログ表］シートのセル範囲A4:C12）を指定していますが、この［商品カタログ表］にデータが増えた場合、どうすればいいでしょうか。データが追加されるたびに、納品管理表に入力されているVLOOKUP関数の数式を修正しなくてはならないのでしょうか。

　今回は、VLOOKUP関数の数式で参照している表にデータの増減があった場合、数式を修正しなくてもよくする方法を解説します。また、この方法を使えば、VLOOKUP関数の記述自体もシンプルでわかりやすくなり、一石二鳥です。ぜひ試してみてくださいね！

ステップ1：数式で参照する範囲をテーブルに変換する

　まずは、［納品管理表］シートに入力した数式で参照する範囲（［商品カタログ表］シートのセル範囲A3:C12）をテーブルに変換します。

　画面表示を［商品カタログ表］シートに切り替えましょう。表をテーブルに変換する場合、事前に書式が設定されていないほうが見栄えのよい仕上がりになるので、セルの塗りつぶしや罫線などの書式はすべて解除しておきます。表内のいずれかのセル（①）を選択した状態で、［挿入］タブ（②）→［テーブル］（③）をクリックします。

　［テーブルの作成］ダイアログボックスが表示されます。テーブルに変換するデータ範囲が自動的に認識されるので、正しく入力されていることを確認（④）します。さらに、［先頭行をテーブルの見出しとして使用する］（⑤）のチェックマークがONになっていることを確認したら、［OK］（⑥）をクリックします。

　先ほどの表がテーブルに変換されました（⑦）。

　ここで、［テーブルデザイン］タブ（⑧）の［テーブル名］欄に任意の名前を入力します。ここでは、「商品テーブル」（⑨）と入力します。これがこのテーブルの名前になります。

　ここまでで、ステップ1の操作は終わりです。

ステップ2：数式で引数として指定する範囲をテーブル名にする

　では、画面表示を［商品カタログ表］シートに切り替え、C列に入力されている数式を修正していきましょう。セルC3に入力されている数式の2つ目の引数には、検索する範囲「商品カタログ表!$A$4:$C$12」（①）が指定されていますが、これを先ほど作成したテーブルの名前に変更します。

　先ほど作成したテーブルの名前は、「商品テーブル」でしたね。よって、2つ目の引数を「商品テーブル」（②）に書き換え、［Enter］キーを押します。

　「商品テーブル」から検索された商品名が表示されます（③）。数式の処理内容は、修正前のものと全く変わらないので、見た目も変化しませんよね。見た目が変わらず、エラーも出ていなければ正しく入力されていると考えてください。

　セルC3に入力した数式を、オートフィルでセルC7までコピーします（④）。

　これで、数式の修正は終わりです。簡単ですよね。

　次の項では、［商品カタログ］シートの表にデータを追加した場合、本当にC列の数式を修正しなくてもよいのかどうか、実際に試してみましょう。

応用：「商品カタログ表」にデータを追加して数式の結果を確認しよう

　画面表示を［商品カタログ］シートに切り替えましょう。「商品カタログ表」に新しいデータを追加します（①）。先ほど、表をテーブルに変換したので、セルにデータを入力するだけで、テーブルの書式は自動的に適用されます（テーブル機能の詳細については、以前の記事をご覧ください）。

　「商品番号　C103、商品名　メガネレンチ、単価　2,010」という商品データが追加されました。

　再び、表示を「納品管理表」シートに切り替えます。現在、セルB7には「C102」と入力されています（②）が、試しにこのデータを「C103」に書き換えてみましょう。「C103」は、先ほど「商品カタログ表」に追加した商品の商品番号です。セルB7の値を「C103」に変更した時、セルC7の商品名が自動的に「メガネレンチ」と変更されるかどうか確かめてみます。

　セルB7に「C103」と入力して（③）［Enter］キーを押すと、セルC7の内容が「メガネレンチ」（④）に切り替わりました！

　「商品カタログ表」にデータが増えた時に、「納品管理表」の数式を変更しなくても、正しい結果を表示できましたね。

VLOOKUP関数の引数で指定する「範囲」はテーブルに変換しよう

　今回は、VLOOKUP関数の数式で参照している表にデータの増減があった場合、数式を修正しなくてもよくする方法を解説しました。

　VLOOKUP関数の引数で指定する「範囲」をテーブルにすることで、数式の修正が不要になるということをわかってもらえたと思います。さらに、記述内容がシンプルになることもメリットの1つです。引数の「範囲」には、「商品カタログ表!$A$4:$C$12」（シート名とセル範囲）と入力していたところ、「商品テーブル」（テーブル名）と入力すればよくなるので、記述がラクになりミスも減りそうですね。ぜひやってみてください！

Windows 7マシン乗り換えの時。新PCはExcel作業効率が上がるものを！

　Windows 7のサポート終了が迫っている。どうせ買い替えるなら業務効率化を狙ってパソコンを買い替えよう！　ExcelやPowerPointを使った作業が一気に快適になるパソコンのスペックを確認！

今月のExcelTips