データの入力規則で、「元の値」をリストにして、プルダウンリスト（ドロップダウンリスト）を利用している方は多いと思います。
「元の値」のリストは、Excel2007以前では、セル範囲での別シートを参照することができません。ですから参照範囲を名前で定義する必要があります。
今回はエクセルの名前の定義を利用して、データが増減してもメンテナンス不要（データ範囲や名前の定義を再設定する必要のない）の可変式リストの作成方法を2通り紹介いたします。
リストの範囲を名前の定義だけにするとデータの増減時にメンテナンスが必要
まず初めに、リストに利用するセルの範囲を選択し、セル範囲に名前を定義します。ここでは「果物」としています。
「プルダウンリスト（ドロップダウンリスト）を表示させたいセル（ここではA2～A9まで）を選択します。
そして「データ」「データの入力規則」をクリックします。
「データの入力規則」ダイアログボックスで、「入力値の種類」を「リスト」に、「元の値」に先ほど定義した「=果物」と入力します。
すると、下記のようにプルダウンリスト（ドロップダウンリスト）が表示されます。
ところが、リストを減らす際には問題ないのですが、リストを増やすと名前の再定義が必要になります。下図のように「レモン」という項目を増やすと、そのままでは「レモン」の項目はリストには出てきません。
名前の範囲を設定し直さないと、新しく追加した「レモン」の項目は表示されません。
名前の定義の範囲を変更するには、「数式」「名前の管理」をクリックします。
「参照範囲」をA9セルに再設定する必要が出てきます。
名前の参照範囲にOFFSET関数を利用する方法
そこで、リストの数に合わせて名前の「参照範囲」を自動的に変更する、可変式参照範囲を設定しておくことで、リストの数が増減しても、上述のような再設定は不要になります。
参照範囲を可変式にするためには、「OFFSET関数」と「COUNTA関数」を利用します。
OFFSET関数とは、もともと基準セルから行を設定した数だけ移動し、設定した数だけ列を移動させたセルの値を参照する関数です。
=OFFSET(基準セル,移動行数,移動列数,基準セルからの行数,基準セルからの列数)という式になります。今回は基準セルと、基準セルからの行数の2つを設定することで可変式参照範囲に名前を定義します。
そのために、基準セルはリストの最初A2に、移動行数、移動列数は0に、基準セルからの行数をCOUNTA関数で個数を調べて行数を指定するという式になります。ちなみに列数は省略できますが、今回は1にしておきます（理由は後述します）。行数と列数は省略できますが、省略した場合は1となります。
今回の場合は、下記のような数式を「参照範囲」に入力します。
「=offset(名前の定義!$A$2,0,0,counta(名前の定義!$A$2:$A$100),1)」
「名前の定義!」の部分はシート名です。基準セルからの行数は、「COUNTA」関数で、2行目から100行目までの個数をカウントし、プルダウンリスト（ドロップダウンリスト）にする行数を決定しています。その設定により、新たに追加した「レモン」が表示されます。
もちろん、削除にも対応しています。リストの数によって可変する、プルダウンリスト（ドロップダウンリスト）の完成です。
リストは横方向（行）でも可能
リストは横方向（行）方向でも可能です。
下のようなリストで、可変可能なリストを作成してみましょう。
「数式」「名前の定義」をクリックして、新しい名前を定義します。
一旦、「名前」を「種類_横」に、「参照範囲」を現在あるリストを範囲としました。
そして改めて、「参照範囲」に「=OFFSET(名前の定義横!$B$1,0,0,1,COUNTA(名前の定義横!$A$1:$IV$1))」と入力します。
先ほどは、行数をカウントしましたが、今回は列数でリストの個数をカウントしています。
チェックボタンをクリックして可変式参照範囲の完成です。
「データの入力規則」で、「入力値の種類」を「リスト」に、「元の値」に「=種類_横」を入力して、プルダウンリスト（ドロップダウンリスト）にリストが表示されるのを確認してください。
ちゃんとプルダウンリスト（ドロップダウンリスト）が表示されています。
もちろん、I列以降にデータを入力すると、プルダウンリスト（ドロップダウンリスト）にリストとして表示されます。
これが一番簡単！ テーブルとして定義する方法
実はこれが一番簡単な、可変式プルダウンリスト（ドロップダウンリスト）の作成方法かもしれません。
リストをテーブルとして定義する方法です。
プルダウンリスト（ドロップダウンリスト）にしたいリストを選択し、「ホーム」の「テーブルとして書式設定」をクリックします。そして適当な（お好みの）書式を選びます。
すると、「テーブルとして書式設定」ダイアログボックスが開きますので、「先頭行をテーブルの見出しとして使用する」にチェックを入れて「OK」です。
そして改めて、名前ボックスに「果物リスト」などの名前を付けます。
名前を入れて、Enterキーを押すと、「テーブル1」と表示されますが大丈夫です。
「名前の管理」で確認すると、ちゃんと「果物リスト」として名前が定義されています。
「入力シート」で、プルダウンリスト（ドロップダウンリスト）で表示させたいセルを選択して「データの入力規則」をクリックし、「元の値」に「=果物リスト」と入力して「OK」をクリックします。
以上で完了です。
例えば、テーブルとして書式設定したセルの下に2つくらいリストを追加してみます。
そして、入力シートで、プルダウンリスト（ドロップダウンリスト）を表示すると、先ほど追加した2つのリストが表示されているのが分かりますでしょうか。
もちろん、元のテーブルとして書式設定設定したリストから、行を削除してもリストから表示されなくなります。
OFFSET関数やCOUNTA関数を使用しなくてもいいので、直感的でわかりやすいかもしれません。
ただ欠点は、列をリストにしたい（横方向にリストを作る）場合には利用できないということです。
可変式プルダウンリスト（ドロップダウンリスト）を利用してデータ入力を連動させよう
以前、ドロップダウンリスト（プルダウンリスト）を4段階（4階層）連動させる方法を紹介しました。
このようなときに、リストの数が増えることを想定してOFFSET関数やテーブルとして書式設定を利用しておけば、リストが増えてもメンテナンス（再設定）不要で利用が可能な、可変式リストが役に立ちますね。
わかりやすく説明したつもりですが、不明な点があればコメントいただけたら幸いです。
コメント