[LAMBDA] NACHZEILE NACHSPALTE kombiniert

[LCohen]

Im gestrigen herber.de/forum/archiv/1928to1932/1929094_Bereich_dynamische_summieren.html findet man diese (hier von mir schon mit der s.u. zweiten blauen Formel versehene) angehängte Datei:
  158931.xlsx (Größe: 14,95 KB / Downloads: 5)

Es geht nun um die Formel in B15: bzw. B15#:

In klassischem Excel würde man
=SUMME(INDEX($D$5:$O$10;VERGLEICH($A15&$B$13;$A$5:$A$10&$C$5:$C$10;);VERGLEICH(B$14;$D$3:$O$3;)-{0.1.2.3.4.5}))
verwenden. Sie ist ausfüllbar in Zeilen und Spalten bis B15[:D18]:.

Mit NACHZEILE geht es auch in B15#, so dass die Formel dynamisch bis B18 ausgefüllt wird:
=NACHZEILE($A15:$A18;LAMBDA(z;SUMME(INDEX($D5:$O10;VERGLEICH(z&$B13;$A5:$A10&$C5:$C10;);VERGLEICH(B14;$D3:$O3;)-{0.1.2.3.4.5}))))
Es muss aber manuell weiter bis D15 kopiert werden. Daher ja auch die $-Spalten-Absolutierer. Also nur in eine Richtung dynamisch.

Der Wunschzustand wäre nun, dieses auch noch überflüssig werden zu lassen (über NACHSPALTE), also eine einzige Fml B15#. Aber:
=NACHSPALTE(B14:D14;LAMBDA(s;NACHZEILE(A15:A18;LAMBDA(z;SUMME(INDEX(D5:O10;VERGLEICH(z&B13;A5:A10&C5:C10;);VERGLEICH(s;D3:O3;)-{0.1.2.3.4.5})))))) ergibt #KALK!
=NACHZEILE(A15:A18;LAMBDA(z;SUMME(INDEX(D5:O10;VERGLEICH(z&B13;A5:A10&C5:C10;);NACHSPALTE(B14:D14;LAMBDA(s;VERGLEICH(s;D3:O3;)-{0.1.2.3.4.5})))))) ergibt 4mal untereinander #KALK!
=NACHZEILE(A15:A18;LAMBDA(z;SUMME(INDEX(D5:O10;VERGLEICH(z&B13;A5:A10&C5:C10;);NACHSPALTE(B14:D14;LAMBDA(s;VERGLEICH(s;D3:O3;)))-{0.1.2.3.4.5})))) ergibt 4mal untereinander #NV (Klammern versetzt!)

An anderer Stelle war mir die Kombi von NACHZEILE und NACHSPALTE schon gelungen, möglicherweise sogar mehrfach verschachtelt. Wer hätte hier eine Idee dazu? Vielleicht hilft ja auch XVERGLEICH statt VERGLEICH. Oder das Verschieben von SUMME. Vielleicht geht es aber auch nicht, weil es nicht um eine zusammenhängende Matrix geht, sondern um zwei 90° verdrehte Vektoren.

Der Thread soll zur allgemeinen Erörterung dieser Mehrdimensionalität dienen.

[maninweb]

Hallo,

mir ist es ebenfalls (bislang) nicht gelungen, NACHSPALTE und NACHZEILE zu verschachteln, sodass ein Array aufgespannt wird.
Gibt es denn einen Grund, warum Du diese beiden Funktionen verwenden möchtest? Ich hätte es wie folgt gelöst ...

PHP-Code:

=LET(Cumulation;6;SearchKeys;$A$26:$A$29&$B$24;SearchPeriods;$B$25:$D$25;Data;$D$5:$O$10;DataKeys;$A$5:$A$10&$C$5:$C$10;DataPeriods;$D$3:$O$3;
 MATRIXERSTELLEN(ZEILEN(SearchKeys);SPALTEN(SearchPeriods);LAMBDA(X;Y;
 LET(N;WENNFEHLER(VERGLEICH(INDEX(SearchKeys;X;1);DataKeys;0);0);
     P;WENNFEHLER(VERGLEICH(INDEX(SearchPeriods;1;Y);DataPeriods;0);0);
       WENN((N>0)*(P>0);WENNFEHLER(SUMME(INDEX($D$5:$O$10;N;SEQUENZ(1;WENN(P>Cumulation-1;Cumulation;P);P;-1)));0);0))))) 


Gruß

[LCohen]

Danke schön, maninweb!

Ich habe mal die $ entfernt 
die Bezüge korrigiert und
unten einmal Data statt der Wiederholung D5:O10 verwendet:

B15#: =LET(Cumulation;6;SearchKeys;A15:A18&B13;SearchPeriods;B14:D14;Data;D5:O10;DataKeys;A5:A10&C5:C10;DataPeriods;D3:O3;
MATRIXERSTELLEN(ZEILEN(SearchKeys);SPALTEN(SearchPeriods);LAMBDA(X;Y;
LET(N;WENNFEHLER(VERGLEICH(INDEX(SearchKeys;X;1);DataKeys;0);0);
P;WENNFEHLER(VERGLEICH(INDEX(SearchPeriods;1;Y);DataPeriods;0);0);
WENN((N>0)*(P>0);WENNFEHLER(SUMME(INDEX(Data;N;SEQUENZ(1;WENN(P>Cumulation-1;Cumulation;P);P;-1)));0);0)))))

Die Zeile 15 (innerhalb der Zeilen 15:18) rechnet momentan nur 0 statt der korrekten Summen. Aber grundsätzlich ist das natürlich eine gute Sache!

Ich kann leider meine Beiträge nicht finden, in welchen ich NACHZEILE und NACHSPALTE verschachtelt habe. Ein Vorteil ihrer Verwendung könnte sein, dass man damit die grundsätzliche Einzelformel B15: auch für B15#: beibehalten kann. Und der Sinn des Ganzen überhaupt dürfte ja klar sein: Dynamik ohne Ausnahme.

MATRIXERSTELLEN habe ich genauso wie REDUCE selbst noch nie benötigt, wird aber bei Unmöglichkeit von NACHZEILE/NACHSPALTE möglicherweise zum Mittel der Wahl.

Ich suche mal weiter!

[LCohen]

Ich habe noch kein funktionierendes NACHZEILE/NACHSPALTE (von mir) gefunden. MATRIXERSTELLEN beginnt mir aber zu gefallen!

3-4-5   1-11-3-2 3-4-6-1
2-3-6-7 1        2-4-9-7-1 ergibt

12      17       14
18       1       23

mit =LET(a;A1:C2;z;ZEILEN(a);s;SPALTEN(a);MATRIXERSTELLEN(z;s;LAMBDA(z;s;SUMME(--TEXTTEILEN(INDEX(a;z;s);"-")))))

Endlich habe ich durch Dich die Berechtigung der Funktion gefunden (vermutlich gibt es noch viele mehr). Also, vielen Dank, maninweb!

Zum Threadproblem damit (neben Deiner, s.o.) die MATRIXERSTELLEN-Lösung, ohne die Struktur der Formel groß zu ändern:

B15#: =LET(z;A15:A18;s;B14:D14;zz;ZEILEN(z);ss;SPALTEN(s);MATRIXERSTELLEN(zz;ss;LAMBDA(zz;ss;SUMME(
INDEX(D5:O10;VERGLEICH(INDEX(z;zz)&B13;A5:A10&C5:C10;);VERGLEICH(INDEX(s;ss);D3:O3;)-SEQUENZ(;6;0))))))

(bei Dir sind ein paar Sicherheitsmechanismen mit drin, die ich nicht habe)

[maninweb]

Moin,

gerne. Habe mich noch etwas damit beschäftigt. Eine Möglichkeit NACHSPALTE und NACHZEILE für diesen Fall zu kombinieren habe ich nicht
gefunden. Ich würde für mich erstmal auch behaupten, das ist nicht machbar. Folgend drei weitere Lösungen in einer Formel; jetzt ohne
Validierungen der 6 Monate rückwärts.

Rekursiv - Erstellt die rekursive Funktion fnColumns, um die Ergebnisse von NACHZEILE auf die Spalten zu expandieren.

PHP-Code:

=LET(SearchKeys;$A$15:$A$18&$B$13;SearchPeriods;$B$14:$D$14;Data;$D$5:$O$10;DataKeys;$A$5:$A$10&$C$5:$C$10;DataPeriods;$D$3:$O$3;
 fnRows;LAMBDA(Period;NACHZEILE(SearchKeys;LAMBDA(A;SUMME(INDEX(Data;VERGLEICH(A;DataKeys;0);VERGLEICH(INDEX(SearchPeriods;1;Period);DataPeriods;0)-{0.1.2.3.4.5})))));
 fnColumns;LAMBDA(This;Item;WENN(Item>1;HSTAPELN(This(This;Item-1);fnRows(Item));HSTAPELN(fnRows(Item))));
 fnColumns(fnColumns;SPALTEN(SearchPeriods))) 


Reduce - Variante mit REDUCE und HSTAPELN

PHP-Code:

=WEGLASSEN(REDUCE("";$B$14:$D$14;LAMBDA(V;A;HSTAPELN(V;NACHZEILE($A$15:$A$18;
 LAMBDA(K;SUMME(INDEX($D$5:$O$10;VERGLEICH(K&$B$13;$A$5:$A$10&$C$5:$C$10;);VERGLEICH(A;$D$3:$O$3;)-{0.1.2.3.4.5})))))));;1) 


Thunk - Variante, die einen soganannten Thunk verwendet - LAMBDA(X;LAMBDA(X)).

PHP-Code:

=LET(SearchKeys;$A$15:$A$18&$B$13;SearchPeriods;$B$14:$D$14;Data;$D$5:$O$10;DataKeys;$A$5:$A$10&$C$5:$C$10;DataPeriods;$D$3:$O$3;
 fnThunk;LAMBDA(X;LAMBDA(X));
 fnPeriod;LAMBDA(Period;NACHZEILE(SearchKeys;LAMBDA(A;SUMME(INDEX(Data;VERGLEICH(A;DataKeys;0);VERGLEICH(Period;DataPeriods;0)-{0.1.2.3.4.5})))));
 WEGLASSEN(REDUCE("";NACHSPALTE(SearchPeriods;LAMBDA(K;fnThunk(fnPeriod(K))));LAMBDA(V;A;HSTAPELN(V;A())));;1)) 


Die kürzeste Variante, wenn man es so sehen möchte, ist die Variante mit REDUCE, wobei REDUCE bei großen Datenmengen meines Wissens dann doch
sehr langsam werden kann. Bei MATRIXERSTELLEN bin ich mir nicht so sicher, ab wann die aufgibt.

Spannend sind übrigens Thunks. Diese ermöglichen den nachträglichen Zugriff auf ein Array, das in einer (!) Zelle gespeichert ist. Man kommt aber dann
meines Wissens nicht herum, trotzdem eine Matrix zu erzeugen, um die Arrayelemente aus der Zelle wieder zu extrahieren. Hier noch ein anderes Beispiel
zu den Thunks.

PHP-Code:

=LET(N;5;F;LAMBDA(x;LAMBDA(x));M;SCAN(0;SEQUENZ(N);LAMBDA(V;A;F(MATRIXERSTELLEN(1;A;LAMBDA(X;Y;Y)))));
 WENNFEHLER(MATRIXERSTELLEN(N;N;LAMBDA(X;Y;INDEX(INDEX(M;X;1)();1;Y)));"-")) 


Gruß

[LCohen]

Vermutlich gilt dann die 32 K-Grenze auch für den Thunk? Für bestimmte Stammdaten, etwa wie Kontenrahmen, könnte das ja ausreichen.

REDUCE scheint rekursiv zu arbeiten. MATRIXERSTELLEN vermutlich nicht (da ähnlich INDEX umgekehrt, mit fixer Größenzuweisung), und ist somit wie NACHSPALTE, NACHZEILE, WEGLASSEN, xSTAPELN und SCAN für Massendaten vorzuziehen.

Thunks muss ich mir mal anschauen, um zu verstehen, was sie bringen bzw. genau bedeuten.

[maninweb]

Hallo,

32 K-Grenze auch für den Thunk? Gut möglich und kann ich mir vorstellen. Habe ich jedoch nie ausprobiert.
Ja, denke ich auch, das Reduce rekursiv arbeitet. Thunks kann ich mir vorstellen, wenn man Zwischenarrays
als Zellelemente ablegen müsste; gebraucht habe ich es bisher in konkreten Fällen noch nicht, was es aber
nicht weniger spannend macht; finde ich.

Gruß

[LCohen]

Eine Idee wäre z.B. FIFO von z.B. Wertpapieren.

Da muss nach jedem Ankauf/Verkauf ein aktualisierter Korb gebildet werden aus Datum-Kaufkurs-Kaufstückpreis der noch enthaltenen Positionen, mithin also ein n*3-Array mitgeschleppt werden, was pro Zeile vorzuhalten ja ein bisschen nervig ist.

Das habe ich bisher mit einem umbrechenden String pro Zelle gelöst.

[LCohen]

Datum-Kaufkurs-Kaufstückpreis sollte natürlich Datum-Kaufkurs-Stück heißen.n

[maninweb]

Moin,

Thunks als vielleicht der bessere Texttrenner in einem Bereich? In Spalte A stehen K-1, K-1-2, K-1-2-3 und K-1-2-3-4. Dann ergibt sich ...

PHP-Code:

=LET(Data;A1:A4;F;LAMBDA(x;LAMBDA(x));M;SCAN("";Data;LAMBDA(V;A;F(TEXTTEILEN(A;"-"))));
 N;ZEILEN(Data);
 P;MAX(SCAN(0;M;LAMBDA(V;A;SPALTEN(A()))));
 WENNFEHLER(MATRIXERSTELLEN(N;P;LAMBDA(X;Y;INDEX(INDEX(M;X;1)();1;Y)));"")) 


Gruß