VBA MIN/MAX mit Bedingung

Forumthread: VBA MIN/MAX mit Bedingung

26.09.2018 16:26:57

Jack

Hallo Zusammen,
ich würde gerne eine Min und Max Formel als VBA umsetzen, leider finde ich dazu nichts was mir konkret weiterhilft.
SPALTEN
(1) In J:J stehen die Begriffe
Max Mustermann
Johann Lichter
Max Mustermann
Frank Schuster
Frank Schuster
Julia Siegel
(2) In J steht der Suchbegriff in der jeweiligen Zeile
Max Mustermann
(3) In S:S stehen die Datumswerte
01.06.2018
05.10.2018
04.04.2018
16.06.2018
31.12.2018
04.09.2018
(4) In T:T soll der jeweilige Min Wert zum Suchbegriff der Zeile ausgegeben werden z.B. J2 = Max Mustermann, dann = 04.04.2018 (nicht 01.06.)
(5) In U:U soll der jeweilige Max Wert zum Suchbegriff der Zeile ausgegeben werden z.B. J2 = Max Mustermann, dann = 01.06.2018 (nicht 04.04.)
FORMEL
=MIN(WENN((($J:$J=$J2));$S:$S))
Das VBA soll den durch die Formel berechneten Wert fix berechnet einsetzen. Es sollen auch mehrere Bedinungen möglich sein z.B. =MIN(WENN((($J:$J=$J2)*($K:$K=$K2));$S:$S))

BISHERIGE FORMEL Leider aber ohne Bedingungen
For aZ = 2 To Sheets("Output").Range("A" & Rows.Count).End(xlUp).Row
If Worksheets("Output").Cells(aZ, 1) = "" Then
Worksheets("Output").Cells(aZ, 3) = ""
Else: Worksheets("Output").Cells(aZ, 3) = Application.WorksheetFunction.Min(Sheets("Output").Range("S:S"))
End If
Next
Danke im Voraus &
beste Grüße,
Jack

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: VBA MIN/MAX mit Bedingung

26.09.2018 17:03:25

Daniel
AW: VBA MIN/MAX mit Bedingung

26.09.2018 18:09:45

Jack
AW: VBA MIN/MAX mit Bedingung

26.09.2018 19:03:49

Daniel

AW: VBA MIN/MAX mit Bedingung

26.09.2018 17:03:25

Daniel

Hi
am besten ist hier wahrscheinlich, die Formeln in die Zellen zu schreiben und dann die Formeln durch die Werte zu ersetzen.
Da es Matrixformeln sind (Eingabe in die Zelle mit STRG+SHIFT+ENTER), müsstest du mit .FormulaArray die Formeln in die Zellen schreiben.
Oder du baust das ganze auf Formeln mit Aggregat um, denn diese kannst du normal mit .Formula oder .FormulaR1C1 in die Zellen schreiben.
Weiterhin ist es empfehlenswert, dann hier nicht mit ganzen Spalten als Zellbezug zu arbeiten, sondern den Zellbereich genau anzugeben, weil Excel in Matrixformeln (auch mit Aggregat) immer alle angegebnen Zellen durchrechnet, das kann dann dauern.
Code könnte so aussehen, ist jetzt aber mangels Beispieldatei nicht getestet.

Sub test()
Dim FO As String
Dim LZ As Long
FO = "=IF(J2="""","""",Aggregate(xx,15,S2:Szzz/((J2:Jzzz=J2)*(K2:Kzzz=K2)),1))"
With Sheets("Output")
LZ = .Cells.SpecialCells(xlCellTypeLastCell).Row
FO = Replace(FO, "zzz", LZ)
With .Range("T2:U" & LZ)
.Columns(1).Formula = Replace(FO, "xx", "15")
.Columns(2).Formula = Replace(FO, "xx", "14")
.Formula = .Value
End With
End With
End Sub

gruß Daniel

AW: VBA MIN/MAX mit Bedingung

26.09.2018 18:09:45

Jack

Hallo Daniel,
danke für die Hilfe. Das hat mich schon mal weiter gebracht.
Ich scheiter noch an Folgendem. Aktuelle VBA

Sub test()
Dim FO As String
Dim LZ As Long
'FO = "=MIN(IF(((C[-10]=RC[-10])),C[-1]))"
Sheets("Tabelle1").Range("t2:t" & "100" + 1).FormulaArray = "=MIN(IF(((C[-10]=RC[-10])),C[-1]))" _
FP = "=max(IF(((C[-11]=RC[-11])),C[-2]))"
'FO = "=IF(J2="""","""",Aggregate(xx,15,S2:Szzz/((J2:Jzzz=J2)*(J2:Jzzz=J2)),1"
With Sheets("Tabelle1")
LZ = .Cells.SpecialCells(xlCellTypeLastCell).Row
'FO = Replace(FO, "zzz", LZ)
With .Range("T2:U" & LZ)
'.Columns(1).FormulaArray = Replace(FO, "xx", "15")
.Columns(2).FormulaArray = Replace(FP, "xx", "14")
'.Formula = .Value
End With
End With
End Sub

Sobald ich jetzt das Array einsetze, gibt er mir zwar den Wert mit {} aus, allerdings setzt er ab diesem Zeitpunkt für jede Zelle die selbe Formel, statt die Zeilen+1 zu setzen.
Mit Array:
T2 = =MIN(WENN(((J:J=J2));S:S))
T3 = =MIN(WENN(((J:J=J2));S:S))
Ohne Array:
T2 = =MIN(WENN(((J:J=J2));S:S))
T3 = =MIN(WENN(((J:J=J3));S:S))
Wenn ich das gelöst habe, klappt es soweit mit deinem Vorgehen, muss dann nur mal die Performance prüfen.
Danke & beste Grüße,
Jack

AW: VBA MIN/MAX mit Bedingung

26.09.2018 19:03:49

Daniel

Hi
Wenn eine Matrixformel in mehrere Zellen schreiben willst, dann musst du die Formel zuerst in EINE Zelle schreiben und dann per Copy/Paste in die anderen Zellen übertragen.
Fügst du die Matrixformel in mehrere Zellen gleichzeitig ein, so bekommst du nicht viele Matrixformeln, sondern eine einzige, bei der Excel dann davon ausgehen, dass es eine Plurale Matrixformel mmitmehreren Ergebnissen ist, welche dann in die markierten Zellen geschrieben werden müssen.
Sowas hast du aber nicht, du hast eine singuläre Matrixformel mit einem Ergebnis.
Gruß Daniel

Schritt-für-Schritt-Anleitung

Vorbereitung: Stelle sicher, dass deine Daten in den entsprechenden Spalten (J für Begriffe, S für Datumswerte) korrekt eingetragen sind.
VBA-Editor öffnen: Drücke ALT + F11, um den VBA-Editor zu öffnen.
Modul erstellen: Klicke mit der rechten Maustaste auf VBAProject, wähle Einfügen und dann Modul.

Code einfügen: Kopiere den folgenden Code in das Modul:

Sub BerechneMinMax()
   Dim FO As String
   Dim LZ As Long
   FO = "=MIN(WENN((J:J=J2),S:S))"
   FP = "=MAX(WENN((J:J=J2),S:S))"

   With Sheets("Output")
       LZ = .Cells.SpecialCells(xlCellTypeLastCell).Row
       .Range("T2:T" & LZ).FormulaArray = FO
       .Range("U2:U" & LZ).FormulaArray = FP
   End With
End Sub

Formel anpassen: Du kannst die Formel je nach Bedarf anpassen, um weitere Bedingungen hinzuzufügen, z.B. =MIN(WENN((J:J=J2)*(K:K=K2),S:S)).
Makro ausführen: Schließe den VBA-Editor und führe das Makro aus, um die MIN/MAX-Werte zu berechnen.

Häufige Fehler und Lösungen

Fehler: Falsche Werte werden angezeigt
Lösung: Stelle sicher, dass die Zellreferenzen korrekt sind. Prüfe auch, ob die Daten in Spalte S im Datumsformat vorliegen.
Fehler: Nur eine Formel in mehreren Zellen
Lösung: Füge die Formel zuerst in eine Zelle ein und kopiere sie dann in die anderen Zellen, um die Matrixformel korrekt zu übertragen.

Alternative Methoden

Einige Anwender bevorzugen es, die Formeln direkt in die Zellen einzutragen und dann die Werte zu ersetzen. Eine Methode ist die Verwendung der AGGREGAT-Funktion:

FO = "=IF(J2="""","""",AGGREGATE(15,6,S:S/((J:J=J2)*(K:K=K2)),1))"

Diese Funktion kann auch ohne den VBA-Editor direkt in Excel verwendet werden, was flexibler ist.

Praktische Beispiele

Angenommen, du hast folgende Daten:

J (Begriffe)	S (Datumswerte)
Max Mustermann	01.06.2018
Johann Lichter	05.10.2018
Max Mustermann	04.04.2018
Frank Schuster	16.06.2018
Julia Siegel	31.12.2018

Wenn du Max Mustermann in Zelle J2 hast, zeigt die Formel in T2 den Wert 04.04.2018 und in U2 01.06.2018.

Tipps für Profis

Leistungsoptimierung: Vermeide die Verwendung von ganzen Spalten in deinen Formeln, da dies die Leistung beeinträchtigen kann. Beschränke den Zellbereich auf die benötigten Zeilen.
Debugging: Nutze Debug.Print im VBA-Code, um Werte während der Ausführung zu überprüfen und Fehler leichter zu identifizieren.
Formeln festlegen: Denke daran, die berechneten Werte zu fixieren, falls du die Formeln nicht mehr benötigst.

FAQ: Häufige Fragen

1. Was ist der Unterschied zwischen Application.WorksheetFunction.Min und WorksheetFunction.Min?
Application.WorksheetFunction.Min ist eine Möglichkeit, auf die Min-Funktion über das Application-Objekt zuzugreifen, während WorksheetFunction.Min direkt verwendet wird. Beide haben ähnliche Funktionen.

2. Kann ich mehrere Bedingungen in der MIN/MAX-Funktion einfügen?
Ja, du kannst Bedingungen kombinieren, indem du sie mit Multiplikation (), wie in `=MIN(WENN((J:J=J2)(K:K=K2),S:S))`, verbindest.

3. Funktioniert das auch in älteren Excel-Versionen?
Ja, die vorgestellten Methoden sind in den meisten Excel-Versionen anwendbar, jedoch kann die Performance variieren, insbesondere bei großen Datenmengen.

Beliebteste Forumthreads (12 Monate)

VBA MIN/MAX mit Bedingung

Forumthread: VBA MIN/MAX mit Bedingung

3 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial