Sisu
Hetkel on Microsoft Excelis saadaval ligi viissada töölehe funktsiooni Function Wizard akna – nupu kaudu fx valemiribal. See on väga korralik komplekt, kuid sellegipoolest satub peaaegu iga kasutaja varem või hiljem olukorda, kus see loend ei sisalda talle vajalikku funktsiooni - lihtsalt sellepärast, et seda pole Excelis.
Seni oli probleemi lahendamiseks ainsaks võimaluseks makrod ehk oma kasutaja määratud funktsiooni (UDF = User Defined Function) kirjutamine Visual Basicus, mis nõuab vastavaid programmeerimisoskusi ja pole kohati sugugi lihtne. Viimaste Office 365 uuendustega on aga olukord paremuse poole muutunud – Excelisse on lisatud spetsiaalne “wrapper” funktsioon LAMBDA. Tema abiga lahendatakse nüüd lihtsalt ja kaunilt oma funktsioonide loomise ülesanne.
Vaatame selle kasutamise põhimõtet järgmises näites.
Nagu te tõenäoliselt teate, on Excelil mitu kuupäeva sõelumisfunktsiooni, mis võimaldavad teil määrata päeva, kuu, nädala ja aasta arvu antud kuupäeva jaoks. Aga miskipärast puudub veerandi numbri määrav funktsioon, mida ka tihti vaja läheb, eks? Parandame selle puuduse ja loome koos LAMBDA oma uue funktsiooni selle probleemi lahendamiseks.
Samm 1. Kirjutage valem
Alustame sellest, et käsitsi kirjutame tavalisel viisil lehe lahtrisse valemi, mis arvutab välja, mida vajame. Kvartalinumbri puhul saab seda teha näiteks nii:
2. etapp. LAMBDAsse pakkimine ja testimine
Nüüd on aeg rakendada uut LAMBDA funktsiooni ja mähkida meie valem sellesse. Funktsiooni süntaks on järgmine:
=LAMBDA(Muutuja1; Muutuja2; … MuutujaN ; Väljendus)
kus ühe või mitme muutuja nimed on loetletud esimesena ja viimane argument on alati valem või arvutatud avaldis, mis neid kasutab. Muutujate nimed ei tohiks välja näha nagu lahtri aadressid ega sisaldada punkte.
Meie puhul on ainult üks muutuja – kuupäev, mille kohta kvartalinumbri arvutame. Nimetagem selle muutuja, ütleme, d. Seejärel mähkige meie valem funktsiooniks LAMBDA ja asendades algse lahtri A2 aadressi fiktiivse muutuja nimega, saame:
Pange tähele, et pärast sellist teisendust hakkas meie valem (tegelikult õige!) tootma viga, sest nüüd ei kanta lahtrist A2 algset kuupäeva sellele üle. Testimiseks ja usalduse tagamiseks saate sellele argumente edastada, lisades need pärast funktsiooni LAMBDA sulgudes:
Samm 3. Loo nimi
Nüüd lihtsast ja lõbusast osast. Avame Nimehaldur tab valem (Valemid – nimehaldur) ja looge nupuga uus nimi Looma (Loo). Mõelge välja ja sisestage meie tulevase funktsiooni nimi (näiteks Nomkvartala) ja põllul on siin (Viide) kopeerige hoolikalt valemiribalt ja kleepige meie funktsioon LAMBDA, ainult ilma viimase argumendita (A2):
Kõik. Pärast klõpsamist OK loodud funktsiooni saab kasutada selle töövihiku mis tahes lehe mis tahes lahtris:
Kasuta teistes raamatutes
LAMBDA ja dünaamilised massiivid
Funktsiooniga loodud kohandatud funktsioonid LAMBDA edukalt toetada tööd uute dünaamiliste massiivide ja nende funktsioonidega (FILTER, UNIK, GRADE) lisati Microsoft Excelisse 2020. aastal.
Oletame, et tahame luua uue kasutaja määratud funktsiooni, mis võrdleks kahte loendit ja tagastaks nendevahelise erinevuse – need esimese loendi elemendid, mida teises ei ole. Elutöö, kas pole? Varem kasutati selleks kumbagi funktsiooni a la VPR (OTSING)või PivotTable-liigendtabelid või Power Query päringud. Nüüd saate teha ühe valemiga:
Ingliskeelses versioonis on see:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Siin on funktsioon COUNTIF loendab esimese loendi iga elemendi esinemiste arvu teises ja seejärel funktsiooni FILTER valib neist ainult need, kellel neid juhtumeid ei esinenud. Mähkides selle struktuuri sisse LAMBDA ja selle põhjal nimega vahemiku loomine koos nimega, näiteks OTSING JAOTUS - saame mugava funktsiooni, mis tagastab kahe loendi võrdlemise tulemuse dünaamilise massiivi kujul:
Kui lähteandmeteks pole tavalised, vaid “nutikad” tabelid, saab ka meie funktsioon probleemideta hakkama:
Teine näide on teksti dünaamiline poolitamine, teisendades selle XML-vormingusse ja seejärel sõeludes seda lahtri haaval, kasutades funktsiooni FILTER.XML, mille me hiljuti sõelusime. Et seda keerulist valemit mitte iga kord käsitsi reprodutseerida, on lihtsam seda LAMBDA-sse mähkida ja selle põhjal luua dünaamiline vahemik ehk uus kompaktne ja mugav funktsioon, pannes sellele näiteks nimeks RAZDTEXT:
Selle funktsiooni esimene argument on lähtetekstiga lahter ja teine eraldusmärk ning see tagastab tulemuse horisontaalse dünaamilise massiivi kujul. Funktsiooni kood on järgmine:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Näidete loetelu on lõputu – igas olukorras, kus sageli tuleb sisestada sama pikk ja tülikas valem, teeb LAMBDA funktsioon elu märgatavalt lihtsamaks.
Tähemärkide rekursiivne loendamine
Kõik eelnevad näited on näidanud funktsiooni LAMBDA ainult ühte, kõige ilmsemat külge – selle kasutamist "ümbrisena" pikkade valemite sellesse mähimiseks ja nende sisestamise lihtsustamiseks. Tegelikult on LAMBDA-l veel üks, palju sügavam külg, mis muudab selle peaaegu täisväärtuslikuks programmeerimiskeeleks.
Fakt on see, et LAMBDA funktsioonide põhimõtteliselt oluline omadus on nende juurutamise võimalus rekursioon – arvutuste loogika, kui funktsioon arvutusprotsessis ise kutsub. Harjumuse põhjal võib see tunduda jube, kuid programmeerimises on rekursioon tavaline asi. Isegi Visual Basicu makrodes saate seda rakendada ja nüüd, nagu näete, on see jõudnud Excelisse. Proovime seda tehnikat praktilise näitega mõista.
Oletame, et tahame luua kasutaja määratud funktsiooni, mis eemaldaks lähtetekstist kõik antud märgid. Sellise funktsiooni kasulikkust, ma arvan, pole vaja tõestada – selle abiga oleks ju väga mugav risustunud sisendandmed kustutada, eks?
Võrreldes eelmiste, mitterekursiivsete näidetega, ootavad meid aga kaks raskust.
- Peame leidma oma funktsioonile nime, enne kui hakkame selle koodi kirjutama, sest selles kasutatakse seda nime juba funktsiooni enda kutsumiseks.
- Sellise rekursiivse funktsiooni sisestamine lahtrisse ja selle silumine, määrates argumendid sulgudes LAMBDA järel (nagu me varem tegime), ei tööta. Peate kohe "nullist" looma funktsiooni Nimehaldur (Nimehaldur).
Nimetagem oma funktsiooni näiteks CLEAN ja soovime, et sellel oleks kaks argumenti – puhastatav tekst ja välistatud märkide loend tekstistringina:
Loome, nagu varem, vahekaardil valem в Nimehaldur nimega vahemik, nimeta see CLEAR ja sisesta väljale Valik järgmine ehitus:
=LAMBDA(t;d;IF(d="";t;CLEAR(ASENDAJA(t;VASAKUD(d);"");MID(d;2;255))))
Siin on muutuja t algtekst, mis tuleb kustutada, ja d on kustutatavate märkide loend.
See kõik töötab nii:
iteratsiooni 1
Fragment SUBSTITUTE(t;LEFT(d);""), nagu võite arvata, asendab lähtetekstis t kustutatava hulga d vasakpoolse märgi esimese märgi tühja tekstistringiga, st eemaldab " A”. Vahetulemusena saame:
Vsh zkz n 125 rubla.
iteratsiooni 2
Seejärel kutsub funktsioon ise välja ja sisendina (esimene argument) saab selle, mis jääb pärast eelmises etapis puhastamist alles ning teine argument on välistatud märkide jada, mis algab mitte esimesest, vaid teisest märgist ehk “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ”, ilma algustäheta „A” – seda teeb funktsioon MID. Nagu varemgi, võtab funktsioon ülejäänutest (B) esimese märgi vasakult ja asendab selle talle antud tekstis (Zkz n 125 rubla) tühja stringiga – saame vahetulemusena:
125 ru.
iteratsiooni 3
Funktsioon kutsub ennast uuesti, saades esimese argumendina eelmise iteratsiooni käigus kustutatavast tekstist järelejäänud (Bsh zkz n 125 ru.) ja teise argumendina välistatud märkide komplekti, mis on kärbitud veel ühe märgi võrra. vasak, st "VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.", ilma algustäheta "B". Seejärel võtab see komplektist jälle esimese märgi vasakult (B) ja eemaldab selle tekstist – saame:
sh zkz n 125 ru.
Ja nii edasi – ma loodan, et saate ideest aru. Iga iteratsiooniga kärbitakse vasakult eemaldatavate märkide loendit ning me otsime ja asendame komplektist järgmise tähemärgi tühimikuga.
Kui kõik märgid otsa saavad, peame tsüklist väljuma – seda rolli täidab lihtsalt funktsioon IF (KUI), millesse meie disain on pakitud. Kui kustutatavaid märke ei ole (d=””), siis funktsioon ei peaks enam ise kutsuma, vaid lihtsalt tagastama kustutatava teksti (muutuja t) lõplikul kujul.
Rakkude rekursiivne iteratsioon
Samamoodi saate rakendada antud vahemikus olevate lahtrite rekursiivset loendust. Oletame, et tahame luua lambda-funktsiooni nimega ASENDUSTE LOETELU lähtetekstis olevate fragmentide hulgi asendamiseks etteantud viiteloendi järgi. Tulemus peaks välja nägema selline:
Need. meie üritusel ASENDUSTE LOETELU seal on kolm argumenti:
- lahter töödeldava tekstiga (allika aadress)
- veeru esimene lahter väärtustega, mida otsingust otsida
- veeru esimene lahter otsingu asendusväärtustega
Funktsioon peaks liikuma kataloogis ülalt alla ja asendama järjestikku kõik vasakpoolses veerus olevad valikud Leidma vastavate valikute juurde paremast veerust Asendaja. Saate seda rakendada järgmise rekursiivse lambda funktsiooniga:
Igal iteratsioonil alla nihutamist rakendab standardne Exceli funktsioon KÕRVALDAMINE (NIHE), millel on antud juhul kolm argumenti – algne vahemik, rea nihe (1) ja veeru nihe (0).
Noh, niipea kui jõuame kataloogi lõppu (n = “”), peame lõpetama rekursiooni – lõpetame enda helistamise ja kuvame selle, mis on kogunenud pärast kõiki asendusi lähteteksti muutujas t.
See on kõik. Ei mingeid keerulisi makrosid ega Power Query päringuid – kogu ülesande lahendab üks funktsioon.
- Kuidas kasutada Exceli uusi dünaamilise massiivi funktsioone: FILTER, SORT, UNIC
- Teksti asendamine ja puhastamine funktsiooniga SUBSTITUTE
- Makrode ja kasutaja määratud funktsioonide (UDF) loomine VBA-s