Python openpyxl中的公式在Excel中显示不正确

Question

我在用Python的openpyxl库把公式导出到Excel时遇到了一些问题。我想把平均值和75%的四分位数的公式导出到Excel里。我使用的是德语版的Excel。对于平均值的公式，导出得还不错，英文公式“=AVERAGE(A2:A7)”在德语版中显示为“=MITTELWERT(A2:A7)”。但是对于75%的四分位数就不行了，英文公式“=QUARTILE.INC(A2:A7,75%)”在德语版中显示为“=@QUARTILE.INC(A2:A7;75%)”，这在Excel里会出错，而不是应该显示的德语公式“=QUARTILE.INKL(A2:A7; 0,75)”，这个公式是可以正常工作的。

这里有一段示例代码：

import pandas as pd
import os

# Change directory
directory = r'C:\Users\abc\Desktop\Test Formula'
os.chdir(directory)

# Create dataframe
data = {
    'Test1': [1, 2, 3, 4, 5, 6],
    'Test2': [16.46, 17.21, 14.98, 21.3, 89.1, 71.77]
}

data['Test1'].append('=AVERAGE(A2:A7)')
data['Test2'].append('=AVERAGE(B2:B7)')

data['Test1'].append('=QUARTILE.INC(A2:A7,75%)')
data['Test2'].append('=QUARTILE.INC(B2:B7,75%)')

df = pd.DataFrame(data)

# Create an Excel file
output_file = os.path.join(directory, "Output.xlsx")
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:  # Specify engine='openpyxl'
    df.to_excel(writer, sheet_name='Test_Output', index=False)

在（德语）Excel中，这段代码会生成以下公式/计算结果：

=MITTELWERT(A2:A7)（正确计算结果为3.5），

=@QUARTILE.INC(A2:A7;75%)（错误计算结果为#NAME?）

应该显示的是=QUARTILE.INKL(A2:A7; 0,75)（这个会正确计算为1）。有没有人知道为什么这段代码在Excel中没有生成正确的公式呢？

Answer 1

你需要添加 _xlfn. 这个部分。下面的代码应该可以正常工作：

data['Test1'].append('=_xlfn.QUARTILE.INC(A2:A7,75%)')
data['Test2'].append('=_xlfn.QUARTILE.INC(B2:B7,75%)')

这是来自 openpyxl 的说明：

openpyxl 文档：