Openpyxl如何正确偏移引用单元格实现合计行动态向上求和

原代码问题说明 #

• 硬编码固定求和区间，数据行数变动后直接失效，且容易把合计行本身纳入计算范围触发循环引用
• 列偏移逻辑混乱，循环内重复对同一单元格赋值，还存在C5:16这类漏写列号的笔误，导致列引用完全错位
• 为单个合计行单独编写遍历循环，重复代码冗余度高，后续扩展维护麻烦

优化实现代码 #

核心逻辑是先识别所有合计行位置，再自动以「上一个分界点（表头/上一个合计行）」到「当前合计行上一行」作为求和区间，逐列写入同列SUM公式，从根源避免引用错误。

from openpyxl import load_workbook

# 替换为实际文件路径
wb = load_workbook("你的目标文件.xlsx")
ws = wb.active

# 可根据实际需求修改配置
total_keywords = {"Total complaints", "Total Attacks"}  # 合计行A列匹配关键词
sum_cols = ["B", "C"]  # 需要执行求和的列，新增列直接追加到列表即可
header_row_num = 1  # 表头所在行号

# 收集所有合计行的行号
total_row_nums = []
for row_idx in range(1, ws.max_row + 1):
    a_col_val = ws.cell(row=row_idx, column=1).value
    if a_col_val in total_keywords:
        total_row_nums.append(row_idx)

# 逐行写入动态求和公式
prev_divide_line = header_row_num
for total_row in total_row_nums:
    # 自动计算求和区间，天然排除合计行本身，不会触发循环引用
    range_start = prev_divide_line + 1
    range_end = total_row - 1
    # 为每个目标列写入对应列的求和公式
    for col in sum_cols:
        ws[f"{col}{total_row}"].value = f"=SUM({col}{range_start}:{col}{range_end})"
    # 更新分界点，供下一个合计行计算区间使用
    prev_divide_line = total_row

wb.save("添加合计公式_结果文件.xlsx")

方案优势 #

• 无循环引用风险：求和区间自动锁定在两个分界点之间，永远不会包含合计行本身
• 动态适配：无论中间新增/删除多少行数据、合计行位置如何变动，只要A列关键词匹配，公式区间会自动适配，不需要手动修改硬编码的行号
• 易扩展：新增合计行类型、新增求和列只需要修改开头配置项即可，不需要重复编写遍历逻辑
• 逻辑清晰：直接通过单元格坐标写入公式，没有多余的偏移计算，不会出现列引用错位问题

内容的提问来源于stack exchange，提问作者user18233539