「構造化参照」を使って、VLOOKUP関数 の参照範囲を自動更新する

「構造化参照」を使えば、関数の引数 "参照範囲"が、表の大きさに合わせて自動修正されます

「参照範囲 をテーブル名にする」ことで"構造化参照"になります

　"構造化参照"は、関数の引数 "参照範囲" の指定に"テーブル化" した表の「テーブル名」を使うことです。

　参照範囲を指定する時に、"セル番地" の代わりに "テーブル名・フィールド名" を使うことで、数式が分かりやすくなるだけではなく、「データの増（減）に対応して、自動的にテーブルが広がる（縮まる）」特性を使うことで、表の大きさが変わっても、"参照範囲を修正"する必要がなくなります。

表を「テーブル化」して作業効率を上げる - 事務作業の省力化や資料作成に役立つ、（Excelの使い方を紹介

VLOOKUP関数で「表引き」する

　VLOOKUP関数を使って「表」から条件に合うものを抜き出す「表引き」は便利です。

　駐車料金を利用時間に合わせて一覧表から表示したり、「No.」を入力すれば所属部署が表示されるように設定すれば入力効率が上がります。

　しかし、情報（DATA）が固定され「表の大きさが変わらない」場合は問題ありませんが、「売上一覧」のように、日々情報が更新され「表の大きさが変わる」場合は、その都度VLOOKUP関数の引数「範囲（参照範囲）」を更新する必要があります。

　こんな場合は、参照範囲を「構造化参照」にすることで表の修正が不要になります。

「テーブル化された表」のデータを削除しても「テーブル名」は変わらない

　"テーブル化"してある「表」は、データを削除・追加しても「テーブル名」は変わりません。

　　  

入力中の「表」から表引きする際は「参照範囲」に注意

　VLOOKUP関数を使って表引きする際、「料金表」や「所属部署一覧」のように、あまり内容が更新されない（変化しない）「表」の場合は簡単です。

　しかし、「売上表」のように日々情報が更新される「表」を"表引き" する場合は、参照するデータの範囲が変わってしまいます。

　このように頻繁に表の大きさが変化する「表」で表引きするためには、関数を使ってVLOOKUP関数の引数「（参照）範囲」をデータの追加に合わせて自動的に更新するようにする必要があります。

「表」の変化に合わせて、参照範囲を手動で更新

　データを追加・削除に合わせて、VLOOKUP関数の引数「範囲」を手作業で修正するのは大変な作業です。　

　　

数式（関数）を使って、VLOOKUP関数の引数「範囲（参照範囲）」が自動更新するようにする

　引数「範囲（参照範囲）」が自動的に更新するようにする方法は複数あります。

　１．INDIRECT関数とCOUNT関数を組み合わせる
　２．OFFSET関数を使う
　３．テーブル化した表の "範囲が自動拡張する" 特性を使う

状況によって、使い分ける

　「１」と「２」の方法は、関数を使う方法ですが、「関数」を使う方法は「データが無い行（空白行）」がある場合は、それを回避する数式を追加する必要があり数式が複雑になってしまいます。

　「３」は関数を使わずに「参照する表」をテーブル化して"構造化参照"にする方法です。

　「構造化参照」にする方法は、参照範囲をセル（番地）ではなく「テーブル名」で指定します。

　「テーブル化された表」はデータを追加・削除しても、それに合わせてテーブル範囲が自動で更新されるので、参照範囲を「テーブル名」しておけば修正の必要がありません。

１．INDIRECT関数とCOUNT関数を使って、引数「範囲（参照参照）」を指定する

　COUNT関数で「表」の最後の行を探し、INDIRECT関数を使って引数「範囲」に使えるようにします。

　①  表の "最後の行番号" を、COUNT関数を使って算出

　②  最後の行番号に "＆" を使って列 "D" を付加して「セル番地の "文字列" 」にする 

　③  INDIRECT関数で "文字列" を引数に使えるように変換

※ "INDIRECT関数" で、指定した "文字列" を「セル番地」として使えるようにする

※  表に「データが無い（空白）のセル」がある場合には、 "COUNTBLANK関数" で空白のセル（行）を加えて、正しい「最終行」にします

"COUNT関数" で最後の行番号を算出する

　COUNT関数で「『表』にデータ（数値）が 何個 あるかをカウント」し、「その "数値" が "最後の行番号" 」になります。

　今回の例では、「表」の上に "１行空白の行" があり、それと"タイトル行" があるので "合わせて ２行下にシフト" する必要があるので、COUNT関数で算出した数値に "２" を加えた数値が「表の最後の行番号」になります。　　

　　

INDIRECT関数で"数値"をセル番地に変換する

　COUNT関数で求めた「最後の行の数値」と文字 "D" を「＆」で連結して「表の最後の "セル番地の文字列" 」を作成し、それをINDIRECT関数を使って引数として使える「セル番地」に変換します。

1. 引数「検索値」を設定
2. 引数「範囲」に表の左上のセル番地を指定
3. 引数「範囲」に表の右下のセル番地を "INDIRECT関数" と "COUNT関数" を使って指定
4. 引数「列番号」で "２列目" を指定
5. 引数「検索方法」をFALSE（完全一致）に指定　　　

　　

※ VLOOKUP関数の引数「検索方法」について

　VLOOKUP関数の引数「検索方法」は、"FALSE（完全一致）" に指定します。

　「検索方法」を指定（引数を入力）しない場合、検索方法は "TRUE（近似値を含む）" になります。

　今回の検索対象「番号」のような場合、「A-1」と「A-10」のデータは "近似値" になり片方のみが表示されます。

※「データが無い（空白）セル」がある場合

　COUNTA関数は「データのあるセル」しかカウントしません。 

　空白のセルがある「表」の最終行を正しく指定するために、"COUNTA関数" の数値に"COUNTBLANK関数" の数値を加えて最後の行番号にします。

　

COUNTBLANK関数の、引数「範囲」には "構造化参照" を使います

　COUNTA関数は「データのある場所をカウントする」ので、範囲指定に「 D:D 」のように "行全体を指定" を使うことができました。

　COUNTBLANK関数は「データのない場所をカウントする」ので、"行全体を指定" することができません（※ 空白部分を全てカウントしてしまう）。

　そこで、「カウントする範囲」を "テーブル化" して「構造化参照」にします。　

COUNTBLANK関数の引数「範囲」には、「構造化参照」を使う

　COUNTBLANK関数は「空白のセル」をカウントする関数なので、引数「範囲」に "列全体" を指定する方法（ D : D ）が使えません。

　「 D3：D12」のように、直接セル番地を指定すると、データが増えるたびに修正が必要になってしまいます。

　この問題を回避するためには「構造化参照」の仕組みを使います。

　引数「範囲」に指定する部分に "名前を付ける" 方法は複数あります。

　　A.　"範囲を指定" して "名前" を付ける

　　B.　"テーブル化" して "名前" を付ける

　２つの方法の違いは、「名前」を付けた部分がテーブル化されるか、テーブル化した部分に「名前」を付けるかの違いなので、「結果は同じ」です、使いやすい方を使って問題ありません。

セルに「名前」を付けると何が便利になるのか（その１） - Excelの機能を活用して、事務作業の省力化や経営分析をする

A．表の範囲を指定して名前を付ける

　表の「金額」の部分を範囲指定して名前を付けます。

　名前を付けた "範囲" は、自動的にテーブル化されます。

　「表」の一部に "名前を付ける" には、「選択範囲から作成」の機能を使います。

1. 表の「金額の項目全体」を範囲選択
2. 「数式」タブを選択
3. 「選択範囲から作成」をクリック
4. 選択範囲から名前･･･のダイアログで「上端行」にチェックを入れる
5. 「OK」ボタンをクリック

　　

「金額」の範囲の名前が「金額」になりました

　「金額」の項目のデータ部分の範囲" に、名前「金額」が付きました。

　この範囲は「テーブル化」されているので、データが増・減しても "範囲が自動的に更新" されます。

　　

B．範囲をテーブル化して名前を付ける

　こちらの方法も、手順は「１」と同様に "範囲" を選択してから作業することになります。

　範囲の「名前」は、先頭行（項目名）になります。

1. 「金額」の範囲を選択する
2. 「挿入」タブを選択
3. テーブルの作成ダイアログで、「先頭行をテーブルの見出しとして･･･」にチェックを入れる
4. 「OK」ボタンをクリック

　　

※ 数式の中に、「範囲に付けた名前」を入力する方法

　「数式の中に範囲に付けた名前を入力」するときに、"手入力" で入力しても問題ありませんが、F3キーを押して「名前の貼り付けダイアログ」を表示させ、その中から選択する方法で入力すれば入力ミスを防ぐことができます。

　　

２．OFFSET関数を使う

　OFFSET関数を使えば、「基準のセル」から "高さ" と "幅" を指定することで"範囲指定" ができます。

　データの増減で変化してしまう、引数「高さ」は、COUNTA関数を使って算出します。

OFFSET関数の引数「高さ」をCOUNTA関数を使って算出します

　OFFSET関数の引数「高さ」をCOUNTA関数を使って算出します。

　今回はOFFSET関数の引数「参照」に「表の左上のセル番地」を直接指定したので、引数「行数」と「列数」は "０" にして縦・横どちらにもシフトしません。

1. VLOOKUP関数の引数「検索値」を指定
2. OFFSET関数の引数「参照」を "表の左上" に指定
3. OFFSET関数の引数「行数」と「列数」は "０" にします
4. OFFSET関数の引数「高さ」をCOUNTA関数で「D列」のデータの数を求め、算出
5. OFFSET関数の引数「幅」を "３" にして、表全体を指定します　　

　　

※「データが無い（空の）セル」がある場合　

　　

３．"テーブルの範囲が自動拡張する" 特性を使う

　関数を使わなくても「表」を "テーブル化" することで、参照範囲が自動的に拡張するようになります。

　"テーブル化" された表は 「縦方向・横方向（隣接が条件）に拡張」してもテーブルの範囲は自動拡張されます。

　テーブルに付けた "名前" を引数「（参照）範囲」にすることで自動拡張されます。

表を「テーブル化」して作業効率を上げる - Excelの機能を活用して、事務作業の省力化や経営分析をする

「構造化参照」でVLOOKUP関数の修正を不要にする - Excelの機能を活用して、事務作業の省力化や経営分析をする

「表」をテーブル化します

　"テーブル化" の操作は範囲を指定して作成することもできますが、「表」として作成（※ 周囲のデータとの間に「空白」がある状態）してあれば、その表の中のどこか１つのセルを選択状態してに実行すれば「自動的に表全体が "テーブル" として認識」されます。

1. 表内の「どこか１カ所のセル」を選択します
2. "挿入タブ" を選択します
3. "テーブル" をクリック
4. テーブルの作成ダイアログで、テーブルの範囲が正しく選択されているのを確認します（※ デフォルトで正しく選択されているはず）
5. 「先頭行をテーブルの見出しとして･･･」にチェックを入れます
6. 「OK」ボタンをクリックします

　　

テーブル名は、自動的に「テーブル１」が付けられます

　作成した "テーブル" には、初期設定では「テーブル１」、「テーブル２」･･･のように作成順に名前が付けられます。

　　

テーブル名を「売上表」に変更します

　テーブル化した"範囲" を選択すると表示される「テーブルデザイン」タブで、テーブル名を変更します。

1. テーブル化した範囲を選択します
2. テーブルデザインタブを選択
3. テーブル名のボックスに「売上表」を入力（※ 上書き）

　　

テーブルが自動的に拡張される

　「表」に隣接するセルに、データを入力するとテーブルの範囲が自動的に拡張されます。

　　

VLOOKUP関数の引数「範囲」を"テーブル名"にします

　VLOOKUP関数の引数「範囲」を、「 B2:D13 」の範囲に付けられた "テーブル名 " の「売上表」を使って "構造化参照" にします。　

　　

　VLOOKUP関数の引数「範囲」が自動拡張になる方法は３種類

　１．INDIRECT関数を使う
　２．OFFSET関数を使う
　３．"テーブルの範囲が自動拡張する" 特性を使う

"１"と"２"の方法は、関数を使って「表の最終行」を算出する方法です。

　この方法は、数式を設定しておくだけで誰でも利用することができます。

 　この２つの方法の欠点は、COUNT(COUNTA)関数でデータの個数をカウントして最終行を調べるので、データが無い（空白の）セルがあると正しく最終行を特定することができなくなる点です。

　この問題はCOUNTBLANK関数を使うことで回避できますが、範囲の指定には「構造化参照（テーブル化が必要）」にする必要があります。

　どうせ「テーブル化」するのであれば、"３"  の方法を使う方がよいかもしれません。

"３" のテーブル名を使う方法は、関数を使わず「テーブル範囲が自動拡張する」特性を利用して、データの増減に対応する方法

　"３" のテーブル名を使う方法は、VLOOKUP関数の数式が複雑にならず簡単ですが、「テーブル化」の仕組みを理解している必要があるので複数の人が使う場合には注意が必要です。

　"１" と "２" の関数を使う方法だけを比較すると、"１" のINDIRECT関数の方が "２" のOFFSET関数より仕組みを理解しやすい点と、数式が簡単になるので使いやすいかもしれません。

k-ohmori9616.hatenablog.com