为什么openpyxl总是将“普通”公式更改为数组公式

Question

我有一张表，上面有下面的公式

=SUMPRODUCT((IFERROR(INDEX(Tabela2[start],MATCH(((Tabela1[type]=J3) *  Tabela1[id]),Tabela2[id],0))>=$I$1;0)*1))

它只计算在特定日期之后发生的特定类型的条目数

但是，当我运行使用openpyxl更新主表的脚本时，它在前面的公式中添加了花括号，这使得它返回了错误的值。我可以通过正常输入来修复它，而无需control+shift+center，但每次更新它时都要修复它，这有点烦人

我还制作了下面的脚本，它只打开和保存文件，还向公式中添加了花括号。有人能告诉我我做错了什么吗

from openpyxl import load_workbook

ficheiro = 'Livro1.xlsx'
workbook = load_workbook(filename=ficheiro)
workbook.save(filename="test1 -test.xlsx")

Answer 1

“…它在公式中添加了花括号。有人能告诉我我做错了什么吗？”

它是Excel的动态数组功能，添加了Office 365兼容性和某些跨平台更新。它假定公式是一个数组，并强制使用大括号（“{”，“}”），除非使用隐式相交运算符（“@”）。缺点是旧版本的Excel无法识别任何一个运算符，并且会导致VBA代码中的字符串变量（查找、解析、筛选、连接“&；”）崩溃

没有办法阻止excel这样做，但这可能是一个有用的解决方法

在执行VBA时插入“@”运算符以强制关闭动态数组函数后，此代码将删除隐式交集。这将循环浏览每个工作表并返回到原始工作表。注释出公式列表，以适合造成困难的公式的特定用途。第二个宏通过测试是否识别较新的数组结构，根据VBA版本更改使用的替换函数的类型。诀窍是使用预编译标识符（#）来处理更新的代码，这些代码是retro编辑器不允许的，但如果没有这些代码，更新的公式将无法工作

Sub DelOperand

Scheat = ActiveSheet.Name
Sheets(1).Select
For Count = 1 To Worksheets.Count

   'Remove forced special character'
    Run FormulaFix("Cell")
    Run FormulaFix("Column")
    Run FormulaFix("FilterXML")
    Run FormulaFix("FormulaText")
   'Run FormulaFix("Frequency")'
   'Run FormulaFix("Growth")'
    Run FormulaFix("Hyperlink")
    Run FormulaFix("If")
    Run FormulaFix("Index")
    Run FormulaFix("Indirect")
    Run FormulaFix("IsFormula")
   'Run FormulaFix("LinEst")'
   'Run FormulaFix("LogEst")'
   'Run FormulaFix("MInverse")'
   'Run FormulaFix("MMult")'
   'Run FormulaFix("Mode.MULT")'
   'Run FormulaFix("Munit")'
    Run FormulaFix("Offset")
    Run FormulaFix("Row")
    Run FormulaFix("Transpose")
   'Run FormulaFix("Trend")'

    If Count = Worksheets.Count Then Exit For
    ActiveSheet.Next.Select
Next

Sheets(Scheat).Select
Err.Clear

End Sub


Function FormulaFix(ByRef Whatzit)
'Credit: Stephen L. Rush'

Dim WhatNow
Dim ErrNum As Integer

On Error Resume Next

WhatNow = "@" & Whatzit

If Application.Evaluate("=XLOOKUP(1,{1,2,3},{3,2,1})") Then
   #If Not Err = 0 Then
    Cells.Replace What:=WhatNow, Replacement:=Whatzit, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
   #Else
    Cells.Replace What:=WhatNow, Replacement:=Whatzit, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   #End If
End If
Next

On Error GoTo 0

End Function