содржина
Во моментот, Microsoft Excel има речиси петстотини функции на работниот лист достапни преку копчето Function Wizard - прозорец fx во лентата со формула. Ова е многу пристоен сет, но, сепак, скоро секој корисник порано или подоцна се соочува со ситуација кога оваа листа не ја содржи функцијата што му е потребна - едноставно затоа што не е во Excel.
Досега единствениот начин за решавање на овој проблем беа макроата, односно пишувањето на сопствената функција дефинирана од корисникот (UDF = User Defined Function) во Visual Basic, што бара соодветни програмски вештини и, понекогаш, воопшто не е лесно. Сепак, со најновите ажурирања на Office 365, ситуацијата се промени на подобро - специјална функција „wrapper“ е додадена во Excel LAMBDA. Со негова помош, задачата за создавање на сопствени функции сега е решена лесно и убаво.
Да го погледнеме принципот на неговата употреба во следниот пример.
Како што најверојатно знаете, Excel има неколку функции за парсирање на датуми кои ви овозможуваат да го одредите бројот на денот, месецот, неделата и годината за даден датум. Но, поради некоја причина не постои функција која го одредува бројот на кварталот, кој исто така често е потребен, нели? Да го поправиме овој недостаток и да создадеме со LAMBDA сопствена нова функција за решавање на овој проблем.
Чекор 1. Напишете ја формулата
Да почнеме со фактот дека рачно на вообичаен начин ќе напишеме формула во ќелија на лист што пресметува што ни треба. Во случај на бројот на четвртина, ова може да се направи, на пример, вака:
Чекор 2. Завиткување во LAMBDA и тестирање
Сега е време да ја примените новата функција LAMBDA и да ја завиткаме нашата формула во неа. Синтаксата на функцијата е како што следува:
=ЛАМБДА(Променлива 1; Променлива 2; … Променлива N ; Изразување)
каде прво се наведени имињата на една или повеќе променливи, а последниот аргумент е секогаш формула или пресметан израз што ги користи. Имињата на променливите не треба да изгледаат како адреси на ќелиите и не треба да содржат точки.
Во нашиот случај, ќе има само една променлива - датумот за кој го пресметуваме кварталниот број. Ајде да ја повикаме променливата за неа, да речеме, d. Потоа завиткување на нашата формула во функција LAMBDA и заменувајќи ја адресата на оригиналната ќелија A2 со фиктивно име на променлива, добиваме:
Забележете дека по таквата трансформација, нашата формула (всушност, точна!) почна да произведува грешка, бидејќи сега оригиналниот датум од ќелијата А2 не се пренесува на неа. За тестирање и доверба, можете да му пренесете аргументи со нивно додавање по функцијата LAMBDA во загради:
Чекор 3. Направете име
Сега за лесен и забавен дел. Отвораме Менаџер за имиња табот формула (Формули - Менаџер со име) и креирајте ново име со копчето Креирај (Креирај). Дојдете и внесете име за нашата идна функција (на пример, Номквартала), и на терен линк (Референца) внимателно копирајте од лентата со формула и залепете ја нашата функција LAMBDA, само без последниот аргумент (А2):
Сè. Откако ќе кликнете на OK креираната функција може да се користи во која било ќелија на кој било лист од оваа работна книга:
Користете во други книги
LAMBDA и динамички низи
Прилагодени функции креирани со функција LAMBDA успешно ја поддржува работата со нови динамички низи и нивните функции (ФИЛТЕРОТ, УНИК, GRADE) додадена во Microsoft Excel во 2020 година.
Да речеме дека сакаме да создадеме нова функција дефинирана од корисникот која ќе споредува две списоци и ќе ја врати разликата меѓу нив – оние елементи од првата листа што не се во втората. Животна работа, нели? Претходно, за ова тие ги користеа двете функции a la VPR (VLOOKUP), или PivotTables или барања за Power Query. Сега можете да направите со една формула:
Во англиската верзија ќе биде:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Еве ја функцијата COUNTIF го брои бројот на појавувања на секој елемент од првата листа во втората, а потоа и функцијата ФИЛТЕРОТ ги избира само оние од нив кои ги немале овие појави. Со завиткување на оваа структура во LAMBDA и создавање именуван опсег врз основа на него со име, на пример, ДИСТРИБУЦИЈА НА ПРЕБАРУВАЊЕ – ќе добиеме удобна функција која го враќа резултатот од споредување на две списоци во форма на динамична низа:
Ако изворните податоци не се обични, туку „паметни“ табели, нашата функција исто така ќе се справи без проблеми:
Друг пример е динамичко разделување на текстот со негово конвертирање во XML и потоа парсирање ќелија по ќелија користејќи ја функцијата FILTER.XML што неодамна ја анализиравме. За да не се репродуцира оваа сложена формула рачно секој пат, ќе биде полесно да се завитка во LAMBDA и да се создаде динамичен опсег врз основа на тоа, т.е. нова компактна и удобна функција, именувајќи ја, на пример, RAZDTEXT:
Првиот аргумент на оваа функција ќе биде ќелијата со изворниот текст, а вториот - знакот за раздвојување и ќе го врати резултатот во форма на хоризонтална динамичка низа. Кодот на функцијата ќе биде како што следува:
=ЛАМБДА(t;d; TRANSPOSE(FILTER.XML(“
Списокот на примери е бесконечен – во секоја ситуација кога често треба да ја внесете истата долга и незгодна формула, функцијата LAMBDA значително ќе го олесни животот.
Рекурзивно набројување на знаци
Сите претходни примери покажаа само една, најочигледна, страна на функцијата LAMBDA – нејзината употреба како „обвивка“ за завиткување долги формули во неа и поедноставување на нивното внесување. Всушност, LAMBDA има уште една, многу подлабока страна што го претвора во речиси полноправно програмски јазик.
Факт е дека фундаментално важна карактеристика на функциите LAMBDA е способноста да се имплементираат во рекурзија – логика на пресметките, кога во процес на пресметување функцијата се повикува себеси. Од навика, можеби звучи морничаво, но во програмирањето, рекурзијата е вообичаена работа. Дури и во макроата во Visual Basic, можете да го имплементирате, а сега, како што можете да видите, дојде во Excel. Ајде да се обидеме да ја разбереме оваа техника со практичен пример.
Да претпоставиме дека сакаме да создадеме функција дефинирана од корисникот која ќе ги отстрани сите дадени знаци од изворниот текст. Корисноста на таквата функција, мислам, не треба да ја докажувате - би било многу погодно да се исчистат затрупаните влезни податоци со негова помош, нели?
Меѓутоа, во споредба со претходните, нерекурзивни примери, нè очекуваат две тешкотии.
- Ќе треба да смислиме име за нашата функција пред да почнеме да ја пишуваме нејзината шифра, бидејќи во неа ова име веќе ќе се користи за повикување на самата функција.
- Внесување на таква рекурзивна функција во ќелија и нејзино дебагирање со наведување на аргументи во загради по LAMBDA (како што направивме порано) нема да работи. Ќе треба веднаш да креирате функција „од нула“. Менаџер за имиња (Име менаџер).
Да ја наречеме нашата функција, да речеме, CLEAN и би сакале таа да има два аргументи - текстот што треба да се исчисти и списокот со исклучени знаци како текстуална низа:
Ајде да создадеме, како што направивме порано, на јазичето формула в Име менаџер именуван опсег, именувајте го CLEAR и внесете во полето Опсег следнава конструкција:
=LAMBDA(t;d;IF(d="";t;CLEAR(SUBSTITUTE(t;LEFT(d);"");MID(d;2;255))))
Овде променливата t е оригиналниот текст што треба да се избрише, а d е списокот на знаци што треба да се избришат.
Сето тоа функционира вака:
Итерација 1
Фрагментот SUBSTITUTE(t;LEFT(d);““), како што може да претпоставите, го заменува првиот знак од левиот знак од множеството d што треба да се избрише во изворниот текст t со празна текстуална низа, односно ја отстранува „ А“. Како среден резултат, добиваме:
Vsh zkz n 125 рубли.
Итерација 2
Потоа функцијата се повикува себеси и како влез (првиот аргумент) го прима она што останало по чистењето во претходниот чекор, а вториот аргумент е низата исклучени знаци кои почнуваат не од првиот, туку од вториот знак, односно „BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYYA. ,“ без иницијалното „A“ – тоа го прави функцијата MID. Како и досега, функцијата го зема првиот знак од лево од преостанатите (B) и го заменува во текстот што ѝ е даден (Zkz n 125 рубли) со празна низа - добиваме како среден резултат:
125 ру.
Итерација 3
Функцијата повторно се повикува себеси, примајќи го како прв аргумент она што останува од текстот што треба да се исчисти при претходната итерација (Bsh zkz n 125 ru.), а како втор аргумент, множеството исклучени знаци скратени со уште еден знак до лево, т.е. „VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.“, без иницијалот „B“. Потоа повторно го зема првиот знак од лево (B) од ова множество и го отстранува од текстот - добиваме:
sh zkz n 125 ru.
И така натаму - се надевам дека ќе ја сфатите идејата. Со секое повторување, списокот на знаци што треба да се отстранат ќе се скрати лево, а следниот знак од множеството ќе го бараме и ќе го замениме со празнина.
Кога ќе завршат сите знаци, ќе треба да излеземе од јамката - оваа улога само ја извршува функцијата IF (АКО), во кој е завиткан нашиот дизајн. Ако нема останати знаци за бришење (d=””), тогаш функцијата повеќе не треба да се повикува себеси, туку едноставно треба да го врати текстот што треба да се исчисти (променлива t) во неговата конечна форма.
Рекурзивна итерација на клетките
Слично на тоа, можете да имплементирате рекурзивно набројување на ќелии во даден опсег. Да претпоставиме дека сакаме да создадеме ламбда функција со име ЛИСТА НА ЗАМЕНА за големопродажна замена на фрагменти во изворниот текст според дадена референтна листа. Резултатот треба да изгледа вака:
Оние. на нашата функција ЛИСТА НА ЗАМЕНА ќе има три аргументи:
- ќелија со текст за обработка (изворна адреса)
- првата ќелија од колоната со вредности за пребарување од пребарувањето
- првата ќелија од колоната со заменливи вредности од пребарувањето
Функцијата треба да оди од врвот до дното во директориумот и последователно да ги замени сите опции од левата колона Да најде до соодветните опции од десната колона Заменик. Можете да го имплементирате ова со следнава рекурзивна ламбда функција:
Поместувањето надолу при секое повторување се имплементира со стандардна ексел функција РАБОТУВАЕ (ОФСЕТ), кој во овој случај има три аргументи – оригинален опсег, поместување на редови (1) и поместување на колона (0).
Па, штом ќе стигнеме до крајот на директориумот (n = „“), мора да ја завршиме рекурзијата - престануваме да се повикуваме и го прикажуваме она што е акумулирано по сите замени во променливата на изворниот текст t.
Тоа е се. Без незгодни макроа или прашања за Power Query - целата задача се решава со една функција.
- Како да ги користите новите функции на динамична низа на Excel: FILTER, SORT, UNIC
- Заменување и чистење на текстот со функцијата SUBSTITUTE
- Креирање макроа и кориснички дефинирани функции (UDF) во VBA