テーブル機能を使えば、行数が増えても、列番号が変わっても、範囲指定しなおすことなく、参照を継続できます。VLOOKUP 関数での利用はすでに紹介しました。
実務で Excel ブックを「入力シート」や「申請シート」のように使っている企業・団体は多いですが、そのようなシートをみて非常に残念なのは「データの入力規則」の「リスト - ドロップダウン リストから選択する」の機能を使っていながら、このテーブル機能を活用していないブックが多いことです。（それも参照先に追加が発生するデータ入力をさせているのに「あとは自分で範囲指定しなおせ」的なものも多いです、、、）
もちろん、これも OFFSET 関数と COUNTA関数を使うことでデータ件数の増減に対応できます。
この入力規則のドロップダウン リストにテーブルを活用していないブックが多い理由はいくつかあると思われます。そもそもテーブル機能を知らない、というのもありますが、この「データの入力規則」のダイアログボックスで構造化参照が直接使えない、というのも大きな理由でしょう。
構造化参照を直接入力できないため利用を諦めているユーザーも多いかもしれません。
ただ、テーブルの範囲をデータの入力規則ダイアログから選択してリストの元データが絶対参照になっていても、絶対参照先が同じワークシートにあるテーブルである場合、行の増減に追随する仕様となっているのです。
この場合は、列名を除いたすべてのデータを範囲として指定します。（列名を含めると当然列名もドロップダウン リストに表示されるためです）
また、対象となるテーブルの列に「名前 (named range)」を付けることでも解決できます。
リスト参照の元データが別のワークシートにある場合はこの名前を利用します。
対象列のタイトル行を除く全データを選択して名前入力ボックスを使って名前を付けるか、タイトル行を含む列の全データを選択して、[数式] タブ [選択範囲から作成] - [上端行] を使って名前を作成し、その名前を「データの入力規則」のリストの「元の値」で入力します。
重要なのはテーブルの行、列を名前として登録するとデータの増減に名前の「範囲」が追随することです。
以下は名前入力ボックスから名前を付け、データの入力規則の元の値に指定し、データを追加した操作です。
Excel の機能の多くはテーブルの構造化参照を使うことができますが、このように直接入力できずエラーになる場合は「名前」を付けて試してみましょう。
なお、上で登録した「コースリスト」という名前は [数式] タブの [名前の管理] から確認できます。
名前の管理でコースリストの参照範囲を見ると「=courseTbl[コースリスト]」という構造化参照になっているのがわかります。この構造化参照に別名を名前でつけた、ということです。
テーブル関連の投稿
[テーブルとVLOOKUP] https://road2cloudoffice.blogspot.jp/2014/10/vlookup.html
[テーブルと入力規則] https://road2cloudoffice.blogspot.jp/2014/10/blog-post.html
[テーブルと集計行] https://road2cloudoffice.blogspot.jp/2014/10/blog-post_31.html
[テーブルと構造化参照] https://road2cloudoffice.blogspot.jp/2014/11/blog-post.html
[テーブルとピボット] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html
[テーブルとVBA] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html
[PR]VBAセミナー受講後は、これさえあれば何もいらない
0 コメント:
コメントを投稿