Lambda funkcija programmā Excel: pilnīga rokasgrāmata un praktiski piemēri

Informatec Digital » Resursi » Lambda funkcija programmā Excel: pilnīga rokasgrāmata un praktiski piemēri

Lambda funkcija programmā Excel ir revolucionizējusi darbu ar formulām Microsoft izklājlapās. Tā ļauj jums izveidot savas pielāgotās funkcijas, izmantojot tikai Excel formulu valodu, nepieskaroties nevienai VBA vai tradicionālās programmēšanas rindiņai . Tas ir tā, it kā jums būtu iespēja programmai pievienot jaunas, pielāgotas vietējās funkcijas.

Turklāt ap LAMBDA ir parādījušās vairākas saistītas funkcijas, piemēram , BYROW, BYCOL, MAP, SCAN, REDUCE un MAKEARRAY , kas paredzētas darbam ar diapazoniem un matricām daudz elastīgākā un efektīvākā veidā. Šīs funkcijas, vispārīgi runājot, darbojas kā mazi cikli, kas atkārto datus un piemēro LAMBDA definētu transformāciju, paverot plašas iespējas uzlabotai analīzei tieši izklājlapā.

Kas ir LAMBDA funkcija programmā Excel un kam tā paredzēta?

Lambda funkcija ir rīks, kas ļauj definēt pielāgotas funkcijas , izmantojot tikai Excel formulas. Tā vietā, lai programmētu VBA vai paļautos uz makro, jebkuru sarežģītu aprēķinu var iekapsulēt vienā, atkārtoti izmantojamā funkcijā ar saviem parametriem un skaidru, tīru gala rezultātu.

Praksē LAMBDA jebkuru formulu pārvērš funkcijā , ko var atkārtoti izmantot tik reižu, cik nepieciešams. Tās galvenā priekšrocība ir tā, ka tā nemanāmi integrējas ar pārējo Excel aprēķinu programmu un to var kombinēt ar standarta funkcijām, atsaucēm, definētiem nosaukumiem un dinamiskajiem masīviem.

Pamata sintakse, ja to lieto tieši šūnā, ir šāda:

=LAMBDA(parametrs1; parametrs2; …; parametrsN; aprēķins)(vērtība1; vērtība2; …; vērtībaN)

Šajā struktūrā parametrs1, parametrs2, …, parametrsN ir nosaukumi, ko piešķirat mainīgajiem funkcijas ietvaros, savukārt aprēķins ir formula, kas izmanto šos parametrus, lai ģenerētu rezultātu. Visbeidzot, otrajā iekavu komplektā jūs nododat faktiskās vērtības , ko šie parametri iegūs, kad funkcija tiks izpildīta.

Ja pastāvīgas LAMBDA funkcijas izveidei izmantojat programmas Excel nosaukumu pārvaldnieku , sintakse nedaudz mainās, jo funkcija tiek definēta tur, bet vēl netiek izsaukta. Šādā gadījumā formāts būtu šāds:

=LAMBDA(var1; var2; …; varN; aprēķins)

Vēlāk jūs varētu izsaukt šo funkciju, izmantojot nosaukumu, ko tai piešķīrāt nosaukumu pārvaldniekā, vienkārši ierakstot nosaukumu un argumentus tāpat kā SUM, AVERAGE vai jebkuru citu standarta funkciju.

Labākā prakse, veidojot un testējot Lambda funkcijas

Sākot darbu ar Lambda funkcijām, ir svarīgi ievērot dažas vadlīnijas, lai nodrošinātu, ka tās darbojas kā paredzēts , un netērējat laiku sarežģītu kļūdu atkļūdošanai. Viens no praktiskākajiem veidiem, kā sākt, ir izveidot un pārbaudīt Lambda funkciju tieši šūnā.

Parasti vispirms tiek uzrakstīta pilna formula ar LAMBDA definīciju un izsaukums tajā pašā izteiksmē, lai jūs varētu uzreiz redzēt, vai rezultāts atbilst gaidītajam. Tādā veidā jūs varat atklāt sintakses vai loģikas kļūdas, pirms saglabājat to kā nosauktu funkciju.

Piemēram, ļoti tipiska testa struktūra būtu šāda:

=LAMBDA(); aprēķins)(testa_vērtības)

Lai pārbaudītu kaut ko ļoti vienkāršu, piemēram, skaitļa pievienošanu 1, varat izmantot:

=LAMBDA(skaitlis; skaitlis + 1)(1)

Šajā gadījumā funkcija atgrieztu vērtību 2. Tas ir ļoti vienkāršs piemērs, taču tas kalpo, lai ilustrētu mehāniku: vispirms jūs definējat parametrus un aprēķinu, un pēc tam jūs izsaucat šo funkciju, nododot atbilstošo argumentu.

Lai izvairītos no kļūdas #CALC!, galvenais ieteikums ir nodrošināt, lai jūsu LAMBDA funkcija vienmēr atgriež rezultātu . To panāk, beigās skaidri iekļaujot izteiksmi, kas atkarībā no jūsu vajadzībām ģenerē vienu vērtību vai masīvu. Ja testēšanas laikā redzat kļūdu #CALC!, pārbaudiet, vai formula patiešām ģenerē kaut ko tādu, ko Excel var parādīt kā rezultātu.

Kad esat pārbaudījis LAMBDA funkciju šūnā un redzējis, ka tā darbojas pareizi, ir īstais laiks pārvietot šo loģiku uz nosaukumu pārvaldnieku un pārvērst to par atkārtoti izmantojamu pielāgotu funkciju visā lapā vai darbgrāmatā.

LAMBDA saistība ar jaunajām matricas funkcijām

Ap LAMBDA ir parādījusies virkne uzlabotu funkciju, piemēram , BYROW, BYCOL, MAP, SCAN, REDUCE un MAKEARRAY (pēdējā dažās versijās tulkota kā ARCHIVOMAKEARRAY), kas paļaujas uz LAMBDA, lai piemērotu transformācijas diapazoniem un pilnām matricām.

Vispārējā ideja ir tāda, ka šīs funkcijas iterējas pa datu diapazoniem (pa rindām, pa kolonnām vai pa elementiem) un katram elementam vai elementu grupai izpilda jūsu definētu Lambda funkciju. Citiem vārdiem sakot, tās darbojas kā cikli, bet ir integrētas Excel formulu valodā.

Tas ļauj veikt darbības, kurām iepriekš bija nepieciešamas palīgkolonnas, starptabulas vai pat makro, tieši ar vienu masīva formulu, kas paplašina un atgriež rezultātus visam diapazonam vienlaikus.

Starp funkcijām, kas saistītas ar LAMBDA, izceļas REDUCE, MAP, SCAN, BYCOL, BYROW un MAKEARRAY , katrai no tām ir konkrēts mērķis: pārvietoties pa rindām, piemērot transformācijas pa kolonnām, uzkrāt rezultātus, izveidot no nulles aprēķinātas matricas utt. Tām visām ir kopīga iezīme, ka tās izmanto LAMBDA kā iekšēju "dzinēju", kuram tās nodod vērtības un akumulatorus, pārvietojoties pa matricu.

BYROW funkcija: atkārtoti apstrādā rindas un atgriež rezultātus pa rindām

Funkcija BYROW tiek izmantota, lai katrai diapazona rindai lietotu funkciju LAMBDA un atgrieztu masīvu ar vienu vērtību katrai apstrādātajai rindai. Tas ir ļoti efektīvs veids, kā aprēķināt starpsummas vai statistiku pa rindām, nekopējot formulas vertikāli.

Tās vispārīgā sintakse ir šāda:

=BYROW(matrica; LAMBDA(rinda; izteiksme))

Pirmais arguments ir masīvs vai diapazons, kuru vēlaties atkārtot (piemēram, B2:D7), bet otrais ir LAMBDA funkcija, kas katru rindu šajā diapazonā ņem kā parametru, pa vienai. LAMBDA funkcija atgriež vērtību, kuru vēlaties saistīt ar šo rindu (iespējams, summu, vidējo vērtību, loģisko pārbaudi utt.).

Iedomājieties, ka jums ir datu tabula diapazonā B2:D7 , un jūs vēlaties iegūt starpsummu katrai rindai. Šūnā E2 varat ierakstīt kaut ko līdzīgu šim:

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

Rezultāts būtu izvades vektors ar vienu vērtību katrai matricas B2:D7 rindai, kur katra vērtība apzīmē šīs rindas elementu summu . Tādā veidā jums nav jāraksta SUM rinda pa rindai: BYROW to izdara jūsu vietā un parāda rezultātu.

BYCOL funkcija: pielieto LAMBDA pa kolonnām

Ļoti līdzīgi funkcijai BYROW, BYCOL funkcija ir paredzēta, lai atkārtoti apstrādātu masīvu pa kolonnām, nevis rindām. Tā piemēro LAMBDA funkciju katrai diapazona kolonnai un atgriež rezultātu masīvu, kur katrs elements atbilst kolonnai.

Tās tipiskā sintakse ir šāda:

=BYCOL(masīvs; LAMBDA(kolonna; izteiksme))

Šajā gadījumā LAMBDA saņemtais parametrs ir visa matricas kolonna , kas tiek apstrādāta katrā solī. Līdzīgi kā BYROW, funkcija atgriež vektoru, taču šī ir paredzēta darbam ar summām vai indikatoriem pa kolonnām.

Turpinot iepriekšējo piemēru, ja vēlaties aprēķināt katras kolonnas vidējo vērtību diapazonā B2:D7 , šūnā B8 varat ievietot šādu formulu:

=BYCOL(B2:D7; LAMBDA(kolonna; VIDĒJAIS(kolonna)))

Rezultāts būs matrica ar tādu pašu kolonnu skaitu kā B2:D7 šūnās, kur katrā pozīcijā ir šīs kolonnas vidējais rādītājs . Tādā veidā jūs iegūsiet visus vidējos rādītājus vienā piegājienā, nevelkot un nometot formulas vai neuztraucoties par relatīvajām atsaucēm.

MAKEARRAY funkcija (MAKEARRAYFILE): izveido aprēķinātus masīvus

Funkcija MAKEARRAY (dažreiz parādīta kā ARCHIVOMAKEARRAY) ļauj ģenerēt pilnīgi jaunu masīvu, norādot rindu un kolonnu skaitu un aprēķinot katru elementu, izmantojot LAMBDA funkciju. Tā nesāk no esoša diapazona, bet gan veido masīvu no nulles.

Tās vispārīgā sintakse ir šāda:

=MAKEARRAY(rindas; kolonnas; LAMBDA(rinda; kolonna; izteiksme))

Arguments `rows` norāda, cik rindu būs izvades matricā, `column` definē kolonnu skaitu, un LAMBDA funkcija kā parametrus saņem rindu un kolonnu indeksus, kas tiek aprēķināti katrā iterācijā. Ar šo informāciju var konstruēt praktiski jebkuru skaitlisku vai teksta modeli.

Ļoti ilustratīvs piemērs ir matricas izveide, kur katrs elements norāda savu pozīciju. Jebkurā šūnā varētu rakstīt kaut ko līdzīgu:

=MAKEARRAYFILE(3; 2; LAMBDA(rinda; kolonna; -(rinda un kolonna)))

Rezultāts būtu 3 rindu un 2 kolonnu matrica , kur katra vērtība apzīmē rindas un kolonnas kombināciju (piemēram, 11, 12, 21, 22, 31, 32), kas tiek pārveidota atbilstoši ievadītajam aprēķinam (šajā gadījumā negatīvā zīme, kas tiek piemērota rindu un kolonnu konkatenācijai).

Vēl viens interesants MAKEARRAY pielietojums ir vektora pārveidošana masīvā, vienlaikus kontrolējot, cik elementu jūs izmantojat. Pieņemsim, ka vēlaties izveidot masīvu ar vertikālā diapazona pirmajām 6 vērtībām. Vispirms varat izveidot pozīciju masīvu, izmantojot MAKEARRAYFILE, iegūt k mazākās pozīciju vērtības un visbeidzot izmantot INDEX, lai izgūtu faktiskos elementus no sākotnējā diapazona.

Formulas piemērs, kas apvieno vairākas funkcijas, varētu būt ar šādu struktūru:

=LET(arrPos; MAKEARRAYFILE(3; 2; LAMBDA(rinda; kolonna; -(rinda un kolonna))); arrPosF; MATCH(arrPos; LeastK(arrPos; SEQUENCE(6))); INDEX(G8:G13; arrPosF))

Šeit LET tiek izmantots, lai definētu starpposma nosaukumus (arrPos, arrPosF), pozīciju masīvs tiek konstruēts ar ARCHIVOMAKEARRAY (3×2), 6 mazākās pozīcijas tiek atlasītas ar SMALLEST un SEQUENCE, un visbeidzot INDEX tiek izmantots, lai atgrieztu atbilstošās vērtības no diapazona G8:G13. Tas ir spēcīgs piemērs tam, kā apvienot LAMBDA un dinamiskā masīva funkcijas, lai veiktu sarežģītas transformācijas bez makro.

MAP funkcija: elementu pa elementiem transformācija

Funkcija MAP tiek izmantota, lai vienlaikus iterētu cauri vienam vai vairākiem masīviem un atgrieztu jaunu masīvu, kur katrs izejas elements tiek aprēķināts, piemērojot LAMBDA funkciju atbilstošajam(-ajiem) ievades elementam(-iem). Tā ir līdzvērtīga klasiskajai "map" funkcijai funkcionālajā programmēšanā.

Pamata sintakse ir:

=MAP(matrica1; LAMBDA_vai_vairāk_matricu)

Vienkāršākajā formā tā ņem vienu masīvu un LAMBDA funkciju, kas saņem katru vērtību no šī masīva. Šī LAMBDA funkcija pārveido vērtību un atgriež jauno versiju, kas veidos daļu no izvades masīva, saglabājot tādus pašus izmērus kā sākotnējam masīvam.

Piemēram, ja vēlaties atkārtot vertikālu diapazonu A21:A26 un atstāt sākotnējo skaitli, ja tas ir pāra skaitlis, vai defisi, ja tas ir nepāra skaitlis, varat izmantot kaut ko līdzīgu:

=MAP($A$21:$A$26; LAMBDA(parametrs1; IF(ES.PAR(parametrs1); param1; «-«)))

Šajā gadījumā MAP analizē katru A21:A26 elementu. LAMBDA pārbauda ar IS.EVEN, vai skaitlis ir pāra skaitlis. Ja tā ir, tā atgriež pašu skaitli; pretējā gadījumā tā atgriež domuzīmi. Rezultāts ir masīvs ar tādu pašu izmēru kā sākotnējais diapazons, bet ar transformāciju, kas piemērota katram elementam.

Šī pieeja ir ļoti noderīga, ja vēlaties lietot nosacījuma loģiku, teksta konvertēšanu, vērtību normalizēšanu vai jebkuru citu vienkāršu darbību, izvairoties no palīgkolonnām un atkārtotām formulām.

SCAN funkcija: kumulatīvie un starprezultāti

Funkcija SCAN tiek izmantota, lai pārbaudītu masīvu, katrai vērtībai piemērojot funkciju LAMBDA un ģenerējot izejas masīvu, kurā redzamas visas uzkrāšanas procesa starpvērtības . Tā ir ļoti līdzīga funkcijai REDUCE, bet tā vietā, lai atgrieztu tikai gala rezultātu, tā saglabā katru soli.

Tās vispārīgā sintakse ir šāda:

=SCAN(; masīvs; LAMBDA(akumulators; vērtība))

Pirmais arguments, kas ir neobligāts, ir akumulatora sākotnējā vērtība (piemēram, 0, ja veicat saskaitīšanu). Otrais arguments ir masīvs vai diapazons, kuru vēlaties atkārtot. Visbeidzot, LAMBDA funkcija saņem divus parametrus: akumulatoru (daļējais rezultāts līdz šim punktam) un apstrādājamā masīva pašreizējo vērtību.

Katrā solī SCAN novērtē LAMBDA vērtību, atjaunina akumulatoru un ģenerē jaunu elementu izejas matricā ar iegūto vērtību. Tādā veidā jūs iegūstat uzkrāto vērtību vai progresīvo transformāciju secību.

Tipisks piemērs ir kumulatīvās kopsummas (sākotnējās summas) aprēķināšana vērtību kopā un no turienes arī relatīvās kumulatīvās frekvences iegūšana. Iedomājieties, ka jums ir dati šūnās A31:A36 un jūs vēlaties absolūto kumulatīvo kopsummu:

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

Šī formula atkārtojas līdz A31:A36, katru vērtību pievienojot iepriekšējai kopsummai. Rezultāts ir masīvs ar tādu pašu elementu skaitu kā sākotnējā diapazonā, bet katrā pozīcijā tiek parādīta kumulatīvā kopsumma līdz šim punktam.

No šīs kumulatīvās summas ir viegli aprēķināt kumulatīvo procentuālo biežumu, katru kumulatīvo summu dalot ar kopējo summu. Piemēram, vispirms varat definēt kopsummu, izmantojot SUM, un pēc tam vēlreiz lietot SCAN:

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

Šajā gadījumā LET piešķir kopsummai visa diapazona A31:A36 summu. Pēc tam SCAN ģenerē kumulatīvo vērtību secību un, dalot to ar kopsummu, iegūst katra soļa relatīvo kumulatīvo frekvenci , visu vienā matricas formulā.

REDUCE funkcija: samazināšana līdz vienai uzkrātajai vērtībai

Arī funkcija REDUCE iterē masīvu, katram elementam piemērojot funkciju LAMBDA, taču atšķirībā no funkcijas SCAN šeit jūs interesē tikai uzkrāšanas procesa galarezultāta iegūšana . Tas ir, tā veic tāda paša veida iterāciju kā funkcija SCAN, bet atgriež tikai pēdējo vērtību akumulatorā.

Tās sintakse ir:

=REDUCE(; masīvs; LAMBDA(akumulators; vērtība))

Tāpat kā SCAN, sākotnējā_vērtība nosaka akumulatora sākuma punktu, matrica ir apstrādājamais diapazons, un LAMBDA kā parametri ir pašreizējais akumulators un vērtība, kas tiek nolasīta šajā brīdī.

Ļoti tipisks lietojums ir tekošās summas vai kumulatīvās darbības aprēķināšana, kur jūs interesē tikai pēdējais rezultāts . Piemēram, lai summētu A1:A6, izmantojot REDUCE, jūs varētu rakstīt:

=SAMAZINĀT(0; A1:A6; LAMBDA(accum; param1; accum + param1))

Šeit REDUCE atkārtojas šūnās A1:A6 un katrā solī atjaunina kumulatīvo vērtību , pievienojot pašreizējās šūnas vērtību (param1). Beigās tā atgriež vienu vērtību: kopsummu. Tas konceptuāli ir līdzīgi SUM izmantošanai, bet ar REDUCE var definēt jebkuru sarežģītāku uzkrāšanas loģiku, ne tikai vienkāršas summas.

REDUCE jauda slēpjas tās spējā strādāt ar iepriekšējo rezultātu katrā solī un turpināt lietot darbības, līdz process ir pabeigts. Tas ļauj ieviest sarežģītus pielāgotus aprēķinus, kas tradicionāli tika apstrādāti ar cikliem makro.

Pielāgotu funkciju izveide, izmantojot LAMBDA un nosaukumu pārvaldnieku

Viena no LAMBDA visspēcīgākajām funkcijām ir spēja pārveidot jebkuru formulu lietotāja funkcijā, izmantojot Excel nosaukumu pārvaldnieku. Tas ļauj jūsu funkcijai piešķirt savu nosaukumu un to izmantot tāpat kā jebkuru citu programmas iebūvēto funkciju.

Tipiska darbplūsma ir šāda: vispirms pārbaudiet LAMBDA funkciju šūnā , iekļaujot gan definīciju, gan izsaukumu ar argumentu piemēriem. Kad esat pārliecinājies, ka tā darbojas pareizi un atgriež paredzēto rezultātu, nokopējiet daļu, kas atbilst LAMBDA definīcijai (bez pēdējā izsaukuma), un ielīmējiet to nosaukumu pārvaldniekā.

Nosaukumu pārvaldniekā izveidojiet jaunu nosaukumu (piemēram, MyVATFunction, MyDiscount, MyWeightedAverage utt.) un laukā “Refers to” ievadiet:

=LAMBDA(var1; var2; …; varN; aprēķins)

Kopš tā brīža jebkurā darbgrāmatas šūnā varat izsaukt funkciju, ierakstot tās nosaukumu tā, it kā tā būtu iebūvēta funkcija , nododot parametru vērtības tādā pašā secībā, kādā tās definējāt.

Tam ir divas nepārprotamas priekšrocības: pirmkārt, tas padara jūsu izklājlapas lasāmākas (garu formulu vietā jūs redzat funkciju ar aprakstošu nosaukumu); otrkārt, tas centralizē loģiku vienuviet. Ja vēlāk vēlaties mainīt aprēķinu, vienkārši modificējiet definīciju nosaukumu pārvaldniekā, un visas formulas, kas to izmanto, tiks automātiski atjauninātas.

Praktiski aspekti un papildu apsvērumi

Lai patiesi izmantotu funkciju LAMBDA un ar to saistītās funkcijas, ir lietderīgi izprast dažus praktiskus to darbības un prasību aspektus . Pirmkārt, šīs funkcijas ir daļa no mūsdienu Excel funkcijām, tāpēc jums ir nepieciešama versija, kurā jau ir iekļauti dinamiskie masīvi un jaunākās funkcijas LAMBDA, BYROW, BYCOL un citas. Tās parasti ir pieejamas jaunākajos Microsoft 365 izdevumos.

Vēl viens svarīgs jautājums ir veiktspēja : lai gan LAMBDA un šķērsošanas funkcijas ir ļoti jaudīgas, ja tās tiek lietotas milzīgiem diapazoniem ar ļoti sarežģītu loģiku, grāmatas atkārtota aprēķināšana var aizņemt ilgāku laiku. Ieteicams LAMBDA funkcijas izstrādāt, ņemot vērā efektivitāti, izvairoties no liekiem aprēķiniem un izmantojot tādas struktūras kā LET, lai definētu atkārtoti izmantojamas starpvērtības.

Ir arī svarīgi saglabāt konsekventu parametru un funkciju nosaukumu piešķiršanas konvenciju . Aprakstošu nosaukumu izmantošana palīdz izprast loģiku, kad pēc mēnešiem atkārtoti apmeklējat failu vai kad kādam citam ir jāstrādā ar jūsu darbgrāmatām. Parametrs ar nosaukumu "aute", "rate", "dataRow" vai "valuesCol" ir daudz skaidrāks nekā vienkārši "x" vai "yoa".

Kas attiecas uz kļūdu #CALC!, tā parasti parādās, ja Excel nevar aprēķināt masīva izteiksmi vai ja funkcija LAMBDA neatgriež derīgu rezultātu. Vienmēr pārbaudiet, vai jūsu formulai ir precīzi definēta izvade un, ja strādājat ar masīva funkcijām, vai izmēri ir konsekventi (piemēram, vai netiek apvienoti nesaderīgi izmēru diapazoni bez pareizas transformācijas).

Visbeidzot, lai gan LAMBDA daudzos gadījumos novērš nepieciešamību pēc VBA, tā to pilnībā neaizstāj. Ir situācijas, kad makro automatizācija joprojām ir labākā izvēle, taču lielam skaitam pielāgotu aprēķinu un datu transformāciju LAMBDA un ar to saistītās funkcijas ļauj visu darbu veikt Excel tradicionālajā formulu vidē.

Pateicoties šīm iespējām, tiem, kas ikdienā strādā ar izklājlapām, tagad ir daudz elastīgāki rīki , lai izstrādātu savus aprēķinus, apkopotu informāciju pa rindām vai kolonnām, pilnībā vai daļēji šķērsotu matricas, ģenerētu detalizētas kopsummas un izveidotu jaunas matricas, kas aprēķinātas acumirklī, un tas viss, neizejot no jau zināmās formulu valodas.