こんにちは。公認会計士の酒井（@SakaiTakama)です。
先日、こんなツイートをしたので今日はその解説を書いてみたいと思います。
ズバリ「都道府県を選ぶと市町村が自動で連動するドロップダウンリストの作り方」です！
それではさっそく見ていきましょう。
Contents
元データの確認
本当なら都道府県・市町村でやりたいのですが、それだと件数が多すぎるのでちょっと簡単にしたデータを使ってみようと思います。
こんな感じです。これをもとに、大項目・中項目のリストを使っていきます。
ちなみによく解説されているのと見ると、大項目を横展開したリストをもとに作るというのが多いのですが、今回は上のテーブルを使って作る方法をやってみたいと思います
こんな形のデータです。
これはこれでいいのですが、大項目の数が増えるとなかなか見にくい表であること、また大抵のデータが上のようなデータであることを考えると、上のデータでもドロップダウンリストを作る方法を知っておいた方がいいと思うのです。
それでは、実際に見ていきましょう。
データの準備
データの並び替え
まず、データを大項目・中項目の順に並び替えます。
これは、大項目をもとに中項目の範囲を連動させるという方法を取る以上、大項目順に並んでないと中項目の範囲を正しく設定できないためです。
もっとも、既に大項目順に並んでいる場合にはこの処理は必要ありません。
大項目リストの作成
次に大項目だけのリストを作ります。
まず、大項目の列をどこか空いているスペースにコピーします。
次に重複削除で大項目だけのリストを作ります。
すると、飲料・お菓子（重複無し）だけのリストが出来上がります。
これを元に大項目のドロップダウンリストを作ります。
データNo.の付番
ここでは元データにデータ番号を採番します。上から１，２，３・・・としてください。
これが、中項目のデータ範囲を指定する際の開始行になります。（詳しくは後で）
大項目ドロップダウンリストの作成
それでは大項目リストを作ってみましょ。
これは簡単です。
データタブの「データの入力規則」から、種類に「リスト」、元の値にリストの範囲を選択してOKします。
こんな感じでドロップダウンリストを作れます。
では次に進みましょう。次からいよいよ中項目のドロップダウンリストの作成です。
中項目ドロップダウンリストの作成
ドロップダウンリストの作成自体は上述のとおり簡単なのですが、中項目は大項目に応じてリスト範囲を変えないといけません。その方法について見ていきましょう。
リストの範囲を変える考え方
まずはどのようなロジックで考えていくのかを見ていきます。
これは端的に、「大項目に応じて中項目の範囲をしている」これができればいいわけです。
つまりはこのようなイメージです。
大項目に応じて始点、終点を指定することで中項目の範囲を連動させることができます。
ここでポイントは3つです。
✔ 始点を指定する方法
✔ 終点を指定する方法
✔ 始点・終点から範囲指定する方法
ちなみに、始点と終点を指定するときには、どこからどれだけ移動して始点・終点とするかがポイントになります。これを起点と言います。
そして、始点と終点は中項目を縦に移動する点と考えると列タイトルのセルを起点とすると分かりやすくなります。
ここからは、このデータをもとに見ていきます。
始点を指定 ※ダジャレじゃないよ！笑
この場合、始点は「ポテトチップス」となるわけですが、どうやって指定するか。
起点である中項目からどれだけ移動するかと言い換えると分かりやすいですね。
ここで「データＮｏ」を使います。
データＮｏは上から順に１．２．３～と採番しているので、「開始点は起点（「中項目」セル）から指定した大項目でデータNo最低値の数だけ縦移動した先」と言うことができます。
こんなイメージです。
ではこれをどうやって数式で表現するかを見てみましょう。
ここで使う関数はＭＩＮＩＦS関数を使います。
=MINIFS([No.]列 , [大項目]列 , リストから選択した[大項目] )
これで、起点から始点までのマスが分かります。
例えばこんな感じです。
（※ちなみに絶対参照にすべきという声も聞こえそうですが、今回それは省略します。）
この例でいくと、大項目に飲料と指定すると、始点が「６」となり、起点から6マス目と指定することができるのです。
ちなみにMINIFS関数が使えないときの裏技も紹介しておきます。
範囲内の最低値を返すMIN関数とIF関数を組み合わせで作るのですが、最後確定エンターを押すときに、Shift＋Ctrl+Enter を同時押しで確定させます。数式はこのようにします。
＝MIN(IF([大項目]列＝リストから選択した[大項目] , [No.]列））
このように入力した状態で、 Shift＋Ctrl+Enter を同時押し です。
このように、数式を{ }でくくった表示がされます。これを「配列関数」と言うそうですが、詳しい説明は省略します。
MINIFSの代用がこれで出来るということだけ覚えておけば十分です。
では次に終点の指定方法を見ていきましょう。
終点の指定
終点は視点からの距離となります。それを表現する値は何か。
簡単ですね。大項目のデータ数です。
なのでCOUNTIF関数で処理します。
＝COUNTIF( [大項目]列 , リストから選択した[大項目])
これだけです。そしてこんな感じになります。
ここまで来たら、これをあとは範囲選択できる関数を駆使してドロップダウンリストにするだけです。
始点・終点から範囲指定する方法
ところで、範囲を操作する関数とはどんなものがあるかご存知ですか？
それ単体では使いにくいのですが、他の関数と組み合わせて使う関数があります。
それがOFFSET関数です。
このイメージはこのようになります。
ちなみに、移動・拡張の指定方法はどちらも（タテ・ヨコ）の並びです。
よって大項目に応じた範囲を選択する数式はこのようになります。
ヨコだけ入れる場合はタテに「１」を入れないとヨコと認識されませんのであしからず。
ここまでこればあと一息です。ちなみにOFFSET関数をこのままセルに入れても、複数のセルを選択した状態にすぎないのでエラーになるだけです。
で、この数式をどう使うかというと、ドロップダウンリストのデータ範囲にコピーします。
つまりこうなります。
ここに数式を入れることでデータ範囲を条件に合わせて動かすことができるのです。
これでようやく完成です。
こんな感じで、大項目を変えれば中項目のリストも変わる、そんなドロップダウンリストができました。
まとめ
都道府県別に市町村を連動させるドロップダウンリストの作り方、まさかこんな長文になると思わなかったです
大事なことは3つ、始点・終点・範囲の選択。
