openpyxlで行を挿入しても、数式の参照先までExcelと同じように調整されるとは限りません。セルが一行下へ移ったのに、数式の文字列は元の行を指したまま、という状態が起こります。
公式資料では、行列の挿入・削除に伴う数式、テーブル、グラフなどの依存関係は管理しないと説明されています。セルの位置と、式の中の参照を別々に確認します。
移動した数式の参照を比べる
次はF2でB2からE2を合計する架空のシートです。二行目へ新しい行を挿入すると元のセルは三行目へ移ります。その後、単純な相対参照だけをTranslatorで移動させます。
from openpyxl import Workbook
from openpyxl.formula.translate import Translator
book = Workbook()
sheet = book.active
for column, value in zip(["B", "C", "D", "E"], [1, 2, 3, 4]):
sheet[column + "2"] = value
sheet["F2"] = "=SUM(B2:E2)"
original_formula = sheet["F2"].value
sheet.insert_rows(2)
print("移動したセルの数式:", sheet["F3"].value)
assert sheet["F3"].value == "=SUM(B2:E2)"
sheet["F3"] = Translator(
original_formula, origin="F2"
).translate_formula("F3")
print("相対参照を移した数式:", sheet["F3"].value)
assert sheet["F3"].value == "=SUM(B3:E3)"
assert Translator("=B2+$C$1", origin="F2").translate_formula("F3") == "=B3+$C$1"
book.close()
挿入直後はF3へ移ったセルに=SUM(B2:E2)が残り、変換後は=SUM(B3:E3)になります。Windows・Python 3.12.10・openpyxl 3.1.5で、数式文字列と絶対参照が保たれる例を確認しました。Excelによる計算結果は確認していません。
Translatorには、式が元にあったF2と、移動先のF3を渡しています。挿入後の位置を元位置として扱うと、どれだけ参照を動かすかが変わります。
範囲を広げる変更とは区別
この例は同じ明細行の参照を一行下へ移す処理です。新しい明細を合計範囲へ追加したいという要件とは、変更の意味が違います。
たとえば月末の合計式を残したまま途中へ明細を足すなら、開始セルと終了セルを業務の範囲に合わせて作り直す必要があります。すべての式へ同じ移動量を適用しても、期待した合計範囲になるとは限りません。
数式のあるセルだけでなく、そのセルを別の場所から参照している式や、定義名、テーブル、グラフも確認対象です。TranslatorはA1参照に対応する道具で、ブック全体の依存関係を一括して修復するものではありません。
計算結果と式の確認を分ける
openpyxlはExcelのような数式の再計算を行いません。保存できたことやdata_onlyで開けたことを、変更後の計算結果が正しい証拠にはしないでください。
まず元の帳票を別名で残し、挿入する位置の前後にある式を一覧へ出します。期待する式の文字列と比べた後、必要な計算環境で結果を確かめます。複雑な帳票へ適用する前に、今回のような小さな表で、動く参照と動かない参照を区別すると追いやすくなります。
基本の編集はopenpyxl入門、帳票の確認項目は見積書・請求書作成の設計で補えます。
