記事タグExcelExcel関数ビジネス検索/行列関数ただでさえ複雑なVLOOKUP関数の数式を、参照先となる表の変更に合わせて修正するのは大変ですよね。今回はテーブルの応用例として、「構造化参照」のテクニックを解説します。前回はテーブルのメリットとして、「表の管理」「数式の入力」「入力規則の自動設定」の3つを紹介しました。【エクセル時短】第39回では、もう一歩踏み込んだテーブルの活用法を紹介します。なにそれ?」と思った人は、ぜひ最後までお読みください!VLOOKUP関数には弱点がある複数の表を対照してデータを取り出したいときには、VLOOKUP(ブイ・ルックアップ)関数をよく使うと思います。ビジネスでは鉄板の関数で、以下のような構文です。検索の型)例えば、製品コードに対応する製品名を取り出すケースを考えてみましょう。以下の1つ目の画面には[発注表]シート、2つ目の画面には[製品コード一覧]シートがあり、[発注表]シート内の製品コードに対応する製品名をVLOOKUP関数で求めています。製品コードから製品名を取り出すため、セルC2にVLOOKUP関数を入力しています。こちらは[製品コード一覧]シート。VLOOKUP関数の引数[範囲]に指定した、参照先となる表があります。[発注表]シートに入力するVLOOKUP関数の数式は、以下のように表せます。=VLOOKUP(B2,製品コード一覧!A1:C10,2,FALSE)しかし、今後に新製品の追加があったら、どうなるでしょうか?[製品コード一覧]シートにある表の行数が増えるため、VLOOKUP関数の引数[範囲]も変更しないと、追加したデータが検索されません。関数式を修正する必要があります。このように、参照先となるデータに増減があると非常に具合が悪いのが、VLOOKUP関数の弱点なわけです。構造化参照でVLOOKUP関数を修正不要に新製品を追加するたびに関数式をいちいち修正していては、手間がかかって仕方ありません。この問題は、VLOOKUP関数の参照先としてテーブルを指定する「構造化参照」で解決できます。1参照先の表のテーブル名を確認するVLOOKUP関数の参照先となる[製品コード一覧]シートの表を、あらかじめ1テーブルに変換しておきます。[テーブルツール]-[デザイン]タブにある2[テーブル名]を確認しましょう。ここでは「テーブル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関数を使うときにとても役立ちます。複雑になりがちな関数式をシンプルにする効果もあるので、ぜひお試しください。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関数の使い方を解説します。2016/2013/2010/2007に対応した全476関数の使い方を、関数の機能ごとの分類でまとめた一覧です。分類から関数を探せます。Share記事をシェアするtweetpocketこの記事が気に入ったらいいね!しようできるネットから最新の記事をお届けします。たかし)元パソコン書籍の編集者。現在は、パソコンのほか、デジタルカメラやスマートフォンなどのデジタルデバイス、Webサービスなどに関する企画・編集・執筆を中心に活動しています。お届けした情報が少しでも役立ったと思ってもらえれば幸いです。