Excelでリスト（データの入力規則など）使用時
項目が増えたり・減ったり・変わったりした時に、データの入力規則をいじって・・リストに反映して・・・ということや。
同じシートの欄外にリスト用のエリアを設けていたら、いつの間にか壊れていた・・・なんてことはないでしょうか。
或いは、それを回避するためにマスタシートを別に設けてみるも、項目の入れ替わりが多いと管理が面倒になったりなど。
ということで、関数のみで手っ取り早く、リストの管理から開放されましょう。
概要
-
マスタシートを設けます。
-
マスタシートに、それぞれマスタとなる項目を入力します。
-
項目は自由に増やしたり減らしたり変更することができます。
-
参照元（リストとなるセル）は何もしなくても選択肢が動的に反映されます！
※項目の変更については、既に入力済みのセルへの反映は行われません（あくまで選択肢のみ）
環境
手元にExcelがなかったので、GoogleSpredSheetで書いていますが、Excelで実現してください。
※GoogleSpredSheetでは同じことができません。
実装手順
mstシート
見た目はこのような感じで、マスタ用のシートを設けます。
器として、列は20列、行は50列用意しています。
初期設定
マスタシートの初期設定を説明します。
| セル | 項目 | 値 | 内容 |
|---|---|---|---|
| B1 | sheet | mst | シート名を入力します。 |
| B2 | 開始行no. | 5 | マスタの値を入力し始める行番号です。例では天体観測が入力されている行のことです。 |
これらは、マスタシートのシート名や、開始行が変更された際に手で変更します。
関数セル
列 @2行目
=LEFT(ADDRESS(ROW(),COLUMN(),2), FIND("$",ADDRESS(ROW(),COLUMN(),2))-1)
関数のあるセルの行絶対参照（e.g. A$1）の値から、"＄"までの文字列を抜き出す（つまり列名称）。
行数 @3行目
=COUNTA(D5:D54)
マスタの50項目までの行数をカウントします。
範囲 @4行目
=CONCATENATE($B$1,"!",D2,$B$2,":",D2,$B$2+D3)
初期設定値とマスタの入力情報を元に、マスタ項目の範囲を作成します。
運用
D列以降の1行目は、マスタの名称を入力します。これはただの見出しです。
そのマスタに対し、同列の5行目からリストの選択肢となる値を入力していきます。
マスタ参照元シート
- Excelで、マスタの項目を選択肢としたいセルを選択します。
- データの入力規則
- 入力値の種類：リスト
- 元の値：=INDIRECT(mst!D4)
これで、動的に反映されるリスト指定が完了です。
4.にて、INDIRECT関数を使用することで、対象セルの文字列をセル参照値として扱うので、結果マスタの項目を指定することになります。
例えば、mstシートの好きな歌列に、「カルマ」を追加します。
そうすると、入力規則を設定したセルの選択肢に、「カルマ」が追加されます。
以上、画像で例示できないのが残念ですが・・。
シートは置いておくので、xlsx形式でダウンロードして確認してみてください。