エクセルでユーザーが選択肢を追加・削除・変更すると自動で反映されるドロップダウンリスト

エクセルのドロップダウンリスト（プルダウンリスト）は、あらかじめ用意された選択肢から選んで入力できる機能で、入力の手間を省いたり、入力誤りを減らしたりすることができます。

そんなドロップダウンリストですが、選択肢を追加、削除、変更する場合は設定画面から再設定を行う必要があり、少々めんどくさいです。

この辺りについては、前回、選択肢の追加、削除、変更を楽に行う方法をご紹介しました。

しかし、これは、あくまでも自分が選択肢の追加、削除、変更をしやすくなるテクニックでした。

時には、選択肢を他の人に設定してもらいたいということもあるでしょう。例えば、アンケートのテンプレートを作成しておいて、他の社員に後から選択肢を自由に設定させるといった場合などです。

ということで、このページではエクセルでユーザーが選択肢を追加・削除・変更すると自動で反映されるドロップダウンリストをご紹介します。

スグにためセル！ – ここを読めばすぐ使える

※　より詳しい解説はここから下に続きます。

ドロップダウンリストとは

ドロップダウンリストとは、プルダウンリストとも呼ばれますが、あらかじめ用意された選択肢から入力する値を選択することができるようなリストのことです。

ドロップダウンリストが設定されているセルを選択すると、セルの右側に下向き三角ボタン「▼」が表示されます。

これをクリックすることで、ドロップダウリストが表示され、選択肢を選ぶことができるようになります。

ドロップダウンリストは、入力の手間を省いたり、入力誤りを減らしたりすることができる大変便利な機能です。

ドロップダウンリストの設定方法

ドロップダウンリストを設定するには、設定したいセルを選択し、「データ」タブから「データの入力規則」をクリックし、表示された画面で「入力値の種類」を「リスト」にし、「元の値」欄に選択肢を入力します。

なお、「元の値」欄にはセル範囲を入力することもでき、その場合は、選択肢をセルに入力することができます。こうしておけば、選択肢を変更したいときは、セルを変更するだけで反映されます。

選択肢をユーザーに設定させたい

そんなドロップダウンリストですが、選択肢の設定をユーザーにさせたい、ということがあります。

例えば、最初にも書いたようにアンケートや申請書などのテンプレートファイルを作っておき、後から選択肢を設定する場合などです。

そういうときは、選択肢をセルに入力しておく方法で設定しておけば、そのセルを変更するだけで、選択肢の変更ができるようになります。

しかし、この方法では次のような場合に問題が生じるのです。

選択肢を追加したい場合はどうすればいい？

今ある選択肢を変更する場合は、そのセルを直接修正するだけで変更ができるのですが、新しい選択肢を追加したい場合はどうすればよいのでしょうか。

下の図のように選択肢がE2セルからE7セルまで指定された状態で、新しい選択肢としてE8セルに入力したとしても、それは選択肢に反映されないのです。

これを解決するには、選択肢を入力させるセル範囲を大きめに用意しておけばいいのです。

例えば、E2セルからE16セルまでとしておけば選択肢が15個までには対応できます。

ちなみに、このセル範囲には列全体を指定しておくこともできます。

そうしておけば、どれだけ選択肢があろうと、入力欄が足りないということは起きないでしょう。

選択肢が少ない場合は空白が選択肢に含まれてしまう

これで選択肢が足りないということはなくなったのですが、この状態でもまだ問題があります。

ドロップダウンリストを設定したセルで選択肢を見てみてください。

すると、選択肢の中に空白スペースが含まれていますね。

これは、選択肢の範囲内に未入力のセルが含まれるためです。

E9セル以降には文字が入力されていないので、それが空白スペースとして選択肢に表示されてしまっているのです。

選択肢の数に応じて自動的に範囲を変える方法

これを解消するためには、選択肢の数に応じて自動的にセル範囲が変わってくれればいいのです。

エクセルにはセル範囲を答えとして返してくれるOFFSET関数があります。そして、選択肢の数を数えるには、COUNTA関数が使えます。

これら２つの関数を組み合わせて、選択肢の数に応じて自動的に範囲を変えてみましょう。

データが入力されたセルの数を返してくれるCOUNTA関数

COUNTA関数の構成は次のようになっています。

COUNTA関数は、文字も含めたデータが入力されたセルの数を数える関数です。

値１～値255には、セル範囲を入力することもできます。

今回は、選択肢の入力セルのうち、データの入力されたセルの数を数えますので、次のような計算式になります。

セル範囲を返してくれるOFFSET関数

次に、OFFSET関数ですが、次のような構成になっています。

OFFSET関数は、基準となるセルから、下に行数分だけ、右に列数分だけ移動したセルを先頭にして、高さ×幅のセル範囲を答えとして返します。

今回のように選択肢の範囲として設定する場合は、基準に、選択肢を入力する範囲のうち、先頭のセルを指定します。

行数と列数ですが、今回は、基準セルを先頭にしたいので、セルを動かす必要がありません。そのため、両方とも0と入力します。

次に、高さですが、この部分が選択肢の数に応じて変動してほしい部分になります。ということで、先ほどのCOUNTA関数を用います。

最後に幅ですが、これは１で固定となります。ちなみに、幅を省略すれば自動的に１となります。

以上をまとめると、次のような計算式になります。

実際にタメしてみましょう

それでは、下の図を例として実際にタメしてみましょう。

まず、選択肢を入力する範囲の先頭セルは、E2セルとなります。

次に、選択肢を入力する範囲全体は、E2セルからE16セルまでとなります。

以上を踏まえて、「データの入力規則」画面の「元の値」欄には、次のように入力します。

これで設定は完了です。この状態で選択肢を見てみると、下の図のように６個の選択肢が表示されています。そして、空白スペースは表示されていません。

これにバナナを追加してみます。メロンの下のE8セルに「バナナ」と入力してみましょう。

すると、下の図のように選択肢にバナナが追加されました。

選択肢の削除には注意が必要

次は、いちごとメロンを削除しましょう。

ここで注意ですが、削除する場合はセルそのものを削除してはいけません。

試しにセルそのものを削除してみると、下の図のように選択肢の入力欄が小さくなってしまいます。

そのため、選択肢を削除する場合は、Delキーを押して入力された文字を消すだけにしましょう。

しかし、中間にある選択肢を削除する場合にはさらに処理が必要です。

いま、いちごとメロンをDelキーで削除しただけですが、選択肢を見てみると、バナナが表示されていませんし、空白スペースが表示されています。

これは、上の計算式により選択肢の範囲を、E2セルを先頭にして、選択肢の入力されたセルの数を範囲としているのですが、選択肢の入力されたセルの数はこの時点で５ですので、その範囲がE2セルからE6セルとなるため、E8セルに入力されたバナナは表示されないということになります。

これを回避するためには、削除するときに間にスペースセルが含まれないようにする必要があります。

具体的には、スペースより下にあるセルを１つずつ入力し直すか、コピペして上にあげてあげる必要があります。（切り取りはNG）

この辺りは少しめんどくささが残りますが、しょうがない部分になります。

一応の対応策

このしょうがない部分ですが、一応対応することもできます。

F2セルからF16セルに次の計算式を入力します。（下の計算式は、F2セルに入力する内容です。F2セルに入力後、F16セルまでコピーしてください。）

次にG2セルからG16セルに次の計算式を入力します。（下の計算式はすべてのセルで同じです。）

そして、ドロップダウンリストの「元の値」を次のように入力します。

詳しい説明は省略しますが、このように設定しておけばE列の選択肢の入力欄に入力がある場合だけ、G列に表示されるようになります。そして、そのG列をドロップダウンリストの「元の値」に設定しているため、虫食いがあっても空白スペースが表示されないようになります。

まとめ

ドロップダウンリストの選択肢をユーザーに設定させるときは意外とあるものです。

また、自分で設定する場合でも、このページでご紹介した方法でドロップダウンリストを設定しておけば、次に選択肢を変更するときに便利になります。

以上、このページではエクセルでユーザーが選択肢を追加・削除・変更すると自動で反映されるドロップダウンリストについてご紹介しました。