Cynnwys
Ar hyn o bryd, mae gan Microsoft Excel bron i bum cant o swyddogaethau taflen waith ar gael trwy'r ffenestr Function Wizard - botwm fx yn y bar fformiwla. Mae hon yn set weddus iawn, ond, serch hynny, yn hwyr neu'n hwyrach mae bron pob defnyddiwr yn dod ar draws sefyllfa lle nad yw'r rhestr hon yn cynnwys y swyddogaeth sydd ei hangen arno - yn syml oherwydd nad yw yn Excel.
Hyd yn hyn, yr unig ffordd i ddatrys y broblem hon oedd macros, hy ysgrifennu eich swyddogaeth ddiffiniedig defnyddiwr eich hun (UDF = Swyddogaeth Diffiniedig Defnyddiwr) yn Visual Basic, sy'n gofyn am sgiliau rhaglennu priodol ac, ar adegau, nid yw'n hawdd o gwbl. Fodd bynnag, gyda'r diweddariadau Office 365 diweddaraf, mae'r sefyllfa wedi newid er gwell - mae swyddogaeth “lapiwr” arbennig wedi'i hychwanegu at Excel LAMBDA. Gyda'i help, mae'r dasg o greu eich swyddogaethau eich hun bellach yn cael ei datrys yn hawdd ac yn hyfryd.
Gadewch i ni edrych ar yr egwyddor o'i ddefnydd yn yr enghraifft ganlynol.
Fel y gwyddoch fwyaf tebygol, mae gan Excel sawl swyddogaeth dosrannu dyddiad sy'n eich galluogi i bennu nifer y diwrnod, y mis, yr wythnos a'r flwyddyn ar gyfer dyddiad penodol. Ond am ryw reswm nid oes swyddogaeth sy'n pennu nifer y chwarter, sydd hefyd ei angen yn aml, iawn? Gadewch i ni drwsio'r diffyg hwn a chreu gydag ef LAMBDA bod yn berchen ar swyddogaeth newydd i ddatrys y broblem hon.
Cam 1. Ysgrifennwch y fformiwla
Dechreuwn gyda'r ffaith y byddwn â llaw yn y ffordd arferol yn ysgrifennu fformiwla mewn cell ddalen sy'n cyfrifo'r hyn sydd ei angen arnom. Yn achos y rhif chwarter, gellir gwneud hyn, er enghraifft, fel hyn:
Cam 2. Lapio i fyny yn LAMBDA a phrofi
Nawr mae'n bryd cymhwyso'r swyddogaeth LAMBDA newydd a lapio ein fformiwla ynddi. Mae cystrawen y swyddogaeth fel a ganlyn:
=LAMBDA(Newidyn1; Newidyn2; … NewidynN ; Mynegiant)
lle mae enwau un neu fwy o newidynnau yn cael eu rhestru yn gyntaf, a bod y ddadl olaf bob amser yn fformiwla neu'n fynegiant cyfrifedig sy'n eu defnyddio. Ni ddylai enwau newidiol edrych fel cyfeiriadau cell ac ni ddylent gynnwys dotiau.
Yn ein hachos ni, dim ond un newidyn fydd – y dyddiad y byddwn yn cyfrifo’r rhif chwarter ar ei gyfer. Gadewch i ni alw'r newidyn ar ei gyfer, dyweder, d. Yna lapio ein fformiwla mewn swyddogaeth LAMBDA a rhoi enw newidyn ffuglen yn lle cyfeiriad y gell wreiddiol A2, cawn:
Sylwch, ar ôl trawsnewidiad o'r fath, bod ein fformiwla (yn wir, yn gywir!) Wedi dechrau cynhyrchu gwall, oherwydd nawr nid yw'r dyddiad gwreiddiol o gell A2 yn cael ei drosglwyddo iddo. Ar gyfer profi a hyder, gallwch drosglwyddo dadleuon iddo trwy eu hychwanegu ar ôl y swyddogaeth LAMBDA mewn cromfachau:
Cam 3. Creu enw
Nawr am y rhan hawdd a hwyliog. Rydym yn agor Rheolwr Enw tab fformiwla (Fformiwlâu - Rheolwr Enw) a chreu enw newydd gyda'r botwm Creu (Creu). Lluniwch a nodwch enw ar gyfer ein swyddogaeth yn y dyfodol (er enghraifft, Nomkvartala), ac yn y maes Cyswllt (Cyfeirnod) copïwch yn ofalus o'r bar fformiwla a gludwch ein swyddogaeth LAMBDA, dim ond heb y ddadl olaf (A2):
Popeth. Ar ôl clicio ar OK gellir defnyddio'r swyddogaeth a grëwyd mewn unrhyw gell ar unrhyw ddalen o'r llyfr gwaith hwn:
Defnyddiwch mewn llyfrau eraill
LAMBDA ac araeau deinamig
Swyddogaethau personol wedi'u creu gyda swyddogaeth LAMBDA cefnogi gwaith yn llwyddiannus gydag araeau deinamig newydd a'u swyddogaethau (Hidlo, UNIK, GRADD) ychwanegu at Microsoft Excel yn 2020.
Gadewch i ni ddweud ein bod am greu swyddogaeth newydd wedi'i diffinio gan ddefnyddwyr a fyddai'n cymharu dwy restr ac yn dychwelyd y gwahaniaeth rhyngddynt - yr elfennau hynny o'r rhestr gyntaf nad ydynt yn yr ail. Gwaith bywyd, ynte? Yn flaenorol, ar gyfer hyn roedden nhw'n defnyddio'r naill swyddogaeth a la VPR (VLOOKUP), neu PivotTables, neu ymholiadau Power Query. Nawr gallwch chi wneud gydag un fformiwla:
Yn y fersiwn Saesneg bydd yn:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Yma y swyddogaeth COUNTIF yn cyfrif nifer y digwyddiadau o bob elfen o'r rhestr gyntaf yn yr ail, ac yna'r ffwythiant Hidlo yn dewis dim ond y rhai nad oedd ganddynt y digwyddiadau hyn. Trwy lapio'r strwythur hwn i mewn LAMBDA a chreu ystod a enwir yn seiliedig arno gydag enw, er enghraifft, DOSBARTHIAD CHWILIAD - byddwn yn cael swyddogaeth gyfleus sy'n dychwelyd canlyniad cymharu dwy restr ar ffurf arae ddeinamig:
Os nad yw'r data ffynhonnell yn dablau cyffredin, ond “clyfar”, bydd ein swyddogaeth hefyd yn ymdopi heb broblemau:
Enghraifft arall yw hollti testun yn ddeinamig trwy ei drosi i XML ac yna ei ddosrannu fesul cell gan ddefnyddio'r swyddogaeth FILTER.XML a dosrannwyd gennym yn ddiweddar. Er mwyn peidio ag atgynhyrchu'r fformiwla gymhleth hon â llaw bob tro, bydd yn haws ei lapio yn LAMBDA a chreu ystod ddeinamig yn seiliedig arno, hy swyddogaeth gryno a chyfleus newydd, gan ei enwi, er enghraifft, RAZDTEXT:
Arg gyntaf y swyddogaeth hon fydd y gell gyda'r testun ffynhonnell, a'r ail - y nod gwahanydd, a bydd yn dychwelyd y canlyniad ar ffurf arae deinamig llorweddol. Bydd y cod swyddogaeth fel a ganlyn:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML("
Mae'r rhestr o enghreifftiau yn ddiddiwedd - mewn unrhyw sefyllfa lle mae'n rhaid i chi nodi'r un fformiwla hir a beichus yn aml, bydd swyddogaeth LAMBDA yn gwneud bywyd yn amlwg yn haws.
Rhifo nodau dro ar ôl tro
Mae pob enghraifft flaenorol wedi dangos dim ond un ochr, yr ochr amlycaf, o swyddogaeth LAMBDA - ei ddefnydd fel “lapiwr” ar gyfer lapio fformiwlâu hir ynddo a symleiddio eu mewnbwn. Mewn gwirionedd, mae gan LAMBDA ochr arall, llawer dyfnach, sy'n ei throi'n iaith raglennu lawn bron.
Y ffaith yw mai nodwedd sylfaenol bwysig o swyddogaethau LAMBDA yw'r gallu i'w rhoi ar waith dychweliad - rhesymeg y cyfrifiadau, pan fydd y swyddogaeth yn y broses gyfrifo yn galw ei hun. O'r arfer, gall swnio'n iasol, ond mewn rhaglennu, mae ailadrodd yn beth cyffredin. Hyd yn oed mewn macros yn Visual Basic, gallwch ei weithredu, ac yn awr, fel y gwelwch, mae wedi dod i Excel. Gadewch i ni geisio deall y dechneg hon gydag enghraifft ymarferol.
Tybiwch ein bod am greu swyddogaeth a ddiffinnir gan y defnyddiwr a fyddai'n dileu'r holl nodau a roddir o'r testun ffynhonnell. Nid oes angen i chi brofi pa mor ddefnyddiol yw swyddogaeth o'r fath - byddai'n gyfleus iawn clirio data mewnbwn sbwriel gyda'i help, iawn?
Fodd bynnag, o gymharu â'r enghreifftiau blaenorol, nad ydynt yn ailadroddus, mae dau anhawster yn ein disgwyl.
- Bydd yn rhaid i ni ddod o hyd i enw ar gyfer ein swyddogaeth cyn i ni ddechrau ysgrifennu ei god, oherwydd ynddo, bydd yr enw hwn eisoes yn cael ei ddefnyddio i alw'r swyddogaeth ei hun.
- Ni fydd rhoi swyddogaeth ailadroddus o'r fath i mewn i gell a'i dadfygio trwy nodi dadleuon mewn cromfachau ar ôl LAMBDA (fel y gwnaethom yn gynharach) yn gweithio. Bydd yn rhaid i chi greu swyddogaeth ar unwaith “o'r dechrau” i mewn Rheolwr Enw (Rheolwr Enw).
Gadewch i ni alw ein swyddogaeth, dyweder, GLAN a hoffem iddo gael dwy ddadl - y testun i'w lanhau a'r rhestr o nodau eithriedig fel llinyn testun:
Gadewch i ni greu, fel y gwnaethom yn gynharach, ar y tab fformiwla в Rheolwr enw ystod a enwir, ei enwi CLEAR a nodi yn y maes Ystod adeiladu canlynol:
=LAMBDA(t; d; IF(d =”";t; CLEAR(SUBSTITUTE(t; CHWITH(d);"”);MID(d; 2; 255))))
Yma y newidyn t yw'r testun gwreiddiol i'w glirio, a d yw'r rhestr o nodau i'w dileu.
Mae'r cyfan yn gweithio fel hyn:
Newid 1
Mae'r darn SUBSTITUTE(t; LEFT(d);"”), fel y gallech ddyfalu, yn disodli'r nod cyntaf o'r nod chwith o'r set d i'w ddileu yn y testun ffynhonnell t gyda llinyn testun gwag, hy yn tynnu'r “ A”. Fel canlyniad canolradd, rydym yn cael:
Vsh zkz n 125 rubles.
Newid 2
Yna mae'r swyddogaeth yn galw ei hun ac fel mewnbwn (y ddadl gyntaf) yn derbyn yr hyn sy'n weddill ar ôl glanhau yn y cam blaenorol, a'r ail ddadl yw'r llinyn o nodau eithriedig sy'n dechrau nid o'r cyntaf, ond o'r ail gymeriad, hy “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ,” heb yr “A” cychwynnol – gwneir hyn gan y swyddogaeth MID. Fel o'r blaen, mae'r swyddogaeth yn cymryd y nod cyntaf o'r chwith o'r rhai sy'n weddill (B) ac yn ei ddisodli yn y testun a roddir iddo (Zkz n 125 rubles) gyda llinyn gwag - rydym yn cael fel canlyniad canolraddol:
125 ru.
Newid 3
Mae'r swyddogaeth yn galw ei hun eto, gan dderbyn fel y ddadl gyntaf yr hyn sy'n weddill o'r testun i'w glirio yn yr iteriad blaenorol (Bsh zkz n 125 ru.), ac fel yr ail ddadl, y set o nodau eithriedig wedi'u cwtogi gan un nod arall i y chwith, hy “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.,” heb y “B” cychwynnol. Yna eto mae'n cymryd y nod cyntaf o'r chwith (B) o'r set hon ac yn ei dynnu o'r testun - rydyn ni'n cael:
sh zkz n 125 ru.
Ac yn y blaen - gobeithio y cewch chi'r syniad. Gyda phob iteriad, bydd y rhestr o nodau i'w dileu yn cael ei chwtogi ar y chwith, a byddwn yn chwilio am y nod nesaf o'r set ac yn ei ddisodli gyda gwagle.
Pan fydd yr holl nodau'n dod i ben, bydd angen i ni adael y ddolen - dim ond y swyddogaeth sy'n cyflawni'r rôl hon IF (OS), y mae ein dyluniad wedi'i lapio. Os nad oes unrhyw nodau ar ôl i'w dileu (d =”"), yna ni ddylai'r ffwythiant alw'i hun mwyach, ond dylai ddychwelyd y testun i'w glirio (newidyn t) yn ei ffurf derfynol.
iteriad rheolaidd o gelloedd
Yn yr un modd, gallwch chi weithredu rhifiad ailadroddus o gelloedd mewn ystod benodol. Tybiwch ein bod am greu swyddogaeth lambda a enwir RHESTR YMOSODIAD ar gyfer cyfnewid darnau yn y testun ffynhonnell yn gyfan gwbl yn unol â rhestr gyfeirio benodol. Dylai'r canlyniad edrych fel hyn:
Y rhai. yn ein swyddogaeth RHESTR YMOSODIAD bydd tair dadl:
- cell gyda thestun i'w brosesu (cyfeiriad ffynhonnell)
- cell gyntaf colofn gyda gwerthoedd i'w chwilio o'r chwilio
- cell gyntaf y golofn gyda gwerthoedd amnewid o'r lookup
Dylai'r swyddogaeth fynd o'r brig i'r gwaelod yn y cyfeiriadur a disodli'r holl opsiynau o'r golofn chwith yn olynol i ddod o hyd i'r opsiynau cyfatebol o'r golofn dde Dirprwy. Gallwch chi weithredu hyn gyda'r swyddogaeth lambda ailadroddus ganlynol:
Gweithredir y newid i lawr ar bob iteriad gan swyddogaeth excel safonol GWAREDU (Gwrthosod), sydd â thair dadl yn yr achos hwn - yr amrediad gwreiddiol, shifft rhes (1) a shifft colofn (0).
Wel, cyn gynted ag y byddwn yn cyrraedd diwedd y cyfeiriadur (n = “”), mae'n rhaid i ni ddod â'r ailadrodd i ben - rydyn ni'n rhoi'r gorau i alw ein hunain ac yn arddangos yr hyn sydd wedi cronni ar ôl yr holl ailosodiadau yn y newidyn testun ffynhonnell t.
Dyna i gyd. Dim macros anodd nac ymholiadau Power Query - mae'r dasg gyfan yn cael ei datrys gan un swyddogaeth.
- Sut i ddefnyddio swyddogaethau arae ddeinamig newydd Excel: FILTER, SORT, UNIC
- Amnewid a glanhau testun gyda'r swyddogaeth SUBSTITUTE
- Creu macros a swyddogaethau a ddiffinnir gan ddefnyddwyr (UDFs) yn VBA