LAMBDA je Excelova nova super funkcija

U ovom trenutku Microsoft Excel ima gotovo pet stotina funkcija radnih listova dostupnih putem prozora – gumba čarobnjaka za funkcije fx u traci formule. Ovo je vrlo pristojan skup, ali, unatoč tome, gotovo svaki korisnik prije ili kasnije naiđe na situaciju da ovaj popis ne sadrži funkciju koja mu je potrebna - jednostavno zato što nije u Excelu.

Do sada su jedini način rješavanja ovog problema bile makronaredbe, odnosno pisanje vlastite korisnički definirane funkcije (UDF = User Defined Function) u Visual Basicu, što zahtijeva odgovarajuće programerske vještine i ponekad nije nimalo jednostavno. Međutim, s najnovijim ažuriranjima za Office 365 situacija se promijenila nabolje - Excelu je dodana posebna funkcija "wrapper" LAMBDA. Uz njegovu pomoć, zadatak stvaranja vlastitih funkcija sada je riješen jednostavno i lijepo.

Pogledajmo princip njegove upotrebe u sljedećem primjeru.

Kao što vjerojatno znate, Excel ima nekoliko funkcija parsiranja datuma koje vam omogućuju da odredite broj dana, mjeseca, tjedna i godine za određeni datum. Ali iz nekog razloga ne postoji funkcija koja određuje broj četvrtine, što je također često potrebno, zar ne? Popravimo ovaj nedostatak i stvarajmo s njim LAMBDA vlastitu novu funkciju za rješavanje ovog problema.

Korak 1. Napišite formulu

Počnimo s činjenicom da ćemo ručno na uobičajeni način napisati formulu u ćeliju lista koja izračunava ono što nam je potrebno. U slučaju broja četvrtine, to se može učiniti, na primjer, ovako:

Korak 2. Zamotavanje u LAMBDA i testiranje

Sada je vrijeme da primijenimo novu LAMBDA funkciju i umotamo našu formulu u nju. Sintaksa funkcije je sljedeća:

=LAMBDA(Varijabla1; Varijabla2; ... VarijablaN ; Izraz)

gdje su imena jedne ili više varijabli navedena prva, a zadnji argument je uvijek formula ili izračunati izraz koji ih koristi. Nazivi varijabli ne bi trebali izgledati kao adrese ćelija i ne bi trebali sadržavati točke.

U našem slučaju bit će samo jedna varijabla - datum za koji izračunavamo broj kvartala. Nazovimo varijablu za to, recimo, d. Zatim umotavanje naše formule u funkciju LAMBDA i zamjenom adrese izvorne ćelije A2 s fiktivnim imenom varijable, dobivamo:

Imajte na umu da je nakon takve transformacije naša formula (zapravo točna!) počela proizvoditi pogrešku, jer sada izvorni datum iz ćelije A2 nije prenesen u nju. Radi testiranja i povjerenja, možete mu proslijediti argumente tako da ih dodate nakon funkcije LAMBDA u zagradi:

Korak 3. Stvorite ime

A sada onaj lagani i zabavni dio. Mi otvaramo Upravitelj imena kartica formula (Formule — Upravitelj naziva) i kreirajte novi naziv pomoću gumba stvoriti (Stvoriti). Osmislite i unesite naziv za našu buduću funkciju (na primjer, Nomkvartala), i na terenu Veza (Referenca) pažljivo kopirajte iz trake formule i zalijepite našu funkciju LAMBDA, samo bez zadnjeg argumenta (A2):

Sve. Nakon klika na OK stvorena funkcija može se koristiti u bilo kojoj ćeliji na bilo kojem listu ove radne knjige:

Koristiti u drugim knjigama

LAMBDA i dinamički nizovi

Prilagođene funkcije stvorene pomoću funkcije LAMBDA uspješno podržava rad s novim dinamičkim nizovima i njihovim funkcijama (FILTER, JEDINSTVENO, GRADE) dodan u Microsoft Excel 2020.

Recimo da želimo napraviti novu korisnički definiranu funkciju koja bi uspoređivala dva popisa i vraćala razliku između njih – one elemente s prvog popisa kojih nema na drugom. Životno djelo, zar ne? Ranije su za to koristili bilo funkcije a la VPR (VLOOKUP), ili zaokretne tablice, ili Power Query upiti. Sada možete učiniti s jednom formulom:

U engleskoj verziji to će biti:

=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)

Evo funkcije COUNTIF broji broj pojavljivanja svakog elementa prve liste u drugoj, a zatim i funkcije FILTER odabire samo one od njih koji nisu imali te pojave. Umotavanjem ove strukture u LAMBDA i stvaranje imenovanog raspona na temelju njega s imenom, na primjer, PRETRAŽIVANJE DISTRIBUCIJE – dobit ćemo zgodnu funkciju koja vraća rezultat usporedbe dviju lista u obliku dinamičkog niza:

Ako izvorni podaci nisu obične, već "pametne" tablice, naša funkcija će se također nositi bez problema:

Drugi primjer je dinamičko dijeljenje teksta pretvaranjem u XML i zatim raščlanjivanjem ćeliju po ćeliju pomoću funkcije FILTER.XML koju smo nedavno analizirali. Kako ovu složenu formulu ne biste svaki put ručno reproducirali, bit će lakše umotati je u LAMBDA i na temelju nje stvoriti dinamički raspon, tj. novu kompaktnu i praktičnu funkciju, nazvavši je, na primjer, RAZDTEXT:

Prvi argument ove funkcije bit će ćelija s izvornim tekstom, a drugi znak razdjelnika, a rezultat će vratiti u obliku horizontalnog dinamičkog niza. Kod funkcije će biti sljedeći:

=LAMBDA(t;d; TRANSPONIRAJ(FILTER.XML(“"&ZAMJENA(t;d? "«)&»“;”//Y”)))

Popis primjera je beskonačan – u svakoj situaciji u kojoj često morate unositi istu dugu i glomaznu formulu, funkcija LAMBDA značajno će vam olakšati život.

Rekurzivno nabrajanje znakova

Svi prethodni primjeri pokazali su samo jednu, najočitiju, stranu funkcije LAMBDA – njezinu upotrebu kao “omotača” za omotavanje dugih formula u nju i pojednostavljenje njihovog unosa. Zapravo, LAMBDA ima još jednu, puno dublju stranu koja ga pretvara u gotovo potpuni programski jezik.

Činjenica je da je temeljno važna značajka LAMBDA funkcija mogućnost njihove implementacije u rekurzija – logika izračuna, kada u procesu izračuna funkcija poziva samu sebe. Iz navike, možda zvuči jezivo, ali u programiranju je rekurzija uobičajena stvar. Čak iu makronaredbama u Visual Basicu, možete ga implementirati, a sada je, kao što vidite, stigao i u Excel. Pokušajmo razumjeti ovu tehniku ​​na praktičnom primjeru.

Pretpostavimo da želimo stvoriti korisnički definiranu funkciju koja bi uklonila sve zadane znakove iz izvornog teksta. Korisnost takve funkcije, mislim, ne morate dokazivati ​​- bilo bi vrlo zgodno očistiti zatrpane ulazne podatke uz njegovu pomoć, zar ne?

Međutim, u usporedbi s prethodnim, nerekurzivnim primjerima, čekaju nas dvije poteškoće.

1. Morat ćemo smisliti ime za našu funkciju prije nego počnemo pisati njezin kod, jer će se u njemu to ime već koristiti za pozivanje same funkcije.
   
2. Unos takve rekurzivne funkcije u ćeliju i otklanjanje pogrešaka navođenjem argumenata u zagradama iza LAMBDA (kao što smo učinili ranije) neće raditi. Morat ćete stvoriti funkciju odmah "od nule". Upravitelj imena (Upravitelj naziva).
   

Nazovimo našu funkciju, recimo, CLEAN i željeli bismo da ima dva argumenta – tekst koji treba očistiti i popis izuzetih znakova kao tekstualni niz:

Kreirajmo, kao i ranije, na kartici formula в Upravitelj imena imenovani raspon, nazovite ga CLEAR i unesite u polje Raspon sljedeća konstrukcija:

=LAMBDA(t;d;IF(d=””;t;CLEAR(ZAMJENA(t;LEFT(d);””);MID(d;2;255))))

Ovdje je varijabla t izvorni tekst koji treba obrisati, a d je popis znakova koji se brišu.

Sve radi ovako:

iteracija 1

Fragment SUBSTITUTE(t;LEFT(d);””), kao što možete pretpostaviti, zamjenjuje prvi znak iz lijevog znaka iz skupa d koji se briše u izvornom tekstu t s praznim tekstualnim nizom, tj. uklanja “ A”. Kao međurezultat dobivamo:

Vsh zkz n 125 rubalja.

iteracija 2

Tada funkcija poziva samu sebe i kao ulaz (prvi argument) prima ono što je ostalo nakon čišćenja u prethodnom koraku, a drugi argument je niz izuzetih znakova koji ne počinje od prvog, već od drugog znaka, tj. “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ,” bez početnog “A” – to radi funkcija MID. Kao i prije, funkcija uzima prvi znak slijeva od preostalih (B) i zamjenjuje ga u tekstu koji joj je dan (Zkz n 125 rubalja) s praznim nizom – dobivamo kao međurezultat:

125 ru.

iteracija 3

Funkcija se ponovno poziva, primajući kao prvi argument ono što je ostalo od teksta koji treba izbrisati u prethodnoj iteraciji (Bsh zkz n 125 ru.), i kao drugi argument, skup isključenih znakova skraćenih za još jedan znak na lijevo, tj. “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.,” bez inicijala “B”. Zatim ponovno uzima prvi znak slijeva (B) iz tog skupa i uklanja ga iz teksta – dobivamo:

sh zkz n 125 ru.

I tako dalje – nadam se da ste shvatili. Sa svakom iteracijom, popis znakova koji se uklanjaju bit će skraćen na lijevoj strani, a mi ćemo tražiti i zamijeniti sljedeći znak iz skupa prazninom.

Kada ponestane svih znakova, morat ćemo izaći iz petlje - ovu ulogu samo obavlja funkcija IF (AKO), u koji je umotan naš dizajn. Ako više nema znakova za brisanje (d=””), tada funkcija više ne bi trebala pozivati ​​samu sebe, već bi trebala jednostavno vratiti tekst koji treba izbrisati (varijabla t) u njegovom konačnom obliku.

Rekurzivna iteracija stanica

Slično, možete implementirati rekurzivno nabrajanje ćelija u zadanom rasponu. Pretpostavimo da želimo stvoriti lambda funkciju pod nazivom ZAMJENSKI LIST za veleprodajnu zamjenu fragmenata u izvornom tekstu prema zadanoj literaturi. Rezultat bi trebao izgledati ovako:

Oni. na našoj funkciji ZAMJENSKI LIST bit će tri argumenta:

1. ćelija s tekstom za obradu (izvorna adresa)
2. prva ćelija stupca s vrijednostima za pretraživanje iz pretraživanja
3. prva ćelija stupca sa zamjenskim vrijednostima iz pretraživanja

Funkcija bi trebala ići od vrha do dna u direktoriju i uzastopno zamijeniti sve opcije iz lijevog stupca Pronaći na odgovarajuće opcije iz desnog stupca Zamjena. Ovo možete implementirati sa sljedećom rekurzivnom lambda funkcijom:

Pomak prema dolje pri svakoj iteraciji implementiran je standardnom excel funkcijom RASPOLAGANJE (POMAK), koji u ovom slučaju ima tri argumenta – izvorni raspon, pomak retka (1) i pomak stupca (0).

Pa, čim dođemo do kraja direktorija (n = “”), moramo prekinuti rekurziju – prestajemo pozivati ​​sami sebe i prikazujemo što se nakupilo nakon svih zamjena u varijabli izvornog teksta t.

To je sve. Bez lukavih makronaredbi ili Power Query upita – cijeli zadatak rješava jedna funkcija.

• Kako koristiti Excelove nove funkcije dinamičkog polja: FILTER, SORT, UNIC
• Zamjena i čišćenje teksta s funkcijom SUBSTITUTE
  
• Stvaranje makronaredbi i korisnički definiranih funkcija (UDF) u VBA