【メモ】Excelの入力規則（リスト）における、構造化参照

やりたい事

入力規則としてリストを設定し、そのリストは動的変化に自動対応させたい。

テーブルと名前定義の二重構造

リストにしたい範囲をテーブル（ListObjectオブジェクト）として定義する。

そのまま構造化参照、たとえば=テーブル1[列1]などとするとエラーが出るので、列のデータ範囲（ListColumnオブジェクトのDataBodyRangeプロパティの部分）に名前をつけておき、それをリストに設定する。

たとえば、テーブルの列データ範囲をリストとしたい場合。

• リストにしたい範囲が列として含まれるテーブル：テーブル1と定義
• 列の見出し以外の範囲に名前をつける：リストと定義
• 入力規則に設定する：=リスト

INDIRECT 関数を使って構造化参照

前述の方法は、やり方が簡単で、入力規則の定義も簡潔ではあるが、列のデータ範囲に被せるように名前をつけるという点で、冗長。

こちらは、リスト定義において、INDIRECT関数を用いて、構造化参照する。

前節と同様にテーブルを作り

• テーブル名：テーブル1
• 列見出し：列1

とする（デフォルトの名前）。そのまま入力規則で、=テーブル1[列1]とするとエラーが発生する。

なので、INDIRECT関数を用い、=INDIRECT(“テーブル1[列1]”)とする。そうすると成功となる。

このままだと、列名が変更された場合、柔軟に対応出来ない（文字列を直接指定しているから）。

そこで、リストにしたいテーブルを、1列で構成する。
その上で、入力規則設定を、=INDIRECT(“テーブル1[#データ]”)とすれば、列見出しが変更されても影響が無い。
この設定であれば、セル参照を用いていないため、テーブルを別シートに移動しても破綻しない。

[#データ]の部分は項目指定子。[#すべて]とすると、列見出しそのものがリストに属してしまうため、気をつける。

リストは一次元配列で成るから、上記設定のまま、テーブルの列数を増やすと、リストが崩れてしまう。また、列を増やした状態で、そのまま同じ入力規則を設定すると、エラーが発生する。

比較

そもそも入力規則に適用するリストであるから、1列の範囲で定義するのが良いと思う。テーブルと名前定義の方法（最初のほう）なら、2列以上のテーブルのそれぞれの列をリストに設定出来るが、リストの要素数がテーブルの行数と同じになるため、リストの要素も同数で無ければ上手くいかない。つまり、要素が少ないほうの列をリストにすると、そのリストに空白要素が入る。と言って、少ないほうの列の、データがある部分の範囲だけに名前をつけると、今度はリストが動的にならない。

INDIRECT を使用する方法は汎用的だが、名前定義のほうが簡便。また、名前定義のほうは、テーブルの名前を変更しても破綻しない。INDIRECT は、文字列を直接指定するから、そこを変更する必要がある。尤も、テーブル名やフィールド名をコロコロ換えるような運用をするな、という話ではある。