Matrixformel mit relativen Bezügen
22.10.2017 15:01:49
Frank
ich habe eine Excel-Datei, deren Arbeitsmappen ich mittels VBA aus einer CSV-Datei fülle. Die Menge an Datensätzen ist immer unterschiedlich. Unterhalb der Datentabelle soll dann jeweils eine kleine Tabelle mit einer Statistik erzeugt werden. Damit sie immer korrekt positioniert ist, habe ich mit relativen Bezügen gearbeitet:
' letzte genutzte Zeile der Tabelle ermitteln - dient als Ankerpunkt für alle weiteren Aktionen
LR = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
' darauf aufbauend die erste Zeile für die Tabelle festlegen, um anschließend mit Offset anstatt konkreter Werte zu arbeiten
FR = LR + 2
' erste Spalte für die Tabelle festlegen, um anschließend mit Offset anstatt konkreter Werte arbeiten zu können
FC = 2
Die statischen Zellen fülle ich dann so:
Cells(FR, FC + 2).Value = "negative min"
Cells(FR, FC + 3).Value = "negative median"
Cells(FR, FC + 4).Value = "negative max"
...
Der Screenshot zeigt, wie das Ganze aussieht:
Jetzt müssen noch die Formeln eingetragen werden, wo im Moment die Platzhalter "Wert ..." stehen. Für die erste Tabelle, die hier als Muster dient, reicht der Datenbereich von Zeile 2 bis 64. Ich habe eine Arrayformel für die Zelle D67 erstellt:
=MIN(WENN(F2:F64<0;F2:F64))
Sie berücksichtigt nur die negativen Werte in Spalte F und ermittelt das Minimum, hier -10,20%. In anderen Zellen folgen dann Formeln für neg. Median und neg. Maximum plus die Pendants für die positiven Werte, wie man im Screenshot sieht.
Um sie per VBA in die entsprechende Zelle zu bringen, nutze ich dieses Statement:
Cells(FR + 1, FC + 2).FormulaArray = "=MIN(IF(R[-65]C[2]:R[-3]C[2]<0,R[-65]C[2]:R[-3]C[2]))"
Für die zweite Zelle D68, deren Formel im Arbeitsblatt so aussieht:
=MIN(WENN(N2:N64<0;N2:N64)) lautet die VBA-Version so:
Cells(FR + 2, FC + 2).FormulaArray = "=MIN(IF(R[-66]C[10]:R[-4]C[10]<0,R[-66]C[10]:R[-4]C[10]))"
(im Screenshot steht hier noch "Wert 2A")
Das funktioniert, aber ich habe vier Fragen:
- Ich kapiere den Aufbau der aufgezeichneten Array-Formel noch nicht. Wie sind dort Anfang und Ende und die Spalte übersetzt worden? Ich möchte es ungern immer aufzeichnen, sondern direkt in VBA schreiben können.
- Was soll das mit dem negativen Vorzeichen?
- Wie muss ich vorgehen, um auf die jeweils unterschiedliche Menge an Datensätzen zu reagieren? Die Zeile 2 als Beginn bleibt immer gleich, das Ende verschiebt sich. Wie kann ich die Variable "LR", mit der ich die letzte Datenzeile ermittele, einbeziehen?
Vielen Dank für Eure Unterstützung!
Viele Grüße
Frank