Office: LAMBDA()-UDF (ben.def. Tabellenfunktionen ohne VBA/xl4m)

LAMBDA()-UDF (ben.def. Tabellenfunktionen ohne VBA)

Kurz nach LET kommt nun mit LAMBDA am 3.12.2020 die Vervollständigung der neuen 365-Microsoft-Welt (seit 7.2.2022 auch in XLWeb, also für jeden kostenlos): Tabellenfunktion-UDF's, die nicht virenanfällig sind, da nur das interne Tabellenfunktionsinstrumentarium anfällt bzw. sich darauf beschränkt werden kann (Dateiformat .XLSX ausreichend).

Wie geht man vor?
1. Man entwirft den Funktionscode in der Tabelle (ganz überwiegend mit LET)
2. Im Namensmanager erstellt man die Funktion mit ihrem Funktionsnamen und -code.

Nachfolgend eine alphabetische Liste schon erstellter Funktionen. Sie steht auch im CEF-Forum (dort außerdem weitere von maninweb oder anderen).

ACCUM, DECUM (Wertevektor) kumuliert oder entkumuliert einen Vektor
AFA (AHDat;ND;AHK;Jahr;Satz) zeigt die AfA eines Jahres zu einem Wirtschaftsgut an
ARRAY.COMBINE (Arr1;Arr2;Origin) hängt Arr2 unter Arr1 und liefert als Origin wahlw. Tabellen-Nr mit/ohne Satz-Nr
ARRAY.SHAKE (Arr) verteilt Arr-Inhalte zufällig neu (1:1 je genau einmal)
ARRAY.SORT (Arr;Richtung;AufAb) sortiert Arr in Lese- oder Telefonbuchrichtung auf oder ab.
COMBIN.LIST (Arr;k;Wdh) zeigt alle (k aus n) Kombinationen eines Vektors Arr (mit oder ohne Wdh.) in einer Liste
CRYPT (Text;Schlüssel;verschlüsseln) ver- und entschlüsselt Text mittels Schlüssel
HOLIDAYS (Jahr;Land) gibt die Feiertage eines dt. Bundeslandes in einer Spalte aus
LISTAND, LISTCOMPL, LISTOR, LISTXOR (Tab1;Tab2) gibt Schnitt, Komplement, Vereinigung oder symm.Differenz aus
PMT.PLAN (Zins;Zzr;Bw;ZW;F) ergibt einen Tilgungsplan zur Funktion RMZ (PMT) mit den Spalten Zinsen, Tilgung, Darlehensrest
RAND.UNIQ (Count;LBound;UBound) ergibt n [Count] ganzz. Zufallszahlen ohne Wdh. innerh. LBound und UBound
RECODE.2.5 (Text;kodieren) kodiert Text hin von 2 auf 5 und zurück von 5 auf 2 Zeichen
RESOURCE.HOURS (StartEndeArray;Bedingungen;Bedingung) addiert die Ressourcen-Belegung eines Kalendertages minutengenau
SORTBY.KEYRANGE (Arr;Keys;Header) sortiert Arr anhand Keys aus Bereich mit oder ohne Header
SPLIT (Txt;[Orientation;]Delimiter) teilt Txt anhand Delimiter in Hoch- oder Quer-Orientation
SUBSTITUTE.CHARS (Txt;Search;Replace) ersetzt Search- im Txt durch Replace-Zeichen (rekursiv)
SUBSTITUTE.STRINGS (Txt;Search-Replace-Arr) ersetzt Search-im Txt mit Replace-Zeichenfolgen
TILT (Arr;Kippfaktor;Filler) kippt die Unterseite eines Arr nach rechts und füllt leere Zellen.
TRANSPOSE8 (Arr;Zustand) rotiert ein Arr in 4 Richtungen, im Zustand normal oder revers
UNIQUE.PARTIAL (Arr;Keys;Debugging) pivotiert Arr anhand Spalten-Keys. Debugging 0-6.
UNPIVOT (Arr) macht aus einem vorgeschriebenen Kreuztabellen-Arr eine pivotierbare Liste
XYFUNCTIONS (X;Arr1;Arr2) ergibt ab X-Untergrenzen in Arr1 eine individuelle Steigung m in Arr2 (zu lokalem n=Y).

____________________________________________________

ACCUM, DECUM

Beschreibung:
Kumuliert oder entkumuliert einen Zeilen- oder Spaltenvektor.

ACCUM-Syntax:
ACCUM(Einzelwerte) in Zeile oder Spalte. Kumuliert einen Vektor.

Beispiel:
=ACCUM({1;2;5;3}) ergibt {1;3;8;11}

Code:
=LAMBDA(b;SCAN(;b;LAMBDA(c;a;a+c))) benannt als ACCUM

Hinweis:
Wird zur Differenzenbildung benötigt, die viel effektiver ist, als Summierung. Beispiel: Ablesetabellen für das Subtrahieren von Wahrscheinlichkeiten (z.B. Binomialverteilung).

DECUM-Syntax:
DECUM(kumulierteWerte) in Zeile oder Spalte. Entkumuliert einen Vektor.

Beispiel:
=DECUM({1;3;8;11}) ergibt {1;2;5;3}

Code:
=LAMBDA(x;LET(d;SEQUENZ(ZEILEN(x);SPALTEN(X));INDEX(x;d)-(d>1)*INDEX(x;d-1))) benannt als DECUM

Hinweis und Warnung:
Zeigt die Differenzen kumulierter Werte (Einzelwerte). Umkehrfunktion zu ACCUM. Achtung: Der Namensmanager erzeugt einen Fehler, wenn man DECUM mit einer anderen Excelfunktion auswertet. So erzeugt =SUMME(DECUM({1;3;8;11})) falsch -18 statt richtig 11. Die Einzelwerte sind alle Zahlenwerte (ISTZAHL WAHR). Wird die Funktion innerhalb eines LET in der Zelle benannt (also ohne Namensmanager), ist alles in Ordnung. Abhilfe erfolgt bis zu einer Fehlerbehebung durch MS leider nur so: =SUMME(--DECUM({1;3;8;11})). - Hinweis: Ich hätte gedacht, dass DECUM auch mit SCAN geht (dort: a-c). Vielleicht schafft es ja einer. Bei mir kam nur Müll raus.
____________________________________________________

AFA

Beschreibung:
Gibt die Jahres-AfA zu einem Wirtschaftsgut zurück

Syntax:
=AFA(AH-Datum;Nutzungsdauer;AH-Kosten;Betrachtetes_Jahr;AfA-Satz)

Beispiel:
=AFA("1.7.2007";10;100000;2007;9) ergibt 15000 (es galt in 2006+2007 3fache degressive AfA)
=AFA("1.7.2007";10;100000;2007;2,25) ergibt 11250 (die AfA wird jedoch auf 2,25 gedeckelt: Firmenentscheidung)
=AFA("1.7.2007";10;100000;2007;1) ergibt 5000 (lineare AfA: Firmenentscheidung)
=AFA("1.7.2008";10;100000;2008;9) ergibt 5000 (in 2008 keine degressive AfA)
=AFA("1.10.2009";10;100000;2009;9) ergibt 5625 (in 2009+2010 2,5fache degressive AfA, wegen Lehman)

Code:
=LAMBDA(AHDat;ND;AHK;Jahr;Satz;LET(
AHJ;JAHR(AHDat);
Anteil;(12-DATEDIF(MONATSENDE(AHDat;-1)+1;1&-(AHJ+1);"M"))/12;
WENNFEHLER(VDB(AHK;0;ND;
MAX(Jahr-0-AHJ-Anteil;0);
MIN(Jahr+1-AHJ-Anteil;ND);
MIN(Satz;VERWEIS(AHJ-2000;{-100;1;6;8;9;11;20;22};{3;2;3;1;2,5;1;2,5;1}))*
MIN(1/MAX(1;10/ND);10/ND));)))
____________________________________________________

ARRAY.COMBINE

Beschreibung:
Hängt Array2 unterhalb von Array1 an. Seit 16.3.22 ersetzbar durch Excel-VSTAPELN und HSTAPELN.

Syntax:
=ARRAY.COMBINE(Array1;Array2;Origin)

Beispiel:
=ARRAY.COMBINE(_Tab1;_Tab2;) gibt das gemeinsame Array zweier Arrays zurück
=ARRAY.COMBINE(_Tab1;_Tab2;1) gibt in einer extra Spalte ("Origin") die Nr. der Herkunftstabelle 1 oder 2 aus
=ARRAY.COMBINE(_Tab1;_Tab2;1,1) ergänzt die Herkunfts-Nr. um den relativen Satz darin (für stabile Rücksortierung)

Code:
=LAMBDA(a;b;o;LET(
x;ZEILEN(a);
y;SPALTEN(a);
z;ZEILEN(b);
k;SEQUENZ(x);
m;SEQUENZ(x+z);
n;SEQUENZ(;y+o);
WENN(m<=x;
WENN(n>y;1+(o=1,1)*k%%%;INDEX(a;k;n));
WENN(n>y;2+(o=1,1)*(m-x)%%%;INDEX(b;m-x;n)))))

Anmerkungen:
Ich habe mich gefragt, ob man Feldnamen (ja oder nein in den beiden Tabs - und ja oder nein am Ziel) benötigt, um z.B. eine Pivotquelle bereitstellen zu können. Ich denke, dass es kein Problem ist, wenn _Tab1 mit solchen Feldnamen mitgeliefert wird. Bei _Tab2 darf das natürlich nicht sein, aber man kann sie ja auch ohne solche übergeben.

Sollten die beiden Tabellen ungleich viele Spalten haben (eher untypisch und eigentlich im Vorfeld anzugleichen), empfiehlt sich bei meiner Lösung ein gleich breiter Bezug für beide (ggflls über Leerzellen). Ansonsten entsteht #BEZUG! als Warnung in den nicht einbezogenen Zellen.

office-hilfe.com/support/threads/36909#LIST enthält ein einzeiliges verkürztes ARRAY.COMBINE: ArC.
____________________________________________________

ARRAY.SHAKE

Beschreibung:
Schüttelt die Inhalte des Arrays zufällig neu (in gleicher Form).

Syntax:
=ARRAY.SHAKE(Array)

Beispiel:
q w e r t z u i o p Ausgangsarray
ü a s d f g h j k l
ö ä y x c v b n m ß

a c f i l o q ß ü x =ARRAY.SHAKE(Ausgangsarray)
ä d g j m ö r t v y
b e h k n p s u w z

Code:
=LAMBDA(a;LET(
x;ZEILEN(a);
y;SPALTEN(a);
z;ZUFALLSMATRIX(x*y);
v;VERGLEICH(z;SORTIEREN(z));
u;INDEX(v;SEQUENZ(x;;0)*y+SEQUENZ(;y));
INDEX(a;KÜRZEN((u+y-1)/y);REST(u-1;y)+1)))

____________________________________________________

ARRAY.SORT

Beschreibung:
Sortiert das Array in gleicher Form wie das Ausgangsarray,
1) die Einträge in Leserichtung (Zeile) oder in Telefonbuchrichtung (Spalte)
2) auf- oder abwärts.

Syntax:
=ARRAY.SORT(Array;Richtung;AufAb)

Beispiel:

q w e r t z u i o p unsortiertes Array
ü a s d f g h j k l
ö ä y x c v b n m ß

a c f i l o q ß ü x =ARRAY.SORT(A1:J3;;)
ä d g j m ö r t v y
b e h k n p s u w z

a ä b c d e f g h i =ARRAY.SORT(A1:J3;1;)
j k l m n o ö p q r
s ß t u ü v w x y z

z w u s p n k h e b =ARRAY.SORT(A1:J3;;-1)
y v t r ö m j g d ä
x ü ß q o l i f c a

z y x w v ü u t ß s =ARRAY.SORT(A1:J3;1;-1)
r q p ö o n m l k j
i h g f e d c b ä a

Code:
=LAMBDA(ii;y;x;LET(
s;SPALTEN(ii);
z;ZEILEN(ii);
a;SEQUENZ(z*s;;s);
b;SEQUENZ(z;;0);
c;SEQUENZ(;s;0);
INDEX(SORTIEREN(INDEX(ii;a/s;REST(a;s)+1);;x);WENN(y;b*s+REST(c;s)+1;c*z+REST(b;z)+1))))
____________________________________________________

COMBIN.LIST

Beschreibung:
Zu der Funktion =KOMBINATIONEN(n;k) erstellt =COMBIN.LIST() die Liste.

Syntax:
=COMBIN.LIST(Arr;k;Wdh)

Beispiel:
=COMBIN.LIST({-2.-1.1.9.3.4.7};4;) stellt die 4er-Kombinationen der nichtnegativen Einträge ohne Wiederholung in einer Liste dar. Besonderheit: Da das Array mit -2 und -1 beginnt, werden auch die 2er- und 3er-Kombinationen gezeigt.

Code:
=LAMBDA(Arr;k;Repeat;LET(
n;ANZAHL2(Arr);
y;SEQUENZ(n^k);
u;INDEX(Arr;REST((y-1)/n^(k-SEQUENZ(;k));n)+1);
q;WENN(ISTZAHL(u);WENNFEHLER(EXP(LN(u)););u);
EINDEUTIG(WENN(Repeat;q;FILTER(q;
WENNFEHLER(INDEX(u;y;1)<INDEX(u;y;2);1)*
WENNFEHLER(INDEX(u;y;2)<INDEX(u;y;3);1)*
WENNFEHLER(INDEX(u;y;3)<INDEX(u;y;4);1)*
WENNFEHLER(INDEX(u;y;4)<INDEX(u;y;5);1)*
WENNFEHLER(INDEX(u;y;5)<INDEX(u;y;6);1))))))

Anmerkungen:
VBA-Rekursionsalternative (Daniel/slowboarder) vom 28.2.2021. Diese hat im Gegensatz zu COMBIN.LIST mit maximal k=6 keine Beschränkung.
____________________________________________________

CRYPT

Beschreibung:
Verschlüsselt oder entschlüsselt einen Text mit Schlüssel

Syntax:
=CRYPT(Text;Schlüssel;verschlüsseln)

Beispiel:
=CRYPT("Das ist zu verschlüsseln";"This is a passphrase";1) ergibt: x©¼s²Æµp»ÈsÆÄ´¶ d¼Æ®¿¯
=CRYPT("x©¼s²Æµp»ÈsÆÄ´¶ d¼Æ®¿¯";"This is a passphrase";0) ergibt: Das ist zu verschlüsseln

Code:
=LAMBDA(c;k;x;LET(
d;WENN(SPALTEN( c)*ZEILEN( c)>1;MATRIXZUTEXT(WENN(c="";"";c);1);c);
e;SEQUENZ(LÄNGE(d));f;CODE(TEIL(d;e;1))-32;
l;WECHSELN(k;" ";);n;CODE(TEIL(l;REST(e-1;LÄNGE(l))+1;1))-32;
TEXTVERKETTEN(;;ZEICHEN(REST(f+n*(1-2*x);224)+32))))

Anmerkungen:
Ver- und Entschlüsselung eines Bezugs (auch Range) oder Texts, z.B.als E-Mail-Text. Verminderte Sicherheit besteht dadurch, dass ein bloßes Falschschreiben des Schlüssels noch zu lesbarer Entschlüsselung führen kann. Sonst müssten die einzelnen Schlüsselzeichen-Codes voneinander abhängig gemacht werden, was eine Excel-Iterationseinstellung erfordern würde - und solche Abhängigkeiten vermindern die Akzeptanz. Daher kommt alles auf den Gebrauch eines guten Schlüssels an, der selbst lang und verschlüsselt sein sollte (Ascii 32-255 nötig, wie auch beim Text). Im LAMBDA()-Code finden in der 2. Zeile eine Array-Berücksichtigung, in der 3. Zeile Text-Berechnungen, in der 4. Zeile Schlüssel-Berechnungen und in der 5. Zeile die Ver-/Entschlüsselung statt. Wird in der 5. Zeile in einer .xlsm/.xlsb AUSWERTEN() zusätzlich drumrum verwendet, wird gleich in ein Array entschlüsselt, falls gegeben.
____________________________________________________

HOLDIDAYS

Beschreibung:
Listet in einer Spalte alle Feiertage eines oder mehrerer Jahre und eines dt. Bundeslandes.

Syntax:
=HOLIDAYS(Jahr;Land)

Beispiel:
=HOLIDAYS(2017;"Brem") ergibt die 12 Feiertage von Bremen 2017
01.01.2017 14.04.2017 16.04.2017 17.04.2017 01.05.2017 25.05.2017
04.06.2017 05.06.2017 03.10.2017 31.10.2017 25.12.2017 26.12.2017
=HOLIDAYS(SEQUENZ(;2;2021);"MeVo") ergibt entsprechend die Feiertage 2021-2022 von Mecklenburg-Vorpommern

Code:
=LAMBDA(Jahr;Land;LET(
ArC;LAMBDA(a;b;LET( x;ZEILEN(a);y;SPALTEN(a);z;ZEILEN(b); k;SEQUENZ(x);m;SEQUENZ(x+z);n;SEQUENZ(;y); WENN(m<=x;INDEX(a;k;n);INDEX(b;m-x;n))));
EASTER;LAMBDA(Jahr;LET(
A;GANZZAHL(Jahr/100);
B;REST(19*REST(Jahr;19)+A-GANZZAHL(A/4)-GANZZAHL((A-GANZZAHL((A+8)/25)+1)/3)+15;30);
C;REST(32+2*REST(A;4)+2*GANZZAHL(REST(Jahr;100)/4)-B-REST(REST(Jahr;100);4);7);
D;B+C-7*GANZZAHL((REST(Jahr;19)+11*B+22*C)/451)+22;
E;DATUM(Jahr;WENN(D>31;4;3);WENN(D-31<1;D;D-31));E));
F;WAHL(SEQUENZ(7);-2;0;1;39;49;50;60)+EASTER(Jahr);
G;--(WAHL(SEQUENZ(11);"1.1";"6.1";"8.3";"1.5";"15.8";"20.9";"3.10";"31.10";"1.11";"25.12";"26.12")&"."&Jahr);
H;("25.12."&Jahr)-WOCHENTAG("25.12."&Jahr;2)-32;
I;{"1111111111111111";"1100000000000100";"0010000000000000";"1111111111111111";"1111111111111111";"1111111111111111";"1111111111111111";"1111111111111111";"1111111111111111";"1111111111111111";"1100001001110000";"0100000000010000";"0000000000000001";"1111111111111111";"0001110110001111";"1100000001110000";"0000000000001000";"1111111111111111";"1111111111111111"};
J;--TEIL(I;(3+SUCHEN(Land;"BaWüBayeBerlBranBremHambHessMeVoNiedNoWeRhPfSaarSachSaAnScHoThür"))/4;1);
K;FILTER(MTRANS(ArC(MTRANS(J);MTRANS(SORTIEREN(ArC(ArC(F;G);H)))));J);
L;INDEX(K;SEQUENZ(ZEILEN(K));SEQUENZ(;SPALTEN(K)-1;2));
M;EINDEUTIG(KKLEINSTE(L;SEQUENZ(ZEILEN(L)*SPALTEN(L))));
M))

Anmerkungen:

1. Sonntage sind enthalten, sowohl feste als auch variable. Denn Oster- und Pfingstsonntag ergeben oft Feiertags- statt Sonntagszuschlag, soweit vorhanden.
2. Mehrere Jahre müssen als Zeile(nvektor) eingegeben werden, wie im Beispiel "Mevo".
3. Die Bundesländer sind mit den ersten 4 Buchstaben einzugeben, Doppelnamen mit deren 2+2. Siehe Code.
4. Die Binärketten im Code könnte man um 11 Stück vermindern, da diese bundeseinheitlich 1 sind. Das ginge dann aber zu Lasten der Code-Ordnung.
5. Im Code sind zwei eigene LAMBDA-Funktionen enthalten: ArC (ARRAY.COMBINE) in einer schlanken Version und EASTER (von maninweb, nach Gauss).
6. Die einzelnen LET-Anweisungen erledigen folgendes:

A-E: Stufen innerhalb EASTER, siehe Link.
F: Liste der 7 von Ostern abhängigen FT: Karfr OsSo OsMo Himm PfSo PfMo Fronl
G: Liste der 11 fixen FT: Neuj 3Kö Frau 1Mai Mariä KindT 3Okt RefT AllH W1 W2
H: Ermittlung des Bußtags
I: 18 Binärketten, die die später sortierten FT den 16 Bundesländern zuordnen. Bayern und Thüringen haben dabei nur eine Kennzeichnung, obwohl dort kath. und ev. unterschieden wird.
J: Bildung des Spaltenvektors für das gewählte Bundesland "FT: ja (1) oder nein (0)"
K: Zuerst werden F-H untereinandergeklebt. Dann wird J als Spalte vorangeklebt. Dann wird nach J=1 gefiltert, so dass die unzutreffenden FT rausfallen.
L: J wird wieder entfernt, da die FT im Array nun endgültig bestimmt sind
M: Das Array (bei mehr als einem Jahr) wird zur Spalte umgeordnet, und der seltene Fall "1Mai=Himm" über EINDEUTIG entfernt.

Mit den gängigen Excelfunktionen kann man nun noch z.B. die SaSo-FT wegfiltern, oder den Anfang oder Ende genauer als ganze Jahre eingrenzen.
____________________________________________________

LISTAND, LISTCOMPL, LISTOR, LISTXOR

Beschreibung:
Gibt die zutreffenden Datensätze zu den Mengenbegriffen zweier Tabellen aus.

Syntax:
=LISTAND(a;b) (die anderen drei Funktionen genauso)

Beispiel:
Gegeben seien:
Einträge 1 2 3 8 5 6 7 in A2:A8
Einträge 5 6 7 4 9 1 in B2:B7
Daraus ergeben sich folgende Listen:
=LISTAND(A2:A8;B2:B7) ergibt 1 5 6 7 (Schnittmenge)
=LISTCOMPL(A2:A8;B2:B7) ergibt 2 3 8 (Restmenge A bzw. Komplement zu B)
=LISTCOMPL(B2:B7;A2:A8) ergibt 4 9 (Restmenge B bzw. Komplement zu A)
=LISTOR(A2:A8;B2:B7) ergibt 1 2 3 8 5 6 7 4 9 (Vereinigungsmenge)
=LISTXOR(A2:A8;B2:B7) ergibt 2 3 8 4 9 (Symmetrische Differenz bzw. exkl. Disjunktion)

Code LISTAND (Schnittmenge bzw. logische Konjunktion):
=LAMBDA(a;b;LET(
ArC;LAMBDA(a;b;LET(x;ZEILEN(a);y;SPALTEN(a);z;ZEILEN(b); k;SEQUENZ(x);m;SEQUENZ(x+z);n;SEQUENZ(;y); WENN(m<=x;INDEX(a;k;n);INDEX(b;m-x;n))));
x;ArC(a;b);EINDEUTIG(ArC(EINDEUTIG(x);EINDEUTIG(x;;1));;1)))

Code LISTCOMPL (Restmenge A bzw. Komplement zu B):
=LAMBDA(a;b;LET(
ArC;LAMBDA(a;b;LET(x;ZEILEN(a);y;SPALTEN(a);z;ZEILEN(b); k;SEQUENZ(x);m;SEQUENZ(x+z);n;SEQUENZ(;y); WENN(m<=x;INDEX(a;k;n);INDEX(b;m-x;n))));
x;ArC(a;b);EINDEUTIG(ArC(a;EINDEUTIG(ArC(EINDEUTIG(x);EINDEUTIG(x;;1));;1));;1)))

Code LISTOR (Vereinigungsmenge bzw. logische Disjunktion):
=LAMBDA(a;b;LET(
ArC;LAMBDA(a;b;LET(x;ZEILEN(a);y;SPALTEN(a);z;ZEILEN(b); k;SEQUENZ(x);m;SEQUENZ(x+z);n;SEQUENZ(;y); WENN(m<=x;INDEX(a;k;n);INDEX(b;m-x;n))));
EINDEUTIG(ArC(a;b))))

Code LISTXOR (Symmetrische Differenz bzw. exklusive Disjunktion):
=LAMBDA(a;b;LET(
ArC;LAMBDA(a;b;LET(x;ZEILEN(a);y;SPALTEN(a);z;ZEILEN(b); k;SEQUENZ(x);m;SEQUENZ(x+z);n;SEQUENZ(;y); WENN(m<=x;INDEX(a;k;n);INDEX(b;m-x;n))));
EINDEUTIG(ArC(a;b);;1)))

Anmerkungen:
Die 4 Funktionen enthalten ARRAY.COMBINE in der Kurzform ArC und sind daher allein funktionsfähig. EDIT 10.3.: ArC kann nun durch das interne VSTAPELN ersetzt werden. Damit werden die Funktionen mehr als 50% kürzer.
____________________________________________________

PMT.PLAN

Beschreibung:
gibt einen Tilgungsplan mit den Parametern der Funktion RMZ (engl. PMT()) zurück. Er umfasst 3 Spalten (Zinsen, Tilgung, Darlehensrest) mit der Anzahl Zeilen entsprechend Zzr.

Syntax:
=PMT.PLAN(Zins;Zzr;Bw;Zw;F)

Beispiel:
=PMT.PLAN(3%;12;100000;;) ergibt

3.000,00 7.046,21 92.953,79
2.788,61 7.257,59 85.696,20
2.570,89 7.475,32 78.220,87
2.346,63 7.699,58 70.521,29
2.115,64 7.930,57 62.590,72
1.877,72 8.168,49 54.422,23
1.632,67 8.413,54 46.008,69
1.380,26 8.665,95 37.342,75
1.120,28 8.925,93 28.416,82
0.852,50 9.193,70 19.223,12
0.576,69 9.469,52 09.753,60
0.292,61 9.753,60 -

Code:
=LAMBDA(zins;zzr;bw;zw;f;LET(rmz;RMZ(zins;zzr;-bw;zw;f);lft;SEQUENZ(zzr);
x;SCAN(bw;lft;LAMBDA(cum;arr;cum*(1+zins)-rmz));
z;zins*WENN(lft=1;bw;INDEX(x;lft-1));
t;rmz-z;
WAHL({1.2.3};z;t;x)))

Anmerkungen:
SCAN sorgt zunächst für die erforderliche Fortschreibung des Restdarlehens, wobei BW als der Accumulator-Start gilt. Da sich in der Spalte Zinsen nicht auf den Accumulator-Start der SCAN-Spalte bezogen werden kann (sondern nur auf den ersten ermittelten Wert), werden deren Werte um 1 Zeile verschoben, und in der 1. Zeile wird der fehlende Wert errechnet.
____________________________________________________

RAND.UNIQ

Beschreibung:
gibt ganzzahlige Zufallszahlen ohne Wiederholung innerhalb von Grenzen als Spaltenvektor zurück. Ähnlich: ARRAY.SHAKE mischt bestehende (auch: Text, nicht nur Zahl) Einträge eines Arrays (auch: Matrix, nicht nur Vektor).

Syntax:
=RAND.UNIQ(n;unten;oben)

Beispiel:
=RAND.UNIQ(8;3;10) könnte z.B. {7;5;3;10;6;8;9;4} zurückgeben, genau wie =ARRAY.SHAKE(ZEILE(3:10))
=RAND.UNIQ(8;1;10) lässt zwei Zahlen zwischen 1 und 10 aus
=RAND.UNIQ(8;1;7) kann nur 7 Zahlen finden; die 8. wird daher zu #BEZUG!
=KÜRZEN(RAND.UNIQ(10;6;32)/3) gibt 10 Zahlen aus 2 bis 10 zurück, wobei bis zu 3 Wiederholungen möglich sind.
=VERGLEICH(RAND.UNIQ(8;1;8);{1.4.7.8})-2 gibt 8 Zahlen von -1 bis 2 mit Mittelwert 0 zurück, davon 3*(-1), 3*(0), 1*(1) und 1*(2), also als eine zwar um 0 normierte und gewichtete, jedoch nicht symmetrische Verteilung (3 Minuswerte, 2 Pluswerte).

Code:
=LAMBDA(n;u;o;LET(
z;ZUFALLSMATRIX(o-u+1);
VERGLEICH(INDEX(z;SEQUENZ(n));SORTIEREN(z))+u-1))

Anmerkungen:
Hier noch zwei kürzere Lösungen von RPP63:
=LAMBDA(n;u;o;INDEX(SORTIERENNACH(SEQUENZ(o-u+1);ZUFALLSMATRIX(o-u+1));SEQUENZ(n)))(6;1;49) (nicht fortlaufende Reihe einmaliger Zahlen, geht natürlich auch ohne LAMBDA)
=SORTIERENNACH(SEQUENZ(Endzahl);ZUFALLSMATRIX(Endzahl)) (fortlaufend ab 1)
____________________________________________________

RECODE.2.5

Beschreibung:
Kodiert je ein Zeichenpaar eines Strings (aus 224 Zeichen von 32-255) in 5 Ziffern 1-9 plus Leerzeichen oder zurück

Syntax:
=RECODE.2.5(Text;kodieren)

Beispiel:
=RECODE.2.5("Das ist umzukodieren";1) ergibt: 23243 38558 35592 38837 39312 41795 36245 33996 34385 34381
=RECODE.2.5("23243 38558 35592 38837 39312 41795 36245 33996 34385 34381";0) ergibt: Das ist umzukodieren

Code:
=LAMBDA(Code;Recode;WENN(Recode;
LET(x;Code&" ";TEXTVERKETTEN(" ";;BASIS(
(CODE(TEIL(x;SEQUENZ(LÄNGE(x)/2)*2-1;1))-32)*224+
(CODE(TEIL(x;SEQUENZ(LÄNGE(x)/2)*2-0;1))-32);9)+11111));
LET(y;Code;z;DEZIMAL(TEIL(y;SEQUENZ(LÄNGE(y&" ")/6;;1;6);5)-11111;9);
TEXTVERKETTEN(;;ZEICHEN(z/224+32)&ZEICHEN(REST(z;224)+32)))))

Anmerkungen:
Ich habe die relativ "teure" Umkodierung von je 2 (aus 224) Zeichen in mit Leerzeichen getrennte 5stellige Zahlen ohne enthaltene Null (also 9 Zeichen) gewählt, damit sie in einem festen Rhythmus auch mündlich durchgegeben werden könnten. Eine günstigere Möglichkeit wäre die Umwandlung von je 3 Ursprungszeichen in 4 Zeichen aus 58, also 0-9, A-X und a-x.
____________________________________________________

RESOURCE.HOURS

Beschreibung:
Addiert die an der Ressource in Anspruch genommene Zeit als eine Dauer, auch wenn die Ressource darin mehrfach beansprucht wird. Beispiel: Taxi mit mehreren Einzelzahlern gleichzeitig, Maschine mit mehreren Produktionswegen.

Syntax:
=RESOURCE.HOURS(StartEndeArray;Bedingungen;Bedingung)

Beispiel:
1 09:00 10:00
1 10:30 11:00
2 09:30 13:22
1 10:50 13:10
=RESOURCE.HOURS(B$1:C$4;A$1:A$4;ZEILE(A1)) ergibt: 03:40
=RESOURCE.HOURS(B$1:C$4;A$1:A$4;ZEILE(A2)) ergibt: 03:52

Code:
=LAMBDA(SE;Conds;Cond;LET(i;1440;
SUMME(--(NACHSPALTE(SEQUENZ(;i)/i;LAMBDA(b;
SUMME((Conds=Cond)*(NACHZEILE(INDEX(SE;;2)*i+INDEX(SE;;1);
LAMBDA(a;MAX(;MIN(KÜRZEN(a)/i;b)-MAX(REST(a;1);b-1/i))))>0))/i))>0))/i))

Anmerkungen:
Die Endzeit darf nicht größer als 24:00 sein. Sie ist darauf herunterzukürzen. Denn sonst müssten Kalendertage mit 48:00 (oder länger) statt 24:00 gelten und auch Endzeiten nach diesem Muster geschrieben werden, da sie sonst mit gleichzeitigen Vorgängen des Folgetages kollidieren. Die Spalte Bedingungen könnte entweder im Code auf mehrere erweitert werden, oder sie fasst mit TEXTVERKETTEN mehrere Spalten als eine zusammen.
____________________________________________________

SORTBY.KEYRANGE

Beschreibung:
sortiert einen Range nach Zeilen über eine Array-Definition anstelle über bis zu 9 Einzelargumente.

Syntax:
=SORTBY.KEYRANGE(Arr;Keys;Header)

Beispiel:

Code:

Getränk Farbe Geschmack    Herkunft            A1:D10: Array
Wein    rot   süß          Frankreich
Wein    rot   süß          Deutschland
Wein    rot   trocken      Frankreich
Wein    rot   trocken      Deutschland
Wein    weiß  halbtrocken  Frankreich
Wein    weiß  halbtrocken  Deutschland
Wein    weiß  trocken      Frankreich
Wein    weiß  trocken      Deutschland
Bier    hell  hopfig       Belgien
                                               A12: Keys (nur erste Zelle A12 als Argument angeben!)
1       2     4            3                        (dass hier 4 genannt werden, ist nur Zufall)
        1                  1                        Bei A12 muss A12:I13 reserviert sein.
 
Getränk Farbe Geschmack    Herkunft            A15: =SORTBY.KEYRANGE(A1:D10;A12;1)
Bier    hell  hopfig       Belgien
Wein    weiß  trocken      Deutschland
Wein    weiß  halbtrocken  Deutschland
Wein    weiß  trocken      Frankreich
Wein    weiß  halbtrocken  Frankreich
Wein    rot   trocken      Deutschland
Wein    rot   süß          Deutschland
Wein    rot   trocken      Frankreich
Wein    rot   süß          Frankreich

Code:
=LAMBDA(Array;Keys;Header;LET([/COLOR]
a;Array;
p;Keys;
t;Header;
r;(1-(SEQUENZ(2)=2)*(ZEILEN(p)=1))*p;
offs;WENNFEHLER(r+{0.0.0.0.0.0.0.0.0;0.0.0.0.0.0.0.0.0};0);
h;WENN(SEQUENZ(2)=2;1-offs*2;offs);
L;SEQUENZ(ZEILEN(a));
m;SEQUENZ(ZEILEN(a)-t;;1+t);
n;SEQUENZ(;SPALTEN(a));
o;SEQUENZ(;SPALTEN(h));
j;INDEX(h;1;o);
jj;INDEX(j;1);
k;INDEX(h;2;o);
kk;INDEX(k;1);
s;SORTIERENNACH(INDEX(a;m;n);
INDEX(a;m;WENNFEHLER(1/(1/INDEX(j;1));jj));WENN(ISTFEHLER(INDEX(j;1));kk;INDEX(k;1));
INDEX(a;m;WENNFEHLER(1/(1/INDEX(j;2));jj));WENN(ISTFEHLER(INDEX(j;2));kk;INDEX(k;2));
INDEX(a;m;WENNFEHLER(1/(1/INDEX(j;3));jj));WENN(ISTFEHLER(INDEX(j;3));kk;INDEX(k;3));
INDEX(a;m;WENNFEHLER(1/(1/INDEX(j;4));jj));WENN(ISTFEHLER(INDEX(j;4));kk;INDEX(k;4));
INDEX(a;m;WENNFEHLER(1/(1/INDEX(j;5));jj));WENN(ISTFEHLER(INDEX(j;5));kk;INDEX(k;5));
INDEX(a;m;WENNFEHLER(1/(1/INDEX(j;6));jj));WENN(ISTFEHLER(INDEX(j;6));kk;INDEX(k;6));
INDEX(a;m;WENNFEHLER(1/(1/INDEX(j;7));jj));WENN(ISTFEHLER(INDEX(j;7));kk;INDEX(k;7));
INDEX(a;m;WENNFEHLER(1/(1/INDEX(j;8));jj));WENN(ISTFEHLER(INDEX(j;8));kk;INDEX(k;8));
INDEX(a;m;WENNFEHLER(1/(1/INDEX(j;9));jj));WENN(ISTFEHLER(INDEX(j;9));kk;INDEX(k;9)));
u;WENN(t;WENN(L=1;INDEX(a;1;n);INDEX(s;L-1;n));s);
u))
____________________________________________________

SPLIT

Beschreibung:
trennt Strings am Trennzeichen nach rechts oder unten auf. Ein zu TEXTVERKETTEN benötigtes Gegenstück. Seit 16.3.22 ersetzbar durch Excel-TEXTTEILEN.

Syntax:
=SPLIT(Text;quer;Trennzeichen)

Beispiel:
=SPLIT("Dies! ist! kein! Eipott!";1;"! ") ergibt {"Dies"."ist"."kein"."Eipott!"}
=SPLIT("Default quer=0 nach unten und Leerz. als Default Trennz.";;) ergibt {"Default";"quer=0";"nach";"unten";"und";"Leerz.";"als";"Default";"Trennz."}

Code:
=LAMBDA(Txt;quer;Trenn;LET([/COLOR]
Txt;Txt;
quer;quer;
y;Trenn;
x;XMLFILTERN("<a><b>"&WECHSELN(Txt;WENN(y="";" ";y);"</b><b>")&"</b></a>";"//b");
WENN(quer;MTRANS(x);x)))

Code 2 (hier mit Txt x als Argument und [tt] als optionalem Argument, Leerzeichen annehmend, sowie Autoerkennung Zeile oder Spalte)
=LAMBDA(x;[tt];LET(
t;WENN(ODER(WURDEAUSGELASSEN(tt);tt="");" ";tt);
u;ZEICHEN(1);
y;MAX(LÄNGE(x)-LÄNGE(WECHSELN(x;t;))+1);
z;WENN(SPALTEN(x)=1;SEQUENZ(;y);SEQUENZ(y));
f;LAMBDA(g;FINDEN(u;WECHSELN(t&x&u;t;u;z+g)));
WECHSELN(TEIL(x;f(0);f(1)-f(0));t;)))

Code 3 (hier mit Txt x als Argument und z als anzugebendes Trenn-Argument, sowie Autoerkennung Zeile oder Spalte)
=LAMBDA(x;z;LET(
v;MAX(LÄNGE(x)-LÄNGE(WECHSELN(x;z;)))+1;
y;WENN(SPALTEN(x)=1;SEQUENZ(;v);SEQUENZ(v));
WENNFEHLER(TEXTNACH(TEXTVOR(z&x&z;z;y+1);z;y);"")))

Anmerkungen:
Auf den XMLFILTERN-Teil wird sich hier bezogen. - Der Code 2 nimmt das Leerzeichen als Trenner an und erlaubt (wie Code 3) auch Vektoren anstelle von Einzelzellen.

Aus A1:A2:
Abc de fg
Hi jk wird damit
Abc de fg
Hi_ jk

Aus A1:B1:
Abc de fg
Hi jk wird damit
Abc hi
de_ jk
fg
____________________________________________________

SUBSTITUTE.CHARS

Beschreibung:
wechselt einzelne Zeichen nacheinander aus. Für Zeichenketten siehe SUBSTITUTE.STRINGS.

Syntax:
=SUBSTITUTE.CHARS(Text;Suchzeichen;Ersetzzeichen)

Beispiel:
=SUBSTITUTE.CHARS("ABCDEFGH";"ACEx";"xACE") ergibt EBADCFGH ("ACE" werden hier mit Hilfe von x durchgeswapt)

Code:
=LAMBDA(textString;changeChars;replaceChars;
WENN(changeChars=""; textString;SUBSTITUTE.CHARS(
WECHSELN(textString; LINKS(changeChars; 1); LINKS(replaceChars; 1));
RECHTS(changeChars; LÄNGE(changeChars)-1);
RECHTS(replaceChars; LÄNGE(replaceChars)-1))))

Anmerkungen:
Rekursiv. Verzichtet man auf LET, wird die Funktion recht einfach und kurz.
____________________________________________________

SUBSTITUTE.STRINGS

Beschreibung:
wechselt Zeichenketten einer 2-Spalten-Zuordnung nacheinander aus. Für einzelne Zeichen siehe SUBSTITUTE.CHARS.

Syntax:
=SUBSTITUTE.STRINGS(Text;Suchtext-Ersetztext-Tabelle)

Beispiel:
=SUBSTITUTE.STRINGS("ABCDEFG";{"ABC"."WXYZ";"ZDE"."PQRSTUVW";"".""}) ergibt WXYPQRSTUVWFG.
Achtung: Die abschließende Leerzeile in der Tabelle wird aufgrund der sehr kurzen Rekursion benötigt!

Code:
=LAMBDA(txt;chngrplc;
WENN(ZEILEN(chngrplc)=1;txt;SUBSTITUTE.STRINGS(
WECHSELN(txt;INDEX(chngrplc;1;1);INDEX(chngrplc;1;2));
INDEX(chngrplc;SEQUENZ(ZEILEN(chngrplc)-1;;2);{1.2}))))

Anmerkungen:
Rekursiv.
____________________________________________________

TILT

Beschreibung:
kippt ein Array von senkrecht in die Ausrichtung eines Backslashes, also von links oben nach rechts unten.

Syntax:
=TILT(Array;Kippfaktor;Füllzeichen)

Beispiel:
=TILT({1.2;3.4;5.6};1;) kippt das Array zu {1.2.""."";"".3.4."";""."".5.6} (Steigung 100% von der Vertikalen aus)
=TILT({1.2;3.4;5.6;7.8;9.10;11.12;13.14;15.16};1/3;) kippt mit Steigung 33,33% von der Vertikalen aus

Code:
=LAMBDA(Arr;Tlt;Show;
WENNFEHLER(INDEX(Arr;SEQUENZ(ZEILEN(Arr));
WENNFEHLER(EXP(LN(KÜRZEN(SEQUENZ(;SPALTEN(Arr)+(ZEILEN(Arr)-1)*Tlt;2)-SEQUENZ(ZEILEN(Arr))*Tlt)));-1) );
WENN(Show="";"";Show)))

Anmerkungen:
Der 0>Kippfaktor>=1 (engl. TILT) sollte 1 oder den Kehrwert einer ganzen positiven Zahl betragen (also etwa 0,5, 1/3 oder z.B. 1/7), damit alle Inhalte abgebildet werden (sonst ggflls Rundungsdifferenzen bei der Zellzuweisung). 0 ist ungünstig; man kann stattdessen aber 1%%%% verwenden. - In Verbindung mit TRANSPOSE8 lassen sich auch alle anderen Kipprichtungen erreichen. Wird Füllzeichen belegt, wird es in den durch das Kippen freien Zellen des vergrößerten Arrays angezeigt, sonst einfach ein Leerzeichen, wie im Beispiel.
____________________________________________________

TRANSPOSE8

Beschreibung:
rotiert ein Array um 90°, 180° und 270° Grad und spiegelt es. Insgesamt gibt es 8 verschiedene Zustände, wenn man das Ausgangsarray mitzählt.

Syntax:
=TRANSPOSE8(Array;Zustand)

Beispiel:
=TRANSPOSE8({1.2.3;4.5.6};1) spiegelt Spalten des Arrays zu {3.2.1;6.5.4}
=TRANSPOSE8({1.2.3;4.5.6};3) rotiert Array um 180° zu {6.5.4;3.2.1}

Code:
=LAMBDA(Array;Zustand;LET(
Arr;Array;
z;Zustand;
x;ZEILEN(Arr);
y;SPALTEN(Arr);
i;SEQUENZ(x);
j;SEQUENZ(;y);
m;x+1-i;
n;y+1-j;
ww;WAHL(REST(z;4)+1;INDEX(Arr;i;j);INDEX(Arr;i;n);INDEX(Arr;m;j);INDEX(Arr;m;n));
WENN(REST(z;8)>3;MTRANS(ww);ww)))

Anmerkungen:
Echtes Drehen des Arrays geschieht in der Reihenfolge der Zustände (relativ zum Ausgangsarray) 0-6-3-5-0 bzw. 1-7-2-4-1.
____________________________________________________

UNIQUE.IDENT

Beschreibung:
Unterscheidet bei UNIQUE (EINDEUTIG) Groß- und Kleinschreibung. Zunächst nur als Einzelspalte!

Syntax:
=UNIQUE.IDENT(Spaltenvektor)

Beispiel:
Wilhelm
Maria
wilhelm
Maria
Wilhelm
maria

ergibt

Wilhelm
Maria
wilhelm
maria

Code:
=LAMBDA(f;WEGLASSEN(EINDEUTIG(HSTAPELN(f;
NACHZEILE(f;LAMBDA(f;PRODUKT(LN(CODE(TEIL(f;SEQUENZ(LÄNGE(f));1)))*LN(SEQUENZ(LÄNGE(f))+1)/10)))));;-1))(B1:B100000)
____________________________________________________

UNIQUE.PARTIAL

Beschreibung:
beschränkt die Excel-Funktion EINDEUTIG auf einzelne ausgewählte Spalten des Arrays. Zeigt auf Wunsch trotzdem alle Spalten des ersten übereinstimmenden Datensatzes an. Teilt auf Wunsch die Häufigkeiten der vorherigen Duplikate des Arrays mit (ähnlich Pivot-Anzahl).

Syntax:
=UNIQUE.PARTIAL(Array;Keys;Debugging)

Beispiel:

Code:

Getränk Farbe Geschmack    Herkunft            A1:D10: Array
Wein    rot   süß          Frankreich
Wein    rot   süß          Frankreich
Wein    rot   trocken      Deutschland
Wein    rot   trocken      Deutschland
Wein    weiß  halbtrocken  Deutschland
Wein    weiß  halbtrocken  Deutschland
Wein    weiß  trocken      Frankreich
Wein    weiß  trocken      Deutschland
Bier    hell  hopfig       Belgien

Getränk Herkunft    Farbe  1                   A12: =UNIQUE.PARTIAL(A1:D10;{1.4.2};0)
Wein    Frankreich  rot    2
Wein    Deutschland rot    2
Wein    Deutschland weiß   3
Wein    Frankreich  weiß   1
Bier    Belgien     hell   1

Getränk Farbe Geschmack    Herkunft     1      A12: =UNIQUE.PARTIAL(A1:D10;{1.4};2)
Wein    rot   süß          Frankreich   3
Wein    rot   trocken      Deutschland  5
Bier    hell  hopfig       Belgien      1

Code:
=LAMBDA(Array;Keys;Debugging;LET(
a;Array;
b;Keys;
z;Debugging;
q;SPALTEN(a);
s;SPALTEN(b);
x;SEQUENZ(ZEILEN(a));
c;WENN(SEQUENZ(;q+1)>q;x;a);
d;SORTBY.KEYRANGE(c;b;0);
e;WENN(SEQUENZ(;q+2)>q+1;1-(x>1)*
WENNFEHLER((INDEX(d;x-1;INDEX(b;1))=INDEX(d;x;INDEX(b;1)));1)*
WENNFEHLER((INDEX(d;x-1;INDEX(b;2))=INDEX(d;x;INDEX(b;2)));1)*
WENNFEHLER((INDEX(d;x-1;INDEX(b;3))=INDEX(d;x;INDEX(b;3)));1)*
WENNFEHLER((INDEX(d;x-1;INDEX(b;4))=INDEX(d;x;INDEX(b;4)));1)*
WENNFEHLER((INDEX(d;x-1;INDEX(b;5))=INDEX(d;x;INDEX(b;5)));1)*
WENNFEHLER((INDEX(d;x-1;INDEX(b;6))=INDEX(d;x;INDEX(b;6)));1)*
WENNFEHLER((INDEX(d;x-1;INDEX(b;7))=INDEX(d;x;INDEX(b;7)));1)*
WENNFEHLER((INDEX(d;x-1;INDEX(b;8))=INDEX(d;x;INDEX(b;8)));1)*
WENNFEHLER((INDEX(d;x-1;INDEX(b;9))=INDEX(d;x;INDEX(b;9)));1);d);
v;INDEX(e;ZEILEN(d)+1-x;SEQUENZ(;q+2));
u;WENN(SEQUENZ(;q+3)>q+2;x;v);
f;FILTER(u;INDEX(u;x;q+2)=1);
y;SEQUENZ(ZEILEN(f));
t;WENN(SEQUENZ(;q+4)>q+3;INDEX(f;y;q+3)-(y>1)*INDEX(f;y-1;q+3);f);
g;SORTBY.KEYRANGE(t;q+1;0);
h;INDEX(g;y;WENN(SEQUENZ(;q+1)=q+1;SPALTEN(g);SEQUENZ(;q)));
i;INDEX(h;y;WENN(SEQUENZ(;s+1)=s+1;SPALTEN(h);b));
j;INDEX(i;y;SEQUENZ(;s));
WAHL(z+1;i;j;h;g;t;f;u)))

Anmerkungen:
Die Funktion verwendet die LAMBDA-Funktion SORTBY.KEYRANGE.
____________________________________________________

UNPIVOT

Beschreibung:
bringt eine Kreuztabelle zurück in eine normale pivotierbare Tabelle. Die Kreuztabelle entspricht dabei folgendem Layout:

Code:

   B B1 B2 B3
A  C
A1    1     2
A2       3
A3    4  5
A4       6  7

und bewirkt:

Code:

A  B  C
A1 B1 1
A1 B3 2
A2 B2 3
A3 B1 4
A3 B2 5
A4 B2 6
A4 B3 7

Syntax:
=UNPIVOT(Kreuztabelle)

Beispiel:
=UNPIVOT(A1:E6) für das Beispiel oben

Code:
=LAMBDA(a;LET(
y;SPALTEN(a)-1;
x;y*ZEILEN(a)-1;
i;SEQUENZ(x;;y);
m;i/y;
n;REST(i;y)+1;
Werte;INDEX(BEREICH.VERSCHIEBEN(INDEX(a;2;2);;;x;y);m;n);
FILTER(WAHL(SEQUENZ(;3);
INDEX(BEREICH.VERSCHIEBEN(INDEX(a;2;1);;;x;1);m);
INDEX(BEREICH.VERSCHIEBEN(INDEX(a;1;2);;;1;y);n);
Werte);Werte<>"")))

Anmerkungen:
Gegenstück könnte CROSSTABLE werden. Das gab es schon mal in Excel 4 (1994).
____________________________________________________

XYFUNCTIONS

Beschreibung:
hängt mehrere lineare Funktionen hintereinander an, auf Höhe des jeweiligen Y-Werts des Vorgängers.

Syntax:
=XYFUNCTIONS(X;XArr;MArr)

Beispiel:
=XYFUNCTIONS(40000;{0;11;18;31;60;90;1000}*1000;{0;20;35;42;48;50;55}%) ergibt € 9.730 Steuer für € 40.000 Einkommen in Österreich.

Code:
=LAMBDA(X;XArr;MArr;LET(
y;SEQUENZ(ZEILEN(MArr)+1);
z;WENN(y>1;INDEX(MArr;y-1);0);
SUMME((X>XArr)*(X-XArr)*(
INDEX(z;SEQUENZ(ZEILEN(z)-1;;2))-
INDEX(z;SEQUENZ(ZEILEN(z)-1;;1))))))

Dokumentation:
=LAMBDA(X-Eingabewert;X-Abschnittsuntergrenzen;m-Abschnittssteigungen;LET(
y;Erhöhen des Abschnittssteigungen-Arrays um 1 Eintrag;
z;Erster Eintrag in dem Array wird Null und die anderen rutschen einen hoch;
Lupo1-Provisions-Formel))

Anmerkungen:
Anwendung z.B. auch für ALG II, Formel 1-Punkte, Provisionsberechnungen.
____________________________________________________
gesammelte Formeln, noch mit Artikel zu versehen:

Wiederholer-Liste
(Office 2019) - benachbarte gleiche Werte (Blöcke) in einer Zeile zählen
=LET(
j;A1:AE1;
i;LAMBDA(x;LET(d;SEQUENZ(ZEILEN(x);SPALTEN(x));INDEX(x;d)-(d>1)*INDEX(x;d-1)));
x;VSTACK(MTRANS(j);"");
y;DROP(WAHL({1.2.3};x;SEQUENZ(ZEILEN(x));DROP(x;-1)<>DROP(x;1));-1);
z;FILTER(y;DROP(y;;2));
w;WAHL({1.2};DROP(z;;-2);i(INDEX(z;;2)));
w)

Zeitenüberlappungen minütlich über 10 Tage (Treppen-Diagramm)
Summenstatistik mit Zeiträumen
=LET(
i;1440;
j;SEQUENZ(;i*10)/i;
k;j-1/1440;
x;A2:B10;
y;DROP(x;;-1);
z;DROP(x;;1);
s;WENN(j<z;j;z)-WENN(k>y;k;y);
l;NACHSPALTE(s;LAMBDA(a;RUNDEN(SUMME(WENN(a<0;0;a)*i);)));
m;MTRANS(WAHL({1;2;3};k;l;l<>WENN(RUNDEN(j*i;)=1;0;INDEX(l;SEQUENZ(;i*10-1;0)))));
f;FILTER(DROP(m;;-1);WENNFEHLER(AUFNEHMEN(m;;-1);));
g;WAHL({1.2};DROP(f;;-1);WENN(SEQUENZ(ZEILEN(DROP(f;;1)))=1;0;INDEX(DROP(f;;1);SEQUENZ(ZEILEN(DROP(f;;1));;0))));
SORTIEREN(VSTACK(g;f)))

2 Teiler mit Anzahl für Summe A2: Summe, B2: Faktor1, C2: Faktor2. Dann B3#:
Office Forum • Thema anzeigen - Anzahl in definierte Teile zerlegen
=LET(x;KÜRZEN(A2/B2);y;SEQUENZ(x+1;;x;-1);z;WAHL({1.2};y;(A2-y*B2)/C2);FILTER(z;(REST(DROP(z;;-1);1)=0)*(REST(DROP(z;;1);1)=0)))

Zeige Datum aus Text, sonst #ZAHL! (für SPLIT als Beispiel)
Office Forum • Thema anzeigen - Wenn in Spalte A ein Datum im Text, dann Spalte B nur Datum
=EXP(LN(MAX(WENNFEHLER(--GLÄTTEN(TEIL(WECHSELN(A1;" ";WIEDERHOLEN(" ";999));SPALTE(A:Z)*999-998;999));0))-40000))+40000

Autorenliste (als SPLIT-Beispiel)
Excel 2019 - Erstellen Sie eine Power-Query-Abfrage, die eine Spalte nach doppelten Instanzen x4 d - MS-Office-Forum
Meyer
Müller, Meyer
Schulz, Müller
Meyer, Schulz, Schmidt
Schmidt
=SORTIEREN(LET(x;TOCOL(SPLIT(A1:A5;", "));FILTER(x;x<>"")))
Meyer
Meyer
Meyer
Müller
Müller
Schmidt
Schmidt
Schulz
Schulz

Q (UADRANT):
=LAMBDA(x;[qq];[yy];[zz];LET(
q;WENN(WURDEAUSGELASSEN(qq);4;qq);
y;WENN(WURDEAUSGELASSEN(yy);1;yy);
z;WENN(WURDEAUSGELASSEN(zz);1;zz);
a;ZEILEN(x);
b;SPALTEN(x);
WAHL(q;
INDEX(x;1;1):INDEX(x;y;z);
INDEX(x;1;1+z):INDEX(x;y;b);
INDEX(x;y+1;1):INDEX(x;a;z);
INDEX(x;y+1;z+1):INDEX(x;a;b))))

=Q(A1:G9) ergibt B2:G9