今まで散々Excel方眼紙を馬鹿にしてきたのだが、扱うことになってしまった。

図　今回のお題、Excel方眼紙で作られた給与明細

こんな感じで何十人分も縦につながっている。これを集計したいので、1人分のデータを1行にしたい　というのがミッションである。
これを作っている人（私に話を持ってきた人とは別）はExcelファイルに直接数字を打ち込んでいるようで、元データは存在しないらしい。本来は元データを作ってから印刷用の方眼紙に転記していれば、こんな仕事は必要ない。しかし、これしかないのだから文句を言っていても仕方がない。

しばらくファイルを眺めていると、いやなポイントが出てきた。
・人によって項目が微妙に違う（交通費があったりなかったり　等）
・人と人の間の空行の数が場所によって違うところがある（上では勤怠が終わってから次の人までの空行は17,18行目の2行だが、場所によって1行になったり3行になったりする）

はじめはVBAで作ろうかと考えたが、毎月Excelファイルが送られてくると聞いてやめた。マクロをファイルに入れる作業が発生すると面倒だ。それに結合がそこら中にある方眼紙が一部破壊される可能性もある。VBAで作るのは危険度が高いと判断した※1。

結論はバッチファイルとAWK。ExcelをcsvにしてAWKで必要な処理をして最終生成物をcsvに吐き出すことにした。今時はAWKなんか使わずにPythonなのだろうが、これだけのためにPythonをインストールするのは、「蟻を殺すのに爆弾を使う」ようなチグハグ感がある。昔から使っていて慣れているのでAWKで行くことにした。

AWKでやっているのは以下の処理
・金額欄に3桁ごとのカンマがあるので、csvにすると "200,000" というように”で囲まれて出力される。後の処理のために"の内側にあるカンマを除去、その後"も除去する。

・空行の数が変化するのは「氏名」という文字にヒットしたら新しい人　と定義することで対応。
・フォーマットが崩壊してもなるべく動作するように、特定文字列（例：所得税）にヒットしたら、次の行の同じ列を読む　という形で金額を取得。
・氏名も取得しておき、1名1行の形でcsvに書き出す。

図　書き出したcsvをExcelで開いたところ。こうなっていれば集計も簡単にできる。

はじめのExcelを開いてcsvで保存する部分は人間がやるしかないと思っていたのだが、やる方法があった。

PowershellでExcelのアクティブシートをCSV（UTF-8）で保存 - メモ※ Excelがインストールされていないと動きませんそれぞれ「xlsx2csv.bat」「xlsx2csv.ps1」をファイルに保存 xlsxファイルをxlsx2csv.batにドラッグ＆ドロップするとcsvファイルが作成されます xlsx2csv.bat powershell -NoProfile -ExecutionPolicy Unrestricte…

imabari.hateblo.jp

初めに動くのがバッチファイル（xlsx2csv.bat)なので、awkの処理をバッチファイルに追記するだけで全体を作ることができた。
xlsx2csv.batにExcelファイルをドラッグアンドドロップすれば全ての処理が完了する。AWKの実行環境を入れる必要はあるが、AWKはインストーラー不要で、実行ファイル(400KB)をバッチファイルやawkスクリプトと一緒に置いておけば済むので、大した問題ではない。

※1　VBAはあまり慣れていないので書きたくないというのもある。

給与明細のExcel方眼紙からデータを抽出した話