Excel ひとつの列の分割テクニック

同僚からちょっと面白いExcelネタを振られたので、そういうときの一番簡なワザを伝授しました。

その同僚の問いはこんな感じ。

• A列に「XXXX-XX_999」というフォーマットで、CSVファイル名の一覧が入っている。
• アンダースコアの後についている「999」はCSVの件数で、この「999」をゲットしたい。
• 但し「XXXX-XX」にあたる部分の桁数は不揃いだし、「999」の方も、1桁だったり、4桁だったり桁は揃っていない。

同僚は「Right関数とかを使うのかな～？」というところまでは想像したものの、桁が揃ってないのでうまくいかず、関数を調べればどうにかなりそうだと見当はついたものの、調べているヒマもなく、アワアワしていたという話。

私はExcelには、大雑把に言って、オペレーター・テイストな小技と、プログラマー的な小技の2種類があると思うのですが、まずは超ラクチンなオペレーター技から。

1. 問題の列を選択。
2. [データ]→[区切り位置]
3. 「カンマやタブの～」のオプションのまま［次へ］
4. [区切り位置]のオプションで[その他]のヨコにあるテキストボックスへ、今回の区切り文字となる「_」を入力→[次へ]→[完了]（まー、「列のデータの書式」を設定したければ、この辺でどうぞ）。

以上、Excel単体でこのテの問題は、サクッと完了できてしまうわけですね。文字列1文字を区切り文字にできてしまう「その他」オプションは意外と便利なので、試す価値アリです(「カンマやタブ～」のところまではきても、「その他」を使ったことのない人は意外と多い)。

ちなみにうちの新人に「Excelならではの機能を使ってもいいし、それよりはちょっと手間でも関数の組合せでも処理できたりするけど、どーゆー手段があると思う？」と気晴らしにネタを振ってみたところ(現在新人はAccess猛特訓中…)、列をエディタへコピーして、「_」を「,」へ置換。テキストファイルを「.txt」で一旦保存。それから「Excelでのcsv ファイルの取り扱い方」で扱った「ファイルを開く」から始めて、上の手順3に合流した流れへ入っていきました。ちゃんと教えたことを流用していて、なかなか良い解答を出してきました。

他に、SQLで頼まれようが、手続き型言語で処理するときだろうが、思考方法としては同じだから、その後につながる基礎体力になるだろうということで、新人へは関数技もあわせて伝授。一応、新人にはExcelにおける一通り文字列関数は教えたし、簡単な関数のコワザも教えたものの、まだまだそれらを組み合わせたコワザはでてこないようなので(調べてわかればオーケーだと思うので、基本はググって調べるなり、閃くなり、自力で問題解決できればオーケーという感じでやっているのですが)、

=VALUE(RIGHT(A1,LEN(A1)-FIND("_",A1)))

とかをやってみせる。

FIND 関数は指定された文字列(検索文字列)を他の文字列(対象)の中で検索し、その文字列が他の文字列内で最初に現れる位置を左端から数え、その番号を返す関数ですね。今回の場合SEARCH関数でも良いですが…。あまり細々したことを一方的に教えても仕方がないので、その辺の応用・知識の広げ方・好みは本人に任せるとして…。「先日教えてもらった関数はこのように使うのか」というカオをしていたので、大丈夫でしょう。

ちなみにこのテの列の分割ネタでよく頼まれ、しかもわりと面倒なネタとしては、住所の分割で、「都道府県とそれに続く住所を分けたい」とか、「市区町村と～以下同文」あたりなんですけどね。

住所を印字する場合、住所ラベルにしろハガキにしろ、印字面積の横幅は決まっているわけで、長い文字列の住所を扱う場合、ある程度の単位で改行する必要がでてきます。ところが、「なーんにも考えないで住所を収集しました！」という人々のデータというのは、最終的に印刷するときのMAX文字数を考えず(横幅とフォントサイズ次第で、つみこめる文字数は違ってきますが、それにしたって限界というものはあります)、1フィールドだったりするわけです。文字数でばっつり改行を入れても、郵便物は届くようではありますが、番地の途中等で改行されるのは見苦しいので、なるべく住所の文脈で改行したいではないですか。

「こんにゃろー、最初から分割していればくっつけるのは簡単なのに、文脈で分割するのは何倍も大変なんだよー。最終的に何の為にデータを集めるのか、少しは考えて回収しろよー」とブツクサ思いつつ、頭をしぼるわけですね。入力の段階で考えておけば手間いらずなのに、こっちになんの相談もせず、勝手に営業なりド素人が集めた回収データは、なにかとファジーで、データとして汚いものが多いわけです。

ちなみに住所分割のコワザは(特に都道府県分割ネタ)、ググると結構小技が出てくるので、わりとメジャーなネタなのではないかと思いマス。なので省略。