Lambda funktsioon Excelis: täielik juhend ja praktilised näited

Informatec Digital » Ressursid » Lambda funktsioon Excelis: täielik juhend ja praktilised näited

Exceli lambdafunktsioon on muutnud Microsofti arvutustabelites valemitega töötamist revolutsiooniliselt. See võimaldab teil luua oma kohandatud funktsioone, kasutades ainult Exceli valemikeelt, ilma et peaksite puudutama ühtegi VBA või traditsioonilise programmeerimise rida . See on justkui võimalus programmi lisada uusi, kohandatud natiivfunktsioone.

Lisaks on LAMBDA ümber tekkinud mitmeid seotud funktsioone, näiteks BYROW, BYCOL, MAP, SCAN, REDUCE ja MAKEARRAY , mis on loodud töötama vahemike ja maatriksitega palju paindlikumal ja võimsamal viisil. Need funktsioonid käituvad laias laastus nagu väikesed tsüklid, mis itereerivad andmeid ja rakendavad LAMBDA poolt määratletud teisendust, avades tohutu hulga võimalusi täpsemaks analüüsiks otse arvutustabelis.

Mis on Exceli LAMBDA funktsioon ja milleks seda kasutatakse?

Lambda funktsioon on tööriist , mis võimaldab teil defineerida kohandatud funktsioone ainult Exceli valemite abil. VBA-s programmeerimise või makrodele lootmise asemel saate mis tahes keerulise arvutuse koondada ühte korduvkasutatavasse funktsiooni, millel on oma parameetrid ja selge ning puhas lõpptulemus.

Praktikas muudab LAMBDA iga valemi funktsiooniks , mida saab kasutada nii mitu korda kui soovite. Selle peamine eelis on see, et see integreerub sujuvalt ülejäänud Exceli arvutusmootoriga ja seda saab kombineerida standardfunktsioonide, viidete, määratletud nimede ja dünaamiliste massiividega.

Lahtris otse kasutades on põhisüntaks järgmine:

=LAMBDA(parameeter1; parameeter2; …; parameeterN; arvutus)(väärtus1; väärtus2; …; väärtusN)

Selles struktuuris on parameeter1, parameeter2, …, parameeterN funktsiooni muutujatele antavad nimed, samas kui arvutus on valem, mis kasutab neid parameetreid tulemuse genereerimiseks. Lõpuks, teises sulgudes annate tegelikud väärtused , mis need parameetrid funktsiooni käivitamisel võtavad.

Kui kasutate Exceli nimehaldurit püsiva LAMBDA-funktsiooni loomiseks, muutub süntaks veidi, kuna defineerite funktsiooni seal, aga te seda veel ei kutsu. Sellisel juhul oleks vorming järgmine:

=LAMBDA(var1; vari2; …; varN; arvutus)

Hiljem kutsuksite seda funktsiooni nimehalduris antud nimega, sisestades lihtsalt nime ja argumendid täpselt nagu SUM, AVERAGE või mis tahes muu standardfunktsiooni puhul.

Parimad tavad lambda funktsioonide loomisel ja testimisel

Lambda-funktsioonidega töötamise alustamisel on oluline järgida mõnda juhist, et tagada nende ootuspärane käitumine ja mitte raisata aega keeruliste vigade silumisele. Üks praktilisemaid viise alustamiseks on luua ja testida Lambda-funktsioon otse lahtris.

Tavaline protseduur on kõigepealt kirjutada täielik valem koos LAMBDA definitsiooniga ja kutsuda see samasse avaldisse, et saaksite kohe näha, kas tulemus on ootuspärane. Nii saate enne nimega funktsioonina salvestamist tuvastada süntaksi- või loogikavigu.

Näiteks väga tüüpiline testi struktuur oleks järgmine:

=LAMBDA(); arvutus(testi_väärtused)

Millegi väga lihtsa, näiteks arvule 1 liitmise, kontrollimiseks võiksite kasutada järgmist:

=LAMBDA(arv; arv + 1)(1)

Sel juhul tagastaks funktsioon väärtuse 2. See on väga lihtne näide, kuid see illustreerib mehaanikat: kõigepealt defineerid parameetrid ja arvutuse ning seejärel kutsud funktsiooni välja, edastades vastava argumendi.

#CALC! vea vältimiseks on oluline soovitus tagada, et teie LAMBDA funktsioon tagastaks alati tulemuse . Selle saavutamiseks lisage lõppu selgelt avaldis, mis annab tulemuseks ühe väärtuse või massiivi, olenevalt teie vajadustest. Kui testimise ajal kuvatakse viga #CALC!, kontrollige, kas valem genereerib tegelikult midagi, mida Excel saab tulemusena kuvada.

Kui olete LAMBDA-funktsiooni lahtris testinud ja veendunud, et see töötab õigesti, on hea aeg see loogika nimehaldurisse üle viia ja muuta see kogu lehel või töövihikus korduvkasutatavaks kohandatud funktsiooniks.

LAMBDA seos uute maatriksfunktsioonidega

LAMBDA ümber on tekkinud rida täiustatud funktsioone, näiteks BYROW, BYCOL, MAP, SCAN, REDUCE ja MAKEARRAY (viimane on mõnes versioonis tõlgitud kui ARCHIVOMAKEARRAY), mis tuginevad LAMBDA-le teisenduste rakendamiseks vahemikele ja täielikele maatriksitele.

Üldine idee on see, et need funktsioonid itereerivad läbi andmevahemike (ridade, veergude või elementide kaupa) ja käivitavad iga elemendi või elementide rühma jaoks teie määratletud lambda-funktsiooni. Teisisõnu, need töötavad nagu tsüklid, kuid on integreeritud Exceli valemikeelde.

See võimaldab teil teha toiminguid, mis varem nõudsid abiveergusid, vahetabeleid või isegi makrosid, otse ühe massiivivalemiga, mis laiendab ja tagastab tulemused kogu vahemiku kohta korraga.

LAMBDA-ga seotud funktsioonidest paistavad silma REDUCE, MAP, SCAN, BYCOL, BYROW ja MAKEARRAY , millel kõigil on kindel eesmärk: ridade läbimine, teisenduste rakendamine veergude kaupa, tulemuste kogumine, nullist arvutatud maatriksite loomine jne. Neil kõigil on ühine joon, et nad kasutavad LAMBDA-d sisemise "mootorina", millele nad edastavad maatriksis liikudes väärtusi ja akumulaatoreid.

BYROW funktsioon: itereerib ridade kaupa ja tagastab tulemused rida-realt

Funktsiooni BYROW kasutatakse LAMBDA funktsiooni rakendamiseks igale vahemiku reale ja massiivi tagastamiseks, millel on iga töödeldud rea kohta üks väärtus. See on väga tõhus viis vahesummade või statistika arvutamiseks rida-realt ilma valemeid vertikaalselt kopeerimata.

Selle üldine süntaks on:

=BYROW(maatriks; LAMBDA(rida; avaldis))

Esimene argument on massiiv või vahemik, mida soovite läbida (näiteks B2:D7) ja teine ​​on LAMBDA-funktsioon, mis võtab parameetrina iga rea ​​selles vahemikus ükshaaval. LAMBDA-funktsioon tagastab väärtuse, mille soovite selle reaga seostada (võimalik, et summa, keskmine, loogikakontroll jne).

Kujutage ette, et teil on andmetabel vahemikus B2:D7 ja soovite saada iga rea ​​​​vahesumma. Lahtrisse E2 võiksite kirjutada midagi sellist:

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

Tulemuseks oleks väljundvektor, millel on maatriksi B2:D7 iga rea ​​kohta üks väärtus, kus iga väärtus tähistab selle rea elementide summat . Nii ei pea te SUM-i rida-realt kirjutama: BYROW teeb seda teie eest ja kuvab tulemuse alammenüüs.

BYCOL funktsioon: rakenda LAMBDA-d veergude kaupa

Väga sarnane funktsiooniga BYROW, on BYCOL funktsioon loodud massiivi läbivaatamiseks veergude, mitte ridade kaupa. See rakendab LAMBDA-funktsiooni vahemiku igale veerule ja tagastab tulemuste massiivi, kus iga element vastab veerule.

Selle tüüpiline süntaks on:

=BYCOL(massiiv; LAMBDA(veerg; avaldis))

Sel juhul on LAMBDA poolt vastuvõetud parameetriks igal sammul töödeldava maatriksi kogu veerg . Sarnaselt funktsiooniga BYROW tagastab see funktsioon vektori, kuid see on loodud töötama veergude kaupa summade või indikaatoritega.

Jätkates eelmise näitega, kui soovite arvutada vahemiku B2:D7 iga veeru keskmist , võite lahtrisse B8 paigutada sellise valemi:

=BYCOL(B2:D7; LAMBDA(veerg; KESKMINE(veerg)))

Tulemuseks on maatriks, millel on sama arv veerge kui lahtrites B2:D7, kus iga positsioon sisaldab selle veeru keskmist . Nii saate kõik keskmised ühe hoobiga kätte ilma valemeid lohistamata või suhteliste viidete pärast muretsemata.

MAKEARRAY funktsioon (MAKEARRAYFILE): loo arvutatud massiivid

Funktsioon MAKEARRAY (mõnikord kuvatakse ka kui ARCHIVOMAKEARRAY) võimaldab teil luua täiesti uue massiivi, määrates ridade ja veergude arvu ning arvutades iga elemendi LAMBDA-funktsiooni abil. See ei alusta olemasolevast vahemikust, vaid loob massiivi nullist.

Selle üldine süntaks on:

=MAKEARRAY(read; veerud; LAMBDA(rida; veerg; avaldis))

Argument „read” näitab, mitu rida väljundmaatriksis on, argument „veerud” defineerib veergude arvu ja LAMBDA funktsioon saab parameetritena igas iteratsioonis arvutatavad rea- ja veeruindeksid. Selle teabe abil saate luua praktiliselt mis tahes numbrilise või tekstilise mustri.

Väga illustreeriv näide on maatriksi loomine, kus iga element näitab oma positsiooni. Mis tahes lahtrisse võiksite kirjutada midagi sellist:

=MAKEARRAYFILE(3; 2; LAMBDA(rida; veerg; -(rida ja veerg)))

Tulemuseks oleks 3-realine ja 2-veeruline maatriks , kus iga väärtus tähistab rea ja veeru kombinatsiooni (näiteks 11, 12, 21, 22, 31, 32), mis on teisendatud vastavalt sisestatud arvutusele (antud juhul rea ja veeru liitmisele rakendatud negatiivne märk).

Teine huvitav MAKEARRAY kasutusala on vektori teisendamine massiiviks, kontrollides samal ajal elementide arvu. Oletame, et soovite luua massiivi vertikaalse vahemiku esimese kuue väärtusega. Esmalt saate luua positsioonide massiivi MAKEARRAYFILE abil, leida k väikseimat positsiooniväärtust ja lõpuks kasutada INDEX-i, et leida algsest vahemikust tegelikud elemendid.

Mitme funktsiooni kombineeriva valemi näide võiks olla sellise struktuuriga:

=LET(arrPos; MAKEARRAYFILE(3; 2; LAMBDA(rida; veerg; -(rida & veerg))); arrPosF; MATCH(arrPos; LeastK(arrPos; JÄRJEND(6))); INDEKS(G8:G13; arrPosF))

Siin kasutatakse LET-funktsiooni vahefunktsioonide (arrPos, arrPosF) defineerimiseks, positsioonide massiiv konstrueeritakse ARCHIVOMAKEARRAY (3×2) abil, SMALLEST ja SEQUENCE abil valitakse 6 väikseimat positsiooni ning lõpuks kasutatakse INDEX-funktsiooni vastavate väärtuste tagastamiseks vahemikust G8:G13. See on võimas näide sellest, kuidas kombineerida LAMBDA ja dünaamilisi massiivifunktsioone keerukate teisenduste tegemiseks ilma makrodeta.

MAP-funktsioon: elementide kaupa teisendamine

MAP- funktsiooni kasutatakse ühe või mitme massiivi samaaegseks läbivaatamiseks ja uue massiivi tagastamiseks, kus iga väljundelement arvutatakse vastava(te)le sisendelemendi(te)le LAMBDA-funktsiooni rakendamise teel. See on samaväärne funktsionaalse programmeerimise klassikalise "map"-funktsiooniga.

Põhisüntaks on:

=MAP(maatriks1; LAMBDA_või_rohkem_maatrikseid)

Lihtsamal kujul võtab see ühe massiivi ja LAMBDA-funktsiooni, mis saab sellest massiivist iga väärtuse. See LAMBDA-funktsioon teisendab väärtuse ja tagastab uue versiooni, mis moodustab osa väljundmassiivist, säilitades samad mõõtmed kui algsel massiivil.

Näiteks kui soovite itereerida läbi vertikaalse vahemiku A21:A26 ja jätta algse numbri, kui see on paarisarv, või sidekriipsu, kui see on paaritu arv, võiksite kasutada midagi sellist:

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

Sel juhul analüüsib MAP iga elementi vahemikus A21:A26. LAMBDA kontrollib funktsiooniga IS.EVEN, kas tegemist on paarisarvuga. Kui on, tagastab see arvu enda; vastasel juhul tagastab see kriipsu. Tulemuseks on massiiv, mis on sama suur kui algne vahemik, kuid teisendus on rakendatud igale elemendile.

See lähenemisviis on väga kasulik tingimusliku loogika, teksti teisendamise, väärtuse normaliseerimise või mõne muu lihtsa toimingu rakendamiseks, vältides abiveergude ja korduvate valemite kasutamist.

SCAN-funktsioon: kumulatiivsed ja vahetulemused

Funktsiooni SCAN kasutatakse massiivi uurimiseks, rakendades igale väärtusele LAMBDA funktsiooni ja genereerides väljundmassiivi, mis näitab kõiki akumuleerimisprotsessi vaheväärtusi . See on väga sarnane funktsiooniga REDUCE, kuid ainult lõpptulemuse tagastamise asemel säilitab see iga sammu.

Selle üldine süntaks on:

=SCAN(; massiiv; LAMBDA(akumulaator; väärtus))

Esimene argument, mis on valikuline, on akumulaatori algväärtus (näiteks 0 liitmise korral). Teine argument on massiiv või vahemik, mida soovite läbida. Lõpuks saab LAMBDA funktsioon kaks parameetrit: akumulaatori (osaline tulemus kuni selle punktini) ja töödeldava massiivi praegune väärtus.

Igal sammul hindab SCAN LAMBDA väärtust, uuendab akumulaatorit ja genereerib väljundmaatriksisse uue elemendi saadud väärtusega. Nii saadakse akumuleeritud väärtuste või progresseeruvate teisenduste jada.

Tüüpiline näide on kumulatiivse summa (jooksva summa) arvutamine väärtuste komplekti põhjal ja sealt edasi ka suhtelise kumulatiivse sageduse saamine. Kujutage ette, et teil on andmed lahtrites A31:A36 ja soovite absoluutset kumulatiivset summat:

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

See valem itereerib vahemikus A31:A36, lisades iga väärtuse eelmisele summale. Tulemuseks on massiiv, millel on sama arv elemente kui algsel vahemikus, kuid iga positsioon kuvab kumulatiivset summat kuni selle punktini.

Sellest kumulatiivsest summast on kumulatiivset protsentuaalset sagedust lihtne arvutada, jagades iga kumulatiivse summa kogusummaga. Näiteks võiksite esmalt summa määratleda funktsiooni SUM abil ja seejärel uuesti funktsiooni SCAN rakendada:

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

Sel juhul määrab LET koguvahemiku A31:A36 summa. Seejärel genereerib SCAN kumulatiivsete väärtuste jada ja jagades selle koguarvuga, saadakse iga sammu suhteline kumulatiivne sagedus , kõik ühes maatriksvalemis.

REDUCE funktsioon: taandamine ühe akumuleeritud väärtuseni

Funktsioon REDUCE itereerib samuti massiivi läbi, rakendades igale elemendile LAMBDA funktsiooni, kuid erinevalt funktsioonist SCAN on siin oluline ainult akumuleerimisprotsessi lõpptulemus . See tähendab, et see teostab sama tüüpi iteratsiooni nagu SCAN, kuid tagastab ainult akumulaatori viimase väärtuse.

Selle süntaks on:

=VÄHENDA(; massiiv; LAMBDA(akumulaator; väärtus))

Nii nagu SCAN-is, määrab algväärtus akumulaatori alguspunkti, maatriks on töödeldav vahemik ja LAMBDA parameetriteks on praegune akumulaator ja sel hetkel loetav väärtus.

Väga tüüpiline kasutusala on jooksva summa või kumulatiivse tehte arvutamine, kus teid huvitab ainult viimane tulemus . Näiteks A1:A6 summeerimiseks REDUCE abil võite kirjutada:

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

Siin itereerib REDUCE lahtrites A1:A6 ja uuendab igal sammul kumulatiivset väärtust, lisades praeguse lahtri väärtuse (parameeter 1). Lõpuks tagastab see ühe väärtuse: kogusumma. See on kontseptuaalselt sarnane SUM-i kasutamisega, kuid REDUCE abil saate defineerida mis tahes keerukama akumuleerimisloogika, mitte ainult lihtsaid summasid.

REDUCE'i võimsus seisneb võimes töötada igal sammul eelmise tulemusega ja jätkata toimingute rakendamist kuni protsessi lõpuni. See võimaldab teil rakendada keerukaid kohandatud arvutusi, mida traditsiooniliselt käsitleti makrode tsüklitega.

Kohandatud funktsioonide loomine LAMBDA ja nimehalduriga

Üks LAMBDA võimsamaid omadusi on võime teisendada mis tahes valem kasutajafunktsiooniks, kasutades Exceli nimehaldurit. See võimaldab teie funktsioonil anda oma nime ja seda kasutada nagu iga teist programmi natiivfunktsiooni.

Tüüpiline töövoog on järgmine: esmalt testige LAMBDA funktsiooni lahtris , sh nii definitsiooni kui ka näidiskõne abil. Kui olete veendunud, et see töötab õigesti ja tagastab oodatud tulemuse, kopeerige LAMBDA definitsioonile vastav osa (ilma viimase kõneta) ja kleepige see nimehaldurisse.

Nimehalduris loote uue nime (näiteks MyKM-funktsioon, MyDiscount, MyWeightedAverage jne) ja sisestate väljale „Viitab” järgmise:

=LAMBDA(var1; vari2; …; varN; arvutus)

Sellest hetkest alates saate funktsiooni oma töövihiku mis tahes lahtris välja kutsuda, kirjutades selle nime justkui sisseehitatud funktsioonina , edastades parameetrite väärtused samas järjekorras, nagu te need defineerisite.

Sellel on kaks selget eelist: esiteks muudab see teie arvutustabelid loetavamaks (pikkade valemite asemel näete kirjeldava nimega funktsiooni); teiseks koondab see loogika ühte kohta. Kui soovite hiljem arvutust muuta, muutke lihtsalt nimehalduris definitsiooni ja kõik seda kasutavad valemid värskendatakse automaatselt.

Praktilised aspektid ja lisakaalutlused

LAMBDA ja sellega seotud funktsioonide täielikuks ärakasutamiseks on kasulik mõista nende käitumise ja nõuete mõningaid praktilisi aspekte . Esiteks on need funktsioonid osa tänapäevastest Exceli funktsioonidest, seega vajate versiooni, mis juba sisaldab dünaamilisi massiive ja uuemaid funktsioone LAMBDA, BYROW, BYCOL ja muid. Need on tavaliselt saadaval Microsoft 365 uusimates versioonides.

Teine oluline küsimus on jõudlus : kuigi LAMBDA ja läbimisfunktsioonid on väga võimsad, võib nende rakendamine tohututele vahemikele ja väga keerulise loogikaga raamatu ümberarvutamine kauem aega võtta. Soovitatav on LAMBDA funktsioonide kavandamisel efektiivsust silmas pidades vältida üleliigseid arvutusi ja kasutada korduvkasutatavate vaheväärtuste määratlemiseks selliseid struktuure nagu LET.

Samuti on oluline säilitada parameetrite ja funktsioonide ühtne nimetamiskonventsioon . Kirjeldavate nimede kasutamine aitab teil loogikast aru saada, kui faili uuesti külastate kuid hiljem või kui keegi teine ​​​​peab teie töövihikutega töötama. Parameeter nimega amount, rate, dataRow või valuesCol on palju selgem kui lihtsalt x või yoa.

#CALC! vea puhul kuvatakse see tavaliselt siis, kui Excel ei suuda massiiviavaldist arvutada või kui LAMBDA funktsioon ei tagasta kehtivat tulemust. Veenduge alati, et teie valemil on täpselt määratletud väljund ja massiivifunktsioonide korral, et mõõtmed oleksid ühtsed (näiteks, et te ei ühenda ühildumatuid suurusvahemikke ilma korraliku teisenduseta).

Lõpuks, kuigi LAMBDA välistab paljudel juhtudel VBA vajaduse, ei asenda see seda täielikult. On olukordi, kus makroautomaatika jääb parimaks valikuks, kuid suure hulga kohandatud arvutuste ja andmete teisenduste puhul võimaldavad LAMBDA ja sellega seotud funktsioonid teil kogu oma töö Exceli tavapärases valemikeskkonnas hoida.

Tänu neile võimalustele on neil, kes igapäevaselt arvutustabelitega töötavad, nüüd palju paindlikumad tööriistad oma arvutuste kujundamiseks, teabe ridade või veergude kaupa kokkuvõtmiseks, maatriksite täielikuks või osaliseks läbimiseks, detailsete kokkuvõtete genereerimiseks ja uute lennult arvutatavate maatriksite loomiseks – seda kõike ilma juba tuttavast valemikeelest lahkumata.