【コピペ可】エクセルで都道府県を自動入力する4つの方法｜郵便番号・住所から抽出

エクセルで都道府県を入力するとき、毎回手入力していると表記ゆれと打ち間違いが必ず発生します。エクセルには自動入力の手段が4つあり、どれを選ぶかは「手元に何があるか」で決まります。

そして都道府県の自動入力には、この記事でいちばん伝えたい落とし穴があります。都道府県の自動入力は、失敗してもエラーが出ません。「東京都府中市」が京都府に、北海道の郵便番号が京都府に、エラー表示なしで静かに変わります。目視では気づけないため、誤判定しにくい式を最初から選んでおくのが安全です。あわせて、入力規則で値を制限する、日本郵便の正式な郵便番号データと照合するなど、確認の手段を組み合わせると精度が上がります。

結論：あなたのケースはどれか

手元にあるデータから、使う方法が決まります。

4つの方法の性格は次のとおりです。

① オートフィルで47都道府県を連続入力する

「北海道」と入力してドラッグするだけで47都道府県が並ぶようにする方法です。エクセルの「ユーザー設定リスト」に都道府県を登録すると、曜日や月と同じようにオートフィルが効くようになります。

コピペ用：47都道府県リスト（正式名称）

下のリストをそのままコピーして、空いているシートのA1セルに貼り付けてください。貼り付け時は右クリックから「形式を選択して貼り付け」→「テキスト」を選ぶと、書式が持ち込まれません。

北海道
青森県
岩手県
宮城県
秋田県
山形県
福島県
茨城県
栃木県
群馬県
埼玉県
千葉県
東京都
神奈川県
新潟県
富山県
石川県
福井県
山梨県
長野県
岐阜県
静岡県
愛知県
三重県
滋賀県
京都府
大阪府
兵庫県
奈良県
和歌山県
鳥取県
島根県
岡山県
広島県
山口県
徳島県
香川県
愛媛県
高知県
福岡県
佐賀県
長崎県
熊本県
大分県
宮崎県
鹿児島県
沖縄県

登録の手順

1. 上のリストを貼り付けたA1からA47までを範囲選択する
2. 「ファイル」をクリックする
3. 「オプション」をクリックする
4. 「詳細設定」をクリックする
5. 画面を下までスクロールし、「全般」の中の「ユーザー設定リストの編集」をクリックする
6. 左側の「ユーザー設定リスト」で「新しいリスト」が選ばれていることを確認する
7. 「リストの取り込み元範囲」に選択した範囲が入っていることを確認する
8. 「インポート」をクリックする
9. 「OK」を2回クリックしてダイアログを閉じる

登録が終わったら、任意のセルに「北海道」と入力し、セル右下のフィルハンドルを下方向にドラッグしてください。青森県、岩手県と順に自動入力されます。47件を超えてドラッグすると、北海道に戻って繰り返されます。

ここで多い失敗：他のPCでは動かない

ユーザー設定リストはブックではなく、そのPCのエクセル本体に保存される設定です。Microsoftのサポート文書でも、他のコンピューターで使うにはそのPC側のレジストリに追加する必要があると案内されています。

そのため、自分のPCでオートフィルが効くようになったブックを同僚に渡しても、相手のPCでは「北海道」のまま連続入力されません。共有前提のブックでは、次の②ドロップダウンリストを使ってください。こちらはブックの中に設定が保存されます。

略式（「県」なし）リストを登録するときの注意

「東京」「大阪」のような略式表記のリストを登録したい場合もあります。ただし略式リストは、後述する④の住所からの抽出には流用しないでください。住所の中に地名として同じ文字列が現れると、別の都道府県として拾われます。用途を「入力用」に限定するのが安全です。

北海道
青森
岩手
宮城
秋田
山形
福島
茨城
栃木
群馬
埼玉
千葉
東京
神奈川
新潟
富山
石川
福井
山梨
長野
岐阜
静岡
愛知
三重
滋賀
京都
大阪
兵庫
奈良
和歌山
鳥取
島根
岡山
広島
山口
徳島
香川
愛媛
高知
福岡
佐賀
長崎
熊本
大分
宮崎
鹿児島
沖縄

② ドロップダウンリストで都道府県を選ばせる

入力欄をプルダウン化して、47都道府県から選ぶだけにする方法です。手入力そのものを無くせるので、表記ゆれが原理的に発生しません。設定はブックに保存されるため、ファイルを配布しても相手のPCで機能します。

設定の手順

1. 新しいシートを追加し、シート名を「リスト」に変更する
2. 「リスト」シートのA1からA47に、上の47都道府県リストを貼り付ける
3. 入力用シートに戻り、プルダウンにしたいセル範囲を選択する
4. 「データ」タブをクリックする
5. 「データの入力規則」をクリックする
6. 「入力値の種類」で「リスト」を選ぶ
7. 「元の値」に =リスト!$A$1:$A$47 と入力する
8. 「OK」をクリックする

「元の値」に47件を直接カンマ区切りで書く方法もありますが、この欄には文字数の上限があり、47都道府県は入りきりません。必ず別シートの範囲を参照してください。

ここで多い失敗：設定前に入っていた誤入力は消えない

データの入力規則は、設定した後の入力しかチェックしません。すでに「東京」「東京都 」（末尾に空白）のような値が入っているセルは、リストを設定してもそのまま残ります。見た目は正常なので気づきません。

既存データを点検するには、「データ」タブの「データの入力規則」右側の下向き矢印から「無効データのマーク」をクリックしてください。リストに一致しないセルに赤い丸が付きます。

③ 郵便番号から都道府県を自動入力する

郵便番号の上2桁は「地域番号」と呼ばれ、導入時に原則として1つの都道府県へ1つの地域番号を割り当てる形で決められました。配達物の多い都府県には複数の番号が割り当てられています。対応表を用意しておけば、郵便番号から都道府県をおおまかに埋めることができます。

ただし、これは配送のための区分であって、都道府県を確定するための仕組みではありません。実際に上2桁と所在都道府県が食い違う市町村が存在します（詳しくは後述します）。この方法は「推定」であると理解したうえで使ってください。

最初に：この方法には静かな誤答があります

郵便番号をセルにそのまま打ち込むと、多くの場合エクセルは数値として扱います。すると先頭の0が消えます。0600001（北海道札幌市）は600001になり、上2桁は「06」ではなく「60」になります。60は京都府です。

実際にエクセルで試すと次のようになります。エラーは1件も出ません。

先頭が0で始まる郵便番号は、北海道（00、04〜09）、秋田県（01）、岩手県（02）、青森県（03）です。つまり東北と北海道のデータだけが静かに壊れます。件数が少ないと集計結果を見ても気づきません。

対策：桁を揃えてから上2桁を取る

数値でも文字列でもハイフン付きでも同じ結果になる式を使います。

=LEFT(RIGHT("0000000"&SUBSTITUTE(A2,"-",""),7),2)

やっていることは3つです。SUBSTITUTEでハイフンを除去し、先頭に0を7個足してから右7桁を取り出し（＝7桁に揃え）、その左2桁を取ります。上の4パターンすべてで正しい上2桁が返ることを確認済みです。

都道府県を取得する式

Microsoft 365またはExcel 2021以降であれば、XLOOKUPを使います。

=XLOOKUP(LEFT(RIGHT("0000000"&SUBSTITUTE(A2,"-",""),7),2),対応表!$A$2:$A$101,対応表!$B$2:$B$101,"該当なし")

最後の引数に「該当なし」を指定しておくのが重要です。指定しないと未一致は#N/Aになりますが、指定しておけば表としてそのまま読めます。

Excel 2019以前ではVLOOKUPを使います。

=IFERROR(VLOOKUP(LEFT(RIGHT("0000000"&SUBSTITUTE(A2,"-",""),7),2),対応表!$A$2:$B$101,2,FALSE),"該当なし")

VLOOKUPの第4引数は必ずFALSEにしてください。TRUEや省略にすると近似一致になり、該当が無いときに「近い値」を返します。これもエラーが出ないタイプの誤答です。また、対応表の列を後から挿入すると第3引数の「2」がずれ、別の列の値を返すようになります。列の増減が想定される表ではXLOOKUPを選んでください。

コピペ用：郵便番号 上2桁と都道府県の対応表（00〜99）

下の表は日本郵便の公表資料に基づく対応です。00から99まで100件すべてを収録しています。「対応表」シートのA1に見出し、A2以降にデータが入るよう貼り付けてください。

CSV形式でも用意しました。テキストとして貼り付けたあと、「データ」タブの「区切り位置」でカンマ区切りに分割してください。上2桁の列は「文字列」として取り込むのを忘れないでください。数値にすると、ここでもまた先頭の0が消えます。

00,北海道
01,秋田県
02,岩手県
03,青森県
04,北海道
05,北海道
06,北海道
07,北海道
08,北海道
09,北海道
10,東京都
11,東京都
12,東京都
13,東京都
14,東京都
15,東京都
16,東京都
17,東京都
18,東京都
19,東京都
20,東京都
21,神奈川県
22,神奈川県
23,神奈川県
24,神奈川県
25,神奈川県
26,千葉県
27,千葉県
28,千葉県
29,千葉県
30,茨城県
31,茨城県
32,栃木県
33,埼玉県
34,埼玉県
35,埼玉県
36,埼玉県
37,群馬県
38,長野県
39,長野県
40,山梨県
41,静岡県
42,静岡県
43,静岡県
44,愛知県
45,愛知県
46,愛知県
47,愛知県
48,愛知県
49,愛知県
50,岐阜県
51,三重県
52,滋賀県
53,大阪府
54,大阪府
55,大阪府
56,大阪府
57,大阪府
58,大阪府
59,大阪府
60,京都府
61,京都府
62,京都府
63,奈良県
64,和歌山県
65,兵庫県
66,兵庫県
67,兵庫県
68,鳥取県
69,島根県
70,岡山県
71,岡山県
72,広島県
73,広島県
74,山口県
75,山口県
76,香川県
77,徳島県
78,高知県
79,愛媛県
80,福岡県
81,福岡県
82,福岡県
83,福岡県
84,佐賀県
85,長崎県
86,熊本県
87,大分県
88,宮崎県
89,鹿児島県
90,沖縄県
91,福井県
92,石川県
93,富山県
94,新潟県
95,新潟県
96,福島県
97,福島県
98,宮城県
99,山形県

上2桁では判定できない地域が実在します

郵便番号は、地形や輸送経路の都合で、隣接する都府県の番号が割り当てられている地域があります。この場合、上2桁から引いた都道府県は実際の所在地と食い違います。日本郵便の郵便番号検索で確認できる例を挙げます。

対馬市や壱岐市は人口が1万人を超える市であり、名簿に含まれる可能性は十分にあります。市や郡がまるごと別の都道府県として集計されるため、影響は小さくありません。

したがって、顧客名簿や請求先の管理など1件の誤りが問題になる用途では、上2桁からの推定ではなく、日本郵便が公開している郵便番号データ（住所を含む正式なデータ）と7桁で照合してください。上2桁方式は、地域別のおおまかな集計や、住所欄の初期値を埋める補助として使うのが適切です。

④ 住所から都道府県だけを抜き出す

「東京都新宿区西新宿2-8-1」のような住所文字列から、「東京都」だけを取り出す方法です。やり方は3通りありますが、実務で使えるのは1つ目だけです。残り2つは、どういう条件で壊れるかを知っておく価値があるので併せて説明します。

推奨：4文字ルール（すべてのバージョンで使える）

都道府県名は3文字か4文字しかありません。4文字なのは神奈川県、和歌山県、鹿児島県の3つだけで、この3つはいずれも4文字目が「県」です。これを利用します。

=LEFT(A2,IF(MID(A2,4,1)="県",4,3))

4文字目が「県」なら4文字、そうでなければ3文字を取る、という判定です。47都道府県の正式名称を順に当てはめて確認すると、例外なく正しく取得できます。北海道、東京都、京都府、大阪府のように末尾が「県」でないものも問題ありません。

この式が壊れる唯一の条件

住所が都道府県から始まっていない場合です。「新宿区西新宿2-8-1」に対しては「新宿区」が返ります。エラーは出ません。政令指定都市や東京23区の住所は、都道府県が省略されて登録されていることがよくあります。

そこで、抽出結果が47都道府県のいずれかであるかを検査する列を隣に作ります。

=IF(COUNTIF(リスト!$A$1:$A$47,LEFT(A2,IF(MID(A2,4,1)="県",4,3)))=0,"要確認","")

「リスト」シートには①で使った47都道府県リストを置いておきます。都道府県から始まっていない住所には「要確認」が付くので、フィルターで抽出して個別に対応できます。

非推奨1：TEXTBEFOREで「都道府県」の位置を探す方法

Microsoft 365、Excel 2024、Excel for the webで使えるTEXTBEFORE関数を使い、「都」「道」「府」「県」のいずれかが最初に現れる位置までを取る、という発想の式が紹介されることがあります。

=TEXTBEFORE(A2,{"都","道","府","県"})

これは動きません。TEXTBEFOREは区切り文字を含まないため、「東京都新宿区」からは「東京」が返り、末尾の「都」が落ちます。さらに深刻なのが京都府です。「京都府京都市」には「都」が2文字目、「府」が3文字目にあり、先に見つかる「都」で切られて「京」が返ります。

区切り文字を足し戻す加工をしても、京都府の問題は解消しません。この方向は避けてください。

非推奨2：47都道府県リストとの照合（INDEX＋MATCH＋SEARCH）

住所の中に47都道府県のどれが含まれるかを探す、という方法です。旧来よく紹介されてきた式がこれにあたります。

=INDEX(リスト!$A$1:$A$47,MATCH(TRUE,ISNUMBER(SEARCH(リスト!$A$1:$A$47,A2)),0))

一見すると正しく動きます。しかしこの式は、複数の都道府県名が住所に含まれていた場合に、リストで先に並んでいるほうを無条件に返します。そして該当する住所が実在します。

「東京都府中市」です。この文字列は「東」「京都府」「中市」と読むこともでき、リストの中の「京都府」に一致してしまいます。

どちらが返るかはリストの並び順で決まります。JISの都道府県コード順（北海道から沖縄県）なら東京都のほうが先に来るため正しい結果になりますが、五十音順に並べ替えたリストでは京都府が先に来ます。実際に試した結果が次のとおりです。

並べ替えボタンを一度押しただけで結果が変わる式を、名簿の処理に使うべきではありません。なお、略式（「県」なし）のリストを使うと危険度はさらに上がります。「神奈川県茅ヶ崎市香川」のような、地名として他県名を含む住所が全国に多数あるためです。

この式を既に使っているブックがある場合は、参照範囲が列全体（A:A のような指定）になっていないかも確認してください。空白セルまで検索対象に入り、該当なしのときの挙動が不安定になります。範囲は必ず $A$1:$A$47 のように限定してください。

補足として、旧来この式は配列数式としてCtrl＋Shift＋Enterで確定する必要がありました。Microsoft 365およびExcel 2021以降では動的配列に対応しているため、Enterだけで確定できます。

静かな誤答チェックリスト

都道府県の自動入力で発生する誤りは、そのほとんどがエラー表示を伴いません。公開・提出の前に次の5点を確認してください。

いずれも、フィルターで異常値だけを抽出できるようにしておくと点検が数秒で終わります。

よくある質問

Web版（Excel for the web）でもオートフィルのユーザー設定リストは使えますか

ユーザー設定リストの登録は「Excelのオプション」から行うため、Web版では設定できません。Web版で作業する場合は、②のドロップダウンリストを使ってください。データの入力規則はブックに保存されるため、Web版でもリストが機能します。

ユーザー設定リストを別のPCに持っていくことはできますか

ユーザー設定リストはPC側の設定として保存されるため、ブックをコピーしても引き継がれません。移行先のPCで同じ手順を実行するのが確実です。複数人で使うブックなら、最初からドロップダウンリスト方式で作るほうが手戻りがありません。

「大阪市北区…」のように政令指定都市から始まる住所はどうすればよいですか

住所文字列だけからは都道府県を機械的に確定できません。政令指定都市名から都道府県を引く対応表を別途用意して、④の判定で「要確認」になった行に対してだけ適用する二段構えにするのが現実的です。市名は同名が存在する場合があるため、一括変換は避けてください。

都道府県名を「東京」のように略式で統一したいのですが

正式名称で取得したあと、SUBSTITUTEで末尾を除去するのが安全です。最初から略式で照合しようとすると、地名との衝突で誤判定が起きます。

まとめ

• これから入力するなら、共有しないブックは①オートフィル、共有するブックは②ドロップダウンリスト
• 郵便番号があるなら③。ただし桁を7桁に揃えてから上2桁を取ること、そして上2桁は推定であり例外地域があることを前提にする
• 住所があるなら④の4文字ルール。TEXTBEFORE方式とリスト照合方式は誤答するため使わない
• 都道府県から始まらない住所は必ず混ざるので、「要確認」を立てる列を作っておく
• この分野の失敗はエラーが出ないため、公開前のチェックリスト確認を習慣にする