【Excel】ドロップダウンリストを使いこなす②【OFFSETで可変】

ドロップダウンリストを1つ目の項目から2つ目にリンクさせる?

ドロップダウンリストは入力の手間やミスを減らせる有用な機能です。ただ、リストの項目の増減による対象範囲の指定なので今回は自動でリストの範囲が項目数によって変化する方法を見ていきます。

なお、今回の最大の利点は項目を増やしたら自動的にリストの項目も可変していくことです。リストが増えない場合は、INDIRECT関数のほうが簡単に作れます。

OFFSET関数を入れる前に

実際に入力規則にOFFSET関数で設定していく

どんな時に使える?

基本的なドロップダウンリストの使い方や、OFFSET関数に関してはこちらをご覧ください。

【Excel】ドロップダウンリストを使いこなす①
ドロップダウンリストって? ドロップダウンリスト(プルダウンとも呼ばれている)はこんな形の入力フォームです。使えるとこんな便利‼︎ 他の人でも使いやすい 誤字が発生しない 決まったフォームなので基本的に入力が楽 ...
【Excel】OFFSET関数を組み合わせる【MATCH、COUNTAなど】
検索関数としてこれまでLOOKUP関数、INDEX関数+MATCH関数を紹介してきました。今回はOFFSET関数の特性・出来ることを見ていきます。>/p> LOOKUP関数に関してはこちら INDEX関数+MATCH関数に関してはこ...

今回も使用したファイルを貼っておきますのでご活用ください。

dropdownlist2


OFFSET関数を入れる前に

ドロップダウンリストにするにはデータの入力規則からリストを選び、元の値(リスト範囲)を選択しますが、今回はその元の値にOFFSET関数を使います。

注意しなければならないのは、セルに関数を入れていく場合には「関数の挿入」ボタンを押したり関数を入れ始めれば関数式のヒントが表示されますが、ダイアログボックスに入力する時には関数のヒントが出てきません。
(例:『=SUM(』と入力すれば、(数値1,…)と何を入力すればいいのか出ます。これを引数(引数)といいます。

先にリスト元になる表を作っておきます。今回は『都道府県→市区町村』という形で見ていきます。もちろんこれ以上のサイズにすることも可能です。シート名は『元データ』とでもしておきましょう。

手順1、都道府県名に対して名前の定義をしておく

都道府県名を横書きまたは縦書きで並べたら、名前の定義で都道府県のリスト範囲を名前付けしておきます。名前の定義に関してはこちらの手順5でも解説しています。

【Excel】高性能カレンダーを作る【自動計算、祝日対応、日付可変】
Excelで作ったカレンダー日付や曜日だけでなく、祝日や下位者の休日も付け加えたい。 より一般的なカレンダーの形に変えたい。 1日ごとに記入できる項目欄を増やしたい。 こんな悩みを解決できるカレンダーを作っていきます。 ...

注意するのは名前の参照範囲です。ずばり以下のようにしてください。

=OFFSET(Sheet3!$A$1,0,0,1,COUNTA(Sheet3!$1:$1))

なぜこうするかというと、現時点は5つの都県しか入っていないものの今後増えた際にいちいち範囲を再指定しないで済むようにしています。1行目でセル情報(都道府県名)が入ったセルの個数分が範囲になるという意味ですね。COUNTA関数に関してはこちらをどうぞ。

【Excel】COUNT〜COUNTIFS関数などでできること
「COUNT関数とかいろいろ種類あるしよくわからないよ。それにしても数えたり、該当の部分にだけマーカーしたりして探すの大変だな…」 COUNT関数は単体でも、他の関数との組み合わせでも使われやすいく使用頻度も高い関数です。今回はCOUNT...

参照範囲の名前は『都道府県』とわかりやすくしておきましょう。

手順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関数には弱点もあります

INDIRECT関数の弱点
1、名前の定義で範囲が関数で変更になる場合は対応できない
2、空欄もリストに入ってしまう為、リストに不必要な空欄が
されてしまう

弱点1について

すごく言葉だけで説明するのが難しいですが、以下のようになっています。

INDIRECT関数は該当するセル名の範囲データを取り出せますが、範囲が可変の状態では反応しません。(いろいろやったりググったりしましたが情報が出てこないで大変でした)

弱点2について

これは好みの問題ですが、リストのメリットの1つである「入力ミスをなくす」に反しているように感じます。要は不要なものは、たとえ空欄であってもリストに載るべきではないです。

もちろん名前の定義で範囲選択をしていってもいいのですが、今回の例題内容に沿って言うならば

①都道府県が追加される度に名前の定義で範囲を設定する必要がある
②市区町村が増減する度に範囲を変更する必要がある

こういった部分で最初の形を作るのは簡単なものの、メンテナンスが非常に面倒になるので運用形態によってはむしろ手間が掛かるのが問題点です。

その点では今回のOFFSET関数を使うやり方ならば、都道府県や市区町村が増えるとしても都道府県なら1行目、市区町村なら該当する列にただ値を入れるだけで自動的にリストが変化するので、最初の手間だけで済むのがメリットです。

今回は都道府県と市区町村でしたが、都道府県の軸を市区町村に変更して、市区町村の軸を店舗情報などにするなど変更すれば様々な使い方が出てくるので使いこなしていきましょう。