如何在Python中正确写入Excel 365的FILTER函数以避免@符号问题？

问题描述 #

使用openpyxl 3.1.4写入Excel的FILTER函数时，即使手动给函数加上_xlfn.前缀（因openpyxl不原生支持该函数），写入后的公式开头会自动多出一个@符号，变为=@FILTER(...)。这个@是Excel的隐式交集运算符，会导致FILTER仅返回首个匹配项，无法展示完整结果列表，手动删除@后功能才恢复正常。

解决方案 #

以下两种方法可直接在代码中解决问题，无需手动操作：

方法1：通过数组公式方式写入 #

openpyxl支持用array_formula属性写入数组公式，Excel会识别其为需返回多结果的公式，不会添加@符号。示例代码：

from openpyxl import load_workbook

wb = load_workbook("your_file.xlsx")
ws = wb["Sheet1"]  # 替换为你的目标工作表名
# 指定结果范围（需覆盖可能的匹配结果行数），写入数组公式
ws.array_formula('C4:C100') = '=_xlfn.FILTER(Index!$D$4:$D$100,Index!$F$4:$F$100="JA","")'
wb.save("your_file.xlsx")

方法2：标记单元格为数组公式 #

若只需在单个单元格写入（Excel会自动溢出结果），可手动标记单元格为数组公式：

from openpyxl import load_workbook

wb = load_workbook("your_file.xlsx")
ws = wb["Sheet1"]
ws['C4'].value = '=_xlfn.FILTER(Index!$D$4:$D$100,Index!$F$4:$F$100="JA","")'
ws['C4'].data_type = 'f'
# 强制标记为数组公式
ws['C4'].is_array_formula = True
wb.save("your_file.xlsx")

原理说明 #

@符号是Excel动态数组功能的隐式交集运算符，openpyxl默认写入的普通公式会被Excel识别为动态数组公式，自动添加@限制返回单个结果。而FILTER是需返回多结果的函数，必须以数组公式形式存在才能正常工作，通过上述方式将公式标记为数组公式，即可避免@符号自动添加。

内容的提问来源于stack exchange，提问作者python_noob