- Met de LAMBDA-functie kunt u in Excel aangepaste functies maken met alleen formules, zonder te programmeren of VBA te gebruiken.
- Gerelateerde functies zoals BYROW, BYCOL, MAP, SCAN, REDUCE en MAKEARRAY passen LAMBDA toe om matrices te doorlopen en te transformeren.
- Door LAMBDA eerst in een cel te testen en het resultaat vervolgens op te slaan in de Name Manager, worden debuggen en hergebruik eenvoudiger.
- LAMBDA en de nieuwe dynamische matrixfuncties vereenvoudigen geavanceerde berekeningen en vervangen veel processen die voorheen met macro's werden opgelost.
De Lambda-functie in Excel heeft een revolutie teweeggebracht in de manier waarop we met formules in Microsoft-spreadsheets werken. Hiermee kunt u uw eigen aangepaste functies maken met alleen de formuletaal van Excel, zonder ook maar één regel VBA of traditionele programmeertaal te hoeven gebruiken . Het is alsof u de mogelijkheid hebt om nieuwe, op maat ontworpen functies aan het programma toe te voegen.
Bovendien zijn er rondom LAMBDA een aantal verwante functies ontstaan, zoals BYROW, BYCOL, MAP, SCAN, REDUCE en MAKEARRAY , die zijn ontworpen om op een veel flexibelere en krachtigere manier met bereiken en matrices te werken. Deze functies gedragen zich, in grote lijnen, als kleine lussen die door de gegevens itereren en een door LAMBDA gedefinieerde transformatie toepassen, waardoor een breed scala aan mogelijkheden voor geavanceerde analyses direct in het spreadsheet ontstaat.
Wat is de LAMBDA-functie in Excel en waarvoor wordt deze gebruikt?
De Lambda -functie is een hulpmiddel waarmee u aangepaste functies kunt definiëren met alleen Excel-formules. In plaats van te programmeren in VBA of te vertrouwen op macro's, kunt u elke complexe berekening in één enkele, herbruikbare functie verpakken, met eigen parameters en een duidelijk, overzichtelijk eindresultaat.
In de praktijk zet LAMBDA elke formule om in een functie die je zo vaak als je wilt kunt hergebruiken. Het grootste voordeel is dat het naadloos integreert met de rest van de rekenmodule van Excel en kan worden gecombineerd met standaardfuncties, verwijzingen, gedefinieerde namen en dynamische matrices.
De basissyntaxis voor direct gebruik in een cel is:
=LAMBDA(parameter1; parameter2; …; parameterN; calculation)(value1; value2; …; valueN)
In deze structuur zijn parameter1, parameter2, …, parameterN de namen die je aan de variabelen binnen de functie geeft, terwijl calculation de formule is die deze parameters gebruikt om een resultaat te genereren. Tot slot geef je binnen de tweede set haakjes de werkelijke waarden door die deze parameters zullen aannemen wanneer de functie wordt uitgevoerd.
Als u de naammanager van Excel gebruikt om een permanente Lambda-functie te maken, verandert de syntaxis enigszins, omdat u de functie daar definieert, maar deze nog niet aanroept. In dat geval is de opmaak als volgt:
=LAMBDA(var1; var2; …; varN; berekening)
Later roept u die functie aan met de naam die u eraan hebt gegeven in de Naammanager. U typt simpelweg de naam en de argumenten in, net zoals u dat zou doen met SUM, AVERAGE of elke andere standaardfunctie.
Aanbevelingen voor het maken en testen van Lambda-functies
Wanneer je begint met het werken met Lambda-functies, is het belangrijk om een paar richtlijnen te volgen om ervoor te zorgen dat ze zich gedragen zoals verwacht en je geen tijd verspilt aan het debuggen van ingewikkelde fouten. Een van de meest praktische manieren om te beginnen is door de Lambda-functie direct in een cel te maken en te testen.
De gebruikelijke procedure is om eerst de volledige formule te schrijven, inclusief de definitie van LAMBDA en de aanroep in dezelfde expressie. Zo kun je direct zien of het resultaat aan de verwachtingen voldoet. Op deze manier kun je syntax- of logische fouten opsporen voordat je de formule als een benoemde functie opslaat.
Een typische teststructuur zou bijvoorbeeld als volgt zijn:
=LAMBDA(); berekening)(test_waarden)
Om iets heel eenvoudigs te controleren, zoals het optellen van 1 bij een getal, kun je het volgende gebruiken:
=LAMBDA(nummer; nummer + 1)(1)
In dit geval zou de functie de waarde 2 retourneren . Het is een heel eenvoudig voorbeeld, maar het dient ter illustratie van de werking: eerst definieer je de parameters en de berekening, en vervolgens roep je de functie aan met het bijbehorende argument.
Een belangrijke aanbeveling om de #CALC! -fout te voorkomen , is ervoor te zorgen dat uw Lambda-functie altijd een resultaat retourneert . Dit bereikt u door aan het einde een expressie op te nemen die een enkele waarde of een matrix produceert, afhankelijk van uw behoeften. Als u de #CALC!-fout tijdens het testen ziet, controleer dan of de formule daadwerkelijk een resultaat genereert dat Excel kan weergeven.
Zodra je de LAMBDA-functie in een cel hebt getest en hebt gezien dat deze correct werkt, is het een goed moment om die logica naar de Naammanager te verplaatsen en er een herbruikbare aangepaste functie van te maken die je in het hele werkblad of de hele werkmap kunt gebruiken.
Relatie tussen LAMBDA en de nieuwe matrixfuncties
Rondom LAMBDA is een reeks geavanceerde functies ontstaan, zoals BYROW, BYCOL, MAP, SCAN, REDUCE en MAKEARRAY (de laatste in sommige versies vertaald als ARCHIVOMAKEARRAY), die LAMBDA gebruiken om transformaties toe te passen op bereiken en complete matrices.
Het algemene idee is dat deze functies door bereiken van gegevens itereren (per rij, per kolom of element per element) en voor elk element of elke groep elementen een door u gedefinieerde Lambda-functie uitvoeren. Met andere woorden, ze werken als lussen, maar dan geïntegreerd in de formuletaal van Excel.
Hiermee kunt u bewerkingen uitvoeren die voorheen hulpkolommen, tussentabellen of zelfs macro's vereisten, rechtstreeks met één enkele matrixformule die de resultaten voor het hele bereik in één keer uitbreidt en retourneert.
Onder de functies die gerelateerd zijn aan LAMBDA, springen REDUCE, MAP, SCAN, BYCOL, BYROW en MAKEARRAY eruit , elk met een specifiek doel: het doorlopen van rijen, het toepassen van transformaties op kolommen, het accumuleren van resultaten, het creëren van matrices die vanaf nul worden berekend, enzovoort. Ze hebben allemaal gemeen dat ze LAMBDA gebruiken als interne "motor", waaraan ze waarden en accumulatoren doorgeven terwijl ze door de matrix bewegen.
BYROW-functie: doorloop de rijen en retourneer de resultaten rij voor rij.
De BYROW- functie wordt gebruikt om een LAMBDA-functie toe te passen op elke rij in een bereik en een array terug te geven met één waarde voor elke verwerkte rij. Het is een zeer efficiënte manier om subtotalen of statistieken rij voor rij te berekenen zonder formules verticaal te hoeven kopiëren.
De algemene syntaxis is als volgt:
=BYROW(matrix; LAMBDA(rij; expressie))
Het eerste argument is de array of het bereik waar je doorheen wilt itereren (bijvoorbeeld B2:D7), en het tweede is een lambdafunctie die elke rij in dat bereik één voor één als parameter neemt. De lambdafunctie retourneert de waarde die je aan die rij wilt koppelen (mogelijk een som, een gemiddelde, een logische controle, enz.).
Stel je voor dat je een gegevenstabel hebt in het bereik B2:D7 en je wilt een subtotaal voor elke rij berekenen. Je zou zoiets als dit in cel E2 kunnen schrijven:
=BYROW(B2:D7; LAMBDA(rij; SUM(rij)))
Het resultaat is een uitvoervector met één waarde voor elke rij van de matrix B2:D7, waarbij elke waarde de som van de elementen in die rij vertegenwoordigt. Op deze manier hoeft u SUM niet rij voor rij te schrijven: BYROW doet dit voor u en geeft het resultaat weer.
BYCOL-functie: pas LAMBDA toe op kolommen
De BYCOL -functie lijkt sterk op BYROW, maar is ontworpen om door een array te itereren op basis van kolommen in plaats van rijen. Het past een lambda-functie toe op elke kolom in het bereik en retourneert een array met resultaten, waarbij elk element overeenkomt met een kolom.
De gebruikelijke syntaxis is:
=BYCOL(array; LAMBDA(column; expression))
In dit geval ontvangt LAMBDA als parameter de volledige kolom van de matrix die bij elke stap wordt verwerkt. Net als BYROW retourneert de functie een vector, maar deze is ontworpen om te werken met totalen of indicatoren per kolom.
Om verder te gaan met het vorige voorbeeld: als u het gemiddelde van elke kolom in het bereik B2:D7 wilt berekenen , kunt u een formule zoals deze in cel B8 plaatsen:
=BYCOL(B2:D7; LAMBDA(column; AVERAGE(column)))
Het resultaat is een matrix met hetzelfde aantal kolommen als B2:D7, waarbij elke positie het gemiddelde van die kolom bevat . Op deze manier krijgt u alle gemiddelden in één keer, zonder formules te hoeven slepen en neerzetten of rekening te hoeven houden met relatieve verwijzingen.
MAKEARRAY-functie (MAKEARRAYFILE): maakt berekende arrays aan
De functie MAKEARRAY (soms weergegeven als ARCHIVOMAKEARRAY) stelt u in staat een volledig nieuwe array te genereren door het aantal rijen en kolommen op te geven en elk element te berekenen met behulp van een LAMBDA-functie. De functie begint niet met een bestaand bereik, maar bouwt de array helemaal opnieuw op.
De algemene syntaxis is als volgt:
=MAKEARRAY(rijen; kolommen; LAMBDA(rij; kolom; expressie))
Het argument `rows` geeft aan hoeveel rijen de uitvoermatrix zal hebben, `columns` definieert het aantal kolommen en de LAMBDA-functie ontvangt de rij- en kolomindices die in elke iteratie worden berekend als parameters. Met deze informatie kunt u vrijwel elk numeriek of tekstpatroon construeren.
Een zeer illustratief voorbeeld is het maken van een matrix waarin elk element zijn eigen positie aangeeft. In elke cel zou je bijvoorbeeld het volgende kunnen schrijven:
=MAKEARRAYFILE(3; 2; LAMBDA(rij; kolom; -(rij & kolom)))
Het resultaat is een matrix van 3 rijen bij 2 kolommen , waarbij elke waarde een combinatie van rij en kolom vertegenwoordigt (bijvoorbeeld 11, 12, 21, 22, 31, 32), getransformeerd volgens de berekening die u invoert (in dit geval het minteken dat wordt toegepast op de samenvoeging van rij en kolom).
Een ander interessant gebruik van MAKEARRAY is het omzetten van een vector naar een array, waarbij je zelf kunt bepalen hoeveel elementen je gebruikt. Stel dat je een array wilt maken met de eerste 6 waarden van een verticaal bereik. Je zou eerst een array van posities kunnen maken met MAKEARRAYFILE, de k kleinste positiewaarden eruit halen en vervolgens INDEX gebruiken om de daadwerkelijke elementen uit het oorspronkelijke bereik op te halen.
Een voorbeeld van een formule die meerdere functies combineert, zou de volgende structuur kunnen hebben:
=LET(arrPos; MAKEARRAYFILE(3; 2; LAMBDA(row; col; -(row & col))); arrPosF; MATCH(arrPos; LeastK(arrPos; SEQUENCE(6))); INDEX(G8:G13; arrPosF))
Hier wordt LET gebruikt om tussenliggende namen (arrPos, arrPosF) te definiëren, een array van posities wordt geconstrueerd met ARCHIVOMAKEARRAY (3×2), de 6 kleinste posities worden geselecteerd met SMALLEST en SEQUENCE, en tot slot wordt INDEX gebruikt om de overeenkomstige waarden uit het bereik G8:G13 terug te geven. Dit is een krachtig voorbeeld van hoe lambda-functies en dynamische arrayfuncties gecombineerd kunnen worden om complexe transformaties uit te voeren zonder macro's.
MAP-functie: element-voor-element transformatie
De MAP- functie wordt gebruikt om gelijktijdig door één of meer arrays te itereren en een nieuwe array terug te geven, waarbij elk uitvoerelement wordt berekend door een lambdafunctie toe te passen op het corresponderende invoerelement(en). Het is equivalent aan een klassieke "map"-functie in functioneel programmeren.
De basissyntaxis is:
=MAP(matrix1; LAMBDA_of_meer_matrices)
In de eenvoudigste vorm neemt het een enkele array en een lambdafunctie die elke waarde uit die array ontvangt. Deze lambdafunctie transformeert de waarde en retourneert de nieuwe versie die deel zal uitmaken van de uitvoerarray, waarbij dezelfde dimensies als de oorspronkelijke array behouden blijven.
Als je bijvoorbeeld door een verticaal bereik A21:A26 wilt itereren en het oorspronkelijke getal wilt behouden als het even is, of een koppelteken als het oneven is, kun je zoiets gebruiken als:
=MAP($A$21:$A$26; LAMBDA(param1; IF(ES.PAR(param1); param1; «-«)))
In dit geval analyseert MAP elk element van A21:A26. LAMBDA controleert met IS.EVEN of het getal even is. Zo ja, dan retourneert het het getal zelf; anders retourneert het een streepje. Het resultaat is een array van dezelfde grootte als het oorspronkelijke bereik, maar met de transformatie toegepast op elk element.
Deze aanpak is erg handig wanneer u voorwaardelijke logica, tekstconversie, waardenormalisatie of andere eenvoudige bewerkingen wilt toepassen, zonder hulpkolommen en herhalende formules.
SCAN-functie: cumulatieve en tussentijdse resultaten
De SCAN -functie wordt gebruikt om een array te onderzoeken door op elke waarde een LAMBDA-functie toe te passen en een uitvoerarray te genereren die alle tussenliggende waarden van het accumulatieproces weergeeft. Het is zeer vergelijkbaar met REDUCE, maar in plaats van alleen het eindresultaat terug te geven, bewaart het elke stap.
De algemene syntaxis is als volgt:
=SCAN(; array; LAMBDA(accumulator; waarde))
Het eerste argument, dat optioneel is, is de beginwaarde van de accumulator (bijvoorbeeld 0 als je aan het optellen bent). Het tweede argument is de array of het bereik waar je doorheen wilt itereren. Tot slot ontvangt de LAMBDA-functie twee parameters: de accumulator (het gedeeltelijke resultaat tot dat moment) en de huidige waarde van de array die je aan het verwerken bent.
Bij elke stap evalueert SCAN de LAMBDA-waarde, werkt de accumulator bij en genereert een nieuw element in de uitvoermatrix met de resulterende waarde. Op deze manier verkrijgt u een reeks geaccumuleerde waarden of progressieve transformaties.
Een typisch voorbeeld is het berekenen van een cumulatief totaal (lopend totaal) over een reeks waarden en vervolgens ook de relatieve cumulatieve frequentie bepalen. Stel dat u gegevens hebt in A31:A36 en u wilt het absolute cumulatieve totaal berekenen:
=SCAN(0; A31:A36; LAMBDA(accum; param1; accum + param1))
Deze formule doorloopt de waarden A31:A36 en telt elke waarde op bij het vorige totaal. Het resultaat is een matrix met hetzelfde aantal elementen als het oorspronkelijke bereik, maar elke positie toont het cumulatieve totaal tot dat punt.
Vanuit dat cumulatieve totaal is het eenvoudig om de cumulatieve procentuele frequentie te berekenen door elk cumulatief totaal te delen door het totale totaal. Je zou bijvoorbeeld eerst het totaal kunnen definiëren met SUM en vervolgens SCAN opnieuw toepassen:
=LET(totaal; SUM(A31:A36); SCAN(0; A31:A36; LAMBDA(accum; param1; (accum + param1)))/totaal)
In dit geval kent LET de som van het gehele bereik A31:A36 toe aan het totaal . Vervolgens genereert SCAN de reeks cumulatieve waarden en door deze te delen door het totaal, verkrijgt men de relatieve cumulatieve frequentie voor elke stap , alles in één matrixformule.
REDUCE-functie: reductie tot één enkele geaccumuleerde waarde
De REDUCE- functie doorloopt ook een array door een LAMBDA-functie op elk element toe te passen, maar in tegenstelling tot SCAN bent u hier alleen geïnteresseerd in het eindresultaat van het accumulatieproces. Dat wil zeggen, het voert hetzelfde type iteratie uit als SCAN, maar retourneert alleen de laatste waarde in de accumulator.
De syntaxis is:
=REDUCE(; array; LAMBDA(accumulator; waarde))
Net als bij SCAN stelt initial_value het startpunt van de accumulator in, matrix is het te verwerken bereik en LAMBDA heeft als parameters de huidige accumulator en de waarde die op dat moment wordt gelezen.
Een veelvoorkomend gebruik is het berekenen van een doorlopende som of een cumulatieve bewerking waarbij je alleen geïnteresseerd bent in het laatste resultaat . Om bijvoorbeeld A1:A6 op te tellen met REDUCE, zou je het volgende kunnen schrijven:
=VERMINDEREN(0; A1:A6; LAMBDA(accum; param1; accum + param1))
Hier doorloopt REDUCE de cellen A1:A6 en werkt bij elke stap de cumulatieve waarde bij door de waarde van de huidige cel (param1) erbij op te tellen. Aan het einde retourneert het één enkele waarde: de totale som. Het is conceptueel vergelijkbaar met het gebruik van SUM, maar met REDUCE kunt u elke complexere accumulatielogica definiëren, niet alleen eenvoudige sommen.
De kracht van REDUCE schuilt in het vermogen om bij elke stap met het vorige resultaat te werken en bewerkingen te blijven toepassen totdat het proces is voltooid. Hierdoor kunt u geavanceerde, aangepaste berekeningen uitvoeren die traditioneel met lussen in macro's werden afgehandeld.
Aangepaste functies maken met LAMBDA en de Name Manager
Een van de krachtigste functies van LAMBDA is de mogelijkheid om elke formule om te zetten in een gebruikersfunctie met behulp van Excel's Naammanager. Hierdoor krijgt uw functie een eigen naam en kan deze worden gebruikt zoals elke andere standaardfunctie in het programma.
De gebruikelijke workflow is als volgt: test eerst de LAMBDA-functie in een cel , inclusief zowel de definitie als de aanroep met voorbeeldargumenten. Zodra je hebt gecontroleerd of deze correct werkt en het verwachte resultaat oplevert, kopieer je het gedeelte dat overeenkomt met de LAMBDA-definitie (zonder de laatste aanroep) en plak je dit in de Naammanager.
In de Naammanager kunt u een nieuwe naam aanmaken (bijvoorbeeld MijnBTW-functie, MijnKorting, MijnGewogenGemiddelde, enz.) en in het veld 'Verwijst naar' vult u het volgende in:
=LAMBDA(var1; var2; …; varN; berekening)
Vanaf dat moment kunt u in elke cel van uw werkblad de functie aanroepen door de naam ervan te schrijven alsof het een ingebouwde functie is , waarbij u de parameterwaarden in dezelfde volgorde doorgeeft als waarin u ze hebt gedefinieerd.
Dit heeft twee duidelijke voordelen: ten eerste maakt het uw spreadsheets leesbaarder (in plaats van lange formules ziet u een functie met een beschrijvende naam); ten tweede centraliseert het de logica op één plek. Als u de berekening later wilt wijzigen, hoeft u alleen de definitie in de Naammanager aan te passen en alle formules die deze gebruiken, worden automatisch bijgewerkt.
Praktische aspecten en aanvullende overwegingen
Om optimaal gebruik te maken van LAMBDA en de bijbehorende functies, is het handig om enkele praktische aspecten van hun werking en vereisten te begrijpen . Ten eerste maken deze functies deel uit van moderne Excel-functies, dus je hebt een versie nodig die al dynamische matrices en de nieuwere LAMBDA-, BYROW-, BYCOL- en andere functies bevat. Deze zijn doorgaans beschikbaar in de nieuwste edities van Microsoft 365.
Een ander relevant punt is de prestatie : hoewel LAMBDA- en traverseringsfuncties zeer krachtig zijn, kan het herberekenen van de gegevens langer duren als je ze toepast op enorme bereiken met zeer complexe logica. Het is raadzaam om LAMBDA-functies te ontwerpen met efficiëntie in het achterhoofd, door redundante berekeningen te vermijden en structuren zoals LET te gebruiken om herbruikbare tussenwaarden te definiëren.
Het is ook cruciaal om een consistente naamgevingsconventie te hanteren voor parameters en functies . Beschrijvende namen helpen je de logica te begrijpen wanneer je het bestand maanden later opnieuw bekijkt of wanneer iemand anders met je werkmappen moet werken. Een parameter met de naam amount, rate, dataRow of valuesCol is veel duidelijker dan simpelweg x of yoa.
Wat betreft de #CALC! -fout , deze verschijnt meestal wanneer Excel de matrixuitdrukking niet kan berekenen of wanneer de Lambda-functie geen geldig resultaat retourneert. Controleer altijd of uw formule een goed gedefinieerde uitvoer heeft en, als u met matrixfuncties werkt, of de dimensies consistent zijn (bijvoorbeeld dat u geen incompatibele groottebereiken combineert zonder de juiste transformatie).
Tot slot, hoewel LAMBDA in veel gevallen de noodzaak voor VBA wegneemt, vervangt het deze niet volledig. Er zijn situaties waarin macroautomatisering de beste optie blijft, maar voor een groot aantal aangepaste berekeningen en gegevenstransformaties kunt u met LAMBDA en de bijbehorende functies al uw werk binnen de conventionele formuleomgeving van Excel uitvoeren.
Dankzij deze mogelijkheden beschikken gebruikers die dagelijks met spreadsheets werken nu over veel flexibelere tools om hun eigen berekeningen te ontwerpen, informatie per rij of kolom samen te vatten, matrices volledig of gedeeltelijk te doorlopen, gedetailleerde totalen te genereren en direct nieuwe matrices te creëren, allemaal zonder de formuletaal te verlaten die ze al kennen.
