Grenzen und Performance von dynamischen Formeln

[LCohen]

Szenario 1: XLWeb schafft mehr Daten, als XL365

Gegeben sei (nur drei Formeln)

A1: =SORTIEREN(ZUFALLSMATRIX(50000;1;1;5000;1))
B1: ="Text"&ZUFALLSMATRIX(50000;1;1;15;1)
C1: =LAMBDA(ab;o;LET(
a;INDEX(ab;;1);
b;INDEX(ab;;2);
c;EINDEUTIG(a);
d;SCAN(1;a=VSTAPELN(0;WEGLASSEN(a;-1));LAMBDA(y;z;(y+1)*z))+1;
e;SCAN(1;d<VSTAPELN(0;WEGLASSEN(d;-1));LAMBDA(y;z;y+z));
f;MAX(d);
g;MAX(e);
h;REST(SEQUENZ(f*g)-1;f)+1;
i;GANZZAHL(SEQUENZ(f*g;;f)/f);
k;WENNFEHLER(WEGLASSEN(SORTIEREN(HSTAPELN(EINDEUTIG(VSTAPELN(HSTAPELN(d;e);HSTAPELN(h;i)));b);2);;2);"");
m;INDEX(k;SEQUENZ(g;f));
n;NACHZEILE(m;LAMBDA(a;TEXTVERKETTEN(";";;a)));
HSTAPELN(c;WAHL(o;m;n))))(A1:B50000;1)

A:B sollten durch ihre Werte ersetzt werden, damit die Performance der C1-Formel besser beobachtet werden kann.

Was passiert in C1# inhaltlich? Eine nach A:A sortierte Liste soll für A:A EINDEUTIG() werden und zugehörige B:B dahinter entweder in seitlichen Zellen oder TEXTVERKETTEN()d zeigen. Hierfür wird mehrfaches Durcharbeiten der Daten etwa über ZÄHLENWENN, AGGREGAT oder VERGLEICH nicht verwendet, sondern die Daten werden über xSTAPELN gespreizt und mittels SORTIEREN in die gewünschte Position gebracht, damit ein einfacher INDEX daraus das Ergebnis zurückgibt. Etwas aufwändig, aber schnell (im Sekundenbereich).

Was habe ich nun beobachtet (Programme-Stand 21.10.2022)?

In XL365 (2211-15813.20002) kann man die fetten Parameter, die am Ende der LAMBDA-Formel übergeben werden, fehlerfrei nur bis ca. (A1:B2200;1) (nicht genau eingegrenzt) verwenden. Bitte für ein Überschreiten davon vor allem die erste und die letzten Zeilen oder Zellen der Spalte D kontrollieren, ob die Inhalte stimmen.

In XLWeb hingegen klappt GELEGENTLICH auch die o.g. Formel - unverändert mit (A1:B50000;1)! Und ohne Fehler. In anderen Fällen schafft sie es auch mal nicht und gibt #ÜBERLAUF! zurück.

[shift-del]

Moin

Könnte mit diesem "Problem" zu tun haben:
https://www.youtube.com/watch?v=iaJv6E0GRC4

Denn ich erhalte auch bei deutlich kleineren Bereichen schon mal einen #ÜBERLAUF!.

[LCohen]

Vielen Dank, shift-del, dafür! Allerdings habe ich keine der 6 genannten Funktionen ZUFALLS...(3), HEUTE, BEREICH.VERSCHIEBEN und INDIREKT verwendet. Zur Erinnerung: A1:B1 sollten ja plattgemacht werden.

Bis wohin klappt es denn bei Dir sicher (in beiden Programmen), falls Du noch mal schauen magst?

Wie gesagt, 3 Stufen: (1) #ÜBERLAUF! - (2) scheinbar ok, aber inh. falsch, insb. am Anfang und Ende - (3) ohne Fehler.

(2) ist dabei nur in XL365 aufgetreten. Wenn es in XLWeb läuft, dann ohne Fehler.

Zur Sicherheit nur in einer frischen Tabelle eingeben, keine Datei laden.

[shift-del]

Zur Erinnerung: A1:B1 sollten ja plattgemacht werden.

Das hatte ich überlesen.

#ÜBERLAUF!
XL365: Nein
XLWeb: Nein

Inhaltliche Fehler (teilweise auch #NV)
XL365: Die Grenze liegt zwischen 1600 und 1800.
XLWeb: Von 50000 bis Zeile 1000 in größeren Sprüngen getestet und immer noch inhaltliche Fehler.

[LCohen]

Sehr interessant. XLWeb müsste ja eigentlich reproduzierbar gleich bei jedem funktionieren, da die Daten und auch die Rechenleistung (?) in der Cloud sind. Anscheinend gibt XLWeb aber davon an den lokalen Rechner etwas ab. Und da könnte dann das Phänomen von MrExcel greifen, dass interne SPILL-Vorgänge es noch nicht zu Ende schaffen, oder so ähnlich. 

Ich habe dort vor allem den Anfang und das Ende geprüft, wo bei XL365 bei größeren Zeilenanzahlen (ab ca. 2200) die Fehler begannen, aufzutreten. Bei XLWeb stimmte es dort immer, was aber ja nicht heißen muss, dass es überall stimmt.

Man könnte nun noch eine Umkehrfunktion schreiben, die zurück in den Ausgangszustand wandelt ... und dann Zustand 1 und Zustand 3 miteinander vergleichen (müsste dann identisch sein, als Hinweis, dass wirklich zuende kalkuliert wurde).

Mögliches Zwischenfazit: 

1. Reproduzierbarkeit von Fehlern ist schwieriger bis unmöglich geworden.
   
2. Eventuell fährt man bei Massendaten derzeit sicherer ohne ein SPILL und vor allem ein mehrfaches SPILL innerhalb von LET/LAMBDA.
   
3. Die Funktion hatte ich in Einzelschritten im Blatt aufgebaut und dann rückwärts zusammengeschoben. Möglicherweise ist jedoch der Beibehalt der Einzelschritte im Blatt fehlervermeidend. Mehr RAM dürfte wohl nicht verbraucht werden, da ja auch die Variableninhalte in der langen Funktion sämtlich aufbewahrt werden.
   
4. Vermutlich ist es am allersichersten, wenn man VBA über Formeln rechnen lässt und diese für den nächsten Schritt plattmacht. Dann dienen sie als Quelle, und können gelöscht werden, wenn das gleiche beim übernächsten Schritt geschieht. Insgesamt ist der Speicherbedarf außerdem geringer, weil es a) weniger Formeln zur gleichen Zeit sowie b) weniger vorgehaltene Daten überhaupt gibt.
   

[maninweb]

Hallo,

neben dem bereits erwähnten könnten auch interne Rekursionen und Iterationen einen Einfluss ausüben. Denn letztendlich
sind in Deiner LAMBDA implizite Rekursionen enthalten, wie NACHZEILE. Ohne NACHZEILE hätte man ja (früher) eine
rekursive LAMBDA geschrieben.

Die Anzahl der Rekursionen ist meines Wissens abhängig von Speicher. Kurze Variablennamen können die Anzahl erhöhen.
Die mögliche Anzahl ist meines Wissens im Laufe der Zeit bei LAMBDA mehr geworden, genaue Grenzen kenne ich nicht.
Diese dürften aber auch Excel selbst geschuldet sein - also, Uralt-Code dürfte das ggf. ebenfalls beeinflussen.

Excel-Web ist quasi eine Neuentwicklung, weshalb da vielleicht mehr möglich ist.

Gruß

[shift-del]

@LCohen

Da ich mit LAMBDA() und den Hilfsfunktionen noch nicht vertraut bin kann ich die Formel nicht analysieren.
Aber ich tippe darauf dass an irgendeiner Stelle Grenzwerte überschritten werden. Auch die Rekursion könnte eine Rolle spielen.

[LCohen]

OT: Mal eine ketzerische Frage:

Warum muss NACHZEILE eigentlich implizit eine rekursive Funktion sein? Ich bin nicht so der Programmiersprachenfachmann, aber da der Umfang jeweils bekannt ist, müsste es doch auch sinngemäß eine For-Next-Schleife sein können. Damit hätte man keine Stackerhöhung. Außerdem für diese These spricht doch der Umfang der möglichen Zeilen in einer Tabelle. 2 hoch 20 Rekursionen kosten ja ganz schön viel Platz.

Über die letzte Anweisung WAHL kann man übrigens sehen, dass ich in all meinen Tests NACHZEILE gar nicht auslösen müsste.

Wenn ich wieder zu Hause bin, nehme ich die Anweisung also einfach mal raus.

Nicht nur implizit, sondern tatsächlich ist hingegen SCAN rekursiv oder iterativ, da man ohne SCAN Iteration verwenden müsste.

[LCohen]

Ich hatte einen Fehler in C1:. So ist es richtig: (<= statt nur < in Definition e). Die falsche Anweisung führt zu einem Fehler in derjenigen frühesten Zeile, wo es nur einen Eintrag gibt (naturgemäß bei shift-del und mir verschieden, da wir die Zufallsergebnisse A:B vorsichtshalber ja fixiert haben).

A1: =SORTIEREN(ZUFALLSMATRIX(50000;1;1;5000;1))
B1: ="Text"&ZUFALLSMATRIX(50000;1;1;15;1)

C1: =LAMBDA(ab;o;LET(
a;INDEX(ab;;1);
b;INDEX(ab;;2);
c;EINDEUTIG(a);
d;SCAN(1;a=VSTAPELN(0;WEGLASSEN(a;-1));LAMBDA(y;z;(y+1)*z))+1;
e;SCAN(1;d<=VSTAPELN(0;WEGLASSEN(d;-1));LAMBDA(y;z;y+z));
f;MAX(d);
g;MAX(e);
h;REST(SEQUENZ(f*g)-1;f)+1;
i;GANZZAHL(SEQUENZ(f*g;;f)/f);
k;WENNFEHLER(WEGLASSEN(SORTIEREN(HSTAPELN(EINDEUTIG(VSTAPELN(HSTAPELN(d;e);HSTAPELN(h;i)));b);2);;2);"");
m;INDEX(k;SEQUENZ(g;f));
n;NACHZEILE(m;LAMBDA(a;TEXTVERKETTEN(";";;a)));
HSTAPELN(c;WAHL(o;m;n))))(A1:B50000;1)

Die Ergebnisse sind nun alle korrekt, auch bei 100.000 statt 50.000 Zeilen und 10.000 statt 5.000 Ergebniszeilen, und in beiden Programmen. #ÜBERLAUF! kommt öfters mal vor, so dass auch MrExcel bestätigt bleibt. Bei höheren Werten kommen auch nochmals Fehler vor. Ich habe sogar A:B jetzt nicht geplättet. Da ich nun schon einen Fehler in der Formel hatte, nehme ich b.a.w. auch weitere mögliche Fehler auf meine Kappe. Möglicherweise könnte noch einer dort liegen, dass der 2. SCAN von Spalte A abweicht oder wenn A nicht lückenlos ist. Ob es nun gänzlich falscher Alarm war?

Die Kombination 200.000 und 20.000 klappt auch noch gelegentlich. Auch 250.000 und 20.000 hat noch mal geklappt. In der Größenordnung "mittlerer 6stelliger Zellanzahlenbereich" für die Ausgabe genau dieses Problems scheint Excel dann bei dynamischen Formeln eine Grenze zu erreichen. Wenn ich nun A:B plätte, so dass nur die eigentliche Aufgabe gerechnet wird, benötigt Excel ca. 5 Sekunden dafür (250.000/20.000). Somit nimmt die Rechenzeit nur linear zur Datenmenge zu, so dass ich in dieser Hinsicht wenigstens etwas richtig gemacht habe (Warum fast? Ich sollte besser VBA einsetzen: kein #ÜBERLAUF!, kein Speichermoloch beim Berechnen, keine so frühe Begrenzung der Datenmenge, ja, sogar keine Begrenzung auf Excels Zeilen). Bei (500.000/50.000) wechseln sich #ÜBERLAUF! und #BEZUG! miteinander ab.

Da die Zwischenschritte jederzeit abrufbar sind, müssen sie natürlich hinzugerechnet werden. Die Frage ist z.B., wieviel Excel allein schon aus der Zuweisung k aufbewahrt. Nur das Ergebnis? Oder die Zwischenergebnisse jeder Verschachtelung? Wann und wo räumt Excel auf? Möglicherweise hilft es, wenn man innerhalb einer Funktion mit vielen Zwischenberechnungen Teile mit eigenen LAMBDA-Funktionen abschließt. Damit wäre LAMBDA auch eine Art "Speicherhygiene". Und man kann noch größere Mengen berechnen.