Obsah
V súčasnosti má Microsoft Excel takmer päťsto funkcií pracovného hárka dostupných cez okno Sprievodca funkciami – tlačidlo fx v riadku vzorcov. Ide o veľmi slušnú zostavu, no napriek tomu sa takmer každý používateľ skôr či neskôr stretne so situáciou, že tento zoznam neobsahuje funkciu, ktorú potrebuje – jednoducho preto, že nie je v Exceli.
Doteraz jediným spôsobom, ako tento problém vyriešiť, boli makrá, teda písanie vlastnej užívateľom definovanej funkcie (UDF = User Defined Function) vo Visual Basicu, čo si vyžaduje primerané programátorské znalosti a niekedy to vôbec nie je jednoduché. S najnovšími aktualizáciami Office 365 sa však situácia zmenila k lepšiemu – do Excelu pribudla špeciálna funkcia „wrapper“ LAMBDA. S jeho pomocou je teraz úloha vytvorenia vlastných funkcií vyriešená jednoducho a krásne.
Pozrime sa na princíp jeho použitia v nasledujúcom príklade.
Ako s najväčšou pravdepodobnosťou viete, Excel má niekoľko funkcií analýzy dátumu, ktoré vám umožňujú určiť číslo dňa, mesiaca, týždňa a roku pre daný dátum. Ale z nejakého dôvodu neexistuje funkcia, ktorá určuje číslo štvrťroka, čo je tiež často potrebné, však? Opravme tento nedostatok a tvorme s LAMBDA vlastnú novú funkciu na vyriešenie tohto problému.
Krok 1. Napíšte vzorec
Začnime tým, že ručne bežným spôsobom napíšeme do bunky listu vzorec, ktorý vypočíta, čo potrebujeme. V prípade štvrťročného čísla to možno urobiť napríklad takto:
Krok 2. Zabalenie do LAMBDA a testovanie
Teraz je čas použiť novú funkciu LAMBDA a zabaliť do nej náš vzorec. Syntax funkcie je nasledovná:
=LAMBDA(Premenná1; Premenná2; ... PremennáN ; vyjadrenie)
kde sú názvy jednej alebo viacerých premenných uvedené ako prvé a posledný argument je vždy vzorec alebo vypočítaný výraz, ktorý ich používa. Názvy premenných by nemali vyzerať ako adresy buniek a nemali by obsahovať bodky.
V našom prípade to bude len jedna premenná – dátum, pre ktorý počítame štvrťročné číslo. Nazvime to premennou, povedzme, d. Potom zabalíme náš vzorec do funkcie LAMBDA a nahradením adresy pôvodnej bunky A2 fiktívnym názvom premennej dostaneme:
Upozorňujeme, že po takejto transformácii začal náš vzorec (v skutočnosti správne!) produkovať chybu, pretože teraz sa doň neprenáša pôvodný dátum z bunky A2. Pre testovanie a dôveru mu môžete odovzdať argumenty tak, že ich pridáte za funkciu LAMBDA v zátvorkách:
Krok 3. Vytvorte meno
Teraz k jednoduchej a zábavnej časti. Otvárame Správca mien pútko vzorec (Vzorce — Name Manager) a pomocou tlačidla vytvorte nový názov vytvoriť (Vytvoriť). Vymyslite a zadajte názov našej budúcej funkcie (napr. Nomkvartala) a v teréne odkaz (Referencia) opatrne skopírujte z riadku vzorcov a prilepte našu funkciu LAMBDA, iba bez posledného argumentu (A2):
Všetko. Po kliknutí na OK vytvorenú funkciu možno použiť v ktorejkoľvek bunke na ľubovoľnom hárku tohto zošita:
Použitie v iných knihách
LAMBDA a dynamické polia
Vlastné funkcie vytvorené pomocou funkcie LAMBDA úspešne podporuje prácu s novými dynamickými poľami a ich funkciami (FILTER, UNIK, GRADE) pridaný do programu Microsoft Excel v roku 2020.
Povedzme, že chceme vytvoriť novú užívateľom definovanú funkciu, ktorá by porovnávala dva zoznamy a vrátila by rozdiel medzi nimi – tie prvky z prvého zoznamu, ktoré nie sú v druhom. Životná práca, nie? Predtým na to používali buď funkcie a la VPR (VLOOKUP)alebo kontingenčné tabuľky alebo dotazy Power Query. Teraz môžete urobiť s jedným vzorcom:
V anglickej verzii to bude:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Tu je funkcia COUNTIF spočíta počet výskytov každého prvku prvého zoznamu v druhom a potom funkcie FILTER vyberie len tých z nich, ktorí tieto výskyty nemali. Zabalením tejto štruktúry do LAMBDA a vytvorenie pomenovaného rozsahu na jeho základe s názvom, napr. VYHĽADÁVANIE DISTRIBÚCIE – dostaneme pohodlnú funkciu, ktorá vráti výsledok porovnania dvoch zoznamov vo forme dynamického poľa:
Ak zdrojovými údajmi nie sú bežné, ale „inteligentné“ tabuľky, naša funkcia si tiež bez problémov poradí:
Ďalším príkladom je dynamické rozdelenie textu jeho konverziou na XML a následnou analýzou bunky po bunke pomocou funkcie FILTER.XML, ktorú sme nedávno analyzovali. Aby sa tento zložitý vzorec nereprodukoval zakaždým ručne, bude jednoduchšie ho zabaliť do LAMBDA a na jeho základe vytvoriť dynamický rozsah, teda novú kompaktnú a pohodlnú funkciu, pomenovať ju napríklad RAZDTEXT:
Prvý argument tejto funkcie bude bunka so zdrojovým textom a druhý – znak oddeľovača a vráti výsledok vo forme horizontálneho dynamického poľa. Funkčný kód bude nasledovný:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Zoznam príkladov je nekonečný – v každej situácii, keď často musíte zadávať rovnaký dlhý a ťažkopádny vzorec, funkcia LAMBDA výrazne uľahčí život.
Rekurzívne vymenovanie znakov
Všetky predchádzajúce príklady ukázali iba jednu, najzrejmejšiu stránku funkcie LAMBDA – jej použitie ako „obalu“ na zabalenie dlhých vzorcov a zjednodušenie ich zadávania. V skutočnosti má LAMBDA ešte jednu, oveľa hlbšiu stránku, ktorá z nej robí takmer plnohodnotný programovací jazyk.
Faktom je, že zásadne dôležitou vlastnosťou funkcií LAMBDA je schopnosť ich implementácie rekurzia – logika výpočtov, kedy v procese výpočtu funkcia volá sama seba. Zo zvyku to môže znieť strašidelne, ale v programovaní je rekurzia bežná vec. Dokonca aj v makrách vo Visual Basicu ho môžete implementovať a teraz, ako vidíte, prišiel aj do Excelu. Skúsme pochopiť túto techniku na praktickom príklade.
Predpokladajme, že chceme vytvoriť užívateľom definovanú funkciu, ktorá by odstránila všetky dané znaky zo zdrojového textu. Užitočnosť takejto funkcie, myslím, netreba dokazovať – bolo by veľmi vhodné s jej pomocou vymazať posypané vstupné dáta, však?
V porovnaní s predchádzajúcimi, nerekurzívnymi príkladmi, nás však čakajú dve ťažkosti.
- Predtým, ako začneme písať jej kód, budeme musieť vymyslieť názov pre našu funkciu, pretože v ňom sa už tento názov bude používať na volanie samotnej funkcie.
- Zadanie takejto rekurzívnej funkcie do bunky a jej ladenie zadaním argumentov v zátvorkách za LAMBDA (ako sme to urobili predtým) nebude fungovať. Budete musieť okamžite vytvoriť funkciu „od začiatku“. Správca mien (Správca mien).
Nazvime našu funkciu povedzme CLEAN a chceli by sme, aby mala dva argumenty – text, ktorý sa má vyčistiť, a zoznam vylúčených znakov ako textový reťazec:
Vytvorme, ako sme to urobili predtým, na karte vzorec в Menný manažér pomenovaný rozsah, pomenujte ho CLEAR a zadajte ho do poľa Rozsah nasledujúca konštrukcia:
=LAMBDA(t;d;IF(d=””;t;CLEAR(SUBSTITUTE(t;LEFT(d);””);MID(d;2;255))))
Tu je premenná t pôvodný text, ktorý sa má vymazať, a d je zoznam znakov, ktoré sa majú vymazať.
Celé to funguje takto:
Iterácia 1
Fragment SUBSTITUTE(t;LEFT(d);””), ako by ste mohli hádať, nahrádza prvý znak z ľavého znaku z množiny d, ktorý sa má vymazať v zdrojovom texte t, prázdnym textovým reťazcom, tj odstraňuje „ A“. Ako medzivýsledok dostaneme:
Vsh zkz n 125 rubľov.
Iterácia 2
Potom sa funkcia zavolá sama a ako vstup (prvý argument) dostane to, čo ostalo po vyčistení v predchádzajúcom kroku a druhý argument je reťazec vylúčených znakov začínajúci nie od prvého, ale od druhého znaku, teda „BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ,” bez začiatočného písmena „A“ – vykonáva to funkcia MID. Rovnako ako predtým, funkcia vezme prvý znak zľava zo zostávajúcich (B) a nahradí ho v texte, ktorý jej bol zadaný (Zkz n 125 rubľov) prázdnym reťazcom – dostaneme ako medzivýsledok:
125 ru.
Iterácia 3
Funkcia sa znova zavolá, pričom ako prvý argument dostane to, čo zostalo z textu, ktorý sa má vymazať pri predchádzajúcej iterácii (Bsh zkz n 125 ru.), a ako druhý argument množinu vylúčených znakov skrátenú o jeden ďalší znak na vľavo, tj „VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.“ bez začiatočného písmena „B“. Potom opäť vezme prvý znak zľava (B) z tejto množiny a odstráni ho z textu – dostaneme:
sh zkz n 125 ru.
A tak ďalej – dúfam, že pochopíte. Pri každej iterácii sa zoznam znakov, ktoré sa majú odstrániť, vľavo skráti a my vyhľadáme a nahradíme ďalší znak zo sady prázdnotou.
Keď sa minú všetky znaky, budeme musieť slučku opustiť – túto rolu práve vykonáva funkcia IF (AK), do ktorej je zabalený náš dizajn. Ak nezostali žiadne znaky na vymazanie (d=””), funkcia by sa už nemala volať, ale mala by jednoducho vrátiť text, ktorý sa má vymazať (premenná t) v jeho konečnej podobe.
Rekurzívna iterácia buniek
Podobne môžete implementovať rekurzívny výpočet buniek v danom rozsahu. Predpokladajme, že chceme vytvoriť funkciu lambda s názvom NÁHRADNÝ ZOZNAM na veľkoobchodnú náhradu fragmentov v zdrojovom texte podľa daného referenčného zoznamu. Výsledok by mal vyzerať takto:
Tie. na našej funkcii NÁHRADNÝ ZOZNAM budú tri argumenty:
- bunka s textom na spracovanie (zdrojová adresa)
- prvá bunka stĺpca s hodnotami na vyhľadávanie z vyhľadávania
- prvá bunka stĺpca s náhradnými hodnotami z vyhľadávania
Funkcia by mala ísť v adresári zhora nadol a postupne nahradiť všetky možnosti z ľavého stĺpca Nájsť na zodpovedajúce možnosti z pravého stĺpca náhradka. Môžete to implementovať pomocou nasledujúcej rekurzívnej funkcie lambda:
Posun nadol pri každej iterácii je implementovaný štandardnou excelovou funkciou LIKVIDÁCIA (POSUN), ktorý má v tomto prípade tri argumenty – pôvodný rozsah, posun riadku (1) a posun stĺpca (0).
No akonáhle sa dostaneme na koniec adresára (n = “”), musíme ukončiť rekurziu – prestaneme volať sami seba a zobrazíme to, čo sa nahromadilo po všetkých náhradách v premennej zdrojového textu t.
To je všetko. Žiadne zložité makrá ani dotazy Power Query – celú úlohu rieši jedna funkcia.
- Ako používať nové funkcie dynamického poľa Excelu: FILTER, SORT, UNIC
- Nahradenie a vyčistenie textu pomocou funkcie SUBSTITUTE
- Vytváranie makier a užívateľom definovaných funkcií (UDF) vo VBA