住所の表記ゆれをExcelで統一する方法|正規化のやり方と限界
顧客リストを扱っていると、同じ住所なのに書き方だけが違うために、システム上は「別の住所」として扱われてしまうケースによく出会います。この記事では、Excelの関数を使って住所の表記を統一(正規化)する方法と、その方法では対応しきれない部分を解説します。
住所の表記ゆれの主なパターン
| パターン | 例1 | 例2 |
|---|---|---|
| 丁目・番地の書き方 | 渋谷1丁目7番1号 | 渋谷1-7-1 |
| 全角・半角数字 | 渋谷１−７−１ | 渋谷1-7-1 |
| 都道府県の省略 | 東京都渋谷区 | 渋谷区 |
| 建物名・部屋番号の有無 | 渋谷1-7-1 〇〇ビル3F | 渋谷1-7-1 |
| 郵便番号のハイフン | 150-0002 | 1500002 |
手順1:全角・半角を関数で統一する
まず、数字とハイフンの全角・半角を揃えます。ASC関数で全角英数字記号を半角に変換できます。
=ASC(A2)
ASC関数は住所の中の全角ハイフン「−」も半角「-」に変換しますが、変換後の文字が本来のハイフン(-)と完全に一致するとは限らないため、変換後に必ず目視確認してください。
手順2:丁目・番地・号の表記をSUBSTITUTEで統一する
「丁目」「番地」「号」を全てハイフンに置換すれば、ある程度パターンを揃えられます。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"丁目","-"),"番地","-"),"号","")
Microsoft 365版のExcelであれば、より柔軟なパターンマッチングができるREGEXREPLACE関数も利用できます(買い切り版のExcel 2021以前には搭載されていません)。
=REGEXREPLACE(A2,"(\d+)丁目(\d+)番(\d*)号?","$1-$2-$3")
手順3:郵便番号を基準に都道府県・市区町村を補完する
都道府県が省略されている住所は、郵便番号を手がかりに補完できます。日本郵便が無料公開している郵便番号データ(KEN_ALL.CSV)をダウンロードし、VLOOKUPで参照します。
- 日本郵便の郵便番号データダウンロードページからCSVを取得
- 作業ブック内に読み込み、郵便番号をキーにした表を作成
- 住所リスト側で =VLOOKUP(郵便番号,郵便番号マスタ,3,FALSE) のように都道府県・市区町村を取得
手作業の限界
ここまでの手順を丁寧に行っても、実務では次のような壁にぶつかります。
- 「丁目・番地・号」の省略パターンが多すぎる——「7番1号」を「7-1」と書く人もいれば「7番地1」と書く人もおり、SUBSTITUTEの置換パターンをいくら追加してもキリがありません
- 同じ住所でも異なる文字列になる——「1-7-1」と「1丁目7番1号」は人間には同じ住所とわかりますが、文字列としては別物なので、完全一致の照合では同一と判定されません
- REGEXREPLACEはMicrosoft 365限定——買い切り版Excelやオフライン環境では使えません
- 建物名・部屋番号が住所欄に混在していると、正規化の前に分離作業が必要になります
これらを本当に解決するには、日本全国の住所を丁目・番地単位で保持した住所マスタと照合しながら正規化する仕組みが必要で、これはExcelの関数だけで組むには荷が重い作業です。
住所の正規化エンジンをそのまま使う方法
リストクリーナーは、オープンソースの住所正規化エンジン(Geolonia normalize-japanese-addresses)を使い、「1-7-1」と「1丁目7番1号」のような表記ゆれを自動で同一住所として正規化します。ファイルをアップロードするだけで完結し、データはブラウザの外に出ません。無料で100行まで試せます。
無料で住所を正規化してみる- 都道府県データはExcelの標準機能だけで完全に正規化できますか?
- 都道府県・市区町村レベルであれば郵便番号マスタとの突合でかなり対応できます。ただし丁目・番地レベルの表記ゆれは、専用の住所正規化エンジンを使わないと限界があります。
- REGEXREPLACEが使えない場合はどうすればいいですか?
- SUBSTITUTE関数を複数組み合わせることである程度は代替できますが、パターンが増えるほど数式が複雑になり保守が難しくなります。