Excel VBA中FormulaArray调用LARGE(IF())函数报错问题咨询

嘿，我看到你因为用FormulaArray调用LARGE数组公式踩坑，就手动逐个单元格写FormulaLocal来绕开问题——确实能解决，但重复代码太繁琐了，我来给你优化下，顺便把FormulaArray的问题也搞定～

1. 用循环告别重复代码 #

直接用For循环批量生成公式，不管你要扩展到多少行都很方便，再也不用复制粘贴重复代码：

Dim i As Integer
' 循环3次对应J314-J316，分别取第1到第3大的值
For i = 1 To 3
    Sheets("OEVK").Range("J" & 313 + i).FormulaLocal = "=LARGER(IF(jelolt_lista!$C:$C=OEVK!B" & 313 + i & ";jelolt_lista!$M:$M);" & i & ")"
Next i

后续要加J317的话，只要把循环上限改成4就行，非常灵活。

2. 搞定FormulaArray的原始问题 #

你之前用FormulaArray出问题，大概率是没注意它的语法要求：

• FormulaArray必须用英文语法：函数名是LARGE（不是你用的LARGER，这是本地语言的函数名），参数分隔用逗号,而不是分号;
• 用FormulaArray设置后，VBA会自动帮你加上数组公式的标识，不用手动按Ctrl+Shift+Enter

单个单元格的正确写法：

Sheets("OEVK").Range("J314").FormulaArray = "=LARGE(IF(jelolt_lista!$C:$C=OEVK!B314,jelolt_lista!$M:$M),1)"

批量设置同样用循环：

Dim i As Integer
For i = 1 To 3
    Sheets("OEVK").Range("J" & 313 + i).FormulaArray = "=LARGE(IF(jelolt_lista!$C:$C=OEVK!B" & 313 + i & ",jelolt_lista!$M:$M)," & i & ")"
Next i

如果你的Excel是Office 365/2021及以上版本，推荐用动态数组函数FILTER替代IF数组，公式更简洁，还不需要数组公式：

Sheets("OEVK").Range("J314").FormulaLocal = "=LARGER(FILTER(jelolt_lista!$M:$M;jelolt_lista!$C:$C=OEVK!B314);1)"

这个写法更直观，计算效率也更高。

3. 小Tips提升公式性能 #

• 尽量不要用整列引用（$C:$C、$M:$M），改成实际的数据范围，比如jelolt_lista!$C$2:$C$1000，这样Excel不用遍历整列，计算速度更快
• 如果公式结构一致，也可以先设置好第一个单元格的公式，再用FillDown批量填充：

  Sheets("OEVK").Range("J314").FormulaLocal = "=LARGER(IF(jelolt_lista!$C:$C=OEVK!B314;jelolt_lista!$M:$M);ROW(A1))"
  Sheets("OEVK").Range("J314:J316").FillDown
  

  这里用ROW(A1)自动生成1、2、3，填充时会自动变成ROW(A2)、ROW(A3)，对应第2、3大的值。

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