使用Openpyxl将IF公式插入excel后出现“@”符号

我的目标是使用 Openpyxl 将此公式输入到 Excel 中。

=CORREL(IF(A2:A7;A2:A7);B2:B7)

IF 公式的原因是忽略内部有 0 的单元格，默认情况下 CORREL 函数不会忽略这些单元格。

当将此公式插入 Excel 时，输出实际上是：

=CORREL(IF(@A2:A7;A2:A7);B2:B7)

如果在给定范围的同一行（此处为第 2-7 行）的单元格中输入公式，则该公式仍然有效。但给出#VALUE！当放在另一行时。手动删除“@”可以解决问题，但不可行，因为我想用它来自动分析。

我发现在某些情况下，Excel 为数组添加了“@”，并替换为“[#This Row]”Excel 2010 之前的版本 https://superuser.com/questions/501691/what-does-the-bar-foo-operator-do-in-excel-in-particular-the-at-sign，这解释了为什么在同一行输入公式时会起作用。 当公式不被识别。 https://stackoverflow.com/questions/61705150/openpyxl-is-inserted-to-formula-when-saving-to-file但是，这两个链接都没有解释为什么在我的示例中添加“@”。

这是在 IF 函数中使用范围的副作用吗？这是将范围解析为 IF 函数的错误（/功能）吗？

这是我用来复制错误的代码。我的 Openpyxl 版本是 3.0.5，我使用 Microsoft 365 中的 Excel。

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
rows = [
    ['Size', 'Batch 1', 'Batch 2'],
    [2, 40, 30],
    [0, 40, 25],
    [0, 50, 30],
    [5, 30, 25],
    [6, 25, 35],
    [7, 20, 40],
]

for row in rows:
    ws.append(row)

formula = '=CORREL(IF(A2:A7,A2:A7),B2:B7)'
ws.cell(row=5, column=5, value=formula) # Formula in row 5 works
ws.cell(row=9, column=5, value=formula) # Formula in row 9 returns #VALUE!
formula = '=CORREL(A2:A7,B2:B7)'
ws.cell(row=6, column=5, value=formula) # CORREL function with ranges works fine
formula = '=IF(A2:A3,A2:A3)'
ws.cell(row=7, column=5, value=formula) # enters "=@IF(@A2:A3;A2:A3)"
formula = '=IF(A2,B2)'
ws.cell(row=8, column=5, value=formula) # enters "=IF(A2;B2)"

wb.save("test.xlsx")

在“test.xlsx”中，这是我的输出（单元格 E5-E9）

-0.9528
-0.9528
#VALUE!
40
#VALUE!

以及公式：

=CORREL(IF(@A2:A7;A2:A7);B2:B7)
=CORREL(A2:A7;B2:B7)
=@IF(@A2:A3;A2:A3)
=IF(A2;B2)
=CORREL(IF(@A2:A7;A2:A7);B2:B7)

在这里回答我自己的问题。谢谢JvdV https://stackoverflow.com/users/9758194/jvdv让我走上正确的道路。

In this https://stackoverflow.com/a/57441549/回答我发现什么解决了我的问题。我在示例代码中保存 Excel 文件之前添加了这一行：

ws.formula_attributes['E9'] = {'t': 'array', 'ref': "E9:E9"}

这实质上将单元格“E9”中的公式设置为读取为数组公式，该公式在 Excel 中通过大括号 ({}) 之间的公式显示。在旧版本的 Excel 中，这是通过在输入公式时按 ctrl-shift-enter 来完成的。请注意，在公式字符串中写入这些花括号不起作用。

如果有人知道“ref”部分是如何工作的，请随时在评论中添加它。