I-LAMBDA iwumsebenzi Omusha Omkhulu Omusha wakwa-Excel

Okwamanje, i-Microsoft Excel inemisebenzi ecishe ibe ngamakhulu amahlanu yamakhasi okusebenza etholakala ngefasitela le-Function Wizard - inkinobho fx kubha yefomula. Lena isethi ehloniphekile, kodwa, nokho, cishe wonke umsebenzisi ngokushesha noma kamuva uhlangabezana nesimo lapho lolu hlu lungenayo umsebenzi awudingayo - ngoba nje alukho ku-Excel.

Kuze kube manje, okuwukuphela kwendlela yokuxazulula le nkinga kwakungama-macros, okungukuthi ukubhala umsebenzi wakho ochazwe ngumsebenzisi (UDF = Umsebenzi Ochazwe Umsebenzisi) ku-Visual Basic, okudinga amakhono afanele wokuhlela futhi, ngezinye izikhathi, akulula nhlobo. Kodwa-ke, ngezibuyekezo zakamuva ze-Office 365, isimo sesishintshile saba ngcono - umsebenzi okhethekile "we-wrapper" wengezwe ku-Excel. I-LAMBDA. Ngosizo lwayo, umsebenzi wokudala imisebenzi yakho manje usuxazululwe kalula futhi kahle.

Ake sibheke isimiso sokusetshenziswa kwayo esibonelweni esilandelayo.

Njengoba kungenzeka wazi, i-Excel inemisebenzi embalwa yokuhlaziya yedethi ekuvumela ukuthi unqume inombolo yosuku, inyanga, isonto, nonyaka yosuku oluthile. Kodwa ngesizathu esithile awukho umsebenzi onquma inombolo yekota, nayo evame ukudingeka, akunjalo? Masilungise lokhu kushiyeka bese sidala nge I-LAMBDA siqu umsebenzi omusha ukuxazulula le nkinga.

Isinyathelo 1. Bhala ifomula

Ake siqale ngeqiniso lokuthi mathupha ngendlela evamile sizobhala ifomula kuseli leshidi elibala lokho esikudingayo. Endabeni yenombolo yekota, lokhu kungenziwa, isibonelo, kanje:

Isinyathelo sesi-2. Ukusonga ku-LAMBDA nokuhlola

Manje sekuyisikhathi sokusebenzisa umsebenzi omusha we-LAMBDA futhi usonge ifomula yethu kuwo. I-syntax yomsebenzi imi kanje:

=LAMBDA(Okuguquguqukayo1; Okuguquguqukayo2; … OkuguquguqukayoN ; Expression)

lapho amagama okuguquguqukayo okukodwa noma ngaphezulu esohlwini lokuqala, futhi ukuphikisana kokugcina kuhlale kuyifomula noma isisho esibaliwe esisisebenzisayo. Amagama aguquguqukayo akufanele abukeke njengamakheli eseli futhi akufanele aqukathe amachashazi.

Esimweni sethu, kuzoba khona okuguquguqukayo okukodwa kuphela - usuku esibala ngalo inombolo yekota. Masibize okuguquguqukayo kwakho, sithi, d. Bese sigoqa ifomula yethu ngomsebenzi I-LAMBDA futhi sifaka ikheli leseli A2 lokuqala ngegama eliguquguqukayo elingelona iqiniso, sithola:

Sicela uqaphele ukuthi ngemva kokuguqulwa okunjalo, ifomula yethu (empeleni, ilungile!) yaqala ukukhiqiza iphutha, ngoba manje usuku lokuqala olusuka kuseli A2 aludluliswanga kulo. Ukuhlola nokuzethemba, ungadlulisela izimpikiswano kuyo ngokuwengeza ngemva komsebenzi I-LAMBDA kubakaki:

Isinyathelo sesi-3. Dala igama

Manje ngengxenye elula nejabulisayo. Siyavula Umphathi wegama ithebhu formula (Amafomula — Umphathi Wegama) bese udala igama elisha ngenkinobho Dala (Dala). Woza futhi ufake igama lomsebenzi wethu wesikhathi esizayo (isibonelo, Nomkvartala), nasensimini isixhumanisi (Inkomba) kopisha ngokucophelela kubha yefomula bese unamathisela umsebenzi wethu I-LAMBDA, kuphela ngaphandle kokuphikisana kokugcina (A2):

Konke. Ngemva kokuchofoza OK umsebenzi odaliwe ungasetshenziswa kunoma iyiphi iseli kunoma yiliphi ishidi lale ncwadi yokusebenzela:

Sebenzisa kwezinye izincwadi

I-LAMBDA kanye namalungu afanayo ashukumisayo

Imisebenzi yangokwezifiso idalwe ngomsebenzi I-LAMBDA ukusekela ngempumelelo umsebenzi ngama-array amasha ashukumisayo kanye nemisebenzi yawo (FILTER, I-UNIK, LIBANGA) yengezwe kuMicrosoft Excel ngo-2020.

Ake sithi sifuna ukwakha umsebenzi omusha ochazwe umsebenzisi ongaqhathanisa izinhlu ezimbili bese ubuyisela umehluko phakathi kwazo - lezo zakhi ezivela ohlwini lokuqala ezingekho kolwesibili. Umsebenzi wokuphila, akunjalo? Ngaphambilini, ngenxa yalokhu basebenzisa noma yimiphi imisebenzi a la VPR (VLOOKUP), noma i-PivotTables, noma imibuzo yombuzo wamandla. Manje ungenza ngefomula eyodwa:

Enguqulweni yesiNgisi kuzoba:

=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)

Lapha umsebenzi COUNTIF ibala inani lokuvela kwento ngayinye yohlu lokuqala kolwesibili, bese kuba umsebenzi FILTER ikhetha kuphela labo abangazange babe nalezi zehlakalo. Ngokusonga lesi sakhiwo I-LAMBDA nokudala ibanga eliqanjwe igama elisuselwa kulo negama, isibonelo, UKUSABAKAZWA KOSESHO - sizothola umsebenzi olula obuyisela umphumela wokuqhathanisa izinhlu ezimbili ngendlela yohlelo oluguquguqukayo:

Uma idatha yomthombo ingeyona evamile, kodwa amathebula "ahlakaniphile", umsebenzi wethu uzophinde ubhekane ngaphandle kwezinkinga:

Esinye isibonelo sihlukanisa umbhalo ngokushintshashintsha ngokuwuguqulela ku-XML bese siwuhlukanisa iseli ngeseli kusetshenziswa umsebenzi we-FILTER.XML esisanda kuwuhlaziya. Ukuze ungakhiqizi kabusha le fomula eyinkimbinkimbi ngesandla ngaso sonke isikhathi, kuzoba lula ukuyisonga nge-LAMBDA bese udala ububanzi obuguquguqukayo obususelwe kuyo, okungukuthi umsebenzi omusha ohlangene futhi okahle, ukuyiqamba, isibonelo, i-RAZDTEXT:

I-agumenti yokuqala yalo msebenzi kuzoba iseli elinombhalo owumthombo, kanti eyesibili – uhlamvu lwesihlukanisi, futhi izobuyisela umphumela ngendlela yohlelo oluguquguqukayo oluvundlile. Ikhodi yomsebenzi izoba kanje:

=LAMBDA(t;d; I-TRANSPOSE(FILTER.XML(““&SUBSTITUTE(t;d? "«)&»“;”//Y”)))

Uhlu lwezibonelo alupheli - kunoma yisiphi isimo lapho ngokuvamile kufanele ufake ifomula efanayo ende nenzima, umsebenzi we-LAMBDA uzokwenza ukuphila kube lula ngokuphawulekayo.

Ukubalwa okuphindaphindayo kwezinhlamvu

Zonke izibonelo zangaphambilini zibonise uhlangothi olulodwa kuphela, olusobala kakhulu, lomsebenzi we-LAMBDA - ukusetshenziswa kwayo "njenge-wrapper" yokugoqa amafomula amade kuwo nokwenza lula okokufaka kwawo. Eqinisweni, i-LAMBDA inolunye uhlangothi, olujule kakhulu, oluyiguqula ibe cishe ulimi olugcwele lwezinhlelo.

Iqiniso liwukuthi isici esibaluleke kakhulu semisebenzi ye-LAMBDA yikhono lokuyisebenzisa kuyo ukuphindisela – ukuhleleka kwezibalo, lapho kubalwa umsebenzi uzishayela wona. Kusukela kulo mkhuba, kungase kuzwakale kuthusa, kodwa ezinhlelweni, ukuphindaphinda kuyinto evamile. Ngisho nakuma-macros ku-Visual Basic, ungayisebenzisa, futhi manje, njengoba ubona, isifikile ku-Excel. Ake sizame ukuqonda le nqubo ngesibonelo esisebenzayo.

Ake sithi sifuna ukudala umsebenzi ochazwe umsebenzisi ozosusa zonke izinhlamvu ezinikeziwe embhalweni womthombo. Ukusebenziseka komsebenzi onjalo, ngicabanga ukuthi, awudingi ukufakazela - kungaba lula kakhulu ukusula idatha yokufaka egcwele ngosizo lwayo, akunjalo?

Kodwa-ke, uma kuqhathaniswa nezibonelo ezedlule, ezingaphindi, ubunzima obubili busilindile.

1. Kuzofanele siqhamuke negama lomsebenzi wethu ngaphambi kokuba siqale ukubhala ikhodi yayo, ngoba kuyo, leli gama liyobe selisetshenziswa ukubiza umsebenzi ngokwawo.
   
2. Ukufaka umsebenzi onjalo wokuphindaphinda kuseli nokuyilungisa ngokucacisa izimpikiswano kubakaki ngemva kwe-LAMBDA (njengoba senza ekuqaleni) ngeke kusebenze. Kuzodingeka udale umsebenzi ngokushesha "kusuka ekuqaleni" phakathi Umphathi wegama (Umphathi Wegama).
   

Masibize umsebenzi wethu, sithi, CLEAN futhi singathanda ukuthi kube nezimpikiswano ezimbili - umbhalo okufanele uhlanzwe kanye nohlu lwezinhlamvu ezingabaliwe njengeyunithi yezinhlamvu yombhalo:

Masidale, njengoba senzile ngaphambili, kuthebhu formula в Umphathi wegama ububanzi obuqanjwe igama, yiqambe ngokuthi CLEAR bese ufaka enkanjini Ibanga ukwakhiwa okulandelayo:

=LAMBDA(t;d;IF(d=””;t;SULE(I-SUBSTITUTE(t;LEFT(d);””);MID(d;2;255))))

Lapha okuguquguqukayo okungu-t kuwumbhalo wokuqala okufanele kusulwe, futhi u-d uwuhlu lwezinhlamvu okufanele zisuswe.

Konke kusebenza kanje:

Isikhathi 1

Isiqephu esithi SUBSTITUTE(t;LEFT(d);””), njengoba ungase uqagele, sithatha indawo yohlamvu lokuqala olusuka kuhlamvu lwesokunxele kusethi ethi d ezosuswa embhalweni ongumthombo t ngochungechunge lombhalo olungenalutho, okungukuthi, isusa “ A”. Njengomphumela omaphakathi, sithola:

I-Vsh zkz n ruble angu-125.

Isikhathi 2

Bese umsebenzi uyazibiza futhi njengokufakwayo (i-agumenti yokuqala) ithola okusele ngemva kokuhlanza esinyathelweni sangaphambilini, futhi i-agumenti yesibili iwuchungechunge lwezinhlamvu ezingabaliwe eziqala hhayi kweyokuqala, kodwa kusukela kuhlamvu lwesibili, okungukuthi “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ,” ngaphandle kokuthi “A” wokuqala - lokhu kwenziwa umsebenzi we-MID. Njengangaphambili, umsebenzi uthatha uhlamvu lokuqala ukusuka kwesobunxele kwabasele (B) bese ulufaka esikhundleni sombhalo onikezwe wona (Zkz n 125 ruble) ngentambo engenalutho - sithola njengomphumela ophakathi:

125 ru.

Isikhathi 3

Umsebenzi uzibiza futhi, uthola njengempikiswano yokuqala lokho okusele kombhalo okuzosulwa ekuphindaphindweni okudlule (Bsh zkz n 125 ru.), Futhi njengempikiswano yesibili, isethi yezinhlamvu ezingabaliwe inqanyulwe ngohlamvu olulodwa ukuze kwesobunxele, okungukuthi “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.,” ngaphandle kokuthi “B” wokuqala. Bese iphinda ithatha uhlamvu lokuqala ukusuka kwesobunxele (B) kule sethi futhi ilususe embhalweni - sithola:

sh zkz n 125 ru.

Futhi njalo - ngethemba ukuthi uyawuthola umqondo. Ngokuphindaphinda ngakunye, uhlu lwezinhlamvu ezizokhishwa luzoncishiswa kwesokunxele, futhi sizosesha futhi sikhiphe uhlamvu olulandelayo kusethi ngesikhala.

Lapho zonke izinhlamvu ziphela, sizodinga ukuphuma ku-loop - le ndima yenziwa umsebenzi nje IF (UMA), lapho umklamo wethu usongwe khona. Uma zingekho izinhlamvu ezisele ezizosuswa (d=””), umsebenzi akumele usazishayela wona, kodwa kufanele uvele ubuyisele umbhalo ukuze ususwe (okuguquguqukayo t) ngendlela yawo yokugcina.

Ukuphindaphinda okuphindayo kwamaseli

Ngokufanayo, ungasebenzisa ukubala okuphindaphindayo kwamaseli ebangeni elinikeziwe. Ake sithi sifuna ukudala umsebenzi we-lambda oqanjwe UHLU LOKUSHINTSHA ngokushintshwa kwesamba semali yezingcezwana kumbhalo womthombo ngokohlu olunikeziwe lwereferensi. Umphumela kufanele ubukeke kanje:

Labo. emsebenzini wethu UHLU LOKUSHINTSHA kuzoba nezingxabano ezintathu:

1. iseli elinombhalo ozocutshungulwa (ikheli lomthombo)
2. iseli lokuqala lekholomu elinamanani okuseshwa ekubhekeni
3. iseli lokuqala lekholomu elinamanani okushintsha asuka ekubhekeni

Umsebenzi kufanele usuke phezulu uye phansi ohlwini lwemibhalo bese ubuyisela ngokulandelana kwazo zonke izinketho ukusuka kukholomu yesokunxele Ukuthola kuya ezinkethweni ezihambisanayo ukusuka kukholamu engakwesokudla I-Substitute. Ungakwazi ukusebenzisa lokhu ngomsebenzi olandelayo we-lambda ophindaphindayo:

U-Shift phansi ekuphindaphindeni ngakunye kusetshenziswa umsebenzi ojwayelekile we-excel UKULAHLWA (OFFSET), kulokhu okunama-agumenti amathathu - ububanzi boqobo, ukushintsha komugqa (1) kanye nokugudluka kwekholomu (0).

Hhayi-ke, ngokushesha nje lapho sifika ekugcineni kohla lwemibhalo (n = “”), kufanele siqede ukuphindaphinda - siyeka ukuzibiza futhi sibonise lokho okunqwabelene ngemva kwakho konke ukumiselela kokuguquguquka kombhalo womthombo u-t.

Yilokho kuphela. Awekho amamakhro akhohlisayo noma imibuzo Yombuzo Wamandla - wonke umsebenzi uxazululwa ngomsebenzi owodwa.

• Isetshenziswa kanjani imisebenzi yohlelo olusha lwe-Excel: FILTER, SORT, UNIC
• Ukushintsha nokuhlanza umbhalo ngomsebenzi we-SUBSTITUTE
  
• Ukudala ama-macros kanye nemisebenzi echazwe ngumsebenzisi (ama-UDF) ku-VBA