メインコンテンツへスキップ
見出し画像

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経由で値を流し込むより地味ですが、事故の少なさで選ぶならこちらだと感じています。

    あなたへのおすすめ