arrays - vba陣列教學 - vba dynamic array



數組公式在VBA中出錯 (2)

你真的不需要VBA這個。 在D1輸入下面的數組公式(使用Ctrl + Shift + Enter而不是Enter),並將其向下拖動

=MAX(IF(A1&B1=$A$1:$A$7&$B$1:$B$7,$C$1:$C$7))

你的輸出應該是這樣的:

因為MAX和MIN忽略A1&B1 = $A$1:$A$7&$B$1:$B$7的FALSE值,並且只對剩餘結果進行操作,所以公式工作。 數組公式使得A1&B1$A$1:$A$7&$B$1:$B$7的“當前行”值相比較。 我想你可以像這樣鎖定$A1&$B1列,但在你的例子中,這是沒有必要的。

https://src-bin.com

 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))"

代碼運行沒有任何錯誤,但結果出來是錯誤的,因為單元格A2和B2沒有得到更新,因為公式從D2向下移動。 A2和B2一樣保持為A2和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)"




excel-formula