毎月、複数の拠点から送られてくるExcelを1枚の集計表にまとめる。総務や経理をやっていると、たいてい誰かがこれを手でやっています。私も長いこと手でやっていました。
この作業を自動化するときによく言われるのが「セルを1つずつ触ると遅いから、範囲をまとめて読み書きしなさい」という話です。私もそう信じていました。実際に両方を作って計ってみたら、縮んだのは13%だけでした。
効いたのは別のところでした。この記事は、その実測の記録です。動かしたスクリプトと、途中で踏んだ4つの罠もそのまま載せます。数値はすべて架空データで再現したもので、勤務先の情報は含みません。
測った条件
- 入力：拠点別の月次報告ブック 30ファイル（1ファイルあたり40科目 × 実績・予算の2列）
- 出力：科目40行 × 拠点30列の集計表1枚。実績合計・予算合計・差異つき
- 手作業なら：30ファイル × 40科目 × 2列 ＝ 2,400セルの転記
- 環境：Windows 11、メモリ16GB、Excel（デスクトップ版）、PowerShell 5.1
- データ：拠点名・科目名・金額はすべてこの記事のために作った架空のもの
手作業の時間は「実測」ではなく件数で示します。ここを推計で埋めると記事全体が信用できなくなるので、測っていないものは測っていないと書きます。2,400セルを1セル3秒で転記すれば2時間、1秒でも40分です。
結果：3つの方式を作って計った
| 方式 | 30ファイルの読込 | 合計 |
|---|---|---|
| ① 1セルずつ読む（Excelを操作） | 109.00秒 | 118.87秒 |
| ② 範囲をまとめて読む（Excelを操作） | 93.43秒 | 103.17秒 |
| ③ Excelを開かずにファイルを直接読む | 0.17秒 | 11.17秒 |
①から②で 13%。②から③で 読込だけなら約550倍、全体で約9倍です。
「一括代入で速くなる」は、思ったほど効かなかった
①と②の差は15.7秒です。1セルずつ読んだセルは 30ファイル × 40行 × 4列 ＝ 4,800セル。1セルあたり約3ミリ秒。確かに1セルずつは遅いのですが、遅さの総量が小さい。
時間を食っていたのは、その手前でした。ファイルを1つ開くのに約3秒かかっており、30ファイルで約90秒。これが全体の8割以上を占めていました。中で何セル触るかを工夫しても、この90秒は動きません。
これは私の思い込みが外れた部分です。「セルを1つずつ触るな」は正しいのですが、正しさの効き目が小さい。ファイル数が少なくてセル数が多い作業（1ファイルに10万行、など）なら逆転するはずで、そこは条件次第です。自分の作業がどちらなのかを先に見るべきでした。
効いたのは「Excelを開かない」だった
xlsxファイルは、実体はzipで固めたXMLの束です。拡張子をzipに変えれば中身が見えます。読むだけならExcelを起動する必要はありません。
Add-Type -AssemblyName System.IO.Compression.FileSystem
function Read-XlsxRange {
param([string]$Path)
$zip = [System.IO.Compression.ZipFile]::OpenRead($Path)
try {
$read = {
param($name)
$en = $zip.Entries | Where-Object { $_.FullName -eq $name }
if (-not $en) { return $null }
$sr = New-Object System.IO.StreamReader($en.Open(), [System.Text.Encoding]::UTF8)
try { return $sr.ReadToEnd() } finally { $sr.Close() }
}
# 文字列セルは本体に実体を持たず、共有文字列への添字だけを持っている
$shared = @()
$ssXml = & $read 'xl/sharedStrings.xml'
if ($ssXml) {
foreach ($m in [regex]::Matches($ssXml, '(?s)<si>(.*?)</si>')) {
$txt = ''
foreach ($t in [regex]::Matches($m.Groups[1].Value, '(?s)<t[^>]*>(.*?)</t>')) {
$txt += $t.Groups[1].Value
}
$shared += [System.Net.WebUtility]::HtmlDecode($txt)
}
}
$sheet = & $read 'xl/worksheets/sheet1.xml'
$cells = @{}
foreach ($m in [regex]::Matches($sheet, '(?s)<c r="([A-Z]+\d+)"([^>]*)>(.*?)</c>')) {
$ref = $m.Groups[1].Value
$attr = $m.Groups[2].Value
$vm = [regex]::Match($m.Groups[3].Value, '(?s)<v>(.*?)</v>')
if (-not $vm.Success) { continue }
$val = $vm.Groups[1].Value
if ($attr -match 't="s"') { $val = $shared[[int]$val] } # 文字列は引き当てる
$cells[$ref] = $val
}
$kc = @(); $kn = @(); $j = @(); $y = @()
for ($r = 6; $r -le 45; $r++) {
$kc += [int]$cells["A$r"]
$kn += [string]$cells["B$r"]
$j += [double]$cells["C$r"]
$y += [double]$cells["D$r"]
}
return @{ kc = $kc; kn = $kn; j = $j; y = $y }
}
finally { $zip.Dispose() }
}この方式にも制約があります。数式の計算結果は、ファイルに保存されている時点の値しか読めません。Excelで開けば再計算されますが、直接読む場合は再計算されないので、元ファイルが数式だらけの場合は注意が必要です。今回の元ファイルは値だけだったので問題になりませんでした。
なお書き出す側はExcelを使っています。③の11.17秒のうち、集計表の組み立てと保存で9.6秒。ここはxlsxを手で組み立てるより、素直にExcelに任せた方が安全です。読むのは自前、書くのはExcel、という分け方に落ち着きました。
速さより先に、3つが同じ答えを出すか確かめた
速くなっても数字が違えば意味がありません。3つの出力を、見出しから合計行まで 42行 × 35列 ＝ 1,470セルすべて突き合わせました。
比較セル数: 1470 1セルずつ → 一括代入版と一致: True Excel開かず → 一括代入版と一致: True
この確認をせずに速い方へ乗り換えるのが、いちばん危ないやり方だと思っています。速度の記事は多いのに、「同じ答えになることを確かめた」と書いてある記事は少ないのが前から気になっていました。
作る途中で踏んだ4つの罠
① Excelに値を渡すと「指定されたキャストは有効ではありません」で落ちる
配列から取り出した値や、オブジェクトのプロパティをそのままセルに入れると落ちます。中身は文字列なのに、包み紙がついたまま渡ってしまうためです。
$ws.Cells.Item(2,2).Value2 = $k.Name # 落ちる
[string]$name = $k.Name
$ws.Cells.Item(2,2).Value2 = $name # 通る2回、同じ理由で止まりました。Excelに渡す直前で型を明示する、と決めてから起きなくなりました。
② 2次元配列の添字に式を書くと、別のものになる
$hdr[0, 2 + $c] # これは「0番目と2番目と$c番目」という3つの添字として扱われる
$hdr[0, (2 + $c)] # 括弧で囲えば意図どおりエラーメッセージは「2次元配列に [0,2,0] では添字できません」でした。読んでも何が起きたのか分からない類のエラーです。カンマの後ろが式だと、添字ではなく「値を並べたもの」として解釈されていました。
③ 文字列に値を差し込むとき、引数が1つしか渡らない
"{0},{1}" -f $code, $name # $name が渡らずエラー
"$code,$name" # 素直にこう書くこれも黙って壊れるのではなく大量のエラーが出るので、気づけはします。ただ30回ループの中で起きると、同じエラーが30個並んで原因が見えにくくなります。
④ これがいちばん怖い。届いていない拠点が、黙って消える
最初に書いたスクリプトは、フォルダにあるファイルから拠点の一覧を作っていました。これだと、ある拠点がファイルを送ってこなかった月に、その拠点が集計から静かに抜け落ちます。エラーは出ません。合計が少し小さくなるだけです。
手作業なら「1つ足りない」と気づきます。自動化すると気づけません。速くなったぶんだけ、間違いも速く出てきます。
そこで拠点マスタ（コード・名称・稼働／廃止）を別に持ち、毎回突き合わせるようにしました。
=== 拠点の突合 ===
マスタ稼働 31 / ファイル 30
【未提出】
131 みやま営業所
逆方向も見ます。マスタに無いファイルと、廃止済みなのに届いているファイルも報告させます。廃止した拠点のファイルが翌月も送られてきて二重に計上される、というのは実際に起こりうる事故です。
自動化で本当に効いたのは、速度よりこちらだったと思っています。毎月かならず同じ確認が入るようになったという点です。人間は忙しい月ほど確認を飛ばします。
集計値はベタ打ちしない
合計欄は、計算した数値を書き込むのではなく =SUM(...) の数式として入れています。受け取った人が1つの数字を直したときに、合計がついてこないと事故になるからです。差異欄も =実績-予算 の数式にしています。
自動生成した表ほど、中の人は「合っている前提」で見ます。あとから手で1マス直されることを想定しておく方が安全でした。
やらなかったこと・この記事の限界
- 手作業の所要時間は計っていません。件数（2,400セル）だけを示しました
- 1回の測定です。他のアプリの動作状況で数十秒は動くはずで、桁の話として読んでください
- 作る側の時間は含みません。今回は罠を踏みながらで数時間かかっています。毎月2時間の作業なら数ヶ月で元は取れますが、年1回の作業なら作るべきではありません
- xlsx直読みは、条件がそろったときだけ有効です。ファイル数が多く、1ファイルが小さく、数式の再計算が要らない場合。逆の条件なら②の方が素直です
- 元ファイルの様式が拠点ごとに違う場合、この方法は成立しません。様式を揃える方が先です
まとめ
- 30ファイルの集計を3方式で作って計った。118.87秒 → 103.17秒 → 11.17秒
- 「セルを一括で読む」で縮んだのは13%だけだった。支配的だったのは、ファイルを1つ開く約3秒 × 30回
- 効いたのはExcelを開かずにxlsxを直接読むこと。読込だけなら約550倍。ただし数式の再計算が要る場合は使えない
- 速い方へ移す前に、3方式が1,470セルすべて同じ値を出すことを確かめた
- いちばん効いたのは速度ではなく、「届いていない拠点」を毎回機械が指摘するようになったこと
- 合計は数式で入れる。あとから1マス直されても追随するように
自分の月次作業のうち、どれが仕組みにできてどれが手作業のまま残るのかは、月次業務の自動化余地診断で判定できます。今回の「拠点別の集計と転記」は、判定基準の4軸すべてが満点になる、いちばん自動化に向いた種類の作業でした。
この記事で書いたスクリプトの全文（4本）と、自分の職場の様式に合わせて直す手順、事故を防ぐ3つの仕掛け、踏んだ罠5つの詳細は、note（有料・1,000円）にまとめました。この記事で書いた結論と実測値は、すべて無料のこちらで読めます。
コメント