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