ドロップダウンリストを1つ目の項目から2つ目にリンクさせる？
ドロップダウンリストは入力の手間やミスを減らせる有用な機能です。ただ、リストの項目の増減による対象範囲の指定なので今回は自動でリストの範囲が項目数によって変化する方法を見ていきます。
なお、今回の最大の利点は項目を増やしたら自動的にリストの項目も可変していくことです。リストが増えない場合は、INDIRECT関数のほうが簡単に作れます。
OFFSET関数を入れる前に
実際に入力規則にOFFSET関数で設定していく
どんな時に使える？
基本的なドロップダウンリストの使い方や、OFFSET関数に関してはこちらをご覧ください。
今回も使用したファイルを貼っておきますのでご活用ください。
OFFSET関数を入れる前に
ドロップダウンリストにするにはデータの入力規則からリストを選び、元の値(リスト範囲)を選択しますが、今回はその元の値にOFFSET関数を使います。
注意しなければならないのは、セルに関数を入れていく場合には「関数の挿入」ボタンを押したり関数を入れ始めれば関数式のヒントが表示されますが、ダイアログボックスに入力する時には関数のヒントが出てきません。
(例：『=SUM(』と入力すれば、(数値1,…)と何を入力すればいいのか出ます。これを引数(引数)といいます。
先にリスト元になる表を作っておきます。今回は『都道府県→市区町村』という形で見ていきます。もちろんこれ以上のサイズにすることも可能です。シート名は『元データ』とでもしておきましょう。
手順1、都道府県名に対して名前の定義をしておく
都道府県名を横書きまたは縦書きで並べたら、名前の定義で都道府県のリスト範囲を名前付けしておきます。名前の定義に関してはこちらの手順5でも解説しています。
注意するのは名前の参照範囲です。ずばり以下のようにしてください。
=OFFSET(Sheet3!$A$1,0,0,1,COUNTA(Sheet3!$1:$1))
なぜこうするかというと、現時点は5つの都県しか入っていないものの今後増えた際にいちいち範囲を再指定しないで済むようにしています。1行目でセル情報(都道府県名)が入ったセルの個数分が範囲になるという意味ですね。COUNTA関数に関してはこちらをどうぞ。
参照範囲の名前は『都道府県』とわかりやすくしておきましょう。
手順2、別シートを用意しておく
先程作成した都道府県と市区町村が入ったシートとは別に、実際にドロップダウンリストを使うシートで以下のように枠を用意しておきましょう。
A2、A3セルに都道府県名が入っていく形になっています。まずA2セルに「データの入力規則」からリストを選び、元の値を =都道府県としてあげれば完了です。これは名前の定義によってリスト参照範囲が都道府県と名前を付けた範囲全体に指定されるからです。
A3セル以降に増やす場合はもちろんオートフィルが可能です。これで都道府県の部分は完了しました。難しいのはここからです。
今回のリストになる元データを再確認してみます。
市区の数が都道府県によって変わります。ここでOFFSET関数の数式を思い出してください。
=OFFSET(参照,行数,列数,[高さ],[幅])
高さの引数が選ぶ都道府県によって変わってしまいます。MATCH関数と組み合わせるにしても、行(列)目は指定できても○○行(列)の指定というのが出来ません。なので1行追加して高さ(市区町村の数)を表示させてしまう事にします。
追加した行で市区町村の数をカウントします。
=COUNTA(A3:A200)
日本で一番市区町村が多いのは北海道で2018/4時点で179です。
<参照元>https://uub.jp/rnk/nctv.html
ちなみにCOUNTA(A:A)は循環参照になってしまうため使用できません。
今回のCOUNTAの場合は以下のように循環参照になります。
なので=COUNTA(A3:A200)と具体的に範囲を指定していく必要があります。もちろん終わりの範囲は必要に合わせて変更できます。
実際に入力規則にOFFSET関数で設定していく
手順3、OFFSET＋INDEX+MATCHで市区町村を可変にする
都道府県は手順1でドロップダウンリストから選べるようになり、市区町村の情報も手順2で情報を拾える下準備は整いました。あとは市区町村を実際にデータの入力規則でいれていく段階です。
今回はA2セルに『東京都_区内』を選択します。そして、B2セルにデータの入力規則を設定していきます。
=OFFSET(元データ!$A$3,0,MATCH($A2,元データ!$1:$1,0)-1,INDEX(元データ!$2:$2,1,MATCH($A2,元データ!$1:$1,0)),1)
=OFFSET(元データ!の市区町村の始まり,縦に移動しない,MATCH($選択したい都道府県,元データ!の都道府県が入っている行、完全一致)-1セル分,INDEX(元データ!の市区町村の数をカウントしている行,移動しない,MATCH($選択したい都道府県,元データ!$の都道府県が入っている行,完全一致)),1列分)
結構複雑に見えますが、慣れないときはパーツ(関数)ごとに分けてセルに出してあげるとわかりやすいかと思います。私は頭の中で整理できない時は以下のように書き出しています。
さて、入力規則が完了しました。実際にA2セルの都道府県の部分を変えて、リストが対象の都道府県毎に変化しているのか確認してみましょう。
『東京_区内』だと23区のリストが出てきて、『東京_区外』だと23区外の市が出るように自動的に切り替わっています。完璧です。
きちんと絶対参照・相対参照していればもちろんオートフィルでリストをA3・B3、A4・B4…と増やしていくことも可能です。
どんな時に使える？
冒頭でも言いましたが、リスト範囲を変更しないならINDIRECT関数と名前の定義で作るほうが簡単です。しかしINDIRECT関数には弱点もあります。
1、名前の定義で範囲が関数で変更になる場合は対応できない
2、空欄もリストに入ってしまう為、リストに不必要な空欄が
表示されてしまう
弱点1について
すごく言葉だけで説明するのが難しいですが、以下のようになっています。
INDIRECT関数は該当するセル名の範囲データを取り出せますが、範囲が可変の状態では反応しません。(いろいろやったりググったりしましたが情報が出てこないで大変でした)
弱点2について
これは好みの問題ですが、リストのメリットの1つである「入力ミスをなくす」に反しているように感じます。要は不要なものは、たとえ空欄であってもリストに載るべきではないです。
もちろん名前の定義で範囲選択をしていってもいいのですが、今回の例題内容に沿って言うならば
①都道府県が追加される度に名前の定義で範囲を設定する必要がある
②市区町村が増減する度に範囲を変更する必要がある
こういった部分で最初の形を作るのは簡単なものの、メンテナンスが非常に面倒になるので運用形態によってはむしろ手間が掛かるのが問題点です。
その点では今回のOFFSET関数を使うやり方ならば、都道府県や市区町村が増えるとしても都道府県なら1行目、市区町村なら該当する列にただ値を入れるだけで自動的にリストが変化するので、最初の手間だけで済むのがメリットです。
今回は都道府県と市区町村でしたが、都道府県の軸を市区町村に変更して、市区町村の軸を店舗情報などにするなど変更すれば様々な使い方が出てくるので使いこなしていきましょう。
