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

2022-02-21 00:00:00 python openpyxl excel-formula

问题描述

我的目标是使用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"部分的工作原理,请随时在评论中添加此内容。

相关文章