葛飾でひっそり暮らすIT屋の G Suite メモ

Google Apps で快適業務ライフなノウハウを伝えます

Google スプレッドシートを使って住所を都道府県、市区町村郡、その他に分ける

f:id:katstech:20161006231142p:plain

事務担当「住所は都道府県を分けて記入させろってぇ!」

営業「お客さんにそんな手間かけさせたら契約率おちるわー」

そんなやり取りがあるかどうかはさておき、住所を都道府県に分けたい場合の対処方法です。

前回のエントリーで、Google Spreadsheet では正規表現が使えて失禁モノという話をしました。

www.katsushikatech.net

そしてその正規表現が、住所を分割するという面倒で喧嘩の元にすらなり得る問題をある程度解決してくれます。

都道府県というのは都道府県の文字の前に2文字か3文字の名称が入りますよね。市区町村郡も、都道府県の後に市区町村郡という文字が出てきたらそこまでを抜き出せば大丈夫。

そういったパターンがあるので、正規表現でわけることができます。ただし、精度は100%ではありません。

世の中には理解しがたい地名というものが結構たくさんあるものです。

それでは参りましょう。

今回使う関数は REGEXREPLACE、SPLIT

まず、REGEXREPLACE ですが、これは正規表現で検索した文字列を別の文字列に置き換えることができます。

SPLIT はある文字列を、特定の文字で区切って複数のデータに分割します。

例えば、

あきたこまち-ささにしき-こしひかり

という文字列を "-" で区切って

| あきたこまち | ささにしき | こしひかり |

と言った具合に。

説明だけだとわかりにくいので、Spreadsheet を見ていきましょう。

まず、REGEXREPLACE で都道府県をわけるのですが、ここでは都道府県と市区町村、それ以降の間に "+" を入れています。

=REGEXREPLACE($A2,"^(..+?[都道府県])(.+?区|.+?[市町村])(.+)","$1+$2+$3")

この "+" の部分はなんでも良いんですが、住所に使われない文字を選ばないといけません。

この "+" を入れることで次の SPLIT 関数で住所をセルに分けることができるのです。

SPLIT 関数では REGEXREPLACE で生成した住所を "+" で分割しています。

=SPLIT($B2,"+")

SPLIT 関数の結果をみると Excel では見ないような現象があります。

それは、セルに記入した関数の結果が複数の列にまたがって表示されること。これは Google Spreadsheet の特徴です。

Spreadsheet の場合は関数を使って計算した結果が複数の列、行になることがあるんです。そのおかげで UNIQUE や FILTER といった関数も使えるんですね。

こうしてめでたく、住所を分割することができました。

正規表現の解説

正規表現に興味がないけれども結果だけ欲しいという場合は上記の手順を実行してもらえればそれでできると思います。

ここからは、正規表現に興味のある方向けに、今回使った正規表現の解説をしていきます。

あくまでも簡易的な説明なので、詳しくは正規表現のドキュメントを読んでいただくとよいでしょう。

正規表現の構文正規表現の構文

今回使った正規表現はこちら。

^(..+?[都道府県])(.+?区|.+?[市町村])(.+)

一番最初にある "^" は行の最初を表す記号。この場合だと、先頭から都道府県が現れるまでの文字列を探し出します。

次に、"()" のペアが3つでてきます、これはキャプチャといって、カッコで囲っている部分を置換後に使用するためにあります。

=REGEXREPLACE($A2,"^(..+?[都道府県])(.+?区|.+?[市町村])(.+)","$1+$2+$3")

上記の関数の3番目の引数のところに "$" があります。この "$" と数字を組み合わせたところに "()" でくくった文字を入れることができます。

"$1" なら1番目のカッコ内、"$2" なら2番めのカッコ内ということですね。なので、これを使うと文字列の前後を入れ替えるみたいなこともできます。

次に "." です。これは任意の1文字を表します。何の文字でもいいから1文字だけ検索するということ。

そして、".+?" はどんな文字でもいいから一番短くマッチするという感じです。例えば、"あいうえおあいうえお" の場合 ".+?う" を検索すると "あいう" までが対象になります。

その次に出てくるのが ""。これは "" で囲まれた文字の中のどれかを探すという感じです。

なので、 "[都道府県]" の場合は都道府県のどれかの1文字を探します。

"^..+?[都道府県]" の場合、行の最初に1文字あって、さらに都道府県にマッチするまでの最短の文字列を探します。そうすることによって、47都道府県名を取り出すことができるんですね。

ちなみに、"^.+?[都道府県]" でも良さそうに見えますが、これだと "京都府" が "京都" までしか検索できません。

市区町村についても都道府県と同様ですが、一箇所だけ "|" という記号があります。

これは "|" の前後のどちらかで検索するという感じ。まずは ".+?区" を探して、無ければ ".+?[市町村]" を探すというもの。

なぜこんなことをしているかというと、千葉市中央区のような "市" のあとの "区" までを行政区としているところがあるからです。

住所ってめんどい。。。

そんな感じで、正規表現を使うと比較的簡単に住所をわけるような文字操作ができるのです。

事務系の仕事をしている人は、使えると便利なんですけどね。まだまだプログラマーとかにしか使われてないっぽいです。

もったいない。

興味を持った方は書籍を読んでチャレンジしてみると、その深い世界にどっぷり。