Excelの連動プルダウンの作り方 4方式を比較

Excelの連動プルダウン(親の選択で子の選択肢が切り替わるドロップダウンリスト)を作りたいけれど、調べると作り方がバラバラで「結局どれを選べばいいの？」と迷うことはありませんか？

連動プルダウンの作り方には大きく分けて4つの方式があります
使うバージョンやデータの形によって向き不向きがあるので、1つの方式だけ覚えても「自分の環境では動かない」となりがちです

そこでこの記事では、連動プルダウンの定番4方式を横並びで比較して、状況に合わせて選べるようにまとめてみました
手順・数式・メリットとデメリット・対応バージョンを方式ごとに並べているので、まずは「方式の選び方」の比較表から見てもらえれば、どれを読めばいいかが分かるはずです

ジャベ雄

結論だけ先に言うと、新しいExcelなら FILTER 方式が一番ラクですよ

連動プルダウンとは 親の選択で子の選択肢が変わる仕組み

連動プルダウンとは、上の段(親)で選んだ内容によって、下の段(子)に出る選択肢が自動で絞り込まれるプルダウンのことです

たとえば「大区分」でフルーツを選ぶと、「中区分」にはフルーツの中身だけが出る
「大区分」を野菜に変えれば、中区分も野菜の中身だけに切り替わる
こういう動きを作るのが連動プルダウンです

「プルダウン」と「ドロップダウンリスト」は同じものを指します
この記事では検索でよく使われる「プルダウン」で統一して進めますね

基本 データの入力規則でプルダウンを作る3つの方法

連動の話に入る前に、土台となる普通のプルダウンの作り方を押さえておきます
連動プルダウンは、この基本の上に「絞り込み」を足したものだからです

プルダウンは「データ」タブ →「データの入力規則」を開き、[設定]タブの[入力値の種類]を「リスト」にして、[元の値]に選択肢を指定すると作れます
この[元の値]の指定の仕方で、次の3つの作り方に分かれます

作り方1 直接入力(カンマ区切り)

[元の値]に選択肢を半角カンマ区切りで直接打ち込む方法です

フルーツ,野菜,穀物

選択肢が少なくて、ほぼ増減しないときに向いています
項目を足したいたびに手で打ち直す必要があるので、変動するリストには向きません

作り方2 セル範囲を参照する

選択肢をどこかのセルに並べておき、[元の値]でそのセル範囲(例 A2:A9)を指定する方法です
見出し行は含めないのがコツです

Microsoft公式でも、別シートに入力したリストを参照する例が紹介されています
選択肢を別シートにまとめておけば、入力画面をすっきり保てます

作り方3 テーブルを参照する(自動更新)

選択肢のリストをあらかじめテーブル化しておく方法です
Microsoft公式は「データがテーブル形式なら、項目を追加・削除するとそのテーブルに基づくプルダウンが自動的に更新される」と案内しています

項目が増えたり減ったりするリストなら、この方法が一番ラクです
あとで説明する連動プルダウンでも、このテーブルが土台として効いてきます

ジャベ雄

リストを使うならテーブル化、これだけ覚えておくと後がラクです

連動プルダウンの主要4方式 手順と数式で比較

ここからが本題の連動プルダウンです
定番の4方式を、それぞれ手順・数式・メリットとデメリット・対応バージョンつきで見ていきます

• INDIRECT + 名前定義(定番・旧バージョンでも動く)
• テーブル + 構造化参照(自動更新・後述のFILTERと相性が良い)
• FILTER / UNIQUE のスピル方式(365・2021・2024向け・一番ラク)
• OFFSET + MATCH(可変範囲・旧バージョンでも動く)

方式1 INDIRECT + 名前定義(最も定番)

2段階の連動でいちばん広く使われている定番方式です
古いExcelでも動くので、環境を選ばないのが強みになります

=INDIRECT(A2)

INDIRECT は、A2に入っている文字列(例「フルーツ」)を名前として解釈して、その名前の範囲をリストに変えてくれます
親で選んだ言葉と同じ名前の範囲が呼び出される仕組みです

名前付き範囲はスペースや一部の記号が使えず、数字始まりも不可です
親の項目名に空白や記号が混じるデータでは、この方式は組みにくくなります
その場合は後述の FILTER 方式や OFFSET 方式が向いています

もう1点、INDIRECT は揮発性関数(シートを編集するたびに再計算が走るタイプ)なので、大量に使うと一般に動作が重くなることがあります
後で出てくる OFFSET も同じ性質なので、数を増やすときは頭の片隅に置いておくと安心です

方式2 テーブル + 構造化参照(自動更新)

リストをテーブルにして、テーブル名で範囲を参照する方式です
項目を追加するとテーブルの範囲が自動で広がるので、参照範囲の手直しが減るのが利点になります

ただしテーブル単体では「絞り込み」までは賄えません
連動(親で絞る)を実現するには、テーブルを土台にしつつ名前定義や FILTER と組み合わせる形になります
このあとの現行記事の手順は、まさにテーブルを土台にして FILTER で絞り込む作り方です

方式3 FILTER / UNIQUE のスピル方式(365・2021・2024向け)

新しいExcelで使える FILTER 関数と UNIQUE 関数を使う方式です
名前を大量に作らなくて済むうえ、3段階・4段階の連動にも強いのが魅力です

仕組みは、補助セル(作業用のセル)に絞り込みの数式を置き、その結果を入力規則で参照する流れです
子の選択肢を作る補助セルには、次のような数式を入れます

=UNIQUE(FILTER(子リスト範囲,親リスト範囲=親セル))

FILTER で「親セルと一致する行」を抜き出し、UNIQUE で重複を1つにまとめています
1段目(親)は =UNIQUE(親リスト範囲) だけで作れます

そして入力規則の[元の値]では、補助セルの番地に「#」(スピル範囲を指す記号)を付けて参照します
たとえば補助セルが F2 なら =$F$2# と書くと、スピルで広がった範囲全体を可変で参照できます

この「#」は、スピル(数式の結果が自動で複数セルに広がる動き)で増減する範囲をまるごと指してくれます
項目が増えても範囲が自動で追いかけてくれるので、手直しがほぼ要りません

FILTER・UNIQUE・スピル(#参照)の詳しい仕組みや対応バージョンは、別記事で1つずつ解説しています
初めてスピルに触れる方は、こちらを先に読むと数式の動きがつかめます

スピルとは何か(FILTER・UNIQUE・#参照の基礎)を解説した記事はこちら

FILTER・UNIQUE・SORT は Microsoft 365 / Excel 2021 / Excel 2024 / Excel for the web で使えます
Excel 2019以前では動かないので、古い環境が混ざる職場では INDIRECT 方式か OFFSET 方式の方が無難です

どのバージョンでどんな新関数が使えるかは、新機能をまとめた記事も参考にしてください

• Excel 2021の新機能と新関数まとめ(動的配列・XLOOKUP など)
• Excel 2024の新機能と新関数まとめ(TEXTSPLIT・VSTACK など)

方式4 OFFSET + MATCH(可変範囲)

カテゴリ順に並べたマスタから、OFFSET と MATCH(と COUNTIF)で必要な範囲だけを切り出す方式です
名前をカテゴリぶん作らずに済み、INDIRECT の名前ルールも回避できるのが持ち味です

考え方は、MATCH で「どの行から始まるか」を、COUNTIF で「何件あるか」を求め、その範囲を OFFSET で切り出す流れです
具体例で見たほうが分かりやすいので、セル番地を固定して書きます

マスタはA列にカテゴリ(親キー)・B列に子の値が並び、1行目が見出し・データはB2から始まるとします
親セルが A2、子の選択肢を作る数式はこうなります

=OFFSET(マスタ!$B$2, MATCH($A2, マスタ!$A:$A, 0)-2, 0, COUNTIF(マスタ!$A:$A, $A2), 1)

ここで引く2がコツです
MATCH は列の何行目か(見出しを1行目として数えた位置)を返しますが、OFFSET は基準セル(マスタ!$B$2)を0行目として数えます
見出し行ぶんと、この数え方のズレを合わせるので基準をデータ先頭(B2)に取るなら-2になります

引く数を間違えると、子リストの先頭が欠けたり隣のカテゴリが混じったりします
基準セルをどこに置くかで引く数が変わるので、マスタの作りに合わせて確かめてください

入力規則の可変リスト自体も、OFFSET と COUNTA を組み合わせると作れます
名前定義の参照範囲を次の式にしておくと、項目を足したとき手直しが要りません

=OFFSET($D$2,0,0,COUNTA($D:$D)-1,1)

方式の選び方 4方式の比較表で状況に合わせる

4方式を見てきましたが、自分の環境ではどれを選べばいいのか、最後に一覧で整理します
軸は「対応バージョン・自動更新・作りやすさ・向いている場面」の4つです

ジャベ雄

迷ったらこの表だけ覚えて、あとは使う方式の節を読めば大丈夫ですよ

テーブル + FILTER で実際に作る手順

ここからは、上の4方式のうち テーブルを土台にして FILTER で絞り込む 作り方を、実際の画面つきで作っていきます
3段階(大区分 → 中区分 → 小区分)の連動プルダウンを、最後に結果まで抽出する形です

新しい関数を使うので、Microsoft 365 か Excel 2021以降で動かしてください
細かいところもなるべく書くので、分かるところは飛ばしながら読んでもらえれば

使うシートの構成(3シート)

最下部に実際のファイルを置いておきますが、下記の3シート構成で作ります

• “プルダウン”シート:実際に操作するシート 大区分 → 中区分 → 小区分と選んでいくと結果が抽出される この各区分を連動させたいのが今回の本題です
• “リスト”シート:大区分・中区分・小区分・結果 の組み合わせリスト 今回は大区分3パターン・中区分6パターン・小区分30パターン・結果30パターンでサンプルを作っています
• “マスタ”シート:関数を置いているだけの中間シート “リスト”シートに関数を置いてもいいんですが、別シートにして非表示にしておくとツールとしてきれいに見えます

“プルダウン”シートの各項目セルに名前を付ける

これはやらなくてもいいんですが、後で出てくる数式の分かりやすさにつながるので名前を付けておきます

• 大区分(B3):main
• 中区分(C3):middle
• 小区分(D3):minor

名前の管理は使い慣れると可読性が上がって便利です
ただ体感ではそれほど知られていない機能なので、無理のない範囲で取り入れてもらえれば

“リスト”シートのリストをテーブルにする

ここまでが下準備です
最近のExcelでリストを使うなら、テーブルにしておくと後がラクになる場面が多くあります

一括で名前管理した状態になって読みやすくなりますし、データ量(行数)が変わっても数式内の参照範囲を直さずに済む可変範囲になってくれます
これはありがたいところです

好みの問題ですが、デフォルトの「テーブル1」という名前は長くて使いにくいので、私は「TB」や「LIST」に変えがちです
今回は「LIST」にしています

“マスタ”シートに関数を設置する

本当は入力規則の中でプルダウン内容を制御したいところですが、肝になる UNIQUE 関数と FILTER 関数が入力規則の中では使えません
そこで”マスタ”シートに外出しして置く、というイメージです

各区分の関数は少しずつ違うので、それぞれ解説します

大区分の関数

=SORT(UNIQUE(LIST[大区分]))

最初の区分なので UNIQUE 関数を使うだけです
今回は全部に付けていますが、SORT 関数を足しておくとプルダウンがきれいに並ぶ場合が多いのでお好みで

中区分の関数

=SORT(UNIQUE(FILTER(LIST[中区分],LIST[大区分]=main,"-")))

連動するプルダウンを作るのに欠かせない FILTER 関数が加わりました
第2引数の絞り込み条件には、”プルダウン”シートで名前を付けた main を指定しています

たまに勘違いされますが、第2引数の”含む”は第1引数の”配列”の範囲に含まれている必要はありません
範囲の行だけ合わせて別の列を指定しても、イメージ通りの絞り込みができます

小区分の関数

=SORT(UNIQUE(FILTER(LIST[小区分],(LIST[大区分]=main)*(LIST[中区分]=middle),"-")))

中区分の関数から、第2引数の”含む”を複数条件にするだけで他は同じです
“含む”を「()」の中に入れて「*」でつなぐと、複数条件の指定ができます

各関数のセルにも名前を付けておく

必須ではないんですが、後々便利なのでセルに名前を付けておきます
“プルダウン”シートとセル位置が同じなので少しややこしいですが、今回はこんな感じです

• 大区分(B3):main_list
• 中区分(C3):middle_list
• 小区分(D3):minor_list

ポイントは、関数を入力した単独セル(範囲ではない)を名前の参照範囲にしているところです

“プルダウン”シートに入力規則を設定する

ようやくプルダウンになる入力規則の設定です

[入力値の種類]を”リスト”にするとプルダウンになります
[元の値]は”カンマ区切りの文字”か”セルの範囲”を指定できますが、今回は”セルの範囲”形式で進めます

ここが最も重要なポイントで、マスタシートで名前管理した各数式セルを指定するんですが、範囲の後ろに「#」を付けるとスピル範囲を指すことができます

このおかげで”マスタ”シートに設定した関数が活きて、大区分を選ぶと中区分、中区分まで選ぶと小区分の絞り込み結果が連動するプルダウンになります

結果を抽出する関数を設定する

マスタシートで小区分を抽出する関数に、条件を1つ足すだけです

=SORT(UNIQUE(FILTER(LIST[結果],(LIST[大区分]=main)*(LIST[中区分]=middle)*(LIST[小区分]=minor),"-")))

リスト次第ではありますが、今回なら SORT 関数と UNIQUE 関数は不要です
簡易的には XLOOKUP 関数でも代用できそうなので、ここもお好みで

手が届かない歯がゆい部分

冒頭の方式比較でFILTER方式にも弱点はあると触れましたが、この作り方で言うと次のところです

小区分まで選んだ後に大区分・中区分を選び直したり、中区分を消したりしても、小区分に選んだ値が残ってしまいます
ここは関数だけではどうにもならない部分です

ただ、結果セルの関数を上のように大区分・中区分・小区分それぞれを条件にした FILTER で組んでおけば、結果の抽出を間違えることはありません
そう考えると許容範囲かなと思います

📚 VBAの独学に効く本PR

Excel VBA塾【動画×本で学ぶ!】

たてばやし淳

Amazonで見る楽天で見る

パーフェクトExcel VBA

高橋宣成

Amazonで見る楽天で見る

Excel マクロ&VBA［実践ビジネス入門講座］完全版 第3版

国本温子

Amazonで見る楽天で見る

私のおすすめからランダムで3冊を表示しています

別シートのリストを参照する方法

選択肢のリストを別のシートにまとめておきたいことはよくあります
別シートのリストを連動プルダウンに使う方法は、大きく3つです

• 直接範囲を指定する:シンプルだが、リストを増やすと範囲を手で直す必要がある
• INDIRECT + 名前定義:動的で柔軟、親の値から名前を呼び出して連動できる
• テーブル:シート名の変更に強く、項目を足すと自動更新される

堅さで選ぶならテーブルが安心です
シート名を変えても壊れにくく、項目の増減も自動で反映されます

連動プルダウンのよくある失敗と対処

連動プルダウンでつまずきやすいポイントと、その対処をまとめます
「絞り込みが効かない」「項目を足したのに反映されない」あたりが定番です

プルダウンの解除・編集・コピー

解除する

プルダウンを消したいときは、対象のセルを選んで「データ」タブ →「データの入力規則」を開き、[すべてクリア]を押します
複数のセルをまとめて解除したいときは、範囲を選んでから同じ操作をします

編集する

選択肢を変えたいときは、同じ「データの入力規則」のダイアログで[元の値]を直すだけです
連動側を変えるなら、参照している補助セルや名前の中身を見直します

入力規則だけをコピーする

設定済みのプルダウンを別のセルにも広げたいときは、「形式を選択して貼り付け」から「入力規則」を選ぶと、入力規則だけをコピーできます
値や書式を巻き込まずに済むので、表の体裁を保ったまま広げられます

まとめ 連動プルダウンは状況で方式を選ぶ

連動プルダウンの作り方を、4方式で比較してきました
「これが唯一の正解」という方式はなく、使うバージョンやデータの形で向き不向きが変わります

• 新しいExcel(365・2021・2024)なら FILTER 方式 がラクで3段階以上にも強い
• 古い環境が混ざるなら INDIRECT 方式 が無難
• 項目名に空白や記号が入るなら FILTER か OFFSET で名前ルールを回避

昔はVBA(プログラミング)を使わないと作れなかった連動も、今は標準機能と新関数だけで作れるようになってきました
その分だけ”Excelで十分”なケースも増えているので、まだまだ現役で頼れる存在です

ジャベ雄

まずは比較表で方式を決めて、あとは下のサンプルで動かしてみてくださいね

サンプルファイル

今回の説明に使ったExcelファイルです
実際に操作してみたほうが分かりやすいと思うので、気になる方はどうぞ

📚 VBAの独学に効く本PR

Excel VBA塾【動画×本で学ぶ!】

たてばやし淳

Amazonで見る楽天で見る

パーフェクトExcel VBA

高橋宣成

Amazonで見る楽天で見る

Excel マクロ&VBA［実践ビジネス入門講座］完全版 第3版

国本温子

Amazonで見る楽天で見る

私のおすすめからランダムで3冊を表示しています