Lambda-funktionen i Excel: komplett guide och praktiska exempel

Informatec Digital » Resurser » Lambda-funktionen i Excel: komplett guide och praktiska exempel

Lambda-funktionen i Excel har revolutionerat hur vi arbetar med formler i Microsofts kalkylblad. Den låter dig skapa dina egna anpassade funktioner med hjälp av enbart Excels formelspråk, utan att röra en enda rad i VBA eller traditionell programmering . Det är som om du har möjlighet att lägga till nya, specialdesignade funktioner i programmet.

Dessutom har ett antal associerade funktioner dykt upp kring LAMBDA, såsom BYROW, BYCOL, MAP, SCAN, REDUCE och MAKEARRAY , utformade för att arbeta med intervall och matriser på ett mycket mer flexibelt och kraftfullt sätt. Dessa funktioner beter sig, i stort sett, som små loopar som itererar genom data och tillämpar en transformation definierad av LAMBDA, vilket öppnar upp en mängd olika möjligheter för avancerad analys direkt i kalkylbladet.

Vad är LAMBDA-funktionen i Excel och vad används den till?

Lambda - funktionen är ett verktyg som låter dig definiera anpassade funktioner med hjälp av endast Excel-formler. Istället för att programmera i VBA eller förlita dig på makron kan du samla alla komplexa beräkningar i en enda, återanvändbar funktion, med egna parametrar och ett tydligt, rent slutresultat.

I praktiken förvandlar LAMBDA vilken formel som helst till en funktion som du kan återanvända så många gånger du vill. Dess största fördel är att den integreras sömlöst med resten av Excels beräkningsmotor och kan kombineras med standardfunktioner, referenser, definierade namn och dynamiska arrayer.

Den grundläggande syntaxen när den används direkt i en cell är:

=LAMBDA(parameter1; parameter2; …; parameterN; beräkning)(värde1; värde2; …; värdeN)

I den här strukturen är parameter1, parameter2, …, parameterN de namn du ger variablerna inom funktionen, medan beräkning är formeln som använder dessa parametrar för att generera ett resultat. Slutligen, inom den andra uppsättningen parenteser, anger du de faktiska värden som dessa parametrar kommer att anta när funktionen körs.

Om du använder Excels namnhanterare för att skapa en permanent LAMBDA-funktion ändras syntaxen något, eftersom du definierar funktionen där, men du anropar den inte än. I så fall skulle formatet vara:

=LAMBDA(var1; var2; …; varN; beräkning)

Senare skulle du anropa funktionen med det namn du gav den i Namnhanteraren, genom att helt enkelt skriva namnet och argumenten precis som du skulle göra med SUMMA, MEDELSNITT eller någon annan standardfunktion.

Bästa praxis vid skapande och testning av Lambda-funktioner

När du börjar arbeta med Lambda-funktioner är det viktigt att följa några riktlinjer för att säkerställa att de fungerar som förväntat och att du inte slösar tid på att felsöka komplicerade fel. Ett av de mest praktiska sätten att börja är att skapa och testa Lambda-funktionen direkt i en cell.

Vanligtvis skriver man först hela formeln med definitionen av LAMBDA och anropet i samma uttryck, så att man omedelbart kan se om resultatet är som förväntat. På så sätt kan man upptäcka syntax- eller logikfel innan man sparar den som en namngiven funktion.

Till exempel skulle en mycket typisk teststruktur vara:

=LAMBDA(); beräkning)(testvärden)

För att kontrollera något väldigt enkelt, som att addera 1 till ett tal, kan du använda:

=LAMBDA(tal; tal + 1)(1)

I det här fallet skulle funktionen returnera värdet 2. Det är ett mycket enkelt exempel, men det tjänar till att illustrera mekanismen: först definierar du parametrarna och beräkningen, och sedan anropar du funktionen genom att skicka motsvarande argument.

En viktig rekommendation för att undvika #CALC! -felet är att se till att din LAMBDA-funktion alltid returnerar ett resultat . Detta uppnås genom att tydligt inkludera ett uttryck i slutet som producerar ett enda värde eller en array, beroende på dina behov. Om du ser #CALC!-felet under testningen, kontrollera att formeln faktiskt genererar något som Excel kan visa som resultat.

När du har testat LAMBDA-funktionen i en cell och sett att den fungerar korrekt är det ett bra tillfälle att flytta den logiken till namnhanteraren och omvandla den till en återanvändbar anpassad funktion över hela bladet eller arbetsboken.

Sambandet mellan LAMBDA och de nya matrisfunktionerna

Runt LAMBDA har en serie avancerade funktioner dykt upp, såsom BYROW, BYCOL, MAP, SCAN, REDUCE och MAKEARRAY (den senare översatt i vissa versioner som ARCHIVOMAKEARRAY), vilka förlitar sig på LAMBDA för att tillämpa transformationer på intervall och kompletta matriser.

Den allmänna idén är att dessa funktioner itererar genom dataområden (rad för rad, kolumn för kolumn eller element för element) och, för varje element eller grupp av element, exekverar en Lambda-funktion som du definierar. Med andra ord fungerar de som loopar, men integrerade i Excels formelspråk.

Detta gör att du kan utföra operationer som tidigare krävde hjälpkolumner, mellanliggande tabeller eller till och med makron, direkt med en enda arrayformel som expanderar och returnerar resultat för hela området samtidigt.

Bland funktionerna relaterade till LAMBDA utmärker sig REDUCE, MAP, SCAN, BYCOL, BYROW och MAKEARRAY , var och en med ett specifikt mål: att gå igenom rader, tillämpa transformationer per kolumn, ackumulera resultat, skapa matriser beräknade från grunden, etc. De har alla gemensamt att de använder LAMBDA som en intern "motor", till vilken de skickar värden och ackumulatorer när de rör sig genom matrisen.

BYROW-funktion: itererar genom rader och returnerar resultat rad för rad

Funktionen BYROW används för att tillämpa en LAMBDA-funktion på varje rad i ett område och returnera en array med ett värde för varje bearbetad rad. Det är ett mycket effektivt sätt att beräkna delsummor eller statistik rad för rad utan att behöva kopiera formler vertikalt.

Dess allmänna syntax är:

=BYROW(matris; LAMBDA(rad; uttryck))

Det första argumentet är den array eller det område du vill iterera igenom (till exempel B2:D7), och det andra är en LAMBDA-funktion som tar varje rad i det området som en parameter, en i taget. LAMBDA-funktionen returnerar det värde du vill associera med den raden (möjligen en summa, ett medelvärde, en logisk kontroll, etc.).

Tänk dig att du har en datatabell i intervallet B2:D7 och du vill få en delsumma för varje rad. Du kan skriva något liknande detta i cell E2:

=BYRÅD(B2:D7; LAMBDA(rad; SUMMA(rad)))

Resultatet skulle bli en utmatningsvektor med ett värde för varje rad i matrisen B2:D7, där varje värde representerar summan av elementen i den raden. På så sätt behöver du inte skriva SUM rad för rad: BYROW gör det åt dig och sprider resultatet nedåt.

BYCOL-funktion: tillämpa LAMBDA per kolumn

Mycket likt BYROW är BYCOL- funktionen utformad för att iterera genom en array efter kolumner istället för rader. Den tillämpar en LAMBDA-funktion på varje kolumn i intervallet och returnerar en array med resultat där varje element motsvarar en kolumn.

Dess typiska syntax är:

=BYCOL(array; LAMBDA(kolumn; uttryck))

I det här fallet är parametern som LAMBDA tar emot hela kolumnen i matrisen som bearbetas i varje steg. I likhet med BYROW returnerar funktionen en vektor, men den här är utformad för att arbeta med totaler eller indikatorer per kolumn.

Om du fortsätter med föregående exempel, om du vill beräkna medelvärdet för varje kolumn i intervallet B2:D7 , kan du placera en formel som denna i cell B8:

=BYCOL(B2:D7; LAMBDA(kolumn; MEDEL(kolumn)))

Resultatet blir en matris med samma antal kolumner som B2:D7, där varje position innehåller medelvärdet för den kolumnen . På så sätt får du alla medelvärden i ett svep utan att behöva dra och släppa formler eller oroa dig för relativa referenser.

MAKEARRAY-funktion (MAKEARRAYFILE): skapa beräknade arrayer

Funktionen MAKEARRAY (visas ibland som ARCHIVOMAKERRAY) låter dig generera en helt ny array genom att ange antalet rader och kolumner och beräkna varje element med hjälp av en LAMBDA-funktion. Den börjar inte från ett befintligt område, utan bygger arrayen från grunden.

Dess allmänna syntax är:

=MAKERRAY(rader; kolumner; LAMBDA(rad; kolumn; uttryck))

Argumentet `rows` anger hur många rader utmatningsmatrisen kommer att ha, `columns` definierar antalet kolumner och LAMBDA-funktionen tar emot rad- och kolumnindexen som beräknas i varje iteration som parametrar. Med denna information kan du konstruera praktiskt taget vilket numeriskt eller textmönster som helst.

Ett mycket illustrativt exempel är att skapa en matris där varje element anger sin egen position. I vilken cell som helst kan du skriva något i stil med:

=SKAPARRAYFILE(3; 2; LAMBDA(rad; kolumn; -(rad & kolumn)))

Resultatet skulle bli en matris med 3 rader och 2 kolumner , där varje värde representerar en kombination av rad och kolumn (till exempel 11, 12, 21, 22, 31, 32), transformerad enligt den beräkning du anger (i det här fallet minustecknet som tillämpas på sammankopplingen av rader och kolumner).

En annan intressant användning av MAKEARRAY är att konvertera en vektor till en array samtidigt som man kontrollerar hur många element man tar. Anta att du vill skapa en array med de första 6 värdena i ett vertikalt område. Du kan först skapa en array av positioner med MAKEARRAYFILE, hämta de k minsta positionsvärdena och slutligen använda INDEX för att hämta de faktiska elementen från det ursprungliga området.

Ett exempel på en formel som kombinerar flera funktioner kan ha följande struktur:

=LET(arrPos; MAKERRAYFILE(3; 2; LAMBDA(rad; kolumn; -(rad & kolumn))); arrPosF; MATCH(arrPos; MinstK(arrPos; SEKVENS(6))); INDEX(G8:G13; arrPosF))

Här används LET för att definiera mellanliggande namn (arrPos, arrPosF), en array av positioner konstrueras med ARCHIVOMAKERRAY (3×2), de 6 minsta positionerna väljs med SMALLEST och SEQUENCE, och slutligen används INDEX för att returnera motsvarande värden från intervallet G8:G13. Det är ett kraftfullt exempel på hur man kombinerar LAMBDA och dynamiska arrayfunktioner för att utföra komplexa transformationer utan makron.

MAP-funktion: element-för-element-transformation

MAP- funktionen används för att iterera genom en eller flera arrayer samtidigt och returnera en ny array där varje utdataelement beräknas genom att applicera en LAMBDA-funktion på motsvarande indataelement. Den motsvarar en klassisk "map"-funktion i funktionell programmering.

Den grundläggande syntaxen är:

=MAP(matris1; LAMBDA_eller_fler_matriser)

I sin enklaste form tar den en enda array och en LAMBDA-funktion som tar emot varje värde från den arrayen. Denna LAMBDA-funktion transformerar värdet och returnerar den nya versionen som kommer att utgöra en del av utmatningsarrayen, med samma dimensioner som den ursprungliga arrayen.

Om du till exempel vill iterera genom ett vertikalt område A21:A26 och lämna det ursprungliga talet om det är jämnt eller ett bindestreck om det är udda, kan du använda något i stil med:

=MAP($A$21:$A$26; LAMBDA(param1; OM(ES.PAR(param1); param1; «-«)))

I det här fallet analyserar MAP varje element i A21:A26. LAMBDA kontrollerar med IS.EVEN om talet är jämnt. Om det är det returnerar den själva talet; annars returnerar den ett bindestreck. Resultatet är en array av samma storlek som det ursprungliga intervallet, men med transformationen tillämpad på varje element.

Den här metoden är mycket användbar när du vill tillämpa villkorlig logik, textkonvertering, värdenormalisering eller någon annan enkel operation, och undvika hjälpkolumner och repetitiva formler.

SCAN-funktionen: kumulativa och mellanliggande resultat

Funktionen SCAN används för att undersöka en array genom att tillämpa en LAMBDA-funktion på varje värde och generera en utmatningsarray som visar alla mellanliggande värden i ackumuleringsprocessen. Den är mycket lik REDUCE, men istället för att bara returnera det slutliga resultatet bevarar den varje steg.

Dess allmänna syntax är:

=SCAN(; array; LAMBDA(ackumulator; värde))

Det första argumentet, som är valfritt, är ackumulatorns initialvärde (till exempel 0 om du adderar). Det andra argumentet är den array eller det område du vill iterera igenom. Slutligen tar LAMBDA-funktionen emot två parametrar: ackumulatorn (det partiella resultatet fram till den punkten) och det aktuella värdet för den array du bearbetar.

Vid varje steg utvärderar SCAN LAMBDA-värdet, uppdaterar ackumulatorn och genererar ett nytt element i utmatningsmatrisen med det resulterande värdet. På så sätt erhålls en sekvens av ackumulerade värden eller progressiva transformationer.

Ett typiskt exempel är att beräkna en kumulativ summa (löpande summa) över en uppsättning värden och därifrån även få den relativa kumulativa frekvensen. Tänk dig att du har data i A31:A36 och du vill ha den absoluta kumulativa summan:

=SCAN(0; A31:A36; LAMBDA(ackum; param1; accum + param1))

Denna formel itererar genom A31:A36 och lägger till varje värde till den föregående summan. Resultatet är en array med samma antal element som det ursprungliga intervallet, men varje position visar den kumulativa summan fram till den punkten.

Från den kumulativa totalen är det enkelt att beräkna den kumulativa procentuella frekvensen genom att dividera varje kumulativ summa med den totala totalen. Du kan till exempel först definiera totalen med hjälp av SUM och sedan tillämpa SCAN igen:

=LET(totalt; SUMMA(A31:A36); SCAN(0; A31:A36; LAMBDA(ackum; param1; (ackum + param1)))/totalt)

I detta fall tilldelar LET summan av hela intervallet A31:A36 till totalsumman . Sedan genererar SCAN sekvensen av kumulativa värden och genom att dividera den med totalen får man den relativa kumulativa frekvensen för varje steg , allt i en enda matrisformel.

Funktionen REDUCE: reduktion till ett enda ackumulerat värde

Funktionen REDUCE itererar också genom en array genom att tillämpa en LAMBDA-funktion på varje element, men till skillnad från SCAN är du här bara intresserad av att få det slutliga resultatet av ackumuleringsprocessen. Det vill säga, den utför samma typ av iteration som SCAN, men returnerar bara det sista värdet i ackumulatorn.

Dess syntax är:

=REDUCE(; array; LAMBDA(ackumulator; värde))

Precis som i SCAN anger initial_value startpunkten för ackumulatorn, matrisen är det område som ska bearbetas, och LAMBDA har som parametrar den aktuella ackumulatorn och det värde som läses av just då.

Ett mycket typiskt användningsområde är att beräkna en löpande summa eller en kumulativ operation där man bara är intresserad av det sista resultatet . För att till exempel summera A1:A6 med hjälp av REDUCE kan man skriva:

=MINSKA(0; A1:A6; LAMBDA(ackum; param1; accum + param1))

Här itererar REDUCE genom A1:A6 och uppdaterar vid varje steg det kumulativa värdet genom att lägga till värdet i den aktuella cellen (param1). I slutet returnerar den ett enda värde: den totala summan. Det är konceptuellt likt att använda SUM, men med REDUCE kan du definiera vilken mer komplex ackumuleringslogik som helst, inte bara enkla summor.

Kraften hos REDUCE ligger i dess förmåga att arbeta med det föregående resultatet i varje steg och fortsätta tillämpa operationer tills processen är klar. Detta gör att du kan implementera sofistikerade anpassade beräkningar som traditionellt hanterades med loopar i makron.

Skapa anpassade funktioner med LAMBDA och namnhanteraren

En av LAMBDAs kraftfullaste funktioner är dess förmåga att konvertera vilken formel som helst till en användarfunktion med hjälp av Excels namnhanterare. Detta gör att din funktion kan ha ett eget namn och användas som vilken annan inbyggd funktion som helst i programmet.

Det typiska arbetsflödet är följande: först testar du LAMBDA-funktionen i en cell , inklusive både definitionen och anropet med exempelargument. När du har verifierat att den fungerar korrekt och returnerar det förväntade resultatet, kopierar du den del som motsvarar LAMBDA-definitionen (utan det slutliga anropet) och klistrar in den i namnhanteraren.

I Namnhanteraren skapar du ett nytt namn (till exempel MinMomsfunktion, MinRabatt, MittVägtMedelvärde osv.) och i fältet "Avser till" anger du:

=LAMBDA(var1; var2; …; varN; beräkning)

Från och med det ögonblicket kan du anropa funktionen i vilken cell som helst i din arbetsbok genom att skriva dess namn som om det vore en inbyggd funktion , och skicka parametervärdena i samma ordning som du definierade dem.

Detta har två tydliga fördelar: för det första gör det dina kalkylblad mer läsbara (istället för att se långa formler ser du en funktion med ett beskrivande namn); för det andra centraliserar det logiken på ett ställe. Om du senare vill ändra beräkningen ändrar du helt enkelt definitionen i namnhanteraren, så uppdateras alla formler som använder den automatiskt.

Praktiska aspekter och ytterligare överväganden

För att verkligen kunna utnyttja LAMBDA och dess tillhörande funktioner är det bra att förstå några praktiska aspekter av deras beteende och krav . För det första är dessa funktioner en del av moderna Excel-funktioner, så du behöver en version som redan innehåller dynamiska arrayer och de nyare LAMBDA-, BYROW-, BYCOL- och andra funktioner. Dessa är vanligtvis tillgängliga i de senaste utgåvorna av Microsoft 365.

En annan relevant fråga är prestanda : även om LAMBDA- och traverseringsfunktioner är mycket kraftfulla, kan det ta längre tid att beräkna om boken om man tillämpar dem på enorma intervall med mycket komplex logik. Det är lämpligt att utforma LAMBDA-funktioner med effektivitet i åtanke, undvika redundanta beräkningar och utnyttja strukturer som LET för att definiera återanvändbara mellanvärden.

Det är också viktigt att upprätthålla en konsekvent namngivningskonvention för parametrar och funktioner . Att använda beskrivande namn hjälper dig att förstå logiken när du återvänder till filen månader senare eller när någon annan behöver arbeta med dina arbetsböcker. En parameter med namnet amount, rate, dataRow eller valuesCol är mycket tydligare än bara x eller yoa.

Angående #CALC! -felet visas det vanligtvis när Excel inte kan beräkna arrayuttrycket eller när LAMBDA-funktionen inte returnerar ett giltigt resultat. Kontrollera alltid att din formel har en väldefinierad utdata och, om du arbetar med arrayfunktioner, att dimensionerna är konsekventa (till exempel att du inte kombinerar inkompatibla storleksområden utan korrekt transformation).

Slutligen, även om LAMBDA eliminerar behovet av VBA i många fall, ersätter det det inte helt. Det finns situationer där makroautomation fortfarande är det bästa alternativet, men för ett stort antal anpassade beräkningar och datatransformationer låter LAMBDA och dess tillhörande funktioner dig hålla allt ditt arbete inom Excels konventionella formelmiljö.

Tack vare dessa möjligheter har de som dagligen arbetar med kalkylblad nu mycket mer flexibla verktyg för att utforma sina egna beräkningar, sammanfatta information i rader eller kolumner, bläddra bland matriser helt eller delvis, generera detaljerade summor och skapa nya matriser beräknade i realtid, allt utan att lämna det formelspråk de redan känner till.