Përmbajtje
Për momentin, Microsoft Excel ka pothuajse pesëqind funksione të fletës së punës të disponueshme përmes butonit - Function Wizard fx në shiritin e formulave. Ky është një grup shumë i mirë, por, megjithatë, pothuajse çdo përdorues herët a vonë ndeshet me një situatë ku kjo listë nuk përmban funksionin që i nevojitet - thjesht sepse nuk është në Excel.
Deri më tani, mënyra e vetme për të zgjidhur këtë problem ishin macros, dmth. të shkruani funksionin tuaj të përcaktuar nga përdoruesi (UDF = Funksioni i Përcaktuar nga Përdoruesi) në Visual Basic, i cili kërkon aftësi të përshtatshme programuese dhe ndonjëherë nuk është aspak i lehtë. Sidoqoftë, me përditësimet më të fundit të Office 365, situata ka ndryshuar për mirë - një funksion i veçantë "mbështjellës" është shtuar në Excel. Lambda. Me ndihmën e tij, detyra e krijimit të funksioneve tuaja tani zgjidhet lehtësisht dhe bukur.
Le të shohim parimin e përdorimit të tij në shembullin e mëposhtëm.
Siç e dini me shumë mundësi, Excel ka disa funksione të analizimit të datave që ju lejojnë të përcaktoni numrin e ditës, muajit, javës dhe vitit për një datë të caktuar. Por për disa arsye nuk ka asnjë funksion që përcakton numrin e tremujorit, i cili gjithashtu shpesh nevojitet, apo jo? Le ta rregullojmë këtë mangësi dhe të krijojmë me Lambda funksionin e vet të ri për të zgjidhur këtë problem.
Hapi 1. Shkruani formulën
Le të fillojmë me faktin se me dorë në mënyrën e zakonshme do të shkruajmë një formulë në një qelizë fletë që llogarit atë që na nevojitet. Në rastin e numrit të tremujorit, kjo mund të bëhet, për shembull, si kjo:
Hapi 2. Përfundimi në LAMBDA dhe testimi
Tani është koha për të aplikuar funksionin e ri LAMBDA dhe për të mbështjellë formulën tonë në të. Sintaksa e funksionit është si më poshtë:
=LAMBDA(Ndryshore 1; Ndryshore 2; ... VariablaN ; shprehje)
ku emrat e një ose më shumë variablave renditen së pari, dhe argumenti i fundit është gjithmonë një formulë ose një shprehje e llogaritur që i përdor ato. Emrat e variablave nuk duhet të duken si adresat e qelizave dhe nuk duhet të përmbajnë pika.
Në rastin tonë, do të ketë vetëm një variabël - data për të cilën ne llogarisim numrin e tremujorit. Le ta quajmë variablin për të, të themi, d. Më pas e mbështjellim formulën tonë në një funksion Lambda dhe duke zëvendësuar adresën e qelizës origjinale A2 me një emër të ndryshores fiktive, marrim:
Ju lutemi vini re se pas një transformimi të tillë, formula jonë (në fakt, e saktë!) filloi të prodhojë një gabim, sepse tani data origjinale nga qeliza A2 nuk transferohet në të. Për testim dhe besim, mund t'i kaloni argumente duke i shtuar ato pas funksionit Lambda në kllapa:
Hapi 3. Krijo një emër
Tani për pjesën e lehtë dhe argëtuese. Ne hapim Emri Menaxher tab formulë (Formulat - Menaxheri i emrit) dhe krijoni një emër të ri me butonin Krijo (Krijo). Dilni dhe vendosni një emër për funksionin tonë të ardhshëm (për shembull, Nomkvartala), dhe në terren lidhje (Referenca) kopjoni me kujdes nga shiriti i formulave dhe ngjisni funksionin tonë Lambda, vetëm pa argumentin e fundit (A2):
Gjithçka. Pasi të klikoni mbi OK funksioni i krijuar mund të përdoret në çdo qelizë në çdo fletë të këtij libri pune:
Përdorni në libra të tjerë
LAMBDA dhe vargje dinamike
Funksionet e personalizuara të krijuara me një funksion Lambda mbështesin me sukses punën me vargje të reja dinamike dhe funksionet e tyre (Filtër, UNIK, KLASA) u shtua në Microsoft Excel në 2020.
Le të themi se duam të krijojmë një funksion të ri të përcaktuar nga përdoruesi që do të krahasonte dy lista dhe do të kthente ndryshimin midis tyre – ato elemente nga lista e parë që nuk janë në të dytën. Puna e jetës, apo jo? Më parë, për këtë ata përdornin ose funksionet a la VPR (VLOOKUP), ose PivotTables, ose pyetjet e Power Query. Tani mund të bëni me një formulë:
Në versionin anglisht do të jetë:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Këtu funksioni countif numëron numrin e dukurive të secilit element të listës së parë në të dytën dhe më pas funksionin Filtër përzgjedh vetëm ata prej tyre që nuk i kanë pasur këto dukuri. Duke e mbështjellë këtë strukturë në Lambda dhe duke krijuar një varg të emërtuar bazuar në të me një emër, për shembull, KËRKONI SHPËRNDARJE - do të marrim një funksion të përshtatshëm që kthen rezultatin e krahasimit të dy listave në formën e një grupi dinamik:
Nëse të dhënat e burimit nuk janë tabela të zakonshme, por "të zgjuara", funksioni ynë gjithashtu do të përballojë pa probleme:
Një shembull tjetër është ndarja dinamike e tekstit duke e kthyer atë në XML dhe më pas duke e analizuar atë qelizë për qelizë duke përdorur funksionin FILTER.XML që analizuam së fundi. Për të mos riprodhuar çdo herë manualisht këtë formulë komplekse, do të jetë më e lehtë ta mbështillni atë në LAMBDA dhe të krijoni një gamë dinamike të bazuar në të, pra një funksion të ri kompakt dhe të përshtatshëm, duke e emërtuar, për shembull, RAZDTEXT:
Argumenti i parë i këtij funksioni do të jetë qeliza me tekstin burimor, dhe e dyta - karakteri ndarës, dhe do të kthejë rezultatin në formën e një grupi dinamik horizontal. Kodi i funksionit do të jetë si më poshtë:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Lista e shembujve është e pafundme - në çdo situatë ku shpesh duhet të futni të njëjtën formulë të gjatë dhe të rëndë, funksioni LAMBDA do ta bëjë jetën dukshëm më të lehtë.
Numërimi rekurziv i karaktereve
Të gjithë shembujt e mëparshëm kanë treguar vetëm një anë, më të dukshmen, të funksionit LAMBDA - përdorimin e tij si një "mbështjellës" për mbështjelljen e formulave të gjata në të dhe për të thjeshtuar hyrjen e tyre. Në fakt, LAMBDA ka një anë tjetër, shumë më të thellë, që e kthen atë në një gjuhë programimi pothuajse të plotë.
Fakti është se një veçori thelbësore e rëndësishme e funksioneve LAMBDA është aftësia për t'i zbatuar ato rekursion – logjika e llogaritjeve, kur në procesin e llogaritjes funksioni thërret veten. Nga zakoni, mund të tingëllojë rrëqethëse, por në programim, rekursioni është një gjë e zakonshme. Edhe në makro në Visual Basic, ju mund ta zbatoni atë, dhe tani, siç mund ta shihni, ai ka ardhur në Excel. Le të përpiqemi ta kuptojmë këtë teknikë me një shembull praktik.
Supozoni se duam të krijojmë një funksion të përcaktuar nga përdoruesi që do të heqë të gjithë karakteret e dhëna nga teksti burimor. Dobia e një funksioni të tillë, mendoj se nuk keni nevojë të provoni - do të ishte shumë e përshtatshme të pastroni të dhënat e hyrjes së mbeturinave me ndihmën e tij, apo jo?
Megjithatë, krahasuar me shembujt e mëparshëm, jo-rekurzivë, na presin dy vështirësi.
- Ne do të duhet të gjejmë një emër për funksionin tonë përpara se të fillojmë të shkruajmë kodin e tij, sepse në të, ky emër do të përdoret tashmë për të thirrur vetë funksionin.
- Futja e një funksioni të tillë rekurziv në një qelizë dhe korrigjimi i tij duke specifikuar argumentet në kllapa pas LAMBDA (siç bëmë më parë) nuk do të funksionojë. Ju do të duhet të krijoni një funksion menjëherë "nga e para". Emri Menaxher (Emri i menaxherit).
Le ta quajmë funksionin tonë, le të themi, CLEAN dhe do të donim që ai të kishte dy argumente - teksti që do të pastrohet dhe lista e karaktereve të përjashtuara si një varg teksti:
Le të krijojmë, siç bëmë më parë, në skedën formulë в Emri menaxher me emrin varg, emërtojeni CLEAR dhe futeni në fushë Varg ndërtimi i mëposhtëm:
=LAMBDA(t;d;IF(d="";t;CLEAR(ZËVENDËSIM(t;LEFT(d);"");MID(d;2;255))))
Këtu ndryshorja t është teksti origjinal që duhet pastruar dhe d është lista e karaktereve që duhen fshirë.
Gjithçka funksionon si kjo:
Iteracion 1
Fragmenti SUBSTITUTE(t;LEFT(d);""), siç mund ta merrni me mend, zëvendëson karakterin e parë nga karakteri i majtë nga grupi d që do të fshihet në tekstin burimor t me një varg teksti bosh, dmth heq " A”. Si rezultat i ndërmjetëm, marrim:
Vsh zkz n 125 rubla.
Iteracion 2
Më pas funksioni thërret veten dhe si hyrje (argumenti i parë) merr atë që ka mbetur pas pastrimit në hapin e mëparshëm, dhe argumenti i dytë është vargu i karaktereve të përjashtuara duke filluar jo nga karakteri i parë, por nga karakteri i dytë, dmth "BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYYA. ,” pa fillestarin “A” – kjo bëhet nga funksioni MID. Si më parë, funksioni merr karakterin e parë nga e majta e atyre të mbetura (B) dhe e zëvendëson atë në tekstin e dhënë (Zkz n 125 rubla) me një varg bosh - marrim si rezultat të ndërmjetëm:
125 ru.
Iteracion 3
Funksioni thërret përsëri veten, duke marrë si argument të parë atë që ka mbetur nga teksti për t'u pastruar në përsëritjen e mëparshme (Bsh zkz n 125 ru.), dhe si argument i dytë, grupi i karaktereve të përjashtuara të cunguara nga një karakter më shumë në e majta, dmth “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.,” pa inicialen “B”. Pastaj merr përsëri karakterin e parë nga e majta (B) nga ky grup dhe e heq atë nga teksti - marrim:
sh zkz n 125 ru.
Dhe kështu me radhë - shpresoj që ta keni idenë. Me çdo përsëritje, lista e karaktereve që do të hiqen do të shkurtohet në të majtë dhe ne do të kërkojmë dhe zëvendësojmë karakterin tjetër nga grupi me një boshllëk.
Kur të mbarojnë të gjithë personazhet, do të na duhet të dalim nga cikli - ky rol kryhet vetëm nga funksioni IF (NESE), në të cilën është mbështjellë dizajni ynë. Nëse nuk ka mbetur asnjë karakter për t'u fshirë (d=””), atëherë funksioni nuk duhet të thërrasë më vetveten, por thjesht duhet ta kthejë tekstin për t'u pastruar (ndryshoren t) në formën e tij përfundimtare.
Përsëritja rekursive e qelizave
Në mënyrë të ngjashme, ju mund të zbatoni një numërim rekurziv të qelizave në një gamë të caktuar. Supozoni se duam të krijojmë një funksion lambda me emrin LISTA E ZËVENDËSIMIT për zëvendësimin me shumicë të fragmenteve në tekstin burimor sipas një liste referimi të caktuar. Rezultati duhet të duket si ky:
Ato. në funksionin tonë LISTA E ZËVENDËSIMIT do të ketë tre argumente:
- qelizë me tekst për përpunim (adresa e burimit)
- qeliza e parë e një kolone me vlera për të kërkuar nga kërkimi
- qeliza e parë e kolonës me vlera zëvendësuese nga kërkimi
Funksioni duhet të shkojë nga lart poshtë në drejtori dhe të zëvendësojë në mënyrë sekuenciale të gjitha opsionet nga kolona e majtë Per te gjetur te opsionet përkatëse nga kolona e djathtë zëvendësim. Ju mund ta zbatoni këtë me funksionin e mëposhtëm rekurziv lambda:
Zhvendosja poshtë në çdo përsëritje zbatohet nga një funksion standard excel SHPENZIMI (OFFSET), i cili në këtë rast ka tre argumente - diapazoni origjinal, zhvendosja e rreshtit (1) dhe zhvendosja e kolonës (0).
Epo, sapo të arrijmë në fund të drejtorisë (n = ""), duhet të përfundojmë rekursionin - ndalojmë së thirruri veten dhe shfaqim atë që është grumbulluar pas të gjitha zëvendësimeve në variablin e tekstit burimor t.
Kjo eshte e gjitha. Nuk ka makro të ndërlikuara ose pyetje të Power Query - e gjithë detyra zgjidhet nga një funksion.
- Si të përdorni funksionet e reja të grupit dinamik të Excel: FILTER, SORT, UNIC
- Zëvendësimi dhe pastrimi i tekstit me funksionin SUBSTITUTE
- Krijimi i makrove dhe funksioneve të përcaktuara nga përdoruesi (UDF) në VBA