Съдържание
В момента Microsoft Excel има почти петстотин функции на работния лист, достъпни чрез прозореца на съветника за функции – бутон fx в лентата с формули. Това е много приличен набор, но въпреки това почти всеки потребител рано или късно се сблъсква със ситуация, в която този списък не съдържа функцията, от която се нуждае - просто защото не е в Excel.
Досега единственият начин за решаване на този проблем бяха макроси, т.е. писане на собствена дефинирана от потребителя функция (UDF = User Defined Function) във Visual Basic, което изисква подходящи умения за програмиране и понякога не е никак лесно. С последните актуализации на Office 365 обаче ситуацията се промени към по-добро – към Excel беше добавена специална функция „wrapper“. Ламбда. С негова помощ задачата за създаване на собствени функции вече се решава лесно и красиво.
Нека разгледаме принципа на използването му в следния пример.
Както най-вероятно знаете, Excel има няколко функции за анализ на дата, които ви позволяват да определите номера на деня, месеца, седмицата и годината за дадена дата. Но по някаква причина няма функция, която да определя номера на тримесечието, което също често е необходимо, нали? Нека поправим този недостатък и да създадем с Ламбда собствена нова функция за решаване на този проблем.
Стъпка 1. Напишете формулата
Нека започнем с факта, че ръчно по обичайния начин ще напишем формула в клетка на лист, която изчислява това, от което се нуждаем. В случай на номер на тримесечие това може да се направи например така:
Стъпка 2. Обвиване в LAMBDA и тестване
Сега е време да приложим новата функция LAMBDA и да обвием нашата формула в нея. Синтаксисът на функцията е както следва:
=LAMBDA(Променлива1; Променлива2; ... Променлива N ; Изразяване)
където имената на една или повече променливи са изброени първи, а последният аргумент винаги е формула или изчислен израз, който ги използва. Имената на променливите не трябва да изглеждат като адреси на клетки и не трябва да съдържат точки.
В нашия случай ще има само една променлива – датата, за която изчисляваме тримесечието. Нека наречем променливата за него, да речем, d. След това обгръщане на нашата формула във функция Ламбда и замествайки адреса на оригиналната клетка A2 с измислено име на променлива, получаваме:
Моля, обърнете внимание, че след такава трансформация нашата формула (всъщност правилна!) започна да генерира грешка, тъй като сега първоначалната дата от клетка A2 не се прехвърля към нея. За тестване и увереност можете да му подадете аргументи, като ги добавите след функцията Ламбда в скоби:
Стъпка 3. Създайте име
Сега за лесната и забавна част. Ние отваряме Мениджър на имена етикет формула (Формули — Мениджър на имена) и създайте ново име с бутона Създаване на (Създаване). Измислете и въведете име за бъдещата ни функция (например Номквартала), и в полето връзка (Референтен) внимателно копирайте от лентата с формули и поставете нашата функция Ламбда, само без последния аргумент (A2):
Всичко. След като щракнете върху OK създадената функция може да се използва във всяка клетка на всеки лист от тази работна книга:
Използване в други книги
LAMBDA и динамични масиви
Персонализирани функции, създадени с функция Ламбда успешно поддържа работа с нови динамични масиви и техните функции (FILTER, УНИК, КЛАС) добавен към Microsoft Excel през 2020 г.
Да кажем, че искаме да създадем нова дефинирана от потребителя функция, която да сравнява два списъка и да връща разликата между тях – тези елементи от първия списък, които не са във втория. Работата на живота, нали? Преди това за това те използваха или функции a la ВПР (VLOOKUP), или обобщени таблици, или заявки на Power Query. Сега можете да направите с една формула:
В английската версия ще бъде:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Ето функцията COUNTIF отчита броя на срещанията на всеки елемент от първия списък във втория и след това функцията FILTER избира само тези от тях, които не са имали тези събития. Чрез обвиване на тази структура в Ламбда и създаване на именуван диапазон въз основа на него с име, например, ТЪРСЕНЕ РАЗПРОСТРАНЕНИЕ – ще получим удобна функция, която връща резултата от сравняването на два списъка под формата на динамичен масив:
Ако изходните данни не са обикновени, а „умни“ таблици, нашата функция също ще се справи без проблеми:
Друг пример е динамичното разделяне на текст чрез преобразуването му в XML и след това анализирането му клетка по клетка с помощта на функцията FILTER.XML, която наскоро анализирахме. За да не възпроизвеждате тази сложна формула ръчно всеки път, ще бъде по-лесно да я увиете в LAMBDA и да създадете динамичен диапазон въз основа на нея, т.е. нова компактна и удобна функция, като я наименувате например RAZDTEXT:
Първият аргумент на тази функция ще бъде клетката с изходния текст, а вторият – разделителят и ще върне резултата под формата на хоризонтален динамичен масив. Функционалният код ще бъде както следва:
=LAMBDA(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
След това функцията се извиква и като вход (първият аргумент) получава това, което е останало след почистването в предишната стъпка, а вторият аргумент е низът от изключени символи, започващ не от първия, а от втория символ, т.е. “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ,” без инициала “А” – това се прави от функцията MID. Както и преди, функцията взема първия знак отляво на останалите (B) и го замества в дадения й текст (Zkz n 125 рубли) с празен низ – получаваме като междинен резултат:
125 ru.
Итерация 3
Функцията се извиква отново, като получава като първи аргумент това, което е останало от текста, който трябва да бъде изчистен при предишната итерация (Bsh zkz n 125 ru.), И като втори аргумент, наборът от изключени знаци, съкратени с още един знак до отляво, т.е. “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.”, без инициала “B”. След това отново взема първия символ отляво (B) от този набор и го премахва от текста – получаваме:
sh zkz n 125 ru.
И така нататък – надявам се, че схващате идеята. С всяка итерация списъкът със знаци, които трябва да бъдат премахнати, ще бъде съкратен отляво и ние ще търсим и заместваме следващия знак от набора с празнота.
Когато всички символи свършат, ще трябва да излезем от цикъла – тази роля просто се изпълнява от функцията IF (АКО), в който е опакован нашия дизайн. Ако няма останали символи за изтриване (d=””), тогава функцията вече не трябва да се самоизвиква, а просто трябва да върне текста за изчистване (променлива t) в окончателната му форма.
Рекурсивна итерация на клетки
По подобен начин можете да приложите рекурсивно изброяване на клетки в даден диапазон. Да предположим, че искаме да създадем ламбда функция с име СПИСЪК ЗА ЗАМЕНА за подмяна на едро на фрагменти в изходния текст по даден референтен списък. Резултатът трябва да изглежда така:
Тези. на нашата функция СПИСЪК ЗА ЗАМЕНА ще има три аргумента:
- клетка с текст за обработка (адрес на източника)
- първата клетка от колона със стойности за търсене от търсенето
- първата клетка от колоната със заместващи стойности от търсенето
Функцията трябва да върви отгоре надолу в директорията и да замества последователно всички опции от лявата колона Да намеря към съответните опции от дясната колона заместител. Можете да приложите това със следната рекурсивна ламбда функция:
Преместването надолу при всяка итерация се изпълнява от стандартна функция на Excel ИЗХВЪРЛЯНЕ (ИЗМЕСТВАНЕ), който в случая има три аргумента – оригиналния диапазон, изместване на ред (1) и изместване на колона (0).
Е, щом стигнем до края на директорията (n = “”), трябва да прекратим рекурсията – спираме да се извикваме и показваме натрупаното след всички замени в изходната текстова променлива t.
Това е всичко. Без сложни макроси или Power Query заявки – цялата задача се решава от една функция.
- Как да използвате новите функции за динамичен масив на Excel: FILTER, SORT, UNIC
- Замяна и почистване на текст с функцията ЗАМЕНЯНЕ
- Създаване на макроси и дефинирани от потребителя функции (UDF) във VBA