vsebina
Microsoft Excel ima trenutno skoraj petsto funkcij delovnega lista, ki so na voljo prek okna čarovnika za funkcije – gumb fx v vrstici formule. To je zelo spodoben nabor, vendar kljub temu skoraj vsak uporabnik prej ali slej naleti na situacijo, ko ta seznam ne vsebuje funkcije, ki jo potrebuje - preprosto zato, ker ni v Excelu.
Do sedaj so bili edini način za rešitev tega problema makri, torej pisanje lastne uporabniško definirane funkcije (UDF = User Defined Function) v Visual Basicu, kar zahteva ustrezno znanje programiranja in včasih sploh ni enostavno. Z zadnjimi posodobitvami Office 365 pa se je situacija spremenila na bolje – Excelu je bila dodana posebna funkcija »ovijalke«. LAMBDA. Z njegovo pomočjo je naloga ustvarjanja lastnih funkcij zdaj enostavno in lepo rešena.
Poglejmo si princip njegove uporabe v naslednjem primeru.
Kot verjetno veste, ima Excel več funkcij za razčlenjevanje datumov, ki vam omogočajo, da določite številko dneva, meseca, tedna in leta za določen datum. Toda iz neznanega razloga ni funkcije, ki bi določala številko četrtine, kar je tudi pogosto potrebno, kajne? Odpravimo to pomanjkljivost in ustvarjajmo z LAMBDA lastno novo funkcijo za rešitev te težave.
Korak 1. Napišite formulo
Začnimo z dejstvom, da bomo ročno na običajen način zapisali formulo v celico lista, ki izračuna tisto, kar potrebujemo. V primeru številke četrtine lahko to storite na primer takole:
2. korak. Zavijanje v LAMBDA in testiranje
Zdaj je čas, da uporabimo novo funkcijo LAMBDA in vanjo zavijemo našo formulo. Sintaksa funkcije je naslednja:
=LAMBDA(Spremenljivka1; Spremenljivka2; … SpremenljivkaN ; izražanje)
kjer so imena ene ali več spremenljivk navedena najprej, zadnji argument pa je vedno formula ali izračunan izraz, ki jih uporablja. Imena spremenljivk ne smejo izgledati kot naslovi celic in ne smejo vsebovati pik.
V našem primeru bo samo ena spremenljivka – datum, za katerega izračunamo številko četrtine. Pokličimo spremenljivko za to, recimo, d. Nato zavijemo našo formulo v funkcijo LAMBDA in nadomestimo naslov prvotne celice A2 z izmišljenim imenom spremenljivke, dobimo:
Upoštevajte, da je po takšni transformaciji naša formula (pravzaprav pravilna!) začela proizvajati napako, ker zdaj izvirni datum iz celice A2 ni prenesen vanjo. Za testiranje in zaupanje mu lahko posredujete argumente tako, da jih dodate za funkcijo LAMBDA v oklepaju:
3. korak. Ustvarite ime
Zdaj pa na enostaven in zabaven del. Odpiramo Upravitelj imen tab Formula (Formule — Upravitelj imen) in ustvarite novo ime z gumbom ustvarjanje (Ustvari). Izmislite in vnesite ime za našo prihodnjo funkcijo (npr. Nomkvartala), in na terenu Link (Sklic) previdno kopirajte iz vrstice formule in prilepite našo funkcijo LAMBDA, samo brez zadnjega argumenta (A2):
Vse. Po kliku na OK ustvarjeno funkcijo je mogoče uporabiti v kateri koli celici na katerem koli listu tega delovnega zvezka:
Uporaba v drugih knjigah
LAMBDA in dinamična polja
Funkcije po meri, ustvarjene s funkcijo LAMBDA uspešno podpira delo z novimi dinamičnimi nizi in njihovimi funkcijami (FILTER, UNIK, GRADE) dodan v Microsoft Excel leta 2020.
Recimo, da želimo ustvariti novo uporabniško definirano funkcijo, ki bi primerjala dva seznama in vračala razliko med njima – tiste elemente s prvega seznama, ki jih ni na drugem. Življenjsko delo, kajne? Prej so za to uporabljali bodisi funkcije a la VPR (VLOOKUP), vrtilne tabele ali poizvedbe Power Query. Zdaj lahko storite z eno formulo:
V angleški različici bo to:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Tukaj je funkcija COUNTIF prešteje število pojavitev vsakega elementa prvega seznama v drugem in nato funkcijo FILTER izbere samo tiste izmed njih, ki teh pojavov niso imeli. Z ovijanjem te strukture v LAMBDA in ustvarjanje imenovanega obsega na njegovi podlagi z imenom, na primer, ISKALNA DISTRIBUCIJA – dobili bomo priročno funkcijo, ki vrne rezultat primerjave dveh seznamov v obliki dinamične matrike:
Če izvorni podatki niso navadne, ampak "pametne" tabele, se bo tudi naša funkcija spopadla brez težav:
Drug primer je dinamično razdelitev besedila s pretvorbo v XML in nato razčlenjevanjem celico za celico s funkcijo FILTER.XML, ki smo jo nedavno razčlenili. Da te zapletene formule ne bi vsakič ročno reproducirali, jo bomo lažje zaviti v LAMBDA in na njeni podlagi ustvariti dinamični razpon, torej novo kompaktno in priročno funkcijo, ki jo poimenujemo na primer RAZDTEXT:
Prvi argument te funkcije bo celica z izvornim besedilom, drugi pa ločilni znak, rezultat pa bo vrnil v obliki vodoravnega dinamičnega niza. Koda funkcije bo naslednja:
=LAMBDA(t;d; TRANSPONIRAJ(FILTER.XML(“
Seznam primerov je neskončen – v vsaki situaciji, ko morate pogosto vnašati isto dolgo in okorno formulo, bo funkcija LAMBDA občutno olajšala življenje.
Rekurzivno naštevanje znakov
Vsi prejšnji primeri so pokazali samo eno, najočitnejšo plat funkcije LAMBDA – njeno uporabo kot »ovoja« za zavijanje dolgih formul in poenostavitev njihovega vnosa. Pravzaprav ima LAMBDA še eno, veliko globljo plat, ki jo spremeni v skorajda popoln programski jezik.
Dejstvo je, da je bistveno pomembna lastnost funkcij LAMBDA zmožnost njihove implementacije v rekurzija – logika izračunov, ko v procesu izračuna funkcija pokliče samo sebe. Iz navade se morda sliši srhljivo, a v programiranju je rekurzija običajna stvar. Celo v makre v Visual Basicu ga lahko implementirate, zdaj pa je, kot vidite, prišel tudi v Excel. Poskusimo razumeti to tehniko s praktičnim primerom.
Recimo, da želimo ustvariti uporabniško definirano funkcijo, ki bi odstranila vse podane znake iz izvornega besedila. Uporabnosti takšne funkcije, mislim, da vam ni treba dokazovati - z njeno pomočjo bi bilo zelo priročno počistiti zamašene vhodne podatke, kajne?
Vendar nas v primerjavi s prejšnjimi, nerekurzivnimi primeri čakata dve težavi.
- Ime naše funkcije si bomo morali izmisliti preden začnemo pisati njeno kodo, saj bo v njej to ime že uporabljeno za klic same funkcije.
- Vnos takšne rekurzivne funkcije v celico in njeno odpravljanje napak z navedbo argumentov v oklepajih za LAMBDA (kot smo storili prej) ne bo delovalo. Takoj boste morali ustvariti funkcijo "iz nič". Upravitelj imen (Upravitelj imen).
Poimenujmo našo funkcijo, recimo, CLEAN in želimo, da ima dva argumenta – besedilo, ki ga je treba očistiti, in seznam izključenih znakov kot besedilni niz:
Ustvarjajmo, kot smo prej, na zavihku Formula в Upravitelj imen imenovan obseg, ga poimenujte CLEAR in vnesite v polje Območje naslednja konstrukcija:
=LAMBDA(t;d;IF(d=””;t;CLEAR(SUBSTITUTE(t;LEFT(d);””);MID(d;2;255))))
Tu je spremenljivka t izvirno besedilo, ki ga želite izbrisati, d pa je seznam znakov, ki jih želite izbrisati.
Vse deluje takole:
Ponavljanje 1
Fragment SUBSTITUTE(t;LEFT(d);””), kot morda ugibate, zamenja prvi znak iz levega znaka iz nabora d, ki ga je treba izbrisati v izvornem besedilu t, s praznim besedilnim nizom, tj. odstrani “ A”. Kot vmesni rezultat dobimo:
Vsh zkz n 125 rubljev.
Ponavljanje 2
Nato funkcija pokliče samo sebe in kot vhod (prvi argument) prejme tisto, kar je ostalo po čiščenju v prejšnjem koraku, drugi argument pa je niz izključenih znakov, ki se ne začne od prvega, temveč od drugega znaka, tj. “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ,« brez začetnice »A« – to naredi funkcija MID. Kot prej funkcija vzame prvi znak z leve od preostalih (B) in ga v besedilu, ki mu je bilo dano (Zkz n 125 rubljev), nadomesti s praznim nizom – kot vmesni rezultat dobimo:
125 ru.
Ponavljanje 3
Funkcija se znova pokliče in kot prvi argument prejme tisto, kar je ostalo od besedila, ki ga je treba počistiti pri prejšnji ponovitvi (Bsh zkz n 125 ru.), in kot drugi argument nabor izključenih znakov, okrnjenih še za en znak na levo, tj. “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.,” brez začetnice “B”. Nato spet vzame prvi znak z leve (B) iz tega niza in ga odstrani iz besedila – dobimo:
sh zkz n 125 ru.
In tako naprej – upam, da razumete. Z vsako ponovitvijo bo seznam znakov, ki jih je treba odstraniti, na levi strani okrnjen, mi pa bomo poiskali in zamenjali naslednji znak iz niza s praznino.
Ko zmanjka vseh znakov, bomo morali zapustiti zanko – to vlogo samo opravlja funkcija IF (ČE), v katerega je zavit naš dizajn. Če ni več znakov za brisanje (d=””), se funkcija ne bi smela več klicati sama, ampak bi morala preprosto vrniti besedilo, ki ga je treba izbrisati (spremenljivka t) v končni obliki.
Rekurzivno ponavljanje celic
Podobno lahko implementirate rekurzivno oštevilčenje celic v danem obsegu. Recimo, da želimo ustvariti lambda funkcijo z imenom NADOMESTNI SEZNAM za veleprodajno zamenjavo fragmentov v izvornem besedilu po podanem seznamu referenc. Rezultat bi moral izgledati takole:
Tisti. na naši funkciji NADOMESTNI SEZNAM bodo trije argumenti:
- celica z besedilom za obdelavo (izvorni naslov)
- prva celica stolpca z vrednostmi za iskanje iz iskanja
- prva celica stolpca z nadomestnimi vrednostmi iz iskanja
Funkcija bi morala iti od zgoraj navzdol v imeniku in zaporedno zamenjati vse možnosti iz levega stolpca Najti na ustrezne možnosti v desnem stolpcu Namestnik. To lahko implementirate z naslednjo rekurzivno lambda funkcijo:
Premik navzdol pri vsaki ponovitvi izvaja standardna excelova funkcija ODSTRANJEVANJE (ZAMIK), ki ima v tem primeru tri argumente – prvotni obseg, premik vrstice (1) in premik stolpca (0).
No, takoj ko pridemo do konca imenika (n = “”), moramo končati rekurzijo – nehamo se klicati in prikažemo, kaj se je nabralo po vseh zamenjavah v spremenljivki izvornega besedila t.
To je vse. Brez zapletenih makrov ali poizvedb Power Query – celotno nalogo reši ena funkcija.
- Kako uporabljati Excelove nove dinamične matrične funkcije: FILTER, SORT, UNIC
- Zamenjava in čiščenje besedila s funkcijo SUBSTITUTE
- Ustvarjanje makrov in uporabniško definiranih funkcij (UDF) v VBA