Funksioni Lambda në Excel: udhëzues i plotë dhe shembuj praktikë

Informatec Digital » Burime » Funksioni Lambda në Excel: udhëzues i plotë dhe shembuj praktikë

Funksioni Lambda në Excel ka revolucionarizuar mënyrën se si punojmë me formulat në spreadsheet-et e Microsoft. Ai ju lejon të krijoni funksionet tuaja të personalizuara duke përdorur vetëm gjuhën e formulave të Excel-it, pa prekur asnjë rresht të VBA-së ose programimit tradicional . Është sikur të keni mundësinë të shtoni funksione të reja, të dizajnuara sipas porosisë, në program.

Për më tepër, rreth LAMBDA-s kanë dalë një numër funksionesh të lidhura, të tilla si BYROW, BYCOL, MAP, SCAN, REDUCE dhe MAKEARRAY , të dizajnuara për të punuar me diapazone dhe matrica në një mënyrë shumë më fleksibile dhe të fuqishme. Këto funksione sillen, në përgjithësi, si sythe të vegjël që përsërisin të dhënat dhe aplikojnë një transformim të përcaktuar nga LAMBDA, duke hapur një gamë të gjerë mundësish për analiza të avancuara direkt në spreadsheet.

Çfarë është funksioni LAMBDA në Excel dhe për çfarë përdoret?

Funksioni Lambda është një mjet që ju lejon të përcaktoni funksione të personalizuara duke përdorur vetëm formula Excel. Në vend që të programoni në VBA ose të mbështeteni në makro, mund të përmblidhni çdo llogaritje komplekse në një funksion të vetëm të ripërdorshëm, me parametrat e vet dhe një rezultat përfundimtar të qartë dhe të pastër.

Në praktikë, LAMBDA shndërron çdo formulë në një funksion që mund ta ripërdorni sa herë të dëshironi. Avantazhi i saj kryesor është se integrohet pa probleme me pjesën tjetër të motorit të llogaritjes së Excel-it dhe mund të kombinohet me funksione standarde, referenca, emra të përcaktuar dhe vargje dinamike.

Sintaksa bazë kur përdoret direkt në një qelizë është:

=LAMBDA(parametri1; parametri2; …; parametriN; llogaritje)(vlera1; vlera2; …; vleraN)

Në këtë strukturë, parametri1, parametri2, …, parametriN janë emrat që u jepni variablave brenda funksionit, ndërsa llogaritja është formula që përdor këto parametra për të gjeneruar një rezultat. Së fundmi, brenda grupit të dytë të kllapave, ju kaloni vlerat aktuale që do të marrin këto parametra kur funksioni të ekzekutohet.

Nëse përdorni Menaxherin e Emrave në Excel për të krijuar një funksion të përhershëm LAMBDA, sintaksa ndryshon pak, sepse ju e përcaktoni funksionin atje, por nuk e thirrni ende. Në atë rast, formati do të ishte:

=LAMBDA(var1; var2; …; varN; llogaritje)

Më vonë do ta thërrisnit atë funksion duke përdorur emrin që i dhatë në Menaxherin e Emrave, thjesht duke shtypur emrin dhe argumentet njësoj siç do të bënit me SUM, AVERAGE ose çdo funksion tjetër standard.

Praktikat më të mira gjatë krijimit dhe testimit të funksioneve Lambda

Kur filloni të punoni me funksionet Lambda, është e rëndësishme të ndiqni disa udhëzime për t'u siguruar që ato të sillen siç pritet dhe të mos humbni kohë duke debuguar gabime të ndërlikuara. Një nga mënyrat më praktike për të filluar është të krijoni dhe testoni funksionin Lambda direkt në një qelizë.

Procedura e zakonshme është që së pari të shkruani formulën e plotë me përkufizimin e LAMBDA-s dhe thirrjen në të njëjtën shprehje, në mënyrë që të shihni menjëherë nëse rezultati është siç pritet. Në këtë mënyrë, mund të zbuloni gabime sintaksore ose logjike përpara se ta ruani atë si një funksion të emëruar.

Për shembull, një strukturë shumë tipike e testimit do të ishte:

=LAMBDA(); llogaritje(vlerat_e_testit)

Për të kontrolluar diçka shumë të thjeshtë si mbledhja e 1-shit në një numër, mund të përdorni:

=LAMBDA(numër; numër + 1)(1)

Në këtë rast, funksioni do të kthente vlerën 2. Është një shembull shumë i thjeshtë, por shërben për të ilustruar mekanikën: së pari përcaktoni parametrat dhe llogaritjen, dhe pastaj e thërrisni atë funksion duke kaluar argumentin përkatës.

Një rekomandim kyç për të shmangur gabimin #CALC! është të siguroheni që funksioni juaj LAMBDA të kthejë gjithmonë një rezultat . Kjo arrihet duke përfshirë qartë një shprehje në fund që prodhon një vlerë të vetme ose një varg, varësisht nga nevojat tuaja. Nëse shihni gabimin #CALC! gjatë testimit, kontrolloni që formula po gjeneron në të vërtetë diçka që Excel mund ta shfaqë si rezultat.

Pasi ta keni testuar funksionin LAMBDA në një qelizë dhe të keni parë se funksionon siç duhet, është koha e duhur për ta zhvendosur atë logjikë te Menaxheri i Emrave dhe për ta shndërruar atë në një funksion të ripërdorshëm të personalizuar në të gjithë fletën ose librin e punës.

Marrëdhënia e LAMBDA-s me funksionet e reja të matricës

Rreth LAMBDA-s, janë shfaqur një sërë funksionesh të avancuara, të tilla si BYROW, BYCOL, MAP, SCAN, REDUCE dhe MAKEARRAY (kjo e fundit përkthehet në disa versione si ARCHIVOMAKEARRAY), të cilat mbështeten në LAMBDA për të aplikuar transformime në diapazone dhe matrica të plota.

Ideja e përgjithshme është që këto funksione iterojnë përmes diapazoneve të të dhënave (sipas rreshtave, sipas kolonave ose element pas elementi) dhe, për secilin element ose grup elementesh, ekzekutojnë një funksion Lambda që ju përcaktoni. Me fjalë të tjera, ato funksionojnë si lakime, por të integruara në gjuhën e formulave të Excel-it.

Kjo ju lejon të kryeni operacione që më parë kërkonin kolona ndihmëse, tabela të ndërmjetme ose edhe makro, direkt me një formulë të vetme vargu që zgjeron dhe kthen rezultate për të gjithë diapazonin menjëherë.

Ndër funksionet që lidhen me LAMBDA-n, dallohen REDUCE, MAP, SCAN, BYCOL, BYROW dhe MAKEARRAY , secila me një objektiv specifik: kalimi nëpër rreshta, aplikimi i transformimeve sipas kolonave, grumbullimi i rezultateve, krijimi i matricave të llogaritura nga e para, etj. Të gjitha kanë të përbashkët që përdorin LAMBDA-n si një "motor" të brendshëm, të cilit i kalojnë vlerat dhe akumulatorët ndërsa lëvizin nëpër matricë.

Funksioni BYROW: iteron nëpër rreshta dhe kthen rezultate rresht pas rreshti

Funksioni BYROW përdoret për të aplikuar një funksion LAMBDA në çdo rresht në një diapazon dhe për të kthyer një varg me një vlerë për çdo rresht të përpunuar. Është një mënyrë shumë efikase për të llogaritur nëntotalet ose statistikat rresht pas rreshti pa pasur nevojë të kopjoni formulat vertikalisht.

Sintaksa e saj e përgjithshme është:

=BYROW(matricë; LAMBDA(rresht; shprehje))

Argumenti i parë është vargu ose diapazoni nëpër të cilin dëshironi të iteroni (për shembull, B2:D7), dhe i dyti është një funksion LAMBDA që merr çdo rresht në atë diapazon si parametër, një nga një. Funksioni LAMBDA kthen vlerën që dëshironi të shoqëroni me atë rresht (ndoshta një shumë, një mesatare, një kontroll logjik, etj.).

Imagjinoni që keni një tabelë të dhënash në diapazonin B2:D7 dhe doni të merrni një nëntotal për secilin rresht. Mund të shkruani diçka të tillë në qelizën E2:

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

Rezultati do të ishte një vektor dalës me një vlerë për secilin rresht të matricës B2:D7, ku secila vlerë përfaqëson shumën e elementeve në atë rresht. Në këtë mënyrë, nuk keni nevojë të shkruani SUM rresht pas rreshti: BYROW e bën këtë për ju dhe e shpërndan rezultatin poshtë.

Funksioni BYCOL: zbato LAMBDA sipas kolonave

Shumë i ngjashëm me BYROW, funksioni BYCOL është projektuar për të iteruar një varg sipas kolonave në vend të rreshtave. Ai zbaton një funksion LAMBDA në secilën kolonë në diapazon dhe kthen një varg rezultatesh ku çdo element korrespondon me një kolonë.

Sintaksa e saj tipike është:

=BYCOL(matricë; LAMBDA(kolonë; shprehje))

Në këtë rast, parametri i marrë nga LAMBDA është e gjithë kolona e matricës që përpunohet në secilin hap. Ngjashëm me BYROW, funksioni kthen një vektor, por ky është projektuar për të punuar me totale ose tregues sipas kolonës.

Duke vazhduar me shembullin e mëparshëm, nëse doni të llogaritni mesataren e secilës kolonë në diapazonin B2:D7 , mund të vendosni një formulë si kjo në qelizën B8:

=BYCOL(B2:D7; LAMBDA(kolonë; MESATARE(kolonë)))

Rezultati do të jetë një matricë me të njëjtin numër kolonash si B2:D7, ku çdo pozicion përmban mesataren e asaj kolone . Në këtë mënyrë, ju i merrni të gjitha mesataret menjëherë pa pasur nevojë të tërhiqni dhe lëshoni formula ose të shqetësoheni për referencat relative.

Funksioni MAKEARRAY (MAKEARRAYFILE): krijon vargje të llogaritura

Funksioni MAKEARRAY (ndonjëherë shfaqet si ARCHIVOMAKEARRAY) ju lejon të gjeneroni një varg krejtësisht të ri duke specifikuar numrin e rreshtave dhe kolonave, dhe duke llogaritur çdo element duke përdorur një funksion LAMBDA. Ai nuk fillon nga një diapazon ekzistues, por e ndërton vargun nga e para.

Sintaksa e saj e përgjithshme është:

=MAKEARRAY(rreshta; kolona; LAMBDA(rresht; kolonë; shprehje))

Argumenti `rreshta` tregon se sa rreshta do të ketë matrica e daljes, `kolonat` përcakton numrin e kolonave dhe funksioni LAMBDA merr indekset e rreshtave dhe kolonave që llogariten në çdo përsëritje si parametra. Me këtë informacion, ju mund të ndërtoni praktikisht çdo model numerik ose tekstual.

Një shembull shumë ilustrues është krijimi i një matrice ku secili element tregon pozicionin e vet. Në çdo qelizë, mund të shkruani diçka si:

=MAKEARRAYFILE(3; 2; LAMBDA(rresht; kolonë; -(rresht & kolonë)))

Rezultati do të ishte një matricë 3 rreshta me 2 kolona , ​​ku secila vlerë përfaqëson një kombinim rreshti dhe kolone (për shembull, 11, 12, 21, 22, 31, 32), të transformuara sipas llogaritjes që futni (në këtë rast, shenja negative zbatohet në bashkimin e rreshtit dhe kolonës).

Një tjetër përdorim interesant i MAKEARRAY është konvertimi i një vektori në një varg, duke kontrolluar njëkohësisht numrin e elementëve që merrni. Supozoni se doni të krijoni një varg me 6 vlerat e para të një diapazoni vertikal. Së pari mund të krijoni një varg pozicionesh me MAKEARRAYFILE, të merrni k vlerat më të vogla të pozicionit dhe së fundmi të përdorni INDEX për të nxjerrë elementët aktualë nga diapazoni origjinal.

Një shembull i një formule, që kombinon disa funksione, mund të ketë këtë strukturë:

=LET(arrPos; MAKEARRAYFILE(3; 2; LAMBDA(rresht; kolonë; -(rresht & kolonë))); arrPosF; MATCH(arrPos; LeastK(arrPos; SEQUENCE(6))); INDEX(G8:G13; arrPosF))

Këtu, LET përdoret për të përcaktuar emrat e ndërmjetëm (arrPos, arrPosF), një varg pozicionesh ndërtohet me ARCHIVOMAKEARRAY (3×2), 6 pozicionet më të vogla zgjidhen me SMALLEST dhe SEQUENCE, dhe së fundmi INDEX përdoret për të kthyer vlerat përkatëse nga diapazoni G8:G13. Është një shembull i fuqishëm se si të kombinohen funksionet LAMBDA dhe ato dinamike të vargjeve për të kryer transformime komplekse pa makro.

Funksioni MAP: transformimi element-për-element

Funksioni MAP përdoret për të iteruar përmes një ose më shumë vargjeve njëkohësisht dhe për të kthyer një varg të ri ku çdo element dalës llogaritet duke aplikuar një funksion LAMBDA në elementin/et hyrës përkatës. Është ekuivalent me një funksion klasik "hartë" në programimin funksional.

Sintaksa themelore është:

=MAP(matrica1; LAMBDA_ose_më_shumë_matrica)

Në formën e tij më të thjeshtë, ai merr një varg të vetëm dhe një funksion LAMBDA që merr çdo vlerë nga ai varg. Ky funksion LAMBDA transformon vlerën dhe kthen versionin e ri që do të formojë pjesë të vargut të daljes, duke ruajtur të njëjtat dimensione si vargu origjinal.

Për shembull, nëse doni të iteroni nëpër një diapazon vertikal A21:A26 dhe të lini numrin origjinal nëse është çift ose një vizë nëse është tek, mund të përdorni diçka si:

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

Në këtë rast, MAP analizon çdo element të A21:A26. LAMBDA kontrollon me IS.EVEN nëse numri është çift. Nëse është, kthen vetë numrin; përndryshe, kthen një vizë. Rezultati është një varg me të njëjtën madhësi si diapazoni origjinal, por me transformimin e aplikuar në secilin element.

Kjo qasje është shumë e dobishme kur doni të aplikoni logjikën e kushtëzuar, konvertimin e tekstit, normalizimin e vlerave ose ndonjë operacion tjetër të thjeshtë, duke shmangur kolonat ndihmëse dhe formulat përsëritëse.

Funksioni SCAN: rezultate kumulative dhe të ndërmjetme

Funksioni SCAN përdoret për të shqyrtuar një varg duke aplikuar një funksion LAMBDA në secilën vlerë dhe duke gjeneruar një varg dalës që tregon të gjitha vlerat e ndërmjetme të procesit të akumulimit. Është shumë i ngjashëm me REDUCE, por në vend që të kthejë vetëm rezultatin përfundimtar, ruan çdo hap.

Sintaksa e saj e përgjithshme është:

=SCAN(; varg; LAMBDA(akumulator; vlerë))

Argumenti i parë, i cili është opsional, është vlera fillestare e akumulatorit (për shembull, 0 nëse po mblidhni). Argumenti i dytë është vargu ose diapazoni nëpër të cilin dëshironi të iteroni. Së fundmi, funksioni LAMBDA merr dy parametra: akumulatori (rezultati i pjesshëm deri në atë pikë) dhe vlera aktuale e vargut që po përpunoni.

Në çdo hap, SCAN vlerëson vlerën LAMBDA, përditëson akumuluesin dhe gjeneron një element të ri në matricën e daljes me vlerën që rezulton. Në këtë mënyrë, ju merrni një sekuencë vlerash të akumuluara ose transformimesh progresive.

Një shembull tipik është llogaritja e një totali kumulativ (totali rrjedhës) mbi një grup vlerash dhe, prej andej, marrja e frekuencës kumulative relative. Imagjinoni që keni të dhëna në A31:A36 dhe dëshironi totalin kumulativ absolut:

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

Kjo formulë përsëritet përmes A31:A36, duke shtuar çdo vlerë në totalin e mëparshëm. Rezultati është një varg me të njëjtin numër elementësh si diapazoni origjinal, por çdo pozicion shfaq totalin kumulativ deri në atë pikë.

Nga ai total kumulativ, është e lehtë të llogaritet frekuenca e përqindjes kumulative duke pjesëtuar çdo total kumulativ me totalin e përgjithshëm. Për shembull, së pari mund ta përcaktoni totalin duke përdorur SUM dhe pastaj të aplikoni përsëri SCAN:

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

Në këtë rast, LET i cakton totalit shumën e të gjithë diapazonit A31:A36. Pastaj SCAN gjeneron sekuencën e vlerave kumulative dhe, duke e pjesëtuar atë me totalin, merrni frekuencën kumulative relative për secilin hap , të gjitha në një formulë të vetme matrice.

Funksioni REDUCE: reduktimi në një vlerë të vetme të akumuluar

Funksioni REDUCE gjithashtu iteron përmes një vargu duke aplikuar një funksion LAMBDA në secilin element, por ndryshe nga SCAN, këtu ju intereson vetëm të merrni rezultatin përfundimtar të procesit të akumulimit. Kjo do të thotë, ai kryen të njëjtin lloj iteracioni si SCAN, por kthen vetëm vlerën e fundit në akumulator.

Sintaksa e saj është:

=REDUCE(; varg; LAMBDA(akumulator; vlerë))

Ashtu si në SCAN, initial_value përcakton pikën fillestare të akumulatorit, matrica është diapazoni që do të përpunohet, dhe LAMBDA ka si parametra akumulatorin aktual dhe vlerën që lexohet në atë moment.

Një përdorim shumë tipik është llogaritja e një shume rrjedhëse ose një operacioni kumulativ ku jeni të interesuar vetëm për rezultatin e fundit . Për shembull, për të mbledhur A1:A6 duke përdorur REDUCE, mund të shkruani:

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

Këtu, REDUCE përsëritet përmes A1:A6 dhe në çdo hap përditëson vlerën kumulative duke shtuar vlerën e qelizës aktuale (param1). Në fund, kthen një vlerë të vetme: shumën totale. Është konceptualisht e ngjashme me përdorimin e SUM, por me REDUCE mund të përcaktoni çdo logjikë më komplekse akumulimi, jo vetëm shuma të thjeshta.

Fuqia e REDUCE qëndron në aftësinë e tij për të punuar me rezultatin e mëparshëm në çdo hap dhe për të vazhduar zbatimin e operacioneve derisa procesi të përfundojë. Kjo ju lejon të zbatoni llogaritje të sofistikuara të personalizuara që tradicionalisht trajtoheshin me sythe në makro.

Krijimi i funksioneve të personalizuara me LAMBDA dhe Menaxherin e Emrave

Një nga veçoritë më të fuqishme të LAMBDA-s është aftësia e saj për të konvertuar çdo formulë në një funksion përdoruesi duke përdorur Menaxherin e Emrave të Excel-it. Kjo i lejon funksionit tuaj të ketë emrin e vet dhe të përdoret si çdo funksion tjetër vendas në program.

Fluksi tipik i punës është ky: së pari, testoni funksionin LAMBDA në një qelizë , duke përfshirë si përkufizimin ashtu edhe thirrjen me argumente shembull. Pasi të verifikoni se funksionon siç duhet dhe kthen rezultatin e pritur, kopjoni pjesën që korrespondon me përkufizimin LAMBDA (pa thirrjen përfundimtare) dhe ngjiteni atë në Menaxherin e Emrave.

Në Menaxherin e Emrave, ju krijoni një emër të ri (për shembull, MyVATFunction, MyDiscount, MyWeightedAverage, etj.) dhe, në fushën "Referon te", ju futni:

=LAMBDA(var1; var2; …; varN; llogaritje)

Që nga ai moment, në çdo qelizë të librit tuaj të punës mund ta thirrni funksionin duke shkruar emrin e tij sikur të ishte një funksion i integruar , duke kaluar vlerat e parametrave në të njëjtën renditje në të cilën i keni përcaktuar ato.

Kjo ka dy përparësi të qarta: së pari, i bën fletëllogaritjet tuaja më të lexueshme (në vend që të shihni formula të gjata, shihni një funksion me një emër përshkrues); së dyti, e përqendron logjikën në një vend. Nëse më vonë dëshironi të ndryshoni llogaritjen, thjesht modifikoni përkufizimin në Menaxherin e Emrave dhe të gjitha formulat që e përdorin atë do të përditësohen automatikisht.

Aspektet praktike dhe konsideratat shtesë

Për të përdorur vërtet LAMBDA-n dhe funksionet e saj të lidhura, është e dobishme të kuptoni disa aspekte praktike të sjelljes dhe kërkesave të tyre . Së pari, këto funksione janë pjesë e veçorive moderne të Excel-it, kështu që ju nevojitet një version që përfshin tashmë vargje dinamike dhe funksionet më të reja LAMBDA, BYROW, BYCOL dhe të tjera. Këto zakonisht janë të disponueshme në botimet më të fundit të Microsoft 365.

Një çështje tjetër e rëndësishme është performanca : megjithëse funksionet LAMBDA dhe funksionet e përshkimit janë shumë të fuqishme, nëse i aplikoni ato në diapazone të mëdha me logjikë shumë komplekse, libri mund të kërkojë më shumë kohë për t'i rillogaritur. Këshillohet që të dizajnohen funksionet LAMBDA duke pasur parasysh efikasitetin, duke shmangur llogaritjet e tepërta dhe duke përdorur struktura si LET për të përcaktuar vlera të ndërmjetme të ripërdorshme.

Është gjithashtu thelbësore të ruhet një konventë e qëndrueshme emërtimi për parametrat dhe funksionet . Përdorimi i emrave përshkrues ju ndihmon të kuptoni logjikën kur e rishikoni skedarin muaj më vonë ose kur dikush tjetër duhet të punojë me librat tuaj të punës. Një parametër i quajtur amount, rate, dataRow ose valuesCol është shumë më i qartë se thjesht x ose yoa.

Lidhur me gabimin #CALC!, ai zakonisht shfaqet kur Excel nuk është në gjendje të llogarisë shprehjen e vargut ose kur funksioni LAMBDA nuk kthen një rezultat të vlefshëm. Gjithmonë verifikoni që formula juaj ka një rezultat të përcaktuar mirë dhe, nëse po punoni me funksione vargu, që dimensionet të jenë konsistente (për shembull, që nuk po kombinoni diapazone madhësish të papajtueshme pa transformimin e duhur).

Së fundmi, ndërsa LAMBDA eliminon nevojën për VBA në shumë raste, ajo nuk e zëvendëson plotësisht atë. Ka situata ku automatizimi i makrove mbetet opsioni më i mirë, por për një numër të madh llogaritjesh dhe transformimesh të të dhënave të personalizuara, LAMBDA dhe funksionet e lidhura me të ju lejojnë të mbani të gjithë punën tuaj brenda mjedisit konvencional të formulave të Excel-it.

Falë këtyre mundësive, ata që punojnë çdo ditë me spreadsheet-e tani kanë mjete shumë më fleksibile për të hartuar llogaritjet e tyre, për të përmbledhur informacionin sipas rreshtave ose kolonave, për të përshkuar matricat plotësisht ose pjesërisht, për të gjeneruar totale të detajuara dhe për të krijuar matrica të reja të llogaritura menjëherë, të gjitha pa lënë gjuhën e formulave që tashmë e njohin.