エクセルでお客様の住所を管理していることは多いと思いますが、一口に住所といっても管理方法は次の2つに大きく分けられると思います。
住所は1セル内に全て登録
都道府県、市区町村、町名・番地でセルを分けて登録
つまり、
住所を「都道府県」「市区町村」「町名・番地」毎に分けるのか分けないか
が、住所の管理における2大分類となります。どちらで管理しているでしょうか？
どちらの管理も一長一短ではありますが、住所を分析に使っていくとなると、都道府県、市区町村、町名・番地で分けて管理したほうが良いですね。分けておくことで、
- 都道府県別でみるとどこが多いのか
- 市区町村別でみるとどこの比率が多いのか
といった分析ができるようになります。
とはいえ、はじめから「住所を分析に使っていこう！」という意思を持って都道府県、市区町村、町名・番地で分けて住所管理していくケースはかなり稀です。大抵は「住所」という1つの項目に住所を登録する形で管理を開始し、後々になって住所を分けていきたいということが発生します。
しかしやれば分かるのですが、思った以上に住所の分割は時間がかかる作業なのです・・・。
そこで今回は、都道府県、市区町村、町名・番地がつながっている住所を、 都道府県、市区町村、町名・番地 に分割する方法を、サンプルファイルを例にまとめました。
住所分割用のエクセルサンプルファイル
とにかくサンプルを使って住所の分割をやりたい！という方はこちらからダウンロードをしてください。ファイルを開くとパスワードを求められますので、「keizokuma」と入力してください。
ファイルをダウンロードすると2つのシートが入っています。「使い方」シートの説明を見て本サンプルの使い方を確認したのち、「住所分割」シートを使用してください。
本サンプルはフル住所を入れたらすぐに都道府県・市区町村・町名番地に分割してくれるツールではありません。最終的には分割された住所を算出することができますが、それまでの間に幾つか手作業でのデータ修正や確認が必要な点がありますので、ご注意ください。
詳しくは以下で解説しています。
住所分割用エクセルサンプルの構成と手順の解説
住所分割用のエクセルサンプルファイルは、次のような構成となっています。
C列に住所を入れると、最終的にG列に都道府県、P列に市区町村、Q列に町名・番地が分割されて算出できるようになっています。東京駅の住所（東京都千代田区丸の内1丁目）を入れた場合の最終的な分割後の形はこのようになります。
東京都 千代田区 丸の内1丁目 と3つに分割できましたね。
先述した通り、このサンプルはC列に住所を入れればすぐに都道府県・市区町村・町名番地に分割してくれるわけではありません。次の手順に沿って操作をしていく必要があります。
住所分割手順１：C列にフル住所を設定
まず、C列に住所文字列を設定します。ここでは、次のポイントに注意します。
- 都道府県を含めること（市区町村から始まる住所文字列としない）
- 不要な半角・全角スペースは除去しておく（番地と建物名の間のスペースなどはOK）
上記の2は最低チェックしたうえで文字を設定します。1については次に説明する方法でチェック可能です。
住所分割手順２：D列の都道府県の存在チェックを確認
C列に住所を設定したら、D列でフィルタをかけて「★なし★」となっているデータを確認します。「★なし★」と表示されている場合、C列の住所文字列に都道府県が入っていません。
例えば次の3行目のデータでは、「東京都」という文字が入っていない住所となっています。そのため、D列は「★なし★」となっています。
このようなデータについてはC列の住所に都道府県を追加修正して、D列が全て「OK」となるようにしていきます。
なぜこのようなに「住所に都道府県が入っているか」のチェックをするかというと、元データの住所に都道府県を入れているケースと入れていないケースがあるためです。特に住所情報の管理を複数人で行っているような場合、住所入力ルールが統一されていないと都道府県ありとなしの両方のデータが混在することになります。
郵便番号があれば都道府県があろうがなかろうが住所としては問題ありませんが、住所を分割する処理においては必要なため、ここで全ての住所について「都道府県あり」の状態にデータを修正していきます。
D列ではこのような数式でチェックを行っています。（D2セルの場合）
=IF(OR(MID(C2,3,1)="県",MID(C2,3,1)="府",MID(C2,3,1)="都",MID(C2,3,1)="道",MID(C2,4,1)="県"),"OK",IF(C2="", "", "★なし★"))IF関数、OR関数、MID関数を用いてC2列の住所文字列に対して判定を行っています。
具体的には、
- 住所の左から3文字目が県または府または都または道、または、住所の左から4文字目が県の場合、「OK」と表示する。
- 1に一致しない場合で住所が空欄なら、空文字を表示する。
- 1と2に当てはまらない場合「★なし★」を表示する。
ということをここで行っています。
住所分割手順３：O列の値をコピーしてP列に値として貼り付け
D列が全て「OK」となったことを確認したら、次にO列のデータをコピーしてP列に値として張り付けます。ここでのポイントは、
P列には値として張り付ける
ということです。
まず、O列の住所を範囲選択します。
次に、右クリックからコピーを選択します。または範囲選択した状態でCtrl+cをしてコピーします。
最後に、P列の先頭セル（P2セル）を右クリックし、値として貼り付けをします。
P列は数式を設定していますので、値として張り付けないとおかしなデータとなってしまうため注意しましょう。
貼り付け後は次のようにO列とP列の値が同じようになります。
住所分割手順４：N列の要確認対象をチェック
P列に貼り付けをすると、N列にチェック結果が表示されますので、「要確認」となっているデータでフィルタをかけます。
この例では、千葉県市川市の市区町村が「市」となってるため、うまく分割できていないことが分かります。
このような場合、P列の値を「市」から「市川市」に変更します。すると、Q列の値が市区町村以降の文字となるので、うまく分割がされました。
※N列の「要確認」の表示は、P列の値を変えてもそのまま表示されます。
住所によっては計算式でうまく分割できないため、N列の数式では「計算式で分割した市区町村（O列）」に対してチェックを入れています。
※ここでは、うまく市区町村で分割されていないだろうと思われるものに対して「要確認」の表示しています。そのため、
- うまく分割されているケースで「要確認」と表示される場合
- 要確認だけれど「要確認」と表示されていない場合
があり得ます。（ここについては次の操作で説明します）
N列の数式（N2セルの場合）
=IF(OR(COUNTBLANK(I2:K2)<2,I2=1,J2=1,K2=1),"要確認","")この数式では次の条件のいづれかに当てはまる場合に「要確認」と表示しています
- I列～K列の中で空欄のセルが1つ以下
- 「区」という文字の位置が1文字目
- 「市」という文字の位置が1文字目
- 「村」という文字の位置が1文字目
住所分割手順５：P列の市区町村の確認
N列が要確認となっているデータをすべてチェックしてP列の値を修正した後は、P列の値を一通り見て問題がありそうな住所が無いかを確認します。
先述した通り、N列では、うまく市区町村で分割されていないだろうと思われるものに対して「要確認」の表示していますので
- うまく分割されているケースで「要確認」と表示される場合
- 要確認だけれど「要確認」と表示されていない場合
があり得るためです。
P列の確認方法は、目視です。アナログですが、次のようにP1セルのフィルターを開き、1つ1つ確認をしていきます。
なぜ目視なのかというと、前述の理由が主ではありますが、市区町村名そのものが間違っているというケースもあり得るからです。目視で一通りチェックをすることで、そのようなデータを見つけて修正しましょう。
住所分割手順６：R列のチェック
最後にR列が「要確認」となっているデータが無いことを確認します。
R列の書式（R2セルの場合）
=IF(G2&P2&Q2=C2,"OK","要確認")C列の住所（分割前）と G・P・O列を結合した住所（分割後）が同じかをチェックしています。
P列（市区町村）に対して修正するケースがあるため、P列を修正したら合わせてC列も修正することで、住所分割前と分割後で文字が一致することとなります。
以上で住所分割作業は完了です。
まとめ
エクセルの関数を利用した住所の都道府県、市区町村、町名・番地への分割方法についてご紹介しました。
特に市区町村以下に住所を分けるのが難しいため、サンプルのエクセルを使ってみてください。
また、この記事に書いたようなちょっとした困ったことでも、自分に経験や知識が足りないために対応できなかったり、時間をかけたわりに全然進まなかったりと、思うようにいかない事ってありますよね。そんなときは専門家に相談したり解決してもらったりする方法もありますので、一人で悩まず対処してみましょう。
エクセルを使った関連記事
エクセルを使ってよくある困ったことを解消できる場合があります。次の記事も参考に。
