Hvordan bruke Excel LAMBDA-funksjonen

Sammendrag

Excel LAMBDA-funksjonen gir en måte å lage egendefinerte funksjoner som kan gjenbrukes gjennom en arbeidsbok, uten VBA eller makroer.

Hensikt

Lag egendefinert funksjon

Returverdi

Som definert av formel

Syntaks

= LAMBDA (parameter,…, beregning)

Argumenter

  • parameter - En inngangsverdi for funksjonen.
  • beregning - beregningen som skal utføres som resultat av funksjonen. Må være siste argument.

Versjon

Excel 365

Bruksanvisninger

I dataprogrammering refererer LAMBDA til en anonym funksjon eller uttrykk. En anonym funksjon er en funksjon definert uten navn. I Excel gir LAMBDA-funksjonen en måte å definere og kapsle inn spesifikk formelfunksjonalitet, omtrent som en Excel-funksjon. Når den er definert, kan en LAMBDA-funksjon navngis og gjenbrukes andre steder i en arbeidsbok. Med andre ord er LAMBDA-funksjonen en måte å lage tilpassede funksjoner på.

En av de viktigste fordelene med en tilpasset LAMBDA-funksjon er at logikken i formelen eksisterer på ett sted. Dette betyr at det bare er én kopi av koden å oppdatere når du løser problemer eller oppdaterer funksjonalitet, og endringer vil automatisk overføres til alle forekomster av LAMBDA-funksjonen i en arbeidsbok. En LAMBDA-funksjon krever ikke VBA eller makroer.

Eksempel 1 | Eksempel 2 | Eksempel 3

Opprette en LAMBDA-funksjon

LAMBDA-funksjoner blir vanligvis opprettet og feilsøkt i formellinjen på et regneark, og deretter flyttet til navnebehandleren for å tilordne et navn som kan brukes hvor som helst i en arbeidsbok.

Det er fire grunnleggende trinn for å lage og bruke en tilpasset formel basert på LAMBDA-funksjonen:

  1. Bekreft logikken du vil bruke med en standardformel
  2. Opprett og test en generisk (ikke navngitt) LAMBDA-versjon av formelen
  3. Navngi og definer LAMBDA-formelen med navnesjefen
  4. Test den nye egendefinerte funksjonen ved hjelp av det definerte navnet

Eksemplene nedenfor diskuterer disse trinnene mer detaljert.

Eksempel 1

For å illustrere hvordan LAMBDA fungerer, la oss begynne med en veldig enkel formel:

=x*y // multiple x and y

I Excel vil denne formelen vanligvis bruke cellereferanser som dette:

=B5*C5 // with cell references

Som du ser, fungerer formelen bra, så vi er klare til å gå videre til å lage en generisk LAMBDA-formel (ikke navngitt versjon). Det første du bør vurdere er om formelen krever innganger (parametere). I dette tilfellet er svaret "ja" - formelen krever en verdi for x, og en verdi for y. Når det er etablert, starter vi med LAMBDA-funksjonen, og legger til de nødvendige parametrene for brukerinngang:

=LAMBDA(x,y // begin with input parameters

Deretter må vi legge til den faktiske beregningen, x * y:

=LAMBDA(x,y,x*y)

Hvis du skriver inn formelen på dette tidspunktet, får du en #CALC! feil. Dette skjer fordi formelen ikke har noen inputverdier å jobbe med, siden det ikke lenger er noen cellereferanser. For å teste formelen, må vi bruke en spesiell syntaks som dette:

=LAMBDA(x,y,x*y)(B5,C5) // testing syntax

Denne syntaksen, der parametere leveres på slutten av en LAMBDA-funksjon i et eget sett med parenteser, er unik for LAMBDA-funksjoner. Dette gjør at formelen kan testes direkte på regnearket før LAMBDA blir kalt. På skjermen nedenfor kan du se at den generiske LAMBDA-funksjonen i F5 returnerer nøyaktig det samme resultatet som den opprinnelige formelen i E5:

Vi er nå klare til å navngi LAMBDA-funksjonen med Navnebehandler. Velg først formelen * ikke inkludert * testparametrene på slutten. Deretter åpner du Navnebehandler med snarveien Control + F3 og klikker på Ny.

I dialogboksen Nytt navn, skriv inn navnet "XBYY", la omfanget være satt til arbeidsbok, og lim inn formelen du kopierte i "Henviser til" -området.

Forsikre deg om at formelen begynner med et likhetstegn (=). Nå som LAMBDA-formelen har et navn, kan den brukes i arbeidsboken som alle andre funksjoner. På skjermen under formelen i G5, kopiert ned, er:

Den nye egendefinerte funksjonen returnerer det samme resultatet som de to andre formlene.

Eksempel 2

I dette eksemplet konverterer vi en formel for å beregne volumet til en sfære til en tilpasset LAMBDA-funksjon. Den generelle Excel-formelen for å beregne volumet til en sfære er:

=4/3*PI()*A1^3 // volume of sphere

hvor A1 representerer radius. Skjermen nedenfor viser denne formelen i aksjon:

Legg merke til at denne formelen bare krever en inngang (radius) for å beregne volum, så LAMBDA-funksjonen vår trenger bare en parameter (r), som vil vises som det første argumentet. Her er formelen konvertert til LAMBDA:

=LAMBDA(r,4/3*PI()*r^3) // generic lambda

Tilbake i regnearket har vi erstattet den originale formelen med den generiske LAMBDA-versjonen. Legg merke til at vi bruker testsyntaks, som lar oss plugge inn B5 for radius:

Resultatene fra den generiske LAMBDA-formelen er nøyaktig de samme som den opprinnelige formelen, så neste trinn er å definere og navngi denne LAMBDA-formelen med Navnebehandler, som forklart ovenfor. Navnet som brukes til en LAMBDA-funksjon kan være et hvilket som helst gyldig Excel-navn. I dette tilfellet vil vi kalle formelen "SphereVolume".

Tilbake i regnearket har vi erstattet den generiske (ikke navngitte) LAMBDA-formelen med den navngitte LAMBDA-versjonen, og angitt B5 for r. Legg merke til at resultatene som returneres av den tilpassede SphereVolume-funksjonen, er nøyaktig de samme som tidligere resultater.

Eksempel 3

I dette eksemplet oppretter vi en LAMBDA-funksjon for å telle ord. Excel har ikke en funksjon for dette formålet, men du kan telle ord med en celle med en tilpasset formel basert på LEN- og SUBSTITUTE-funksjonene slik:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

Les detaljert forklaring her. Her er formelen i aksjon i et regneark:

Legg merke til at vi får feil telling på 1 når formelen får en tom celle (B10). Vi vil løse dette problemet nedenfor.

Denne formelen krever bare én inngang, som er teksten som inneholder ord. I vår LAMBDA-funksjon vil vi kalle dette argumentet "tekst". Her er formelen konvertert til LAMBDA:

=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+1)

Merknad "tekst" vises som det første argumentet, og beregningen er det andre og siste argumentet. På skjermen nedenfor har vi erstattet den originale formelen med den generiske LAMBDA-versjonen. Legg merke til at vi bruker testsyntaks, som lar oss plugge inn B5 for tekst:

=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+1)(B5)

Resultatene fra den generiske LAMBDA-formelen er de samme som den opprinnelige formelen, så neste trinn er å definere og navngi denne LAMBDA-formelen med Name Manager, som forklart tidligere. Vi vil kalle denne formelen "CountWords".

Nedenfor har vi erstattet den generiske (ikke navngitte) LAMBDA-formelen med den navngitte LAMBDA-versjonen, og angitt B5 for tekst. Legg merke til at vi får nøyaktig de samme resultatene.

Formelen som brukes i Name Manager for å definere CountWords, er den samme som ovenfor, uten testsyntaks:

=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+1)

Å fikse det tomme celleproblemet

Som nevnt ovenfor, returnerer formelen ovenfor feil antall 1 når en celle er tom. Dette problemet kan løses ved å erstatte +1 med koden nedenfor:

=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(LEN(TRIM(B5))>0)

Full forklaring her. For å oppdatere den eksisterende navngitte LAMDA-formelen, må vi igjen bruke Navnebehandler:

  1. Åpne Navnebehandler
  2. Velg navnet "CountWords" og klikk "Edit"
  3. Erstatt koden "Henviser til" med denne formelen:
=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+(LEN(TRIM(text))>0))

Når Navnebehandleren er lukket, fungerer CountWords riktig på tomme celler, som vist nedenfor:

Merk: ved å oppdatere koden en gang i Navnebehandler, oppdateres alle forekomster av CountWords-formelen samtidig. Dette er en viktig fordel med egendefinerte funksjoner opprettet med LAMBDA -formeloppdateringer kan administreres på ett sted.

Interessante artikler...