構造化Excelテク：小難しい関数を使わず、住所を都道府県とそれ以下に分離する方法：Tech TIPS
Excelで住所録などの都道府県名と市区町村以下を分けて、集計したいということがある。このような場合、簡単に都道府県名が分けられそうだが、これが意外と面倒だ。多少手作業は入るがお手軽な方法と、関数を使って一発で都道府県を分ける方法を紹介しよう。
対象：Excel 2016／2019／2021／365
Excelで住所の都道府県と市区町村に分ける方法
Excelで住所録などの都道府県名と市区町村以下を分けて、集計したいということがある。本Tech TIPSでは、多少手作業は入るがお手軽な方法と、関数を使って一発で都道府県を分ける方法を紹介しよう。なお、画面で使用した住所は、「テストデータ・ジェネレータ」で自動生成したものである。
「Microsoft Excel（エクセル）」で住所録やアンケート結果を処理する際、都道府県と市区町村以下を分けて、都道府県別に集計するといったこともあるだろう。また、住所を正規化する場合にも、都道府県を分けると、間違いやヌケなどが把握しやすくなる。
ただ、住所欄から都道府県を切り取って、別のセルに貼り付ける、という作業を1つずつ行うのは面倒だ。都道府県で並べ替えて、都道府県名の入力作業を楽にするという手もあるが、面倒なことには変わりない。
そこで、本Tech TIPSでは、Excelで住所の都道府県と市区町村以下を分ける方法を紹介する。多少手作業は入るがお手軽な方法と、関数を使って一発で都道府県を分ける方法を取り上げよう。
フラッシュフィルを使って手軽に分離
後述する関数を使う方法では一発で都道府県が分けられるものの、IF関数を使う必要があるなど、ついつい使い方を忘れがちだ。どんな数式だったか検索して、入力という段取りを毎回踏むのであれば、多少手作業が入っても「フラッシュフィル」を使う方法が手軽だ。
フラッシュフィルは、Excelが法則性を検知してデータを自動的に入力する機能だ。例えば、A列に姓、B列に名が入った表があった場合、「C2」セルに「A2」セルの姓と「B2」セルの名を結合したデータを入力し、C列に対してフラッシュフィルを実行すると、自動的に法則性を理解し、C列に「姓＋名」を入力するというものだ。
文字列の結合なら「=A2&B2」で簡単だが、この場合、オートフィルをした後に、列をコピーして値として貼り付け直す必要がある。この点、フラッシュフィルであれば、文字列として自動入力されるので、列をコピーする手間は不要だ。
フラッシュフィルとは（1）
Excelが法則性を検知してデータを自動的に入力する機能だ。A列に姓、B列に名が入った表があった場合、「C2」セルに「A2」セルの姓と「B2」セルの名を結合したデータを手動で入力する。
この機能を使って、都道府県名を抜き出そう。ただし、「神奈川県」「和歌山県」「鹿児島県」の3県については、後述の通り、手作業が必要になるので注意してほしい（詳細は後述）。
まず、住所欄の先頭行にある住所の都道府県名を「都道府県」列に入力する。この際、先頭行が「神奈川県」「和歌山県」「鹿児島県」の3県であった場合、それ以外の都道府県の住所の行でこの作業を行う。
都道府県を入力したら、そのセルを選択した状態で、［Ctrl］＋［E］キーを押すか、［ホーム］タブの［編集］グループにある［フィル］−［フラッシュフィル］を選択すると、「都道府県」列の残りの列に都道府県名が入力されるはずだ。もし、誤って列見出しがフィルされてしまったら、都道府県名を入力する行を変えてみよう。
フラッシュフィルを使って都道府県名を取り出す（2）
都道府県名を入力したセルを選択した状態で、［Ctrl］＋［E］キーを押すと、フラッシュフィルが実行され、都道府県名（「住所」列の先頭から3文字）が入力される。よく見ると、「神奈川県」「和歌山県」「鹿児島県」の3県は、最後の「県」が抜けていることが分かる。
ただ、よく見ると「神奈川県」「和歌山県」「鹿児島県」の3県は、「神奈川」「和歌山」「鹿児島」となってしまい、「県」がなくなっていることが分かる。都道府県が分けられたのではなく、先頭の3文字が抜き出されたわけだ。
そのため、先頭行が「神奈川県」「和歌山県」「鹿児島県」だった場合、先頭から4文字が抜き出されてしまうため、それ以外の行で「都道府県名」を入力する必要があったわけである。
ただ、これら3県以外は、全ての都道府県が3文字なので、フラッシュフィルを使えば、ほとんどの都道府県名が入力できる。
「神奈川県」「和歌山県」「鹿児島県」の3県を修正する
フラッシュフィルで正しく入力できなかった「神奈川県」「和歌山県」「鹿児島県」の3県については、別途修正が必要になる。
3県しかないので、「神奈川」を「神奈川県」、「和歌山」を「和歌山県」、「鹿児島」を「鹿児島県」にそれぞれ置換するのが手っ取り早いだろう。［ホーム］タブの［編集］グループにある［検索と選択］−［置換］を選択し、［検索と置換］ダイアログの［置換］タブを開き、「検索する文字列」欄に「神奈川」、「置換後の文字列」欄に「神奈川県」と入力したら、［オプション］ボタンをクリックして、「セルの内容が完全に同一であるものを検索する」にチェックを入れて、［すべて置換］ボタンをクリックする。
置換を使って県を追加する（2）
［検索と置換］ダイアログが表示されるので、「検索する文字列」欄に「和歌山」、「置換後の文字列」欄に「和歌山県」と入力したら、［オプション］ボタンをクリックして、「セルの内容が完全に同一であるものを検索する」にチェックを入れて、［すべて置換］ボタンをクリックする（［すべて置換］を実行するのが不安ならば、［置換］ボタンをクリックして、1つずつ確認しながら置換していってもよい）。これで「都道府県」列の「和歌山」が「和歌山県」になるはずだ。同様に「神奈川」と「鹿児島」に対しても置換を行い、「神奈川県」「鹿児島県」にする。
これを「和歌山」「鹿児島」についても行えばよい。「セルの内容が完全に同一であるものを検索する」のチェックを忘れると、「住所」列の住所までも変換され、「神奈川県県……」のようになったり、「鹿児島県市」になったりしてしまうので注意してほしい。
このように手作業は必要になるものの、関数を使わないので手順さえ覚えておけば、簡単に都道府県が分けられる。
関数を使って都道府県名を分ける
住所から都道府県を分ける方法として、関数を使うのが一般的だ。ただ、関数が少し複雑なので、引数などを間違えないようにしよう。
フラッシュフィルでは、先頭から3文字を抜き出すことで都道府県を分けた。基本的には同様の作業を関数で行うのだが、その際に先頭から4文字目が「県」であった場合は先頭から4文字を、「県」でなかった場合は先頭から3文字を抜き出すようにする。これで、一発で都道府県名を分けることができる。
もちろん、〇〇県県市や〇〇県県郡といった地名があった場合は、先頭から4文字目まで抜き出されてしまうが、幸いなことに都道府県名を含む住所の先頭から4文字目が「県」となるような地名はないようだ。
具体的には、MID関数を使って4文字目が「県」かどうかを判定し、それが「真」の場合は先頭から4文字目（左から4文字目）までを、「偽」の場合は先頭から3文字目（左から3文字目）までを、それぞれ抜き出す。
MID（＜住所セル＞,4,1）="県"
先頭から4文字目を抜き出すには、LEFT関数を使い、以下のようにする。
LEFT（＜住所セル＞,4）
これらとIF関数を使って、以下のように記述すれば、都道府県を分けることができる。
=IF(MID（＜住所セル＞,4,1）="県",LEFT（＜住所セル＞,4）,LEFT（＜住所セル＞,3）)
この式で分けた都道府県名は、一度コピーして、値として貼り付ければ、文字列になる。
都道府県名が抜けている行を探す
フラッシュフィルや関数を使って都道府県を分ける方法は、「住所」欄に入力された住所に都道府県名が正しく入力されていることを前提としている。ただ、実際の住所録やアンケート結果などの場合、都道府県名を省略したデータが含まれていることもある。
このような場合、「住所」欄で都道府県名が省略されているものを見つけるのは大変だ。一度、フラッシュフィルや関数を使って都道府県を分けて、このデータ内に都道府県名でないものを見つけるのが簡単だ。
フラッシュフィルや関数を使って分けた「都道府県」列にフィルターを適用し、都道府県名ではないものを見つければよい。フィルターを開いて、表示された都道府県名のチェックを外していくと、都道府県名でないものが見つけられる（47つのチェックを外す必要があるが）。
都道府県名でないものが見つかった場合、その住所に該当する都道府県名を入力すればよい。
関数を使って市区町村以下を入力する
都道府県名が分けられたら、それ以外の市区町村名以下を抜き出そう。簡単なのは、SUBSTITUTE関数を使って、「住所」セルから「都道府県」セルの値を削除してしまう方法だ。SUBSTITUTE関数は、置換を行う関数で、文字列から検索文字列を探し、それを置換文字列に置き換えるというものだ。置換文字列を「""（半角ダブルクォーテーションを2つ）」にすれば、検索文字列を削除できる。
=SUBSTITUTE(＜住所セル＞,＜都道府県セル＞,"")
ただ、この方法にも難点があり、「長野県長野市南長野県町（みなみながのあがたまち）」のような市区町村名以下に「都道府県名」と同じ文字列が含まれていると、「長野市南町」といったように誤った地名に変換される（かなりまれなケースだが）。
こうした誤変換を防ぐには、「住所」から「都道府県」の文字数を引いた、右側を抜き出せばよい。RIGHT関数を使い、以下のように記述すればよい。RIGHT関数は文字列の末尾 (右端) から指定された文字数の文字列を返す関数で、LEN関数は文字列の文字数を返す関数だ。
=RIGHT(＜住所セル＞,LEN(＜住所セル＞)-LEN(＜都道府県セル＞))
関数を使って市区町村以下を入力する（2）
「住所」列から「都道府県」列の値が削除された値が入力される。これをオートフィルでコピーすれば、「市区町村以下」列に市区町村以下の住所が入力できる。ただ、この状態ではセルが数式の状態なので、貼り付けオプションの［値］で貼り付けを行うこと。
これで、「住所」から都道府県名を抜いた、市区町村以下が入力できる。RIGHT関数で分けた住所は、一度コピーして、値として貼り付ければ、文字列になる。
フラッシュフィルを使って市区町村以下を入力する
手作業が必要になるが、フラッシュフィルを使う方法もある。都道府県名を抜き出した場合と同様、「神奈川県」「和歌山県」「鹿児島県」を除く住所の行で、都道府県名を削除した市区町村以下を入力し、そのセルを選択した状態で［Ctrl］＋［E］キーを押すと、住所の先頭3文字が削除されたデータが入力できる。
「神奈川県」「和歌山県」「鹿児島県」の場合、先頭に「県」が残った形になってしまうので、これを取り除く必要がある。Excelの場合、検索や置換で正規表現が使えないため、「^県」などとして、先頭にある「県」を削除するということができない。市区町村名には「県」を使ったものがあるため、「県」を一括削除するのもダメだ。
フラッシュフィルを使って市区町村以下を入力する（3）
「神奈川県」「和歌山県」「鹿児島県」の3県は、市区町村以下の住所の先頭に「県」が残った状態となっている。そこで、「都道府県」列にフィルターを適用し、「神奈川県」「和歌山県」「鹿児島県」の行のみを表示する。後は、市区町村以下の住所の先頭にある「県」を削除すればよい。
この場合、「都道府県」列にフィルターを適用し、「神奈川県」「和歌山県」「鹿児島県」のそれぞれを順番に選択し、先頭の「県」の文字を削除していくのが簡単で確実だろう。
Copyright© Digital Advantage Corp. All Rights Reserved.