EXCELで会員管理（第３回）住所から都道府県を取り出し対応する管轄支店を入力する？
第２回 ブチョーの入力した変なデータを直すのが手間なんですが？
前回まで
いきなり1000件の会員情報をエクセルに入力しなければいけなくなったミカミ。株主総会というイベントを間近に控えた多忙な毎日の中で、万全な事前準備で順調に入力を終え、またブチョーのルールお構いなしのデータも、関数や置換機能を駆使し、わずかな時間で修正を終えることができた。いよいよ集計を始める事になるが・・・。
管轄支店毎に管理するって、支店ごとにシートを作るんですか？ブチョーとか「あ」「か」「さ」「た」「な」ってシート作って、そこに大学名と採用した社員名入力して、どこの大学出身が何人？なんて作ってますよ。
うーん。典型的な悪い例だね。今回だってカナ入力してるよね。五十音順に並べたきゃカナ順で並べ替えれば良いし、フィルタや検索使えば必要なデータってすぐに探せるし抽出もできるのに。それじゃ出身大学ベースにしか集計も検索もできないよね。
「あかさたな」で作っているって事はシートが１０個あるのかな？管理したい項目が増えたら１０シート全て修正しなきゃだね。計算式も変更や追加があったら１０個直さないといけないよね。１０個のシートで同じ構成を維持するのって結構手間なんだよ。
同じ構成にこだわる必要あるんですか？
構成が変わっちゃったら大学名変更になって「あ」から「か」にコピーする時にも面倒だよね。似ているけど微妙に違うのが一番間違うよ。同じだろうっていう思い込みで。
それにこう言う作り方してる人は、各シートの合計を別のエクセルシートに入力しなおして、全体の合計計算して資料を作る・・・なんて不必要に時間と手間をかけているんだよね。手作業では良かった管理が必ずしもパソコンでの管理で良いとは限らない。むしろ「悪」になることもあるんだ。上と同じの〃もそうだよね。
イタタタ。私もやっています・・・。
ん、ていうか、そもそも人事管理システムでできるのになんでエクセルでやってるの？ブチョーは。
ずいぶん話がそれちゃったけど、そろそろ始めようか。
●このエントリの内容
管轄支店を数式で入力するまでの流れ
こういうのって数式使って一発でできるんですか？
一発じゃ無理だね。47都道府県でどの県がどの支店の管轄かってルールは決まっていれば、IF関数を47個ネストすればできなくはないけど、そんな数式面倒で書けないよ。書けたとしてもメンテなんて出来ない。もっとわかりやすくなるように考えるんだ。
まず、この県はこの支店という対応テーブルを作る。そして入力してある住所から都道府県名を取り出せれば、後はVLOOKUP関数と対応テーブルを使って一発で転記できるよね。
対応テーブルを作るのと、住所から都道府県名を取り出すのが重要なんですね。
（準備）都道府県と管轄支店の対応テーブルを作成するには？
対応テーブルってどういう風に作るんですか？
超カンタン。こんな感じで左が都道府県名、右が管轄支店という表を作るだけ。
こういったモノは、新規シートを追加してリストって名前にして、そこにまとめておくと良いよ。
あ、なんか身構えちゃいましたけど、こんな簡単で良いんですね。
リストは入力した会員データとは別のシートにしておいた方が良いんですね。なるほど、なるほど。
そ、これだけ。
でも、せっかくだから今後データをメンテナンスしていく際に便利なように、管轄支店をリストから入力できる方法も教えておくね。
まず管轄支店の名称を入力しておきます。その範囲を指定し、リボンの「数式」→名前の定義をクリックします。
名前をわかりやすいよう「管轄支店」や「管轄支店リスト」のように設定しておきます。
リストから選択して入力できるようにしたい範囲を選択します。
リボンの「データ」→「データの入力規則」→「データの入力規則」を選択します。
設定のタブから入力の種類を「リスト」にし、元の値で[F3]キーを押し、名前の貼り付けから先ほど設定した名前（管轄支店）を選択します。
名前貼り付けから選択すると簡単ですが、直接元の値の欄に、=管轄支店 と入力しても大丈夫です。
そうすると、リストから管轄支店を選択できるようになります。
リストから選択するのってどうやっているのかな？と思っていたんです。結構簡単にできちゃうんですね。でもこれ名前を定義してやらないといけないんですか？
○と×だけ入力するような、選択肢が少ないものなら、元の値に○,×とカンマで区切って値を入力するだけでもOK。
ワークシート上の範囲を指定しても良いんだけど、Excel2003以前だと同じシートからしか範囲を指定できないとか制約があるから、エクセルのバージョンを考慮せずに使いたいのであれば、ちょっと面倒に感じるけど名前を定義して、その名前を元の値に設定すると良いよ。
住所から都道府県名を取り出す方法は？
住所から都道府県名を取り出すのってどうすれば良いんですか？「都」「道」「府」「県」それぞれの文字があるかチェックすれば良さそうとは思うんですけど、超複雑な数式になりそうなんですけど。
都道府県があるから、それぞれの判定しなきゃいけない・・・という視点から抜け出せないと難しいね。
ちょっと発想を変えてみよう。都道府県名で４文字あるのは神奈川県と和歌山県と鹿児島県だけなんだ。それ以外は全部３文字。したがって４文字目が県だったら左から４文字が、それ以外だったら左から３文字が都道府県名になるんだ。
それを数式にするとこんな風になる。（F2に数式を入力）
=IF(MID(E2,4,1)="県",LEFT(E2,4),LEFT(E2,3))
MID関数で住所の４文字目から１文字取り出し、それが「県」であったらLEFT関数で左から４文字、それ以外だったらLEFT関数で左から３文字を住所から取り出すという意味。
おおーすごい。ホントですね。
なんか都道府県って文字を全てチェックしなきゃいけない、超難しい数式をつかわなきゃいけないって思い込んでたけど、発想しだいでなんですね。これなら私でもできそうです。
データ入力の際にしっかり都道府県名まで入力してもらったのは、都道府県名を簡単に抜き出すためなんだ。一番困るのが入力されていたり、されていなかったり統一されていない事なんだ。
そういう場合はどうするんですか？
郵便番号が入っていれば何とかなる。ちょっと手間ではあるけど。これは後で説明するね。
住所から都道府県名以降を取り出す方法は？
じゃあ、続いて住所から都道府県名以降を取り出してみようか。
住所から都道府県名を削除するって事ですね。
考え方としては、F列にある先程取り出した都道府県名の文字数を数える。その文字数に1を足してそれ以降を取り出す。鳥取県なら3文字だから4文字目から100文字取り出す。という感じ。
それを数式にするとこんな風になる。（G2に数式を入力）
=MID(E2,LEN(F2)+1,100)
LEN関数が文字数を数える関数なんですね。あ、なんで100文字なんですか？
住所はだいたい100文字もあれば全て入るから。心配なら200でも良いよ。
ネットで調べると、LEN(E2)-LEN(F2)みたいに、住所の文字数を数えてから都道府県名分の文字数をマイナスする方法が紹介されている事が多いんだけど、文字数が本当に推定できないような場合なら別だけど、複雑になるだけであまり意味無いから。住所が20文字しかなくても100と指定してもエラーにはならないから、100って指定しちゃった方が楽だよね。
ブチョーが入力した住所で全角スペースを削除したのは、このロジックだと都道府県名以降がスペースから始まっちゃうから。
なるほどです。
でも、これって長野県が信州に改称されていたら、もう少し面倒だったんですね。
よくもそんな古い事を・・・。そうだね。２文字が発生するからね。
どうなるかはわからないけど、道州制になったら住所変更は大変だよね。
ひえー。そうなったらどうするんですか？
ま、それはその時考えれば良いさ。
VLOOKUP関数で住所の都道府県に対応する管轄支店を転記しよう！
いよいよ本丸ですね。管轄支店の入力。
これ実はハガキの入力中に、同時に入力しちゃった方が良いのかな？ってみんなで相談していたんです。
完全にルールが決まっている事なら数式で入力しちゃうのが楽だし正確だよね。単純で面倒な作業はみんなエクセルにやってもらおうよ。
事前準備はできているから、後はVLOOKUP関数で転記するだけだね。
数式は、=VLOOKUP(F2,リスト!$A:$B,2,0)となります。
検索値はF2を指定して、範囲はリストシートのA列～B列を指定します。今回は特に絶対参照にしなくても良いのですが、VLOOKUPの範囲は絶対参照にするクセをつけておいた方が間違えが減ります。列番号は2を指定し、検索方法は0またはFALSEを指定します。（0もFALSEも意味は同じす。）
私、VLOOKUP関数が苦手なんですよ。どうやったら間違えが減りますか？
間違えても気にしないで何度もやる！に尽きるんだけど。
関数を入力する際は、よく使うものは直接入力していこう。
VLOOKUP関数であれば、=VLまで入力すると、候補にVLOOKUP関数のみ表示されるから、TABキーを押すと確定できる。
確定すると、=VLOOKUP( まで自動入力される。これならスペルミスはなくなるよね。
今回のように引数を指定する際に、別のワークシートのセル範囲を指定したい場合は、[SHIFT]+[F3]を押して「関数の引数のダイアログ」を表示させると楽かな。そのまま入力ていっても良いけどね。
へー。数式入力している時でも、[SHIFT]+[F3]を押せば「関数の引数のダイアログ」を表示できるんですね。
後はコピーすれば完成ですね。おおー、管轄支店の入力も一瞬ですね。
ちょっと質問なんですけど、市区町村名だけの取り出すって事はできるんですか？
都道府県名が入力されていない場合はどうするの？っていうのとやり方は同じだから、それは次回にまとめて説明するね。
VLOOKUP関数についての詳細はこちらの動画も視聴いただければ幸いです。
最後に
全６回でお届けするエクセルで会員管理について。
第３回目は都道府県名に対応する管轄支店の入力方法についてです。
住所から都道府県名とそれ以降を分割する方法は、入力されているデータと考え方しだいで、とても簡単に行う事ができます。
データを入力する際は、どんな集計まで行うか？ということまでしっかり考えて入力しておくと良いです。
また、今回のように、自分で対応テーブルを作成しVLOOKUP関数で必要な値を転記する、という方法はよく使います。
例えば、2015/3は何期なのか？というのを、年月に対応する会計期という対応テーブルを作っておくと簡単に取り出す事ができます。会計期間が1月～12月であればわざわざ作るまでもありませんが、そういう会計期間の会社は少数です。また月に対応する四半期なども設定しておくと集計の際に便利です。
