arrays - VBA 中的数组公式错误

Question

 A      B    C   D  
east    1   56
west    5   98
east    1   78
west    5   99
south   3   23
east    2   45
south   3   67

我想获得 A+B 连接的相同组合的最大值。例如，对于 east1，我应该在 D 列中得到 78。为此，我使用 VBA，但代码似乎不起作用。我正在使用数组公式。

我的代码是:

.Range("D2:D" & OutputLastRow).FormulaArray = "=MAX(("$A$2:$A$" & OutputLastRow=A2)*("$B$2:$B$" & OutputLastRow=B2)*("$C$2:$C$" & OutputLastRow))"

代码运行时没有任何错误，但结果是错误的，因为当公式从 D2 向下移动时，单元格 A2 和 B2 没有得到更新。 A2 仍为 A2，B2 仍为 B2。我已经尝试过使用循环，但这也不起作用。

Answer 1

在多单元格区域上使用 .FormulaArray 时，这与将数组公式放入第一个单元格并自动填充不同。您应该分两步明确地执行此操作。经过一些更正后，您的代码应该如下所示:

' 1- Enter the array formula in top cell
Range("D2").FormulaArray = _
  "=MAX(($A$2:$A$" & OutputLastRow & "=A2)*($B$2:$B$" & OutputLastRow & _
  "=B2)*($C$2:$C$" & OutputLastRow & "))"

' 2- Then autofill down the column
Range("D2:D" & OutputLastRow).FillDown 

或者，您可以使用 AGGREGATE 函数创建一个与您的公式完全相同的“正常”公式:

Range("D2:D" & OutputLastRow).Formula = _
  "=Aggregate(14, 6, ($A$2:$A$" & OutputLastRow & "=A2)*($B$2:$B$" & OutputLastRow & _
  "=B2)*($C$2:$C$" & OutputLastRow & "), 1)"