PR

 本誌はこれまでExcelのさまざまなテクニックを紹介しています。住所から都道府県名を抜き出す数式もありました。日本の都道府県は、名前が4文字なのは3県だけで残りの44都道府県は3文字だという特徴を使ったものです。

 同様のテクニックを使って市町村名以下の文字を抜き出すこともできます。図1のE3セルにその式が入っています。都道府県名ではLEFT関数で左側から文字を取り出しましたが、市町村名以下の文字を取り出すためにMID関数を使いました。都道府県名の次から100文字取り出しています。市町村名以下が100文字を超えることはないでしょう。

拡大表示
図1 都道府県名を抜き出す数式をわずかに変更すると市町村名以下の住所を抜き出す数式もできる。ただし間違った都道府県名があってもそのまま抜き出してしまう

 この式、左隣のセルに都道府県名が入っているのが前提なら、もっと簡略化して「=SUBSTITUTE(C3,D3,"")」とすることも可能です。SUBSTITUTEは文字列を置換するための関数です。住所の文字列から都道府県名を「空文字」に置換することで都道府県名を消去しています。

 ただしここまでの数式は元の住所データに誤りがあっても知らん顔です。図1をご覧ください。「茨木県」「神木川県」などと変な県名があります。9 行目は「北海道」が抜けているために札幌市が都道府県名になっています。

 こんな間違いを見つけ出す機能も付加した関数を作ってみました。使うのはExcelのマクロです。都道府県名はtdfk関数、市町村名以下はscs関数と名付けました。間違った都道府県名や異字体、旧字体、余分なスペースなどを検出します(図2)。

拡大表示
図2 都道府県名を抜き出すtdfk 関数、市町村名以下を抜き出すscs関数を自作した。間違った都道府県名や異自体、旧字体、余分なスペースなどがあると「■間違い■」と表示する

 図3にプログラムリストを示しました。日経パソコンのWebページではマクロの入ったExcelのブックファイルをダウンロードできるようにしてあるのでぜひお試しください。

拡大表示
図3 自作の関数をマクロで作成した。47都道府県名をあらかじめプログラムの中に書いてあり、住所先頭の3文字または4文字と比較する。市町村名以下を抜き出す関数は内部で都道府県名を抜き出す関数を呼んでいる

 プログラムの中に47都道府県名をすべて書いてあります。住所文字列の先頭3文字を取り出して、3文字の都道府県名と照らし合わせています。同様に先頭4文字を4文字の県名と照らし合わせます。どちらかで合致していれば都道府県名として出力します。さもなければ「■間違い■」となります。

 住所が間違っていても自動修正する機能はないので手動で修正してください。また「東京都札幌市」など、都道府県名と市町村名の組み合わせが間違っていても検出できません。あらかじめご了承ください。

* 2005年6月27日号特集1「Excel会心の一手」。この記事は別冊『日経パソコンスキルアップ講座大全集(1)Excel必修テクニック』にも収録しています。

■解説で使用したExcelファイルを以下からダウンロードできます。

 自己解凍形式(47KB)
 Excelファイル形式(46KB)

 うまくダウンロードできない場合は日経パソコン講座ファイルのダウンロード方法をご覧ください。