現役システムエンジニアが業務改善関連の「実施した講座の内容」や「実務で遭遇した課題と対処方法」などを掲載するブログです。2019年12月3日この記事では複数のプルダウン(入力規則のリスト)を片方の選択値に合わせて、もう片方の選択肢を変化させる(連動させる)方法を紹介します。セル範囲に名前を定義。具体的にはこのような例で利用します。地方のセルを選択すると都道府県の選択肢が変化します。簡単な方法(セル範囲名とINDIRECT関数)地方のプルダウン設定手順まずこのような地方と都道府県の一覧表を用意します。次に地方の入力セルを選択し、メニューの「データ」より「データの入力規則」を選択します。「元の値」に地方データのセル範囲を設定します。これによりプルダウンで地方を選択できる状態になります。都道府県を地方ごとにセル範囲名を設定します。それには都道府県を地方ごとに範囲選択します。空白セルは選択範囲から除く必要があります。その状態で数式バーの左にあるセル名欄に地方の名前を入力します。この名前は一覧表の地方名と一致する必要があります。これを地方の数だけ繰り返し、このような状態にします。セル範囲名の設定を間違えた場合は少し異なる手順が必要です。詳細はこちらの記事で紹介しています。設定内容を編集・削除したい場合はこちらを参照頂けると幸いです。セル範囲に名前を定義。都道府県の入力セルにプルダウンを設定します。都道府県の入力セルを選択し、メニューの「データ」より「データの入力規則」を選択します。入力値の種類を「リスト」に変更します。元の値に下記の数式を設定します。=INDIRECT(地方の入力セル)これにより地方を切り替えると都道府県の選択肢が変わる状態になります。(プルダウンメニューが連動)数式の説明(読み飛ばし可)INDIRECT関数は文字列のセル範囲名やアドレスをセル参照に変換する関数です。「元の値」に指定したセル範囲名を使用することは可能ですがセルを指定するとセル範囲でなくただの文字列として扱われます。それでは動作しないのでINDIRECT関数でセル範囲に変換する必要があります。高度な方法(OFFSET関数とMATCH関数)セル範囲を設定する方法との相違点(メリット・デメリット)先述のセル範囲の方法では地方ごとにセル範囲名を設定する手間があります。(連動元の選択肢の数だけ設定の手間)今回の地方の場合、9つのため莫大な手間とは言えませんが、もっと多いものとなると手間と設定を間違えるリスクが大きくなります。選択肢が増えたり変わる場合もセル範囲からやり直す必要があります。これから紹介する使用した方法ではそれらの問題が解決します。そのトレードオフで数式が複雑になり理解が難しくなります。・設定が簡単で分かりやすい。・選択肢が多いとセル範囲名設定の手間が膨大になる。・選択肢の変更があるとセル範囲名の設定変更が必要。・数式が複雑よってこれから紹介する方法は連動元プルダウンの選択肢が多い場合か頻繁に変更が発生する場合にのみ検討すべき方法になります。設定手順(連動先の選択肢が横方向の場合)地方(連動元)の設定はセル範囲のものと変わりません。都道府県(連動先)の入力規則の設定で下記のような数式を設定します。(色付きの箇所は各自カスタマイズして頂く箇所です)=OFFSET(一覧表の開始セル,MATCH(連動元のセル,連動元の選択肢のセル範囲,0)-1,0,1,連動先の最大選択肢数)今回の都道府県の例ではこうなります。=OFFSET($C$5,MATCH($C$2,$B$5:$B$12,0)-1,0,1,9)これによりプルダウンが連動する状態になります。この方法は都道府県(連動先)が横方向に並んでいる場合のみです。縦方向の場合、数式を変える必要があります。設定手順(連動先の選択肢が縦方向の場合)このような数式になります。=OFFSET(連動元のセル,0,MATCH(一覧表の開始セル,連動元の選択肢のセル範囲,0)-1,連動先の最大選択肢数,1)今回の都道府県の例ではこうなります。連動先の選択肢に不要な空白が発生します。これは最初の方法のセル範囲名では選択肢に空白を含まない範囲を選ぶことで空白を発生させないことが可能です。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)))次に選択肢が縦方向の場合の数式です。一覧表の開始セル,0,MATCH(連動元のセル,連動元の選択肢のセル範囲,0)-1,連動先の最大選択肢数,1)),1)今回の例ではこうなります。構造はさほど変わっておらずセル参照も同じものが2回呼ばれます。これは一度目のOFFSET&MATCHで連動元の選択肢に該当する個数をCOUNTA関数で算出し、(ここが太字の改修箇所)それを二度目のOFFSET&MATCHの取得件数に使用しています。これにより余計な空白を除外することが可能です。選択肢が空白セルなしに詰められている必要があります。プルダウンで不要な空白を表示させない方法セル範囲に名前を定義。セル範囲に名前を定義。コメントをキャンセルメールアドレスが公開されることはありません。*ウェブサイト上に表示された文字を入力してください。