EXCELで会員管理（第４回）住所から市区町村は取り出せる？
第３回 住所から都道府県を取り出し対応する管轄支店を入力する？
前回まで
いきなり1000件の会員情報をエクセルに入力しなければいけなくなったミカミ。株主総会というイベントを間近に控えた多忙な毎日の中で、万全な事前準備で順調に入力を終え、またブチョーのルールお構いなしのデータも、関数や置換機能を駆使し、わずかな時間で修正を終えることができた。住所から都道府県を取り出すといった文字列操作を行っている最中にわいた疑問。市区町村だけを取り出すという事はできるのか？都道府県名が入力されていたり、入力されていなかったりする場合はどうすれば良いのか？
少し時間もあるから、この前の疑問について。市区町村名だけを取り出す事はできるのか？都道府県名が入力されていたり、入力されていなかったりする場合、都道府県名を取り出す事はできるのか？について説明するね。
結論から言うと、郵便局の提供している郵便番号データをダウンロードして使う。そこには郵便番号に対応する、都道府県名、市区町村名などが入っているから、それをVLOOKUP関数つかって取り出すんだ。
へー。そういうデータも公開されているんですね。
うん。ちなみに全国の郵便番号データは12万件あるよ。それを使う。
そんなにあるんですね。
これって入力した会員データの郵便番号が間違っていたら、うまく転記できないんじゃ・・・。
おーーー。スルドイね。そうなったら入力データをメンテナンスしていくしかないね。
●このエントリの内容
郵便番号データのダウンロード
それじゃ、早速ダウンロードしてみようか。下のリンクから郵便局のダウンロードページに行けるよ。
なんかいっぱいあるけど、どれをダウンロードしたら良いんですかね？
今回はカナは使わないからどれでも良いよ。よくわからなければ「読み仮名データの促音・拗音を小書きで表記するもの」をダウンロードしておけば問題ないかな。そこから全国一括をクリックしてダウンロードして。
はい。全国一括をクリックして・・・。
ken_all.zipってファイルをダウンロードするんですね。とりあえずデスクトップに保存しておきますね。
zipファイルとCSVファイルについて
気になったんですけど、CSV形式とか、zip形式って何ですか？
CSVっていうのは Comma Separated Values の略で、シーエスブイと読むんだ。
CSV形式は、値をカンマ（,）で区切って書いたテキストファイルのこと。ほとんどのアプリケーションで、CSV形式でデータを出力する機能、CSVファイルを取り込む機能がサポートされてから、アプリケーション間のデータのやり取りに使われることが多いんだ。
？？？？？？？
えーと。CSV形式じゃなく、エクセルのファイルを公開するんじゃダメなんですか？そっちの方がわかりやすいのに。
たまたま僕らはエクセルで使おうとしているけど、別の表計算ソフトを使う人もいるし、基幹システムでデータを取り込むためにシステムエンジニアさんが使うかも知れないでしょ。全ての要望にイチイチ全てに対応はしてられないから、汎用的な形式でデータは提供するので、後は自由に使って下さいってことさ。
なるほど。CSVって単語はチョー重要ですね。もっと詳しく教えて下さい。
そうだね。CSVファイルをエクセルで開く方法、CSVファイルがどういったものか？をもっと知りたければ、ちょっと古いんだけど、僕が書いた詳しい記事があるから参考にしてみてね。
CSVって何？ | どんと来いCSV for Excel | SILAND.JP
リンク先を紹介するなんて、ちょっと手抜きっぽいですね・・・。おおー。カンマで区切られた値ですね！よくわかります。
zip形式ってのは、データ圧縮のフォーマットなんだ。圧縮ってのはサイズが大きいものを小さくする事。ネットからダウンロードする時、ファイルサイズ大きいとダウンロードにも時間がかかるよね。その短縮のため。
あと、zip形式だと複数のファイルを１つのファイルにまとめる事ができるんだ。だから、このフォルダの内容をまるっとアップロードしたい、ダウンロードして欲しい、メールで送りたいって時に使われるんだ。
ダウンロードしたzipファイルを使うときの注意点として、ダウンロードしたファイルをダブルクリックすると中をみる事はできる。zipファイル内にエクセルファイルは開いて内容は確認できるけど上書き保存はできないんだ。保存したつもりで閉じてしまって、入力したデータが消失してしまった、なんて事があるから、ダウンロードしたzipファイルは、かならず展開（解凍）し、展開したファイルを使うようにしてね。
ふーん。zip形式ってふとん圧縮袋みたいなもんですね！
空気抜いて圧縮すれば保管スペースも小さく済むし、洋服だったら何枚も１つの袋に入れられるし、透明だから何が入っているかはわかるけど、そのままじゃ使えない。使うには袋から出さなきゃいけない。ふとん圧縮袋から出すのが展開・解凍って事ですね。
ふとん圧縮袋って。
あーでもイメージ的には近いかな。
zip形式の展開（解凍）方法については、これも僕が書いた記事を参考にしてみてね。
実際のデータと説明を比較して確認する
ダウンロードして、デスクトップに保存しておいた、ken_all.zipを右クリックして「全て展開」すれば良いんですね。
デスクトップにken_allってフォルダが作成されて、その中にKEN_ALL.CSVってあります。これをダブルクリックして開けば良いんですね？
慣れてきたらもっと良い方法があるんだけど、とりあえずダブルクリックして開いて良いよ。
開きましたが・・・。なんですかコレ？項目名が無いから、何が何やら全然わかりません。
ちゃんと、郵便局のWebサイトで郵便番号データの説明もされているから、それと照らし合わせるしかないんだ。面倒だけどね。
なんかー。書いてある事はしっかり読まなきゃダメって事ですね。
それじゃあ、郵便番号データの郵便番号データファイルの形式等の説明を見て項目名を入力していってみます。
行を挿入して・・・と。
全国地方公共団体コードって何ですかね・・・10番以降も何のことやらさっぱりわからないです・・・。
明らかに使わない列は消しちゃおう。カナとか使わないよね。10番以降も消しちゃおう。
はい。できました。こんな感じでいいですかね。
全国地方公共団体コードは、情報処理の効率化と円滑化に資するため、コード標準化の一環として、総務省（当時：自治省）が昭和43年に全国の都道府県及び市区町村のコードを設定したものなんだ。ウチの給与計算システムでも自治体コードって名前で入力しているでしょ。
あー、はい入力しています。それなんですね？でも桁数が違うような？？？
長野県長野市なら202011って入力してますけど、これだと20201になっていますよ？
最後の1桁は入力間違えがないか確認するためのチェックディジットになるんだ。説明すると長くなるから詳しくはWikipediaを読んでね。
12万件のデータを加工する（郵便番号と自治体コード）
加工をはじめる前に、まずデータ件数を確認しデータもざっとで良いから確認するクセをつけよう。どうしたら連続データの末端まで一気にカーソルを移動できるか覚えている？
たしか[CTRL]+[↓]を押してみれば・・・。おおー12万件以上もデータがあるんですね。
[CTRL]+[HOME]でA1セルに、[CTRL]+[END]でワークシートの末端に移動ですね。[SHIFT]を押しながらだと範囲指定ができる、と。
あれ？データちょっと変ですよ。だって北海道の郵便番号が6桁になっています。郵便番号は7桁ですよね？
そうなんだ。
CSV形式のファイルをダブルクリックで開くと、数値に変換できるものはExcelが数値データに変換してしまう。だからCSVファイルを開く時に変換しないように指示しながら開くか、開いた後に自分で使いたい形式に変換するか、どちらかで対応する必要があるんだ。今回は後者になるね。
例えば郵便番号。今は数値になっているから頭ゼロが飛んでしまっている。今回、北海道なら 060-xxxx という形式で使いたい。ハイフンで区切った形の文字列にしたい。数値を書式設定した文字列に変換する場合はTEXT関数を使うんだ。
まず、今の郵便番号の隣に列を挿入する。そしてそこに
=TEXT(C2,"000-0000")と入力して、最終行までコピーしてみよう。
数式を入力して、数式を入力したセルの右端をダブルクリックして最終行までコピーっと。おっと。さすがに12万件もあると少しだけ時間かかりますね。
おーーー。できました。
かかるって言っても数秒でしょ。じゃあ、それを数式から値だけに変換しちゃって。
[CTRL]+[C]でコピーして、右クリックから「形式を選択して貼り付け」→「形式を選択して貼り付け」で「値」を選択しOKですね。
コピー元と同じ範囲を指定する事で、数式が入っているものを、その結果の値のみに上書きできるんですよね。
キーボードを使った範囲指定を覚えると、データ件数が10件でも100,000件でも手間もスピードも全く変わらないのが不思議です。件数は1万倍もあるのに手間は変わらない。マウスでドラッグして範囲指定していると、100件200件でもイライラしちゃうのに。
だよね。
マウスだけで範囲指定している人は、めちゃめちゃ時間を損しているんだよ。
続けて、自治体コードも桁を揃えた文字列に変換しておこうか。
=TEXT(A2,"00000")と入力して、最終行までコピー。値のみに変換ってのをやっちゃってもらえるかな。
はい。できましたー。
TEXT関数の表示形式で0を5個指定すると足りない分を0で埋めて5桁になるんですね。
あとは、VLOOKUP関数の検索値と比較するために郵便番号を一番左端にしよう。いらない列もあったら削除してしまおう。データ件数が多いから必要な列のみにする。TEXT関数で作った郵便番号と自治体コード、都道府県名、市区町村名だけにしよう。
できましたー。でも自治体コードはなんで残したんですか？
市区町村で並べ替えしたい、ピボットテーブルで集計したい時に、このコードがあると便利からかな。
VLOOKUP関数で転記する際の注意事項
あとはVLOOKUP関数を使って、会員情報に入力されている郵便番号を検索値にして、郵便番号データから市区町村名を転記するようにすればOKなんだけど。
けど？
慣れないうちは、会員情報と同じブックにシートを作成して、そこに郵便番号データをまるっとコピーしちゃった方が良いね。で、VLOOKUP関数ではそのシートを参照するようにする。
わざわざ同じブック内にコピーした方が良いんですね？
関数で異なるブックの値を引数にすると、何を参照しているのかわかりづらくなる。そして「ファイルを開く時にリンクの更新をしますか？」と毎回言われてウザくなる。リンク元のファイルを移動、削除しちゃうとリンクの更新すらできなくなる。時が経つと誰もどうして良いかわからないメッセージが表示される奇怪なブックになる。
異なるブックを参照するような数式を入れると、ファイルを開くときにリンクを更新するかどうかの問い合わせが入るようになります。
ファイルを移動したり名前を変更したりすると、リンクの更新ができなくなります。
リンクの編集からリンク元を変更する事はできますが、このような状態に陥る方は、そもそもリンクの更新をやるべきかどうか？リンク元のファイルが何か？すら把握できていない事が多いです。
僕は絶対に異なるブックの値を引数にするような事はしない。すぐに数式を値のみに変換するなら別だけど。
ああー、リンクの更新のメッセージってこういう事すると出るんですね。わたし、いつもよくわからないからそのままキャンセルしています。
異なるブック間で数式をコピーする際にも起きやすいね。
このシートの数式をコピーして、別のブック（ファイル）のシートに貼り付けした。数式がよくわからないから、とりあえず誰が作ったかわからないけど、できているシートの数式をコピーして使おうとすると、こんな事態を起こしやすい。
よくわからないで、いい加減に使い回ししようとすると、更によくわからない状況に陥るんですね。
どこかでそのスパイラルを断ち切れつと良いね。
できるだけわかりやすく、シンプルに。自分自身がわかりやすければ、他人にも、コンピュータにもわかりやすいんだよ。とりあえず今なんとかなれば良いっていうのと正反対だからね。
うう、痛い言葉ですーー。
VLOOKUP関数については、EXCELで会員管理（第３回）住所から都道府県を取り出し対応する管轄支店を入力する？のエントリを見てね！
VLOOKUP関数で住所の都道府県に対応する管轄支店を転記しよう！
出た！手抜き！
じゃないから・・・
最後に
全６回でお届けするエクセルで会員管理について。
第４回目は郵便局の提供する郵便番号データを活用する方法になります。
インターネット上には色々なデータが提供されていますので、これらをうまく活用できるようになりたいものです。
ネット上でダウンロードできるzipファイルって何？CSVファイルって何？という事から、提供されているデータがそのままでは使えない時に自分で変換する方法、異なるブックの値を引数にするとリンクの更新がやっかいだから、極力同じブックに貼り付けてから行うという事についてまとめてみました。
また、郵便番号データは全国版だと12万件もありますが、エクセルでは10件加工するのも、10万件加工するのも、数量が1万倍になっても手間はほとんど変わらないという事実を知っていただきたくて書きました。
