Excel 複数のプルダウンを連動させる方法
この記事では複数のプルダウン（入力規則のリスト）を片方の選択値に合わせて、
もう片方の選択肢を変化させる（連動させる）方法を紹介します。
複数のプルダウンを連動させる手順
連動させるシートの例
具体的にはこのような例で利用します。
地方と都道府県のセルがあり、
地方のセルを選択すると都道府県の選択肢が変化します。
簡単な方法(セル範囲名と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(連動元のセル,0,MATCH(一覧表の開始セル,連動元の選択肢のセル範囲,0)-1,連動先の最大選択肢数,1)
今回の都道府県の例ではこうなります。
=OFFSET($C$5,0,MATCH($C$2,$C$4:$J$4,0)-1,9,1)
選択肢から不要な空白を削除したい場合
OFFSET関数とMATCH関数では北海道のような選択肢の少ない連動元の場合、
連動先の選択肢に不要な空白が発生します。
これは最初の方法のセル範囲名では選択肢に空白を含まない範囲を選ぶことで
空白を発生させないことが可能です。
しかし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関数の取得件数に使用しています。
これにより余計な空白を除外することが可能です。
ただし選択肢が空白セルなしに詰められている必要があります。
このような状態になってしまうためこれが発生しないように
横方向なら左に、縦方向なら上に空白無しに詰めるようにしましょう。
Excel完全マスター: 関数・テクニック・新機能を網羅した決定版ガイド
本書は、Excelの基礎から応用まで、包括的に学べるガイドブックです。当サイトの豊富な情報を、電子書籍向けに最適化し、読みやすく再構成しました。
【本書の特徴】
- 情報量：Word-A4サイズ換算で1,400ページ相当
- 基本操作から高度なテクニックまで、段階的に学習可能
- 140種類の関数を詳細に解説
- 109種類の実用的なテクニックと具体例を紹介
- 最新機能（XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピル等）を徹底解説
【対象読者】
- Excel初心者からプロフェッショナルまで
- 体系的にExcelスキルを向上させたい方
- 業務効率化を目指すビジネスパーソン
- データ分析や可視化のスキルを磨きたい方
【本書の強み】
- 実務に即した例題と解説
- 視覚的な図表やスクリーンショットで理解を促進
- 最新のExcelバージョンに対応した内容
- 著者の長年の経験に基づく、実践的なTipsを多数収録
【更新とサポート】
- 常に最新の情報を反映するため、定期的に内容を更新
- 最新版の入手方法：Amazonカスタマーサポートへお問い合わせください
【入手方法】
- 買い切り：950円
- Kindle Unlimited：読み放題プランで利用可能
Excelマスターを目指す方、業務効率を劇的に向上させたい方に、自信を持っておすすめできる一冊です。この1冊で、Excelの可能性を最大限に引き出す力が身につきます。