前回はテーブルのメリットとして、「表の管理」「数式の入力」「入力規則の自動設定」の3つを紹介しました。【エクセル時短】第39回では、もう一歩踏み込んだテーブルの活用法を紹介します。

キーワードは「構造化参照」。「なにそれ？」と思った人は、ぜひ最後までお読みください！

VLOOKUP関数には弱点がある

複数の表を対照してデータを取り出したいときには、VLOOKUP（ブイ・ルックアップ）関数をよく使うと思います。ビジネスでは鉄板の関数で、以下のような構文です。

=VLOOKUP（検索値, 範囲, 列番号, 検索の型）

例えば、製品コードに対応する製品名を取り出すケースを考えてみましょう。以下の1つ目の画面には［発注表］シート、2つ目の画面には［製品コード一覧］シートがあり、［発注表］シート内の製品コードに対応する製品名をVLOOKUP関数で求めています。

［発注表］シート。製品コードから製品名を取り出すため、セルC2にVLOOKUP関数を入力しています。

こちらは［製品コード一覧］シート。VLOOKUP関数の引数［範囲］に指定した、参照先となる表があります。

［発注表］シートに入力するVLOOKUP関数の数式は、以下のように表せます。

=VLOOKUP(B2,製品コード一覧!A1:C10,2,FALSE)

しかし、今後に新製品の追加があったら、どうなるでしょうか？

［製品コード一覧］シートにある表の行数が増えるため、VLOOKUP関数の引数［範囲］も変更しないと、追加したデータが検索されません。つまり、関数式を修正する必要があります。

このように、参照先となるデータに増減があると非常に具合が悪いのが、VLOOKUP関数の弱点なわけです。

構造化参照でVLOOKUP関数を修正不要に

新製品を追加するたびに関数式をいちいち修正していては、手間がかかって仕方ありません。この問題は、VLOOKUP関数の参照先としてテーブルを指定する「構造化参照」で解決できます。

1参照先の表のテーブル名を確認する

VLOOKUP関数の参照先となる［製品コード一覧］シートの表を、あらかじめ①テーブルに変換しておきます。そして、［テーブルツール］-［デザイン］タブにある②［テーブル名］を確認しましょう。ここでは「テーブル2」となっています。テーブル名は自動的に付与されますが、「製品コード一覧」などと任意の名前に変更することも可能です。

2VLOOKUP関数の数式を修正する

VLOOKUP関数の引数［範囲］をテーブル名に書き換えます。ここでは「=VLOOKUP(B2,テーブル2,2,FALSE)」と修正しました。このような参照方法を「構造化参照」と呼びます。

3参照先のテーブルに行を追加する

修正した数式が正しく動作するか、VLOOKUP関数の参照先のテーブルにデータを追加して確かめてみましょう。テーブルに変換してあるので、11行目に追加したデータも自動的に「テーブル2」に含まれます。

4構造化参照を確認する

［発注表］シートの表に、追加した製品コード（S1-003CN）を入力しました。セルC32のVLOOKUP関数の引数［範囲］は変えていません。関数式を修正しなくても、新しい製品名が正しく取り出されていることがわかります。

列の追加・削除にも対応できる

構造化参照を使えば、参照先のテーブルに行を追加しても関数式を修正しなくてもいいことがわかりました。続けて、列の追加・削除にも対応させておきましょう。これまでの例と同じく、［製品コード一覧］シートの表に列が追加されたケースを考えます。

1参照先のテーブルに列を追加する

［製品コード一覧］シートの表（テーブル2）に、B列として「在庫管理コード」を追加しました。

［発注表］シートを確認すると、VLOOKUP関数の結果が変わってしまいました。これは参照する列がずれたためです。

2数式を構造化参照に修正する

VLOOKUP関数の引数［列番号］で指定していた「2」を、「COLUMN(テーブル2[製品名])」と修正します。テーブル2の「製品名」列の列番号を、COLUMN関数で取得するわけです。

これにより、VLOOKUP関数の数式は以下のようになります。「製品名」をくくる「[」と「]」は半角であることに注意してください。

=VLOOKUP(B2,テーブル2,COLUMN(テーブル2[製品名]),FALSE)

3製品名を取り出せた

製品名を取り出すことができました。数式を構造化参照に修正してあるので、先ほど追加した列を削除したとしても正しく動作します。

テーブルと構造化参照のテクニックは、VLOOKUP関数を使うときにとても役立ちます。複雑になりがちな関数式をシンプルにする効果もあるので、ぜひお試しください。

=VLOOKUP(B2,テーブル2,MATCH("製品名",テーブル2[#見出し],0),FALSE)

=VLOOKUP(B2,テーブル2,(COLUMN(テーブル2[製品名])-COLUMN(テーブル2))+1,FALSE)

関連記事

VLOOKUP関数の使い方

ExcelのVLOOKUP（ブイ・ルックアップ）関数は、ビジネスの現場でよく使われるメジャーな関数ですが、引数の指定が複雑で、いざ使おうとしたときに困ってしまいがちです。今さら人には聞きにくい、VLOOKUP関数の使い方をマスターしましょう。

VLOOKUP関数で範囲を下に向かって検索する

商品番号の一覧表を検索して商品の価格を取り出すなど、範囲を下に向かって検索して一致する値を求めるVLOOKUP（ブイ・ルックアップ）関数の使い方を解説します。

COLUMN関数でセルの列番号を求める

指定したセルの列番号を求める、COLUMN関数の使い方を解説します。

関数まとめ

Excel関数 機能別一覧（全510関数）

すべてのエクセル関数を、「数学／三角関数」「論理関数」「統計関数」といった機能ごとの分類でまとめた一覧です。Microsoft 365（旧称：Office 365）専用の関数、およびExcel 2021～2013に対応した関数と、その使い方解説記事を探せます。