Max Wert in bestimmten Bereichen

Forumthread: Max Wert in bestimmten Bereichen

20.09.2022 14:42:36

Kai

Hallo zusammen,
ich bin in dem Thema Excel und VBA ein Rookie und versuche mir über Tutorials Wissen für meine Problemstellung anzueignen.
Leider habe ich aktuell für eine Thematik keinen Ansatz.
Ich möchte mit einem Makro folgendes ausführen:
In einer Spalte befinden sich diverse Zahlen, die immer durch ein #WERT! getrennt sind. Der Bereich zwischen den #WERT! soll immer als ein Bereich gewertet werden und der lokale höchste Zahlenwert soll ausgegeben werden. Anschließend sollen die gesamten Werte in einer neuen Zeile untereinander aufgelistet werden, ohne die Reihenfolge zu verändern.
Ich hatte erst den Ansatz, dass ich immer einen Vergleich mache von der aktuellen Zeile zur nächsten und dann den höchsten Wert farbig markiere. Das geht aber leider nicht, weil man auch nur mal eine 1 in einem Bereich stehen hat und die würde dann so nicht mit berücksicht werden.
Ich hoffe mir kann jemand helfen.
Vielen Dank im Voraus
Gruß Kai

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Max Wert in bestimmten Bereichen

20.09.2022 14:45:19

Kai

https://www.herber.de/bbs/user/155283.xlsx

AW: Max Wert in bestimmten Bereichen

21.09.2022 09:15:04

Yal

Hallo Kai,
wenn Du einen ZUsatz zu deiner Frage postet, musst Du den Haken bei "Frage noch offen" setzen. Sonst erscheint deine Frage nicht in der liste der offenen Fragen.
Ein nicht ganz einfache Fragestellung.
Erstens musst Du die "Bereiche" identifizieren: in C3, füge =ISTFEHLER(A3)*1+C2
und erweitere diese Formel nach unten: der kleine Quadrat unten rechts am Zellenumrandung anklicken und nach unten ziehen (sorry, keine Ahnung wie "kaum Kenntnisse" zu bewerten ist ;-)
Dann hast Du eine Reihe von 1 zwischen die erste Fehler und der letzte Zahl vor dem nächste Fehler, 2 danach, usw.
Dann könntest Du darauf eine Pivottabelle bauen (Menü Einfügen, Pivottable) und den Max pro Bereich auswerten.
Um das Ergebnis per Formel zu haben, fehlt eine Formel "MaxWenn". Man kann diese nachbauen, in dem man eine UDF (User Defined Function), ein Stück VBA-Code, das wie eine Excelformel im Blatt eingesetzt wird:


Public Function MaxWenn(ByVal Werte, ByVal Bereich, ByVal Abgleicher) As Variant
Dim Erg
Dim i
Dim Eingang, Referenz
On Error Resume Next
Eingang = Werte 'To Array
Referenz = Bereich
If Werte.Columns.Count = 1 Then
For i = LBound(Eingang, 1) To UBound(Eingang, 1)
If Referenz(i, 1) = Abgleicher Then If Eingang(i, 1) > Erg Then Erg = Eingang(i, 1)
Next
Else
For i = LBound(Eingang, 2) To UBound(Eingang, 2)
If Referenz(1, i) = Abgleicher Then If Eingang(1, i) > Erg Then Erg = Eingang(1, i)
Next
End If
MaxWenn = Erg
End Function

Dieses Coding ist in einem VBA-Modul abzulegen: VBA-Editor öffnen (Alt+F11), Modul einfügen (Einfügen, Modul), Code reinkopieren.
Dann wird in D4 die Formel
=MaxWenn($A$3:$A$22;$C$3:$C$22;C3)
gesetzt und nach unten erweitert.
Durch einen bedingte Formatierung wird die Zeile gefärbt:
_ Bereich A3:A22 markieren
_ Menü "Start", "Bedingte Formatierung", "Neue Regel..."
_ "Formel zur Ermittlung [..]"
_ Formel =($A3=$D3) eingeben
_ "Formatieren...", "Ausfüllen", Farbe auswählen
VG
Yal

MaxWenn, ein etwa leichtere Version

21.09.2022 09:23:08

Yal

Hallo Kai,
vielleicht nicht für Dich sondern für diejenigen, die auf diese Thread stossen werden.
Ich habe den MaxWenn in eine leichtere Version umgestellt:


Public Function MaxWenn(ByVal Werte, ByVal Bereich, ByVal Abgleicher) As Variant
Dim Erg, Ro, Co
On Error Resume Next
For Ro = 1 To Werte.Rows.Count
For Co = 1 To Werte.Columns.Count
If Bereich.Cells(Ro, Co) = Abgleicher Then If Werte.Cells(Ro, Co) > Erg Then Erg = Werte.Cells(Ro, Co).Value
Next
Next
MaxWenn = Erg
End Function

VG
Yal

AW: MaxWenn, ein etwa leichtere Version

21.09.2022 09:36:25

Kai

Hallo Yal,
ich werde es berücksichtigen. Vielen Dank für die Hilfe.
Gruß Kai

AW: MaxWenn, ein etwa leichtere Version

22.09.2022 09:49:43

Kai

Hallo Yal
besteht auch die Möglichkeit, dass für den ISTFEHLER und MAXWENN eine Formel zu nutzen, wo das immer bis zum Ende automatisch gezogen wird ?
Die Liste der Einträge wird varieren und hat keine feste Endzelle.
Gruß Kai

AW: MaxWenn, ein etwa leichtere Version

22.09.2022 18:00:38

Yal

Hallo Kai,
die Formel ISTFEHLER ist in dem Fall Zeilenbezogen.
=ISTFEHLER(A3)*1+C2
Du könntest im Voraus diese Formel bis Zeile 200 runterziehen. Sie wäre dann vorrätig da.
Dito für die Formel MaxWenn, aber mit einer Anpassung in der Bezug:
=MaxWenn($A$3:$A$200;$C$3:$C$200;C3)
Wenn die Länge deiner Liste immer anders ist, liegt es die Vermutung nah, dass diese zuerst importiert wird. Wenn diese aus einer Datei oder Datenbank kommt, könnte es sinnvoll diese Liste mit Power Query zu handeln.
Siehe https://excelhero.de/power-query/power-query-ganz-einfach-erklaert
Grüß aus dem Ländle, wo mir davo schwätzet
Yal

Beliebteste Forumthreads (12 Monate)

Forumthread: Max Wert in bestimmten Bereichen

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Max Wert in bestimmten Bereichen

Forumthread: Max Wert in bestimmten Bereichen

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread