Hallo Leute,
ich habe mal wieder ein kleines Problem:
Ich möchte einen Max-Wert suchen wobei allerdings der Suchbereich nach unten begrenzt ist (also NICHT die komplette Spalte) und zwar suchen soll er ab der Zeile wo die Formel drin steht (im Beispiel unten Zeile1) bis zu einer bestimmten Begrenzung (hier 829 in Spalte A in Zeile 4). Die Werte wo der Max-Wert herausgezogen werden soll stehen in Spalte B. Die Begrenzung setzt da ein wo ein bestimmter Zeilenwert in Spalte A einen gewissen Wert hat (hier Wert 829, Spalte A).
Beispiel: (Spalten A,B,C) / Zeilen 1,2,3,4
ZEILE--A----B---C
----1---325--5
----2---440--4
----3---614--7
----4---829--2
----5---126--8
Spalte A gibt die Begrenzung wieder bis wo der Max-Wert der Spalte B gesucht werden soll (829).
Spalte B gibt die Werte wieder wo der Max-Wert herausgezogen werden soll (BIS 829 in Spalte A), also Zeile B1 bis Zeile B4 in diesem Beispiel da hier die 829 drin steht und das die Begrenzung sein soll.
In Spalte C1 steht dann der gesuchte Wert und zwar von B1 (5), B2 (4), B3 (7), B4 (2). B5 (8) darf nicht mehr in die Max-Werte mit einbezogen werden, da er unter der 829 liegt und bei 829 soll ja die Begrenzung liegen.
ZEILE--A----B---C
----1---325--5---7
----2---440--4
----3---614--7
----4---829--2
----5---126--8
In C1 müsste also 7 als höchster Werte stehen, da von B1-B4 ja 7 der höchste Wert ist, da dort in Spalte A 829 (also Zeile 4) steht und bis dahin soll ja der Max-Wert gesucht werden. Kann man das irgendwie mit Sverweis oder Index etc. lösen?
Also wenn in Spalte A 829 steht, dann suche den Max-Wert heraus von B1 bis Spalte A wo 829 steht (in dem Fall hier B4).
Ich habe dies über etliche wenn-Funktionen "gelöst", aber da gibt es doch bestimmt etwas Eleganteres.
Hier die Wenn-Funktion für C1:
=WENN(A4=829;MAX(B1:B4);"")
Da diese für C2 nicht mehr funktioniert (mit copy und paste) habe ich diese in C1 implementiert und kann diese nun runterkopieren in C2:
In C1 steht nun:
=WENN(A4=829;MAX(B1:B4);WENN(A3=829;MAX(B1:B3);""))
Wenn man diese Formel nun in C2 kopiert, dann funktioniert das auch für C2.<br>
Da dies aber wieder nicht in C3 funktioniert habe ich das wieder in C1 implementieren müssen für C3 und kann diese nun runterkopieren bis C3:
in C1 steht nun:<br><br>
=WENN(A4=829;MAX(B1:B4);WENN(A3=829;MAX(B1:B3);WENN(A2=829;MAX(B1:B2);"")))
Somit funktioniert die Ausgabe auch in C3.
Es wird immer der Wert 7 ausgegeben von C1 bis C3, was korrekt ist, da Max-Wert, aber Ihr seht schon, dass z.B. bei 20 Werten (z.B. B1 - B20 Max-Wert rausziehen und da würde dann 829 drin stehen, also in A20) das eine Schar von Wenn-Funktionen nach sich ziehen würde (Hier im Beispiel sind es ja nur 4 von B1 bis B4). Es würde also entsprechend lang werden mit 20 Wenn-Funktionen. Gibt es da eine elegantere Lösung?
Das I-Tüpfelchen wäre noch, da es mehrere 829 gibt in Spalte A, dass der Max-Wert nur bis zum nächsten 829 genommen wird (im Beipiel A4). Steht zum Beispiel in A6 wieder 829 und in B6 z.B. 9 wäre der Max-Wert zwar höher, darf aber nicht als Max-Wert gewertet werden, da ja 829 in A4 steht und da soll die Begrenzung sein, beim "ersten" 829 und wenn ich die Formel z.B. in C1 bis C4 habe.
Ab C5 müsste dann entsprechend die Begrenzung A6 gelten, da C5 ja unter C4 steht (und entsprechend unter A4 wo die alte 829 drin steht). Noch ein Beispiel dazu:
Ab C5 würde dann die nächste Begrenzung A6 mit 829 gelten und der Höchstwert von 9.
Hier nochmal die Werte wie es aussehen soll:
ZEILE--A----B---C
----1---325--5---7
----2---440--4---7
----3---614--7---7
----4---829--2---7 (Begrenzung erstes 829 in A4)
----5---126--8---9 (Maxwert B6 gilt ab hier für Formel in C5, da Formel unterhalb des letzten 829 in Spalte A4)
----6---829--9---9
Vielen Dank für Eure Mühe!