Office: Wert in einer Matrix Suchen und Spalte zurückgeben, mehrere Max

Excel 365

Hallo zusammen,
ich melde mich auf diesen Thread hin, weil ich ein ziemlich ähnliches Problem habe, es aber nicht hinbekomme.
(Office 2007) - Wert in einer Matrix Suchen und Spalte/Überschrift zurückgeben (Excel 2003)
Ich möchte in meinem (noch unfertigen) Tabellenblatt Tmax in C59 die Spaltenüberschrift (Zeile 3) des Maximums aus der Teilmatrix B4:K44 haben, in D59 die Zeilenüberschrift dieses Maximums (Spalte A).
Die oben als erstes genannte Formel (angepasst auf meine Tabelle) liefert mir die Fehlermeldung #Bezug! bzw. #NV.

Nun kann es aber sein, dass dieses Maximum mehrfach vorkommt, also will ich auch die Überschriften der weiteren Maxima in C60:D62 aufführen. Geht das überhaupt mit dem Summenprodukt oder müssen da Hilfsspalten eingefügt werden, was hier viel zu umständlich wäre?
Wäre dankbar für eure Antwort.
VG Tim

 

Hola,
in C59:
=WENNFEHLER(INDEX($B$3:$K$3;AGGREGAT(15;6;SPALTE($B$3:$K$3)-1/($B$4:$K$44=KGRÖSSTE($B$4:$K$44;1));ZEILE(A1)));"")
Die Formel dann runterziehen.
Gruß,
steve1da

 

Vielen Dank, das ist genial.
Leider vestehe ich die Formel nicht gut genug (z. B. warum Funktion 15 =kkleinste), um sie für D59 anzupassen. Dort soll ja die Zeilenüberschrift des betreffenden Wertes hin. Ich hatte an
=WENNFEHLER(INDEX($A$4:$A$44;AGGREGAT(15;6;ZEILE($A$4:$A$44)-1/($B$4:$K$44=KGRÖSSTE($B$4:$K$44;1));SPALTE(A1)));"") gedacht (einfach mal "Zeile" und "Spalte" vertauscht und die Bezüge angepasst. Das klappt aber nicht.

 

Das wäre dann:
=WENNFEHLER(INDEX($A$4:$A$44;AGGREGAT(15;6;ZEILE($A$4:$A$44)-1/($B$4:$K$44=KGRÖSSTE($B$4:$K$44;1));ZEILE(A1)));"")

 

Ich habe die Formel mal auf -3 angepasst, statt -1, weil bei mir ja die Daten erst in Zeile 4 beginnen. Dann werden zwar die richtigen Jahreszahlen geliefert, aber nicht in der richtigen Reihenfolge, d. h. Datum und Jahr passen nicht zusammen, sondern sind verschoben. Ich könnte zwar in D59 mit A4 beginnen und dann in D60 mit A1 usw. weitermachen, das stimmt ja aber nur in diesem Beispiel so. Es sollen ja noch alle anderen Dekaden berechnet werden und dann käme ja wieder was anderes raus. Bekommt man das auch noch in, auch wenn weniger Maxima vorhanden sind?
Ich habe mal die neuste Version angehängt.

 

Hallo,
weiß jemand, wie man die Datumswerte zu den passenden Jahreszahlen bekommt, also in die gleiche Reihenfolge bringt, so dass es passt?
VG
Tim

 

Hallo Tim,

ja, das kann ich Dir aufzeigen. Dazu bedarf es einer zusätzlichen Verknüpfung mit den Ergebniswerten für die Tage. Doch die bisherigen Formeln lassen sich zuvor verkürzen.

Die Formel für die Tage, in C59:
=WENNFEHLER(AGGREGAT(15;6;B$3:K$3/($B$4:$K$44=MAX(B$4:K$44));ZEILE(A1));"")
und diese nach unten ziehend kopieren.

Für die zugeordneten Jahre in D59 folgende Formel:
=WENNFEHLER(AGGREGAT(15;6;A$4:A$44/(B$4:K$44=MAX(B$4:K$44))/(B$3:K$3=C59);ZÄHLENWENN(C$59:C59;C59));"")
(Der ZÄHLENWENN()-Formelteil ist notwendig, weil es ja auch möglich sein kann dass am gleichen Tag in unterschiedlichen Jahren auch der MAX-Wert auftreten kann)

Mit diesen Formeln sind die MAX-Datenwerte nach Tag sortiert. Ist das so gewollt? Wenn als Ergebnislistung eine Sortierung unter Berücksichtigung von Jahr und Tag erfolgen soll, muss noch eine Umstellung der Formeldefinition noch vorgenommen werden. Wenn Deine Dekaden-Auswertung immer exakt 10 Tage erfassen soll (also Monats übergreifend), dann könnte man die Formeldefinition auch noch entsprechend anpassen, dass man die Formeln nicht nur nach unten sondern auch nach rechts ziehend kopieren kann.

 

@neopa C
Sorry Werner, dass ich mich nicht zeitnah melden konnte, dies ging aus persönlichen Gründen leider nicht. Vielen Dank, die Formel passt so und liefert mir genau das, was ich wollte. Die Sortierung nach Jahr und Tag wäre zwar nice to have, muss aber nicht sein. Übergreifend sind die Dekaden nicht, die letzte hat ggf. sogar 11 Tage. Das geht also nicht mit dem nach rechts ziehen.

Nun habe ich aber versucht, die Formel auf das gleiche Problem nur mit Minimum statt Maximum anzupassen. Dachte, das wäre ganz einfach, ist es aber nicht, weil ich die Funktion AGGREGAT nicht verstanden habe. Aber so große Änderungen können es doch nicht sein, oder?
Danke vorab.
Tim

 

Hallo Tim,

ohne es mir jetzt nochmal näher anzusehen: Ändere einfach das 1. Argument von einer 15 in eine 14.

 

Hallo Werner,
danke, das hatte ich schon erfolglos versucht. Hab´s jetzt aber gefunden. Die 15 muss stehen bleiben und doch nur MAX gegen MIN ausgetauscht werden. Hatte noch einen anderen Fehler in der Formel, deshalb hatte es nicht geklappt.
VG Tim

 

Hallo Tim,

sorry. Natürlich, ich hätte mir doch nochmal wenigstens meine Formeln ansehen sollen, dann hätte ich gleich festgestellt, daß Deine Zusatzfrage nicht nur die AGGREGAT()-Funktion betrifft sondern die gesamte Formel.

 

Nun habe ich aber leider festgestellt, dass die Suche nach dem Minimum sehr gut funktioniert, solange es negative Werte als Minimum gibt. Gibt es aber nur Werte >=0 funktioniert es nicht mehr, weil KKLEINSTE leere Zellen als 0 interpretiert (Ist das eigentlich ein Bug oder so gewollt?). Hier: ab DW69
Ich könnte natürlich den Suchbereicht so verkleinern, dass nur die belegten Zellen durchsucht werden, aber dann müsste ich jedes Jahr die Formeln für alle 36 Dekaden anpassen. Ist ja eine Höllenarbeit.
Muss man das jetzt kompliziert programmieren und auch die AGGREGAT Funktion anpassen oder gibt es eine einfache Möglichkeit?
VG Tim

 

Hola,
=MINWENNS(DS4:EB41;DS4:EB41;"<>0")
Gruß,
steve1da

 

Hallo,
das hilft nicht wirklich weiter. Zum einen kann ich mit MINWENNS nur das absolute Minimum und nicht auch den zweit- oder drittkleinsten Wert erhalten. Außerdem ist <>0 nicht hilfreich, weil es ja auch "echte" Nullen gibt. Also wenn der Wert 0 ist und nicht "leer". Mit "<>""" müsste das zwar funktionieren, aber die AGGREGAT-Funktion liefert dann auch falsche Werte.
Gruß
Tim

 

Ungetestet:
=KKLEINSTE(WENN(DS4:EB41<>"";DS4:EB41);Zeile(A1))