こんにちは。開発チームの加藤です。
今回のコラムは、住所リストでよくある「都道府県を分ける」時の便利な機能をご紹介いたします。
以前【EXCEL術】で関数を利用した都道府県の分け方を紹介いたしましたが、今回は関数を使用せずに分ける方法をご紹介いたします。
フラッシュフィル機能を使う
都道府県ごとの分析やシステムへのデータ取り込みの際、都道府県と以降の住所を分ける必要がある場合があります。その際に、都道府県とそれ以降の住所が分かれていないデータを支給されることがあります。
そこで今回はExcelのフラッシュフィル機能を使って、1つのセルに入った住所を複数セルに分ける方法をご紹介いたします。
*フラッシュフィルとはExcel2013以降のバージョンで利用できる機能で、規則性を検知するとデータを自動的に入力します。
次の表をフラッシュフィルを使って、住所から都道府県だけを抽出してみます。
都道府県を抽出
まず、１つ以上のセルにExcelに規則性を与えるためのデータを入力します。
今回は都道府県を抽出したいので、C2のセルに「東京都」と入れます。
同じ列の空欄を選択し、データ→フラッシュフィルを選択します。
すると、自動的に住所から都道府県が抽出され、C列に都道府県名が入力されました。
しかし、神奈川県と鹿児島県のみ、「県」がついていません。
*47都道府県の中で神奈川県、鹿児島県、和歌山県が４文字の都道府県になります。
実は、Excelは「都道府県名を抽出する」という規則性ではなく、「住所が入っているセルの最初3文字を抽出する」という規則性を検知し、C列にデータが自動入力されたので、神奈川県と鹿児島県だけ４文字目の「県」の字が抜けていたのです。
そのため、この2つの県のみ、県の文字を付け足す必要があります。
手作業でこの2県のみ県を付け足していくのは大変です。そこでExcelの置き替え機能を使います。
ホーム→検索と選択→置替を選びます。
検索する文字と置き換え後の文字を入力します。
検索はシートまたはブック単位でされます。今回はセル内に「神奈川」と入っているところに「県」を付け足したいので、「セル内容が完全に同一であるものを検索する」にチェックを入れます。
鹿児島も同様にして「県」を付け足します。
都道府県以降の住所だけを抽出
では次に、都道府県以降の住所だけを抽出してみます。
この場合も同様に、Excelに規則性を与えるためのデータを入力し、フラッシュフィル機能で都道府県以下の住所が抽出します。
神奈川県と鹿児島県の住所のみ、最初に県の文字がついてしまっています。
こちらも、Excelは「都道府県を除いた住所を抽出」という規則性ではなく、「住所が入っているセルの最初3文字を除いて、それ以降を抽出する」という規則性を検知し、D列にデータが自動入力されたからです。
この2つの県のみ、県の文字を消していく必要があります。
もしデータ量が多いと、その中からこの2県を探すのは大変です。そこでExcelの検索機能を使い、検索して、県の文字を消していきます。
*並び替えでリストを50音順に並べ、以降の住所が県から始まるものを探すことも可能です。
ホーム→検索と選択→検索を選びます。
検索する文字を入力します。検索はシートまたはブック単位でされます。
検索する文字列に「神奈川県」と入れ、「セル内容が完全に同一であるものを検索する」にチェックを入れます。
そうすると、セルに「神奈川県」とだけ入っているセルが検索されます。
その横の都道府県以降の住所のセルに入っている住所の「県」を消していきます。
鹿児島県も同様にして「県」を消していきます。
【注意】
このとき、以降の住所のＤ列のセルにB列からフラッシュフィルを使って抽出したという情報が記録されているので、一度、D列をコピーし、「値と元の書式」形式を選択して貼り付けておきます。そうすることで、不要な情報を削除できます。
もし、D列をコピーし、「値と元の書式」形式を選択して貼り付けを行っていない場合、D3セルの先頭の「県」を削除すると、同じく先頭が「県」で始まるD7の住所が消えてしまいます。
このようにして、関数を使わずにフラッシュフィル機能と検索と置換機能で住所を都道府県とそれ以降に分けることができました。
市町村と番地に分ける
では次に住所を市町村と番地に分けてみます。
以降の住所から市町村と番地をフラッシュフィルを使って抽出しました。
市町村は問題なく抽出されましたが、番地は８行目のNHK札幌放送局のみ、「1条西9-1-5」と番地以外も一緒に抽出されています。
Excelが下記のように規則性を検知したからだと思われます。
市町村：「住所が入っているセルの後ろから数字が連続する部分を除いた部分を抽出する」
番地：「住所が入っているセルの数字部分から後ろを抽出する」
今回の場合、このセルE8の不要な「1条西」は手動で削除する必要があります。
【注意】
算用数字（1、2、3...）と異なり、漢数字（一、二、三...）は文字として扱われます。そのため、番地の一部が漢数字表記の箇所は文字として扱われ、市町村のセルに入力されました。
*Excelでリストを作成し、処理を行うときは、表記を合わせて入力するようにしましょう。
住所の結合
では次に、都道府県と市町村、番地と3つに分けた住所を結合します。
こちらの場合は、いずれもきちんと結合されました。
ExcelがB～Dのセルを結合すると規則性を検知したので、住所の欄には都道府県から番地までの住所が自動で入力されました。
しかし、こちらのC列のように、間に空白のセルがある場合、望むようにセルが結合されず、住所が正しく表示されませんでした。
ただ、間にあるセルが空白でない場合は、BとD~Eのセルが結合され、住所が正しく表示されました。
フラッシュフィル機能を使ってみて
このように、フラッシュフィルは、必ずしもこちらが思う規則性でデータを入力してくれるわけではありません。
あくまでExcelのアルゴリズム上で規則性を見つけてデータを自動入力してくれるのです。
そのため、フラッシュフィル機能を使用する場合、こちらが望む形でデータが入力されたか、確認する必要があります。
さいごに
いかがでしたでしょうか。
関数を使わずに住所の都道府県を分ける方法をご紹介させていただきました。
関数を使用しないので、単純でわかりやすいですが、必ずしも使い手側が望む規則性でデータを自動入力してくれるのではないので、完璧にはできません。
しかし、フラッシュフィル機能も業務効率化の手助けになるかと思います。ぜひ、機会がありましたら試していただけたらと思います。
企画開発部 開発チーム
加藤
