sadržaj
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:
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:
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:
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):
Sve. Nakon klika na OK kreirana funkcija može se koristiti u bilo kojoj ćeliji na bilo kojem listu ove radne knjige:
Koristite u drugim knjigama
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:
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:
Ako izvorni podaci nisu obične, već "pametne" tablice, naša funkcija će se također nositi bez problema:
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:
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(“
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.
- 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.
- 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:
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:
One. na našoj funkciji ZAMJENA LISTA biće tri argumenta:
- ćelija s tekstom za obradu (izvorna adresa)
- prva ćelija kolone sa vrijednostima za pretraživanje iz pretraživanja
- 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:
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