[gelöst] Zellbezug in einer Matrix berechnen

[peaty]

Hallo,
ich habe eine Matrix aus mehrern Spalten und Zeilen, in der ich die n größten Werte ausgeben will - soweit so gut, ist ja mit KGRÖSSTE oder AGGREGAT kein Problem.

Wo ich nicht weiter komme, ist wie ich den Zellbezug der größten Wertes ausgeben/berechnen kann. Habe schon mit den Funktionen INDEX und VERGLEICH rumprobiert, aber VERGLEICH geht nur mit einer Spalte / einer Zeile, aber nicht mit einer Matrix.
Kann mir jemand auf die Sprünge helfen, wie ich da am besten ran gehe?

Danke und Gruß
peaty

[karolus]

Hallo

Code: Alles auswählen

=INDEX(2_D_BEREICH ; VERGLEICH( senkrecht ) ; VERGLEICH( wagrecht ))

[peaty]

Code: Alles auswählen

=INDEX(2_D_BEREICH ; VERGLEICH( senkrecht ) ; VERGLEICH( wagrecht ))

Geanu da komme ich ja nicht weiter, da ich bei VERGLEICH nur eine einspaltige oder einzeilige Matrix angeben kann; VERGLEICH(I15;B4:M10;0) funktioniert leider nicht;
oder meinst du mit VERGLEICH( senkrecht ) bzw. VERGLEICH( wagrecht ) senkreicht was anderes?

[karolus]

Hallo

index_vergleich_vergleich.ods

(16.01 KiB) 128-mal heruntergeladen

[peaty]

Hallo Karolus,
deine Beispieldatei ist nicht das was ich brauche; ich hab mal eine Datei mit meinen Daten angehängt.

Auswertung.ods

(14.37 KiB) 111-mal heruntergeladen

In den Zellen I15 bis I24 habe ich die 10 größten Werte aus der Matrix B4:M9 ausgegeben; in den Zellen H15 bis H24 soll stehen wann diese Werte aufgetreten sind (z.B. Jun 2018, ...)

Gruß
peaty

[mikele]

Hallo,
das ist schon ein bisschen aufwändiger, aber mit etwas Matrizenmultiplikation machbar

Code: Alles auswählen

=DATUM(SUMME(MMULT(MTRANS($B$4:$M$9=I15);JAHR($A$4:$A$9)));SUMME(MMULT(($B$4:$M$9=I15);MTRANS(MONAT($B$3:$M$3))));1)

[peaty]

Hallo,
das ist schon ein bisschen aufwändiger, aber mit etwas Matrizenmultiplikation machbar

Vielen lieben Dank, da wäre ich allein nie drauf gekommen.

Deine Formel enthält mit MTRANS und MMULT gleich 2 Funktionen, die ich bisher noch nie benutzt haben und um ehrlich zu sein, so ganz verstehen tue ich die Formel auch nicht - aber sie funktioniert und dafür nochmal meinen herzlichsten Dank.

[mikele]

Hallo,
ein kurzer Versuch der Erläuterung: da die Verweis()-Funktionen stets nur einen einzeiligen bzw. einspaltigen Bereich zulassen, muss eine andere Lösung her.
Zunächst liefert :

Code: Alles auswählen

$B$4:$M$9=I15

liefert eine Matrix (6 Zeilen und 12 Spalten) die praktisch nur aus Nullen besteht und an der eine Stelle eine 1 hat.
Nun muss diese noch mit dem passen Zeilen-/Spaltenindex in verbindung gebracht werden. In deinem konkreten Fall wäre es der Monat (aus Zeile 3) bzw. das Jahr (aus Spalte A). Daraus kann man dann ein Datum bauen und im Format MMM JJJJ anzeigen lassen.
Nun gibt es die so genannte Matrizenmultiplikation, mit der man eine Matrix mit einer anderen Matrix multiplizieren kann. Dazu müssen "nur" die Zeilen und Spalten zueinander passen. Dahe rmuss ich z. B. aus der Zeile B3:M3 eine Spalte machen (mathematisch: die Zeile transponieren Mtrans()). Dass Ergebnis von

Code: Alles auswählen

MMULT(($B$4:$M$9=I15);MTRANS(MONAT($B$3:$M$3)))

ist dann eine Spalte von 6 Zeilen, die aus lauter Nullen besteht und nur an einer Stelle den Monat liefert. Die Summe aller dieser Werte ist dann natürlich auch der Monat.
Praktisch das Gleiche mache ich mit dem Jahr.
Das Verfahren geht schief, wenn ein gesuchter Wert mehrmals in der Liste auftaucht!!!
Mann könnte auch folgendes machen:

Code: Alles auswählen

=($B$4:$M$9=I15)*(ZEILE($B$4:$M$9)*100+SPALTE($B$4:$M$9))

würde (als Matrixformel mit Shift+Strg+Enter abgeschlossen) eine 6x12 Matrix liefern, die überall Nullen liefert und nur dort, wo der gesuchte Wert steht eine Zahl (in dem konkreten Fall die 407) deren Hunderter die Zeile (hier 4) und der Rest die Spalte (hier 7, also G) liefert.
Per

Code: Alles auswählen

=MAX(($B$4:$M$9=I15)*(ZEILE($B$4:$M$9)*100+SPALTE($B$4:$M$9)))

würde man dann den Wert erhalten, der in der größten Zeile/Spalte steht. Daraus könnte man dann wieder Monat und Jahr extrahieren ...
In deinem Fall, wenn also zwei gleiche Werte unter den Top Ten wären, wäre das aber auch problematisch ...
Besser wäre es für diese Art von Auswertung, wenn die Daten Zeilenweise vorliegen würden:
Feb 2018 12251
Mrz 2018 186360
...
Feb 2023 139893
Daraus ließe sich dann mit erheblich weniger Aufwand eine Gesamtübersicht und Top-Ten-Liste erstellen.
Anbei mal zwei Varianten: auf Tabelle 2 Übersicht und Top-Ten per Formel und alternativ zwei Pivottabellen, um ohne Formelaufwand eine Übersicht und Sortierung zu erhalten.

[Helmut_S]

Hallo, ich weiß, dass ich wieder einmal spät dran bin, aber vielleicht interessiert meine Lösung doch den einen oder anderen Leser hier im Forum.
Meine Lösung setzt voraus, dass das Layout der Tabelle etwas geändert werden kann. Durch die horizontale Anordnung der Jahreszahlen kommt mein Vorschlag mit der einfachen Funktion "Vergleich()" aus. Zum Testen habe ich die Werte der 2d-Matrix durch Zufallszahlen ersetzt. Bezüge sind bei Bedarf selbstverständlich anzupassen.
Gruß Helmut