Excel 複数のプルダウンを連動させる方法

2020年5月8日

この記事では複数のプルダウン(入力規則のリスト)を片方の選択値に合わせて、

もう片方の選択肢を変化させる(連動させる)方法を紹介します。

複数のプルダウンを連動させる手順

連動させるシートの例

具体的にはこのような例で利用します。

地方と都道府県のセルがあり、

地方のセルを選択すると都道府県の選択肢が変化します。

プルダウンで地方を選択
プルダウンで地方を選択すると
都道府県のプルダウンに選択した地方に対応する選択肢が表示される
都道府県のプルダウンに選択した地方に対応する選択肢が表示される
地方が変わると都道府県も変化
地方が変わると都道府県も変化

簡単な方法(セル範囲名とINDIRECT関数)

地方のプルダウン設定手順

まずこのような地方と都道府県の一覧表を用意します。

地方と都道府県の一覧表
地方と都道府県の一覧表

次に地方の入力セルを選択し、メニューの「データ」より「データの入力規則」を選択します。

メニューの「データ」、「データの入力規則」の位置
メニューの「データ」、「データの入力規則」の位置

次に「入力値の種類」より「リスト」を選択し、「元の値」に地方データのセル範囲を設定します。

「データの入力規則ウインドウ」の「入力値の種類」と「元の値」の位置
「データの入力規則ウインドウ」の「入力値の種類」と「元の値」の位置

これによりプルダウンで地方を選択できる状態になります。

プルダウンで地方を選択
プルダウンで地方を選択

都道府県のプルダウン設定手順

都道府県を地方ごとにセル範囲名を設定します。

それには都道府県を地方ごとに範囲選択します。

なお空白セルは選択範囲から除く必要があります。

その状態で数式バーの左にあるセル名欄に地方の名前を入力します。

この名前は一覧表の地方名と一致する必要があります。

地方に属する都道府県を空白を除きセル選択し、セル範囲名として選択している地方名を入力
具体的なセルの配置

これを地方の数だけ繰り返し、このような状態にします。

一覧表のセル範囲とセル範囲名の対応
一覧表のセル範囲とセル範囲名の対応

なおセル範囲名の設定を間違えた場合は修正する必要があります。

最後に都道府県の入力セルにプルダウンを設定します。

都道府県の入力セルを選択し、メニューの「データ」より「データの入力規則」を選択します。

都道府県の入力セルを選択し、メニューの「データ」より「データの入力規則」を選択

「入力規則」の画面を起動し、入力値の種類を「リスト」に変更します。

そして元の値にINDIRECT関数を利用し下記の数式を設定します。

=INDIRECT(地方の入力セル)
データの入力規則にINDIRECT関数を設定
「データの入力規則」ウインドウの設定例

これにより地方を切り替えると都道府県の選択肢が変わる状態になります。

(プルダウンメニューが連動)

プルダウンで地方を選択
プルダウンで地方を選択

数式の説明(読み飛ばし可)

INDIRECT関数は文字列のセル範囲名やアドレスをセル参照に変換する関数です。

「元の値」に指定したセル範囲名を使用することは可能ですが

セルを指定するとセル範囲でなくただの文字列として扱われます。

それでは動作しないのでINDIRECT関数でセル範囲に変換する必要があります。

高度な方法(OFFSET関数とMATCH関数)

セル範囲を設定する方法との相違点(メリット・デメリット)

先述のセル範囲の方法では地方ごとにセル範囲名を設定する手間があります。

(連動元の選択肢の数だけ設定の手間)

また選択肢が増えたり変わる場合もセル範囲からやり直す必要があります。

OFFSET関数MATCH関数を使用した方法ではそれらの問題が解決します。

しかしそのトレードオフで数式が複雑になり理解が難しくなります。

方法メリットデメリット
セル範囲名・設定が簡単で分かりやすい。・選択肢が多いとセル範囲名設定の手間が膨大になる。
・選択肢の変更があるとセル範囲名の設定変更が必要。
OFFSET関数MATCH関数・設定の手間が少なく、選択肢の変更でも作業が発生しない・数式が複雑

よってこれから紹介する方法は連動元プルダウンの選択肢が多い場合か

頻繁に変更が発生する場合にのみ検討する方法になります。

設定手順(連動先の選択肢が横方向の場合)

地方(連動元)の設定はセル範囲のものと変わりません。

都道府県(連動先)の入力規則の設定で下記のような数式を設定します。

(色付きの箇所は各自カスタマイズして頂く箇所です)

=OFFSET(一覧表の開始セル,MATCH(連動元のセル,連動元の選択肢のセル範囲,0)-1,0,1,連動先の最大選択肢数)

今回の都道府県の例ではこうなります。

=OFFSET($C$5,MATCH($C$2,$B$5:$B$12,0)-1,0,1,9)
OFFSETを利用した数式の図解(連動先のセル範囲が横方向)
OFFSETを利用した数式の図解(連動先のセル範囲が横方向)

これによりプルダウンが連動する状態になります。

ただしこの方法は都道府県(連動先)が横方向に並んでいる場合のみです。

縦方向の場合、数式を変える必要があります。

設定手順(連動先の選択肢が縦方向の場合)

このような数式になります。

=OFFSET(連動元のセル,0,MATCH(一覧表の開始セル,連動元の選択肢のセル範囲,0)-1,連動先の最大選択肢数,1)

今回の都道府県の例ではこうなります。

=OFFSET($C$5,0,MATCH($C$2,$C$4:$J$4,0)-1,9,1)
OFFSETを利用した数式の図解(連動先のセル範囲が縦方向)
OFFSETを利用した数式の図解(連動先のセル範囲が縦方向)

選択肢から不要な空白を削除したい場合

OFFSET関数MATCH関数では北海道のような選択肢の少ない連動元の場合、

連動先の選択肢に不要な空白が発生します。

北海道は1件のみのため多くの空白が発生
北海道は1件のみのため多くの空白が発生

これは最初の方法のセル範囲名では選択肢に空白を含まない範囲を選ぶことで

空白を発生させないことが可能です。

しかしOFFSET関数MATCH関数の例では連動先の最大選択肢数として

固定値(例は9)を入れた影響で全ての選択で空白を含めた9個の選択肢が

表示される状態になってしまいます。

これに対応するには入力規則に設定する数式をCOUNTA関数を入れて改造します。

ただし数式が更に長く複雑になるため、このままで問題ない場合は

そのままにしておくのもよいでしょう。

まず選択肢が横方向の場合の数式です。太字が数式の改修箇所です。

=OFFSET(一覧表の開始セル,MATCH(連動元のセル,連動元の選択肢のセル範囲,0)-1,0,1,COUNTA(OFFSET(一覧表の開始セル,MATCH(連動元のセル,連動元の選択肢のセル範囲,0)-1,0,1,連動先の最大選択肢数)))

今回の例ではこうなります。

=OFFSET($C$5,MATCH($C$2,$B$5:$B$12,0)-1,0,1,COUNTA(OFFSET($C$5,MATCH($C$2,$B$5:$B$12,0)-1,0,1,9)))
空白を除外する数式の図解(連動先のセル範囲が横方向)
空白を除外する数式の図解(連動先のセル範囲が横方向)

次に選択肢が縦方向の場合の数式です。

=OFFSET(一覧表の開始セル,0,MATCH(連動元のセル,連動元の選択肢のセル範囲,0)-1,COUNTA(OFFSET( 一覧表の開始セル,0,MATCH(連動元のセル,連動元の選択肢のセル範囲,0)-1,連動先の最大選択肢数,1)),1)

今回の例ではこうなります。

=OFFSET($C$5,0,MATCH($C$2,$C$4:$J$4,0)-1,COUNTA(OFFSET($C$5,0,MATCH($C$2,$C$4:$J$4,0)-1,9,1)),1)
空白を除外する数式の図解(連動先のセル範囲が縦方向)
空白を除外する数式の図解(連動先のセル範囲が縦方向)

数式が複雑になっていますが、構造はさほど変わっておらず

セル参照も同じものが2回呼ばれます。

これは一度目のOFFSET関数MATCH関数で連動元の選択肢に該当する個数を

COUNTA関数で算出し、(ここが太字の改修箇所)

それを二度目のOFFSET関数MATCH関数の取得件数に使用しています。

これにより余計な空白を除外することが可能です。

連動先の選択肢から不要な空白を除外した状態
連動先の選択肢から不要な空白を除外した状態

ただし選択肢が空白セルなしに詰められている必要があります。

選択肢の途中に空白が混ざっていると、空白削除不可
選択肢の途中に空白が混ざっていると、空白削除不可

このような状態になってしまうためこれが発生しないように

横方向なら左に、縦方向なら上に空白無しに詰めるようにしましょう。

関連記事

応用例

BMI判定をVLOOKUP関数で行う方法

入力規則によるプルダウン(ドロップダウン)リストの記事一覧