Edited at

[Excel]3つ以上のリストを連動させる

入力規則で使用できるようになるリスト機能ですが、リストの内容を動的に変更する方法はいくつか知られています。

例えばこういうのとかこういうのです。


課題

世に知られているリスト連動のいくつかは複数の課題を含んでいます。


  • 3つ以上のリスト連動どうしよう

  • 項目を増やしたいけど名前定義を追加するの忘れそう

  • リストに複数表示されてしまう空白を消したい

これらを解決できる方法になります。


やりたいこと

3つ以上のリスト連動、つまりこんなのです。

動的リストの生成2.gif


詳細

上の例をもとに説明します。


リストデータの準備

大分類、中分類、小分類、それぞれでシートを分けて管理します。

また、それぞれの項目がどの分類に属しているか判別できるようにキーとなる上位分類もセットで管理します。

重要なのは「上位分類」で並べ替えをしておくことです。

image.png


リストデータからOFFSETでリストを取り出し入力規則にする

指定する分類のリストを取り出す方法はシンプルです。

開始位置と個数を計算してOFFSETで取得するだけです。

上位分類の並べ替えをしておくことで指定する分類の開始位置と指定する分類の個数を計算できるようになります。

image.png

例えば、漁業の中分類のリストは、シート「中分類」の3個目から2個分データを取り出せばよいです。

中分類の開始位置と個数はそれぞれMATCHとCOUNTIFで求められます。


中分類の開始位置

= MATCH(大分類の値,中分類!$A:$A,0)



中分類の個数

= COUNTIF(中分類!$A:$A,大分類の値)


あとはOFFSETに組み込めばよいので以下のようになります。

注意としてOFFSETは0からカウントしますのでMATCHの結果から1を引きます。


中分類の入力規則 (C3セルで設定する)

'= OFFSET(中分類!$B$1,開始位置-1,0,個数)

= OFFSET(中分類!$B$1,MATCH($B3,中分類!$A:$A,0)-1,0,COUNTIF(中分類!$A:$A,$B3))

↓イメージ

image.png

小分類も同様に求めますが、検索する値は大分類と中分類を結合したものになります。


小分類の開始位置

= MATCH(大分類の値&"_"&中分類の値,小分類!$A:$A,0)



小分類の個数

= COUNTIF(小分類!$A:$A,大分類の値&"_"&中分類の値)



小分類の入力規則 (D3セルで設定する)

= OFFSET(小分類!$B$1,MATCH($B3&"_"&$C3,小分類!$A:$A,0)-1,0,COUNTIF(小分類!$A:$A,$B3&"_"&$C3))


↓イメージ

image.png

大項目のリストは通常通り大項目シートで範囲指定してください。詳細は省略します。

以上で完成です。

名前定義を使用していないのでリストの項目を増やしたい場合は各シートに項目を追加するだけです。

分類を増やしたい場合はシートを増やして入力規則を規則的に登録していくだけです。


補足

構造化参照を使う方法もあります。

ただし構造化参照は入力規則に直接入力できないため名前定義を経由しなければなりません。複雑化する可能性があります。

どちらが良いかはケースに寄るので適宜使い分けてください。

個人的には大分類の入力規則だけは構造化参照で指定した方が増減に対応でき運用が楽になるので好きです。