2014/10/28

テーブルのすすめ 入力規則

Office 2007 以降で最も重要な機能追加は「テーブル機能」である。
テーブル機能を使えば、行数が増えても、列番号が変わっても、範囲指定しなおすことなく、参照を継続できる。VLOOKUP 関数での利用はすでに紹介した。

実務で Excel ブックを「入力シート」や「申請シート」のように使っている企業・団体が多いが、そのようなシートをみて非常に残念なのは「データの入力規則」の「リスト - ドロップダウン リストから選択する」の機能を使っていて、このテーブル機能を活用していないブックが多いことである。(それも参照先にデータ入力させているのに「あとは自分で範囲指定しなおせ」的なものもある)

ドロップダウン リストから選択させている例

もちろん、これも OFFSET 関数と COUNTA関数を使うことでデータ件数の増減に対応できる。

この入力規則のドロップダウン リストにテーブルを活用していないブックが多い理由はいくつかある。そもそもテーブル機能を知らない、というのもあるが、この「データの入力規則」のダイアログボックスで構造化参照が直接使えない、というのも大きな理由であろう。




構造化参照を直接入力できないため利用を諦めているユーザーも多いかもしれない。

ただ、テーブルの範囲をデータの入力規則ダイアログから選択してリストの元データが絶対参照になっていても、絶対参照先が同じワークシートにあるテーブルである場合、行の増減に追随する仕様となっているのだ。

この場合は、列名を除いたすべてのデータを範囲として指定する。(列名を含めると当然列名もドロップダウン リストに表示されるため)



また、対象となるテーブルの列に「名前 (named range)」を付けることでも解決できる。
リスト参照の元データが別のワークシートにある場合はこの名前を利用する
対象列のタイトル行を除く全データを選択して名前入力ボックスを使って名前を付けるか、タイトル行を含む列の全データを選択して、[数式] タブ [選択範囲から作成] - [上端行] を使って名前を作成し、その名前を「データの入力規則」のリストの「元の値」で入力する。

重要なのはテーブルの行、列を名前として登録するとデータの増減に名前の「範囲」が追随することである。

以下は名前入力ボックスから名前を付け、データの入力規則の元の値に指定し、データを追加した操作である。


Excel の機能の多くはテーブルの構造化参照を使うことができるが、このように直接入力できずエラーになる場合は「名前」を付けて試してみよう。

なお、上で登録した「コースリスト」という名前は [数式] タブの [名前の管理] から確認できる。
名前の管理でコースリストの参照範囲を見ると「=courseTbl[コースリスト]」という構造化参照になっているのがわかる。この構造化参照に別名を名前でつけた、ということとなる。




[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