Excel LAMBDA functie, De basis
Inhoud
Inleiding
In Maart 2021,
kondigde Microsoft de nieuwe Excel LAMBDA functie aan. Met deze nieuwe
functie kunnen we aangepaste functies maken, die functies kunnen vervangen
die nu in VBA geschreven zijn. Deze aangepaste functies zijn om twee redenen
krachtig:
- Iedere gebruiker die in staat is een formule te maken in Excel kan
nu nieuwe functies definieren;
- Omdat gebruik gemaakt wordt van de ingebouwde functies en van de
multi-threaded calculatie motor van Excel, is de snelheid van deze nieuwe
LAMBDA functies significant hoger dan VBA UDF's.
Deze pagina legt de basis-principes uit van de nieuwe LAMBDA functie
en laat zien hoe je deze functie omzet naar een eigen functie.
Merk op, dat de LAMBDA functie alleen beschikbaar is in Microsoft 365.
Download
het bijbehorende
bestand
De LAMBDA functie uitgelegd
Er bestaan al enkele goede artikelen over de nieuwe LAMBDA functie:
De Microsoft Research blog:
LAMBDA: The ultimate Excel worksheet function
Microsoft Excel Help:
LAMBDA functie
Als je al bekend bent met de LET functie, dan zal de struktuur van de
LAMBDA functie er bekend uitzien. Hieronder een voorbeeld in haar eenvoudigste
vorm:
=LAMBDA(x;x^2)
Alle argumenten behalve het laatste zijn invoerwaarden van de functie,
het laatste argument is de berekening die het eindresultaat geeft. Dit houdt
dus in dat de LAMBDA functie altijd tenminste één argument meer heeft dan
het aantal invoerwaarden. In het bovenstaande voorbeeld is x de invoer en
x^2 de resultaatberekening.
Iedereen die wiskunde heeft gehad op de middelbare school zal de formule
van een parabool herkennen:
y=ax²+bx+c
En waarschijnlijk ook de formule om de nulpunten van de parabool te bepalen,
de ABC-formule:
In wiskundetermen: de ABC-formule lost x op in ax²+bx+c=0 voor gegeven
a, b en c.
Vertaald naar een Excel LAMBDA functie ziet dit er zo uit:
=LAMBDA(a;b;c;(-b+{-1\1}*WORTEL(b^2-4*a*c))/2/a)
(De {-1\1} is Excel's manier om beide resultaten uit de formule te verkrijgen,
het gedraagt zich als het +/- teken in de ABC-formule)
ALs je deze formule in een gewone cel in Excel zet, dan geeft Excel een
onverwacht resultaat weer, een nieuwe Excel foutcode:
#BEREKENEN!
Dit komt doordat we de benodigde argumenten niet aan de functie hebben
doorgegeven. Als we de LAMBDA functie in een cel willen gebruiken, dan moeten
de argumenten van de LAMBDA functie achter de formule worden geplaatst,
tussen haakjes. Stel dat onze argumenten staan in de cellen A2, B2 en C2:
=LAMBDA(a;b;c;(-b+{-1\1}*SQRT(b^2-4*a*c))/2/a)(A2;B2;C2)
Met als resultaat:
Omzetten in een echte functie
Misschien begin je je af te vragen waar alle drukte voor nodig is, dit
is nog steeds een (omslachtig uitziende) gewone formule in een cel. Niets
bijzonders toch? Maar wacht even af, we komen er wel. Open het Namen beheren
venster (Formule tab, Namen beheren knop):
en klik op de knop "Nieuw...". Voer deze details in:
Merk op dat ik de moeite genomen heb om een opmerking toe te voegen,
hieronder laat ik zien waarom dat nuttig is. Nadat je op OK hebt geklikt
kan je deze nieuwe naam gebruiken alsof het een echte functie is:
Presto! Een nieuwe gebruikers-gedefiniëerde functie!
Zoals beloofd laat ik nog even zien waarom die Opmerking in de naam definitie
handig is. Selecteer een lege cel en begin te typen =Qua, Excel laat de
auto-aanvullen keuzelijst zien, die de opmerking toont:
Dit is dus een handige manier om uitleg te tonen tijdens het invoeren
van de functie!
Een ander voorbeeld
Naam van het werkblad in een cel
Een belangrijk voordeel van de LAMBDA functie is dat het je in staat
stelt om een complexe formule in een eenvoudige functie om te zetten. Een
goed voorbeeld is de veelgevraagde formule om de naam van het werkblad weer
te geven in een cel. Hiervoor gebruik je normaal gesproken de CEL functie:
=CEL("address";A1)
Deze formule geeft als resultaat:
C:\Users\piete\Documents\[Lambda Demo JKP.xlsx]Lambda Explanations
In dit voorbeeld is de naam van het werkblad "Lambda Explanations". Om
de informatie die ervoor staat te verwijderen, moeten we het vierkante haakje
"]" vinden en alle tekst die daarna komt behouden:
=DEEL(CEL("filename";A1);VIND.ALLES("]";CEL("filename";A1))+1;LENGTE(CEL("filename";A1)))
Lelijk en onhandig. Laten we de eveneens nieuwe LET functie eens gebruiken
om dit simpeler te maken:
=LET(f;CEL("filename";A1);DEEL(f;VIND.ALLES("]";f)+1;LENGTE(f))
Nu zijn we nog maar twee stappen verwijderd van het omzetten van deze
formule in een Lambda.
1. Voeg de LAMBDA functie toe aan de formule (het enige argument is een
cel op het werkblad waar je de naam van wilt weergeven)
=LAMBDA(cl;LET(f;CEL("filename";cl);DEEL(f;VIND.ALLES("]";f)+1;LENGTE(f)))
2. Definiëer een naam genaamd SheetName:
Dat is alles, nu kan je de naam van een werkblad met deze simpele formule
weergeven in een cel:
=SheetName(A1)
Microsoft heeft ook een handige tool om Lambda formules
te bewerken, genaamd de
Advanced Formula Environment. Op de tab Invoegen
klik je op "Invoegtoepassingen downloaden":
Zoek vervolgens naar "Advanced formula"
en klik op 'Toevoegen':
Lambdas delen
Download
het bijbehorende
bestand
Hoe zit het met het delen van lambda functies met je collega's? Het is
relatief eenvoudig om Lambda formules van de ene werkmap te kopiëren naar
de andere. Als je een lambda functie van werkmap A naar B wilt kopiëren,
zorg er dan voor dat er een cel is in werkmap A met daarin de LAMBDA functie
die gekopiëerd moet worden. Selecteer nu die cel en alle cellen waarin de
argumenten staan van die LAMBDA en kopiëer ze. Zoek nu een leeg bereik in
werkmap B en plak. Na het plakken kunnen deze cellen weer leeg gemaakt worden
want de naam met de LAMBDA functie is reeds gekopiëerd. Mocht een lambda
functie gebruik maken van andere lambda functies, dan zullen deze automatisch
mee gekopiëerd worden.
Je kunt lambdas ook delen
met anderen door er een public github gist
van te maken.
Vervolgens kunnen anderen uw lambda's gebruiken door
deze te importeren met de
Advanced Formula Environment add-in.
De QuadraticEquation lambda
kan je op deze manier importeren middels deze link:
https://gist.github.com/jkpieterse/568a6e267fb54ff71adb66299b447e70
Mijn water97 lambda's
staan in deze gist:
https://gist.github.com/jkpieterse/9f2f1d30f188ca20e3311e83b5a2a8a5
En een andere handige lambda is SheetName:
https://gist.github.com/jkpieterse/1541a4c51afa490958563fd7a9fdca0b
Ik heb ook een artikel geschreven over
hoe je een VBA UDF functie
converteert naar een LAMBDA functie.
Collega MVP Mourad Louha heeft een goed stuk geschreven over
het gebruiken van lambdas voor de manipulatie van matrices.
