Kaundan
Sa pagkakaron, ang Microsoft Excel adunay halos lima ka gatos nga worksheet functions nga magamit pinaagi sa Function Wizard window – button fx sa formula bar. Kini usa ka desente kaayo nga set, apan, bisan pa, hapit matag tiggamit sa madugay o sa madali makasugat sa usa ka sitwasyon diin kini nga lista wala maglangkob sa function nga iyang gikinahanglan - tungod lang kay wala kini sa Excel.
Hangtud karon, ang bugtong paagi sa pagsulbad niini nga problema mao ang mga macros, ie pagsulat sa imong kaugalingong user-defined function (UDF = User Defined Function) sa Visual Basic, nga nagkinahanglan ug tukma nga mga kahanas sa pagprograma ug, usahay, dili sayon. Bisan pa, sa labing bag-o nga pag-update sa Office 365, ang kahimtang nabag-o alang sa labi ka maayo - usa ka espesyal nga function sa "wrapper" ang gidugang sa Excel LAMBDA. Uban sa tabang niini, ang tahas sa paghimo sa imong kaugalingon nga mga gimbuhaton karon dali nga masulbad ug matahum.
Atong tan-awon ang prinsipyo sa paggamit niini sa mosunod nga pananglitan.
Sama sa imong nahibal-an, ang Excel adunay daghang mga function sa pag-parse sa petsa nga nagtugot kanimo sa pagtino sa gidaghanon sa adlaw, bulan, semana, ug tuig alang sa gihatag nga petsa. Apan sa pipila ka rason walay function nga nagtino sa gidaghanon sa quarter, nga kasagaran usab gikinahanglan, di ba? Atong ayuhon kini nga kakulangan ug paghimo uban sa LAMBDA kaugalingon nga bag-ong function aron masulbad kini nga problema.
Lakang 1. Isulat ang pormula
Magsugod kita sa kamatuoran nga sa kamut sa naandan nga paagi magsulat kita usa ka pormula sa usa ka sheet cell nga nagkalkula kung unsa ang kinahanglan naton. Sa kaso sa quarter number, mahimo kini, pananglitan, sama niini:
Lakang 2. Pagputos sa LAMBDA ug pagsulay
Karon na ang panahon nga gamiton ang bag-ong function sa LAMBDA ug ibutang ang among pormula niini. Ang function syntax mao ang mosunod:
=LAMBDA(Variable1; Variable2; … VariableN ; ekspresyon)
diin ang mga ngalan sa usa o daghan pa nga mga variable gilista una, ug ang katapusan nga argumento kanunay usa ka pormula o usa ka kalkulado nga ekspresyon nga naggamit niini. Ang mga variable nga ngalan kinahanglan dili sama sa mga adres sa cell ug kinahanglan dili adunay mga tulbok.
Sa among kaso, adunay usa ra ka variable - ang petsa kung diin among gikalkula ang quarter nga numero. Tawgon nato ang variable alang niini, ingnon ta, d. Dayon pagputos sa among pormula sa usa ka function LAMBDA ug ilisan ang adres sa orihinal nga cell A2 sa usa ka tinumotumo nga variable nga ngalan, atong makuha:
Palihug timan-i nga pagkahuman sa ingon nga pagbag-o, ang among pormula (sa tinuud, husto!) Nagsugod sa paghimo og usa ka sayup, tungod kay karon ang orihinal nga petsa gikan sa cell A2 wala gibalhin niini. Alang sa pagsulay ug pagsalig, mahimo nimong ipasa ang mga argumento niini pinaagi sa pagdugang niini pagkahuman sa function LAMBDA sa parentesis:
Lakang 3. Paghimo og ngalan
Karon alang sa sayon ug makalingaw nga bahin. Abri mi Pangalan sa Tigdumala tab pormula (Mga Pormula — Tagdumala sa Ngalan) ug paghimo og bag-ong ngalan gamit ang buton Paghimo (Paghimo). Paghimo ug pagbutang ug usa ka ngalan para sa atong umaabot nga gimbuhaton (pananglitan, Nomkvartala), ug sa kapatagan link (Sumbanan) pag-ayo pagkopya gikan sa formula bar ug idikit ang among function LAMBDA, lamang nga walay katapusan nga argumento (A2):
Ang tanan. Human sa pag-klik sa OK ang gibuhat nga function mahimong magamit sa bisan unsang cell sa bisan unsang panid niini nga workbook:
Gigamit sa ubang mga libro
LAMBDA ug dinamikong mga han-ay
Pasadya nga mga gimbuhaton nga gihimo gamit ang usa ka function LAMBDA malampuson nga nagsuporta sa trabaho uban sa bag-ong dinamikong arrays ug sa ilang mga gimbuhaton (filter, UNIK, GRADO) gidugang sa Microsoft Excel sa 2020.
Ingnon ta nga gusto namong maghimo usa ka bag-ong function nga gitakda sa user nga magkumpara sa duha ka lista ug ibalik ang kalainan tali kanila - kadtong mga elemento gikan sa una nga lista nga wala sa ikaduha. Trabaho sa kinabuhi, di ba? Kaniadto, alang niini ilang gigamit ang bisan unsang mga function a la VPR (VLOOKUP), o PivotTable, o Power Query nga mga pangutana. Karon mahimo nimo ang usa ka pormula:
Sa English nga bersyon kini mahimong:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Dinhi ang function COUNTIF nag-ihap sa gidaghanon sa mga panghitabo sa matag elemento sa unang listahan sa ikaduha, ug dayon ang function filter nagpili lamang sa kanila nga wala niini nga mga panghitabo. Pinaagi sa pagputos niini nga istruktura LAMBDA ug paghimo sa usa ka gihinganlan nga range base niini nga adunay usa ka ngalan, pananglitan, PAGPANGITA NGA PAGHATAG - makakuha kami usa ka kombenyente nga function nga nagbalik sa sangputanan sa pagtandi sa duha nga mga lista sa porma sa usa ka dinamikong laray:
Kung ang gigikanan nga datos dili ordinaryo, apan "smart" nga mga lamesa, ang among function makasagubang usab nga wala’y mga problema:
Ang laing pananglitan mao ang dinamikong pagbahin sa teksto pinaagi sa pag-convert niini ngadto sa XML ug dayon pag-parse niini sa cell pinaagi sa cell gamit ang FILTER.XML function nga bag-o lang natong gi-parse. Aron dili manu-mano ang paghimo niining komplikado nga pormula sa matag higayon, mas sayon ang pagputos niini sa LAMBDA ug paghimo og dinamikong han-ay base niini, ie usa ka bag-ong compact ug kombenyente nga function, pagngalan niini, pananglitan, RAZDTEXT:
Ang una nga argumento sa kini nga function mao ang cell nga adunay gigikanan nga teksto, ug ang ikaduha - ang karakter sa separator, ug kini ibalik ang resulta sa porma sa usa ka pinahigda nga dinamikong laray. Ang function code mahimong ingon sa mosunod:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Ang lista sa mga pananglitan walay katapusan - sa bisan unsang sitwasyon diin kanunay ka nga mosulod sa parehas nga taas ug hago nga pormula, ang LAMBDA function makapasayon sa kinabuhi.
Recursive enumeration sa mga karakter
Ang tanan nga nangaging mga pananglitan nagpakita lamang sa usa, ang labing klaro, nga bahin sa function sa LAMBDA - ang paggamit niini isip usa ka "wrapper" alang sa pagputos sa taas nga mga pormula niini ug pagpayano sa ilang input. Sa tinuud, ang LAMBDA adunay lain, labi ka lawom, nga bahin nga nahimo kini nga hapit usa ka hingpit nga sinultian nga programming.
Ang kamatuoran mao nga ang usa ka sukaranan nga hinungdanon nga bahin sa mga gimbuhaton sa LAMBDA mao ang abilidad sa pagpatuman niini pagbalikbalik - lohika sa mga kalkulasyon, kung sa proseso sa pagkalkula ang function nagtawag sa iyang kaugalingon. Gikan sa batasan, kini tingali makahadlok, apan sa pagprograma, ang pagbalik-balik usa ka kasagaran nga butang. Bisan sa mga macro sa Visual Basic, mahimo nimong ipatuman kini, ug karon, ingon sa imong nakita, naabut na kini sa Excel. Atong sulayan nga masabtan kini nga teknik sa usa ka praktikal nga pananglitan.
Ibutang ta nga gusto namon maghimo usa ka function nga gitakda sa gumagamit nga magtangtang sa tanan nga gihatag nga mga karakter gikan sa gigikanan nga teksto. Ang kapuslanan sa ingon nga usa ka function, sa akong hunahuna, dili nimo kinahanglan pamatud-an - kini labi ka kombenyente sa paghawan sa mga littered input data sa tabang niini, di ba?
Bisan pa, kung itandi sa kaniadto, dili recursive nga mga pananglitan, duha ka kalisud ang naghulat kanato.
- Kinahanglan nga maghimo kita og usa ka ngalan alang sa atong function sa dili pa kita magsugod sa pagsulat sa code niini, tungod kay niini, kini nga ngalan magamit na sa pagtawag sa function mismo.
- Ang pagsulod sa ingon nga recursive function sa usa ka cell ug pag-debug niini pinaagi sa pagtino sa mga argumento sa mga bracket pagkahuman sa LAMBDA (sama sa among gibuhat kaniadto) dili molihok. Kinahanglan ka nga maghimo usa ka function dayon "gikan sa wala" sa Pangalan sa Tigdumala (Ngalan Manager).
Tawgon nato ang atong function, ingnon ta, CLEAN ug gusto namo nga adunay duha ka argumento - ang teksto nga limpyohan ug ang lista sa wala iapil nga mga karakter isip usa ka text string:
Himoon nato, sama sa atong gibuhat kaniadto, sa tab pormula в Tagdumala sa ngalan ginganlan nga range, nganli kini nga KLARO ug isulod sa field range mosunod nga pagtukod:
=LAMBDA(t;d;IF(d=””;t;CLEAR(SUBSTITUTE(t;LEFT(d);””);MID(d;2;255))))
Dinhi ang variable t mao ang orihinal nga teksto nga tangtangon, ug ang d mao ang lista sa mga karakter nga papason.
Kini tanan nagtrabaho sama niini:
Pag-uli 1
Ang tipik nga SUBSTITUTE(t;LEFT(d);””), sama sa imong pagtag-an, mopuli sa unang karakter gikan sa wala nga karakter gikan sa set d nga tangtangon sa source text t gamit ang walay sulod nga text string, ie nagtangtang sa “ A”. Ingon sa usa ka intermediate nga resulta, atong makuha:
Vsh zkz n 125 nga mga rubles.
Pag-uli 2
Dayon ang function nagtawag sa iyang kaugalingon ug isip input (ang unang argumento) makadawat sa nahibilin human sa paglimpyo sa miaging lakang, ug ang ikaduha nga argumento mao ang hilo sa wala iapil nga mga karakter nga nagsugod dili sa una, apan gikan sa ikaduha nga karakter, ie "BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ," nga wala ang inisyal nga "A" - kini gihimo sa MID function. Sama sa kaniadto, gikuha sa function ang una nga karakter gikan sa wala sa nahabilin nga mga (B) ug gipulihan kini sa teksto nga gihatag niini (Zkz n 125 rubles) nga adunay usa ka walay sulod nga pisi - makuha namon ingon usa ka intermediate nga sangputanan:
125 ru.
Pag-uli 3
Ang function nagtawag sa iyang kaugalingon pag-usab, nga nakadawat isip unang argumento kung unsa ang nahibilin sa teksto nga malimpyohan sa miaging pag-uli (Bsh zkz n 125 ru.), Ug isip ikaduha nga argumento, ang hugpong sa wala iapil nga mga karakter giputol sa usa pa ka karakter ngadto sa ang wala, ie “VGDEEGZIKLMNOPRSTUFHTSCHSSHHCHYYYYUYA.,” nga walay inisyal nga “B”. Unya gikuha pag-usab ang una nga karakter gikan sa wala (B) gikan sa kini nga set ug gitangtang kini gikan sa teksto - makuha namon:
sh zkz n 125 ru.
Ug uban pa - nanghinaut ko nga makuha nimo ang ideya. Sa matag pag-uli, ang lista sa mga karakter nga tangtangon pagaputlon sa wala, ug among pangitaon ug pulihan ang sunod nga karakter gikan sa set nga adunay kahaw-ang.
Kung mahurot na ang tanan nga mga karakter, kinahanglan namon nga mogawas sa loop - kini nga tahas gihimo ra sa function IF (KUNG), diin giputos ang among disenyo. Kung wala'y nahabilin nga mga karakter nga papason (d = ""), nan ang function kinahanglan dili na magtawag sa iyang kaugalingon, apan kinahanglan nga ibalik ang teksto aron ma-clear (variable t) sa katapusan nga porma niini.
Recursive nga pag-uli sa mga selula
Sa susama, mahimo nimong ipatuman ang usa ka recursive enumeration sa mga cell sa gihatag nga range. Ibutang ta nga gusto namon maghimo usa ka function sa lambda nga ginganlag LISTAHAN SA PAGPALIT para sa pakyaw nga pag-ilis sa mga tipik sa tinubdan nga teksto sumala sa gihatag nga listahan sa pakisayran. Ang resulta kinahanglan nga tan-awon sama niini:
Mga. sa among function LISTAHAN SA PAGPALIT adunay tulo ka argumento:
- cell nga adunay teksto nga iproseso (adres sa gigikanan)
- ang una nga cell sa usa ka kolum nga adunay mga kantidad nga pangitaon gikan sa pagpangita
- ang una nga cell sa kolum nga adunay mga kapuli nga kantidad gikan sa pagpangita
Ang function kinahanglan moadto gikan sa taas hangtod sa ubos sa direktoryo ug pulihan nga sunud-sunod ang tanan nga mga kapilian gikan sa wala nga kolum Pangita-on sa katugbang nga mga kapilian gikan sa tuo nga kolum Kapuli. Mahimo nimong ipatuman kini sa mosunod nga recursive lambda function:
Ang pagbalhin paubos sa matag pag-uli gipatuman sa usa ka standard nga excel function PAGBALIGYA (OFFSET), nga sa niini nga kaso adunay tulo ka mga argumento - ang orihinal nga range, row shift (1) ug column shift (0).
Aw, sa diha nga kita moabut sa katapusan sa direktoryo (n = ""), kita kinahanglan nga tapuson ang recursion - kita mohunong sa pagtawag sa atong mga kaugalingon ug ipakita kon unsa ang natipon human sa tanan nga mga puli sa tinubdan text variable t.
Mao ra. Walay malisud nga macros o Power Query nga mga pangutana - ang tibuok buluhaton masulbad pinaagi sa usa ka function.
- Giunsa paggamit ang bag-ong dinamikong array function sa Excel: FILTER, SORT, UNIC
- Pag-ilis ug paglimpyo sa teksto gamit ang SUBSTITUTE function
- Paghimo ug macros ug user-defined functions (UDFs) sa VBA