arrays - erweitern - vba dim bedeutung



Fehler bei der Array-Formel in VBA (2)

Sie brauchen dafür nicht unbedingt VBA. Geben Sie in D1 die folgende Array-Formel ein (mit Strg + Shift + Enter statt nur Enter) und ziehen Sie sie nach unten

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

Ihre Ausgabe sollte folgendermaßen aussehen:

Die Formel funktioniert, weil MAX und MIN die FALSE-Werte für A1&B1 = $A$1:$A$7&$B$1:$B$7 ignorieren und nur für die verbleibenden Ergebnisse gelten. Die Array-Formel macht es so, dass A1&B1 mit dem Wert "aktuelle Zeile" für $A$1:$A$7&$B$1:$B$7 verglichen wird $A$1:$A$7&$B$1:$B$7 . Ich denke, Sie könnten Spalten für $A1&$B1 so sperren, aber in Ihrem Beispiel war es nicht notwendig.

 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

Ich möchte den maximalen Wert der gleichen Kombination von A + B verkettet bekommen. Ex- für east1, sollte ich 78 in Spalte D bekommen. Dafür benutze ich VBA, aber der Code scheint nicht zu funktionieren. Ich verwende Array-Formel dafür.

Mein Code ist:

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

Der Code wird ohne Fehler ausgeführt, aber das Ergebnis ist falsch, da die Zelle A2 und B2 nicht aktualisiert werden, wenn die Formel von D2 nach unten verschoben wird. A2 bleibt als A2 und B2 als B2. Ich habe es mit der Schleife versucht, aber das funktioniert auch nicht.


Answer #1

Wenn Sie .FormulaArray für einen Bereich mit mehreren Zellen verwenden, ist das nicht so, als würde eine Array-Formel in die erste Zelle .FormulaArray und automatisch gefüllt. Sie sollten das lieber in zwei Schritten tun. Ihr Code sollte nach einigen Korrekturen wie folgt aussehen:

' 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 

Alternativ können Sie die AGGREGATE Funktion verwenden, um eine "normale" Formel zu AGGREGATE , die exakt Ihrer entspricht:

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