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: 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, wohin die UDF-Namen-Links zeigen. Die Funktionen mit selbst folgen unten alphabetisch nach der Liste.

CEF ARRAY.APPEND (maninweb) (Arr1;Arr2;Default) ähnlich wie ARRAY.COMBINE
CEF ARRAY.COMBINE (LCohen) (Arr1;Arr2;Origin) hängt Arr2 unter Arr1 und liefert als Origin wahlw. Tabellen-Nr mit/ohne Satz-Nr
CEF ARRAY.CREATE (maninweb) (Arr1;Arr2;Default) Code und Beschreibung und/oder Link folgen
CEF ARRAY.PUSH (maninweb) (Arr1;Default) Beschreibung und/oder Link folgt
CEF ARRAY.REMOVE.FIRSTN (maninweb) in Anlehnung an PowerQuery-M
CEF ARRAY.REMOVE.LASTN (maninweb) in Anlehnung an PowerQuery-M
CEF ARRAY.REVERSE (maninweb) (Arr) dreht die letzte Zeile von Arr nach oben
CEF ARRAY.SHAKE (LCohen) (Arr) verteilt Arr-Inhalte zufällig neu (1:1 je genau einmal)
CEF EASTER.GAUSS (maninweb) (Year) errechnet den Ostersonntag vom Year nach Gauss
CEF INCOMETAX.AUSTRIAN.2020 (LCohen) (zvE;Abschnitte;Sätze) berechnet die ö ESt mittels =XYFUNCTIONS(), s.u.
CEF INCOMETAX.GERMAN.2004 (LCohen, maninweb) (zvE;Splitting) berechnet die Steuer auf das zvE normal oder mit Splitting
CEF LAMBDA allg. mit Debuggingschritten (LCohen) - als Idee, wie man mittels WAHL verschiedene Ergebnistabellen ausgibt
CEF LAMBDA allg. mit Lazy Finger (LCohen) - als Idee, mit Tastendruck statt Eingabe durch die LAMBDA-Zustände zu toggeln
CEF LAMBDA.MREXCEL (MrExcel-LAMBDA-Ideeneinreichungen)
CEF SPLIT (LCohen, maninweb) (Txt;[Orientation;]Delimiter) teilt Txt anhand Delimiter in Hoch- oder Quer-Orientation
CEF RAND.UNIQ (LCohen) (Count;LBound;UBound) ergibt n [Count] ganzz. Zufallszahlen ohne Wdh. innerh. LBound und UBound
CEF SORTBY.KEYRANGE (LCohen) Korr1 Korr2 (Arr;Keys;Header) sortiert Arr anhand Keys aus Bereich mit oder ohne Header
CEF SUBSTITUTE.CHARS (LCohen) (Txt;Search;Replace) ersetzt Search- im Txt durch Replace-Zeichen (rekursiv)
CEF SUBSTITUTE.STRINGS (LCohen) (Txt;Search-Replace-Arr) ersetzt Search-im Txt mit Replace-Zeichenfolgen
CEF TRANSPOSE8 (LCohen) (Arr;Zustand) rotiert ein Arr in 4 Richtungen, im Zustand normal oder revers
CEF UNIQUE.PARTIAL (LCohen) Korr1 Korr2 (Arr;Keys;Debugging) pivotiert Arr anhand Spalten-Keys. Debugging 0-6.
CEF UNPIVOT (LCohen) (Arr) macht aus einem vorgeschriebenen Kreuztabellen-Arr eine pivotierbare Liste
CEF XYFUNCTIONS (LCohen) (X;Arr1;Arr2) ergibt ab X-Untergrenzen in Arr1 eine individuelle Steigung m in Arr2 (zu lokalem n=Y).
____________________________________________________

ARRAY.COMBINE

Beschreibung:
Hängt Array2 unterhalb von Array1 an.

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(_Tab1;_Tab2;Origin;LET(
a;_Tab1;
b;_Tab2;
o;Origin;
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.
____________________________________________________

ARRAY.SHAKE

Beschreibung:
Gibt ein geschütteltes Array zurück. Jeder Eintrag (auch Text möglich) befindet sich genau einmal, aber meist woanders ggü. dem m*n-Ausgangsarray, im m*n-Array. Ähnlich: RAND.UNIQ nur als Spalte und nur für Zahlen, s.u.

Syntax:
=ARRAY.SHAKE(Array)

Beispiel:
=ARRAY.SHAKE({1;2;3;4;5;6}) könnte z.B. {2;5;3;6;1;4} zurückgeben, genau wie =RAND.UNIQ(6;1;6)

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)))
____________________________________________________

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!
=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))
____________________________________________________

SORTBY.KEYRANGE

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

Syntax:
=SORTBY.KEYRANGE(n;unten;oben)

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

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(
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)))

Anmerkungen:
Auf den XMLFILTERN-Teil wird sich hier bezogen.
____________________________________________________

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

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