DENSE_RANK() in Excel // MTRANS vertikal erweitern

13.06.2025 11:11:47

velo

Hallo zusammen,

da wir in der Firma demnächst endlich mal von Excel 2016 auf 365 wechseln, schau ich mir bereits ein wenig die neuen Möglichkeiten an, die mit dem Wechsel einhergehen.

Die Datei:
https://www.herber.de/bbs/user/177755.xlsx

Folgende Fragen:
1. Wie kann ich die MTRANS Formel dynamisch erweitern (also vertikal in Relation zu der Matrix Formel in K7)?
2. Gibt es in Excel eine Funktion, die so ähnlich funktioniert wie die DENSE_RANK() Funktion in SQL?

Danke bereits im Voraus :)

VG
velo

AW: DENSE_RANK() in Excel // MTRANS vertikal erweitern

13.06.2025 12:48:18

BoskoBiati

Hallo,

in 365 bietet sich das an, in K2:

=LET(xa;SEQUENZ(MAX(A:.A);1;1;1);xb;XVERWEIS(xa;F.:.F;B.:.B;"n.v.";0;1);HSTAPELN(xa;xb))

Es gibt aber auch andere Möglichkeiten.

Gruß

Edgar

AW: DENSE_RANK() in Excel // MTRANS vertikal erweitern

13.06.2025 13:24:48

daniel

Hi

MTRANS hier dynamisch zu erweitern wird schwierig.
Das Problem ist, das die Spaltenzahl im Ergebnis variabel ist und eine einzige Formel, die nach rechts und nach unten spillt, funktioniert nur, wenn es eine Zeilenanzahl und eine Spaltenanzahl gibt. du hättest hier aber unterschiedliche Spaltenanzahlen.

hier mal eine Formel, die die Aufgabe kompeltt löst, dh es reichen die Spalten A-D, die Zwischenrechnungen (Punkte gesamt und Rang) werden in der Formel gemacht.

=LET(a;WEGLASSEN(A.:.D;1);

n;SPALTENWAHL(a;2);

p;SPALTENWAHL(a;3)+SPALTENWAHL(a;4);

pp;SORTIEREN(EINDEUTIG(p);;-1);

nn;NACHZEILE(pp;LAMBDA(x;TEXTVERKETTEN(",";1;FILTER(n;p=x;""))));

r;SEQUENZ(ANZAHL2(pp));

nnn;r&","&nn&"|";

WENNFEHLER(TEXTTEILEN(TEXTVOR(TEXTKETTE(nnn);"|";-1);",";"|");""))

und ja, LET solltest du dir auf jeden Fall anschauen, weil es erlaubt, komplexe Formeln in einer halbwegs übersichtlichen Weise darzustellen.

Gruß Daniel

AW: DENSE_RANK() in Excel // MTRANS vertikal erweitern

13.06.2025 13:44:53

MCO

Moin!

So ganz über beide Funktionenn hab ich es nicht hinbekommen, eine Hilfsspalte bleibt. Das geht auch nicht anders, glaub ich.

Schau es dir mal an:

-	J	K	L	M
2	Platz	1	Zoe Zeller
3		2	Elias Eberhardt	Gabriel Gro
4		4	Alexander Adler
5		5	Franziska Fuchs
6		6	Fabian Freitag
7		7	Ulrike Uhl
8		8	Karla Kr ger

-	J	K	L
2	Platz	=EINDEUTIG(SORTIEREN(F2#))	=ZUZEILE(FILTER($B$2:.$B$500;--($F$2#=K2);""))
3			=ZUZEILE(FILTER($B$2:.$B$500;--($F$2#=K3);""))
4			=ZUZEILE(FILTER($B$2:.$B$500;--($F$2#=K4);""))
5			=ZUZEILE(FILTER($B$2:.$B$500;--($F$2#=K5);""))
6			=ZUZEILE(FILTER($B$2:.$B$500;--($F$2#=K6);""))
7			=ZUZEILE(FILTER($B$2:.$B$500;--($F$2#=K7);""))
8			=ZUZEILE(FILTER($B$2:.$B$500;--($F$2#=K8);""))

Für doppelte Ränge genügt diese Formel in K2:

=SORTIEREN(F2#)

Diese Formel kann man zwar runterziehen

=VSTAPELN(LAMBDA(r;FILTER($B$2:$B$500;$F$2:$F$500=r))(K2))

produziert aber bei doppelten Rängen einen Überlauf-Fehler (nicht verwunderlich)

Diese formel
=VSTAPELN(LAMBDA(r;FILTER($B$2:$B$500;$F$2:$F$500=r))(K2#))
Sollte zwar alles sauber untereinanderstapeln, tut sie aber nicht.
Und hier hört meine Weisheit dann auf :-(

Vielleicht findest du ja noch einen Hinweis an anderer Stelle.

Gruß, MCO

AW: DENSE_RANK() in Excel // MTRANS vertikal erweitern

13.06.2025 14:02:57

velo

Hi MCO,

sehr detailliert und guter Input - vielen Dank!

Bekomme schonmal eine gute Ahnung was jetzt alles mit Matrix Formeln möglich ist und was nicht.

Danke und VG
velo

AW: DENSE_RANK() in Excel // MTRANS vertikal erweitern

13.06.2025 14:58:34

BoskoBiati

Hi,

wenn man mehrere Namen zu einer Platzierung hat, könnte man sie mit einer relativ einfachen Formel in eine Zelle schreiben:

=LET(xa;EINDEUTIG(F:.F);xb;WEGLASSEN(SORTIEREN(xa;1;1);-1);xc;WEGLASSEN(B:.B;1);xd;WEGLASSEN(F:.F;1);xe;NACHZEILE(xb;LAMBDA(x;TEXTVERKETTEN(" /  ";1;FILTER(xc;xd=x;""))));HSTAPELN(xb;xe))

Um die Ergebnisse aus C & D zu summieren reicht auch das:

=C2:.C2000+D2:.D2000

Gruß

Edgar

AW: DENSE_RANK() in Excel // MTRANS vertikal erweitern

13.06.2025 13:32:52

velo

Hi Daniel,

auch dir vielen Dank für deinen Input!
Ist eigentlich die Ideallösung mMn, v.a. mit der Rang Geschichte im Hinterkopf.

VG
Felix

P.S.: Fürs "Protokoll" an die Leute, die ebenfalls eine DENSE_RANK() Alternative in Excel suchen.
Eine ziemlich simple Möglichkeit hab ich noch gefunden:

=VERGLEICH(F2#;SORTIEREN(EINDEUTIG(F2#));0)

AW: DENSE_RANK() in Excel // MTRANS vertikal erweitern

13.06.2025 14:25:41

daniel

die letzte Zeile ist etwas umständlich.

einfacher so:

=LET(a;WEGLASSEN(A.:.D;1);

n;SPALTENWAHL(a;2);

p;SPALTENWAHL(a;3)+SPALTENWAHL(a;4);

pp;SORTIEREN(EINDEUTIG(p);;-1);

nn;NACHZEILE(pp;LAMBDA(x;TEXTVERKETTEN(",";1;FILTER(n;p=x;""))));

r;SEQUENZ(ANZAHL2(pp));

nnn;TEXTVERKETTEN("|";1;r&","&nn);

WENNFEHLER(TEXTTEILEN(nnn;",";"|");""))

kleine Einschränkung dieser Methode:
die Anzahl der Daten ist nicht beliebig, da alle Werte in einem einzigen Text vereinigt werden und dieser nicht länger als 32767 Zeichen lang sein darf.

Gruß Daniel

AW: DENSE_RANK() in Excel // MTRANS vertikal erweitern

13.06.2025 14:50:07

BoskoBiati

Hi,

geht auch so:

=LET(xa;EINDEUTIG(F:.F);xb;WEGLASSEN(SORTIEREN(xa;1;1);-1);xc;WEGLASSEN(B:.B;1);xd;WEGLASSEN(F:.F;1);xe;NACHZEILE(xb;LAMBDA(x;TEXTVERKETTEN(", ";1;FILTER(xc;xd=x;""))));xf;TEXTVERKETTEN("#";0;xe);HSTAPELN(xb;WENNFEHLER(TEXTTEILEN(xf;",";"#");"")))

Gruß

Edgar

AW: DENSE_RANK() in Excel // MTRANS vertikal erweitern

13.06.2025 12:57:31

velo

Hallo Edgar,

danke schonmal für deine Antwort - die LET Funktion muss ich mir allgemein noch genauer anschauen.

Aber würde sagen eher JEIN, da hier bei gleicher Punktzahl (z.B. Platz 2) der zweite Name nicht angezeigt wird. Deswegen habe ich ja in der ursprünglichen Datei auch MTRANS(FILTER()), dass hier alle Personen auf den jeweiligen Platz angezeigt werden.

Trotzdem vielen Dank schonmal für den Denkanstoß!

VG
Felix

AW: DENSE_RANK() in Excel // MTRANS vertikal erweitern

13.06.2025 12:59:16

BoskoBiati

Hallo,

so funktionierts ohne Probleme:

=VSTAPELN(SPALTENWAHL(B1:F1;5;1);SPALTENWAHL(WEGLASSEN(SORTIERENNACH(B.:.F;F.:.F;1;B.:.B;1);-1);5;1))

Gruß

Edgar

AW: DENSE_RANK() in Excel // MTRANS vertikal erweitern

13.06.2025 13:01:06

velo

Perfekt, vielen Dank! :)

Werde mir die Funktionen definitiv mal näher "studieren"

Schönes WE!
velo

DENSE_RANK() in Excel // MTRANS vertikal erweitern

Forumthread: DENSE_RANK() in Excel // MTRANS vertikal erweitern

11
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

DENSE_RANK() in Excel // MTRANS vertikal erweitern

Forumthread: DENSE_RANK() in Excel // MTRANS vertikal erweitern

11 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

11
Beiträge zum Forumthread

Beiträge zu diesem Forumthread