エクセルで作成された住所録に、なぜか郵便番号が入っていない！
一つずつ手入力なんて、とてもできない！
これ、意外と事務さんあるあるの悩みではないかと思います。
※地番は適当です。
今回は『既存の住所録に、郵便番号を一括追加する方法』をご紹介します。
画像は拡大できるよ
PHONETIC関数は使えない
「住所から郵便番号へ変換」などと検索すると、
で出来る！というような結果を見たことはありませんか？
最初にお伝えすると、PHONETIC関数では解決しません。
残念ながら、この関数が有効利用できるのは「新しく住所録を作った場合」なのです。
既存の住所録では役に立ちません！
▼多くのページで「できる」と言っているのは、こういうことです。
❶ A1に郵便番号を手入力 → 変換で住所を選択
❷ B1に「=PHONETIC(A1) 」と入力 → 郵便番号が自動で出てくる
既存の住所録でこの関数を使っても、
一つ一つ手作業するのと同じになっちゃう！
最終的にやりたいこと
結局、既存の住所録には、既存の郵便番号表が不可欠！
そこで登場するのが「郵便番号データ」です。
そしてエクセルには「VLOOKUP」という、使いやすい関数があります。
❶ この二つをかけ合わせれば！
❷ こういうカップリングが誕生して、
❸ 郵便番号が反映されるので、
❹ あとはオートフィルで一括追加！
すごく楽＆効率的！
郵便番号を一括追加する方法
郵便番号データを入手する
❶ 郵便番号データは、日本郵便の公式ページからダウンロードできます。
❷ データが必要な都道府県をクリック
❸ ダウンロードしたフォルダの上で右クリック →「すべて展開」を選択
❹ 新たにできたフォルダの中にcsvが入っているので、これを開きます。
郵便データcsvを加工する
前述の「最終的にやりたいこと」でお伝えした通り、セル内の文字が完全一致しないとVLOOKUP関数は使用できません。
そこで「既存の住所録」と「郵便番号データ」の文字を一致させる加工を行います。
すでに一致している人は問題ないよ。
一致していない人は、一緒にやってみよう！
まず、csvを開いた時点では、このような感じです。
このうち、使うのはここ！
住所を加工する（セルの文字をつなげる）
❶ まず、住所をつなげます。
I列の右に列（J列）を追加し、J1セルに以下の数式をコピペします。
▼ こんな感じ。
❷ J1セルの右下にマウスカーソルを置くと、黒い十字に変わります。この状態でダブルクリックすると、一番下まで数式がコピペできます。
これがオートフィルだよ！
❸ J列を選択して「Crtl+C」（コピー） → Jの上で右クリック →貼り付けオプションから「値」を選択。
これで数式から、ただの文字列になります。
もし保存をしたい方は、「ファイル」→「名前を付けて保存」→「場所の選択」→「ファイルの種類」でExcelブックを選択しましょう。
csvのままでは上書き保存できません！
郵便番号を加工する（ハイフンを入れる）
郵便番号データでは、最初は連続した7ケタの数字です。
1000000
この郵便番号を
100-0000
このようにハイフン入りにします。
必要ない人は飛ばしてね！
❶ まずC列の右に4列を追加します。
❷ D1セルに、以下の数式をコピペします。
❸ E1セルに、『-（ハイフン）』を入力します。
❹ F1セルに、以下の数式をコピペします。
❺ G1セルに、以下の数式をコピペします。
❻ D1からG1までを選択し、G1セル右下の黒い十字が出たらダブルクリック。
❼ D:G列が一括でオートフィル出来ます。
❽ PCが苦手な方は、これもやっておきましょう（省略してもOK！）
G列を選択して「Crtl+C」（コピー） → Gの上で右クリック →貼り付けオプションから「値」を選択。
これで数式から、ただの文字列になります。
なぜ文字列にする必要があるかというと、数式のままでは反映場所がおかしくなったり、ファイルを閉じると反映されなくなったりするので、あとで「なんか変！」と、混乱しないためです。
最初から文字列にしてしまったほうが安心！
住所録を加工する
住所のセルに「地番」まで含まれている場合は、次の方法でセルを分割します。
❶ 既存の住所録を開きます。
データを反映させるためには、地番部分が邪魔ですね。
これを後ろのセルに切り出しましょう！
あとで絶対に役に立ちます！！（理由は後述）
❷ 住所が入った列（下記画像ではB列）を選択します。
❸ 上メニュー「データ」→「テーブルまたは範囲から」を選択します。
❹ ダイアログボックスが開くので、OKを押します。
❹ パワークエリが開くので、「列の分割」→「数字以外から数字による分割」を選択。
❺ 数字部分が分割されたら、左上のアイコン（「閉じて読み込む」の文字じゃない）をクリック。
❻ 新しいシートにテーブルができます。……が、テーブル機能は邪魔なので、これを解除します。
テーブル内ならどこでもいいので、どこかのセルをクリック → 上メニュー「テーブルデザイン」→「範囲に変換」を選択。
❼ 色がついている列（下図の場合はA:D）を選択し、「クリア」→「書式のクリア」を選択。
❽ 分割された地番部分を、CONCATENATE関数でつなげます。
❾ ここまでやってきたように、E列はオートフィル → 文字変換します。
その後、B:D列を削除します。
❿ エラーは範囲選択すれば、一括解除できます。
⓫ 元のシート（もともと住所録があったシート）にコピペで戻します。
住所録に反映させる
ここまでくれば、あともう少し！
VLOOKUPで、最後の仕上げです！ 頑張って！
郵便データの位置を変える
VLOOKUP関数を使うために、下準備します。
❶ 郵便データcsvの「住所」を、「郵便番号」の左に持ってきます。
住所録への反映とVLOOKUPの使い方
❶ 住所録の郵便番号のセル（下図の場合はA1）に、VOOKUP関数を入力します。
❷ VLOOKUPがよく分からない方は、以下をご覧ください。
よって、今回はA2セルに、以下の数式を入力します。
❸ ついに、やりたかったことが目に見える形になりました！
❹ あとは、オートフィル → 文字変換を行えば完成です！
長かったよね！
本当にお疲れさまでした！！
コメント