Efnisyfirlit
Í augnablikinu er Microsoft Excel með næstum fimm hundruð vinnublaðsaðgerðir tiltækar í gegnum Function Wizard gluggann – hnappinn fx í formúlustikunni. Þetta er mjög þokkalegt sett, en engu að síður lendir næstum sérhver notandi fyrr eða síðar í aðstæðum þar sem þessi listi inniheldur ekki þá aðgerð sem hann þarf – einfaldlega vegna þess að hann er ekki í Excel.
Hingað til hefur eina leiðin til að leysa þetta vandamál verið fjölva, þ.e. að skrifa eigin notendaskilgreinda aðgerð (UDF = User Defined Function) í Visual Basic, sem krefst viðeigandi forritunarkunnáttu og er stundum alls ekki auðvelt. Hins vegar, með nýjustu Office 365 uppfærslunum, hefur ástandið breyst til hins betra - sérstakri „umbúðir“ aðgerð hefur verið bætt við Excel LAMBDA. Með hjálp þess er verkefnið að búa til þínar eigin aðgerðir nú leyst auðveldlega og fallega.
Við skulum skoða meginregluna um notkun þess í eftirfarandi dæmi.
Eins og þú veist líklegast hefur Excel nokkrar dagsetningarþáttunaraðgerðir sem gera þér kleift að ákvarða fjölda dags, mánaðar, viku og árs fyrir tiltekna dagsetningu. En af einhverjum ástæðum er engin aðgerð sem ákvarðar fjölda fjórðungsins, sem er líka oft þörf, ekki satt? Við skulum laga þennan galla og búa til með LAMBDA eigin nýja aðgerð til að leysa þetta vandamál.
Skref 1. Skrifaðu formúluna
Við skulum byrja á því að handvirkt á venjulegan hátt munum við skrifa formúlu í blaðreit sem reiknar út hvað við þurfum. Þegar um er að ræða ársfjórðungstölu er þetta til dæmis hægt að gera svona:
Skref 2. Pakkið inn í LAMBDA og prófun
Nú er kominn tími til að nota nýju LAMBDA aðgerðina og pakka formúlunni okkar inn í hana. Setningafræði fallsins er sem hér segir:
=LAMBDA(Breyta 1; Breyta 2; ... VariableN ; Tjáning)
þar sem nöfn einnar eða fleiri breyta eru skráð fyrst og síðasta röksemdin er alltaf formúla eða útreiknuð tjáning sem notar þær. Breytuheiti ættu ekki að líta út eins og frumuföng og ættu ekki að innihalda punkta.
Í okkar tilviki verður aðeins ein breyta - dagsetningin sem við reiknum fjórðungstöluna fyrir. Köllum breytuna fyrir hana, segjum d. Vefjum síðan formúlunni okkar inn í fall LAMBDA og með því að skipta út heimilisfangi upprunalega reitsins A2 fyrir gervibreytuheiti fáum við:
Vinsamlegast athugaðu að eftir slíka umbreytingu byrjaði formúlan okkar (reyndar rétt!) að framleiða villu, því nú er upprunalega dagsetningin frá reit A2 ekki flutt til hennar. Fyrir prófun og sjálfstraust geturðu sent rök til þess með því að bæta þeim við á eftir fallinu LAMBDA innan sviga:
Skref 3. Búðu til nafn
Nú að auðvelda og skemmtilega hlutanum. Við opnum Nafnastjóri flipi uppskrift (Formúlur - Nafnastjóri) og búðu til nýtt nafn með hnappinum Búa til (Búa til). Komdu með og sláðu inn nafn fyrir framtíðaraðgerðina okkar (til dæmis, Nomkvartala), og á sviði Link (Tilvísun) afritaðu vandlega af formúlustikunni og límdu fallið okkar LAMBDA, aðeins án síðustu röksemda (A2):
Allt. Eftir að hafa smellt á OK hægt er að nota búið til aðgerð í hvaða reit sem er á hvaða blaði sem er í þessari vinnubók:
Notaðu í öðrum bókum
LAMBDA og kraftmikil fylki
Sérsniðnar aðgerðir búnar til með aðgerð LAMBDA með góðum árangri að styðja við vinnu með nýjum kraftmiklum fylkjum og virkni þeirra (FILTER, UNIK, GRADE) bætt við Microsoft Excel árið 2020.
Segjum að við viljum búa til nýja notendaskilgreinda aðgerð sem myndi bera saman tvo lista og skila mismuninum á milli þeirra - þessir þættir úr fyrsta listanum sem eru ekki í þeim seinni. Lífsstarfið, er það ekki? Áður notuðu þeir annaðhvort aðgerðir a la VPR (SKRÁNING), eða PivotTables, eða Power Query fyrirspurnir. Nú geturðu gert með einni formúlu:
Í ensku útgáfunni verður það:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Hér er aðgerðin COUNTIF telur fjölda tilvika hvers þáttar í fyrsta listanum í þeim seinni, og síðan fallsins FILTER velur aðeins þá þeirra sem ekki áttu þessa uppákomu. Með því að pakka þessari uppbyggingu inn í LAMBDA og búa til nefnt svið byggt á því með nafni, til dæmis, DREIFING LEITAR – við fáum þægilega aðgerð sem skilar niðurstöðu af samanburði á tveimur listum í formi kraftmikils fylkis:
Ef upprunagögnin eru ekki venjuleg, heldur „snjöll“ töflur, mun aðgerðin okkar einnig takast á við án vandræða:
Annað dæmi er að skipta texta á virkan hátt með því að breyta honum í XML og flokka hann síðan reit fyrir reit með því að nota FILTER.XML aðgerðina sem við þáttuðum nýlega. Til þess að endurskapa ekki þessa flóknu formúlu handvirkt í hvert skipti, verður auðveldara að pakka henni inn í LAMBDA og búa til kraftmikið svið byggt á því, þ.e. nýja fyrirferðarlítið og þægilegt hlutverk, sem nefnir hana td RAZDTEXT:
Fyrstu rökin í þessari aðgerð verða reitinn með frumtextanum og sá síðari - skiljustafurinn, og hann mun skila niðurstöðunni í formi lárétts kraftmikils fylkis. Aðgerðarkóði verður sem hér segir:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Listinn af dæmum er endalaus - í öllum aðstæðum þar sem þú þarft oft að slá inn sömu löngu og fyrirferðarmiklu formúluna mun LAMBDA aðgerðin gera lífið áberandi auðveldara.
Endurkvæm upptalning á stöfum
Öll fyrri dæmi hafa aðeins sýnt eina, augljósustu hliðina á LAMBDA aðgerðinni - notkun þess sem „umbúðir“ til að vefja langar formúlur inn í hana og einfalda inntak þeirra. Reyndar hefur LAMBDA aðra, miklu dýpri hlið sem gerir það að nánast fullgildu forritunarmáli.
Staðreyndin er sú að grundvallaratriðið í LAMBDA aðgerðum er hæfileikinn til að innleiða þær í endurkoma – rökfræði útreikninga, þegar í útreikningsferlinu kallar fallið sig. Frá vana, það kann að hljóma hrollvekjandi, en í forritun er endurtekning algengur hlutur. Jafnvel í fjölvi í Visual Basic geturðu útfært það og nú, eins og þú sérð, er það komið í Excel. Við skulum reyna að skilja þessa tækni með hagnýtu dæmi.
Segjum að við viljum búa til notendaskilgreinda aðgerð sem myndi fjarlægja alla tiltekna stafi úr frumtextanum. Gagnsemi slíkrar aðgerð, held ég, þú þarft ekki að sanna - það væri mjög þægilegt að hreinsa rusl inntaksgögn með hjálp þess, ekki satt?
Hins vegar, samanborið við fyrri, óendurkvæma dæmin, bíða okkar tveir erfiðleikar.
- Við verðum að finna upp nafn á fallið okkar áður en við byrjum að skrifa kóðann þess, því í því verður þetta nafn þegar notað til að kalla fallið sjálft.
- Það virkar ekki að slá inn slíkt endurkvæmt fall inn í frumu og villuleit með því að tilgreina rök í sviga á eftir LAMBDA (eins og við gerðum áðan). Þú verður að búa til aðgerð strax „frá grunni“ inn Nafnastjóri (Nafnastjóri).
Við skulum kalla fallið okkar, segjum, CLEAN og við viljum að það hafi tvö rök – textinn sem á að hreinsa og listinn yfir útilokaða stafi sem textastreng:
Við skulum búa til, eins og við gerðum áðan, á flipanum uppskrift в Nafnastjóri nefnt svið, nefndu það Hreinsa og sláðu inn í reitinn Range eftirfarandi byggingu:
=LAMBDA(t;d;IF(d=““;t;Hreinsa(STAÐARI(t;VINSTRI(d);““);MID(d;2;255))))
Hér er breytan t upprunalega textinn sem á að hreinsa og d er listi yfir stafi sem á að eyða.
Þetta virkar allt svona:
Ítrekun 1
Brotið SUBSTITUTE(t;LEFT(d);”“), eins og þú gætir giska á, kemur í stað fyrsta stafsins úr vinstri stafnum úr menginu d sem á að eyða í frumtextanum t fyrir tóman textastreng, þ.e. fjarlægir „ A”. Sem milliniðurstaða fáum við:
Vsh zkz n 125 rúblur.
Ítrekun 2
Þá kallar fallið á sig og sem inntak (fyrsta viðfangið) fær það sem er eftir eftir hreinsun í fyrra skrefi, og önnur viðfangið er strengur af útilokuðum stöfum sem byrja ekki frá fyrsta, heldur frá seinni stafnum, þ.e. „BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ," án upphafs "A" - þetta er gert með MID aðgerðinni. Eins og áður tekur aðgerðin fyrsta stafinn frá vinstri af þeim sem eftir eru (B) og kemur í stað hans í textanum sem honum er gefinn (Zkz n 125 rúblur) fyrir tóman streng - við fáum sem milliniðurstöðu:
125 kr.
Ítrekun 3
Fallið kallar sig aftur, tekur við sem fyrstu röksemd það sem er eftir af textanum sem á að hreinsa við fyrri endurtekningu (Bsh zkz n 125 ru.), Og sem seinni röksemdin er mengið af útilokuðum stöfum stytt af einum staf í viðbót til að til vinstri, þ.e. „VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.,“ án upphafsstafs „B“. Síðan tekur það aftur fyrsta stafinn frá vinstri (B) úr þessu setti og fjarlægir hann úr textanum - við fáum:
sh zkz n 125 ru.
Og svo framvegis - ég vona að þú skiljir hugmyndina. Við hverja endurtekningu verður listi yfir stafi sem á að fjarlægja stytt til vinstri og við munum leita að og skipta út næsta staf úr settinu með tómi.
Þegar allar persónurnar klárast verðum við að fara út úr lykkjunni - þetta hlutverk er bara framkvæmt af aðgerðinni IF (EF), þar sem hönnun okkar er vafin. Ef engir stafir eru eftir til að eyða (d=””), þá ætti aðgerðin ekki lengur að kalla sig, heldur ætti hún einfaldlega að skila textanum sem á að hreinsa (breytu t) í endanlegri mynd.
Endurkvæm endurtekning frumna
Á sama hátt er hægt að útfæra endurkvæma upptalningu á frumum á tilteknu sviði. Segjum að við viljum búa til lambda fall sem heitir SKIPTALIsti til heildsöluskipta á brotum í frumtexta samkvæmt tilteknum heimildalista. Útkoman ætti að líta svona út:
Þeir. á athöfn okkar SKIPTALIsti það verða þrjú rök:
- klefi með texta til að vinna úr (heimildarfang)
- fyrsta reitinn í dálki með gildum til að leita úr leitinni
- fyrsta reit dálksins með skiptigildum úr leitinni
Aðgerðin ætti að fara frá toppi til botns í möppunni og koma í stað allra valkosta í vinstri dálki í röð Að finna til samsvarandi valkosta úr hægri dálki Staðgengill. Þú getur útfært þetta með eftirfarandi endurkvæma lambda aðgerð:
Niðurfærsla við hverja endurtekningu er útfærð með venjulegri Excel aðgerð FÖRGUN (FRÆÐI), sem í þessu tilfelli hefur þrjú rök - upprunalega svið, röð tilfærslu (1) og dálkbreyting (0).
Jæja, um leið og við náum endalokum möppunnar (n = “”), verðum við að binda enda á endurtekninguna – við hættum að kalla okkur og birtum það sem safnast hefur upp eftir allar skiptin í frumtextabreytunni t.
Það er allt og sumt. Engar erfiðar fjölvi eða Power Query fyrirspurnir - allt verkefnið er leyst með einni aðgerð.
- Hvernig á að nota nýja dynamic array aðgerðir Excel: SÍA, RÖÐA, UNIC
- Skipta út og hreinsa upp texta með SUBSTITUTE aðgerðinni
- Búa til fjölvi og notendaskilgreindar aðgerðir (UDF) í VBA