(C) 2009 - 2021 by Mourad Louha · Alle Rechte vorbehalten

Die LAMBDA-Funktion in Excel

Das Excel-Team hat gestern – 03.12.2020 – die LAMBDA-Funktion in Excel angekündigt und im Excel-Betakanal zur Verfügung gestellt. Um den Betakanal nutzen zu können, ist eine Teilnahme am Office-Insider-Programm erforderlich. Ein Hinweis dazu: oft ist es so, dass neue Features in Excel stückweise ausgerollt werden. Wenn der aktuellste Betakanal bereits genutzt wird, die LAMBDA-Funktion jedoch noch nicht vorhanden ist, dann ist ein bißchen Geduld gefragt. In einem der nächsten Updates sollte dann die Funktion auftauchen.

Mit der LAMBDA-Funktion lassen sich benutzerdefinierte Funktionen erstellen, die rein formelbasierte Berechnungen beinhalten. Es ist somit keine Programmierung in VBA oder JavaScript erforderlich. Laut dem Excel-Blog sind zudem noch weitere Features in Zusammenhang mit der LAMBDA-Funktion geplant und diese erste Veröffentlichung ist nur der Anfang.

Dieser Artikel stellt die LAMBDA-Funktion sowie die derzeit noch gültigen Randbedingungen zu deren Verwendung vor. Als Beispiel stelle ich dann meine benutzerdefinierte Funktion OSTERN vor, die das Osterdatum eines beliebigen Jahres berechnet und hierbei die LAMBA-Funktion verwendet.

Die LAMBDA-Funktion in Excel

Die LAMBDA-Funktion

Die Syntax der LAMBDA-Funktion ist recht einfach. Als erstes Argument sollte ein Parameter angegeben werden. Unter einem Parameter ist eine Variable zu verstehen, deren Benennung der in Excel zulässigen Benennung für Namen folgt. Allerdings sollten Punkte, die ja in Namen zulässig sind, in der Benennung von Parametern vermieden werden.

Bis zu 253 weitere Parameter können optional als Folgeargumente definiert werden. Das letzte in der LAMBDA-Funktion verwendete Argument muss jedoch in jedem Fall eine Berechnung sein. Im Normalfall wird die Berechnung sinnvollerweise die zuvor definierten Parameter verwenden.

Syntax der LAMBDA-Funktion

Angenommen, es soll eine Formel erstellt werden, die je nach Angabe eines Steuerungsparameters die Summe oder Differenz zweier Zahlen berechnet. Die Formel liesse sich dann so formulieren:

=LAMBDA(X;Y;Z;WAHL(Z;X+Y;X-Y))

Je nachdem, ob Z den Wert 1 oder 2 einnimmt, würde die Summe oder die Differenz von X und Y berechnet werden. Wird allerdings die Formel in Excel so eingegeben, wie sie hier zuvor aufgeführt ist, liefert Excel jedoch den Fehler #KALK!.

Das liegt daran, dass zwar die Funktion definiert, aber keine Werte übergeben wurden. Das Ergebnis von LAMBDA ist als Funktion – eine sogenannte anonyme Funktion, wie Sie in einigen Programmiersprachen vorkommt – zu betrachten, weshalb die Eingangswerte angegeben werden müssen, um ein Ergebnis zu erhalten. Beispielsweise würde folgende Formel den Wert 3 zurückgeben.

=LAMBDA(X;Y;Z;WAHL(Z;X+Y;X-Y))(1;2;1)

Die Funktion LAMBDA ist auch in der Lage dynamische Arrays zurückzugeben, weshalb die Angabe zwei gleich hoher Bereiche dann die Summe bzw. die Differenz der jeweiligen Werte liefert. Beispielsweise:

=LAMBDA(X;Y;Z;WAHL(Z;X+Y;X-Y))(A2:A9;B2:B9;1)

In folgender Abbildung ist das zurückgegebene dynamische Array zu sehen, was die Summe der einzelnen Werte aufführt.

Beispiel zu einer LAMBDA-Funktion

LAMBDA kann übrigens noch viel mehr: so erkennt LAMBDA Datentypen und kann sogar Rekursionen ausführen, also sich selbst aufrufen. Diese Features werde ich jedoch in separaten Artikeln vorstellen.

Aber wie wird nun aus einer LAMBDA-Funktion eine benutzerdefinierte Funktion erstellt? Ganz einfach: einen Excel-Namen anlegen und die erstellte LAMBDA-Funktion als Formel hinterlegen – und zwar ohne Angabe der Eingangswerte. Folgende Abbildung zeigt die LAMBDA-Funktion, die unter dem Namen MEINEFUNKTION abgelegt wurde.

Benutzerdefinierte Funktion als Name

Anschließend kann der Name als benutzerdefinierte Funktion verwendet werden, wie hier folgt zu sehen. MEINEFUNKTION verhält sich wie eine Art Blackbox, die im Hintergrund die in LAMBDA hinterlegte Formel ausführt.

Verwenden einer benutzerdefinierten Funktion

Natürlich könnte jetzt eingewendet werden, dass dies etwas umständlich ist. Da dies jedoch die erste Version der LAMBDA-Funktion ist und weitere Entwicklungen geplant sind, denke ich mal, dass das Excel-Team sich dazu was einfallen lassen wird.

Zu beachten ist bei der Benennung von benutzerdefinierten Funktionen, dass deren Name nicht mit dem Namen einer bereits bestehenden Excel-Funktion übereinstimmt. Und, wenn im internationalen Umfeld gearbeitet wird, gilt dies auch für die Zielsprachen. Es empfiehlt sich, gegebenenfalls den Namen der benutzerdefinierten Funktionen ein Präfix voranstellen.

Da die eigenen benutzerdefinierten Funktionen als Name abgelegt werden, sind diese logischerweise an die Arbeitsmappe gebunden. Es gelten dann auch die Excel-Regeln für Namen, wenn z.B. Arbeitsblätter von einer Mappe in eine andere kopiert werden.

OSTERN als benutzerdefinierte Funktion in Excel

Im Internet sind eine ganze Reihe an Excel-Formeln zu finden, die das Osterdatum eines Jahres berechnen. Manche dieser Formeln schränken diese Berechnung jedoch auf einen Zeitraum ein, z.B. auf Jahreszahlen größer 1900. Manche Formeln wiederum berechnen nicht alle Jahre korrekt. Eine der besten und zuverlässigsten Methode zur Berechnung des Osterdatums ist meines Wissens die Gaussformel. Eine sehr elegante Umsetzung dieser Methode ist bei Excelformeln.de zu finden. Diese Formel verwende ich nachfolgend als Grundlage zur Erstellung der OSTERN-Funktion.

Die Berechnung des Osterdatums erfolgt hierbei in mehrere Schritten. Angenommen, das Jahr steht in Zelle A1, dann würden in den Zellen A2 bis A6, dem Ergebnis, folgende Formeln stehen.

A2=GANZZAHL(A1/100)
A3=REST(19*REST(A1;19)+A2-GANZZAHL(A2/4)-GANZZAHL((A2-GANZZAHL((A2+8)/25)+1)/3)+15;30)
A4=REST(32+2*REST(A2;4)+2*GANZZAHL(REST(A1;100)/4)-A3-REST(REST(A1;100);4);7)
A5=A3+A4-7*GANZZAHL((REST(A1;19)+11*A3+22*A4)/451)+22
A6=DATUM(A1;WENN(A5>31;4;3);WENN(A5-31< 1;A5;A5-31))

Parametrisieren wir nun in einem ersten Schritt diese Zellen A2 bis A6, indem wir die Zelladressen durch die Parameter A bis E ersetzen und das Jahr in A1 durch die Variable Jahr.

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

Die Excel-Funktion LET ermöglicht es, innerhalb einer Formel Teilberechnungen durchzuführen und diese wiederum in weiteren Teilberechnungen zu verwenden. Eine Pseudoformel würde somit wie folgt aussehen:

=LET(A;Berechnung_A;B;Berechnung_B; … … ; Ergebnis; Berechnung_Ergebnis; Ergebnis)

Die Berechnung des Osterdatums unter Berücksichtigung des Jahres - hier gleich 2020 - sieht dann bei Verwendung der LET-Funktion wie folgt aus:

=LET(Jahr;2020;
     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)

Somit ist die einzige Eingangsvariable das Jahr. Kapseln wir nun die Formel in eine LAMBDA-Funktion. Wir erhalten:

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

Wie zu sehen, ist die Jahresangabe von der LET-Funktion zu der LAMBDA-Funktion gewandert und dient dort als erster Parameter. Und in diesem Fall wird die LAMBDA-Funktion auf 2020 angewendet. Es ist fast geschafft, denn nun brauchen wir nur noch den Namen OSTERN angelegen und die LAMBDA-Funktion abzüglich des Eingangswertes (2020) als Formel angeben.

Folgende Abbildung zeigt dann die Verwendung der neuen Funktion OSTERN. Auch hier akzeptiert die Funktion einen Bereich als Werteliste und liefert ein dynamisches Array zurück.

OSTERN als benutzerdefinierte Excel-Funktion

Fazit

LAMBDA-Funktionen sind ein innovativer Meilenstein in Excel, der aus meiner Sicht, ähnlich zu den dynamischen Arrays und Datentypen, das Arbeiten mit Excel verändern wird. Ich bin wirklich sehr gespannt, was das Excel-Team in den kommenden Wochen und Monaten an Verbesserungen zu den LAMBDA-Funktionen zur Verfügung stellen wird.

Wünschen würde ich mir beispielsweise eine Möglichkeit, eigene benutzerdefinierte Funktionen in einer Art Bibliothek ablegen zu können, um von jeder Arbeitsmappe aus auf diese zugreifen zu können.

Happy Exceling :-)

 

 
Comments

Hi there,

for my English speaking readers, a translation of the Easter-Formula:

=LAMBDA(Year,
 LET(A,INT(Year/100),
     B,MOD(19*MOD(Year,19)+A-INT(A/4)-INT((A-INT((A+8)/25)+1)/3)+15,30),
     C,MOD(32+2*MOD(A,4)+2*INT(MOD(Year,100)/4)-B-MOD(MOD(Year,100),4),7),
     D,B+C-7*INT((MOD(Year,19)+11*B+22*C)/451)+22,
     E,DATE(Year,IF(D>31,4,3),IF(D-31< 1,D,D-31)),E))(2020)

Best,
Mourad

Bin ja gespannt, wann die Funktion allgemein verfügbar sein wird :-)

Hallo Horst,

ja, ich auch. Dieses Jahr wird das sicherlich nichts mehr ;-)
Gefühlsmäßig würde ich raten, so vielleicht in 6-9 Monaten. Mal gucken …

Gruß,
Mourad

Hallo Herr Louha …. als ich das mit dem Lambda gelesen habe, fiel mir sofort ein, das ich vor ca. 20 Jahren eine Vorlesung in der Computersprache „Scheme“ hören musste….. Lambda, Let … kam mir irgendwie bekannt vor….und wenn man das googelt kommt als Oberstes..:

Prozeduren gehören zu den wichtigsten Sprachelementen von Scheme. Sie können mit einem Lambda-Ausdruck (lambda) beschrieben werden. Da sie in Scheme wie jeder andere Datentyp behandelt werden, ist es möglich, sie mit let oder define an einen Bezeichner zu binden…

Gruß

Christian

Hallo Christian,

sehr interessant, vielen Dank für Ihren Hinweis :-)
Mir ist zwar bekannt, dass Lambda auch in anderen Programmiersprachen enthalten ist, Scheme kannte ich jedoch noch nicht. Ein Link zu Scheme: https://de.wikipedia.org/wiki/Scheme

Gruß,
Mourad

Das Ablegen der fertigen Formel (Minus das „(2020)“ am Ende) als Lambda klappt bei mir leider nicht, =OSTERN(2021) gibt dann nur #NAME? zurück. Was mache ich falsch, hat sich seit der Veröffentlichung noch etwas an den Lambdas geändert?

Die direkte Verwendung der =LET… Formel hingegen klappt wunderbar.

Hallo Julian,

ist denn die LAMBDA-Funktion bei Dir generell verfügbar? Gebe die mal =LAMBDA. ein, die müsste dann im IntelliSense angezeigt werden. Wenn LAMBDA nicht verfügbar ist, kann es nicht funktionieren. LAMBDA braucht derzeit eine Beta-Version (Office Insider) von Excel. Wenn LAMBDA allerdings verfügbar ist, gibt’s keinen Grund für den bei Dir angezeigten Fehler. Versuche es dann auch in einer neuen Mappe, die Du als XLSB (wichtig) speicherst. Wenn’s dann immer noch nicht geht, poste mal hier den Build Deiner Excel-Version (Konto / Info, rechts im Text neben Version). Ich könnte das Problem ans Excel-Team weitergeben; allerdings ohne Versprechen einer Lösung.

Gruß,
Mourad

…danke, ich war der Annahme, dass das Feature inzwischen in O365 angekommen ist! Sorry, dass ich das nicht zunächst gecheckt habe. So oder so danke für den großartigen Artikel!

Hallo Julian,

kein Problem und Danke :-)

Gruß
Mourad

leider immer noch nicht verfügbar.. schaden. ich finde das eine sehr nützliche funktion.

Hallo Robert,

ja, da gebe ich Dir absolut recht. Ich schätze mal, das wird aber nicht mehr allzu lange dauern, bis LAMBDA mehr Usern zur Verfügung steht.

Viele Grüße
Mourad

Hallo,

die LAMBDA-Funktion wurde gestern – 08.02.2022 – vom Excel-Team offiziell freigeschaltet. Damit steht LAMBDA nun auch bspw. dem Aktuellen Kanal in Excel zur Verfügung.

Gruß,
Mourad

Hallo Mourad,

ich habe da mal eine ganz dumme Frage … bitte beachten VBA ist böse … LAMBDA ersetzt VBA.

Wie kriege ich die Names mit den LAMBDA-Formeln, aus einem Workbook in ein anderes ? VBA scheidet aus !!! VBA ist böse ! :-)
Ein AddIn scheidet ebenfalls aus … die Names sind zwar im AddIn … lassen sich aber nicht ansprechen.

Ein Worksheet des AddIns in die Datei kopieren … netter Versuch. Schon vergessen: VBA ist böse ! :-)
Selbst, wenn ich ein Sheet über VBA in mein Worksheet kopiere, erziele ich nicht den gewünschten Effekt.
In dem kopierten Worksheet funktionieren die LAMBDAs … korrekt … aber nicht in den eigentlichen Worksheets des Workbooks,
in denen ich die LAMBDAs eigentlich drin haben wollte. Und lösche ich das Worksheet, sind auch die LAMBDAs weg.

Die einzige Möglichkeit ist tatsächlich … allen Ernstes ? … die Names per Hand zu kopieren ??? Echt jetzt !!!

Ach ja … eine Vorlage (xltx-Datei) mit darin enthaltenen LAMBDAs, das klappt wunderbar … aber eben nur für neue Dateien.

Oder hat jemand eine Lösung, an die nur noch niemand gedacht hat ?

Moin Sabina

richtig vermutet, das ist erstmal gar nicht in Excel vorgesehen. Somit ist tatsächlich von Hand angesagt.

Du müsstest somit den Namen, der mit der LAMBDA assoziiert ist, entweder manuell in die neue Datei reinkopieren – ergo erstellen – oder ggf. durch Kopieren eines Blattes in die neue Datei anlegen.

Das Excel-Team hat jedoch kürzlich den Advanced Formula Editor als Add-In veröffentlicht, womit Du LAMBDAs mit einem Editor erstellen kannst und Deine LAMBDAs auf GitHub in einem Gist ablegen kannst. Siehe hier https://github.com/microsoft/advanced-formula-environment. Allerdings kann dieser Editor nur englische Funktionsnamen und leider kein ; als Listentrennzeichen erkennen. Von daher ist er für uns deutsche Excel-User kaum brauchbar. Soll sich aber ggf. zukünftig ändern. Siehe dann hier https://github.com/microsoft/advanced-formula-environment/issues/3, wo Du bspw. auch Deine Stimme abgeben könntest, sofern Du ein GitHub-Konto hast.

Das Problem mit dem Löschen, das Du ansprichst, kann ich nicht nachvollziehen. Sofern sich der Name bzw. die LAMBDA auf die Arbeitsmappe bezieht. Klappt bei mir. Von daher kann ich nicht viel dazu sagen.

Viele Grüße
Mourad

Hi Sabina,

kannst du das mit der Mustervorlage als .xltx genauer erklären? Du hast eine Vorlage erstellt und da einige Lambda-Funktionen im Namensmanager definiert?

Vielen Dank und viele Grüße !