Excel(エクセル)プルダウンリスト(ドロップダウンリスト)を可変式リストにすればリストの項目が増えても大丈夫! リストを名前の定義とテーブルにする2つの方法

スポンサーリンク

データの入力規則で、「元の値」をリストにして、プルダウンリスト(ドロップダウンリスト)を利用している方は多いと思います。

「元の値」のリストは、Excel2007以前では、セル範囲での別シートを参照することができません。ですから参照範囲を名前で定義する必要があります。

Excelで入力規則のリストボックス(プルダウンリスト)が表示されない?バージョン違いによる問題はリスト範囲に名前をつけることで解決! Offset関数を利用してを可変リストも作成可能 プルダウン可変式リストは超便利!
久しぶりのOffice(オフィス)記事です。先日、Excel(エクセル)365では、入力規則に設定されたリストボックス(ドロップダウンリスト)が表示されるのに、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列以降にデータを入力すると、プルダウンリスト(ドロップダウンリスト)にリストとして表示されます。

VBAを利用せずリストを予測候補表示(サジェスト機能)で絞り込む方法!エクセルのドロップダウンリスト(プルダウンメニュー)は便利なようで結構めんどい。。。
エクセルのドロップダウンリスト(プルダウンリスト)って、便利なようですがリストが増えると選択するのが大変なんですよね。入力候補が多数あると、入力したい値を探すのに一苦労です。入力規則があるとデータが多いときに選択が大変...

これが一番簡単! テーブルとして定義する方法

実はこれが一番簡単な、可変式プルダウンリスト(ドロップダウンリスト)の作成方法かもしれません。

リストをテーブルとして定義する方法です。

プルダウンリスト(ドロップダウンリスト)にしたいリストを選択し、「ホーム」の「テーブルとして書式設定」をクリックします。そして適当な(お好みの)書式を選びます。

テーブルとして書式設定

すると、「テーブルとして書式設定」ダイアログボックスが開きますので、「先頭行をテーブルの見出しとして使用する」にチェックを入れて「OK」です。

先頭行をテーブルの見出しとして使用する

そして改めて、名前ボックスに「果物リスト」などの名前を付けます。

改めてセル範囲に名前を定義

名前を入れて、Enterキーを押すと、「テーブル1」と表示されますが大丈夫です。

重複するセルの名前

「名前の管理」で確認すると、ちゃんと「果物リスト」として名前が定義されています。

名前の管理で確認

「入力シート」で、プルダウンリスト(ドロップダウンリスト)で表示させたいセルを選択して「データの入力規則」をクリックし、「元の値」に「=果物リスト」と入力して「OK」をクリックします。

元の値の設定

以上で完了です。

例えば、テーブルとして書式設定したセルの下に2つくらいリストを追加してみます。

リストを追加してみる

そして、入力シートで、プルダウンリスト(ドロップダウンリスト)を表示すると、先ほど追加した2つのリストが表示されているのが分かりますでしょうか。

追加したリストがメンテナンスフリーで表示される

もちろん、元のテーブルとして書式設定設定したリストから、行を削除してもリストから表示されなくなります。

OFFSET関数やCOUNTA関数を使用しなくてもいいので、直感的でわかりやすいかもしれません。

ただ欠点は、列をリストにしたい(横方向にリストを作る)場合には利用できないということです。

可変式プルダウンリスト(ドロップダウンリスト)を利用してデータ入力を連動させよう

以前、ドロップダウンリスト(プルダウンリスト)を4段階(4階層)連動させる方法を紹介しました。

エクセルのドロップダウンリスト(プルダウンリスト)を4段階(4階層)連動させる方法(365,2019,2016,2013,2010,2007対応)
以前、エクセルのドロップダウンリスト(プルダウンリスト)で、2007以前のエクセルでは、別シートの参照元を名前で定義しないとリストを取得できないという記事を投稿しました。今回は、結構需要が高いと思われる、ドロップダウンリス...

このようなときに、リストの数が増えることを想定してOFFSET関数やテーブルとして書式設定を利用しておけば、リストが増えてもメンテナンス(再設定)不要で利用が可能な、可変式リストが役に立ちますね。

わかりやすく説明したつもりですが、不明な点があればコメントいただけたら幸いです。

スポンサーリンク
Office・Windows
スポンサーリンク
リーダーの独り言

コメント

タイトルとURLをコピーしました