LAMBDA iExcel's New Super Function

Mukati

Parizvino, Microsoft Excel ine angangoita mazana mashanu ebasa rekushanda anowanikwa kuburikidza neFundo Wizard hwindo - bhatani fx mune formula bar. Iyi seti yakanaka kwazvo, asi, zvisinei, anenge wese mushandisi nekukurumidza kana gare gare anosangana nemamiriro ezvinhu apo runyorwa urwu rusina basa raanoda - nekuda kwekuti harisi muExcel.

Kusvika ikozvino, nzira chete yekugadzirisa dambudziko iri yaive macros, kureva kunyora yako wega-yakatsanangurwa basa (UDF = Mushandisi Inotsanangurwa Basa) muVisual Basic, iyo inoda hunyanzvi hwekugadzirisa zvirongwa uye, dzimwe nguva, haisi nyore zvachose. Nekudaro, neazvino Office 365 inogadziridza, mamiriro achinja kuti ave nani - yakakosha "wrapper" basa rakawedzerwa kuExcel. LAMBDA. Nerubatsiro rwayo, basa rekugadzira ako pachako mabasa rave kugadziriswa zviri nyore uye zvakanaka.

Ngatitarisei musimboti wekushandiswa kwayo mumuenzaniso unotevera.

Sezvaungangoziva iwe, Excel ine akati wandei zuva parsing mabasa ayo anotendera iwe kuti uone nhamba yezuva, mwedzi, vhiki, uye gore yezuva rakapihwa. Asi nokuda kwechimwe chikonzero hapana basa rinotarisa nhamba yekota, iyo inowanzodiwawo, handiti? Ngatigadzirisei kukanganisa uku uye tigadzire ne LAMBDA pachako basa idzva kugadzirisa dambudziko iri.

Danho 1. Nyora chimiro

Ngatitangei nenyaya yekuti nemaoko nenzira yakajairwa tichanyora fomula musheet cell inoverenga zvatinoda. Panyaya yekota nhamba, izvi zvinogona kuitwa, semuenzaniso, seizvi:

Nhanho 2. Kupeta muLAMBDA uye kuedza

Iye zvino yave nguva yekushandisa iyo nyowani LAMBDA basa uye kuputira fomula yedu mairi. Basa re syntax rakaita seizvi:

=LAMBDA(Variable1; Variable2; … VariableN ; Tsanangudzo)

apo mazita echimwe kana kupfuura anorongwa kutanga, uye nharo yekupedzisira inogara iri fomula kana kuti chirevo chakaverengerwa chinoashandisa. Mazita anochinjika haafanire kuita sekero dzesero uye haafanire kunge aine madotsi.

Muchiitiko chedu, pachava nekusiyana kumwe chete - zuva ratinoverenga nhamba yekota. Ngatishevedze shanduko yacho, toti, d. Zvadaro kuputira fomula yedu mune basa LAMBDA uye kutsiva kero yepakutanga sero A2 ine manyepo akasiyana zita, tinowana:

Ndapota cherechedza kuti mushure mekushandura kwakadaro, fomu yedu (chaizvo, yakarurama!) yakatanga kubudisa kukanganisa, nokuti ikozvino zuva rekutanga kubva muchitokisi A2 harina kuendeswa kwairi. Kuti uedze uye uvimbe, unogona kupfuudza nharo kwairi nekudziwedzera mushure mebasa LAMBDA mumaparenthes:

Danho 3. Gadzira zita

Iye zvino kune chikamu chiri nyore uye chinonakidza. Tinovhura Zita reManeja Tab Formula (Mafomula - Maneja Wezita) uye gadzira zita idzva nebhatani sika (Gadzira). Huya uye uise zita rebasa redu remangwana (semuenzaniso, Nomkvartala), uye kumunda batanidzo (Referensi) nyatsotevedzera kubva kuformula bar uye unamate basa redu LAMBDA, chete pasina kupokana kwekupedzisira (A2):

Zvose. Mushure mekudzvanya pa OK basa rakagadzirwa rinogona kushandiswa mune chero sero pane chero pepa rebhuku rino rebasa:

Shandisa mune mamwe mabhuku

Nekuti yakagadzirwa ne LAMBDA Sezvo mabasa anotsanangurwa nemushandisi ari, chokwadi, ane mazita emhando, unogona nyore kuita kuti awanikwe kwete chete mubhuku rebasa razvino. Izvo zvichave zvakakwana kukopa sero nebasa racho uye nekuriisa chero mune pepa reimwe faira.

LAMBDA uye dynamic arrays

Custom mabasa akagadzirwa ane basa LAMBDA zvinobudirira kutsigira basa neane dynamic arrays uye mabasa awo (FILTER, UNIK, giredha) yakawedzerwa kuMicrosoft Excel muna 2020.

Ngatitii tinoda kugadzira chinhu chitsva-chakatsanangurwa nemushandisi chinofananidza rondedzero mbiri uye kudzosa mutsauko uripo pakati pawo - izvo zvinhu kubva parunyorwa rwekutanga zvisiri muchipiri. Basa rehupenyu, handizvo here? Pakutanga, kune izvi vaishandisa chero mabasa a la VPR (VLOOKUP), kana PivotTables, kana Power Query mibvunzo. Iye zvino unogona kuita neimwe formula:

Mushanduro yeChirungu ichave:

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

Heino basa COUNTIF inoverenga nhamba yezviitiko zvechimwe nechimwe chezvinyorwa zvekutanga mune yechipiri, uyezve basa FILTER inosarudza avo chete avo vasina kuitika izvi. Nekuputira chimiro ichi mukati LAMBDA uye kugadzira rondedzero yakanyorwa yakavakirwa pairi ine zita, semuenzaniso, TSVAGA KUGOVA - isu tichawana basa rakaringana rinodzosa mhedzisiro yekuenzanisa mazita maviri muchimiro cheane simba array:

Kana iyo sosi data isiri yakajairwa, asi "smart" matafura, basa redu richakwanisawo kurarama pasina matambudziko:

Mumwe muenzaniso kupatsanura zvine simba mavara nekuashandura kuti ave XML wobva waapatsanura sero nesero uchishandisa FILTER.XML basa ratakapfuudza. Kuti urege kuburitsa iyi fomula yakaoma nemaoko nguva dzese, zvichava nyore kuiputira muLAMBDA uye kugadzira dhizaini renji rakavakirwa pairi, kureva, nyowani compact uye inoshanda basa, kuitumidza, semuenzaniso, RAZDTEXT:

Nharo yekutanga yebasa iri ichava sero rine chinyorwa chezvinyorwa, uye chechipiri - mutsara wekuparadzanisa, uye ichadzorera mhedzisiro muchimiro chehorizontal dynamic array. Iyo kodhi yebasa ichave seizvi:

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

Rondedzero yemienzaniso haiperi - mune chero mamiriro ezvinhu apo iwe unowanzo pinda iyo yakafanana yakareba uye yakaoma fomula, iyo LAMBDA basa rinoita kuti hupenyu huve nyore.

Kudzokororwa kuverengwa kwevatambi

Mienzaniso yese yapfuura yakaratidza imwe chete, iyo yakanyatsojeka, yedivi reLAMBDA basa - kushandiswa kwayo se "wrapper" yekuputira mafomula marefu mairi uye kurerutsa mapindiro avo. Muchokwadi, LAMBDA ine imwe, yakadzika zvakanyanya, divi rinorishandura kuita rinenge rakazara-yakazara hurongwa mutauro.

Icho chokwadi ndechekuti chinhu chakakosha cheLAMBDA mabasa ndiko kugona kuashandisa mukati kudzokorora - logic yekuverenga, kana iri mukati mekuverenga basa rinozvidaidza. Kubva pane iyo tsika, inogona kunzwika inotyisa, asi mukuronga, kudzokorora chinhu chakajairika. Kunyangwe mumacros muVisual Basic, unogona kuishandisa, uye ikozvino, sezvauri kuona, yauya kuExcel. Ngatiedzei kunzwisisa nzira iyi nemuenzaniso unoshanda.

Ngatitii tinoda kugadzira basa-rinotsanangurwa nemushandisi rinobvisa mavara ese akapihwa kubva muzvinyorwa. Kubatsira kwebasa rakadaro, ndinofunga, haufanire kuratidza - zvingave zviri nyore kujekesa dhata rekuisa data nerubatsiro rwayo, handiti?

Nekudaro, tichienzanisa neyakapfuura, isingadzokeri mienzaniso, matambudziko maviri akatimirira.

Tichafanira kuuya nezita rebasa redu tisati tatanga kunyora kodhi yaro, nokuti mairi, zita iri richatoshandiswa kudana basa racho pacharo.
Kupinza basa rekudzokorodza rakadaro musero nekurigadzirisa nekutsanangura nharo mumabhuraketi mushure meLAMBDA (sezvatakaita pakutanga) hazvishande. Iwe uchafanirwa kugadzira basa nekukasira "kubva pakutanga" mukati Zita reManeja (Maneja weZita).

Ngatishevedze basa redu, toti, CLEAN uye tinoda kuti ive nenharo mbiri - zvinyorwa zvicheneswe uye runyorwa rwemavara asina kubatanidzwa setambo yemavara:

Ngatigadzire, sezvatakaita pakutanga, pane tab Formula в Name manager zita renji, ripe zita ZVAKARE uye pinda mumunda dungwerungwe kugadzirwa kunotevera:

=LAMBDA(t;d;IF(d=””;t;CHABVA(TAPISA(t;LEFT(d);””);MID(d;2;255))))

Pano musiyano t ndiwo mavara ekutanga anofanira kucheneswa, uye d ndiwo rondedzero yemavara achadzimwa.

Zvose zvinoshanda seizvi:

Kuchinja 1

Chimedu SUBSTITUTE(t; LEFT(d);””), sezvaungafungidzira, chinotsiva hunhu hwekutanga kubva paruboshwe kubva pakaseti d kuti dzibviswe muzvinyorwa t netambo isina chinhu, kureva kubvisa “ A”. Semhedzisiro yepakati, tinowana:

Vsh zkz n 125 rubles.

Kuchinja 2

Zvino basa racho rinozvidaidza uye sekuisa (nharo yekutanga) inogamuchira zvakasara mushure mekuchenesa mudanho rakapfuura, uye nharo yechipiri tambo yemavara asina kubatanidzwa kutanga kwete kubva kune wekutanga, asi kubva kune wechipiri, kureva “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ," pasina yekutanga "A" - izvi zvinoitwa neMID basa. Sezvakamboitika, basa racho rinotora chimiro chekutanga kubva kuruboshwe rwevakasara (B) uye kuchitsiva mumashoko akapiwa kwairi (Zkz n 125 rubles) netambo isina chinhu - tinowana semugumisiro wepakati:

125 ru.

Kuchinja 3

Basa racho rinozvidaidza zvakare, richigashira senharo yekutanga izvo zvasara zvechinyorwa kuti zvicheneswe pakudzokorora kwapfuura (Bsh zkz n 125 ru.), Uye senharo yechipiri, seti yemavara asina kubatanidzwa akaderedzwa nemumwe hunhu kuruboshwe, kureva “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.,” pasina mavambo “B”. Zvadaro zvakare inotora hunhu hwekutanga kubva kuruboshwe (B) kubva pane iyi seti uye yoibvisa kubva muzvinyorwa - tinowana:

sh zkz n 125 ru.

Uye zvichingodaro - ndinovimba iwe unowana iyo pfungwa. Nekudzokororwa kwega kwega, runyoro rwemavara achabviswa anozodimburwa kuruboshwe, uye isu tichatsvaga uye nekutsiva anotevera mavara kubva pane seti asina chinhu.

Kana mavara ese apera, isu tichada kubuda muchiuno - basa iri rinongoitwa nebasa IF (KANA), umo magadzirirwo edu akaputirwa. Kana pasina mavara asara kuti adzime (d=””), ipapo basa racho harifanire kuzvidaidza, asi rinofanirwa kungodzosera zvinyorwa kuti zvicheneswe (variable t) muchimiro chayo chekupedzisira.

Recursive iteration yemasero

Saizvozvo, iwe unogona kuita inodzokororwa kuverenga kwemasero mune yakapihwa renji. Ngatitii tinoda kugadzira basa re lambda rakanzi REPLACEMENT LIST kutsiva nehorese yezvidimbu muzvinyorwa zvekwakabva maererano nereferensi rakapihwa. Mhedzisiro inofanira kutaridzika seizvi:

Avo. pamutambo wedu REPLACEMENT LIST pachava nenharo nhatu:

sero rine mavara ekugadzirisa (kunobva kero)
iyo yekutanga sero yekoramu ine kukosha kwekutsvaga kubva pakutsvaga
iyo yekutanga sero yekoramu ine kutsiva kukosha kubva pakutarisa

Basa racho rinofanira kubva kumusoro kusvika kuzasi mudhairekitori uye kutsiva zvakatevedzana zvese sarudzo kubva kuruboshwe kuruboshwe Kuwana kune dzinoenderana sarudzo kubva pakona yekurudyi Substitute. Iwe unogona kuita izvi neinotevera recursive lambda basa:

Pano, mutsauko t unochengeta zvinyorwa zvepakutanga kubva pane inotevera column cell adhiresi, uye mavara n na z anonongedza kumasero ekutanga mumakoramu Kuwana и Substitute, maererano.

Semumuenzaniso wapfuura, basa iri rinotanga ratsiva chinyorwa chepakutanga nechishandiso SUBSTITUTE (SUBSTITUTE) data pamutsara wekutanga wedhairekitori (kureva SPbon St. Petersburg), uye yobva yazvidaidza pachayo, asi nekuchinja mune dhairekitori kudzika kune inotevera mutsara (kureva inotsiva St. Petersburg on St. Petersburg) Wobva wazvidaidza zvakare nekuchinja pasi - uye kutsiva iyo yatove Peter on St. Petersburg etc.

Shift pasi pane imwe neimwe iteration inoshandiswa neyakajairwa excel basa KURASA (OFFSET), iyo munyaya iyi ine nharo nhatu - mutsara wepakutanga, row shift (1) uye column shift (0).

Zvakanaka, kana tangosvika kumagumo edhairekitori (n = ""), isu tinofanirwa kupedzisa kudzokororwa - isu tinomira kuzvidaidza uye kuratidza izvo zvakaunganidzwa mushure mekutsiviwa kwese mune kwakabva mavara akasiyana t.

Ndizvo zvose. Hapana anonyengera macros kana Power Query mibvunzo - iro basa rese rinogadziriswa nebasa rimwe chete.

Maitiro ekushandisa eExcel's matsva ane simba array mabasa: FILTER, SORT, UNIC
Kutsiva uye kuchenesa zvinyorwa neiyo SUBSTITUTE basa
Kugadzira macros uye mushandisi-anotsanangurwa mabasa (UDFs) muVBA