【Excel】入力規則のリストを階層連動させる方法!Excelの都道府県から市区町村ドリルダウン

Excelの入力規則で、都道府県を選択したら市区町村のリストも連動して表示させたい場面があります。例えば、住所録やアンケートフォームを作成する際に、ユーザーの入力を効率化し、誤入力を防ぐために役立ちます。この記事では、Excelの入力規則を使って、都道府県と市区町村を階層的に連動させる具体的な方法を解説します。

この設定を行うことで、プルダウンリストから選択するだけで、正しい都道府県に対応する市区町村が表示されるようになります。これにより、データ入力の手間が省け、データの正確性も向上します。

都道府県と市区町村のリスト作成

階層連動させるためには、まず都道府県と、それに対応する市区町村のリストをExcelシート上に準備する必要があります。このリストが、入力規則で参照される元データとなります。リストは、検索や管理がしやすいように、別シートにまとめておくのが一般的です。

ここでは、2つのシートを用意します。「リストシート」に都道府県と市区町村の元データを作成し、「入力シート」で実際にドリルダウン機能を使います。

リストシートの準備

「リストシート」を開き、A列に都道府県名を、B列以降に各都道府県に対応する市区町村名を縦に並べて入力します。例えば、A1に「北海道」、B1からB5に「札幌市」「函館市」「小樽市」「旭川市」「室蘭市」のように入力します。この時、都道府県名と市区町村名の間に空白行を入れないように注意してください。

この形式で、すべての都道府県と市区町村のデータを入力します。データ量が多い場合は、コピー&ペーストや他のデータソースからのインポートも活用できます。各都道府県の市区町村リストは、必ず同じ列に連続して入力してください。

名前の定義でリストを管理

次に、各都道府県の市区町村リストに名前を定義します。これにより、INDIRECT関数などで参照しやすくなります。都道府県名と同じ名前を、対応する市区町村リストの範囲に付けるのが一般的です。

例えば、「北海道」の市区町村リスト(B1:B5)には、「北海道」という名前を定義します。この作業を、すべての都道府県について繰り返します。名前の定義は、「数式」タブの「名前マネージャー」から行うか、範囲を選択した状態で数式バーの左にある「名前ボックス」に直接入力してEnterキーで確定します。

名前の定義を行う際は、大文字・小文字を区別せず、スペースを含まない名前を使用してください。また、Excelの予約語(Print_Areaなど)や、シート名・セル参照と重複する名前は避ける必要があります。

都道府県セルへの入力規則設定

まず、入力したいシート(ここでは「入力シート」とします)のA列に、都道府県を選択するための入力規則を設定します。これにより、A列にはリストから都道府県名のみが入力できるようになります。

「入力シート」のA1セルを選択した状態で、「データ」タブの「データの入力規則」をクリックします。「設定」タブで、「入力値の種類」を「リスト」に、「元の値」に「リストシート」の都道府県名が入力されている範囲(例: `リストシート!$A$1:$A$47`)を指定します。

「元の値」に直接範囲を指定する代わりに、都道府県名が入力されている範囲に名前を定義しておき、その名前(例: `都道府県リスト`)を指定することも可能です。これにより、後から都道府県を追加・削除した場合でも、名前の定義を更新するだけで反映されるため管理が容易になります。

市区町村セルへの入力規則設定(階層連動)

次に、B列の市区町村セルに、A列で選択された都道府県に応じて市区町村リストが自動で切り替わるように入力規則を設定します。これが階層連動の核となる部分です。

「入力シート」のB1セルを選択した状態で、「データ」タブの「データの入力規則」をクリックします。「設定」タブで、「入力値の種類」を「リスト」に、「元の値」に以下の数式を入力します。

`=INDIRECT(A1)`

この数式は、B1セルの左隣にあるA1セルに入力されている値(都道府県名)を、名前の定義で作成したリスト名として参照します。例えばA1に「北海道」と入力されていれば、「北海道」という名前で定義された市区町村リストがB1の入力規則の元値として使用されます。

この設定を、B列の他のセル(B2、B3…)にもコピーします。コピーする際は、数式が相対参照で正しくコピーされるように注意してください。数式が `=INDIRECT(A2)`、`=INDIRECT(A3)` のように、各行の都道府県セルを参照するように自動で調整されます。

OFFSET関数を使った代替案

INDIRECT関数は、参照するセルが多い場合や、複雑なリスト構造を持つ場合に、計算に時間がかかることがあります。代替案として、OFFSET関数を使う方法もあります。OFFSET関数は、開始位置から指定した行数・列数だけ移動したセルの範囲を返します。

この場合、名前の定義は、都道府県名ではなく、各都道府県の市区町村リストの範囲に、都道府県名と同じ名前を定義しておく必要があります(例: 「北海道」という名前で、北海道の市区町村リスト範囲を指定)。

「入力シート」のB1セルを選択し、「データの入力規則」で「元の値」に以下の数式を入力します。

`=OFFSET(リストシート!$B$1,MATCH(A1,リストシート!$A$1:$A$47,0)-1,0,COUNTIF(リストシート!$A$1:$A$47,A1),1)`

この数式は、A1セルに入力された都道府県名に対応する市区町村リストの範囲を動的に返します。COUNTIF関数でその都道府県の市区町村数をカウントし、OFFSET関数でその数だけ範囲を指定しています。

OFFSET関数は、INDIRECT関数よりもパフォーマンスが良い場合がありますが、数式が複雑になる傾向があります。どちらの関数を使用するかは、データの量や複雑さ、パフォーマンス要件に応じて選択してください。

設定の確認と応用

設定が完了したら、実際に「入力シート」で都道府県セル(A列)のプルダウンから都道府県を選択し、次に市区町村セル(B列)のプルダウンで対応する市区町村が表示されるかを確認します。正しく連動していれば、都道府県を変更すると市区町村のリストも自動で切り替わるはずです。

この技術は、住所入力だけでなく、商品カテゴリとそのサブカテゴリ、部門と役職など、様々な階層構造を持つデータ入力に応用できます。例えば、商品リストで「家電」を選択したら、次に「テレビ」「冷蔵庫」といったサブカテゴリが表示されるように設定できます。

よくある失敗パターンと対処法

設定がうまくいかない場合、いくつかの原因が考えられます。

名前の定義が正しくない

最も多い失敗は、名前の定義が間違っているケースです。都道府県名と完全に一致しない、スペースが含まれている、または範囲指定が間違っていると、INDIRECT関数がリストを正しく参照できません。

「数式」タブの「名前マネージャー」を開き、定義した名前と範囲が正しいか、都道府県名と完全に一致しているかを確認してください。特に、大文字・小文字の違いや、全角・半角の誤りがないか注意深くチェックします。

入力規則の元の値の指定ミス

入力規則の「元の値」に、INDIRECT関数やOFFSET関数を正しく入力できていない場合も問題が発生します。数式に誤字脱字がないか、セル参照が間違っていないかを確認してください。

特に、INDIRECT関数を使用する場合は、参照するセル(A1など)が相対参照になっていることを確認してください。絶対参照($A$1など)になっていると、数式をコピーしても正しいセルを参照しません。

リストシートのデータ形式

リストシートのデータ形式が、想定と異なっている場合も原因となり得ます。市区町村リストが都道府県名のすぐ下から連続して入力されていない、または都道府県名と市区町村名の間に空白行が入っていると、OFFSET関数などで正しく範囲を特定できなくなります。

リストシートのデータが、都道府県名とそれに対応する市区町村名のリストで構成されており、各都道府県の市区町村リストが連続していることを確認してください。空白行や不要なデータは削除しておきます。

別シートへの参照方法

INDIRECT関数やOFFSET関数で他のシートを参照する場合、シート名にスペースが含まれていると、数式が複雑になることがあります。シート名を `’リストシート’!A1` のようにシングルクォーテーションで囲む必要があります。名前の定義でシート名を省略している場合も、参照時にシート名を明示する必要があるか確認してください。

例えば、`=INDIRECT(“‘”&A1&”‘!B1:B5”)` のような形になることがあります。シート名の変更にも対応するため、名前の定義を利用し、その名前を参照する方が管理は容易です。

まとめ

Excelの入力規則とINDIRECT関数(またはOFFSET関数)、名前の定義を組み合わせることで、都道府県と市区町村のような階層的なリストを簡単に作成できます。これにより、ユーザーはプルダウンから選択するだけで、入力の手間を省き、データの正確性を高めることができます。

この技術は、住所録作成だけでなく、様々なデータ管理業務での活用が期待できます。次回は、この応用として、さらに深い階層(例: 都道府県・市区町村・番地)の連動設定について解説します。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】