Nilalaman
Sa ngayon, ang Microsoft Excel ay may halos limang daang worksheet function na available sa pamamagitan ng Function Wizard window – button fx sa formula bar. Ito ay isang napaka-disenteng hanay, ngunit, gayunpaman, halos lahat ng gumagamit ay maaga o huli ay nakatagpo ng isang sitwasyon kung saan ang listahang ito ay hindi naglalaman ng function na kailangan niya - dahil lamang sa wala ito sa Excel.
Hanggang ngayon, ang tanging paraan upang malutas ang problemang ito ay mga macro, ibig sabihin, pagsulat ng iyong sariling function na tinukoy ng gumagamit (UDF = User Defined Function) sa Visual Basic, na nangangailangan ng naaangkop na mga kasanayan sa programming at, kung minsan, ay hindi madali. Gayunpaman, sa pinakabagong mga update sa Office 365, ang sitwasyon ay nagbago para sa mas mahusay - isang espesyal na function na "wrapper" ay idinagdag sa Excel LAMBDA. Sa tulong nito, ang gawain ng paglikha ng iyong sariling mga pag-andar ay nalutas na ngayon nang madali at maganda.
Tingnan natin ang prinsipyo ng paggamit nito sa sumusunod na halimbawa.
Tulad ng malamang na alam mo, ang Excel ay may ilang mga function ng pag-parse ng petsa na nagbibigay-daan sa iyong matukoy ang bilang ng araw, buwan, linggo, at taon para sa isang partikular na petsa. Ngunit sa ilang kadahilanan ay walang function na tumutukoy sa bilang ng quarter, na madalas ding kailangan, di ba? Ayusin natin ang pagkukulang na ito at gumawa ng gamit LAMBDA sariling bagong function upang malutas ang problemang ito.
Hakbang 1. Isulat ang formula
Magsimula tayo sa katotohanan na manu-mano sa karaniwang paraan ay magsusulat tayo ng isang formula sa isang sheet cell na kinakalkula kung ano ang kailangan natin. Sa kaso ng quarter number, maaari itong gawin, halimbawa, tulad nito:
Hakbang 2. Pag-wrap sa LAMBDA at pagsubok
Ngayon ay oras na upang ilapat ang bagong function ng LAMBDA at i-wrap ang aming formula dito. Ang function syntax ay ang mga sumusunod:
=LAMBDA(Variable1; Variable2; ... VariableN ; pagpapahayag)
kung saan ang mga pangalan ng isa o higit pang mga variable ay unang nakalista, at ang huling argumento ay palaging isang formula o isang kalkuladong expression na gumagamit ng mga ito. Ang mga variable na pangalan ay hindi dapat magmukhang mga cell address at hindi dapat maglaman ng mga tuldok.
Sa aming kaso, magkakaroon lamang ng isang variable - ang petsa kung saan namin kinakalkula ang quarter number. Tawagan natin ang variable para dito, sabihin nating, d. Pagkatapos ay i-wrap ang aming formula sa isang function LAMBDA at pinapalitan ang address ng orihinal na cell A2 ng isang kathang-isip na variable na pangalan, makukuha natin:
Mangyaring tandaan na pagkatapos ng naturang pagbabago, ang aming formula (sa katunayan, tama!) ay nagsimulang gumawa ng isang error, dahil ngayon ang orihinal na petsa mula sa cell A2 ay hindi inilipat dito. Para sa pagsubok at kumpiyansa, maaari mong ipasa ang mga argumento dito sa pamamagitan ng pagdaragdag sa kanila pagkatapos ng function LAMBDA sa panaklong:
Hakbang 3. Lumikha ng isang pangalan
Ngayon para sa madali at masaya na bahagi. Binuksan namin Pangalan ng Tagapamahala tab pormula (Mga Formula — Name Manager) at lumikha ng bagong pangalan gamit ang pindutan Lumikha (Lumikha). Bumuo at maglagay ng pangalan para sa ating function sa hinaharap (halimbawa, Nomkvartala), at sa larangan link (Sanggunian) maingat na kopyahin mula sa formula bar at i-paste ang aming function LAMBDA, wala lamang ang huling argumento (A2):
Lahat. Pagkatapos mag-click sa OK ang nilikhang function ay maaaring gamitin sa anumang cell sa anumang sheet ng workbook na ito:
Gamitin sa ibang mga libro
LAMBDA at mga dynamic na array
Mga custom na function na ginawa gamit ang isang function LAMBDA matagumpay na sumusuporta sa trabaho gamit ang mga bagong dynamic na array at ang kanilang mga function (FILTER, UNIK, GRADO) idinagdag sa Microsoft Excel noong 2020.
Sabihin nating gusto naming lumikha ng bagong function na tinukoy ng user na maghahambing ng dalawang listahan at ibabalik ang pagkakaiba sa pagitan ng mga ito – ang mga elementong iyon mula sa unang listahan na wala sa pangalawa. Trabaho sa buhay, di ba? Noong nakaraan, para dito ginamit nila ang alinman sa mga function a la VPR (VLOOKUP), o PivotTables, o Power Query query. Ngayon ay maaari mong gawin sa isang formula:
Sa Ingles na bersyon ito ay magiging:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Narito ang function COUNTIF binibilang ang bilang ng mga paglitaw ng bawat elemento ng unang listahan sa pangalawa, at pagkatapos ay ang function FILTER pipili lamang sa kanila na hindi nagkaroon ng mga ganitong pangyayari. Sa pamamagitan ng pambalot sa istrukturang ito LAMBDA at paglikha ng pinangalanang hanay batay dito na may pangalan, halimbawa, PAGHAHANAP DISTRIBUTION – makakakuha tayo ng isang maginhawang function na nagbabalik ng resulta ng paghahambing ng dalawang listahan sa anyo ng isang dynamic na array:
Kung ang pinagmulan ng data ay hindi karaniwan, ngunit "matalinong" mga talahanayan, ang aming pag-andar ay makakayanan din nang walang mga problema:
Ang isa pang halimbawa ay ang dynamic na paghahati ng text sa pamamagitan ng pag-convert nito sa XML at pagkatapos ay pag-parse nito ng cell sa pamamagitan ng cell gamit ang FILTER.XML function na na-parse namin kamakailan. Upang hindi ma-reproduce nang manu-mano ang kumplikadong formula na ito sa bawat oras, magiging mas madali itong balutin sa LAMBDA at lumikha ng isang dynamic na hanay batay dito, ibig sabihin, isang bagong compact at maginhawang function, na pinangalanan ito, halimbawa, RAZDTEXT:
Ang unang argumento ng function na ito ay ang cell na may source text, at ang pangalawa - ang separator character, at ibabalik nito ang resulta sa anyo ng horizontal dynamic array. Ang function code ay magiging ganito:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Ang listahan ng mga halimbawa ay walang katapusan - sa anumang sitwasyon kung saan madalas mong kailangang ipasok ang parehong mahaba at masalimuot na formula, ang LAMBDA function ay gagawing mas madali ang buhay.
Recursive enumeration ng mga character
Ang lahat ng mga nakaraang halimbawa ay nagpakita lamang ng isa, ang pinaka-halata, na bahagi ng LAMBDA function - ang paggamit nito bilang isang "wrapper" para sa pagbabalot ng mahabang mga formula dito at pagpapasimple ng kanilang input. Sa katunayan, ang LAMBDA ay may isa pa, mas malalim, na bahagi na ginagawa itong halos isang ganap na programming language.
Ang katotohanan ay ang isang pangunahing mahalagang tampok ng mga function ng LAMBDA ay ang kakayahang ipatupad ang mga ito pag-urong – lohika ng mga kalkulasyon, kapag nasa proseso ng pagkalkula ang function ay tumatawag mismo. Mula sa ugali, ito ay maaaring tunog katakut-takot, ngunit sa programming, recursion ay isang pangkaraniwang bagay. Kahit na sa mga macro sa Visual Basic, maaari mo itong ipatupad, at ngayon, tulad ng nakikita mo, ito ay dumating sa Excel. Subukan nating maunawaan ang pamamaraan na ito sa isang praktikal na halimbawa.
Ipagpalagay na gusto naming lumikha ng isang function na tinukoy ng gumagamit na mag-aalis ng lahat ng ibinigay na mga character mula sa pinagmulang teksto. Ang pagiging kapaki-pakinabang ng naturang function, sa palagay ko, hindi mo kailangang patunayan - ito ay magiging napaka-maginhawa upang i-clear ang littered input data sa tulong nito, tama?
Gayunpaman, kumpara sa nauna, hindi recursive na mga halimbawa, dalawang paghihirap ang naghihintay sa amin.
- Kakailanganin nating makabuo ng isang pangalan para sa ating function bago natin simulan ang pagsulat ng code nito, dahil dito, ang pangalang ito ay gagamitin na para tawagan ang function mismo.
- Ang pagpasok ng naturang recursive function sa isang cell at pag-debug nito sa pamamagitan ng pagtukoy ng mga argumento sa mga bracket pagkatapos ng LAMBDA (gaya ng ginawa namin kanina) ay hindi gagana. Kakailanganin mong lumikha ng isang function kaagad "mula sa simula" sa Pangalan ng Tagapamahala (Name Manager).
Tawagan natin ang aming function, sabihin nating, CLEAN at gusto naming magkaroon ito ng dalawang argumento – ang text na lilinisin at ang listahan ng mga hindi kasamang character bilang isang text string:
Gumawa tayo, tulad ng ginawa natin kanina, sa tab pormula в Tagapamahala ng pangalan pinangalanang range, pangalanan itong CLEAR at ipasok sa field Saklaw sumusunod na konstruksyon:
=LAMBDA(t;d;IF(d=””;t;CLEAR(SUBSTITUTE(t;LEFT(d);””);MID(d;2;255))))
Dito ang variable na t ay ang orihinal na text na tatanggalin, at ang d ay ang listahan ng mga character na tatanggalin.
Ang lahat ay gumagana tulad nito:
Pag-ulit 1
Ang fragment na SUBSTITUTE(t;LEFT(d);””), gaya ng maaari mong hulaan, ay papalitan ang unang character mula sa kaliwang character mula sa set d na tatanggalin sa source text t ng walang laman na text string, ibig sabihin, inaalis ang “ A”. Bilang isang intermediate na resulta, nakukuha namin ang:
Vsh zkz n 125 rubles.
Pag-ulit 2
Pagkatapos ay tinawag ng function ang sarili nito at bilang input (ang unang argumento) ay tumatanggap ng kung ano ang natitira pagkatapos ng paglilinis sa nakaraang hakbang, at ang pangalawang argumento ay ang string ng mga hindi kasama na mga character na nagsisimula hindi mula sa una, ngunit mula sa pangalawang character, ibig sabihin, "BVGDEEGZIKLMNOPRSTUFHTSCHSSHHCHYYYYYA. ," nang walang inisyal na "A" - ginagawa ito ng MID function. Tulad ng dati, kinukuha ng function ang unang character mula sa kaliwa ng mga natitira (B) at pinapalitan ito sa tekstong ibinigay dito (Zkz n 125 rubles) na may isang walang laman na string - nakukuha namin bilang isang intermediate na resulta:
125 ru.
Pag-ulit 3
Tinatawag muli ng function ang sarili nito, na tinatanggap bilang unang argumento kung ano ang natitira sa tekstong i-clear sa nakaraang pag-ulit (Bsh zkz n 125 ru.), At bilang pangalawang argumento, ang hanay ng mga hindi kasamang character ay pinutol ng isa pang character sa sa kaliwa, ie “VGDEEGZIKLMNOPRSTUFHTSCHSSHHCHYYYYUYA.,” nang walang inisyal na “B”. Pagkatapos ay muli nitong kukunin ang unang character mula sa kaliwa (B) mula sa set na ito at aalisin ito mula sa teksto - nakukuha natin:
sh zkz n 125 ru.
At iba pa - sana makuha mo ang ideya. Sa bawat pag-ulit, ang listahan ng mga character na aalisin ay puputulin sa kaliwa, at hahanapin at papalitan namin ng void ang susunod na character mula sa set.
Kapag naubos na ang lahat ng character, kakailanganin nating lumabas sa loop - ang papel na ito ay ginagampanan lamang ng function IF (KUNG), kung saan nakabalot ang aming disenyo. Kung walang natitirang character na tatanggalin (d=””), hindi na dapat tawagin ng function ang sarili nito, ngunit dapat na lang ibalik ang text para i-clear (variable t) sa huling anyo nito.
Recursive na pag-ulit ng mga cell
Katulad nito, maaari kang magpatupad ng recursive enumeration ng mga cell sa isang ibinigay na hanay. Ipagpalagay na gusto naming lumikha ng isang lambda function na pinangalanan LISTAHAN NG PALIT para sa pakyawan na pagpapalit ng mga fragment sa pinagmulang teksto ayon sa isang ibinigay na listahan ng sanggunian. Ang resulta ay dapat magmukhang ganito:
Yung. sa aming function LISTAHAN NG PALIT magkakaroon ng tatlong argumento:
- cell na may text na ipoproseso (source address)
- ang unang cell ng isang column na may mga value na hahanapin mula sa lookup
- ang unang cell ng column na may mga kapalit na value mula sa lookup
Ang function ay dapat pumunta mula sa itaas hanggang sa ibaba sa direktoryo at palitan nang sunud-sunod ang lahat ng mga opsyon mula sa kaliwang column Hanapin sa mga kaukulang opsyon mula sa kanang hanay Kapalit. Maaari mong ipatupad ito gamit ang sumusunod na recursive lambda function:
Ang shift pababa sa bawat pag-ulit ay ipinapatupad ng isang karaniwang excel function TANGGALIN (OFFSET), na sa kasong ito ay may tatlong argumento – ang orihinal na hanay, row shift (1) at column shift (0).
Buweno, sa sandaling maabot natin ang dulo ng direktoryo (n = ""), dapat nating tapusin ang recursion - huminto tayo sa pagtawag sa ating sarili at ipakita kung ano ang naipon pagkatapos ng lahat ng mga kapalit sa source text variable t.
Iyon lang. Walang nakakalito na macro o Power Query na mga query – ang buong gawain ay nalulutas sa pamamagitan ng isang function.
- Paano gamitin ang bagong dynamic array function ng Excel: FILTER, SORT, UNIC
- Pagpapalit at paglilinis ng text gamit ang SUBSTITUTE function
- Paglikha ng mga macro at user-defined function (UDF) sa VBA