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フィールドだったりするわけです。文字数でばっつり改行を入れても、郵便物は届くようではありますが、番地の途中等で改行されるのは見苦しいので、なるべく住所の文脈で改行したいではないですか。

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

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

買ったわー

GWはGeekWeek…。本日、マシンを買ってきました。ひさしぶのマシンのお買い物ですから、ついつい店員さんに根掘り葉掘り、部品ひとつひとつについて、訊いてしまいました。

見れば欲しくなるので普段は見ないようにしている…、というのもあって、ハードはマッタク詳しくないんですよね。ぽんぽん新しいCPUやらが出てくるし、グラフィックス系の部分もよく知らないし、Vistaもいじっていませんし…。ここ数年流行している光沢液晶も、いまだに使ったことがありませんし。

「完全に時代遅れだなー」という自覚があるので、ここぞとばかりにハテナ？と思っていたことはがんがん質問。

「ハードをいじるのことが、この人、かなり好きなんだろうな」という感じのハードへのラブが伝わってくる人で(「ナカを開けるとこのコはキレイなんですよー」と思わず嬉しそうに云う様は、まるで「内臓がキレイですねー」と嬉しそうにいう外科医のよう)、気兼ねなく質問できてヨカッタです。で、このオタな感じの店員さんは、「見た目より実をとれ」的な解説をする人で（さすがにあれだけ質問すれば、私の価値観を見極めますわね）、なんだかんだでディスプレイもノングレア液晶をプッシュされ、ノングレア液晶に…。

「光沢なんてただの飾りです！えらいヒトにはそれがわからんのですよ。映り込むのに！」

…みたいな？

あのツルツルした華やかさにふらふらと気持ちは傾いていたのですが、職場の蛍光灯が明るすぎたりして、常にディスプレイの「反射」には悩まされている日常があるので、「映り込み」の話を聞いて光沢はアッサリ断念。

省スペースのデスクトップが欲しかったので(≠ノートで。この数年ずーーーーーっと家でも職場でもノートだったので、なんというか飽きました)、まー満足です。OSはXP(今回は一応、Vistaのダウングレードで購入。XPはProfessionalなのでIISも使えるわー←所詮なにかとMSっ子)、CPUはCore2Duoとかがいいなーと思っていて(要はCPUが2つ以上ってところに拘っていた)、省スペース、の3つが必須条件。となると、殆ど選択肢がなかったところはありますが…。

お届けは29日の午前中デス。たのしみーー。

え、機種は？…秘密デス。

Excel 汚いデータをキレイにするにあたって便利な関数

データをクリーニングするにあたって便利な関数は、大体においてこんなものでしょう。

• RIGHT関数/LEFT関数/MID関数…右から、左から、何個目から何個まで文字列を取得
• TEXT関数…数値を書式変換した文字列にする
• LEN関数…文字数
• TRIM関数…左右の空白除去
• VALUE関数…文字型のものを数字型に変換
• JIS関数/ASC関数…全角・半角変換
• UPPER関数/LOWER関数…英数の大文字・小文字変換

Replace関数(特定文字を置換する)も便利な関数ですが、Excelの場合だと、置換機能があるので、ちょっとした半手作業でデータを処理する場合、私はあまり使いません。

オーソドックスな利用シチュエーションは以下の通り。とりあえず、ここでは機能と使い方のヒントを列記していきます。関数の詳細については、Excelのヘルプを見てください。

LEFT関数を使って0つき数字の生成

RIGHT関数はそもそも右から何文字目までをゲットしてくる、という典型的な文字列操作関数。「0001」「0002」など0をつけて数字の桁を揃えたいとき、非常によく使います。
ex)A1に「123」という値が入っていて、B1のセルに「00123」という5桁のゼロつき数字にして表示したいとき
　B1のセルに「=RIGHT("00000" & A1,5)」と書く
　※()カッコの中の文字列を引数と云います。「引数」というコトバは、頻出単語なので、関数の中に入れる文字列のコトなんだ、ととりあえず覚えておきましょう。この例の場合だと「"00000" & A1」と「5」のことを引数と云います。
　たとえばこのままドラッグすれば、A列の数字が全部5桁の0つき数字に揃います。

TEXT関数を使って数値の書式を揃える

TEXT関数を使って、数値の書式を変換することも可能です。上記の例で0つき数字5桁に揃えるには、
「=TEXT(A1, "00000")」
とかですね。TEXT関数でカンマつきの数値変換、及び日付変換などもできます。VBで云うところのFormat関数のようなものです。

LEFT関数で固定長の文字列の生成

RIGHT関数とは逆に左から何文字目までをゲットしてくる、というタイプの関数。固定長の文字列を作成したいときなどによく使いますね。固定長というのは、全角の6文字なら6文字で、文字と全角スペースで六文字を埋めてしまうこと。PCだと可変のCSVでのやりとりなどが多いのでこのワザの出番はあまりありませんが、ホスト相手にやりとりするデータだと、固定長にすることが多いので、基本はコレです。
ex)A1に「まつもとひろゆき」とかいてあって、B1のセルに「まつもとひろゆき□□□□□□□□□□□□」と表示したいとき。
　B1に「=LEFT(A1 &REPT("□",20),20)」と書けば、
「まつもとひろゆき□□□□□□□□□□□□」
という結果に。REPT関数は今回の場合、□を20コ繰り返すという意味。□を全角スペースと見立てると、固定長になります。

MID関数

左から何個目から、何個までをゲットしてくる関数。ちなみに左から「何個目から…」というところを1に設定すると、LEFT関数と同一の意味で使えます(一番左は1とカウントします)。上記の「まつもとひろゆき」の例の場合、「=MID(A1 & REPT("□",20),1,20」となります。
ちょっとした固定長のデータの検証するときなどにも多用していますねー。このテの仕様書は大抵Excelでいただいているので、フィールド名と文字数などが表になっているわけです。開始文字列の数をExcel上でちゃちゃと計算して(区切り文字があればそれも考慮)、仕様書の文字列数をそのままセットすれば、MID関数で固定長のデータを拾ってこれるわけですよね。私の場合、開始文字列と、拾ってくる文字列数を、仕様書のフォーマットにあわせて縦に向かって計算して、コピー&ペースト術で、それらのタテ型の列をヨコに並べる。で、このフィールドごとの開始文字列のポジションの計算結果と文字数を利用して、MID関数を利用することが多いです。仕様書をそのまま利用できるのでなかなかこのテの組合せは、ちょっとしたときにベンリで重宝しています。

LEN関数

文字数をカウントして返してくるカウント。Replace関数やMID関数、FIND関数などと組み合わせて使うシーンが多いですねー。

TRIM関数

引数の文字列の左右の空白を除去してくれます。「□あいうえお□□」という文字列がセルA1に入っていたとしたら、「=TRIM(A1)」で「あいうえお」を返してきます。空白が入っていれば、文字列の文字コードの組み合わせは変わってきてしまい、「見た目」は一緒なのに、データは異なるデータとして認識されますので、なにかと不都合がおこります。ですからなにかとデータにはTRIM関数を一旦かませてから、比較したり、加工したりすることなどが多いです。

VALUE関数

セルの書式設定が「文字列」のまま、見た目「数字」のデータを計算しようとしても、データ型は文字列として扱われ、四則の計算などの処理は一切出来ません。ですから、数字としてデータを扱いたい場合、一旦「VALUE」関数で、数値型になおしてから、計算などを行います(詳しくは「Excel文字列を数値変換する」を参照)。

JIS関数/ASC関数

JIS関数…半角文字を全角文字に変換する関数
ASC関数…全角文字を半角文字に変換する関数
というわけで、引数に指定された文字列を変換してくれますヨ。いわゆる「全角/半角」を知らないも入力するのがExcelワールドなので、集計作業等をするにあたってデータをクリーニングしたいとき、このテの関数はかなり重宝します。

UPPER関数/LOWER関数

UPPER関数…英文字をすべて大文字に変換する関数
LOWER関数…英文字をすべて小文字に変換する
このあたりも上記同様、押さえておきたい関数ですね。

以上、大抵の言語には、このテの役割を果す関数は、名前は違えど揃っているのではないでしょうか。プログラマーの方はこのテのコワザの思考方法は一通り抑えているのではないかと思います。

Excel的には、これらの関数と「Excelコピー&ペースト術」を組み合わせで、一発限りのちょっとしたデータをクリーニングのときなどは、大方半手作業で処理できてしまうわけです。

理想は勿論このテの加工をせずにすむよう、汚いデータを作成しないことです。その為には､事前にきちんとなんの為にデータを収集しているのだからどんなモノが必要なのか計画を練り入力時のがっつりしたエラーチェックなどは必須事項です。後から修正する方が当然大変ですから…。

しかし現実にはキタナいデータを突然送り付けられたり、特に単発のものであれば、多少の整形処理をしなくてはならないシーンは多いわけです。そんなとき、これらの関数を知っていれば、グンと早く、より確実な仕上がりのキレイなデータにすることができます。手作業部分が増えれば、それだけヒューマンエラーの出る可能性も大きくなるので、なるべく手作業をやらずにすむ部分はやらないというポリシーで、バッチ的に処理する工夫をして、ラクをしましょう。

Excel文字列を数値変換する～見た目は数字なのに計算できない！とお困りの方へ

セルには明らかに数字が入っているのに、何故かSum関数やら引き算やら、数値計算ができない！──というシチュエーションの原因は、大抵、セルの書式が文字列になっていることによります。この場合、単に、セルの書式設定を「文字列」から「標準」などに変更するだけでは、計算結果を表示してくれません。何故なら文字列の書式 設定で入力されてしまった数字は、単にセルの書式設定を「標準(或いは数値など)」に戻すだけでは、見た目は数字でもやっぱりExcelには「文字列」として認識されてしまうからです。

よってここでは、「文字列」認識されてしまっているセルの値を「数値」として認識するよう、細工する必要がでてきます。以下が私がよく使っている文字列→数値変換の手順です。

1. VALUE関数を利用して、あいているスペースに、セルの書式が文字列になってしまっているセルを引数にして、だだーーっとドラッッグしてコピー(B1のセルが文字列だとしたら、[=VALUE(B1)]とかです)。
2. 貼り付け予定先のエリアの書式を[標準]にしておく([文字列]ではなく！[文字列]のセルに貼り付けても、再び文字列になってしまいます)。
3. VALUE関数で表示させたセルをコピー→形式を選択して貼り付け→[値]オプションをチェック！→任意のセル(貼り付け先のセルの書式は[標準]だとか、ちゃんとしたものを選んでください)を選択して、貼り付け！

Excelでのcsvファイルの取り扱い方でやったように、0落ちをおそれるばかりに、シートを全選択して、全てのセルの書式を文字列にしたときなどに、「見た目は数字なのに、Sum関数さえ使えない！」という自体によく陥るわけですね。それはセルの書式が文字列だから計算できないわけで(見た目は数字だけど、あくまでも「文字列」として扱っているわけですなー。Excelはそのセルを数字だとは思っていないので、計算などはできないわけです…、とデータ型の意識の希薄な人々の場合にはちと説明しづらいのですが、そーゆーもんだと思ってください)、計算したい列などに対しては、文字列から数値変換する必要があるんですね。そういう場面で、このテの変換ワザをよく使っていマス。

Excel上で文字列から数字変換するコワザは他にも色々あって、Value関数を使うかわりに、空いているセルに「=文字列のセル*1」という乗算を行なって、乗算を入れたセルの書式を「標準」選択。[形式を選択して貼り付け]→[値]オプションという流れに入るやり方も、王道の一つとしてあります。

さらにExcel2002あたりのバージョンからは、文字列のセルに数字のみを入れていると、左上隅に緑色のエラーインジケータが表示されているセルが現れ、そのインジゲータを選ぶと「！」が表示。「！」をクリックすると、[数値に変換する]というメニューが表示されるので、これを選ぶと、数字に変換される親切設計になっています。エラーインジゲータが一定範囲で連続して出ている列なんかだと、まとめて列のかたまり毎選択して、いっぺんに数値に変換は可能です。ですが、まー、一定の範囲をまとめて数値変換したいときには、イマヒトツ一発の動作で決まりづらいので、個人的には、関数やコピー業に慣れている人は、Value関数などの計算式を入れる方法が、途中、標準のセルが混ざっていようが気にせず確実に数値変換できるので、ベンリと感じるのではないかなー、という気もします。その辺は、その人の好みによりますが…。

他には、空いているセルの書式を[標準]にしてから「1」を入力。そのセルをコピー。文字列の書式になっている数値型にしたいセルの範囲を選択したのち[形式を選択して貼り付け]→[乗算]を選択→[OK]といったやり方。

[データ]→[区切り位置]→[カンマやタブの…]を選択→[次へ]→区切り文字に[タブ]を選んだまま→[次へ]→[列のデータ形式]で[標準/G]を選んだまま[完了]というやり方もあります。しかしこのやり方では、セルの内容的には数値になっているものの(例えば0つき数字であれば、0が落ちている=数値変換はなされているということ)、セルの書式は文字列のままになってしまうので、最終的には数値にしたかったセルの書式を[標準]に設定しなおした方が、キレイに決まると思います。

ちなみにプログラムでExcelにデータを吐き出すとき、シートを全選択してセルの書式は一旦文字列にしてからデータを吐き出す…、という流れで書く人は意外と多いので、納品されたExcel のセルの書式設定が[文字列]になっていて、その後、「数字として加工ができん！」というような場面は、よくありマス。まー、Excelをプログラムで吐き出す立場からすれば、日付だろうと、0つき数字だろうと、書式を文字列にしてしまえば「見た目」は確実に確保されるわけですから、指定された「見た目どおり」という意味では手軽な手段なんですよね。

というわけで「この納品されてきた Excel表にちょっと手を加えたいのに、どーして計算できないの？！」と大騒ぎしている人々の尻拭い/相談されることは、Excelラブを公言している立場上、好むと好まざるとにかかわらず意外と多いわけです。あんまり変換する表の数が多いときは、責任もって確実に全ての表を、単に0つき数字のコードとして使っているのか数値計算用する必要のあるフィールドなのかをイチイチ見極めて、変換するのは私にとっても苦痛なので(そもそも自分の担当案件ではございませんので…)、発注先に再納品を要求するよう薦めたり、数値変換のやり方を教えて、「あとは自力で頑張ってね」と申し渡したりするわけですが(二度とこんなザルな発注をしてくれるなよ…、てなところです)。

そんなこんなで、Excel納品を依頼する場合には、一応、この辺のセルの書式についても、発注先には予め注文をつけていた方が無難 だと思います。その一方で、このテのExcelを納品をしてくるプログラマ側には、素人が発注するとき、この辺のことは当然気づかないで発注しているんだから、プロならその辺まで察してフォローしてやれよ、とも思うわけですが…。発注元の「仕様」に入っていなかった云々というより、そもそもExcel納品を依頼されているときに、Excelとして死んでいる表を納品してどーする、と思うわけですね…。

Excelコピー&ペースト術 ～ 行列を入れ替える他

Excelのコピー術、形式を選択して貼り付けから、整形する際によく使う、以下2点についての紹介(ドラッグとかについては、常識すぎるので他を当たってください)。

▲クリックすると画像拡大。
コピーしたエリアの左上のセルを選択して、右クリック→[形式を選択して貼り付け]を呼び出せば、式から値へとそのまま入れ替わる感じになりますね。

• 値
• 行列を入れ替える

まず、形式を選択して貼り付けの呼び出し方は共通ですので、説明を。

1. コピー範囲を選択
2. 一旦コピー
3. 貼り付け先のセルを選択
4. 右クリックで[形式を選択して貼り付け]を選択

以上の動作を行うとずらずらっと、オプションボタンが出てきます。オプションボタンを選択して、[OK]をクリックすれば、便利なコピー＆ペーストは完了です。

[値]オプションを選択

これはセルに関数などの式が入っていて、計算結果を固定してしまいたいとき、このオプションを利用すれば、計算式から見たままの値へと固定されます。式は便利ですが、コピーなどしていくうちに相対的に動いたりもして、危ない側面もありますから、値が不動だと決定したならば、固定してしまいたいときがありますよね。その際にご利用ください。

行列を入れ替える

コトバ通り。このチェックボックスを入れると、行と列が入れ替わりますね。たまーに行と列を入れ替えたい場面というのは出てくるかと思いますが、これであっさり解決です。

わりとこのチェックボックスを知らない方は多いみたいですねー。よく訊かれます。

Excelでのcsvファイルの取り扱い方～0落ち/日付変換対策

データのやりとりはcsv形式(カンマ区切り)、という方が多いと思います。csvファイルをダブルクリックすると、Excelと関連づけられていて、Excelが起動するマシンがほとんどですよね。ここにワナがあります。csvファイルをExcelで開くと、データが壊れる可能性がある、ということを皆様ご存知でしょうか？

壊れる可能性のあるデータの代表格は、

• ゼロつき数字のセル(「00001」など、ゼロからはじまるもの。Excelで開くと、数字型として認識されて0が消えてしまう)
• 「2-2-3」など、数字とハイフォンやスラッシュのみで構成されているセル(「住所の番地のみ」のセルなどで、数字とハイフォンだけ、などだと、日付形式として認識され、番地が日付に解釈されてしまう)
• カッコで囲まれた数字のみのセルはマイナスになってしまう（「(5000)」は「-5000」に）。

値をみてExcelが勝手にデータを、数字型だの日付型だのに解釈してしまって、データが壊れてしまうわけなのです。もらったそのままのデータの中身をみるだけなら、セルの表示書式は「文字列」でなければならないのです(ただし、文字列の書式だと、計算などはできなくなります。あくまでも「文字列」で数字ではないので)。

Excelでデータを確認して、「上書き保存」などされれば、完全にデータは壊れてしまうわけです（0落ちはともかく、日付変換などは致命的）。このテの事故は大変に多いですね。以下、csvファイルに対するお作法。

大前提としては、拡張子を変更しなければならないので、拡張子を表示しているマシンってトコですね。拡張子ってナニ？！という方はまずは拡張子の表示、隠しファイルの表示で、マシンを拡張子表示状態にしてください。Macは…。わかりません。

事故防止対策：Excelとcsvファイルの関連付けを外す(Winでのやり方)

Excelをインストールすると、Excelとcsvファイルが関連付けされるので、まずはこれを解除します。

1. エクスプローラを起動。
2. [ツール]→[フォルダ　オプション]→[ファイルの種類]→[登録されているファイルの種類]の中から拡張子[CSV]を選択。
3. 下段[拡張子'CSV'の詳細]→[変更]→[プログラム]の中から、利用しているエディタを探す、もしくは[参照]。→[OK]

以上で、CSVとエディタへの関連付けに変更されます。ハッキリいってデフォルト設定でCSVとExcelを関連付けていても、経験上、役に立つことはほとんどないと思いますので(紛らわしくて事故の元)、外した方が賢明だと思います。

具体的なExcelでのcsvファイルの取り扱い方

1. [*.csv]というファイル名を[*.txt]ヘ変更。
2. Excelを起動。
3. [ファイル]→[開く]→csvファイルを選択。→[開く]
4. [カンマやタブなどの区切り文字によって…]にチェック→[次へ]
5. [区切り文字]で[カンマ]にチェック→[次へ]
6. [データのプレビュー]で、文字列型のフィールドを選択して[列の選択]で[文字列]にチェック(全フィールドの選択も可。私は大抵、データを壊したくないので、とりあえず全フィールドを文字列にして、開いて中身を確認する、というパターンが多いです)→[完了]

ちなみに、ファイル名をtxt名に変更しないと、セルの書式を選択するウィザートが起動しません。

以上がオーソドックスな開き方。

他に、似たような手順としては、[データ]→[外部データの取り込み]→[テキストファイルのインポート]で拡張子を選ぶ[ファイルの種類]のボックスで［すべてのファイル(*.*)］を選択して、任意のCSVを選ぶという方法（その後は[カンマやタブなどの区切り文字によって…]という上記の手順と一緒）もあります。

CSVファイルをエディタで開いてコピー、Excelに貼り付けて、[データ]→[区切り位置]を選択して、[カンマやタブなどの区切り文字によって…]以降の手順に入る方法もあります。

少し気の利いたエスケープシーケンスを利用できるエディタ*1をお持ちの方でしたら、カンマをタブ区切りに変換。Excelのセルの書式を文字列にしておく、エディタのタブ区切りデータをコピーしてExcelに貼り付け、という方法などもOKです。

そんなこんなで、csvファイルよりタブ区切りのデータの方が、好ましいと私などは思っていますが、世の中はcsvファイルが主流ですからね。仕方ありません。

データに0つき数字などのあるcsvファイルで、スタートの私たちから仲介役を渡ってエンドまでの距離が長い場合や、事前に取り扱い方の注意をいくらうながしても、「この人、どーもわかってないな…」と危険を感じるとき、わざと拡張子をtxtにしてファイルを受け渡すこともあります。そうすると大抵素人さんは「Excelでファイルが開かない！」と大騒ぎして、データ作成元のこちらに問い合わせしてくるので、そのタイミングで"待ってました"とばかりに、電話で開き方講座をリモートコントロールして行なうわけです。csv取り扱いのマニュアルをつけようが、事前に注意を促そうが、現実に困るまでは、何を云われているのかわからない人はわかってはくれませんので、拡張子を変えてしまうのが、相手のレベルも測れて、意外と安全なんですよね…。

*1タブは¥t、改行は¥nとかで表現できるエディタであれば、検索対象に「,」カンマを入れて、置換後文字列に「¥t」と入れれば一発で置換完了。WinでフリーのエディタだとTeraPadあたりだと、アドミ権限をもっていなくてもインストール可能なので、わりと万人向けにオススメかも。

Excel布教ことはじめ

客寄せパンダ的にExcelの簡単な小技・哲学なぞもちまちま載せていこうと思います。新人教育の時節ですし…（遠い目）。

Excel小技サイトは数あれど、私の中にもExcel哲学はありますので…。小技は数ある小技サイトで十分だと思いますし、細かいテクニックはその都度調べればいいと思っているわけですが、最低限覚えておくべき実践的な技、及びExcelを使うときに根底を流れるべき「哲学」の部分が、小技サイトや市販の教本にはどこか物足りないさを感じるわけですよ。私のExcel上における価値の基盤は、速さ、正確さ、美しさ。云ってみれば、処理の最短方法というところでしょうか。

ジオング
足(線引き)なんてただの飾り…！とは云いませんが。エクセルで線を引っ張るのは、最後でいーの！引き方にもちゃんと美学があるわけですから。

普段から私はExcelラブを公言しているので、Excel/Accessネタでは便利屋の如く呼び出され、他人の手元をみるチャンスも多いのですが…。

Excelは手軽なアプリなだけに、ユーザーのレベルも千差万別で、それ故に怖いツールだなーと思うことがしばしばあります。

この辺の「常識」の違いが、人災的ボトルネックになったりもするので（中継ぎ役が確認のつもりでCSVファイルをExcelで開いたせいで、データが壊れた！！とかはたまに聞く話…。運用を考えるとき、この種の余計なことをさせない経路を作るか、「常識」を関係者各位に徹底認知させるかはケースバイケースですが、その辺も読み込んでおかないとあっさり事故になりますからねー）、こちら側から歩み寄る必要は常に感じているわけですが。普段Excelを使い倒す必要のない人たちにどうこう云ってもしょうがないので、わかっている方が、基本、先回りして予防に走るしかないなー、というのが私のスタンスです。

とはいえ、あんなものは飾りです。 偉い人にはそれがわからんのですよ。(byジオン兵)と云いたくなることも、正直多いです。

あんなもの、というのは、罫線引き、セルの結合、フォントサイズ、色つけなどなど。Excelのビギナーであるほど（営業さんとか）「見た目のみ」重視で表を作成しているフシがありますが、私らデータ屋は再利用のしやすさを重視します。

見栄えも重要ではありますが、幾らキレイに色をつけても、表をつくるからには、色だけではなくフラグ(或いはコード)もたってなければナンセンス。フラグさえ立っていれば、色づけ線引きなんて瞬殺ですから。

「データを作成する」ときは、常に再利用（加工のしやすさ）について考慮するべき。偉いヒトにはそれがわからんのですよ。いくら綿密に網掛けをしたって、そのデータを二次利用するとき、網掛けじゃ、フィルターだとか並び替えだとかが全然できないじゃんよ。営業君がご丁寧に網掛けしてくれたデータをみて、その後ぼちぼち手作業でフラグをたてていくときのむなしさときたら…！同じ労力をかけるのなら、色づけじゃなくて、まずはフラグを立ててくれよ！！コストのムダ！──と心中叫んでいるExcelユーザーは多いはず。

タグでいえば、物理タグと論理タグの溝みたいなものでやんす。私は当然論理タグ派です！！なんちゃって。あっ、そこでソースをみないこと！

mumble→今年の新人はカーリング型でしたっけ。超氷河期時代の私たちとは隔世の感がありますの…。

6月以降どこへ配属されるにしろ、ググり方、Excel、Accessの基本は3種の神器として叩き込まねばの、うちで預かるからには。

でも実際の仕事としては、手頃なサイズの課題が今現在、私の手元にはあまりないんだよなー…。入力作業でも集計作業、ラベル印刷でも細かいものならなんでもいいんだけど、誰かなんか軽めの仕事をくれないかなー。やっぱりナマの教材の方が燃えるし。

自分の得意ジャンルのものは、ある意味、気がつくと同時に処理しちゃっているところがあるので、私の周囲では常に残件ゼロ状態…。わりと困る。

Webアプリのバッチ処理

こんばんは。なにかとスタンドアローンのじょにです（←ちょっとヤケ）。

普段私はバッチ処理系なものを作ることが多いので、大量データを一気に加工するシーンはよくあります（でもMax60万件くらい。まーそんなもんです。Accessなので）。バッチ処理といえども、流して帰る…、という悠長なことはなかなかやってはいられませんので、なんであれ、待ち時間（処理時間）はなるべく短くすることは常に気にかけています。その際、まず私が考えることは、アプリからDBへのアクセス回数を減らこと。

DB設計も絡んでくる話だとは思いますが、手っ取り早い手段としては、

• コード等が入っているマスターテーブルをハッシュに入れて、いちいちエラーチェックやら情報付加の為にマスターテーブルにアクセスしにいかない。
• ぶんぶんLoopさせているところは、SQL一本で一挙にカタをつけられないか考える（Loopの回数を減らす）。

あたりでしょうか。

ADO.NET であれば、テーブルをDataSet（メモリ常駐型のデータ表現）へ展開させるので、自然とそれほどDBへはアクセスしなくてすむようになりましたが（もっともメモリに展開してからの書き方は色々で、書き方次第かなり速度に差は出ます。DataViewに突っ込んでRowFilterを使うよりハッシュを使った方が早いとか、リレーションシップを生成した方が早いとか）、Access単体の時はADO/DAO接続なので、インデックスの張り方、Dictionary関数とSQLの使い方に比重がかかってきます。その為には、メモリでもワークテーブルでも使えるものはなんでも使おうとします。

ところがWebのお作法は少し違うようですね。

近頃近所でアワアワしている人が、テストサーバーでWebアプリからよくCSVファイルをテーブルへ登録する実験を行なっています。登録したい件数が多すぎて（といっても4万件くらい？）、1回でアップロードできないので、その原因を探っているとのこと。テスト環境のColdFusionからかえってくるエラーメッセージはメモリーオーバーです（なんでメモリーオーバーのエラー表示？？？）。そんなこんなで、現時点で本番環境で速やかにできることといえば、CSVファイルを分割して、ちまちまアップロードすることくらいなんですね。あまりにそれでは手間ヒマがかかりすぎるということで、うまいことソースに修正を加えたいらしいのですが…。

実験してみてわかったことは、一見胡散臭そうにみえたCSVファイルの読み込み、INSERT文自体は4万件であろうとも、実はほんの数秒の処理であること。負荷の原因は、どうやらCSVファイルに対するエラーチェックだということです。で、そのエラーチェックのソースがどうなっているかといえば、1件1件エラーチェックする為に、次々、コード表を持っているテーブルを見に行っては値のチェックを行なっている。え。

4万件×何回テーブルを見に行っているの？

担当者は「Oracleだから沢山アクセスがあってもダイジョーブ」とか云っていますが、それってホントのことですか？この辺のことは簡易DBのAccessユーザーの私には正直わかりませんが、仮に1行につき10回DBを見に行ったとすれば、あっという間に40万回の連続ワザ。ホントにそれでヘーキなのでしょうか。なんであれ、ラウンドトリップ回数なんてなるべく少なくするべきものなんじゃないの？？？

Webの世界だと、色んな人が色々なテーブルを参照したり更新していたりいるし、アプリケーションサーバーでパワーの要ることをやったり（ハッシュとかも私のようにばんばん使っていると、一人分のメモリ消費×同時アクセスしている人数なので、あっという間にサーバーのメモリを消費してしまうわけですね）、色んなレコードに対して一挙にどうこうすると、Webを使っていた他の人が困っちゃう…、という発想があるから、ロックをかけるレコードはなるべく少なく、ちまちま1件ずつレコードを見に行くのがまずは基本のようですが。

CSVファイルの登録なんてバッチ処理なんだし、ある程度のコード表ならいちいちDBへアクセスしにいかずにメモリで持っていた方が断然早いし、既存データとの重複チェックを行ないたいなら、ワークテーブルに一端入れて、SQLで一発チェックした方が手っ取り早い気がするんですけど（或いはテーブルに入れてステータス管理とかでもまぁいいけど、インデックスとか張っちゃったりしていると問題ありそうなので、ワークテーブル）。ワークテーブルにもセッションみたいなものを入れられるフィールドを1列作っておけば、ワークテーブルに入ったデータのうち、どのセッションで利用したデータかの識別はできるわけだし──とか考えちゃうわけですが、なんらかの「失敗」がおきて残っちゃったワークテーブルのデーターをいつ削除するんだ、とか色々あって、そういう考え方自体危険なのかなぁ…、と無知なだけにモヤモヤ。

普段、スタンドアローン寄りのアプリばかり作っているので、この種の「バッチ処理」の実装部分で私はそこまでメモリの消費を気にしたり、同時アクセスしているメンバーのことをあまり考えていません。複数人で使うLAN内で使ってもらうようなアプリに「バッチ処理」を載せるときには、DBへアクセスしている人が自分以外にいないかをチェックする機能をつけて、「バッチ処理を行なう人は現時点で一人」という環境を作ってから、バッチ処理を実行させたり。「運用」とセットで考えると、単体のプログラムの外側で簡単に解決できちゃうことも多いですから、その辺で無理のないような回避方法を考えるとか（←1本のソフト単体でなんでもかんでもどうにかしようとはあまり思っていない。人の動きを簡便にすることは念頭にありますが）。

私の使っているDBはAccessですし、同時アクセスの人数はもともと少なく、それが通ってしまう素朴な世界に住んでいるので、考え方も「素直」なんですよねー。Web界隈に生息するイシカワさんと話していても、この辺からしていつも距離を感じます。想定条件のあまりの違いに、私ってスタンドアローンなんだなー、Winアプリなんだなー、としみじみ思うわけですね。

まーなにはともあれ、とりあえず、1回のアップロードで全部登録することを最優先に考えるなら、処理時間は無視して、5,000件くらいごとに、スリープさせるロジックをループの最中にいれちゃえば、殆ど今のソースを変えずに、全部登録できるんじゃないかなーというのが私の見込みです。どこでメモリーオーバーをおこしているにせよ、ファイルを小さくすれば通るロジックであることは確かなので、ときどきスリープして、ある程度のかたまり毎に処理を完結させてしまえば、どうにかなるのではないかなーという仮説。根本的な解決にはなりませんが、うまくいっているロジックを大改造するよりは安全・お手軽だし、ファイルをちまちま分割してからアップロードする現状よりは、黙って待つ方がラクチンだと思うし。

なんだか誰も信用してくれずに実験すらしてくれないんですけど（カウントして余りがゼロになったときにスリープするだけなのに！！）、実験してもらえないほど的外れな仮説なのでしょうか？なにかとマイノリティの自覚はあるけどさ…（悲）。

ColdFusionとJava

ColdFusionはイシカワさんと違って素人なのですが(そもそも私はWeb系全般に疎い…。それに比べてイシカワさんはCFの日本語の参考書を全種類買い集めた人です)。隣で他人がColdFusionのソースの改修でアワアワしていたので、なんとなくソースを見ていて、ナゾなところを、ちまちまとググっておりました（好奇心＆手伝いです）。

そんなこんなでウワサには聞いていたけど、CFってホントにJavaで実装しているんだなぁ、と妙に実感したサンプルソース。

<cfset fr=CreateObject("java","java.io.FileReader")>
<cfset fr.init("/tmp/testfile.txt")>
<cfset br=CreateObject("java","java.io.BufferedReader")>
<cfset br.init(fr)>
<cfloop condition="true">
  <cfset line=br.readLine()>
  <cfif not IsDefined("line")><cfbreak></cfif>
  <cfoutput>#line#<br></cfoutput> <!--- この部分 --->
</cfloop>
<cfset fr.close()>

参照元：逆引きColdFusion(1)

上記のソースで私にはよくわからなかったのが、initで、これはなんだろうと思ってググってみたら、リファレンスでは以下のような定義になっていました。

Use the init method, with appropriate arguments, to call an instance of the class.

CFML Reference

initメソッドでjavaのクラスのインスタンス生成というわけなんですね。initのカッコの中は当然コンストラクタの引数。なるほど。

さらにもう一つ、冒頭のソースで躓いたのは、

<cfloop condition="true">

conditionの後の""に条件式を入れるようですが、trueの存在がなんとなく唐突に見えてナットクがいきづらかったのですね。

  <cfif not IsDefined("line")><cfbreak></cfif>

で、読みこんだ行がカラッポだったら、ループを抜けるのでどーでもいいといえばどーでもいいのでしょうが、

<cfloop condition="br.ready()">
  <cfset line=br.readLine()>
  <cfoutput>#line#<br></cfoutput>
</cfloop>

とかの方がなんとなく、個人的には読みやすい気がする…。この辺は普段、自分がそういう書き方ばかりをしているせいなのでしょうか（あ、でも条件式にreadyメソッドを入れることが適切なのか、Javaに疎いので実はあまりわかっていません。どーなんでしょう？)。

ちなみにあれやこれやのサーフィンの道中に、書き込み速度のやり方の比較実験の記事も見つけました（読み込みにあらず）。

CFFILE vs Java.IO.BufferedWriter

CFFILE tag を使うより、圧倒的にJava.IOを使った方が早いんですね。

5.5MBくらいの書き込みでCFFILE tagをフツーに使っていたら600秒かかっていたものが、Java.IOを利用したら、1秒になったそうです。上記サイトの筆者は、当然のことながら、CFFILEはとても手軽だけど、大きいファイルを扱う時にはJavaのメソッドを使った方が良さそうだという結論をくだしています。

まーそりゃそうだろうな、と思いつつも、600秒って10分ですよ。落ちているかと思って、途中でブラウザを閉じてもおかしくない長さですよね。道具は適切に使い分けたいものですね。

memo→本日のブックマーク

はてなのブクマ

ColdFusionのブクマ。ColdFusionは手軽だし、エラーとかも返してくれて結構いいツールだと思うけど、なんで流行らないのでしょーか？

ColdFusionカフェテリア

フォーラム。CF8って.NET Framework 2.0も使えるのか。へー。ほー。でも.NETを使うと今のところWinに環境が限定されちゃうもんね(悲)。Monoプロジェクトに期待したいところだけど、そのままの移植は厳しそうかなぁ…。

Coldfusion-Style

なんか追っかけていたら色々と…。

カネゴンの78ちゃんねるまとめブログ

エイプリルフールですね。あちこち今年も、エイプリルフールサイトが登場しているのでしょうか。

窓の社は毎年なんとなく見てしまうわけですが（今年は携帯サイトっぽいデザイン）、近年個人的に大ヒットしているのは、円谷ステーション。 毎年4月1日のみ存在しているハイクオリティサイト。今年は「カネゴンの78ちゃんねるまとめブログ」。2chのパロディみたいですね（SNSサイトは帰ってこれなかったらしい？？）。この1日限りに向ける情熱に、脱帽します…。

1日限りなので、今日もせっせと探索しよーと思いマス。o(％)o

サイバー大学

この仕事をやっているとコンピューターサイエンスを しっかり学びたいという欲求がしばしば起こります。 1人で学ぶのは、ちょっと範囲が大きすぎで難しいですしだからと言って情報系大学に入学するのもスクーリングなどかなり難しい。

そこで、私が注目してるのがサイバー大学
ちょっと、名前があれなのですが、文部省が認可した学位も 取れる正規の大学です。

授業は、すべてネットから受講可能で通う必要がありません。 設立してから、1年が立ちましたが社会人学生が多くそこそこ うまく行ってる様子です。

ですが、問題がひとつ。

学費が卒業までに270万もかかります。 正規の大学なのでしょうがない気もしますが、私みたいな 境遇の人には、かなり厳しい気がします。

せめて100万以下に、ならないものでしょうか。