本記事には広告（PR）が含まれます。
くわしくは プライバシーポリシー をご確認ください。
ExcelのFILTER関数を使うと、指定した条件に合うデータだけを動的に抽出できます。
この抽出結果をドロップダウンの選択肢として利用すれば、「都道府県を選ぶと、該当する市区町村だけが表示される」といった連動入力も作成できます。
FILTER関数の数式そのものを入力規則へ直接入力することはできませんが、セル上に表示されたスピル範囲を参照すれば、VBAを使わずに連動ドロップダウンを設定できます。
また、選択内容を自動で消去したい場合や、複数行の入力規則を行ごとに切り替えたい場合は、VBAを組み合わせる方法もあります。
本記事では、都道府県と市区町村を例に、次の2つの方法を解説します。
- FILTER関数のスピル範囲を使うVBAなしの方法
- VBAで入力規則を細かく制御する方法
条件によって選択肢が変わる入力フォームを作成したい方は、ぜひ参考にしてください。
- プルダウン(入力規則)の設定方法については以下の記事にてご確認いただけます。
▶Excelプルダウンのコピー・複数選択・削除・色分けの方法まとめ｜連動や空白対応も解説！ - 申請書や発注書などでの活用例を知りたい方は、以下の記事もあわせてご覧ください。
▶Excel連動プルダウンの活用事例まとめ｜申請書・発注書で役立つ入力効率アップ術！
この記事でできること
- FILTER関数を使った連動ドロップダウンの仕組みを確認する
- VBAを使わずに連動ドロップダウンを作成する
- FILTER関数とスピル範囲の指定方法を確認する
- FILTER関数を複数条件の抽出に応用する
- VBAで選択済みの内容を自動で消去する
- 連動ドロップダウンが動かない原因を確認する
FILTER関数とドロップダウンを連動すると何ができる？
FILTER関数とドロップダウンを組み合わせると、最初に選んだ内容に応じて、次のドロップダウンに表示する選択肢を切り替えられます。
たとえば、次のような入力フォームに活用できます。
- 都道府県を選ぶと、該当する市区町村だけを表示する
- 商品カテゴリを選ぶと、該当する商品だけを表示する
- 部署を選ぶと、所属する担当者だけを表示する
本記事では、都道府県と市区町村を例に、FILTER関数で抽出した候補をドロップダウンに表示する方法を解説します。
なぜFILTER関数が便利なのか？
これまでのExcelでは、こうした「条件によって選択肢を変える」仕組みは、INDIRECT関数＋名前の定義 などで実現されてきました。
ただ、この方法は分類ごとに名前を設定する必要があり、項目が多い場合は管理に手間がかかることがあります。
FILTER関数なら、次のようなシンプルな構文で動的なリスト抽出が可能です：
=FILTER(市区町村一覧[市区町村], 市区町村一覧[都道府県]=A2, "該当なし")これにより、一覧の更新や拡張にも強く、柔軟性の高い連動リストを構築できるようになります。
この章では主に「どんな場面で使えるか？」を紹介しましたが、
次章では、FILTER関数の基本構文について簡単に紹介していきます。
連動ドロップダウンで使うFILTER関数の基本
FILTER関数の基本構文
FILTER関数は、指定した条件に一致するデータだけを抽出する関数です。
基本構文は次のとおりです。
=FILTER(範囲, 条件, [一致しない場合の表示])例えば、A2セルで選択した都道府県に一致する市区町村を抽出するには、次の数式を使用します。
=FILTER(市区町村一覧[市区町村],市区町村一覧[都道府県]=A2,"該当なし")A2セルで「北海道」を選ぶと、市区町村一覧テーブルの［都道府県］列が「北海道」と一致する市区町村だけが一覧表示されます。
元データや条件が変わると抽出結果も自動で更新されるため、選択肢が増減するリストの作成に向いています。
※FILTER関数やスピル範囲を使うには、Microsoft 365やExcel 2021以降など、動的配列に対応したExcelが必要です。
▶Excel FILTER関数の使い方まとめ｜複数条件・別シート・使えない時の対処法も解説！【事務作業がグッと楽に】
VBAなしでFILTER関数とドロップダウンを連動する方法
ここでは、都道府県を選択すると、該当する市区町村だけが2つ目のドロップダウンに表示される仕組みを作成します。
今回の設定内容は次のとおりです。
- A2セル：都道府県を選択するドロップダウン
- B2セル：市区町村を選択するドロップダウン
- 地域一覧シート：都道府県と市区町村の元データ
- 地域一覧シートのF2セル：FILTER関数の抽出結果を表示
元データを用意する
最初に、ドロップダウンへ表示する元データを用意します。
今回は、「都道府県」と、それぞれに対応する「市区町村」を2列に並べた一覧を使用します。
元データをテーブルに変換しておくと、市区町村を追加したときにもFILTER関数の参照範囲が自動で広がります。
1つ目の都道府県ドロップダウンを設定する
最初に、A2セルへ都道府県を選択するドロップダウンを設定します。
今回は、都道府県の一覧をテーブルに変換し、テーブル名を「都道府県一覧」としています。
- A2セルを選択して、［データ］タブから［データの入力規則］を開きます。
- 入力値の種類で［リスト］を選択し、［元の値］へ次のように入力します。
=INDIRECT("都道府県一覧") - ［OK］をクリックすると、A2セルで都道府県を選択できるようになります。
都道府県一覧を参照し、A2セルに1つ目のドロップダウンを設定します。
ドロップダウンの基本的な作り方については、以下の記事で詳しく解説しています。
▶Excelドロップダウンリストの基本とトラブル対処法まとめ
テーブル名や名前を文字列として参照するINDIRECT関数については、以下の記事をご覧ください。
▶エクセルのINDIRECT関数とは？参照先を文字列で指定する使い方と注意点まとめ
FILTER関数で選択肢を抽出する
次に、A2セルで選択した都道府県に一致する市区町村だけを抽出します。
今回は、地域一覧シートのF2セルに次のFILTER関数を入力します。
=FILTER(市区町村一覧[市区町村],市区町村一覧[都道府県]=住所入力!A2,"該当なし")この数式では、市区町村一覧テーブルの「都道府県」列から、住所入力シートのA2セルと一致するデータを探します。
一致した行の「市区町村」だけが、F2セルから下方向へ一覧表示されます。
たとえば、A2セルで「北海道」を選択すると、札幌市や函館市など、北海道に該当する市区町村だけが抽出されます。
スピル範囲を入力規則に設定する
次に、FILTER関数で抽出された市区町村の一覧を、B2セルのドロップダウンに設定します。
- B2セルを選択して、［データ］タブから［データの入力規則］を開きます。
- 入力値の種類で［リスト］を選択し、［元の値］へ次のように入力します。
=地域一覧!$F$2#
F2は、FILTER関数を入力したセルです。
セル参照の末尾に#を付けることで、F2セルから展開されたスピル範囲全体を参照できます。
市区町村の件数が増減した場合も、ドロップダウンに表示される範囲が自動で切り替わります。
都道府県を変更して動作を確認する
最後に、A2セルの都道府県を変更し、B2セルのドロップダウンに表示される市区町村が切り替わるか確認します。
たとえば、A2セルで「青森県」を選択すると、FILTER関数の抽出結果が青森県の市区町村に変わります。
B2セルのドロップダウンを開き、青森市、弘前市、八戸市などが表示されれば設定は完了です。
FILTER関数とスピル範囲の指定方法を確認する
FILTER関数で抽出した結果は、スピル範囲を参照することでドロップダウンの選択肢に利用できます。
ただし、入力規則の［元の値］に、FILTER関数の数式そのものを入力する方法では正しく設定できません。
たとえば、次の数式はワークシートのセルに入力して使用します。
=FILTER(市区町村一覧[市区町村],市区町村一覧[都道府県]=住所入力!A2,"該当なし")FILTER関数を地域一覧シートのF2セルに入力した場合、入力規則では次のようにスピル範囲を参照します。
=地域一覧!$F$2#末尾の「#」は、F2セルから展開された結果全体を参照するための記号です。
つまり、
- FILTER関数：条件に合う市区町村をセル上に抽出する
- 入力規則：抽出されたスピル範囲をドロップダウンとして参照する
という役割分担になります。
FILTER関数の数式を入力規則へ直接設定するのではなく、数式を入力した先頭セルに「#」を付けて指定することがポイントです。
Microsoftも、動的配列の結果全体は、先頭セルにスピル範囲演算子「#」を付けて参照できると説明しています。
▶Spilled range operator |Microsoft Support
FILTER関数を複数条件に応用する方法
FILTER関数を複数条件で絞りこむ方法は2通りあります。
- 複数の条件すべてに一致するデータを抽出する（AND条件）
- いずれかの条件に一致するデータを抽出する（OR条件）
それぞれの方法について紹介します。
複数の条件すべてに一致するデータを抽出する
AND条件を設定するときは、(条件1)*(条件2)のように掛け算で記述します。
図の例ではA3セルで指定した都道府県に一致し、市区町村名にB3セルの文字を含むデータを抽出しています。
=FILTER(市区町村一覧[市区町村],(市区町村一覧[都道府県]=A3)*ISNUMBER(SEARCH(B3,市区町村一覧[市区町村])),"該当なし")
いずれかの条件に一致するデータを抽出する
OR条件を設定するときは、(条件1)+(条件2)のように足し算で記述します。
図の例では東京都または大阪府の市町村を抽出しています。
=FILTER(市区町村一覧[市区町村],(市区町村一覧[都道府県]=F3)+(市区町村一覧[都道府県]=F4),"該当なし")
これらの数式で抽出された結果も、先頭セルに「#」を付けて入力規則へ指定すれば、複数条件に対応したドロップダウンとして利用できます。
VBAで連動ドロップダウンを細かく制御する方法
FILTER関数のスピル範囲を入力規則に設定すれば、VBAを使わなくても連動ドロップダウンを作成できます。
ただし、1つ目のドロップダウンを変更しても、2つ目のセルに以前選択した内容が残ることがあります。
たとえば、次のような状態です。
- 都道府県で「北海道」を選択する
- 市区町村で「札幌市」を選択する
- 都道府県を「青森県」へ変更する
- B2セルには「札幌市」が残ったままになる
ドロップダウンの選択肢は青森県の市区町村へ切り替わっていても、セルに入力済みの「札幌市」は自動では消去されません。
このような入力内容の不一致を防ぎたい場合は、VBAを使って、都道府県を変更したときにB2セルの内容を自動で消去できます。
VBAを使うと便利な場面
1組の連動ドロップダウンを作るだけであれば、VBAは必須ではありません。
一方、次のような処理を追加したい場合は、VBAを使うと便利です。
- 1つ目の選択肢を変更したときに、2つ目の入力内容を消去する
- 条件に一致する候補がない場合に、入力済みの値を削除する
- 複数行の入力内容を行ごとに制御する
- 特定のセルが変更されたときだけ処理を実行する
本記事では、前章で作成した連動ドロップダウンに、選択済みの市区町村を自動で消去する処理を追加します。
今回のVBAで行う処理
今回のセル配置は、VBAなしの手順と同じです。
| セル・シート | 内容 |
|---|---|
| 住所入力シートのA2セル | 都道府県を選択 |
| 住所入力シートのB2セル | 市区町村を選択 |
| 地域一覧シートのF2セル | FILTER関数の抽出結果 |
処理の流れは次のとおりです。
- A2セルの都道府県を変更する
- FILTER関数の抽出結果が切り替わる
- B2セルに入力されていた市区町村をVBAで消去する
- 新しい都道府県に対応した市区町村を選び直す
入力規則はすでに、
=地域一覧!$F$2#を参照しているため、VBAで入力規則そのものを毎回設定し直す必要はありません。
VBAコードを設定する
次のコードを「住所入力」シートのコード画面へ貼り付けます。
Private Sub Worksheet_Change(ByVal Target As Range)
' A2セル以外が変更された場合は処理しない
If Intersect(Target, Me.Range("A2")) Is Nothing Then Exit Sub
On Error GoTo ExitHandler
Application.EnableEvents = False
' 選択済みの市区町村を消去
Me.Range("B2").ClearContents
ExitHandler:
Application.EnableEvents = True
End Subこのコードでは、A2セルが変更されたときだけB2セルの内容を消去します。
入力規則は削除されないため、B2セルを選択すると、変更後の都道府県に対応した市区町村を引き続きドロップダウンから選択できます。
VBAコードの処理内容
| コード | 処理内容 |
|---|---|
Worksheet_Change |
シート内のセルが変更されたときに処理を実行 |
Intersect(Target, Me.Range("A2")) |
変更されたセルがA2かどうかを確認 |
Application.EnableEvents = False |
VBAによるセル変更でイベントが繰り返されるのを防止 |
Me.Range("B2").ClearContents |
B2セルに入力されている市区町村を消去 |
Application.EnableEvents = True |
イベント処理を元に戻す |
Application.EnableEventsを一時的に無効にしているのは、B2セルを消去したことで別のイベント処理が繰り返し実行されるのを防ぐためです。
コードを貼り付ける場所
Alt＋F11キーを押してVBAエディターを開く- 画面左側のプロジェクトから対象ブックを探す
- 「Microsoft Excel Objects」にある「住所入力」シートをダブルクリックする
- 表示されたコード画面へVBAコードを貼り付ける
「住所入力」シートのコード画面に、A2セルの変更を検知するイベントマクロを設定します。 - マクロ有効ブック形式で保存する
- Excelへ戻り、A2セルの都道府県を変更して動作を確認する
このコードは標準モジュールではなく、A2セルとB2セルがある「住所入力」シートのコード画面へ貼り付けてください。
マクロ有効ブックとして保存する
VBAコードを利用するファイルは、通常の.xlsx形式ではなく、次の形式で保存します。
Excel マクロ有効ブック（*.xlsm）.xlsx形式のまま保存すると、作成したVBAコードが削除されるため注意してください。
また、ファイルを開いたときにセキュリティ警告が表示された場合は、内容を確認したうえでマクロを有効にします。
VBAの動作を確認する
コードを設定したら、次の手順で動作を確認します。
- A2セルで「北海道」を選択する
- B2セルで「札幌市」を選択する
- A2セルを「青森県」へ変更する
- B2セルの「札幌市」が消去されることを確認する
- B2セルのドロップダウンに青森県の市区町村が表示されることを確認する
都道府県を変更したときにB2セルが空白になり、新しい市区町村を選択できれば設定は完了です。
複数行で利用する場合
商品明細や申請書など、複数行で連動ドロップダウンを使いたい場合は、変更されたセルの行番号を取得し、同じ行にある2つ目のセルを消去する処理が必要です。
複数行への対応は1組だけの場合よりコードが複雑になるため、まずはA2セルとB2セルの基本例で動作を確認してから応用しましょう。
VBAを使うときの注意点
VBAを設定したファイルは、マクロが無効になっている環境では動作しません。
また、Excel for the webではVBAを実行できないため、デスクトップ版Excelを使用する必要があります。
会社のパソコンなど、マクロの利用が制限されている環境では、VBAなしの連動ドロップダウンだけを利用してください。
FILTER関数は条件に合う選択肢を抽出し、入力規則はその結果をドロップダウンとして表示します。
さらにVBAを組み合わせると、以前選択した内容を消去するなど、入力ミスを防ぐための処理を追加できます。
FILTER関数とドロップダウンが連動しないときの確認ポイント
スピル範囲の先頭セルを指定しているか確認する
スピル範囲を入力規則へ設定するときは、FILTER関数を入力した先頭セルを指定します。
今回の例では、FILTER関数を入力しているのはF2セルです。
そのため、正しい設定は次のとおりです。
=地域一覧!$F$2#F3セルなど、スピル範囲の途中にあるセルを指定すると、元の値がエラーと判断されます。
=地域一覧!$F$3#エラーが表示された場合は、FILTER関数を実際に入力したセル番地を確認してください。
セル参照の末尾に「#」が付いているか確認する
入力規則の［元の値］で、FILTER関数を入力したセルだけを指定すると、先頭の1件しか参照されません。
抽出結果全体をドロップダウンに表示するには、セル参照の末尾に「#」を付けます。
=地域一覧!$F$2#FILTER関数がエラーになっていないか確認する
入力規則の参照先が正しくても、元になるFILTER関数がエラーになっていると、ドロップダウンへ候補を表示できません。
FILTER関数を入力したセルを確認し、#SPILL!や#CALC!などのエラーが表示されていないか確認しましょう。
#SPILL!が表示されている場合は、抽出結果が広がる範囲に文字や数式、結合セルなどがないか確認してください。
条件セルと元データが一致しているか確認する
FILTER関数の条件には、1つ目のドロップダウンを設定したセルを指定します。
今回は住所入力シートのA2セルを参照しているため、次のように設定しています。
=FILTER(市区町村一覧[市区町村],市区町村一覧[都道府県]=住所入力!A2,"該当なし")入力規則を設定したセルがA2以外の場合は、数式の参照先も実際のセル番地に合わせて変更してください。
都道府県名が元データと一致しているか確認する
1つ目のドロップダウンで選択した文字と、元データの都道府県名が一致していない場合は抽出できません。
たとえば、「北海道」と「北海道 」のように末尾へ空白が入っている場合、見た目が同じでも別の文字として判定されることがあります。
該当する候補が表示されないときは、元データに不要な空白や表記の違いがないか確認してください。
FILTER関数をテーブル内に入力していないか確認する
FILTER関数などの動的配列数式は、Excelテーブルのセル内ではスピルできません。
FILTER関数は、テーブルの外側にある通常のセルへ入力してください。
Microsoftも、スピルする動的配列数式はテーブル内部ではサポートされず、テーブル外のセルへ配置するよう案内しています。
▶Dynamic array formulas and spilled array behavior |Microsoft Support
FILTER関数とドロップダウンに関するよくある質問
FILTER関数とドロップダウンの連動にVBAは必要ですか？
1組の連動ドロップダウンを作成するだけであれば、VBAは必須ではありません。
FILTER関数で抽出した結果を、次のようにスピル範囲として入力規則に設定すれば、VBAを使わずに連動できます。
=地域一覧!$F$2#一方、1つ目の選択肢を変更したときに2つ目の入力内容を消去したい場合や、複数行を制御したい場合はVBAが役立ちます。
FILTER関数を入力規則の「元の値」に直接入力できますか？
FILTER関数の数式そのものを、入力規則の［元の値］へ直接入力する方法では正しく設定できません。
FILTER関数はワークシート上のセルに入力し、入力規則では数式を入力した先頭セルに「#」を付けて、スピル範囲全体を参照します。
=地域一覧!$F$2#スピル範囲を別シートに置いても連動できますか？
はい。今回の例のように、入力欄を「住所入力」シート、FILTER関数の抽出結果を「地域一覧」シートへ分けても連動できます。
入力規則の［元の値］には、シート名を含めて次のように指定します。
=地域一覧!$F$2#シート名に空白が含まれる場合は、シート名をシングルクォーテーションで囲みます。
='地域 一覧'!$F$2#FILTER関数の結果に重複した項目が表示される場合はどうすればよいですか？
重複を除きたい場合は、FILTER関数とUNIQUE関数を組み合わせます。
=UNIQUE(FILTER(市区町村一覧[市区町村],市区町村一覧[都道府県]=住所入力!A2,"該当なし"))さらに並べ替えたい場合は、SORT関数も組み合わせられます。
=SORT(UNIQUE(FILTER(市区町村一覧[市区町村],市区町村一覧[都道府県]=住所入力!A2,"該当なし")))VBAを設定したのにB2セルの内容が消去されません
次の点を確認してください。
- コードを標準モジュールではなく「住所入力」シートのコード画面へ貼り付けているか
- 都道府県のドロップダウンがA2セルに設定されているか
- 市区町村のドロップダウンがB2セルに設定されているか
- ファイルをマクロ有効ブック形式で保存しているか
- ファイルを開いたときにマクロを有効にしているか
セルの配置が異なる場合は、コード内のA2とB2を実際のセル番地へ変更してください。
FILTER関数や入力規則を、仕事で使える形まで学びたい方へ
FILTER関数や連動ドロップダウンは、一度仕組みを理解すると、名簿・発注書・入力フォームなど幅広い業務へ応用できます。
ただし、数式をそのまま覚えるだけでは、表の配置や条件が変わったときに対応できず、途中でつまずいてしまうこともあります。
Udemyの
「マクロ不要のExcel自動化術 ～上級者への数式・関数『烈火の書』」では、入力規則や条件付き書式、データ集計など、Excelを数式で自動化する考え方を動画で学べます。
「まずはマクロを使わず、関数でできることを増やしたい」という方は、講座内容を確認してみてください。
※本リンクにはアフィリエイトリンク（PR）が含まれます。
VBAを使った自動化を基礎から学びたい方は、以下の記事もあわせてご覧ください。
▶VBAを独学で学ぶには？初心者でもわかるマクロの勉強手順とおすすめ教材
FILTER関数のスピル範囲を使えば連動ドロップダウンを作成できる
FILTER関数を使うと、選択した条件に一致する候補だけを動的に抽出できます。
抽出結果をドロップダウンへ利用するときは、FILTER関数の数式を入力規則へ直接入力するのではなく、次のようにスピル範囲を参照することがポイントです。
=地域一覧!$F$2#1組の連動ドロップダウンであれば、この方法でVBAを使わずに作成できます。
一方で、
- 1つ目の選択肢を変更したときに、2つ目の入力内容を消去する
- 複数行の入力規則を行ごとに切り替える
- 条件に応じて入力規則そのものを設定し直す
といった処理を行いたい場合は、VBAを組み合わせると柔軟に制御できます。
連動しない場合は、FILTER関数を入力した先頭セル、末尾の「#」、元データの表記、スピルエラーの有無を確認してみてください。
まずはVBAなしの基本方法から試し、必要に応じてVBAによる自動化へ発展させましょう。
【関連記事】
- Excelドロップダウンリストの基本とトラブル対処法まとめ｜表示されない・複数列表示・行数制限など
- VLOOKUP×ドロップダウンリストの連携術｜商品名を選ぶだけで価格を自動表示！
- Excel連動プルダウンの活用事例まとめ｜申請書・発注書で役立つ入力効率アップ術！
※参考書籍※
- 【逆引き！Excel実務ワザ大全 増補新版】（Amazon）
※本ボックスにはアフィリエイトリンク（PR）が含まれます。
くわしくは プライバシーポリシー へ。