18.11.2022, 14:03
=LET(
w;SORTIEREN(A2:INDEX(B:B;ANZAHL2(A:A)+1));
x;SPALTENWAHL(w;1);
s;SPALTENWAHL(w;2);
DECUM;LAMBDA(x;LET(d;SEQUENZ(ZEILEN(x));INDEX(x;d)-(d>1)*INDEX(x;d-1)));
u;FILTER(HSTAPELN(x;SCAN(0;s;LAMBDA(a;c;a+c)));1-VSTAPELN(WEGLASSEN(x;1)=WEGLASSEN(x;-1);0));
f;WEGLASSEN(HSTAPELN(SPALTENWAHL(u;1);DECUM(SPALTENWAHL(u;2)));-1);
f)
Was macht es so schnell?
a) bei 1 Mio Zeilen mit 6 "Pivot-Zeilen" ca. 1,5 Sekunden
b) bei 1 Mio Zeilen mit 100000 "Pivot-Zeilen" ca. 35 Sekunden für die Erstellung; Änderung eines Wertes: 1,5 Sekunden
c) Das richtige Pivot ist trotzdem noch viel schneller: Erstellung von b) auch mit nur 1,5 Sekunde
Kritik: Vermutlich noch das eine oder andere VSTAPELN oder WEGLASSEN zuviel. Aber für den Speed egal.
Für ein gekapseltes LAMBDA (falls man es braucht) benötigt man nur das erste SORTIEREN(...) als Argument w zu verwenden.
Für ANZAHL oder MITTELWERT statt SUMME käme ein zählendes statt summierendes SCAN zusätzlich mit in den Code. So könnte man die Funktion mit mehreren AGGREGAT-Formen ausstatten, wie maninweb es hier tut:
clever-excel-forum.de/Thread-LAMBDA-In-NACHZEILE-verwendete-Funktion-dynamisieren
Aber eben ohne die "exponenzielle Seuche".
w;SORTIEREN(A2:INDEX(B:B;ANZAHL2(A:A)+1));
x;SPALTENWAHL(w;1);
s;SPALTENWAHL(w;2);
DECUM;LAMBDA(x;LET(d;SEQUENZ(ZEILEN(x));INDEX(x;d)-(d>1)*INDEX(x;d-1)));
u;FILTER(HSTAPELN(x;SCAN(0;s;LAMBDA(a;c;a+c)));1-VSTAPELN(WEGLASSEN(x;1)=WEGLASSEN(x;-1);0));
f;WEGLASSEN(HSTAPELN(SPALTENWAHL(u;1);DECUM(SPALTENWAHL(u;2)));-1);
f)
Was macht es so schnell?
a) bei 1 Mio Zeilen mit 6 "Pivot-Zeilen" ca. 1,5 Sekunden
b) bei 1 Mio Zeilen mit 100000 "Pivot-Zeilen" ca. 35 Sekunden für die Erstellung; Änderung eines Wertes: 1,5 Sekunden
c) Das richtige Pivot ist trotzdem noch viel schneller: Erstellung von b) auch mit nur 1,5 Sekunde
- w: Die Daten A:B (beginnend in A2) werden nach A:A sortiert
- u: A:A ("Pivot-Zeile") wird neben das kumulierte B:B ("Pivot-Wert") gestellt und die Sätze "A:A <> A:A-Nachfolger" in der nächsten Zeile gefiltert (also ein A:A-EINDEUTIG mit jeweils dessen letzten B:B-Wert dazu)
- f: Die kumulierten B:B-Werte werden in Einzelwerte aufgelöst
Kritik: Vermutlich noch das eine oder andere VSTAPELN oder WEGLASSEN zuviel. Aber für den Speed egal.
Für ein gekapseltes LAMBDA (falls man es braucht) benötigt man nur das erste SORTIEREN(...) als Argument w zu verwenden.
Für ANZAHL oder MITTELWERT statt SUMME käme ein zählendes statt summierendes SCAN zusätzlich mit in den Code. So könnte man die Funktion mit mehreren AGGREGAT-Formen ausstatten, wie maninweb es hier tut:
clever-excel-forum.de/Thread-LAMBDA-In-NACHZEILE-verwendete-Funktion-dynamisieren
Aber eben ohne die "exponenzielle Seuche".