Excelの入力規則による、連動ドロップダウンリスト――シンプルバージョン

TAKESAN

2024年8月17日 20:02

Excelのドロップダウンリスト

入力規則のリスト機能

Excelには入力規則に、複数の選択肢から選んでそれを値として入力するという、ドロップダウンリストの機能があります。

これはとても便利な機能で、手入力によるミスを防いだり、リスト以外の値の入力を禁止したり出来ます。

選択肢が多くなったら

このように便利な機能ですが、入力出来る候補が多くなると、リストも長くなって、入力したいものを探しにくい場合があります。

連動ドロップダウンリスト

そこで、選択肢を何らかのカテゴリーに基づいて分類し、まずそれを選択させて、選択した分類に応じて、それに含まれる値のリストを作るという
連動したドロップダウンリスト
があれば便利です。

このように便利な連動リストですが、これをExcelで実現するのは、すんなりとは行きません。皆さん、色々の方法を使って工夫を凝らし、この機能を実装なさっています。この記事では、比較的シンプルな構成のマスター（リストの元になるデータ）から多段リストを作成する方法を紹介します。

連動ドロップダウンリストの実装

前提

この記事では、主に下記の知識を前提とします。

入力規則のリストの設定が解る
参照を使ったリスト作成が解る
テーブル化と構造化参照が解る
INDIRECT関数による参照が解る

不明な場合は、下記の記事を参照ください。

リストのマスター

ドロップダウンリストを作成する、つまり選択するもととして、都道府県と地域区分を用います。

区分は下記リンク先を参照しました。古いので、今の区分と同じか判りませんが、この記事はリストを作成するのが目的ですので、そのまま使います。

マスター表の変更

今は連動のリストを作成したいので、

まず地域区分をリストにする
選んだ地域区分に応じて都道府県をフィルターしてリストにする

このような手順で作れば良さそうですが、これをExcelで実現するのは簡単ではありません。そこで、マスターを次のような表に変更します。

これは、各分類をテーブルの列見出しにして、そこに属する各都道府県を下に配置していったものです。つまり、元の表では、東北：青森、東北：岩手、というように、分類に属する都道府県の数だけ行があったものを、横に並べ直して一意（ただ一つ）にしたという事です。これによって、Excelではやりにくい絞り込みを事前におこないました。
なお、テーブル名はT_地域区分としています。今回は、見てすぐ解るように、T_◯◯と、テーブルを示すプレフィックス（接頭辞）をつけて、名前は日本語にしてあります。

ここで注意しておく必要があるのは、テーブルとして整然で、データベースとして扱いやすいのは、元のマスターであるという事です。それを敢えて崩してリスト設定用に作っている所を、念頭に置きます。

地域区分リストの作成

では、最初のリスト、つまり地域区分によるリストを作成しましょう。
リストに使うのはもちろん、T_地域区分の見出しです。そのように表を作り直したのですから。
リストを入力するためのテーブルを作ります。T_リストとしました。

そして、地域区分列の所に入力規則を設定します。
数式で入力規則を設定する場合、直接設定する前に、別のセルに数式を入れて挙動を見るのが有用です。入力規則を入れるボックスは極めて使い勝手が悪く見づらいからです。
入力規則リストで、範囲を参照させると、その各セルが選択肢に対応します。ですから、T_地域区分の見出し行を参照させれば良いはずです。

=T_地域区分[#見出し]

これは構造化参照ですので、何をしているか明白です。T_地域区分の見出し行を参照するという事です。結果は次のようにスピルされます（2つ以上のセル範囲だから）。

これを入力規則で参照させるのですが、入力規則内ではそのまま構造化参照は使えません。ですのでINDIRECT関数を使います。INDIRECT関数は、文字列を渡すと、その文字列を参照として解釈させる関数です。たとえば次のように使います。

先ほどの構造化参照の式を、A9セルに文字列として格納してあります。これはただの文字列なので参照ではありません。A1はA1を見に行きますが、"A1"はただの文字列であるのと一緒です。しかし、INDIRECT関数に文字列を渡す事によって、その文字列が参照として解釈されます。A10セルは

=INDIRECT(A9)

こうなっています。A9セルには構造化参照の文字列が入っているので、

=INDIRECT("T_地域区分[#見出し]")

この式と同じになり、中の文字列が直接に参照として機能するわけです。そして、入力規則の中ではINDIRECT関数が機能するので、すぐ上の式を入れれば、T_地域区分の見出しをリストに出来るという寸法です。

上手く行きました。INDIRECT関数が少しややこしいですが、やっている事はそんなに複雑ではありません。これは構造化参照ですので、見出しの数が変わっても自動的に対応出来て、変更に対して頑健です。

都道府県リストの作成

ここまでは、基本の入力規則の作成方法です。ここから、分類ごとに絞り込んだリストの作成です。といっても、絞り込み自体は、最初のT_地域区分テーブルの作成段階で済んでいます。ですので、やるべきは、

T_地域区分テーブルから、最初に選択した分類の列データを参照させる

事です。

先ほどと同じように、入力規則として設定する数式を、セルに書いて挙動を見ます。まず、地域区分として南関東を選択した場合を考えます。

南関東を選択したので、リストに表示されるべきは、

埼玉
千葉
東京
神奈川

この4都県です。これをどう表示させましょうか。まず、南関東列を構造化参照すると、

=T_地域区分[南関東]

こうです。テーブル名の後を角括弧で囲んで、その中に列名を入れれば、列のデータ部分が参照されます。

神奈川の下に0が3つ入っているのは、空白を参照すると0が返るからです。ルックアップ関数等でお馴染みの挙動です。
そして、この構造化参照を、選択した地域区分に連動させたいのです。そのために、INDIRECT関数を使います。まず次のようにします。

=INDIRECT("T_地域区分[" & "南関東" & "]")

INDIRECT関数は、渡された文字列を参照として解釈するのでした、そこでT_地域区分[南関東]を関数に入れます。しかし、そのまま渡すのでは無く、南関東の部分を分離させるのです。このようにすると、

上手く動きます。ということは、この"南関東"の所を参照にすれば、そこに連動させたリスト生成が出来るはずではありませんか。

=INDIRECT("T_地域区分[" & T_リスト[地域区分] & "]")

上手く行きました。こうすれば、地域区分の入力に対して都道府県リストを動的に変更出来ます。

良い感じです。後はこれを、入力規則に入れれば…

エラーが出て失敗します。なぜでしょうか。
ここで、入力規則の前提、すなわち、

入力規則の中で構造化参照は直接使えない

のを思い出します。いまは、T_地域区分の構造化参照の中に、T_リストの構造化参照を入れるという二重構造です。したがって、INDIRECT関数も二重構造にする必要があります。ですから数式は次のように書きます。

=INDIRECT(
"T_地域区分[" & INDIRECT("T_リスト[地域区分]") & "]"
)

T_地域区分[
と
]
の間には、T_リストの地域区分列に入れた値が自動的に入るようにします。そのために、ここで構造化参照を、INDIRECT関数を通しておこなうわけです。

後はこれを、入力規則に入れてあげれば良いのです。

これで上手く行きましたね。INDIRECT関数を二重にするのがごちゃごちゃしますが、スピルが使えれば、いきなり入力規則に書かずに、外のセルで挙動を試して数式を評価出来るので便利です。

この種の連動リストでは、マスターの表に対して名前をつけて実現する例が多いですが、表の構成が変わればそれに応じて名前を追加するなどする必要があります。ここで示した方法は、使っているのは構造化参照のみであり、変更にも自動的に対応出来ます。

複数行入力への対応

ここまでで、基本的な連動リストのデザインは出来上がりました。しかし今は、入力が1行のみです。シンプルな検索ではそういう場合もあるかも知れませんが、むしろ実務では、複数行にわたりリスト入力するのが普通です。ここで、行を増やして、地域区分に北陸を入力してみましょう。

先ほど連動リストを作ったので、テーブルでは行の追加で入力規則も一緒に反映されるので、リストも同じようになるはずです。実際、地域区分はそう出来ました。では都道府県を入力しましょう…

あれ、変ですね。北陸を選んだのに、都道府県に出てくるのは東海地域です。なぜでしょう。
ここで、入力規則を外のセルに出して見直してみましょう。

VALUEエラーが出た上でスピルしています。その原因は、

INDIRECT("T_リスト[地域区分]")

ここです。ここは、地域区分で入力した箇所の構造化参照です。しかし、[地域区分]とした場合、列のデータ部分全体を参照するのでした。という事は、そこでは東海と北陸が入ったセル範囲が参照されますので、結果的にエラーになります。
そこで、入力規則の当該部分を次のように修正します。

INDIRECT("T_リスト[@地域区分]")

構造化参照で@をつけると、同じ行を参照します。同じ行の別列にあるセルなので、それは1セルです。こうすれば、下の行に移ったらその行の地域区分セルが参照されるので、

上手く行きます。先ほどから説明してきた、外のセルに入力規則の数式を入れると便利ではあるのですが、構造化参照での同行参照のように、行が違うと求める挙動が得られない場合があるので注意しましょう。慎重に、行を揃えて数式を書くのも良いでしょう。

ちなみに、同じテーブルの同じ行なので、入力規則に入れるのは

=INDIRECT(
"T_地域区分[" & INDIRECT("[@地域区分]") & "]"
)

こうしたほうがシンプルだと思われますが、これはエラーになります。同テーブル同じ行の構造化参照は、INDIRECT関数の中でエラーになります。これはかなり重要な挙動なので、把握しておきましょう。

INDIRECT関数の中ではエラーになる
※2024年8月30日追記：
画像がテーブル外からの参照になっていたので、
テーブル内からの参照の画像に差し替えました

3段階以上の連動リストと汎用性

ここまでで、2段階の連動リストを作る事が出来ました。これは、

名前つき範囲を定義しない
一切のセルアドレス参照を使わない（構造化参照のみ使う）

ので、マスターの変更に強く、どこを参照しているか明確で、テーブルの上に二重で名前をつけるような冗長な処理も必要ありません。規則に入れる数式は少し複雑ですが、作れば安定して動きます。また、このしくみを用いれば、3段階以上の連動リストも作成出来ます。

しかし、気をつけなくてはならない事があります。
ここで用いた方法は、整合的にまとめられたマスターデータを、敢えて入力規則用に変更してテーブルを作りました。そして、構造化参照で列名を参照させてあります。これは、

分類ごとの値が多くなる
分類の階層が多くなる

と、それに応じた処理に大きなコストがかかるのを意味します。都道府県程度であれば、地域区分はそれほどありませんし、都道府県は計47で、それが何十年に一度も変更されないのは解っています。しかるに、実務上で取り扱うデータがそうである保証はありません。また、このやりかたは、

分類を横断した重複が無い

のを前提しています。地域区分で言えば、別の地域に同じ都道府県名が存在しないのが保証されています。だからたとえば、都道府県から更にリストを作る場合、都道府県を横に並べて列名を参照させられます。しかし、たとえば次のようなデータだとどうでしょうか。

この場合、岐阜支社にも大阪支社にも総務部や営業部があります。1段階目として支社を横並びにして、その下に部門を並べたら、部門を横断した重複が発生します。だから、部門を横並びにしてその下にメンバーを並べる事が出来ません。ただ総務部としても、どの支社の総務部か判らないからです。これを防ぐには、たとえば、岐阜支社_総務部のように連結して新しい表を作るなどする必要がありますが、これはかなり面倒な作業です。

これらを考慮すると、この方法は、

比較的規模の小さいマスターで
段階がそれほど必要無く
値が分類横断で重複しない

場合に使うのを検討するようなものだと言えます。作ってしまえば安定しますが、実務上の汎用性があるとは言えません。

複雑なマスターに対応した多段連動リスト

ここまでを踏まえた上で、より複雑なマスターに基づいた、多段連動リストを作成するのは可能です。こんな感じです。

これは、マスターテーブルから各分類の検索用テーブルを作る処理そのものを自動にして、リストを作ったものです。ただし、スピルや他の関数を駆使しています。たとえばこんな入力規則です↓

=OFFSET(
     INDEX(
       N_店名種類#,
       XMATCH(INDIRECT("input[@店名]") & "_" & INDIRECT("input[@種類]"),N_店名種類#)
     ),
     1,0
   )#

わざわざこういう事をして実装するのが実務に適しているかと言うと、微妙な所です。
ここで作ったものは、その内に説明するかも知れませんし、しないかも知れません…。