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.