2014/10/28

[Excel] テーブルのすすめ 入力規則

Office 2007 以降で最も重要な機能追加は「テーブル機能」です。
テーブル機能を使えば、行数が増えても、列番号が変わっても、範囲指定しなおすことなく、参照を継続できます。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 コメント:

コメントを投稿

Powered by Blogger.

自己紹介


PowerBI コミュニティ勉強会の 沼口 です。
https://powerbi.connpass.com/
最近の Excel は Office 365 のクラウドサービスと 連携する方向性が打ち出されています。この「Road to Cloud Office」ブログでは、Excel ユーザーの視点から Power BI Service や、Office 365 の活用方法を模索した結果をお伝えしています。
Microsoft MVP for Data Platform 2017-2018