
Excelの集計自動化は値より数式を書き戻す|openpyxlでの設計
こんにちは、すきまログです。
毎週・毎月のように「シートの数字を拾って、別のシートに転記する」という作業をやっていた時期がありました。地味に時間を食う割に、ミスがあっても気づきにくい作業です。今日はこれを Python の openpyxl でどう自動化したか、その考え方をまとめてみます。
何が面倒だったか
集計作業自体は単純です。ある範囲のデータを合計・平均して、レポート用のシートに書き写す。ただ、これが「毎回手でやる」だと地味に効いてきます。
元データの行数は毎回変わるので、範囲選択からやり直しになる
転記先の書式(罫線・色分け)を毎回揃え直す
「先月分の転記、まだやってなかった」に気づくのが数字を見に来た時になる
一つひとつは数分の作業でも、頻度が高いと積み上がります。
数式で書き戻す、というやり方
最初は Python 側で数値を計算して転記先に流し込む方式を試しましたが、数式そのものを書き戻す方式に切り替えました。
転記先のセルに、生の数値ではなく =SUM(...) や =XLOOKUP(...) のような数式を直接セットする
こうすると、元データが後から修正されても転記先が自動で追従する
「今月分だけ集計し忘れていた」という事故が起きにくくなる(数式が入っている限り、見た目で気づける)
openpyxl では、セルに文字列として数式を入れるだけです。
# 値ではなく数式を書き込む。行番号はスクリプトが都度組み立てる
ws.cell(row=r, column=col).value = f"=SUM({sum_range})"
ws.cell(row=r, column=col2).value = f"=_xlfn.XLOOKUP(A{r},問診!$A:$A,問診!$P:$P,0)"XLOOKUP のような新しい関数は、_xlfn. を頭に付けないと Excel 側で #NAME ? になります。ここは最初に一度ハマりました。
数値を計算してから書き込むより、Excel の計算機能をそのまま使わせる方が事故が少ないというのが、実際にやってみて分かったことでした。
ドラッグコピーのズレが消えたのも大きかった
もともとこのシートには、手で数式をドラッグコピーしたことによるバグが残っていました。
参照が自分の行ではなく数百行ズレた行を指していた
$ を付け忘れた検索範囲が行ごとにスライドして、途中からデータ範囲の外を見ていた
スクリプトが1行ずつ正しい数式を生成するようにしたら、この手のズレは構造的に起きなくなりました。数式を手で伸ばさなくてよくなったのが、実は一番大きな効果だったかもしれません。
つまずいたポイント
列構成の差異:月ごとにシートの列が微妙にズレていることがあり、決め打ちの列番号で書くと数ヶ月後に壊れる。ヘッダー行から列位置を都度探す作り方に変えた
書式のズれ:数式だけ書き戻すと、罫線や太字などの書式が引き継がれないことがある。書式はテンプレート行をコピーしてから数式だけ差し替える形にして解決した
反映漏れに気づけない:自動化した直後は「動いているはず」で安心してしまい、実際にシートを開いて確認する頻度が下がった。今は反映後に件数だけでもログに残すようにしている
手作業をやめて変わったこと
「今月の集計、終わってたっけ」と確認しに行く時間そのものがなくなりました。数式が入っているシートを見れば、最新かどうかが一目で分かる状態になったのが一番大きいです。
すきま時間の使い方として
集計作業は「数字を合わせる」ことに気を取られがちですが、実際に楽になったのは数式の形で書き戻して、後から追従させるという設計判断でした。API経由で値を流し込むより地味ですが、事故の少なさで選ぶならこちらだと感じています。