回答此问题可获得 20 贡献值,回答如果被采纳可获得 50 分。
<p>我的目标是使用Openpyxl将这个公式输入Excel</p>
<pre><code>=CORREL(IF(A2:A7;A2:A7);B2:B7)
</code></pre>
<p>IF公式的原因是忽略内部为0的单元格,默认情况下CORREL函数不会忽略这些单元格</p>
<p>将此公式插入excel时,输出实际上是:</p>
<pre><code>=CORREL(IF(@A2:A7;A2:A7);B2:B7)
</code></pre>
<p>如果公式输入的单元格位于给定范围的同一行(此处第2-7行),则此公式仍然有效。但是给了#价值!当换成另一排时。手动删除“@”可以解决此问题,但不可行,因为我想使用它来自动化分析</p>
<p>我发现,在某些情况下,excel会为数组添加“@”,并从<a href="https://superuser.com/questions/501691/what-does-the-bar-foo-operator-do-in-excel-in-particular-the-at-sign">pre-2010 versions of Excel</a>替换为“[#此行]”,这就解释了为什么在同一行中输入公式时会起作用。
当公式<a href="https://stackoverflow.com/questions/61705150/openpyxl-is-inserted-to-formula-when-saving-to-file">is not recognized.</a>时,openpyxl还会将“@”符号添加到公式中
但是,这两个链接都不能解释为什么在我的示例中添加“@”</p>
<p>这是在IF函数中使用范围的副作用吗?这是将范围解析为IF函数的错误(/特性)吗</p>
<p>下面是我用来复制错误的代码。我的Openpyxl版本是3.0.5,我使用微软365的Excel</p>
<pre><code>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")
</code></pre>
<p>在“test.xlsx”中,这是我的输出(单元格E5-E9)</p>
<pre><code>-0.9528
-0.9528
#VALUE!
40
#VALUE!
</code></pre>
<p>公式如下:</p>
<pre><code>=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)
</code></pre>