名簿や受注リストに郵便番号だけがあって、住所の列が空のまま。
1,000行を手で埋めるのは現実的ではありません。
やり方は大きく3つあります。どれも動きますが、それぞれ別の壊れ方をします。
この記事では、やり方と一緒に「何が壊れるか」「どう直すか」まで書きます。
先に結論
| 方法 | 向いている場面 | 気をつけること |
|---|---|---|
1. 郵便番号データを取り込んで XLOOKUP | 社外にデータを出したくない。何度も使う | 先頭ゼロで一致しなくなる。シートが重くなる |
| 2. GASで外部APIを呼ぶ | 件数が少ない。すぐ試したい | 郵便番号が外部サービスに送られる。APIが止まると使えなくなる |
| 3. アドオンを使う | 毎回やる。構築に時間をかけたくない | 自分で中身を直せない |
方法1. 郵便番号データを取り込んで XLOOKUP
日本郵便が郵便番号データを無償で公開しています。これをシートに取り込んで突き合わせます。
手順
- 日本郵便のサイトから「住所の郵便番号（1レコード1行、UTF-8形式）」をダウンロードする
- zip を解凍して CSV を取り出す
- スプレッドシートに新しいシートを追加し、
郵便番号データなどの名前をつける - 先に C 列（郵便番号）の表示形式を「書式なしテキスト」にしておく（表示形式 → 数値 → 書式なしテキスト）
- ファイル → インポート → アップロード から、その CSV を取り込む
- 元のシートで
XLOOKUPを書く
=XLOOKUP(TEXT(A2,"0000000"),
郵便番号データ!$C$2:$C$125000,
郵便番号データ!$G$2:$G$125000 & 郵便番号データ!$H$2:$H$125000 & 郵便番号データ!$I$2:$I$125000,
"要確認")C列 が郵便番号、G・H・I列 が都道府県・市区町村・町域です（UTF-8・1レコード1行形式の場合）。
データは約12.5万行なので、範囲は $C:$C のような列全体ではなく、行番号で区切ってください。
列全体を指定すると、計算のたびに100万行を走査することになり、目に見えて重くなります。
手順4を飛ばして郵便番号列が数値になってしまった場合は、検索する側だけでなく探される側も TEXT() で揃える必要があります。
TEXT(郵便番号データ!$C$2:$C$125000,"0000000")ただしこれは12.5万行ぶんの変換を毎回走らせることになります。手順4をやっておくほうが軽く済みます。
壊れるところ① 先頭ゼロが消えて、一致しなくなる
これが一番よく起きます。
0600000（札幌市中央区）のようにハイフンなしの7桁をセルに貼ると、スプレッドシートは数値だと解釈して 600000 にします。 先頭の 0 が消えます。
実際に3つの書き方を貼って確かめた結果です。
| 貼り付けた文字列 | セルの中身 | 配置 |
|---|---|---|
0600000 | 600000 | 右寄せ（数値になった） |
100-0001 | 100-0001 | 左寄せ（文字列のまま） |
０６０００００（全角） | ０６０００００ | 左寄せ（文字列のまま） |
この3つの範囲では、数値に変わったのはハイフンなしの7桁だけでした。
ハイフンが入っていたり全角だったりすると、数字として解釈されずに文字列のまま残ります。
だから「うちは大丈夫だった」という人と「全部ずれた」という人が分かれます。
手元のデータがどの書き方かを、まず確認してください。
どのくらい影響するか
日本郵便のデータに入っている郵便番号は 120,720種類（2026年10月6日時点のデータを集計）。
そのうち 0 で始まるものが 14,081件、全体の 11.7% あります。
| 都道府県 | 0で始まる郵便番号 |
|---|---|
| 北海道 | 7,974件 |
| 秋田県 | 2,156件 |
| 青森県 | 2,033件 |
| 岩手県 | 1,918件 |
北海道と東北北部が、丸ごと該当します。
全国の名簿なら、おおよそ9件に1件が合わなくなる計算です。
直し方
A. 取り込む前に、列の表示形式を「書式なしテキスト」にしておく
上の手順4がこれです。先に文字列だと宣言しておけば、先頭ゼロは消えません。
B. 突き合わせるときに、両側を7桁ゼロ埋めの文字列に揃える
すでに数値になってしまった列は、TEXT(A2,"0000000") を通せば 0600000 に戻せます。
検索値と検索範囲の両方を揃えないと一致しません。
壊れるところ② シートが重くなる
郵便番号データは約12.5万行あります。列が十数個あるので、セル数は200万近くになります。
スプレッドシート1ファイルの上限は1,000万セルなので入りはしますが、開くたびに読み込みが走ります。XLOOKUP の範囲を列全体で指定していると、さらに重くなります。
「開くのが遅くなった」と感じたら、たいていこれです。
必要な都道府県だけに絞って取り込むと、かなり軽くなります。
方法2. GASで外部APIを呼ぶ
郵便番号を渡すと住所を返してくれるWeb APIがいくつか公開されています。
Apps Script から呼んで、カスタム関数にする方法です。
件数が少ないときは、これが一番早く動きます。
壊れるところ① 郵便番号が外部サービスに送られる
API を呼ぶというのは、自分の手元のデータを、他社のサーバーに送るということです。
送られるのは郵便番号だけで、氏名や電話番号は送られません。
ですが名簿の一部であることに変わりはありません。
- 顧客名簿を扱っているなら、社内の規定に触れないか
- そのAPIの提供者は、送られた郵便番号をどう扱うと書いているか
使う前に、この2つは確認してください。 無料で公開されているAPIほど、利用規約やプライバシーポリシーが見当たらないことがあります。
壊れるところ② APIが止まると、使えなくなる
個人や小規模な事業者が公開しているAPIは、予告なく停止したり、仕様が変わったりします。
そうなったときシートがどうなるかは、書き方によって変わります。
エラー表示になることもあれば、空欄が返ることも、古い値が残ったままになることもあります。
いずれにせよ、新しい行で住所が取れなくなります。
自分のシートがどう壊れるかを知っておきたいなら、APIのURLをわざと存在しないものに変えて、一度試してみてください。
壊れるところ③ 件数が増えると、速度と上限にぶつかる
1行ずつAPIを呼ぶので、1,000行なら1,000回の通信が発生します。
- Apps Script の実行時間には上限があります（数分）
- 外部へのリクエスト回数にも1日あたりの上限があります（Googleアカウントの種別によります）
数十行なら気になりません。数千行だと、途中で止まります。
方法3. アドオンを使う
Google Workspace Marketplace で配布されているアドオンを使う方法です。
データの取り込みも数式も書かずに済みます。
そのかわり、中身を自分で直せません。 作者の設計をそのまま受け入れることになります。
選ぶときは、ここを見てください。
- データを外部に送るか。 郵便番号データを同梱しているなら通信は発生しません
- 要求する権限は何か。 インストール時に表示されます。「すべてのスプレッドシート」を求めるものと、「開いているスプレッドシートだけ」のものがあります
- 該当しない郵便番号の行をどうするか。 空欄で上書きされると、元の内容が消えます
このサイトについて
当方も temoba 事務データ整形 という無料のアドオンを作っています。
Google Workspace Marketplace での公開に向けて、いま Google の審査を受けています。
上の3点がどうなっているかは使い方のページに書いています。
記事の内容と当方の利害は一致しているので、そのつもりで読んでください。
方法1と方法2も、実際に動く手順として書いています。
どの方法でも、共通して気をつけること
郵便番号で分かるのは「町域」まで
郵便番号から分かるのは、都道府県・市区町村・町域（例：千代田）までです。
番地や建物名は郵便番号には含まれていません。 自動入力できるのはここまでです。
1つの郵便番号に、複数の住所が割り当てられていることがある
日本郵便のデータは 124,526行ありますが、郵便番号の種類は 120,720種類です。
つまり同じ郵便番号が複数行に分かれているものが 1,339件あります。
このうち、町域名の注記（「（○○を除く）」など）を取り除いても住所が1つに決まらないものが 1,267件（全体の約1.0%）ありました。
XLOOKUP や VLOOKUP は最初に見つけた1件を返すので、これらでは意図しない町域が入ります。
また、町域の欄が「以下に掲載がない場合」などの注記だけで、町域そのものが分からない郵便番号が 1,709件あります。
こちらは市区町村までしか埋められません。
該当しなかった行を、空欄で潰さない
存在しない郵便番号、入力ミス、全角と半角の混在。
一致しなかった行に空欄を書き込むと、元々入っていた住所まで消えます。
XLOOKUP の第4引数（見つからないときの値）には "" ではなく、"要確認" のような目印を入れておくほうが安全です。あとから絞り込めます。
まとめ
| 困りごと | 対処 |
|---|---|
| 一部の行だけ住所が入らない | 先頭ゼロで一致していない。検索値と検索範囲の両方を TEXT(A2,"0000000") で揃える |
| 北海道・東北だけ全滅している | 同上。0 で始まる郵便番号が数値化している |
| 開くのが遅くなった | 12.5万行がシートに入っている。範囲を列全体で指定していないか確認。必要な県だけに絞る |
| ある日から住所が取れなくなった | 外部APIが停止または仕様変更した可能性 |
| 元の住所が消えた | 一致しなかった行に空欄を書き込んでいる |
| 町域が入らない郵便番号がある | 日本郵便のデータに町域が無い（1,709件）。市区町村までで確定 |