使用Openpyxl为多单元格添加非溢出数组公式（解决@符号问题）

问题根源 #

你遇到的@符号是Excel的隐式交集运算符，用ws.cell()直接赋值普通公式时，Excel会自动给数组公式加上@，把它转成单值运算，破坏原本的数组逻辑，导致返回#Div/0或#N/A错误。而ArrayFormula类是openpyxl专门用来定义传统数组公式的工具，能绕过这个自动转换。

批量实现代码 #

1. 导入依赖 #

from openpyxl import load_workbook
from openpyxl.formula.array import ArrayFormula
from openpyxl.utils import column_index_from_string, get_column_letter

2. 加载工作簿 #

# 替换为你的文件路径
wb = load_workbook("stock_data.xlsx")
calc_sheet = wb["Sheet1"]
raw_sheet = wb["Sheet2"]

# 提前转换列名到列号，避免手动数错
start_year_col = column_index_from_string("L")  # 1990年数据列
end_year_col = column_index_from_string("MM")   # 2024年数据列
year_count = end_year_col - start_year_col      # 计算年份跨度

3. 批量遍历添加数组公式 #

假设Sheet1从第10行开始，A列存股票代码，B列需要计算年均增长率：

# 遍历Sheet1的目标行（根据实际行数调整范围）
for row in range(10, calc_sheet.max_row + 1):
    stock_code = calc_sheet.cell(row=row, column=1).value
    if not stock_code:
        continue  # 跳过空股票代码行
    
    # 在Sheet2中定位对应股票的行（假设Sheet2A列是股票代码）
    stock_row = None
    for r in range(2, raw_sheet.max_row + 1):
        if raw_sheet.cell(row=r, column=1).value == stock_code:
            stock_row = r
            break
    
    if not stock_row:
        calc_sheet.cell(row=row, column=2).value = "无对应数据"
        continue
    
    # 构建年均增长率数组公式（以EPS为例）
    start_cell = f"{get_column_letter(start_year_col)}{stock_row}"
    end_cell = f"{get_column_letter(end_year_col)}{stock_row}"
    array_formula = f'=((raw_sheet!{end_cell}/raw_sheet!{start_cell})^(1/{year_count}))-1'
    
    # 用ArrayFormula赋值，避免自动添加@符号
    calc_sheet.cell(row=row, column=2).value = ArrayFormula(array_formula)

4. 保存处理后的文件 #

wb.save("processed_stock_data.xlsx")

关键注意点 #

• 传统数组公式特性：用ArrayFormula赋值的是传统数组公式，每个单元格都能看到完整公式，方便排查错误，完全符合你拒绝溢出公式的需求。
• 列号转换工具：用column_index_from_string和get_column_letter处理列名与列号的转换，避免手动数列导致的公式错误。
• 错误兜底：添加了空股票代码、找不到对应股票的判断，减少无效错误值的出现。

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