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

応用ワザ
この記事は約8分で読めます。

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

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

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

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

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

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

スポンサーリンク

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

エクセルでユーザーが選択肢を追加・削除・変更すると自動的に反映されるドロップダウンリストを作るには、選択肢を入力するセルをあらかじめ大きめに用意しておき、その範囲をOFFSET関数とCOUNTA関数で指定すると、選択肢の追加、削除、変更が誰でも簡単にできるようになります。

1 選択肢を入力するセル範囲を大きめに用意しておく。

2 プルダウンリストを設定したいセルを選択し、「データの入力規則」画面を開く。

3 「元の値」に次の計算式を入力する。

=OFFSET(選択肢を入力する範囲の先頭セル,0,0,COUNTA(選択肢を入力する範囲全体))

選択肢を追加・削除・変更しても自動的に反映されるドロップダウンリストを作るの図

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

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

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

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

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

ドロップダウンリストの図

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

スポンサーリンク
スポンサーリンク

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

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

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

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

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

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

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

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

スポンサーリンク
スポンサーリンク

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

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

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

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

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

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

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

スポンサーリンク
スポンサーリンク

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

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

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

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

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

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

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

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

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

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

スポンサーリンク
スポンサーリンク

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

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

=COUNTA(値1,値2,・・・,値255)

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

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

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

=COUNTA(選択肢を入力する範囲全体)

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

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

=OFFSET(基準, 行数, 列数, [高さ], [幅])

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

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

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

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

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

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

=OFFSET(選択肢を入力する範囲の先頭セル, 0, 0, COUNTA(選択肢を入力する範囲全体))

スポンサーリンク
スポンサーリンク

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

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

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

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

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

=OFFSET(E2, 0, 0, COUNTA(E2:E16))

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

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

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

スポンサーリンク
スポンサーリンク

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

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

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

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

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

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

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

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

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

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

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

スポンサーリンク
スポンサーリンク

一応の対応策

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

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

=IF(E2=””,””,row())

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

=IFERROR(INDEX($E$2:$E$16,MATCH(SMALL($F$2:$F$16,ROW()-1),$F$2:$F$16,0)),””)

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

=OFFSET(G2,0,0,15-COUNTIF(G2:G16,””))

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

スポンサーリンク
スポンサーリンク

まとめ

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

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

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

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