Turinys
Šiuo metu „Microsoft Excel“ turi beveik penkis šimtus darbalapio funkcijų, pasiekiamų per funkcijų vedlio langą – mygtuką fx formulės juostoje. Tai labai padorus rinkinys, tačiau, nepaisant to, beveik kiekvienas vartotojas anksčiau ar vėliau susiduria su situacija, kai šiame sąraše nėra jam reikalingos funkcijos – tiesiog todėl, kad jos nėra „Excel“.
Iki šiol vienintelis būdas išspręsti šią problemą buvo makrokomandos, ty savo vartotojo nustatytos funkcijos (UDF = User Defined Function) rašymas programoje Visual Basic, kuris reikalauja atitinkamų programavimo įgūdžių ir kartais nėra lengvas. Tačiau su naujausiais „Office 365“ atnaujinimais situacija pasikeitė į gerąją pusę – „Excel“ papildyta specialia „wrapper“ funkcija. LAMBDA. Su jo pagalba dabar lengvai ir gražiai išsprendžiama užduotis susikurti savo funkcijas.
Pažvelkime į jo naudojimo principą kitame pavyzdyje.
Kaip tikriausiai žinote, „Excel“ turi keletą datos analizės funkcijų, kurios leidžia nustatyti tam tikros datos dienos, mėnesio, savaitės ir metų skaičių. Bet kažkodėl nėra kvartalo skaičių nustatančios funkcijos, kurios taip pat dažnai prireikia, tiesa? Ištaisykime šį trūkumą ir kurkime su LAMBDA savo naują funkciją šiai problemai išspręsti.
1 veiksmas. Parašykite formulę
Pradėkime nuo to, kad rankiniu būdu įprastu būdu lapo langelyje parašysime formulę, kuri apskaičiuoja tai, ko mums reikia. Ketvirčio numerio atveju tai galima padaryti, pavyzdžiui, taip:
2 veiksmas. Įvyniojimas į LAMBDA ir bandymas
Dabar atėjo laikas pritaikyti naują LAMBDA funkciją ir į ją įtraukti mūsų formulę. Funkcijos sintaksė yra tokia:
=LAMBDA(Kintamasis1; Kintamasis2; … KintamasisN ; Išraiška)
kur pirmiausia pateikiami vieno ar kelių kintamųjų pavadinimai, o paskutinis argumentas visada yra juos naudojanti formulė arba apskaičiuota išraiška. Kintamųjų pavadinimai neturėtų atrodyti kaip langelių adresai ir juose neturėtų būti taškų.
Mūsų atveju bus tik vienas kintamasis – data, kuriai skaičiuosime ketvirčio numerį. Pavadinkime jo kintamąjį, tarkime, d. Tada apvyniokite mūsų formulę į funkciją LAMBDA ir pakeitę pradinio langelio A2 adresą išgalvotu kintamojo pavadinimu, gauname:
Atkreipkite dėmesį, kad po tokios transformacijos mūsų formulė (tiesą sakant, teisinga!) pradėjo kelti klaidą, nes dabar pradinė data iš langelio A2 į ją neperkelta. Norėdami patikrinti ir pasitikėti, galite perduoti argumentus pridėdami juos po funkcijos LAMBDA skliausteliuose:
3 veiksmas. Sukurkite vardą
Dabar lengva ir linksma dalis. Atsidarome Vardų tvarkytuvė kortelė formulė (Formulės – vardų tvarkyklė) ir sukurkite naują pavadinimą mygtuku kurti (Sukurti). Sugalvokite ir įveskite būsimos funkcijos pavadinimą (pvz., Nomkvartala), ir lauke ryšys (Nuoroda) atsargiai nukopijuokite iš formulės juostos ir įklijuokite mūsų funkciją LAMBDA, tik be paskutinio argumento (A2):
Viskas. Paspaudus ant OK sukurta funkcija gali būti naudojama bet kuriame šios darbaknygės lapo langelyje:
Naudoti kitose knygose
LAMBDA ir dinaminiai masyvai
Pasirinktinės funkcijos, sukurtos naudojant funkciją LAMBDA sėkmingai palaiko darbą su naujais dinaminiais masyvais ir jų funkcijomis (FILTRAS, UNIKALUS, GRADE) įtraukta į „Microsoft Excel“ 2020 m.
Tarkime, kad norime sukurti naują vartotojo apibrėžtą funkciją, kuri palygintų du sąrašus ir grąžintų skirtumą tarp jų – tuos pirmojo sąrašo elementus, kurių nėra antrame. Gyvenimo darbas, ar ne? Anksčiau tam jie naudojo bet kurias funkcijas a la VPR (ŽIŪRĖTI), arba PivotTables arba Power Query užklausas. Dabar galite tai padaryti naudodami vieną formulę:
Angliškoje versijoje tai bus:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Čia funkcija COUNTIF skaičiuoja kiekvieno pirmojo sąrašo elemento pasikartojimų skaičių antrajame, o paskui funkciją FILTRAS atrenka tik tuos, kurie neturėjo šių įvykių. Įvyniojus šią struktūrą LAMBDA ir pagal jį sukurti pavadintą diapazoną su pavadinimu, pavyzdžiui, PAIEŠKOS PLATINIMAS – gausime patogią funkciją, kuri grąžina dviejų sąrašų palyginimo rezultatą dinaminio masyvo pavidalu:
Jei šaltinio duomenys yra ne įprastos, o „protingos“ lentelės, mūsų funkcija taip pat susidoros be problemų:
Kitas pavyzdys – dinamiškas teksto skaidymas konvertuojant jį į XML, o tada analizuojant langelį po langelio naudojant FILTER.XML funkciją, kurią neseniai analizavome. Kad šios sudėtingos formulės nereikėtų kaskart atkartoti rankiniu būdu, bus lengviau ją įvynioti į LAMBDA ir pagal ją sukurti dinaminį diapazoną, ty naują kompaktišką ir patogią funkciją, pavadinant ją, pavyzdžiui, RAZDTEXT:
Pirmasis šios funkcijos argumentas bus langelis su šaltinio tekstu, o antrasis – skyriklio simbolis ir jis pateiks rezultatą horizontalaus dinaminio masyvo pavidalu. Funkcijos kodas bus toks:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Pavyzdžių sąrašas begalinis – bet kokioje situacijoje, kai dažnai tenka įvesti tą pačią ilgą ir gremėzdišką formulę, LAMBDA funkcija žymiai palengvins gyvenimą.
Rekursyvus simbolių išvardijimas
Visi ankstesni pavyzdžiai parodė tik vieną, ryškiausią, LAMBDA funkcijos pusę – jos naudojimą kaip „įvyniojimą“ ilgoms formulėms įvynioti ir jų įvedimui supaprastinti. Tiesą sakant, LAMBDA turi kitą, daug gilesnę pusę, kuri paverčia ją beveik visaverte programavimo kalba.
Faktas yra tas, kad iš esmės svarbi LAMBDA funkcijų savybė yra galimybė jas įdiegti rekursija – skaičiavimų logika, kai skaičiavimo procese funkcija išsikviečia pati save. Iš įpročio tai gali atrodyti siaubingai, tačiau programuojant rekursija yra įprastas dalykas. Netgi „Visual Basic“ makrokomandose galite tai įdiegti, o dabar, kaip matote, ji atėjo į „Excel“. Pabandykime suprasti šią techniką praktiniu pavyzdžiu.
Tarkime, kad norime sukurti vartotojo apibrėžtą funkciją, kuri pašalintų visus nurodytus simbolius iš šaltinio teksto. Tokios funkcijos naudingumo, manau, nereikia įrodinėti – jos pagalba būtų labai patogu išvalyti šiukšlintus įvesties duomenis, tiesa?
Tačiau, palyginti su ankstesniais, nerekursiniais pavyzdžiais, mūsų laukia du sunkumai.
- Prieš pradėdami rašyti jos kodą, turėsime sugalvoti savo funkcijos pavadinimą, nes joje šis pavadinimas jau bus naudojamas pačiai funkcijai iškviesti.
- Tokios rekursinės funkcijos įvedimas į langelį ir jos derinimas nurodant argumentus skliausteliuose po LAMBDA (kaip darėme anksčiau) neveiks. Turėsite iš karto sukurti funkciją „nuo nulio“. Vardų tvarkytuvė (Vardų vadybininkas).
Pavadinkime savo funkciją, tarkime, CLEAN ir norėtume, kad ji turėtų du argumentus – valomą tekstą ir neįtrauktų simbolių sąrašą kaip teksto eilutę:
Kurkime, kaip darėme anksčiau, skirtuke formulė в Vardo vadybininkas pavadintas diapazonas, pavadinkite jį CLEAR ir įveskite lauką Diapazonas tokia konstrukcija:
=LAMBDA(t;d;IF(d="";t;CLEAR)(PAKEITIMAS(t;LEFT(d);"");VIDURI(d;2;255))))
Čia kintamasis t yra pradinis tekstas, kurį reikia išvalyti, o d yra simbolių, kuriuos reikia ištrinti, sąrašas.
Viskas veikia taip:
1 kartojimas
Fragmentas SUBSTITUTE(t;LEFT(d);""), kaip galite spėti, pakeičia pirmąjį simbolį iš kairiojo simbolio iš rinkinio d, kurį reikia ištrinti šaltinio tekste t tuščia teksto eilute, ty pašalina " A“. Kaip tarpinį rezultatą gauname:
Vsh zkz n 125 rubliai.
2 kartojimas
Tada funkcija išsikviečia save ir kaip įvestį (pirmasis argumentas) gauna tai, kas liko po valymo ankstesniame žingsnyje, o antrasis argumentas yra neįtrauktų simbolių eilutė, prasidedanti ne nuo pirmojo, o nuo antrojo simbolio, ty „BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ”, be pradinio „A“ – tai atlieka MID funkcija. Kaip ir anksčiau, funkcija paima pirmąjį simbolį iš kairės iš likusių (B) ir jai pateiktame tekste (Zkz n 125 rubliai) pakeičia tuščia eilute – gauname kaip tarpinį rezultatą:
125 rubliai.
3 kartojimas
Funkcija vėl išsikviečia save, kaip pirmąjį argumentą gaudama tai, kas liko iš teksto, kurį reikia išvalyti per ankstesnę iteraciją (Bsh zkz n 125 ru.), o kaip antrą argumentą – neįtrauktų simbolių rinkinį, sutrumpintą dar vienu simboliu. kairėje, ty „VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.“, be pradinio „B“. Tada vėl paima pirmąjį simbolį iš kairės (B) iš šio rinkinio ir pašalina jį iš teksto – gauname:
sh zkz n 125 ru.
Ir taip toliau – tikiuosi supratai mintį. Su kiekviena iteracija šalinamų simbolių sąrašas kairėje bus sutrumpintas, o kitą aibės simbolį ieškosime ir pakeisime tuštuma.
Kai baigsis visi simboliai, turėsime išeiti iš kilpos – šį vaidmenį tiesiog atlieka funkcija IF (JEI), kuriame įvyniotas mūsų dizainas. Jei nebeliko ištrintinų simbolių (d=""), tada funkcija nebeturėtų iškviesti savęs, o tiesiog grąžinti valomą tekstą (kintamąjį t) galutine forma.
Rekursinė ląstelių iteracija
Panašiai galite įdiegti rekursinį ląstelių išvardijimą tam tikrame diapazone. Tarkime, kad norime sukurti lambda funkciją pavadinimu PAKEITIMO SĄRAŠAS už didmeninį šaltinio teksto fragmentų pakeitimą pagal pateiktą nuorodų sąrašą. Rezultatas turėtų atrodyti taip:
Tie. mūsų renginyje PAKEITIMO SĄRAŠAS bus trys argumentai:
- langelis su tekstu, kurį reikia apdoroti (šaltinio adresas)
- pirmasis stulpelio langelis su reikšmėmis, kurių reikia ieškoti iš peržvalgos
- pirmasis stulpelio langelis su pakeitimo reikšmėmis iš peržvalgos
Funkcija turi eiti iš viršaus į apačią kataloge ir paeiliui pakeisti visas kairiojo stulpelio parinktis Rasti į atitinkamas parinktis iš dešiniojo stulpelio Pavaduojantis narys. Tai galite įgyvendinti naudodami šią rekursinę lambda funkciją:
Poslinkis žemyn kiekvienoje iteracijoje įgyvendinamas naudojant standartinę „Excel“ funkciją NAIKINIMAS (OFFSET), kuris šiuo atveju turi tris argumentus – pradinį diapazoną, eilutės poslinkį (1) ir stulpelio poslinkį (0).
Na, o kai tik pasiekiame katalogo pabaigą (n = “”), privalome baigti rekursiją – nebeskambiname patys ir rodome, kas susikaupė po visų keitimų šaltinio teksto kintamajame t.
Tai viskas. Jokių sudėtingų makrokomandų ar Power Query užklausų – visa užduotis išspręsta viena funkcija.
- Kaip naudotis naujomis „Excel“ dinaminio masyvo funkcijomis: FILTER, SORT, UNIC
- Teksto keitimas ir išvalymas naudojant funkciją SUBSTITUTE
- Makrokomandų ir vartotojo nustatytų funkcijų (UDF) kūrimas VBA