LAMBDA je Excelova nova super funkcija

U ovom trenutku, Microsoft Excel ima skoro pet stotina funkcija radnog lista dostupnih kroz prozor Function Wizard – dugme fx u traci formule. Ovo je vrlo pristojan set, ali, ipak, gotovo svaki korisnik prije ili kasnije naiđe na situaciju da ova lista ne sadrži funkciju koja mu je potrebna – jednostavno zato što nije u Excelu.

Do sada, jedini način za rješavanje ovog problema bili su makroi, odnosno pisanje vlastite korisnički definirane funkcije (UDF = User Defined Function) u Visual Basicu, što zahtijeva odgovarajuće vještine programiranja i ponekad nije nimalo lako. Međutim, s najnovijim ažuriranjima za Office 365, situacija se promijenila na bolje – u Excel je dodana posebna funkcija “wrapper” LAMBDA. Uz njegovu pomoć, zadatak kreiranja vlastitih funkcija sada je lako i lijepo riješen.

Pogledajmo princip njegove upotrebe u sljedećem primjeru.

Kao što verovatno znate, Excel ima nekoliko funkcija raščlanjivanja datuma koje vam omogućavaju da odredite broj dana, meseca, nedelje i godine za dati datum. Ali iz nekog razloga ne postoji funkcija koja određuje broj četvrtine, što je također često potrebno, zar ne? Ispravimo ovaj nedostatak i krenimo 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 treba. U slučaju broja kvartala, to se može učiniti, na primjer, ovako:

LAMBDA je Excels nova super funkcija

Korak 2. Završavanje 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 prva navedena, a posljednji argument je uvijek formula ili izračunati izraz koji ih koristi. Imena varijabli ne bi trebalo da izgledaju kao adrese ćelija i ne bi trebalo da sadrže tačke.

U našem slučaju će postojati 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 originalne ćelije A2 sa fiktivnim imenom varijable, dobijamo:

LAMBDA je Excels nova super funkcija

Imajte na umu da je nakon takve transformacije naša formula (zapravo, tačna!) počela proizvoditi grešku, jer se sada originalni datum iz ćelije A2 ne prenosi u nju. Za testiranje i sigurnost, možete mu proslijediti argumente tako što ćete ih dodati nakon funkcije LAMBDA u zagradi:

LAMBDA je Excels nova super funkcija

Korak 3. Kreirajte ime

Sada za laki i zabavni dio. Otvaramo Ime Manager tabulator formula (Formule — Menadžer imena) i kreirajte novo ime pomoću dugmeta stvoriti (Stvoriti). Smislite i unesite ime za našu buduću funkciju (na primjer, Nomkvartala), i na terenu link (Referenca) pažljivo kopirajte iz trake formule i zalijepite našu funkciju LAMBDA, samo bez zadnjeg argumenta (A2):

LAMBDA je Excels nova super funkcija

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

LAMBDA je Excels nova super funkcija

Koristite u drugim knjigama

Jer stvoren sa LAMBDA Budući da su korisnički definirane funkcije, u stvari, imenovani opsezi, lako ih možete učiniti dostupnim ne samo u trenutnoj radnoj knjizi. Bit će dovoljno kopirati ćeliju s funkcijom i zalijepiti je bilo gdje na listu druge datoteke.

LAMBDA i dinamički nizovi

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

Recimo da želimo kreirati novu korisnički definiranu funkciju koja bi upoređivala dvije liste i vraćala razliku između njih – one elemente iz prve liste koji nisu u drugoj. Životno delo, zar ne? Ranije su za to koristili bilo koju funkciju a la VPR (Vlookup), ili zaokretne tabele ili Power Query upiti. Sada možete s jednom formulom:

LAMBDA je Excels nova super funkcija

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 funkcije FILTER odabire samo one od njih koji nisu imali ove pojave. Umotavanjem ove strukture LAMBDA i kreiranje imenovanog raspona na osnovu njega s imenom, na primjer, DISTRIBUCIJA PRETRAŽIVANJA – dobit ćemo zgodnu funkciju koja vraća rezultat poređenja dvije liste u obliku dinamičkog niza:

LAMBDA je Excels nova super funkcija

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

LAMBDA je Excels nova super funkcija

Drugi primjer je dinamičko cijepanje teksta pretvaranjem u XML i zatim raščlanjivanjem ćeliju po ćeliju pomoću funkcije FILTER.XML koju smo nedavno analizirali. Kako se ova složena formula ne bi reproducirala svaki put ručno, bit će lakše umotati je u LAMBDA-u i kreirati dinamički raspon na temelju nje, odnosno novu kompaktnu i zgodnu funkciju, nazvavši je, na primjer, RAZDTEXT:

LAMBDA je Excels nova super funkcija

Prvi argument ove funkcije će biti ćelija sa izvornim tekstom, a drugi – znak za razdvajanje, a rezultat će vratiti u obliku horizontalnog dinamičkog niza. Kôd funkcije će biti sljedeći:

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

Lista primjera je beskonačna – u svakoj situaciji u kojoj često morate unositi istu dugačku i glomaznu formulu, LAMBDA funkcija će značajno olakšati život.

Rekurzivno nabrajanje znakova

Svi prethodni primjeri su pokazali samo jednu, najočigledniju, stranu LAMBDA funkcije – njenu upotrebu kao „omotača“ za umotavanje dugih formula u nju i pojednostavljivanje njihovog unosa. U stvari, LAMBDA ima još jednu, mnogo dublju stranu koja ga pretvara u gotovo punopravni programski jezik.

Činjenica je da je fundamentalno važna karakteristika LAMBDA funkcija mogućnost njihove implementacije rekurzija – logika proračuna, kada u procesu izračunavanja funkcija poziva samu sebe. Iz navike, možda zvuči jezivo, ali u programiranju je rekurzija uobičajena stvar. Čak i u makroima u Visual Basicu, možete ga implementirati, a sada je, kao što vidite, došao u Excel. Pokušajmo razumjeti ovu tehniku ​​s praktičnim primjerom.

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

Međutim, u odnosu na prethodne, nerekurzivne primjere, očekuju nas dvije poteškoće.

  1. Morat ćemo smisliti ime za našu funkciju prije nego počnemo pisati njen kod, jer će se u njoj ovo ime već koristiti za pozivanje same funkcije.
  2. Unošenje takve rekurzivne funkcije u ćeliju i otklanjanje grešaka navođenjem argumenata u zagradama nakon LAMBDA (kao što smo radili ranije) neće raditi. Morat ćete kreirati funkciju odmah "od nule". Ime Manager (Upravitelj imena).

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

LAMBDA je Excels nova super funkcija

Kreirajmo, kao što smo ranije radili, na kartici formula в Ime menadžer imenovani raspon, nazovite ga CLEAR i unesite u polje domet sljedeća konstrukcija:

=LAMBDA(t;d;IF(d=””;t;BRIŠI(ZAMENJA(t;LEVO(d);””);SREDINA(d;2;255))))

Ovdje je varijabla t originalni tekst koji treba obrisati, a d je lista znakova za brisanje.

Sve funkcionira ovako:

Ponavljanje 1

Fragment SUBSTITUTE(t;LEFT(d);””), kao što možete pretpostaviti, zamjenjuje prvi znak lijevog znaka iz skupa d koji treba obrisati u izvornom tekstu t praznim tekstualnim nizom, odnosno uklanja “ A”. Kao srednji rezultat dobijamo:

Vsh zkz n 125 rubalja.

Ponavljanje 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 isključenih znakova koji ne počinje od prvog, već od drugog znaka, tj. „BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ,” bez početnog “A” – to radi MID funkcija. Kao i prije, funkcija uzima prvi znak slijeva od preostalih (B) i zamjenjuje ga u tekstu koji joj je dat (Zkz n 125 rubalja) praznim nizom - dobijamo kao međurezultat:

125 ru.

Ponavljanje 3

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

sh zkz n 125 ru.

I tako dalje – nadam se da ste shvatili ideju. Sa svakom iteracijom, lista znakova koje treba ukloniti bit će skraćena na lijevoj strani, a mi ćemo tražiti i zamijeniti sljedeći karakter iz skupa prazninom.

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

Rekurzivna iteracija ćelija

Slično, možete implementirati rekurzivno nabrajanje ćelija u datom opsegu. Pretpostavimo da želimo da kreiramo lambda funkciju pod nazivom ZAMJENA LISTA za veleprodajnu zamjenu fragmenata u izvornom tekstu prema datoj referenc listi. Rezultat bi trebao izgledati ovako:

LAMBDA je Excels nova super funkcija

One. na našoj funkciji ZAMJENA LISTA biće tri argumenta:

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

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

LAMBDA je Excels nova super funkcija

Ovdje varijabla t pohranjuje originalni tekst iz sljedeće ćelije stupca Adresa, a varijable n i z upućuju na prve ćelije u kolonama Naći и Zamjenik, respektivno.
Kao iu prethodnom primjeru, ova funkcija prvo zamjenjuje originalni tekst funkcijom SUBSTITUTE (ZAMJENA) podatke u prvom redu direktorija (tj SPbon St. Petersburg), a zatim poziva sam sebe, ali s pomakom u direktoriju na sljedeći red (tj. zamjenjuje St. Petersburg on St. Petersburg). Zatim se ponovo poziva sa pomakom prema dolje – i zamjenjuje već Peter on St. Petersburg itd

Pomak prema dolje pri svakoj iteraciji implementiran je standardnom Excel funkcijom ODSTRANJEVANJE (OFFSET), koji u ovom slučaju ima tri argumenta – originalni opseg, pomak reda (1) i pomak stupca (0).

Pa, čim dođemo do kraja direktorija (n = “”), moramo prekinuti rekurziju – prestajemo da se pozivamo i prikazujemo ono što se nakupilo nakon svih zamjena u izvornoj tekstualnoj varijabli t.

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

  • Kako koristiti nove funkcije dinamičkog niza Excela: FILTER, SORT, UNIC
  • Zamjena i čišćenje teksta sa funkcijom SUBSTITUTE
  • Kreiranje makronaredbi i korisnički definiranih funkcija (UDF) u VBA

Ostavite odgovor