Lambda funkcija u Excelu: kompletan vodič i praktični primjeri

Informatec Digital » Resursi » Lambda funkcija u Excelu: kompletan vodič i praktični primjeri

Funkcija LAMBDA u Excelu Donio je radikalnu promjenu u načinu rada s formulama unutar Microsoftovih proračunskih tablica. Zahvaljujući njemu, možete kreirati vlastite prilagođene funkcije koristeći samo Excelov jezik formula, bez dodirivanja ijedne linije VBA ili... klasično programiranjeTo je kao da imate mogućnost dodavanja novih izvornih funkcija programu, dizajniranih posebno za vas.

Nadalje, oko LAMBDA se pojavio niz povezanih funkcija, kao što su BYROW, BYCOL, MAP, SCAN, REDUCE ili MAKEARRAYOve funkcije su dizajnirane za rad s rasponima i matricama na mnogo fleksibilniji i moćniji način. One se, na neki način, ponašaju poput malih petlji koje iteriraju kroz podatke i primjenjuju transformaciju definiranu pomoću LAMBDA funkcije, otvarajući širok spektar mogućnosti za naprednu analizu direktno u proračunskoj tabeli.

Šta je LAMBDA funkcija u Excelu i za šta se koristi?

Funkcija LAMBDA To je alat koji vam omogućava da definirate prilagođene funkcije koristeći samo Excel formule. Umjesto programiranja u VBA-i ili oslanjanja na makroe, možete enkapsulirati bilo koji složeni izračun u jednu, višekratno upotrebljivu funkciju, s vlastitim parametrima i jasnim, čistim konačnim rezultatom.

U praksi, LAMBDA pretvara bilo koju formulu u funkciju koji možete ponovo koristiti onoliko puta koliko želite. Njegova velika prednost je što se besprijekorno integrira s ostatkom Excelovog mehanizma za izračunavanje i može se kombinirati sa standardnim funkcijama, referencama, definiranim imenima i dinamičkim nizovima.

Osnovna sintaksa kada se koristi direktno u ćeliji je:

=LAMBDA(parametar1; parametar2; …; parametarN; izračun)(vrijednost1; vrijednost2; …; vrijednostN)

U ovoj strukturi, parametar1, parametar2, …, parametarN Ovo su imena koja dajete varijablama unutar funkcije, dok proračun To je formula koja koristi te parametre za generiranje rezultata. Konačno, unutar drugog skupa zagrada,... stvarne vrijednosti koji će uzeti te parametre prilikom izvršavanja funkcije.

Ako iskoristite prednost ime manager Da biste kreirali trajnu Lambda funkciju u Excelu, sintaksa se malo mijenja, jer definirate funkciju, ali je još ne pozivate. U tom slučaju, format bi bio:

=LAMBDA(var1; var2; …; varN; izračun)

Kasnije biste pozvali tu funkciju koristeći ime koje ste joj dali u Upravitelju imena, jednostavno upisujući ime i argumente baš kao što biste to učinili sa SUM, AVERAGE ili bilo kojom drugom standardnom funkcijom.

Najbolje prakse pri kreiranju i testiranju Lambda funkcija

Kada počnete raditi s LAMBDA-om, važno je slijediti niz preporuka kako biste funkcije se ponašaju kako se očekuje I ne gubite vrijeme na otklanjanje grešaka u komplikovanim situacijama. Jedan od najpraktičnijih načina za početak je kreiranje i testiranje LAMBDA funkcije direktno u ćeliji.

Uobičajeni postupak je da se prvo napiše kompletna formula s definicijom LAMBDA funkcije i pozivom u istom izrazu, tako da odmah možete vidjeti da li je rezultat očekivan. Na ovaj način možete otkriti sintaktičke ili logičke greške prije nego što je sačuvate kao imenovanu funkciju.

Na primjer, vrlo tipična struktura testa bi bila:

=LAMBDA(); izračun)(testne_vrijednosti)

Da biste provjerili nešto vrlo jednostavno poput dodavanja 1 broju, možete koristiti:

=LAMBDA(broj; broj + 1)(1)

U ovom slučaju, funkcija bi vratila vrijednost 2To je vrlo jednostavan primjer, ali služi za ilustraciju mehanike: prvo definirate parametre i izračun, a zatim pozivate tu funkciju prosljeđivanjem odgovarajućeg argumenta.

Ključna preporuka kako biste izbjegli greške #KALC! Radi se o tome da se uvjerite da je vaš LAMBDA uvijek vraća rezultatTo se postiže jasnim uključivanjem izraza na kraju koji proizvodi jednu vrijednost ili niz, ovisno o tome šta tražite. Ako tokom testiranja vidite grešku #CALC!, provjerite da li formula zaista generira nešto što Excel može prikazati kao rezultat.

Nakon što ste testirali LAMBDA senzor u ćeliji i vidjeli da ispravno radi, pravo je vrijeme da premjesti tu logiku u Upravitelj imena i pretvorite je u prilagođenu funkciju za višekratnu upotrebu na cijelom listu ili cijeloj radnoj knjizi.

Veza LAMBDA funkcije s novim matričnim funkcijama

Oko LAMBDA tehnologije se pojavio niz naprednih funkcija, kao što su BYROW, BYCOL, MAP, SCAN, REDUCE i MAKEARRAY (ovo drugo se u nekim verzijama prevodi kao ARCHIVOMAKEARRAY), koji se oslanjaju na LAMBDA funkciju za primjenu transformacija na raspone i kompletne matrice.

Opšta ideja je da ove funkcije oni prelaze raspone podataka (po redovima, po kolonama ili element po elementu) i, za svaki element ili grupu elemenata, izvršavaju LAMBDA funkciju koju sami definirate. To jest, rade kao petlje, ali integrirane u Excelov jezik formula.

Ovo vam omogućava da izvršite operacije koje su prethodno zahtijevale pomoćne kolone, međutabele ili čak makroe, direktno pomoću jedne formule niza koja se proširuje i vraća rezultate za cijeli raspon odjednom.

Sljedeće funkcije povezane s LAMBDA-om posebno se ističu: SMANJI, MAPIRAJ, SKENIRAJ, POBOJI, POREDI i NAPRAVI RASPOREDSvaki od njih ima specifičan cilj: kretanje kroz redove, primjenu transformacija po kolonama, akumuliranje rezultata, kreiranje matrica izračunatih od nule itd. Svima im je zajedničko da koriste LAMBDA kao interni "motor", kojem prosljeđuju vrijednosti i akumulatore dok se kreću kroz matricu.

Funkcija BYROW: iteracija kroz redove i vraćanje rezultata red po red

Funkcija BYROW Koristi se za primjenu Lambda funkcije na svaki red u rasponu i vraćanje niza s jednom vrijednošću za svaki obrađeni red. To je vrlo efikasan način za izračunavanje međuzbirova ili statistike red po red bez potrebe za vertikalnim kopiranjem formula.

Njegova opšta sintaksa je:

=BYROW(matrica; LAMBDA(red; izraz))

Prvi argument je matrica ili raspon kroz koji želite iterirati (na primjer, B2:D7), a drugi je LAMBDA funkcija koja prima svaki red u tom rasponu kao parametar, jedan po jedan. LAMBDA funkcija vraća vrijednost koju želite povezati s tim redom (moguće zbir, prosjek, logičku provjeru itd.).

Zamislite da imate tabelu podataka u rasponu B2:D7 i želite dobiti međuzbir za svaki red. U ćeliju E2 možete napisati nešto ovako:

=BYROW(B2:D7; LAMBDA(red; SUM(red)))

Rezultat bi bio izlazni vektor s jednom vrijednošću za svaki red matrice B2:D7, gdje svaka vrijednost predstavlja zbir elemenata tog reda. Na ovaj način ne morate pisati SUM red po red: BYROW to radi umjesto vas i prosipa rezultat prema dolje.

Funkcija BYCOL: primjena LAMBDA po kolonama

Veoma slična funkciji BYROW BYCOL Dizajniran je za kretanje kroz matricu po kolonama umjesto po redovima. Primjenjuje LAMBDA funkciju na svaku kolonu raspona i vraća niz rezultata gdje svaki element odgovara jednoj koloni.

Njegova tipična sintaksa je:

=BYCOL(niz; LAMBDA(kolona; izraz))

U ovom slučaju, parametar koji LAMBDA prima je puna kolona matrice koja se obrađuje u svakom koraku. Slično kao BYROW, funkcija vraća vektor, ali je sada dizajnirana za rad sa ukupnim iznosima ili indikatorima po koloni.

Nastavljajući s prethodnim primjerom, ako želite izračunati prosjek svake kolone u rasponu B2:D7U ćeliju B8 možete postaviti formulu poput ove:

=POKOLONA(B2:D7; LAMBDA(kolona; PROSJEK(kolona)))

Rezultat će biti matrica sa istim brojem kolona kao B2:D7, u kojoj svaka pozicija sadrži prosjek te koloneNa ovaj način dobijate sve prosjeke odjednom, bez potrebe za bavljenjem formulama ili brigom o relativnim referencama.

Funkcija MAKEARRAY (MAKEARRAYFILE): kreiranje izračunatih nizova

Funkcija NAPRAVITE EARRAY (u nekim prijevodima prikazano kao ARCHIVOMAKEARRAY) vam omogućava generiranje potpuno novog niza određivanjem broja redova i kolona i izračunavanjem svakog elementa pomoću LAMBDA funkcije. Ne počinje od postojećeg raspona, već izgraditi matricu od nule.

Njegova opšta sintaksa je:

=MAKEARRAY(redovi; kolone; LAMBDA(red; kolona; izraz))

Argument redova označava koliko redova će izlazna matrica imati, stubova Broj kolona je definisan, a funkcija LAMBDA kao parametre prima indekse redova i kolona koji se izračunavaju u svakoj iteraciji. Pomoću ovih informacija možete konstruisati praktično bilo koji numerički ili tekstualni obrazac.

Vrlo ilustrativan primjer je kreiranje matrice gdje svaki element označava svoju poziciju. U bilo kojoj ćeliji možete napisati nešto poput:

=MAKEARRAYFILE(3; 2; LAMBDA(red; kolona; -(red i kolona)))

Rezultat bi bila matrica od 3 reda sa 2 kolonegdje svaka vrijednost predstavlja kombinaciju reda i kolone (na primjer, 11, 12, 21, 22, 31, 32), transformiranu prema izračunu koji unesete (u ovom slučaju, negativni znak primijenjen na spajanje reda i kolone).

Još jedna zanimljiva upotreba MAKEARRAY-a je pretvoriti vektor u matricu kontrolisanje broja elemenata koje uzimate. Pretpostavimo da želite formirati niz sa prvih 6 vrijednosti vertikalnog raspona. Prvo možete kreirati niz pozicija pomoću funkcije FILEMAKEARRAY, dobiti k najmanjih vrijednosti pozicija i na kraju koristiti INDEX za preuzimanje stvarnih elemenata iz originalnog raspona.

Primjer formule koja kombinira nekoliko funkcija mogao bi imati ovu strukturu:

=LET(arrPos; MAKEARRAYFILE(3; 2; LAMBDA(red; kolona; -(red i kolona))); arrPosF; MATCH(arrPos; LeastK(arrPos; SEQUENCE(6))); INDEX(G8:G13; arrPosF))

LET se ovdje koristi za definirati međuimena (arrPos, arrPosF), niz pozicija se konstruiše pomoću ARCHIVOMAKEARRAY (3×2), 6 najmanjih pozicija se bira pomoću SMALLEST i SEQUENCE, i na kraju se INDEX koristi za vraćanje odgovarajućih vrijednosti iz raspona G8:G13. To je snažan primjer kako kombinovati LAMBDA i dinamičke funkcije niza za izvođenje složenih transformacija bez makroa.

MAP funkcija: transformacija element po element

Funkcija MAP Koristi se za iteraciju kroz jednu ili više matrica istovremeno i vraćanje nove matrice u kojoj svaki izlazni element Izračunava se primjenom LAMBDA funkcije na odgovarajući ulazni element(e). Ekvivalentno je klasičnoj "mapi" u funkcionalnom programiranju.

Osnovna sintaksa je:

=MAP(matrica1; LAMBDA_ili_više_matrica)

U svom najjednostavnijem obliku, uzima jedan niz i LAMBDA funkciju koja prima svaku vrijednost iz tog niza. Ova LAMBDA funkcija transformira vrijednost i vraća novu verziju koja će postati dio izlaznog niza, čuvajući originalne vrijednosti. iste dimenzije nego originalna matrica.

Na primjer, ako želite iterirati kroz vertikalni raspon A21:A26 i ostaviti originalni broj ako je paran ili crticu ako je neparan, mogli biste koristiti nešto poput:

=MAP($A$21:$A$26; LAMBDA(parametar1; AKO(ES.PAR(parametar1); parametar1; «-«)))

U ovom slučaju, MAP analizira svaki element A21:A26. LAMBDA provjerava sa IS.EVEN da li je broj paran. Ako jeste, vraća sam broj; u suprotnom, vraća crticu. Rezultat je niz iste veličine kao i originalni raspon, ali sa primijenjena transformacija svakom elementu.

Ovaj pristup je veoma koristan kada želite primijeniti uslovnu logiku, konverziju teksta, normalizaciju vrijednosti ili bilo koju drugu jednostavnu operaciju, izbjegavajući pomoćne kolone i ponavljajuće formule.

Funkcija SCAN: kumulativni i međurezultati

Funkcija SCAN Koristi se za ispitivanje matrice primjenom LAMBDA funkcije na svaku vrijednost i generiranjem izlazne matrice gdje su prikazane vrijednosti. sve međuvrijednosti procesa akumulacije. Vrlo je sličan funkciji REDUCE, ali umjesto da vraća samo konačni rezultat, zadržava svaki korak.

Njegova opšta sintaksa je:

=SCAN(; niz; LAMBDA(akumulator; vrijednost))

Prvi argument, koji je opcionalan, je početna vrijednost Prvi argument je akumulator (na primjer, 0 ako sabirate). Drugi argument je niz ili raspon kroz koji želite iterirati. Konačno, LAMBDA funkcija prima dva parametra: akumulator (djelimični rezultat do te tačke) i trenutnu vrijednost niza koji obrađujete.

U svakom koraku, SCAN procjenjuje LAMBDA vrijednost, ažurira akumulator i generira novi element u izlaznoj matrici s rezultirajućom vrijednošću. Na ovaj način se dobija niz akumuliranih vrijednosti ili progresivnih transformacija.

Tipičan primjer je izračunavanje povećanje kumulativnog (tekući zbir) preko skupa vrijednosti i, odatle, također dobiti relativnu kumulativnu frekvenciju. Zamislite da imate podatke u A31:A36 i želite apsolutnu kumulativnu frekvenciju:

=SCAN(0; A31:A36; LAMBDA(accum; param1; accum + param1))

Ova formula iterira kroz A31:A36, dodajući svaku vrijednost prethodnom zbiru. Rezultat je niz s istim brojem elemenata kao i originalni raspon, ali svaka pozicija se prikazuje kumulativni ukupan iznos do te tačke.

Iz tog kumulativnog iznosa, lako je izračunati kumulativnu procentualnu učestalost dijeljenjem svakog kumulativnog iznosa sa ukupnim iznosom. Na primjer, prvo možete definirati ukupan iznos pomoću funkcije SUM, a zatim ponovo primijeniti funkciju SCAN:

=LET(ukupno; SUM(A31:A36); SCAN(0; A31:A36; LAMBDA(accum; param1; (accum + param1)))/ukupno)

U ovom slučaju, LET dodjeljuje Ukupno zbir cijelog raspona A31:A36. Zatim SCAN generira niz akumuliranih vrijednosti i, dijeljenjem s zbirom, za svaki korak dobija se relativna kumulativna učestalostsve u jednoj matričnoj formuli.

Funkcija REDUCE: redukcija na jednu akumuliranu vrijednost

Funkcija SMANJITE Također prelazi kroz niz primjenjujući LAMBDA na svaki element, ali za razliku od SCAN-a, ovdje vas zanima samo dobijanje konačni rezultat procesa akumulacije. To jest, izvodi isti tip prolaska kroz proces kao i SCAN, ali vraća samo posljednju vrijednost akumulatora.

Njegova sintaksa je:

=SMANJENJE(; niz; LAMBDA(akumulator; vrijednost))

Ig baš kao u SCAN-u, početna_vrijednost utvrđuje početnu tačku akumulatora, matrica Ovo je raspon koji treba obraditi, a LAMBDA kao parametre ima trenutni akumulator i vrijednost koja se u tom trenutku čita.

Vrlo tipična upotreba je izračunavanje tekućeg zbroja ili kumulativne operacije gdje vas zanima samo najnoviji rezultatNa primjer, da biste sabrali A1:A6 koristeći REDUCE, mogli biste napisati:

=REDUCE(0; A1:A6; LAMBDA(accum; param1; accum + param1))

Ovdje, REDUCE iterira kroz A1:A6 i ažurira u svakom koraku. sad Sabiranje vrijednosti trenutne ćelije (param1). Kada se završi, vraća jednu vrijednost: ukupnu sumu. Konceptualno je slično korištenju funkcije SUM, ali sa funkcijom REDUCE možete definirati bilo koju složeniju logiku akumulacije, ne samo sume.

Moć funkcije REDUCE leži u činjenici da vam omogućava da radite s prethodnim rezultatom u svakom koraku i nastavite primjenjivati ​​operacije dok se proces ne završi. Na ovaj način možete implementirati sofisticirane prilagođene izračune koji su se tradicionalno rješavali petljama u makroima.

Kreiranje prilagođenih funkcija pomoću LAMBDA i Name Managera

Jedan od najmoćnijih aspekata LAMBDA tehnologije je mogućnost pretvoriti bilo koju formulu u korisničku funkciju korištenjem Excelovog Upravitelja imena. Ovo omogućava vašoj funkciji da ima vlastito ime i da se koristi kao bilo koja druga izvorna funkcija u programu.

Tipičan tok rada je obično sljedeći: prvo, testirajte LAMBDA u ćelijiuključujući i definiciju i poziv s primjerima argumenata. Nakon što provjerite da li ispravno radi i vraća očekivani rezultat, kopirajte dio koji odgovara LAMBDA definiciji (bez završnog poziva) i zalijepite ga u Upravitelj imena.

U Upravitelju naziva kreirate novi naziv (na primjer, MojaPDVFunkcija, MojPopust, MojPonderiraniProsjek, itd.) i u polje "Odnosi se na" unosite:

=LAMBDA(var1; var2; …; varN; izračun)

Od tog trenutka nadalje, funkciju možete pozvati u bilo kojoj ćeliji radne knjige tako što ćete upisati njeno ime. kao da je to ugrađena funkcija, prosljeđujući vrijednosti parametara istim redoslijedom kojim ste ih definirali.

Ovo ima dvije očite prednosti: s jedne strane, Činite svoje stranice čitljivijima (Umjesto dugih formula, vidite funkciju s opisnim nazivom); s druge strane, centralizirate logiku na jednom mjestu. Ako kasnije želite promijeniti izračun, jednostavno izmijenite definiciju u Upravitelju naziva i sve formule koje je koriste bit će automatski ažurirane.

Praktični aspekti i dodatna razmatranja

Da biste zaista imali koristi od LAMBDA tehnologije i njenih povezanih funkcija, korisno je imati na umu neke praktične aspekte o njihovo ponašanje i njihovi zahtjeviPrvo, ove funkcije su dio modernih funkcija programa Excel, tako da vam je potrebna verzija koja već uključuje dinamičke nizove i nove funkcije LAMBDA, BYROW, BYCOL itd. One su obično dostupne u najnovijim izdanjima sistema Microsoft 365.

Još jedna relevantna tema je performanceIako su LAMBDA funkcije i traversal funkcije vrlo moćne, ako ih primijenite na ogromne raspone s vrlo složenom logikom, ponovno izračunavanje knjige može potrajati duže. Preporučljivo je dizajnirati LAMBDA funkcije imajući na umu efikasnost, izbjegavajući redundantne izračune i koristeći strukture poput LET za definiranje međuvrijednosti koje se mogu ponovno koristiti.

Takođe je ključno imati određeni redoslijed kada... imenovanje parametara i funkcijaKorištenje opisnih imena pomaže vam da shvatite logiku kada se vratite na datoteku mjesecima kasnije ili kada neko drugi mora raditi s vašim knjigama. Parametar pod nazivom amount, rate, dataRow ili valuesCol je mnogo jasniji od jednostavnog x ili yoa.

U vezi s greškom #KALC!Ova greška se obično pojavljuje kada Excel ne može izračunati izraz niza ili kada funkcija LAMBDA ne vraća valjan rezultat. Uvijek provjerite da li vaša formula ima dobro definiran izlaz i, ako radite s funkcijama niza, da li su dimenzije konzistentne (na primjer, da ne kombinujete nekompatibilne raspone veličina bez odgovarajuće transformacije).

Konačno, iako Lambda u mnogim slučajevima izbjegava potrebu za VBA, ne zamjenjuje ga u potpunosti. Postoje situacije u kojima automatizacija korištenjem makroa ostaje najbolja opcija, ali za ogroman broj prilagođenih izračuna i transformacija podataka, LAMBDA i njene pridružene funkcije Omogućit će vam da sav posao obavite unutar konvencionalnog okruženja formula u Excelu.

Zahvaljujući ovim mogućnostima, oni koji svakodnevno rade s proračunskim tablicama sada imaju mnogo fleksibilniji alati da dizajniraju vlastite proračune, sumiraju informacije po redovima ili kolonama, prolaze kroz matrice u potpunosti ili djelomično, generiraju detaljne kumulativne vrijednosti i kreiraju nove matrice izračunate u hodu, a sve to bez napuštanja jezika formula koji već poznaju.