前置き
ドロップダウン リストを使いたい時に変数用のシートに名前付きの範囲を作成し、データの入力規則でリストとして指定する運用は広く行われていることと思う。ここにデータを追加する場合、自然に考えれば末尾に追加したいところであるが、名前付き範囲は末尾にデータを追加してもドロップダウン リストには反映されないため、ドロップダウン リストに反映させるための余計なひと手間が必要になってしまう。
概要
データの範囲をテーブル化しておくと、データを末尾に追加してもドロップダウン リストに自動で反映されるよ、というTIPS。
検証
事前準備
ドロップダウン リスト用の値の一覧を2つ用意した。A列では $A$3:$A$5 の範囲に名前を付けており、B列では $B$2:$B$5 の範囲をヘッダーありのテーブルに変換している。
A列用のデータの入力規則は以下のように設定している。
B列用はこちら。テーブルを参照させる場合はINDIRECT関数を使わなければいけない点に留意。
それぞれ以下のようなドロップダウン リストになる。
値を末尾に追加
それぞれの一覧の末尾にデータを追加し、ドロップダウン リストの変化を確認してみる。
確認の前に、画面上で確認できる変化について触れておこう。青い線が囲んでいる範囲が $B$2:$B$5 から $B$2:$B$6 に拡張され、 「日本酒」のセルの右下に付いていた ┛ が「ウイスキー」のセルに移動している。これはテーブルの末尾にデータを追加した場合はオートコンプリートが働き、自動でテーブルが拡張されたためである。
本筋に戻って、A列のドロップダウン リストの値を確認してみる。名前として定義した範囲が $A$3:$A$5 なので、この範囲の外で起こるデータの増減はリストに反映されていない。対策として、範囲の間に行を追加してデータを挿入（＋必要であればデータの順番を修正）している人が多いのではないだろうか。
一方のB列はテーブルの自動拡張の恩恵を受け、追加した値がドロップダウン リストに反映されている。
考察
長らく使っていた名前付き範囲を使う手法は、「範囲の間に行を追加して～」というバッドノウハウを常に頭の片隅に置いておかなければならないという地味な負担があった。テーブルを使うことにより、データを末尾に追加していくという自然な運用を実現でき、バッドノウハウが不要になった。テーブルを使う利点は他にも数多くあるため、よく調べて使いこなせるようになろう。