Excel Solution #11
−データの変換や日付の計算に、各種の関数を活用する−
 | ||
 | ハッピーマンデーの祝日を、Excel の関数で算出する フリーランスで仕事をしていると、曜日や祝日に疎くなってしまう。そのせいで、祝日にアポを入れようとする失敗を何回もやっている。 それは、曜日に関係なく「月月火水木金金」で仕事をしている自分にも非があるのだが、「○日が祝日」ではなく「第○月曜日が祝日」と規定する、いわゆるハッピーマンデー制度の導入も、この混乱に輪をかけている。（と、他人のせいにしてしまおう） そこで、Excel の日付関数を利用して、ハッピーマンデー制度で祝日になるのが何日なのかを算出してみよう。たとえば、かつては 1 月 15 日に固定されていた「成人の日」が、現在は 1 月の第2月曜日になっている。これを例にとって、どのような数式を使えばよいのかを解説する。 まず、1 月の最初の日（1 月 1 日）が何曜日かを計算する。 =DATE（） 関数に年・月・日の値をそれぞれ数値で渡すと、対応するシリアル値を返してくれるが、年については毎年変化するので、＝YEAR（） 関数の引数に ＝TODAY（） 関数をネストさせることで、現在の年が何年なのかを調べる。月の最初の日なら、月と日はそれぞれ「1」で変わらないから、数値で直接指定してしまえばよい。その結果を ＝WEEKDAY（） 関数に渡すことで、曜日を知ることができる。 ＝WEEKDAY(DATE(YEAR(TODAY()),1,1),3) ＝WEEKDAY() 関数では、2 番目の引数(種類)として「3」を指定しているので、月曜日なら「0」を返す。月曜日が「0」なら、日曜日に対応する戻り値は「7」になる。そこで、7から先の数式の結果を引くと、月の初日(1日)から次の日曜日までの差を算出できる。 ＝7-WEEKDAY(DATE(YEAR(TODAY()),1,1),3) 各月の 1 日を基準にした「次の日曜日」とは、すなわち「第 1 日曜日」ということになる。ということは、「第 1 日曜日」の 8 日後が「第 2 月曜日」なので、先の数式で得られた結果よりも、シリアル値を 8 だけ増やせばよい。したがって、以下の数式を使えば「第 2 月曜日」が、月の先頭日から数えて何日目になるのか分かる。 ＝(7-WEEKDAY(DATE(YEAR(TODAY()),1,1),3))＋8 先にも行なったように、＝DATE() 関数に年・月・日を数値として渡せば、指定した日付のシリアル値が分かる。すでに取り上げているように、年は「＝YEAR(TODAY())」、月は 1 月だから「1」、日は上記の数式で分かるから、これらを ＝DATE() 関数にネストさせればできあがりだ。 ＝DATE(YEAR(TODAY()),1,(7-WEEKDAY(DATE(YEAR(TODAY()),1,1),3))＋8) 2 番目の引数を「1」以外の数値に変更すれば、その他の月でも第 2 月曜日の算出が可能になる。これで、祝日法の改正があっても大丈夫だ。 ちなみに、この例にあるように複雑怪奇な数式ができてしまった場合には、数式の内容を機能別に分けて、それぞれを個別に範囲名に割り当ててしまうと便利だ。たとえば、範囲名「一月の第一日曜日」に数式「7-WEEKDAY(DATE(YEAR(TODAY()),1,1),3)」を割り当てることができる。すると、この数式の計算結果を、他の数式から「一月の第一日曜日」という名前で参照できる。 通常、範囲名にはセル番地を割り当てるものだが、Excel ではセル範囲名に文字列や数値、数式を割り当てることもできる。保守性の良い数式を作るのに意外と便利なので、興味があったら試してみよう。 | |
 | ||
| ||
スムーズな引っ越しは事前の準備が肝心
IT 系の物書き、なんていう仕事をしていると、さまざまなデジタル商品を自腹で買うことが非常に多い。最近でも、ノート PC に続いて某社の HDD プレーヤーを購入したばかりだ。もっとも、自分の場合、携帯電話やデジカメは原稿のネタではなく、原稿を書くための手段なので、それほどむやみに最新の製品を追い掛け回しているわけではない。それでもノート PC の購入頻度は比較的高いし、その他のデジタル商品も、なんだかんだといろいろ買っている。
そうなると、玉突き式に代替される旧製品をどうするかが問題になるが、中古として売りに出す場合と、身内・友人・知人に譲渡する場合に分けられる。後者の場合、デジカメのようにそのまま渡せばすむものもあれば、PC のように OS の再セットアップを要するものもある。つまり、移管に伴って整理整頓、あるいは環境作り直しといった作業を伴うわけだ。
これは、自宅の引越、あるいは会社勤めをしていたときにしばしば遭遇した、オフィスの引越しや配置換えに伴う自席の引越、といったイベントにも通じるものがある。引っ越した先でスムーズに、それぞれのモノが収まるべき場所に収まってくれないと、いつになっても片付かない。特に会社の場合、仕事の能率にも響く。そこで、スムーズな移転ができるように、モノの配置や収容方法にいろいろと工夫を凝らしたものだ。
こうした工夫は、PC の代替、あるいは別の OS やアプリケーションへの乗り換え、といった事態でも必要になる。PC が何かをするための道具である以上、作業に付随して発生するデータなどの移行作業が必要になるが、往々にして、移行に際してデータの変換や整理といった作業を必要とする。
そこで今回は、データ変換ツールとして Excel を利用する用法についてみてみよう。過去 2 ヶ月にわたり、他のアプリケーションとのデータ交換について取り上げてきたが、これらはストレートに、データを右から左に渡せるものばかりだった。それに対して今月は、関数などの機能を駆使して、受け入れ側の事情に合わせてデータを加工する作業を伴っているところが違う。Windows から Macintosh に "switch" する方にとっても、こうしたノウハウは役に立つはずだ。
そうなると、玉突き式に代替される旧製品をどうするかが問題になるが、中古として売りに出す場合と、身内・友人・知人に譲渡する場合に分けられる。後者の場合、デジカメのようにそのまま渡せばすむものもあれば、PC のように OS の再セットアップを要するものもある。つまり、移管に伴って整理整頓、あるいは環境作り直しといった作業を伴うわけだ。
これは、自宅の引越、あるいは会社勤めをしていたときにしばしば遭遇した、オフィスの引越しや配置換えに伴う自席の引越、といったイベントにも通じるものがある。引っ越した先でスムーズに、それぞれのモノが収まるべき場所に収まってくれないと、いつになっても片付かない。特に会社の場合、仕事の能率にも響く。そこで、スムーズな移転ができるように、モノの配置や収容方法にいろいろと工夫を凝らしたものだ。
こうした工夫は、PC の代替、あるいは別の OS やアプリケーションへの乗り換え、といった事態でも必要になる。PC が何かをするための道具である以上、作業に付随して発生するデータなどの移行作業が必要になるが、往々にして、移行に際してデータの変換や整理といった作業を必要とする。
そこで今回は、データ変換ツールとして Excel を利用する用法についてみてみよう。過去 2 ヶ月にわたり、他のアプリケーションとのデータ交換について取り上げてきたが、これらはストレートに、データを右から左に渡せるものばかりだった。それに対して今月は、関数などの機能を駆使して、受け入れ側の事情に合わせてデータを加工する作業を伴っているところが違う。Windows から Macintosh に "switch" する方にとっても、こうしたノウハウは役に立つはずだ。
Outlook 2003のアドレス帳を Enrourage に取り込む
たとえば、Outlook 2003 を使っているユーザーが、Entourage 2004 への乗り換え (あるいは両者の併用) を行ないたい、と考えた場合、メール本文の移動もさることながら、アドレス帳をどうするかが問題になる。筆者自身もそうだが、アドレス帳に記録されているデータが多くなると、手作業で入力し直すのは不可能な相談だ。かといって、Entourage 2004 には Outlook 2003 の連絡先情報を直接取り込む手段は用意されていない。
そうなると、Outlook 2003 側で連絡先の情報をエクスポートして、それを Entourage 2004 側でインポートすることになる。幸い、Outlook 2003はタブ区切り、あるいはカンマ区切りのテキスト ファイルにエクスポートする機能が用意されているし、Entourage 2004 にもこれらの形式のファイルからインポートする機能がある。
ところが、Outlook の連絡先と Entourage のアドレス帳では、存在する項目が違うため、単に Outlook 側でエクスポートしたファイルを Entourage 側でインポートする、というだけでは話が済まない。実は、Entourage でテキスト ファイルからインポートする際には、どことどこの項目をインポートするかを指定できるのだが、ダイアログでゴチャゴチャ細工をするぐらいなら、最初から Entourage に合わせた完璧なデータを揃えておく方が、トラブルが少ない。
そこで Excel の登場となる。具体的な作業手順は以下のとおりだ。
(ここでは Outlook 2003 → Entourage 2004 という想定で例を示しているが、もちろん、逆方向のデータ移行も可能だ)
なお、カンマ区切りテキスト (CSV) 形式のファイルなら、そのまま Excel で無条件に開くことができるが、タブ区切りテキスト形式の場合、ファイルを開こうとするとテキスト ファイル ウィザードが起動する。そこで、ウィザード 1 画面目で [カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ] を選択して [完了] をクリックすればよい。
このように、データ交換時の編集ツールとして Excel を使用することには、以下のような利点がある。
ただし、「2.」で関数を使った変換処理を行った場合、そのままではテキスト形式で保存できないから、いったん別の空白シートで変換を行い、その結果をインポート用のシートにコピー & ペーストするのがよい。(同じセルで [コピー] と [形式を選択してペースト] を連続して行い、値だけをペーストする方法もある)
ちなみに、入力ミスで元データに複数のスペースが連続して入力されてしまっている場合には、＝TRIM() 関数を使うと余分なスペースを取り除いて、スペース 1 個だけに整理できる。また、＝CLEAN() 関数を使うと、何かの拍子にセルに入力されてしまったタブや改行などの制御コードを取り除くこともできる。覚えておくと便利だ。
なお、カンマ区切りでもタブ区切りでも、テキスト形式で保存する際には文字・数値以外の情報を保存できない点や、セル内改行が含まれているとデータが壊れる可能性がある点に注意する必要がある。
そうなると、Outlook 2003 側で連絡先の情報をエクスポートして、それを Entourage 2004 側でインポートすることになる。幸い、Outlook 2003はタブ区切り、あるいはカンマ区切りのテキスト ファイルにエクスポートする機能が用意されているし、Entourage 2004 にもこれらの形式のファイルからインポートする機能がある。
ところが、Outlook の連絡先と Entourage のアドレス帳では、存在する項目が違うため、単に Outlook 側でエクスポートしたファイルを Entourage 側でインポートする、というだけでは話が済まない。実は、Entourage でテキスト ファイルからインポートする際には、どことどこの項目をインポートするかを指定できるのだが、ダイアログでゴチャゴチャ細工をするぐらいなら、最初から Entourage に合わせた完璧なデータを揃えておく方が、トラブルが少ない。
 | 図 1 : Entourage 2004 で、テキスト ファイルからアドレス帳にインポートを行なっているところ。インポート元ファイルのどの項目を利用するか指定できるが、最初から Entourage の形式に合わせた内容のデータを用意する方が、間違いがない |
(ここでは Outlook 2003 → Entourage 2004 という想定で例を示しているが、もちろん、逆方向のデータ移行も可能だ)
- まず、双方の項目の違いを把握するため、Entourage 側でダミーのデータを 1 件、アドレス帳に入力しておく。自分の情報を使うのが、無駄にならなくてよいだろう。
- その情報をテキスト ファイルにエクスポートしておく。
- 次に、Outlook 側で、手持ちの連絡先の情報をエクスポートする。
- Excel を使って、Entourage でエクスポートしたテキスト ファイルと、Outlook でエクスポートしたテキスト ファイルを開く。それぞれ項目名や項目の並び順がまったく異なるが、どの項目同士が対応しているかは項目名を見れば理解できるから、Outlook でエクスポートしたテキスト ファイルから Entourage でエクスポートしたテキスト ファイルに、対応する項目の内容をそれぞれコピー & ペーストして、Entourage 用のデータを作成する。
- その上で、もし足りない情報があれば、それを手作業でセルに書き込んでおく。
- 最後に、できあがったデータを、テキスト形式のままで保存する (Excel ブックとして保存しない、という意味)。
- そのテキスト ファイルを Entourage でインポートする
なお、カンマ区切りテキスト (CSV) 形式のファイルなら、そのまま Excel で無条件に開くことができるが、タブ区切りテキスト形式の場合、ファイルを開こうとするとテキスト ファイル ウィザードが起動する。そこで、ウィザード 1 画面目で [カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ] を選択して [完了] をクリックすればよい。
 | 図 2 : A列は Outlook 2003 がエクスポートした連絡先、C 列は Entourage 2004 がエクスポートしたアドレス帳の、項目一覧を示したもの ([形式を選択してペースト]で、行列方向を入れ替えてある)。項目名や項目の並び順が両者で異なっているため、事前に移行先に合わせてデータを整形しておくとよい |
このように、データ交換時の編集ツールとして Excel を使用することには、以下のような利点がある。
- 表形式になっているので、データの入力や編集が楽。もちろん、テキスト形式のデータならテキスト エディタで直接編集することも可能だが、この方法は項目を間違えるリスクを伴う上に、行単位、あるいは列単位でまとめて情報を編集するのが難しい
- 文字列関数を使って、データの整合性を取ることができる。たとえば、ふりがなで使うカタカナや番地などで使う数字に、全角と半角が混在している事例は多いと思われるが、＝JIS() や ＝ASC() といった関数を使えば、どちらか一方に統一する作業が簡単に行える。
ただし、「2.」で関数を使った変換処理を行った場合、そのままではテキスト形式で保存できないから、いったん別の空白シートで変換を行い、その結果をインポート用のシートにコピー & ペーストするのがよい。(同じセルで [コピー] と [形式を選択してペースト] を連続して行い、値だけをペーストする方法もある)
ちなみに、入力ミスで元データに複数のスペースが連続して入力されてしまっている場合には、＝TRIM() 関数を使うと余分なスペースを取り除いて、スペース 1 個だけに整理できる。また、＝CLEAN() 関数を使うと、何かの拍子にセルに入力されてしまったタブや改行などの制御コードを取り除くこともできる。覚えておくと便利だ。
なお、カンマ区切りでもタブ区切りでも、テキスト形式で保存する際には文字・数値以外の情報を保存できない点や、セル内改行が含まれているとデータが壊れる可能性がある点に注意する必要がある。
関数を駆使して、住所のデータを項目ごとに分割してみよう
Outlook と Entourage はもともと同じ会社の製品だから、並び順や細かい内容に違いがあるものの、コピー & ペースト操作で項目の並び順を入れ替える程度の作業だけで、エクスポートとインポートによるデータの移行を行える。しかし、そんなに簡単に話が進まない場合も考えられる。
たとえば、Outlook や Entourage のアドレス帳では、住所の情報が「郵便番号」「都道府県」「市区町村」「番地」と分かれている。しかし、住所を表記するときに、
〒151-8533 東京都渋谷区笹塚 1-50-1 笹塚 NA ビルディング
というように、いちいち区別せずに全部がワンセットになっているケースが少なくない。もちろん、このデータを手作業でカット & ペーストする等の方法で分割することもできるが、非常に面倒な作業になるし、作業ミスが入り込む可能性も高くなる。また、都道府県名や市区町村名は長さがバラバラだから、[データ]-[区切り位置] で位置を指定して分割するわけにはいかない。何か別の解決策が必要だ。
そこで Excel の登場だ。元データがどういう形になっているか、状況を固定するのは難しいので、とりあえず、Excel のワークシートになっているという前提で話を進める。
たとえば、A1 セルに「〒151-8533 東京都渋谷区笹塚 1-50-1 笹塚 NA ビルディング」という住所情報が入力されていたとする。これを、Excel の文字列関数を使って、「郵便番号」「都道府県」「市区町村」「番地」に対応する B - D列に分割してみよう。使うのは文字列処理関数だ。
たとえば、Outlook や Entourage のアドレス帳では、住所の情報が「郵便番号」「都道府県」「市区町村」「番地」と分かれている。しかし、住所を表記するときに、
〒151-8533 東京都渋谷区笹塚 1-50-1 笹塚 NA ビルディング
というように、いちいち区別せずに全部がワンセットになっているケースが少なくない。もちろん、このデータを手作業でカット & ペーストする等の方法で分割することもできるが、非常に面倒な作業になるし、作業ミスが入り込む可能性も高くなる。また、都道府県名や市区町村名は長さがバラバラだから、[データ]-[区切り位置] で位置を指定して分割するわけにはいかない。何か別の解決策が必要だ。
そこで Excel の登場だ。元データがどういう形になっているか、状況を固定するのは難しいので、とりあえず、Excel のワークシートになっているという前提で話を進める。
たとえば、A1 セルに「〒151-8533 東京都渋谷区笹塚 1-50-1 笹塚 NA ビルディング」という住所情報が入力されていたとする。これを、Excel の文字列関数を使って、「郵便番号」「都道府県」「市区町村」「番地」に対応する B - D列に分割してみよう。使うのは文字列処理関数だ。
◎B 列 (郵便番号)
これは簡単だ。郵便番号は7桁に統一されているから、「〒」を取り除いて、残り8文字 (ハイフンを含むので1文字増える) を抜き出せばよい。つまり、2 文字目から 8 文字分を抜き出すということで、＝MID() 関数を使って
＝MID(A1,2,8)
とすればよい。
これは簡単だ。郵便番号は7桁に統一されているから、「〒」を取り除いて、残り8文字 (ハイフンを含むので1文字増える) を抜き出せばよい。つまり、2 文字目から 8 文字分を抜き出すということで、＝MID() 関数を使って
＝MID(A1,2,8)
とすればよい。
 図 3 : 郵便番号は文字数も桁位置も決まっているので、＝MID() 関数で位置を直接指定するだけで抜き出せる |
◎C 列 (都道府県)
都道府県は、「都」「道」「府」「県」のいずれかで終わる。だから、これらの文字が何文字目に位置するかを調べるのが基本になる。ただし、A1 セルの内容は郵便番号の情報を含んでいるから、それを除外しなければならない。
指定した文字が何文字目に位置するかを調べるには、＝FIND() / ＝FINDB() / ＝SEARCH() / ＝SEARCHB() と、4 種類の関数がある。関数名に「B」が付くのはバイト単位、付かないのは文字単位で結果を返す関数だが、日本語を扱うのにバイト単位は具合が悪いので、これらは除外する。＝FIND() と ＝SEARCH() の違いはアルファベットの大文字・小文字を区別するかどうかだが、日本語の場合はどちらでも関係ない。とりあえず ＝FIND() を使ってみよう。
たとえば、「都」が何文字目かを調べる場合、＝FIND("都",A1) という数式を使う。ただし、先に述べたように、これは先頭から数えた結果なので、郵便番号を含んでしまう。そこで、「〒」、ハイフンを含む郵便番号 (8 文字)、郵便番号と住所の境界のスペースの、合計 10 文字を差し引いて、11文字目から抜き出す必要がある。したがって、数式は以下のようになる。
＝MID(A1,11,FIND("都",A1)-10)
2 番目の引数で、開始位置を「11」としているのは、郵便番号がらみの先頭 10 文字を除外して、11 文字目から抜き出すためだ。一方、3 番目の引数で取り出す文字数を指定する部分では差し引く数が 1 だけ減っているが、これは ＝FIND() 関数の検索対象になっている「都」そのものを含めるための処理。これを忘れると「東京」しか取り出せなくなってしまう。
つまり、正確には「＝MID(A1,11,FIND("都",A1)＋1-11)」なのだが、数式を簡単にするために「＋1-11」を事前に計算してしまい、「-10」としている。
ここでは「都」を検索文字列に指定しているが、＝FIND() 関数で検索する文字列を「道」「府」「県」に変更すれば、他の地域についても対応できる。元データを最初に都道府県名順に並べて変えておくと、まとめて引数を書き換えることができるので、数式の入力や修正が楽になる。
なお、ひとつの数式で「都道府県」すべてに対応することも不可能ではないが、さまざまなケースを想定して条件分岐を盛り込んでいくと、とてつもなく複雑な数式ができてしまう。そんな数式を作るために手間をかけるぐらいなら、元データを都道府県名で並べ替えておいて、数式中の検索文字列を必要に応じて書き換える方が速い。「県」以外の名前を使っているのは、北海道・東京都・京都府・大阪府しかないのだから。
都道府県は、「都」「道」「府」「県」のいずれかで終わる。だから、これらの文字が何文字目に位置するかを調べるのが基本になる。ただし、A1 セルの内容は郵便番号の情報を含んでいるから、それを除外しなければならない。
指定した文字が何文字目に位置するかを調べるには、＝FIND() / ＝FINDB() / ＝SEARCH() / ＝SEARCHB() と、4 種類の関数がある。関数名に「B」が付くのはバイト単位、付かないのは文字単位で結果を返す関数だが、日本語を扱うのにバイト単位は具合が悪いので、これらは除外する。＝FIND() と ＝SEARCH() の違いはアルファベットの大文字・小文字を区別するかどうかだが、日本語の場合はどちらでも関係ない。とりあえず ＝FIND() を使ってみよう。
たとえば、「都」が何文字目かを調べる場合、＝FIND("都",A1) という数式を使う。ただし、先に述べたように、これは先頭から数えた結果なので、郵便番号を含んでしまう。そこで、「〒」、ハイフンを含む郵便番号 (8 文字)、郵便番号と住所の境界のスペースの、合計 10 文字を差し引いて、11文字目から抜き出す必要がある。したがって、数式は以下のようになる。
＝MID(A1,11,FIND("都",A1)-10)
2 番目の引数で、開始位置を「11」としているのは、郵便番号がらみの先頭 10 文字を除外して、11 文字目から抜き出すためだ。一方、3 番目の引数で取り出す文字数を指定する部分では差し引く数が 1 だけ減っているが、これは ＝FIND() 関数の検索対象になっている「都」そのものを含めるための処理。これを忘れると「東京」しか取り出せなくなってしまう。
つまり、正確には「＝MID(A1,11,FIND("都",A1)＋1-11)」なのだが、数式を簡単にするために「＋1-11」を事前に計算してしまい、「-10」としている。
 図 4 : 「都道府県」の情報を抜き出すには、「都」「道」「府」「県」の文字位置を調べて、抜き出す文字数を動的に変化させる必要がある |
 図 5 : Excel で、こんなものも作れてしまうという一例。「開始位置」を指定する場合、桁位置を示す数字を 1 だけ増やす必要がある点に注意 |
ここでは「都」を検索文字列に指定しているが、＝FIND() 関数で検索する文字列を「道」「府」「県」に変更すれば、他の地域についても対応できる。元データを最初に都道府県名順に並べて変えておくと、まとめて引数を書き換えることができるので、数式の入力や修正が楽になる。
なお、ひとつの数式で「都道府県」すべてに対応することも不可能ではないが、さまざまなケースを想定して条件分岐を盛り込んでいくと、とてつもなく複雑な数式ができてしまう。そんな数式を作るために手間をかけるぐらいなら、元データを都道府県名で並べ替えておいて、数式中の検索文字列を必要に応じて書き換える方が速い。「県」以外の名前を使っているのは、北海道・東京都・京都府・大阪府しかないのだから。
◎D 列 (市区町村)
基本的な考え方としては、これも「市」「区」「町」「村」の文字位置を調べて、都道府県と同じ容量で数式を作ればいいのではないかと思われるので、まずはそれでやってみよう。
＝MID(A1,11＋LEN(C1),FIND("区",A1)-(10＋LEN(C1)))
これも ＝MID() 関数を使っている。最初の引数は、C 列と同様に A1 セルを指定している。
2 番目の引数は開始位置だが、これは 11 (「〒」、ハイフンを含む郵便番号 8 文字、その後のスペースを足した合計文字数に、さらに 1 を加えて区名の先頭文字に位置を合わせている) に、C 列に入力されている都道府県名の文字数を加えたものだ。たいていの都道府県名は 3 文字だが、「鹿児島県」のように字数が異なるものがあるので、数値を直接指定せずに、＝LEN() 関数で文字数を数えている。
3 番目の引数は抜き出す文字数だが、まず ＝FIND() 関数で「区」の位置を調べている。ただし、これが返す値は A1 セルに入力されている文字列の先頭から数えた値だから、「〒…都」までの文字数を差し引く必要がある。それが「10＋LEN(C1)」の部分で、区名の先頭文字ではなく、その左側までを取り除くため、2 番目の引数より数字が 1 だけ小さい。
これで、「区」を「市」「町」「村」に変えれば全国どこでも対応できそうだが、実は、そう簡単にはいかない。この項目が最難関なのだ。
都道府県名の場合、名前そのものに「都」「道」「府」「県」を含んでいるケースがないから、関数で使用する検索文字列を変えるだけで機械的に処理できるが、市区町村の場合、名前そのものに「市」「区」「町」「村」といった文字が含まれているケースがある。たとえば、市川市 (千葉県)、村山市 (山形県)、といったものだ。
考え方としては、たとえば「市」の文字位置を調べた時点で、その右側にさらに「市」という文字が含まれていないかどうかを調べて、含まれている場合はそちらを使うようにすればよい。しかし、数式が複雑になってしまう難点もあり、手間の割には得られるものが少なくなりかねない。
データの量にもよるが、関数で分割した後のデータを調べて、不具合がでているところだけ手作業で修正する、というのがもっとも現実的かもしれない。どのみち、分割後のデータが正しい内容になっているかどうか確認しなければならないし、全部のデータを手作業で分割するのに比べれば、それでもまだ楽なのだから。
基本的な考え方としては、これも「市」「区」「町」「村」の文字位置を調べて、都道府県と同じ容量で数式を作ればいいのではないかと思われるので、まずはそれでやってみよう。
＝MID(A1,11＋LEN(C1),FIND("区",A1)-(10＋LEN(C1)))
これも ＝MID() 関数を使っている。最初の引数は、C 列と同様に A1 セルを指定している。
2 番目の引数は開始位置だが、これは 11 (「〒」、ハイフンを含む郵便番号 8 文字、その後のスペースを足した合計文字数に、さらに 1 を加えて区名の先頭文字に位置を合わせている) に、C 列に入力されている都道府県名の文字数を加えたものだ。たいていの都道府県名は 3 文字だが、「鹿児島県」のように字数が異なるものがあるので、数値を直接指定せずに、＝LEN() 関数で文字数を数えている。
3 番目の引数は抜き出す文字数だが、まず ＝FIND() 関数で「区」の位置を調べている。ただし、これが返す値は A1 セルに入力されている文字列の先頭から数えた値だから、「〒…都」までの文字数を差し引く必要がある。それが「10＋LEN(C1)」の部分で、区名の先頭文字ではなく、その左側までを取り除くため、2 番目の引数より数字が 1 だけ小さい。
 図 6 : 「市区町村」でも「都道府県」と同じ考え方だが、C 列にある都道府県名の文字数を調べておかないと開始位置が正確にならないので、その分だけ数式が複雑になる |
これで、「区」を「市」「町」「村」に変えれば全国どこでも対応できそうだが、実は、そう簡単にはいかない。この項目が最難関なのだ。
都道府県名の場合、名前そのものに「都」「道」「府」「県」を含んでいるケースがないから、関数で使用する検索文字列を変えるだけで機械的に処理できるが、市区町村の場合、名前そのものに「市」「区」「町」「村」といった文字が含まれているケースがある。たとえば、市川市 (千葉県)、村山市 (山形県)、といったものだ。
考え方としては、たとえば「市」の文字位置を調べた時点で、その右側にさらに「市」という文字が含まれていないかどうかを調べて、含まれている場合はそちらを使うようにすればよい。しかし、数式が複雑になってしまう難点もあり、手間の割には得られるものが少なくなりかねない。
データの量にもよるが、関数で分割した後のデータを調べて、不具合がでているところだけ手作業で修正する、というのがもっとも現実的かもしれない。どのみち、分割後のデータが正しい内容になっているかどうか確認しなければならないし、全部のデータを手作業で分割するのに比べれば、それでもまだ楽なのだから。
◎E 列 (番地)
これは、「郵便番号」「都道府県」「市区町村」を差し引いた残りだから、話は簡単だ。
A1 セルの内容が「〒」＋「郵便番号」＋「半角スペース」＋「都道府県」＋「市区町村」＋「番地」という構成だという前提で考えると、A1 セルの内容から「〒」＋「郵便番号」＋「半角スペース」＋「都道府県」＋「市区町村」を取り除けばよい。
つまり、文字列の置き換えだから、＝SUBSTITUTE() 関数を使うことになる。置き換え対象になる文字列は複数の文字列の集合体だが、数式中で文字列を連結するには「&」で結べばよいので、数式は以下のようになる。
＝SUBSTITUTE(A1,"〒"&B1&""&C1&D1,"")
ポイントは 2 番目の引数で、「〒」「郵便番号」「半角スペース」「都道府県」＋「市区町村」を、順番に「&」で結んでいる。ただし、「〒」と「半角スペース」は固定文字列だから、""で囲んでそのまま記述する。A1 セルの内容からこれらを連結した結果を取り除くわけだから、3 番目の引数で指定している置換文字列は空白、つまり引用符だけを記述して "" とすればよい。すると、残った番地の情報だけが返される。
これは、「郵便番号」「都道府県」「市区町村」を差し引いた残りだから、話は簡単だ。
A1 セルの内容が「〒」＋「郵便番号」＋「半角スペース」＋「都道府県」＋「市区町村」＋「番地」という構成だという前提で考えると、A1 セルの内容から「〒」＋「郵便番号」＋「半角スペース」＋「都道府県」＋「市区町村」を取り除けばよい。
つまり、文字列の置き換えだから、＝SUBSTITUTE() 関数を使うことになる。置き換え対象になる文字列は複数の文字列の集合体だが、数式中で文字列を連結するには「&」で結べばよいので、数式は以下のようになる。
＝SUBSTITUTE(A1,"〒"&B1&""&C1&D1,"")
ポイントは 2 番目の引数で、「〒」「郵便番号」「半角スペース」「都道府県」＋「市区町村」を、順番に「&」で結んでいる。ただし、「〒」と「半角スペース」は固定文字列だから、""で囲んでそのまま記述する。A1 セルの内容からこれらを連結した結果を取り除くわけだから、3 番目の引数で指定している置換文字列は空白、つまり引用符だけを記述して "" とすればよい。すると、残った番地の情報だけが返される。
 図 7 : 「番地」は、これまでに抜き出した情報の残りだから、すでに左側の列で抜き出した情報を取り去った残りを返すような数式を作ればよい。ここでは ＝SUBSTITUTE() 関数を使い、すでに抜き出した文字列を連結した結果を空白文字列と置き換えているが、別の方法も考えられるだろう |
結論
このように、文字列処理関数や文字列演算の機能を利用すると、Excel をデータの前処理用ツール、あるいは一種のテキスト処理ツールとして利用することができる。
たとえば、データベースに入力したデータが正確に機能するには、そこに登録する内容の表記ルールに統一性を持たせる方が好ましい。ときには、データベースの項目名一覧に合わないような内容のデータを扱わなければならないケースも考えられる。そこで、Excel を前処理ツールとして利用することで、問題を解決できるケースは少なくないだろう。
Excel が装備している 200 以上の関数のうち、文字列関数は、どちらかというと出番が少ない部類に入る。しかし、使い方次第で、案外といろいろなことができるのだ。その結果はテキスト ファイルに保存してもよいし、直接、他のアプリケーションにコピー & ペーストしてもよい。
このように、文字列処理関数や文字列演算の機能を利用すると、Excel をデータの前処理用ツール、あるいは一種のテキスト処理ツールとして利用することができる。
たとえば、データベースに入力したデータが正確に機能するには、そこに登録する内容の表記ルールに統一性を持たせる方が好ましい。ときには、データベースの項目名一覧に合わないような内容のデータを扱わなければならないケースも考えられる。そこで、Excel を前処理ツールとして利用することで、問題を解決できるケースは少なくないだろう。
Excel が装備している 200 以上の関数のうち、文字列関数は、どちらかというと出番が少ない部類に入る。しかし、使い方次第で、案外といろいろなことができるのだ。その結果はテキスト ファイルに保存してもよいし、直接、他のアプリケーションにコピー & ペーストしてもよい。
