【Excel】OFFSET関数を使った前の入力内容に応じて自動的に変わる階層型のドロップダウンリストの作り方

2025年2月19日

Excelでは階層型のドロップダウンリストを作成することもできます。階層型のリストでは、1つ前に選択したデータに応じてリストの内容を自動的に変えることができます。

たとえば、リストで都道府県に東京都を選択すると、市区町村には東京都の地名が表示されます。

千代田区を選択すると、町名では千代田区の地名が表示されるようになります。

都道府県に神奈川県を選択すると、神奈川県の市区町村が表示されるようになります。

このような階層型のリストを使うことで、長いリストからデータを探す手間を省くことができます。

OFFSET関数を使った前の入力内容に応じて自動的に変わる階層型のドロップダウンリストの作り方

まず、階層型のリストに使用するデータの一覧を用意します。今回はこのような地名のデータを用います。この際、データは1行に都道府県、市区町村、町名を入力するようにしてください。

次に各階層のリストの名前を定義します。名前を定義することで、この後数式を入力するのが楽になります。都道府県の列を選択し、数式タブの「定義された名前」から「選択範囲から作成」をクリックします。

上端行にチェックを入れた状態で「OK」を押します。

これで、都道府県のリストは「都道府県」という名前が付けられました。同じように市区町村、町名のリストにも名前を付けていきます。これで、それぞれの列に都道府県、市区町村、町名という名前が付けられました。

次に入力欄にリストを設定してきます。都道府県を入力するセルにカーソルを合わせ、データの入力規則をクリックします。

リストを設定し、元の値に「=都道府県」と入力します。名前を定義しているので、これで都道府県のリスト範囲を選択していることになります。

OKを押せば、都道府県のプルダウンリストが表示されます。

ここでは、いったん都道府県に「東京都」を入力した状態で、市区町村のリストを設定していきます。

先ほどと同じように入力規則でリストを選択します。市区町村のリストは都道府県名に応じて変更する必要があります。そこで、OFFSET関数を使用します。

OFFSET関数は、指定したセルを基準に、行と列を指定した数だけ移動した位置にあるセルやセル範囲を参照する関数です。

OFFSET(基準, 行数, 列数, [高さ], [幅])
指定したセルを基準に別のセル範囲を指定できる。基準：基準となるセルまたはセル範囲を指定。行数：基準セルから何行下に移動するかを指定。列数：基準セルから何列右に移動するかを指定。高さ：参照する範囲の高さを指定。幅：参照する範囲の幅を指定。

元の値には以下の数式を入力します。

=OFFSET(市区町村,MATCH(A2,都道府県,0)-1,0,COUNTIF(都道府県,A2),1)

引数	数式	役割
基準	市区町村	市区町村の範囲を指定。
行数	MATCH(A2,都道府県,0)-1	MATCH関数を使って市区町村のリストの何行目から参照するかを選択する。MATCH関数では、指定した範囲内で特定の値を探し、その値が範囲内の何番目にあるかを返す関数。ここでは、セルA2に記載された「東京都」が都道府県のリストの何行目にあるかを検索する。東京都はリストの1行目にあるので、「1」という値を返す。 ※ここで、注意が必要なのが、MATCH関数はリストの先頭行を1行目として数えますが、OFFSET関数では、基準セルから何行移動するかを指定するので、東京都の行から参照するには、行数を0としなければいけません。そのため、ここではMATCH関数の後に-1を付けて調整します。
列数	0	参照する範囲は市区町村の範囲で、基準から移動する必要はないため「0」を指定。
高さ	COUNTIF(都道府県,A2)	東京都と書かれた行の数だけ参照するので、COUNTIFを使って、東京都が書かれたセルが何行あるかを数える。
幅	1	参照する列数は市区町村の1列なので、幅には「1」を指定

これでOKを押せば、東京都に対応する市区町村のリストが表示されるようになります。

なお、都道府県の欄を空欄にした状態でリストを設定するとエラーが表示されますが、そのまま「はい」を押します。

都道府県の欄を埋めれば、同じように都道府県に対応するリストが表示されます。

町名のリストの設定方法も基本は同じです。ここでは、いったん市区町村に「千代田区」を入力した状態で、市区町村のリストを設定していきます。

データの入力規則の元の値には以下のように数式を入力します。

=OFFSET(町名,MATCH(B2,市区町村,0)-1,0,COUNTIF(市区町村,B2),1)

引数	数式	役割
基準	町名	町名の範囲を指定。
行数	MATCH(B2,市区町村,0)-1	MATCH関数を使ってセルB2の千代田区が市区町村のリストの何行目にあるかを検索し、行数の調整のため「-1」を付けます
列数	0	参照する範囲は町名の範囲で、基準から移動する必要はないため「0」を指定。
高さ	COUNTIF(市区町村,B2)	千代田区と記載されたセルの行数を数えるので、COUNTIFでセルB2を条件とする。
幅	1	参照する列数は町名の1列なので、幅には「1」を指定