Excel LET i LAMBDA funkcije: Kompletan vodič s primjerima

Kada radite s podacima u Excelu, dođe do tačke u kojoj ponovljene formule i beskrajni rasponi postaju neupravljivi; tu oni dolaze do izražaja Funkcije LET i LAMBDA, dva ključna dijela za kreiranje čistih, višekratno upotrebljivih i bržih za održavanje proračuna.

S ovim funkcijama, i s nekoliko moderne matrične funkcije koje se oslanjaju na njih, možete definirati međuimena, enkapsulirati izračune i primijeniti transformacije na matrice kao da izvodite petlju; BYROW, BYCOL, MAP, SCAN, REDUCE i MAKEARRAY Oni su šlag na torti koji vam omogućava da primijenite LAMBDA funkciju na redove, kolone ili elemente matrice na elegantan i efikasan način.

Šta su LET i LAMBDA u Excelu?

LAMBDA funkcija omogućava kreirajte prilagođene funkcije koristeći sam jezik excel formule, bez makroa ili VBA: definirate parametre, pišete izračun i, ako želite, pozivate ga s argumentima kako biste dobili trenutni rezultat.

S druge strane, funkcija LET se koristi za dodijelite imena međurezultatima unutar iste formule, tako da ih možete ponovo koristiti više puta bez ponovnog izračunavanja, čime se dobija na čitljivosti i efikasnosti u složenim tabelama.

Oko LAMBDA funkcije pojavile su se funkcije koje tretiraju nizove kao kolekcije koje treba pregledati: BYROW (po redovima), BYCOL (po kolonama), MAP (elementi mape), SCAN (akumulira i vraća međustanja), SMANJITE (vraća samo konačni kumulativ) i NAPRAVITE EARRAY (generira matrice na zahtjev).

Zamislite ovaj set kao alate koji simulirati petlje i prelaske preko nizovaProsljeđujete LAMBDA funkciju sa željenom transformacijom, a Excel radi ostalo, vraćajući vektore ili matrice s rezultatima.

Rezultat je da možete kreirati sofisticirane proračune bez pribjegavanja pomoćnim kolonama ili vanjskom kodu; sve je sadržano u čistim formulama, sa manjim rizikom od grešaka i boljim održavanjem.

Kako testirati i definirati LAMBDA funkciju bez grešaka

Dobra praksa je izgraditi i validirati LAMBDA funkciju direktno u ćeliji Prije definiranja kao funkcije za višekratnu upotrebu: definirajte parametre, napišite izračun i dodajte testni poziv sa željenim argumentima na kraju.

Ako ne napravite taj probni poziv, relativno je lako naletjeti na to Greška #CALC! U nepotpunim formulama, završni poziv vas prisiljava da procijenite izračun i potvrdite da je struktura ispravna.

Najjasniji način rada je: LAMBDA(parametar1; parametar2; …; izračun)(argument1; argument2; …); dakle, u jednoj ćeliji pišete definiciju i njeno izvršenje kako biste validirali rezultat.

Na primjer, da biste broju dodali 1, možete pokušati: =LAMBDA(number; number + 1)(1)Ovaj izraz vraća vrijednost 2, što potvrđuje da su LAMBDA i njen poziv dobro isplanirani.

Nakon provjere, možete registrirati tu LAMBDA funkciju kao prilagođenu funkciju (s imenom) ili ubacite ga u druge formule i matrične funkcije za rješavanje ambicioznijih problema.

LET: sintaksa, argumenti i razmatranja

Opšta sintaksa LET funkcije je: =LET(nombre1; nombre_valor1; cálculo_o_nombre2; ); njegov cilj je imenovanje međurezultata i koristite ta imena u konačnom proračunu.

Ključni argumenti LET-a: ime1 (obavezno) je prvi identifikator koji dodijelite; mora počinjati slovom, ne smije biti rezultat formule i ne može ući u sukob sa sintaksa raspona u Excelu.

Argument vrijednost_name1 (obavezno) je vrijednost ili izraz koji će biti povezan sa ime1Na ovaj način izbjegavate ponavljanje istog izračuna i poboljšava performanse ako ćete ga ponovo koristiti.

Treći argument, naziv_izračuna_ili_naziv2 (obavezno), to može biti jedna od dvije stvari: ili konačni obračun koji koristi sva deklarirana imena ili drugo ime koje treba definirati; ako odlučite deklarirati ime, tada ćete morati navesti i vrijednost_name2 y naziv_izračuna_ili_naziv3.

vrijednost_name2 (opciono) dodjeljuje vrijednost imenu deklariranom u prethodnom koraku; ako nastavite s lančanjem imena, ponovi uzorak ime/vrijednost dok posljednji argument nije izračun.

Na kraju, naziv_izračuna_ili_naziv3 (opciono) može biti konačni izračun ili treće ime; zapamtite da Posljednji argument funkcije LET mora uvijek biti izračunavanje. koji vraća očekivani rezultat.

Ovaj skalabilni obrazac vam omogućava jasno povezivanje definicija i sprečava dupliranje izraza unutar iste formule; ukratko, LET pojednostavljuje, pojašnjava i ubrzava složene radne sveske.

Moderne matrične funkcije koje koriste LAMBDA

Ove funkcije prolaze kroz nizove primjenjujući transformaciju definiranu pomoću LAMBDA, ponašajući se kao da su iteratori; navedite međuzbirove po redu ili koloni, mapiranja, akumulirana i konstrukcija prilagođenih matrica.

BYROW: Primijenite LAMBDA na svaki red

BYROW Izračunava LAMBDA funkciju za svaki red u ulaznom rasponu i vraća vektor kolone rezultata; idealan je za kreiranje međuzbirova red po red ili indikatora bez pomoćnih kolona.

Njegova sintaksa je: =BYROW(rango; LAMBDA(fila; cálculo_por_fila)), gdje je parametar fila predstavlja trenutni red koji funkcija obrađuje; LAMBDA vraća jednu vrijednost po redu.

Praktičan primjer: ako je vaša matrica u B2:D7 i želite sabrati svaki red, u E2 piši =BYROW(B2:D7; LAMBDA(fila; SUMA(fila))); dobit ćeš vektor sa zbirom svakog reda, spreman za upotrebu u analizi ili grafici.

BYCOL: primijenite LAMBDA na svaku kolonu

BYCOL Funkcija funkcioniše slično kao BYROW, ali prolazi kroz kolone; vraća vektor kolone sa jednim rezultatom za svaku kolonu u izvornom opsegu.

Sintaksa je: =BYCOL(rango; LAMBDA(columna; cálculo_por_columna))parametar kolona izlaže trenutnu kolonu LAMBDA funkciji, koja proizvodi jednu vrijednost po koloni.

Praktičan primjer: s podacima u B2:D7, mjesto u B8 formula =BYCOL(B2:D7; LAMBDA(columna; PROMEDIO(columna))); dobit ćeš vektor sa prosjekom svake kolone, korisno kao sažetak ili kontrola kvalitete.

MAKEARRAY: Kreiranje izračunatih nizova

NAPRAVITE EARRAY generira matricu veličine koju navedete, izračunavajući svaki element pomoću LAMBDA funkcije koja prima indeks reda i indeks kolone; u nekim španskim okruženjima to se smatra NAPRAVI DATOTEKU.

Njegov opšti oblik je: =MAKEARRAY(n_filas; n_columnas; LAMBDA(fila; columna; cálculo_por_posición))Svaki presek reda/kolone prolazi kroz LAMBDA funkciju i vraća željenu vrijednost za tu koordinatu.

Primjer identifikatora pozicije: u bilo kojoj ćeliji koristite =ARCHIVOMAKEARRAY(3; 2; LAMBDA(fila; col; -(fila&col))) da kreirate niz od 3 reda sa 2 kolone gdje svaki element spaja svoj red i kolonu (i prisiljen je numerirati znakom minus).

Još jedan primjer kombiniranja nekoliko funkcija: =LET(arrPos; ARCHIVOMAKEARRAY(3; 2; LAMBDA(fila; col; -(fila&col))); arrPosF; COINCIDIR(arrPos; K.ESIMO.MENOR(arrPos; SECUENCIA(6))); INDICE(G8:G13; arrPosF))S ovom konstrukcijom, generirate pozicije, dobivate 6 maloljetnika i mapirate ih preko raspona pomoću INDEX-a.

MAP: transformiranje elementa u element

MAP uzima jedan ili više nizova i vraća drugi iste veličine primjenom LAMBDA funkcije na svaki element; savršeno je za čišćenje, normalizacija ili uslovne oznake bez pomoćnih kolona.

Osnovna sintaksa je: =MAP(matriz; LAMBDA(valor; transformación)); ako proslijedite više nizova, LAMBDA prima više parametara, jedan po nizu; rezultat čuva dimenzije ulazne matrice.

Klasičan primjer za označavanje parnih i neparnih brojeva u A21:A26: =MAP($A$21:$A$26; LAMBDA(param1; SI(ES.PAR(param1); param1; "-")))Dakle, svaki element se zamjenjuje samim sobom ako je paran, ili crticom ako nije; sva obrada je vektorska.

SKENIRANJE: akumulirano sa međustanjima

SCAN Iterira kroz niz s akumulatorom LAMBDA i vraća sva međustanja, ne samo konačno; idealan je za tekući ukupni iznosi, kumulativni procenti i proračune koji zavise od prethodnog rezultata.

Struktura je: =SCAN(valor_inicial; matriz; LAMBDA(acumulador; valor; nuevo_acumulado)), gdje početna_vrijednost akumulator se pokreće, matrica je raspon koji treba obraditi i LAMBDA definira tranziciju između država.

Za klasični jackpot preko A31:A36, pisati: =SCAN(0; A31:A36; LAMBDA(acum; param1; acum + param1)); dobit ćeš niz parcijalnih suma u jednom koraku.

A ako želite kumulativni omjer, možete kombinirati LET i SCAN: =LET(total; SUMA(A31:A36); SCAN(0; A31:A36; LAMBDA(acum; param1; (acum + param1))) / total)Ovdje prvo izračunavate ukupno sa LET-om a zatim svako međustanje podijelite s tim ukupnim brojem.

SMANJENJE: konačno akumulirano

SMANJITE Radi kao SCAN, ali vraća samo posljednje stanje akumulatora; to jest, svodi cijeli niz na jednu vrijednost primjenjujući transformaciju definiranu pomoću LAMBDA.

Njegov uzorak je: =REDUCE(valor_inicial; matriz; LAMBDA(acumulador; valor; nuevo_acumulado)); konačna vrijednost je obično zbir, proizvod, logički presek ili rezultat procesa koji vas zanima.

Primjer konačne tekuće sume na A1:A6: =REDUCE(0; A1:A6; LAMBDA(acum; param1; acum + param1)); u jednom izrazu, dobijate ukupan iznos bez otkrivanja međukoraka.