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

2019年9月23日

この記事では複数のプルダウン

(入力規則のリスト)を

片方の選択値に合わせて、

もう片方の選択肢を変化させる

(連動させる)方法を紹介します。

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

連動させるシートの例

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

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

地方のセルを選択すると

都道府県の選択肢が変化します。

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

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

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

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

地方と都道府県の一覧表のキャプチャ

次に地方の入力セルを選択し、

メニューの「データ」より

「データの入力規則」を選択します。

地方の入力セルを選択し、メニューの「データ」よりータの入力規則」を選択する図解

次に「入力値の種類」より「リスト」を選択し、

「元の値」に地方データのセル範囲を設定します。

データの入力規則に地方のセル範囲を設定する図解

これによりプルダウンで地方を

選択できる状態になります。

プルダウンで地方を選択する状態の図解

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

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

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

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

その状態で数式バーの左にあるセル名欄に

地方の名前を入力します。

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

地方に属する都道府県を空白を除きセル選択し、セル範囲名として選択している地方名を入力する状態の図解

これを地方の数だけ繰り返し、

このような状態にします。

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

なおセル範囲名の設定を間違えた場合は

少し異なる手順が必要です。

詳細はこちらの記事で紹介しています。

設定内容を編集・削除したい場合は

こちらを参照頂けると幸いです。

最後に都道府県の入力セルに

プルダウンを設定します。

都道府県の入力セルを選択し、

メニューの「データ」より

「データの入力規則」を選択します。

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

「入力規則」の画面を起動し、

入力値の種類を「リスト」に変更します。

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

=INDIRECT(地方の入力セル)
データの入力規則にINDIRECT関数を設定する図解

これにより地方を切り替えると

都道府県の選択肢が変わる状態になります。

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

プルダウンで地方を選択する状態の図解

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

INDIRECT関数は文字列のセル範囲名やアドレスを

セル参照に変換する関数です。

「元の値」に指定したセル範囲名を

使用することは可能ですが

セルを指定するとセル範囲でなく

ただの文字列として扱われます。

それでは動作しないので

INDIRECT関数でセル範囲に

変換する必要があります。

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

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

先述のセル範囲の方法では

地方ごとにセル範囲名を設定する手間があります。

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

今回の地方の場合、9つのため

莫大な手間とは言えませんが、

もっと多いものとなると手間と

設定を間違えるリスクが大きくなります。

また選択肢が増えたり変わる場合も

セル範囲からやり直す必要があります。

これから紹介する使用した方法では

それらの問題が解決します。

しかしそのトレードオフで

数式が複雑になり理解が難しくなります。

メリットデメリット
セル範囲名・設定が簡単で分かりやすい。・選択肢が多いとセル範囲名設定の手間が膨大になる。
・選択肢の変更があるとセル範囲名の設定変更が必要。
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(連動元のセル,0,MATCH(一覧表の開始セル,連動元の選択肢のセル範囲,0)-1,連動先の最大選択肢数,1)

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

=OFFSET($C$5,0,MATCH($C$2,$C$4:$J$4,0)-1,9,1)

OFFSETを利用した数式の図解(連動先のセル範囲が縦方向)

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

OFFSETとMATCHを使用する場合、

北海道のような選択肢の少ない連動元の場合、

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

連動先の選択肢に不要な空白が含まれる状態のキャプチャ

これは最初の方法のセル範囲名では

選択肢に空白を含まない範囲を選ぶことで

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

しかしOFFSETとMATCHの例では

連動先の最大選択肢数として

固定値(例は9)を入れた影響で

全ての選択で空白を含めた9個の選択肢が

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

これに対応するにはこの部分を改造します。

ただし数式が更に長く複雑になります。

このままで問題ない場合は

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

まず選択肢が横方向の場合の数式です。

太字は数式の改修箇所です。

=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関数で行う方法

使用した機能の詳細

プルダウンで不要な空白を表示させない方法

セル範囲に名前を定義。設定と編集・削除方法

文字列による柔軟なセル指定を行う(INDIRECT関数)

基準セルからの距離、高さと幅を指定してセルの内容を取得(OFFSET関数)

検索値に対応するセルの相対的な位置を取得(MATCH関数)

空白以外の件数を取得(COUNTA関数)

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

フォローする