【Excel】関数だけで連動したプルダウンを作りたい

利用する機能と関数

今回の連動プルダウンを作るために利用する機能と関数です

ある程度の解説はしますがそれぞれ、単独では理解している前提で進めるのでご了承を

利用するシートの構成

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

“プルダウン”シート

実際に操作するシート

大区分→中区分→小区分と選択していけば、結果が抽出されるように設定しています

この各区分を連動させたいのが今回の本題

“リスト”シート

「大区分・中区分・小区分・結果」の組み合わせリスト

今回は「大区分：3パターン、中区分：6パターン、小区分：30パターン、結果：30パターン」でサンプルデータを作成しています

“マスタ”シート

実際は関数を置いているだけの中間シート

“リスト”シートの中に関数を置いてもいいんですが、別シートにして非表示しておくとツールとしてはきれいに見える気がします

作業手順

細々したところもなるべく書いていくので、ある程度分かっている方は飛ばしながら読んでください

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

これはやらなくてもいいんですが、後述する数式のわかりやすさとかもあるので名前を付けておきます

• 大区分（B3）：main
• 中区分（C3）：middle
• 小区分（D3）：minor

名前の管理は使い慣れると可読性の向上にもなって便利ですが、体感それほど認知されているイメージではないため適度に

“リスト”シートにあるリストをテーブルへ

ここまでが下準備的なところ

画像ではすでにテーブル化していますが、昨今Excelでリストを使うとなったらテーブルにしとけば楽になることが多々あります

一括で名前管理した状態になり可読性が上がりますし、名前管理したことでデータ量（行数）が変化しても数式内の参照範囲を修正する必要がない可変範囲になってくれることはありがたい限り

好みの問題ですが、デフォルトの「テーブル1」で設定される名前が長いし半角全角混ざって使いにくいので私は「TB」や「LIST」に変更しがちで、今回は「LIST」にしています

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

本当は入力規則の中でいい感じにプルダウン内容を制御したいところですが、肝になる”UNIQUE関数”と”FILTER関数”が入力規則内で使えないので外出ししているイメージ

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

大区分の関数

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

最初の区分なので”UNIQE関数”を使うだけ

全部につけてますが、”SORT関数”を使っておくとプルダウンもきれいになるの場合が多くお好みで

中区分の関数

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

連動するプルダウンを作るために必要な”FILTER関数”が追加

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

大区分の絞り込み条件は”プルダウン”シートで名前を付けたセルを指定します

小区分の関数

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

中区分の関数から第2引数の含むを複数条件にしてあげるだけで他は同じ

第2引数の”含む”を「()」の中に入れて「*」でつなげてあげれば複数条件の指定ができます

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

必須ではないんですが後々便利なのでセルに名前を付けておきます

“プルダウン”シートとセルが同じなので少しややこしくなりましたが、今回はこんな感じ

• 大区分（B3）：main_list
• 中区分（C3）：middle_list
• 小区分（D3）：minor_list

ポイントは関数を入力した単独セル（範囲じゃない）が名前の参照範囲なところ

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

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

“入力値の種類”を”リスト”にしてあげるとプルダウンになります

“元の値”を”カンマ区切りの文字”か”セルの範囲”にしてあげれば選択肢になりますが今回は”セルの範囲”形式で

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

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

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

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

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

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

歯がゆいところに手が届かない部分

冒頭、「歯がゆいところに手が届かない」としていた内容がこちら

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

とは言え、結果セルの関数を↑の通り大区分・中区分・小区分をそれぞれ条件としたFILTER関数で設定しておけば結果の抽出を間違えることはないので許容範囲内でしょうか

あとがき

Excelは新機能、新関数がどんどん追加されて汎用性が上がり続けてますよね

昔はプログラミング（VBA）も活用しないと実現できなかったことが標準機能でも再現できることが増えています（その分学習コストは上がりますが）

世の中”脱Excel”を掲げる製品が多く支持されるのもわかりますが、”Excelで十分”なケースや”小回りが利くExcelの方がむしろいい”ケースも多くみられるのでまだまだ現役の期間は長そうです

サンプルファイル

今回説明に使ったExcelファイルです

実際操作してみたほうがわかりやすいと思うので気になる方はどうぞ