Excel LET i LAMBDA funkcije: Potpuni vodič s primjerima

Kada radite s podacima u Excelu, dođe do toč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 stvaranje čistih, višekratno upotrebljivih i bržih za održavanje izračuna.

S ovim funkcijama i s nekoliko moderne matrične funkcije koji 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ćuje primjenu LAMBDA funkcije na retke, stupce ili elemente matrice na elegantan i učinkovit način.

Što su LET i LAMBDA u Excelu?

Funkcija LAMBDA omogućuje stvaranje prilagođenih funkcija korištenjem samog jezika Excel formule, bez makroa ili VBA: definirate parametre, napišete izračun i, ako želite, pozovete ga s argumentima kako biste dobili trenutni rezultat.

S druge strane, funkcija LET se koristi za dodijeliti imena međurezultatima unutar iste formule, tako da ih možete ponovno koristiti više puta bez ponovnog izračuna, čime se postiže čitljivost i učinkovitost u složenim tablicama.

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

Zamislite ovaj skup kao alate koji simulirati petlje i prolaske kroz nizovePredajete LAMBDA funkciju sa željenom transformacijom, a Excel radi ostalo, vraćajući vektore ili matrice s rezultatima.

Rezultat je da možete izraditi sofisticirane izračune bez pribjegavanja pomoćnim stupcima ili vanjskom kodu; sve je sadržano u čistim formulama, s manjim rizikom od pogrešaka i boljim održavanjem.

Kako testirati i definirati LAMBDA bez grešaka

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

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 njezino izvršenje kako biste potvrdili 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 njegov 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ća sintaksa LET-a je: =LET(nombre1; nombre_valor1; cálculo_o_nombre2; ); njegov cilj je imenovanje međurezultata i koristiti ta imena u konačnom izračunu.

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

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

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

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

konačno, izračun_ili_naziv3 (neobavezno) može biti konačni izračun ili treće ime; zapamtite da Posljednji argument funkcije LET uvijek mora biti izračun koji vraća očekivani rezultat.

Ovaj skalabilni uzorak omogućuje vam jasno povezivanje definicija i sprječava dupliciranje izraza unutar iste formule; ukratko, LET pojednostavljuje, pojašnjava i ubrzava složene radne bilježnice.

Moderne matrične funkcije koje koriste LAMBDA

Ove funkcije prolaze kroz polja primjenjujući transformaciju definiranu s LAMBDA, ponašajući se kao da su iteratori; navedite međuzbrojeve po retku ili stupcu, mapiranja, akumulirana i konstrukciju prilagođenih matrica.

BYROW: Primijenite LAMBDA na svaki redak

BYROW Izračunava LAMBDA za svaki redak u ulaznom rasponu i vraća vektor stupca rezultata; idealan je za stvaranje međuzbrojeva redak po redak ili indikatora bez pomoćnih stupaca.

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

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

BYCOL: primijenite LAMBDA na svaki stupac

BYCOL Funkcija radi slično kao i BYROW, ali prolazi kroz stupce; vraća vektor stupca s jednim rezultatom za svaki stupac u izvornom rasponu.

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

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

MAKEARRAY: stvaranje izračunatih nizova

NAPRAVITE generira matricu veličine koju navedete, izračunavajući svaki element s LAMBDA funkcijom koja prima indeks retka i indeks stupca; u nekim španjolskim okruženjima to se smatra IZRADI DATOTEKU.

Njegov opći oblik je: =MAKEARRAY(n_filas; n_columnas; LAMBDA(fila; columna; cálculo_por_posición))Svaki presjek retka/stupca prolazi kroz LAMBDA i vraća željenu vrijednost za tu koordinatu.

Primjer identifikatora pozicije: u bilo kojoj ćeliji koristite =ARCHIVOMAKEARRAY(3; 2; LAMBDA(fila; col; -(fila&col))) stvoriti niz od 3 retka s 2 stupca gdje svaki element spaja svoj redak i stupac (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 INDEXA.

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šćenja, normalizacije ili uvjetne oznake bez pomoćnih stupaca.

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 označavanja 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.

SCAN: akumulirano s međustanjima

SCAN Iterira kroz niz s akumulatorom LAMBDA i vraća sva međustanja, ne samo konačno; idealan je za tekući zbrojevi, kumulativni postoci i izračune koji ovise o prethodnom rezultatu.

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

Za klasični jackpot preko A31:A36piše: =SCAN(0; A31:A36; LAMBDA(acum; param1; acum + param1)); dobit ćeš niz parcijalnih zbrojeva 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 s LET-om a zatim svako međustanje podijelite s tim ukupnim brojem.

SMANJENJE: konačni akumulirani iznos

SMANJITI Radi kao SCAN, ali vraća samo posljednje stanje akumulatora; to jest, svodi cijeli niz na jednu vrijednost primjenom transformacije definirane s LAMBDA.

Njegov uzorak je: =REDUCE(valor_inicial; matriz; LAMBDA(acumulador; valor; nuevo_acumulado)); konačna vrijednost je obično zbroj, produkt, logički presjek 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, dobivate ukupan iznos bez otkrivanja međukoraka.