[LAMBDA] schnelles Summen-Pivot

[LCohen]

=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

1. w: Die Daten A:B (beginnend in A2) werden nach A:A sortiert
   
2. 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)
   
3. f: Die kumulierten B:B-Werte werden in Einzelwerte aufgelöst
   

Es gibt keine klassischen Aggregat-Funktionen mehr, die mehrfach durch den Datenbestand müssen ("exponenzielle Seuche": ZÄHLENWENN, VERGLEICH(;;FALSCH), AGGREGAT, SUMMENPRODUKT, ...). Damit funktioniert die Formel ähnlich schlank, wie das echte PIVOT mit dem "Schubladisieren". Bremsen tun noch die Kumulation und die De-Kumulation. Das kann Microsoft im Excel-Programm-Code schneller.

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".

[LCohen]

=LET(
x;SORTIEREN(A2:INDEX(A:A;ANZAHL2(A:A)+1));
y;WEGLASSEN(x;1)=WEGLASSEN(x;-1);
z;SCAN(0;VSTAPELN(0;y);LAMBDA(a;c;a*c+1));
u;HSTAPELN(x;z;VSTAPELN(y;0));
f;FILTER(SPALTENWAHL(u;{1.2});1-SPALTENWAHL(u;3));
f)

ist das ANZAHL-Pivot dazu (benötigt daher nur Spalte A). Mischt man die beiden, hätte man das MITTELWERT-Pivot.

[RPP63]

"exponenzielle Seuche"

Moin!

Wollte ich nicht glauben und habe das mal für die "Anzahl-Pivot" nachgestellt.

Erst mal schaffen wir eine Datenbasis mit 1.000.000 Zeilen.

• als Artikel die sechs Buchstaben A bis F

• als Werte dreistellige Zufallszahlen



Konstruktionsmakro:

Option Explicit
Dim Start#

Sub Fill_it()
Range("A1,D1,G1") = "Artikel"
Range("B1,E1,H1") = "Wert"
Range("A2").Formula2 = "=CHAR(RANDARRAY(1000000,,65,70,1))"
Range("B2").Formula2 = "=RANDARRAY(1000000,,100,999,1)"
Range("A2#").Copy: Range("A2#").PasteSpecial xlPasteValues
Range("B2#").Copy: Range("B2#").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.Goto Cells(1)
End Sub

Frisch ans Werk!

Ich habe seit längerem eine Anz-Pivot-Formel für Spalte A:

=LET(a;A2:INDEX(A:A;ANZAHL2(A:A));

  x;SORTIEREN(EINDEUTIG(a));

  y;ZÄHLENWENNS(a;x);

  WAHL({1.2};x;y))

Bei Deiner Formel habe ich das +1 in x entfernt.



Laufzeitvergleich:

Ausführen der Sub Laufzeittest()

Sub Laufzeittest()
Piv_Anzahl_LC
Piv_Anzahl_RPP
End Sub

Sub Piv_Anzahl_LC()
Start = Timer
Range("G2").Formula2 = "=LET(" & _
                        "x,SORT(A2:INDEX(A:A,COUNTA(A:A)))," & _
                        "y,DROP(x,1)=DROP(x,-1)," & _
                        "z,SCAN(0,VSTACK(0,y),LAMBDA(a,c,a*c+1))," & _
                        "u,HSTACK(x,z,VSTACK(y,0))," & _
                        "f,FILTER(CHOOSECOLS(u,{1,2}),1-CHOOSECOLS(u,3))," & _
                        "f)"
Debug.Print "LCohen: " & Timer - Start
End Sub

Sub Piv_Anzahl_RPP()
Start = Timer
Range("D2").Formula2 = "=LET(a,A2:INDEX(A:A,COUNTA(A:A))," & _
                      "x,SORT(UNIQUE(a))," & _
                      "y,COUNTIFS(a,x)," & _
                      "CHOOSE({1,2},x,y))"
Debug.Print "RPP63: " & Timer - Start
End Sub

Ergibt in meinem Direktfenster:

PHP-Code:

LCohen: 3,875
RPP63: 0,9609375 


Meine exponentielle Seuche ist viermal schneller! 

	D	E	F	G	H
1	Artikel	Wert		Artikel	Wert
2	A	167572		A	167572
3	B	166789		B	166789
4	C	166606		C	166606
5	D	167029		D	167029
6	E	165818		E	165818
7	F	166186		F	166186

Zelle	Formel
D2	=LET(a;A2:INDEX(A:A;ANZAHL2(A:A));x;SORTIEREN(EINDEUTIG(a));y;ZÄHLENWENNS(a;x);WAHL({1.2};x;y))
G2	=LET(x;SORTIEREN(A2:INDEX(A:A;ANZAHL2(A:A)));y;WEGLASSEN(x;1)=WEGLASSEN(x;-1);z;SCAN(0;VSTAPELN(0;y);LAMBDA(a;c;a*c+1));u;HSTAPELN(x;z;VSTAPELN(y;0));f;FILTER(SPALTENWAHL(u;{1.2});1-SPALTENWAHL(u;3));f)

Gruß Ralf

---

Und jetzt das Ganze mit einer "Summenpivot".
Modul Modul2

Option Explicit 
Dim Start# 
 
Sub Laufzeittest2() 
Fill_it2 
Piv_Summe_LC 
Piv_Summe_RPP 
End Sub 
 
Sub Fill_it2() 
Cells.Clear 
Range("A1,D1,G1") = "Artikel" 
Range("B1,E1,H1") = "Wert" 
Range("A2").Formula2 = "=CHAR(RANDARRAY(1000000,,65,70,1))" 
Range("B2").Formula2 = "=RANDARRAY(1000000,,100,999,1)" 
Range("A2#").Copy: Range("A2#").PasteSpecial xlPasteValues 
Range("B2#").Copy: Range("B2#").PasteSpecial xlPasteValues 
Application.CutCopyMode = False 
Application.Goto Cells(1) 
End Sub 
 
Sub Piv_Summe_LC() 
Start = Timer 
Range("G2").Formula2 = "=LET(" & _
                        "w,SORT(A2:INDEX(B:B,COUNTA(A:A)+1))," & _
                        "x,CHOOSECOLS(w,1)," & _
                        "s,CHOOSECOLS(w,2)," & _
                        "DECUM,LAMBDA(x,LET(d,SEQUENCE(ROWS(x)),INDEX(x,d)-(d>1)*INDEX(x,d-1)))," & _
                        "u,FILTER(HSTACK(x,SCAN(0,s,LAMBDA(a,c,a+c))),1-VSTACK(DROP(x,1)=DROP(x,-1),0))," & _
                        "f,DROP(HSTACK(CHOOSECOLS(u,1),DECUM(CHOOSECOLS(u,2))),-1)," & _
                        "f)" 
Debug.Print "LCohen: " & Timer - Start 
End Sub 
 
Sub Piv_Summe_RPP() 
Start = Timer 
Range("D2").Formula2 = "=LET(anz,COUNTA(A:A)," & _
                      "a,A2:INDEX(A:A,anz)," & _
                      "b,B2:INDEX(B:B,anz)," & _
                      "x,SORT(UNIQUE(a))," & _
                      "y,SUMIFS(b,a,x)," & _
                      "CHOOSE({1,2},x,y))" 
Debug.Print "RPP63: " & Timer - Start 
End Sub 
 

Code:

LCohen: 3,140625
RPP63: 0,96875

Mehr als dreimal schneller …

[RPP63]

Um die Leistung der Formel aus der Threaderöffnung zu würdigen:
Sie spielt ihre Vorteile bei vielen "Artikeln" aus!
1.000.000 Zeilen mit 100.000 Artikeln:

Code:

LCohen: 2,375
RPP63: 174,26953125

Oops!

[LCohen]

RPP63: Das wollte ich ja auch nur. Analogie: Die AN 225 "Mriya" startete nicht so schnell, wie ein Porsche Turbo. Aber sie überholte ihn irgendwann, und über 300 km (die Strecke, die ein Porsche Turbo vermutlich ohne Nachtanken bei Höchstgeschwindigkeit schafft) ist sie schon doppelt so schnell. Im Gegensatz zu ihm kann sie aber wohl weiter als 2000 km am Stück Strecke machen. Außerdem kann sie noch eine gerade Strecke nehmen.

---

Ich habe jetzt eine LAMBDA-Formel über alle gruppierbaren Parameter, die sich allein aus der Lage der sortierten Grunddaten ermitteln lassen (!), gebaut. Ich taufe sie einfach mal frech PVT ("kurzes Pivot").

PVT: =LAMBDA(XQuelle;[YAuswahl];LET(
XZähler;SEQUENZ(ZEILEN(XQuelle));
XSort;SORTIEREN(XQuelle;{1.2});
XBez;INDEX(XSort;;1);
XWert;INDEX(XSort;;2);
XVorIdent;VSTAPELN(FALSCH;WEGLASSEN(XBez;1)=WEGLASSEN(XBez;-1));
XErster;NICHT(XVorIdent);
XLetzter;NICHT(VSTAPELN(WEGLASSEN(XVorIdent;1);FALSCH));
XVon;XErster*XZähler;
XBis;XLetzter*XZähler;
XKumSum;SCAN(0;XWert;LAMBDA(a;c;a+c));
YBez;EINDEUTIG(XBez);
YVon;FILTER(XVon;XErster);
YBis;FILTER(XBis;XLetzter);
YAnz;YBis-YVon+1;
YKumSum;FILTER(XKumSum;XLetzter);
YSum;LET(x;VSTAPELN(0;YKumSum);WEGLASSEN(x;1)-WEGLASSEN(x;-1));
YMittelw;YSum/YAnz;
YMin;INDEX(XWert;YVon);
YMax;INDEX(XWert;YBis);
YMedian;(INDEX(XWert;(YVon+YBis)/2)+INDEX(XWert;(YVon+YBis+1)/2))/2;
Y;HSTAPELN(YBez;YVon;YBis;YAnz;YKumSum;YSum;YMittelw;YMin;YMax;YMedian);
z;WENN(WURDEAUSGELASSEN(YAuswahl);Y;SPALTENWAHL(Y;YAuswahl));
z))

Da ANZAHL bzw. ZÄHLENWENN (hier: YAnz) darin die einzige Funktion ist, die nur mit den Bezeichnungen (Spalte 1) und ohne deren Werte (Spalte 2) des auszuwertenden Bereichs auskommt, habe ich darauf verzichtet, den Quellbereich auch einspaltig statt zweispaltig auswerten zu können. Man muss also eine zweispaltige Quelle als Parameter übergeben. Ist die 2. Spalte leer, können auch nur die 4 ersten Spalten YBez, YVon, YBis, YAnz zurückgegeben werden; der Rest besteht dann aus Nullwerten.

Die fetten Parameter werden in der Gruppierung standardmäßig bereitgestellt. Will ich nur bestimmte, spezifiziere ich das optionale Argument [YAuswahl]. =PVT(C1#;{1.6.10}) ergibt bspw. also nur YBez, YSum, YMedian.

Die Designdatei für die Formel, mit der ich ihre Schritte geschrieben habe, habe ich angehängt. Eine befriedigende Lösung für den Rest aller AGGREGAT-Möglichkeiten wie KKLEINSTE, QUARTILE oder MODUS.EINF ist hier noch nicht enthalten. Durch die ausschließliche Behandlung der Lage der Daten könnte man aber anstelle der Gesamtdaten auch z.B. vermutlich leicht einen TOP5- oder FLOP10-Filter darüber legen.

Viel Spaß beim Anschauen!

  221119 Designdatei für Pivotierformel.xlsx (Größe: 17,09 KB / Downloads: 11)

[LCohen]

Hier ein erstes Anwendungsbeispiel (dort: #9), bei dem der zu erwartende Sort jedoch "durchbrochen" wird (sortiert BLEIBT es dort allein nach dem Inhaltswechsel eines Feldes, d.h. es findet kein Sort statt, sondern eine Gruppierung).

Die Funktion selbst ist dort unverändert.

[RPP63]

Ich reaktiviere diesen Thread, weil es jetzt ja PIVOTMIT() gibt.
Vorweg:
Die .Formula2-Eigenschaft hat noch einen kleinen Übersetzungsbug (SUM bleibt SUM und wird nicht in SUMME umgewandelt, deshalb #NAME?).
Deshalb verwende ich .Formula2Local
Außerdem habe ich die Artikelzahl auf halbwegs realistische 500 bei 1.000.000 Zeilen gewählt.

Das Ergebnis vorweg:

PHP-Code:

LCohen: 2,57421875
RPP63: 6,046875
PIVOTMIT: 4,80859375 


Deshalb nochmal Chapeau! LCohen!

Der gesamte Code folgt.
Laufzeittest2 starten.

Modul Modul1

 
Option Explicit 
Dim Start# 
  
Sub Laufzeittest2() 
Fill_it2 
Piv_Summe_LC 
Piv_Summe_RPP 
Piv_Sum_Formula2 
End Sub 
  
Sub Fill_it2() 
Cells.Clear 
Range("A1,D1,G1,J1") = "Artikel" 
Range("B1,E1,H1,K1") = "Wert" 
Range("A2").Formula2 = "=RANDARRAY(1000000,,1,500,1)" 
Range("B2").Formula2 = "=RANDARRAY(1000000,,100,999,1)" 
Range("A2#").Copy: Range("A2#").PasteSpecial xlPasteValues 
Range("B2#").Copy: Range("B2#").PasteSpecial xlPasteValues 
Application.CutCopyMode = False 
Application.Goto Cells(1) 
End Sub 
  
Sub Piv_Summe_LC() 
Start = Timer 
Range("G2").Formula2 = "=LET(" & _
                        "w,SORT(A2:INDEX(B:B,COUNTA(A:A)+1))," & _
                        "x,CHOOSECOLS(w,1)," & _
                        "s,CHOOSECOLS(w,2)," & _
                        "DECUM,LAMBDA(x,LET(d,SEQUENCE(ROWS(x)),INDEX(x,d)-(d>1)*INDEX(x,d-1)))," & _
                        "u,FILTER(HSTACK(x,SCAN(0,s,LAMBDA(a,c,a+c))),1-VSTACK(DROP(x,1)=DROP(x,-1),0))," & _
                        "f,DROP(HSTACK(CHOOSECOLS(u,1),DECUM(CHOOSECOLS(u,2))),-1)," & _
                        "f)" 
Debug.Print "LCohen: " & Timer - Start 
End Sub 
  
Sub Piv_Summe_RPP() 
Start = Timer 
Range("D2").Formula2 = "=LET(anz,COUNTA(A:A)," & _
                      "a,A2:INDEX(A:A,anz)," & _
                      "b,B2:INDEX(B:B,anz)," & _
                      "x,SORT(UNIQUE(a))," & _
                      "y,SUMIFS(b,a,x)," & _
                      "CHOOSE({1,2},x,y))" 
Debug.Print "RPP63: " & Timer - Start 
End Sub 
 
Sub Piv_Sum_Formula2() 
Start = Timer 
Range("J2").Formula2Local = "=LET(anz;ANZAHL2(A:A);" & _
                            "a;A2:INDEX(A:A;anz);" & _
                            "b;B2:INDEX(B:B;anz);" & _
                            "PIVOTMIT(a;;b;SUMME;;0))" 
Debug.Print "PIVOTMIT: " & Timer - Start 
End Sub 

Gruß Ralf

[LCohen]

Das erstaunt mich. Immerhin macht Microsoft das für eine weltweite Userschar von vermutlich mehr als 100 Millionen. Dann sollten sie doch optimieren. Da das aber auch später passieren kann, ist es wiederum egal.

[RPP63]

Dann sollten sie doch optimieren. Da das aber auch später passieren kann, ist es wiederum egal.

Vorgeschichte:
Ich hatte vor einer Woche eine PN von @PivPQ bekommen, in der er mir mitteilte, dass PIVOTMIT() bei ihm die langsamste Variante ist.
Habe heute ein Update eingespielt und siehe da:
Ja, MS hat "optimiert", und zwar zum Schlechteren (genauer: die Funktion rechnet jetzt ein Drittel(!) langsamer) …
Aktueller Durchlauf auf meiner alten Möhre:

Code:

LCohen: 2,50390625
RPP63: 5,951171875
PIVOTMIT: 6,46875

Noch ein Zusatz!
Im Nachbarthread stellte ich ja folgende Frage:

Dennoch die Nachfrage, warum man zusätzlich ein "verkapptes" GROUPBY() anbietet, wenn doch PIVOTBY() die bessere Funktionalität bietet ("aufgebohrt" durch optionale Parameter).

Vielleicht (so war meine vage Hoffnung) rechnet es ja schneller, weil "kastriert"?
Pustekuchen!
Genau so langsam!

Code:

=LET(anz;ANZAHL2(A:A);a;A2:INDEX(A:A;anz);b;B2:INDEX(B:B;anz);GRUPPIERENNACH(a;b;SUMME))

[LCohen]

Meine Variante könnte z.B. den Nachteil haben, dass sie für den Nutzen der Schnelligkeit zu viel Speicher belegt. Das habe ich aber nicht ausprobiert, bzw. ich weiß nicht, wie man Speicherbedarf einer LET/LAMBDA-Funktion ZU JEDER ZEIT ermittelt.