【エクセル時短】は「少しでも早く仕事を終わらせたい!」というビジネスパーソンのみなさんに、Excelの作業効率をアップするワザをお届けする連載です。毎週木曜日更新。
注目・最新記事の一覧

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

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

VLOOKUP関数には弱点がある

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

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

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

【エクセル時短】VLOOKUP関数には「構造化参照」! テーブルを活用してデータの増減に自動対応する

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

【エクセル時短】VLOOKUP関数には「構造化参照」! テーブルを活用してデータの増減に自動対応する

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

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

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

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

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

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

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

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

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

【エクセル時短】VLOOKUP関数には「構造化参照」! テーブルを活用してデータの増減に自動対応する

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

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

【エクセル時短】VLOOKUP関数には「構造化参照」! テーブルを活用してデータの増減に自動対応する

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

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

【エクセル時短】VLOOKUP関数には「構造化参照」! テーブルを活用してデータの増減に自動対応する

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

4構造化参照を確認する

【エクセル時短】VLOOKUP関数には「構造化参照」! テーブルを活用してデータの増減に自動対応する

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

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

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

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

【エクセル時短】VLOOKUP関数には「構造化参照」! テーブルを活用してデータの増減に自動対応する

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

【エクセル時短】VLOOKUP関数には「構造化参照」! テーブルを活用してデータの増減に自動対応する

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

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

【エクセル時短】VLOOKUP関数には「構造化参照」! テーブルを活用してデータの増減に自動対応する

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

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

=VLOOKUP(B2,テーブル2,COLUMN(テーブル2[製品名]),FALSE)
3製品名を取り出せた

【エクセル時短】VLOOKUP関数には「構造化参照」! テーブルを活用してデータの増減に自動対応する

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

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

HINT表がA列から始まらない場合の対処法

もし参照元の表がA列から始まっていない場合は、手順2で入力している数式を、以下のいずれかの数式で変更することで対応できます。

MATCH関数を使う

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

テーブル2の見出しの中から「製品名」の文字列が入力されている場所を探します。「MATCH("製品名",テーブル2[#見出し]」の結果は「3」となります。

COLUMN関数を使う

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

「COLUMN(テーブル2[製品名])」でテーブル2の「製品名」の列番号を調べます。「COLUMN(テーブル2)」では、テーブル2のある位置(列番号)を調べます。差分は「2」となるので「1」を足して「3」となります。

どちらの結果でも、VLOOKUP関数の引数[列番号]として利用できますが、数式を簡単にするために構造化参照を利用するわけですから、複数の関数を組み合わせるのは得策ではないでしょう。参照元の表をA列から開始するように整えることをおすすめします。

関連記事

【エクセル時短】今さら聞けないVLOOKUP関数の使い方。複雑な「4つの引数」を理解しよう

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

VLOOKUP関数で範囲を縦方向に検索する

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

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

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

関数まとめ

Excel関数一覧 機能別 - 仕事で使えるエクセルの全476関数を網羅!

Excel 2016/2013/2010/2007に対応した全476関数の使い方を、関数の機能ごとの分類でまとめた一覧です。「数学/三角関数」「論理関数」「統計関数」などの分類から関数を探せます。