Innhold
For øyeblikket har Microsoft Excel nesten fem hundre regnearkfunksjoner tilgjengelig gjennom funksjonsveiviser-vinduet – knappen fx i formellinjen. Dette er et veldig anstendig sett, men likevel møter nesten alle brukere før eller siden en situasjon der denne listen ikke inneholder funksjonen han trenger – rett og slett fordi den ikke er i Excel.
Inntil nå var den eneste måten å løse dette problemet på makroer, dvs. å skrive din egen brukerdefinerte funksjon (UDF = User Defined Function) i Visual Basic, som krever passende programmeringskunnskaper og til tider ikke er lett i det hele tatt. Men med de siste Office 365-oppdateringene har situasjonen endret seg til det bedre - en spesiell "wrapper"-funksjon er lagt til Excel LAMBDA. Med dens hjelp er oppgaven med å lage dine egne funksjoner nå løst enkelt og vakkert.
La oss se på prinsippet for bruken i følgende eksempel.
Som du mest sannsynlig vet, har Excel flere funksjoner for datoparsing som lar deg bestemme nummeret på dagen, måneden, uken og året for en gitt dato. Men av en eller annen grunn er det ingen funksjon som bestemmer nummeret på kvartalet, som også ofte er nødvendig, ikke sant? La oss fikse denne mangelen og lage med LAMBDA egen ny funksjon for å løse dette problemet.
Trinn 1. Skriv formelen
La oss starte med at vi manuelt på vanlig måte vil skrive en formel i en arkcelle som beregner hva vi trenger. Når det gjelder kvartalsnummeret kan dette for eksempel gjøres slik:
Trinn 2. Pakning inn i LAMBDA og testing
Nå er det på tide å bruke den nye LAMBDA-funksjonen og pakke inn formelen vår. Funksjonssyntaksen er som følger:
=LAMBDA(Variabel1; Variabel2; ... VariabelN ; Uttrykk)
der navnene på en eller flere variabler er oppført først, og det siste argumentet alltid er en formel eller et beregnet uttrykk som bruker dem. Variabelnavn skal ikke se ut som celleadresser og bør ikke inneholde prikker.
I vårt tilfelle vil det bare være én variabel – datoen vi beregner kvartalstallet for. La oss kalle variabelen for den, for eksempel d. Deretter pakker vi formelen inn i en funksjon LAMBDA og erstatte adressen til den opprinnelige cellen A2 med et fiktivt variabelnavn, får vi:
Vær oppmerksom på at etter en slik transformasjon begynte formelen vår (faktisk riktig!) å produsere en feil, for nå overføres ikke den opprinnelige datoen fra celle A2 til den. For testing og tillit kan du sende argumenter til den ved å legge dem til etter funksjonen LAMBDA i parantes:
Trinn 3. Lag et navn
Nå til den enkle og morsomme delen. Vi åpner Navnesjef tab formel (Formler – navnebehandler) og opprett et nytt navn med knappen Opprett (Skape). Kom opp med og skriv inn et navn for vår fremtidige funksjon (f.eks. Nomkvartala), og i felten link (Henvisning) kopier forsiktig fra formellinjen og lim inn funksjonen vår LAMBDA, bare uten det siste argumentet (A2):
Alt. Etter å ha klikket på OK den opprettede funksjonen kan brukes i hvilken som helst celle på et hvilket som helst ark i denne arbeidsboken:
Bruk i andre bøker
LAMBDA og dynamiske arrays
Egendefinerte funksjoner opprettet med en funksjon LAMBDA lykkes med å støtte arbeid med nye dynamiske arrays og deres funksjoner (FILTER, UNIK, GRADE) lagt til Microsoft Excel i 2020.
La oss si at vi ønsker å lage en ny brukerdefinert funksjon som vil sammenligne to lister og returnere forskjellen mellom dem – de elementene fra den første listen som ikke er i den andre. Livets arbeid, ikke sant? Tidligere brukte de enten funksjoner a la VPR (VISNING), eller Pivottabeller, eller Power Query-spørringer. Nå kan du gjøre med én formel:
I den engelske versjonen blir det:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Her er funksjonen ANTALL.HVIS teller antall forekomster av hvert element i den første listen i den andre, og deretter funksjonen FILTER velger bare de av dem som ikke hadde disse forekomstene. Ved å pakke denne strukturen inn LAMBDA og lage et navngitt område basert på det med et navn, for eksempel, SØKEDISTRIBUSJON – vi får en praktisk funksjon som returnerer resultatet av å sammenligne to lister i form av en dynamisk matrise:
Hvis kildedataene ikke er vanlige, men "smarte" tabeller, vil vår funksjon også klare seg uten problemer:
Et annet eksempel er dynamisk deling av tekst ved å konvertere den til XML og deretter analysere den celle for celle ved å bruke FILTER.XML-funksjonen vi nylig analyserte. For ikke å reprodusere denne komplekse formelen manuelt hver gang, vil det være lettere å pakke den inn i LAMBDA og lage et dynamisk område basert på den, dvs. en ny kompakt og praktisk funksjon, som for eksempel gir den navnet RAZDTEXT:
Det første argumentet til denne funksjonen vil være cellen med kildeteksten, og det andre - skilletegn, og det vil returnere resultatet i form av en horisontal dynamisk matrise. Funksjonskoden vil være som følger:
=LAMBDA(t;d; TRANSPOSER(FILTER.XML(“
Listen over eksempler er uendelig – i enhver situasjon der du ofte må angi den samme lange og tungvinte formelen, vil LAMBDA-funksjonen gjøre livet merkbart enklere.
Rekursiv oppregning av tegn
Alle tidligere eksempler har vist bare én, den mest åpenbare, siden av LAMBDA-funksjonen – dens bruk som en "innpakning" for å pakke inn lange formler i den og forenkle input. Faktisk har LAMBDA en annen, mye dypere side som gjør det til nesten et fullverdig programmeringsspråk.
Faktum er at en grunnleggende viktig egenskap ved LAMBDA-funksjoner er muligheten til å implementere dem i rekursjon – logikk for beregninger, når funksjonen kaller seg selv i beregningsprosessen. Fra vanen kan det høres skummelt ut, men i programmering er rekursjon en vanlig ting. Selv i makroer i Visual Basic kan du implementere det, og nå, som du ser, har det kommet til Excel. La oss prøve å forstå denne teknikken med et praktisk eksempel.
Anta at vi ønsker å lage en brukerdefinert funksjon som vil fjerne alle gitte tegn fra kildeteksten. Nytten av en slik funksjon, tror jeg, du trenger ikke å bevise - det ville være veldig praktisk å fjerne forsøplede inndata med dens hjelp, ikke sant?
Sammenlignet med de tidligere, ikke-rekursive eksemplene, venter oss imidlertid to vanskeligheter.
- Vi må komme opp med et navn for funksjonen vår før vi begynner å skrive dens kode, for i den vil dette navnet allerede bli brukt til å kalle selve funksjonen.
- Å legge inn en slik rekursiv funksjon i en celle og feilsøke den ved å spesifisere argumenter i parentes etter LAMBDA (som vi gjorde tidligere) vil ikke fungere. Du må opprette en funksjon umiddelbart "fra bunnen av". Navnesjef (navneansvarlig).
La oss kalle funksjonen vår, for eksempel CLEAN, og vi vil at den skal ha to argumenter – teksten som skal renses og listen over ekskluderte tegn som en tekststreng:
La oss lage, som vi gjorde tidligere, på fanen formel в Navneansvarlig navngitt område, navngi det CLEAR og skriv inn i feltet Område følgende konstruksjon:
=LAMBDA(t;d;HVIS(d=””;t;CLEAR(ERSTATNING(t;VENSTRE(d);””);MIDTT(d;2;255))))
Her er variabelen t den opprinnelige teksten som skal slettes, og d er listen over tegn som skal slettes.
Det hele fungerer slik:
iterasjon 1
Fragmentet SUBSTITUTE(t;LEFT(d);””), som du kanskje gjetter, erstatter det første tegnet fra venstre tegnet fra settet d som skal slettes i kildeteksten t med en tom tekststreng, dvs. fjerner " EN". Som et mellomresultat får vi:
Vsh zkz n 125 rubler.
iterasjon 2
Deretter kaller funksjonen seg selv og mottar som input (det første argumentet) det som er igjen etter rengjøring i forrige trinn, og det andre argumentet er strengen av ekskluderte tegn som starter ikke fra det første, men fra det andre tegnet, dvs. "BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ," uten den innledende "A" - dette gjøres av MID-funksjonen. Som før tar funksjonen det første tegnet fra venstre av de resterende (B) og erstatter det i teksten som er gitt til det (Zkz n 125 rubler) med en tom streng - vi får som et mellomresultat:
125 ru.
iterasjon 3
Funksjonen kaller seg selv igjen, og mottar som det første argumentet det som er igjen av teksten som skal slettes ved forrige iterasjon (Bsh zkz n 125 ru.), Og som det andre argumentet er settet med ekskluderte tegn avkortet med ett tegn til. venstre, dvs. "VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.," uten initialen "B". Så tar den igjen det første tegnet fra venstre (B) fra dette settet og fjerner det fra teksten – vi får:
sh zkz n 125 ru.
Og så videre – jeg håper du forstår ideen. Med hver iterasjon vil listen over tegn som skal fjernes avkortes til venstre, og vi vil søke etter og erstatte neste tegn fra settet med et tomrom.
Når alle karakterene går tom, må vi gå ut av loopen - denne rollen utføres bare av funksjonen IF (HVIS), der designet vårt er pakket inn. Hvis det ikke er tegn igjen å slette (d=””), skal funksjonen ikke lenger kalle seg selv, men skal ganske enkelt returnere teksten som skal slettes (variabel t) i sin endelige form.
Rekursiv iterasjon av celler
På samme måte kan du implementere en rekursiv opptelling av celler i et gitt område. Anta at vi ønsker å lage en lambda-funksjon kalt ERSTATNINGSLISTE for engrosutskifting av fragmenter i kildeteksten i henhold til en gitt referanseliste. Resultatet skal se slik ut:
De. på vår funksjon ERSTATNINGSLISTE det vil være tre argumenter:
- celle med tekst som skal behandles (kildeadresse)
- den første cellen i en kolonne med verdier for å søke fra oppslaget
- den første cellen i kolonnen med erstatningsverdier fra oppslaget
Funksjonen skal gå fra topp til bunn i katalogen og erstatte sekvensielt alle alternativer fra venstre kolonne Å finne til de tilsvarende alternativene fra høyre kolonne Erstatning. Du kan implementere dette med følgende rekursive lambda-funksjon:
Skift ned ved hver iterasjon implementeres av en standard excel-funksjon FJERNING (OFFSET), som i dette tilfellet har tre argumenter – det opprinnelige området, radskift (1) og kolonneskift (0).
Vel, så snart vi når slutten av katalogen (n = “”), må vi avslutte rekursjonen – vi slutter å kalle oss selv og viser det som har samlet seg etter alle erstatningene i kildetekstvariabelen t.
Det er alt. Ingen vanskelige makroer eller Power Query-spørringer – hele oppgaven løses av én funksjon.
- Slik bruker du Excels nye dynamiske array-funksjoner: FILTER, SORT, UNIC
- Erstatte og rydde opp i tekst med ERSTATT-funksjonen
- Opprette makroer og brukerdefinerte funksjoner (UDF) i VBA