住所一覧などで、都道府県と、それ以降の部分で分割したくなること、ありますよね。
やりましょう。
先に結論（解説長いので）
都道府県までで区切る式。
=LEFT(住所,MIN(IFERROR(FIND({"都","道","府","県"},住所,3),LEN(住所))))
住所をA2などのセルに置き換えてください。
この時、式を入力したら、Ctrl＋Shift＋Enterで確定してください。
次に、都道府県より先の部分を切り出す式。
=RIGHT(住所,LEN(住所)-LEN(都道府県))
こちらも住所をA2などの参照先のセルに置き換えてください。
あと、都道府県を先ほどの、都道府県までで区切った結果があるセルに置き換えてください。B2など。
あとはコピーなりオートフィルなりで、最終行まで反映させれば完成です。
（古いバージョンではコピーできない場合もあるので注意。その場合はオートフィルを使いましょう。）
ここから先はただのお勉強。
都道府県で区切る式の解説
都道府県で区切る式について解説していきます。
=LEFT(住所,MIN(IFERROR(FIND({"都","道","府","県"},住所,3),LEN(住所))))
階層が深いところから順に進めていきますので、FIND、LEN、IFERROR、MIN、LEFTの順に解説していきます。
FIND
FIND関数は、検索したい文字を、ある文字列の中から探し出し、何文字目で見つけたかがわかる関数です。
例えば、北海道札幌市の中から、道という文字が何文字目にあるのかを探してみましょう。
=FIND(検索文字,対象,開始位置)
検索文字を、対象から探しだします。ただし、検索は開始位置で指定した文字数目から開始します。
=FIND("道","北海道札幌市")とすると、３という結果が返ってきます。
3文字目に道という文字を見つけたということですね。
開始位置は省略可能で、省略した場合は対象の先頭から、検索文字を探し始めます。
たとえば省略せずに,4をつけ、=FIND("道","北海道札幌市",4)とすると、北海道札幌市の札から先に、道があるかを探しにいきますので、結果は見つからずに、エラーとなります。（#VALUE!）
それでは今回の式をみてみましょう。
FIND({"都","道","府","県"},住所,3)
- 検索文字：{"都","道","府","県"}
- 対象：住所（今回は北海道札幌市としましょう）
- 開始位置：3
という作りになっています。
検索文字が特殊な形になってますが、これは配列というものです。
詳細はここでは省きますが、こうすることで、都、道、府、県の分だけ、FINDを繰り返してくれると思ってください。
そして今回の場合、以下の結果となります。
- 都・・・#VALUE!
- 道・・・3
- 府・・・#VALUE!
- 県・・・#VALUE!
ちなみに、FIND単体で使う場合は、配列を使ってもほとんど意味がありません。
最初に指定した検索文字の結果しか返ってこないので、FIND({"都","道","府","県"},"北海道札幌市",3)とした場合、最初の都でエラーになってるので、#VALUE!が表示されておしまいです。
最後に、開始位置を３としている理由ですが、京都府対策です。
都と府の両方が含まれているので、あとあと問題になります。
3文字目から検索開始させることで、京都府の都はすっ飛ばすようにし、府が3文字目で見つかったという結果にさせてます。
幸い日本の都道府県は短いものでも2文字＋都道府県だからできる方法ですね。
将来、北県みたいな短い名前が出て作られない事を願うばかりです。
LEN
LEN関数はシンプルな機能で、指定した文字列の文字数を返します。
例えば、=LEN("北海道札幌市")なら6文字なので、6となります。
今回の実際の式は
LEN(住所)
となっていますので、元の住所が何文字かをカウントしています。
IFERROR
FIND関数の上に用意したのが、IFERROR関数。
これは、エラーの場合に、#VALUE!などではなく、指定した値を返すことができるものです。
=IFERROR(値,エラーの場合の値)
値が正常ならそのまま値を、エラーの場合はエラーの場合の値を返します。
例えば、=IFERROR(SUM(1),0)ならば1となり、=IFERROR(SUM(あ),0)ならば0になります。
SUM(1)の結果は問題なく1なので結果がそのまま返ってきて、SUM(あ)はエラー（#NAME?）なので、エラーの場合の値として設定した0が返ってきたということですね。
さて、今回のIFERRORがどうなっているかといいますと・・・
IFERROR(FIND({"都","道","府","県"},住所,3),LEN(住所))
住所は北海道札幌市として、解いていきましょう。
FIND・・・エラー、3、エラー、エラー
LEN・・・6
なので、FINDの結果でエラーになるところを、6に置き換えるという式になります。
つまりIFERRORの結果は、6，3，6，6の4つという事になります。
答えが複数あると最終的に都合が悪いので、4つの結果の内、どれが１つに絞る必要があり、それを次に解説するMINで行います。
MIN
MIN関数は、指定した数値群から、一番小さい値を返す関数です。
=MIN(数値,数値,数値,・・・)
指定した数値の中から、一番小さい数値を結果として返します。
例えば、=MIN(4,3,2,1)ならば、結果は1となります。
今回のMINの式は
MIN(IFERROR(FIND({"都","道","府","県"},住所,3),LEN(住所))
となっており、要するにIFERRORの結果の中から、一番小さい値を探してるわけです。
IFERRORの解説のところで、現在IFERRORの結果は、6，3，6，6の4つとなっていますので、このMN関数の結果は3となります。
LEFT
最後はLEFT関数で、その名のとおり左から指定文字数分だけを抜き出すことができます。
=LEFT(文字列,文字数)
文字列を左から、指定した文字数分だけ切り抜きます。
今回用意したLEFT関数の式は以下のとおり。
=LEFT(住所,MIN(IFERROR(FIND({"都","道","府","県"},住所,3),LEN(住所))))
要するに、住所を左から、MIN関数の結果分だけ抜き出すという事です。
住所を北海道札幌市とした場合、先ほどのMIN関数の解説での結果が3でしたので、置き換えるとこうなります。
=LEFT("北海道札幌市",3)
結果はもうわかりますね、北海道です。
様々な関数の力を借りて、ようやく欲しい答えにたどり着けた感じですね。
都道府県より先の部分を抜き出す式
都道府県が切り抜けたら、後は簡単です。
式は以下のとおり。
=RIGHT(住所,LEN(住所)-LEN(都道府県))
今回も引き続き、住所は北海道札幌市としましょう。
RIGHT関数は、文字列を右から、指定した文字数分だけ切り抜く関数です。
そして住所は北海道札幌市、都道府県は北海道なので、
LEN(住所)＝6
LEN(都道府県)＝3
となり、これを実際の式に当てはめていくと
=RIGHT("北海道札幌市",6-3)
となり、結果は北海道札幌市の右3文字、札幌市となります。
手段はいろいろ
都道府県で区切るといっても、やり方はいくらでもあります。
わかりやすいところでは、最後のRIGHTなんて、MIDでもいいです。
途中のMIN関数も、ちょっと手をいれればMAX関数でもできます。
例：MAX(IFERROR(FIND({"都","道","府","県"},A2,3),0)
ただしこの場合、青森県青森市県営住宅のような住所の場合、青森県青森市県まで拾ってしまうという問題があるので、私はMINを使いました。
そんな中で、今回紹介した式は、以下のコンセプトのもとで組んでます。
- 県営住宅などに引っかからない（MIN使う）
- 住所をセル参照に置き換えればすぐに使える（少ない手直しですぐ使える）
- できるだけ式は短く（FINDの検索文字を配列化）
- 余計な作業領域は使わない（都道府県の配列を式内に組み込む）
もっと詰めていけばまた違う結果になるんだろうなとは思いつつも、まぁまぁ納得のいく仕上がりになったので、今回紹介に至りました。
コメント