使用Openpyxl将IF公式插入EXCEL后出现的符号(&QOT；@&QOOT；)

问题描述

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

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

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

将此公式插入EXCEL时，输出实际为：

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

如果在与给定范围(此处为第2-7行)同一行的单元格中输入公式，则此公式仍然有效。但却给了#价值！当放在另一排的时候。手动删除"；@"；可以解决此问题，但不可行，因为我要将其用于自动分析。

我发现，在某些情况下，Excel为数组添加了"；@"；，并将其替换为pre-2010 versions of Excel中的"；[#This Row]"；，这就解释了为什么在同一行输入公式时会起作用。 当公式is not recognized.is not recognized.时，openpyxl也会将"；@"；符号添加到公式中 但是，这两个链接都不能解释为什么在我的示例中添加"；@"；。

这是在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让我走上了正确的道路。

在this答案中，我找到了解决问题的方法。在示例代码中保存Excel文件之前，我添加了此行：

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

这实际上将单元格‘E9’中的公式设置为读取为数组公式，在Excel中通过将公式放在大括号({})之间来显示。在旧版本的Excel中，这是通过在输入公式时按Ctrl-Shift-Enter组合键来完成的。请注意，在公式字符串中写入这些大括号不起作用。

如果有人知道"ref"部分的工作原理，请随时在评论中添加此内容。