はじめに
今回やりたいことです。
階層的に整理された、2段階のデータがあります。
入力規則としてドロップダウンリストを設定し、1階層目の選択内容に従って、2階層目の入力候補がフィルターされる というシートを作成します。
下の例でいうと、担当部署として「ばら」を選択した行では、「ばら」に属するデータ「サクラ」「イチゴ」「アーモンド」のみが選択できるようになっています。
よくあるやり方
まずは、これまでよく使われてきたやり方に触れておきます。
定義された名前を使う方法です。
下準備
定義用のシートに、以下のような構成でセルに階層構造を定義します。
範囲を選択し、[数式]タブの定義された名前 [選択範囲から作成]をクリックし、[上端行]にチェックを入れてOKを押します。
すると、上端行の値からそれぞれの列で、定義された名前が作成されます。
下準備はこれで完了です。
入力規則の設定
次に、シートに入力規則を設定していきます。
1階層目
こちらは単純です。
入力規則の[元の値]に、先ほど作成した定義用のシートの1行目を指定します。
これで、1階層目を入力するためのドロップダウンリストができました。
2階層目
2階層目には少しExcel関数の知識が必要です。
入力規則の[元の値]に、＝INDIRECT(1階層目の入力セル)を入力します。
この例では1階層目の入力セルには、隣のC2セルを指定しています。
入力規則や条件付き書式で、相対的な位置関係を参照したい際には、R1C1参照形式にしておくとわかりやすいです。
これで定義された名前を使用した2段階リストができました。
INDIRECT関数
簡単に関数の説明をしておきます。
INDIRECT関数は、引数に指定した文字列から、セル・名前に対する参照を返す関数です。
今回の例ではC2セルからさかなという文字を取り出し、定義された名前であるさかなのセル範囲を参照しています。
本筋ではないので、詳しくは公式ページなどをご参照ください。
この方法の悪いところ
ひとえにメンテナンス性が悪いです。
例えば、部署名の「ばら」が「バラ科」に変更されたとき、以下の手順で変更が必要になります。
1. 定義用シートの編集
2. 定義された名前の編集
他にも所属人員の追加では定義された名前の範囲を編集しなければなりませんし、新しい部署ができれば新規で定義された名前を作成しなければなりません。
シートと名前の2項目を毎回編集しなくてはならないのはかなりの手間です。
今回説明するやり方
テーブル形式で管理できればメンテナンス性が高まるのではないかと考えました。
以降はスピルが使用できるバージョン、M365環境にて動作します。
下準備
まずは下準備として、管理用のテーブルを作成します。
テーブルにはわかりやすい名前として、それぞれT_部署 T_部員と名付けておきました。
1階層目のための準備
部署のリストです。
テーブルに項目が増えたときにも範囲が自動的に拡張するように設定したいと思います。
テーブルの範囲を参照する、構造化参照という方法で、自動的にテーブルの列全体を指定できます。
しかし、データの入力規則では、この構造化参照は使用できません。
これを解決する方法はいくつかあって、例えば先ほどと同様にINDIRECT関数を使用する方法。
もう１つは、いったん構造化参照で別のセルに書き出し、そのセルからのスピル範囲を示す演算子#を使う方法です。
INDIRECTを使う方法では、テーブル名や列名が変わったときに追従しなくなってしまうので、スピル演算子を使う方法を使用します。
ついでにSORT関数を追加して、並び替えも行っておきます。
(あいうえお順に並べ替えています)
=SORT(T_部署[所属])
2階層目のための準備
ところで、入力規則の元の値には、参照(セル範囲)を指定することはできますが、配列(値の集合)を指定することはできません。
| 元の値 | 可否 |
|---|---|
| 参照 (セル範囲) |  |
| 配列 (値の集合) |  |
なので、範囲として連続的に指定できるセルの並びを用意してあげる必要があります。
テーブルの並びがバラバラだと、セル範囲として指定することができないので、テーブルの入力状態によらず、並びを整理してあげる仕組みを作ります。
これにも、SORT関数を使用します。
あいうえお順に並べることで、項目ごとに固めて並べることができます。
=SORT(T_部員)
入力規則の設定
次に、シートに入力規則を設定していきます。
1階層目
1階層目は簡単です。
1階層目のために並べ替えて書き出したセル範囲を、スピル演算子#を使って指定します。
2階層目
ここが一番ややこしいところです。入力規則に以下の数式を設定します。
OFFSET関数で参照を返すのがポイントです。
ここで、定義!$K$2は下準備で2階層目のために書き出したセル、C2は入力欄の1階層目のセルです。
=LET(
rng,定義!$K$2#,
x,C2,
r,XMATCH(x,CHOOSECOLS(rng,1))-1,
cnt,SUM(N(CHOOSECOLS(rng,1)=x)),
OFFSET(rng,r,1,cnt,1)
)
関数の解説です。
LET関数を使って、手続き的に計算していますが、やってることは以下の通り、目的のセル範囲を抽出する処理です。
解説は以上です。
おまけ
最後にできそうでできない方法を書いておきます。
「テーブルから条件に合う部分だけを抽出したい」といえばFILTER関数です。
しかしながら、入力規則には配列は指定できず、参照で指定する必要があります。
FILTER関数の返り値は参照ではなく配列ですので、直感的なこの方法は使えません。
=FILTER(CHOOSECOLS(定義!$K$2#,2),CHOOSECOLS(定義!$K$2#,1)=C2)