змест
На дадзены момант Microsoft Excel мае амаль пяцьсот функцый працоўных лістоў, даступных праз акно майстра функцый – кнопку fx у радку формул. Гэта вельмі прыстойны набор, але, тым не менш, практычна кожны карыстальнік рана ці позна сутыкаецца з сітуацыяй, калі ў гэтым спісе адсутнічае патрэбная яму функцыя - проста таму, што яе няма ў Excel.
Дагэтуль адзіным спосабам вырашэння гэтай праблемы былі макрасы, г.зн. напісанне ўласнай вызначанай карыстальнікам функцыі (UDF = User Defined Function) у Visual Basic, што патрабуе адпаведных навыкаў праграмавання і, часам, зусім няпроста. Аднак з апошнімі абнаўленнямі Office 365 сітуацыя змянілася ў лепшы бок - у Excel была дададзена спецыяльная функцыя «абгортка». LAMBDA. З яго дапамогай цяпер лёгка і прыгожа вырашаецца задача стварэння ўласных функцый.
Разгледзім прынцып яго выкарыстання на наступным прыкладзе.
Як вы, хутчэй за ўсё, ведаеце, Excel мае некалькі функцый аналізу даты, якія дазваляюць вызначыць нумар дня, месяца, тыдня і года для дадзенай даты. Але чамусьці няма функцыі, якая вызначае нумар квартала, што таксама часта патрэбна, праўда? Давайце выправім гэты недахоп і створым с LAMBDA уласную новую функцыю для вырашэння гэтай праблемы.
Крок 1. Запішыце формулу
Пачнем з таго, што ўручную звычайным спосабам мы напішам у вочку ліста формулу, якая разлічвае тое, што нам трэба. У выпадку з нумарам квартала гэта можна зрабіць, напрыклад, так:
Крок 2. Завяршэнне ў LAMBDA і тэставанне
Цяпер прыйшоў час прымяніць новую функцыю LAMBDA і загарнуць у яе нашу формулу. Сінтаксіс функцыі наступны:
=LAMBDA(Пераменная1; Пераменная2; … ПераменнаяN ; выраз)
дзе імёны адной або некалькіх зменных пералічаны першымі, а апошні аргумент заўсёды з'яўляецца формулай або вылічаным выразам, які іх выкарыстоўвае. Імёны зменных не павінны выглядаць як адрасы вочак і не павінны ўтрымліваць кропак.
У нашым выпадку будзе толькі адна зменная - дата, для якой мы разлічваем нумар квартала. Давайце назавем зменную для гэтага, скажам, d. Затым загортваем нашу формулу ў функцыю LAMBDA і падставіўшы адрас зыходнай ячэйкі A2 на выдуманае імя зменнай, атрымаем:
Звярніце ўвагу, што пасля такога пераўтварэння наша формула (па сутнасці, правільная!) Стала выдаваць памылку, таму што цяпер у яе не пераносіцца зыходная дата з ячэйкі А2. Для праверкі і ўпэўненасці вы можаце перадаць яму аргументы, дадаўшы іх пасля функцыі LAMBDA у дужках:
Крок 3. Стварыце назву
А цяпер простая і вясёлая частка. Адкрываем Менеджэр імёнаў таб формула (Формулы — Менеджэр імёнаў) і стварыце новае імя з дапамогай кнопкі Ствараць (Стварыць). Прыдумайце і ўвядзіце назву для нашай будучай функцыі (напрыклад, Номквартала), і ў полі спасылка (Даведка) старанна скапіруйце з радка формул і ўстаўце нашу функцыю LAMBDA, толькі без апошняга аргументу (A2):
усё. Пасля націску на OK створаную функцыю можна выкарыстоўваць у любой ячэйцы на любым аркушы гэтай кнігі:
Выкарыстанне ў іншых кнігах
LAMBDA і дынамічныя масівы
Карыстальніцкія функцыі, створаныя з дапамогай функцыі LAMBDA паспяхова падтрымліваецца праца з новымі дынамічнымі масівамі і іх функцыямі (Фільтр, УНІК, GRADE) дададзены ў Microsoft Excel у 2020 годзе.
Дапусцім, мы хочам стварыць новую карыстальніцкую функцыю, якая будзе параўноўваць два спісы і вяртаць розніцу паміж імі – тыя элементы з першага спісу, якіх няма ў другім. Праца жыцця, ці не так? Раней для гэтага выкарыстоўвалі альбо функцыі а-ля ВПР (ВПР), або зводныя табліцы, або запыты Power Query. Цяпер вы можаце зрабіць з адной формулай:
У англійскай версіі гэта будзе:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Вось функцыя COUNTIF падлічвае колькасць уваходжанняў кожнага элемента першага спісу ў другі, а потым у функцыю Фільтр выбірае толькі тыя з іх, у якіх гэтых выпадкаў не было. Загарнуўшы гэтую структуру ў LAMBDA і стварэнне найменнага дыяпазону на яго аснове з назвай, напрыклад, ПОШУК РАСПРОСТРАНЕННЯ – атрымаем зручную функцыю, якая вяртае вынік параўнання двух спісаў у выглядзе дынамічнага масіву:
Калі зыходнымі дадзенымі з'яўляюцца не звычайныя, а «разумныя» табліцы, наша функцыя таксама справіцца без праблем:
Іншым прыкладам з'яўляецца дынамічнае раздзяленне тэксту шляхам пераўтварэння яго ў XML і наступнага разбору ячэйкі з дапамогай функцыі FILTER.XML, якую мы нядаўна разбіралі. Каб не прайграваць кожны раз гэтую складаную формулу ўручную, прасцей будзе загарнуць яе ў LAMBDA і стварыць на яе аснове дынамічны дыяпазон, г.зн. новую кампактную і зручную функцыю, назваўшы яе, напрыклад, RAZDTEXT:
Першым аргументам гэтай функцыі будзе з'яўляцца вочка з зыходным тэкстам, а другім - сімвал падзельніка, і яна верне вынік у выглядзе гарызантальнага дынамічнага масіва. Код функцыі будзе наступным:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Спіс прыкладаў бясконцы - у любой сітуацыі, калі часта даводзіцца ўводзіць адну і тую ж доўгую і грувасткую формулу, функцыя LAMBDA прыкметна палегчыць жыццё.
Рэкурсіўны пералік знакаў
Усе папярэднія прыклады паказалі толькі адзін, самы відавочны, бок функцыі LAMBDA - яе выкарыстанне ў якасці «абгорткі» для абгортвання ў яе доўгіх формул і спрашчэння іх уводу. На самай справе ў LAMBDA ёсць яшчэ адзін, значна больш глыбокі, бок, які ператварае яго практычна ў паўнавартасную мову праграмавання.
Справа ў тым, што прынцыпова важнай асаблівасцю функцый LAMBDA з'яўляецца магчымасць іх рэалізацыі ў рэкурсія – логіка вылічэнняў, калі ў працэсе вылічэння функцыя выклікае сама сябе. Па звычцы гэта можа прагучаць жудасна, але ў праграмаванні рэкурсія - звычайная справа. Нават у макрасах у Visual Basic вы можаце рэалізаваць гэта, а цяпер, як бачыце, гэта дайшло і да Excel. Паспрабуем разабрацца ў гэтай тэхніцы на практычным прыкладзе.
Выкажам здагадку, што мы хочам стварыць вызначаную карыстальнікам функцыю, якая выдаляла б усе зададзеныя сімвалы з зыходнага тэксту. Карыснасць такой функцыі, думаю, даказваць не трэба – ачышчаць засмечаныя ўваходныя дадзеныя з яе дапамогай было б вельмі зручна, праўда?
Аднак у параўнанні з папярэднімі, нерэкурсіўнымі прыкладамі, нас чакаюць дзве цяжкасці.
- Нам трэба будзе прыдумаць імя для нашай функцыі, перш чым мы пачнем пісаць яе код, таму што ў ім гэтае імя ўжо будзе выкарыстоўвацца для выкліку самой функцыі.
- Увядзенне такой рэкурсіўнай функцыі ў ячэйку і яе адладка шляхам указання аргументаў у дужках пасля LAMBDA (як мы рабілі раней) не будзе працаваць. Вам трэба будзе стварыць функцыю адразу «з нуля». Менеджэр імёнаў (Менеджэр імёнаў).
Давайце назавем нашу функцыю, скажам, CLEAN, і мы хацелі б, каб яна мела два аргументы - тэкст, які трэба ачысціць, і спіс выключаных сімвалаў у выглядзе тэкставага радка:
Створым, як і раней, на ўкладцы формула в Менеджэр імёнаў названы дыяпазон, назавіце яго CLEAR і ўвядзіце ў поле Range наступная канструкцыя:
=LAMBDA(t;d;КАЛІ(d=””;t;АЧЫСТЫЦЬ(ЗАМЕНА(t;НАЛЕВА(d);””);СЯРЭДЗІНА(d;2;255))))
Тут зменная t - зыходны тэкст, які трэба ачысціць, а d - спіс сімвалаў, якія трэба выдаліць.
Усё гэта працуе так:
ітэрацыя 1
Фрагмент SUBSTITUTE(t;LEFT(d);””), як можна здагадацца, замяняе ў зыходным тэксце t першы сімвал з левага сімвала з набору d, які падлягае выдаленню, на пусты тэкставы радок, г.зн. А”. У якасці прамежкавага выніку атрымліваем:
Вш зкз п 125 руб.
ітэрацыя 2
Затым функцыя выклікае сябе і ў якасці ўваходных дадзеных (першы аргумент) атрымлівае тое, што засталося пасля ачысткі на папярэднім кроку, а другі аргумент - радок выключаных сімвалаў, пачынаючы не з першага, а з другога сімвала, г.зн. «BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ,” без пачатковай “А” – гэта робіцца функцыяй MID. Функцыя, як і раней, бярэ першы знак злева з пакінутых (B) і замяняе яго ў зададзеным ёй тэксце (Zkz n 125 рублёў) пустым радком – атрымліваем прамежкавы вынік:
125 руб.
ітэрацыя 3
Функцыя выклікае сябе зноў, прымаючы ў якасці першага аргумента тое, што засталося ад тэксту, які трэба ачысціць на папярэдняй ітэрацыі (Bsh zkz n 125 ru.), і ў якасці другога аргумента набор выключаных сімвалаў, усечаны яшчэ на адзін сімвал для злева, г.зн. «ВГДЕЕГЗИКЛМНОПРСТУФХЦЩШШЧЫЙЫЮЯ.», без пачатковай літары «В». Потым зноў бярэ з гэтага набору першы сімвал злева (B) і выдаляе яго з тэксту – атрымліваем:
ш зкз п 125 ру.
І гэтак далей – спадзяюся, вы зразумелі. Пры кожнай ітэрацыі спіс сімвалаў, якія трэба выдаліць, будзе скарачацца злева, і мы будзем шукаць і замяняць наступны сімвал з набору пустатай.
Калі ўсе сімвалы скончацца, нам трэба будзе выйсці з цыкла - гэтую ролю проста выконвае функцыя IF (КАЛІ), у які загорнуты наш дызайн. Калі не засталося сімвалаў для выдалення (d=””), то функцыя больш не павінна выклікаць сама сябе, а павінна проста вярнуць тэкст, які трэба ачысціць (зменная t), у яго канчатковай форме.
Рэкурсіўная ітэрацыя вочак
Падобным чынам вы можаце рэалізаваць рэкурсіўны пералік ячэек у зададзеным дыяпазоне. Дапусцім, мы хочам стварыць лямбда-функцыю з імем СПІС ЗАМЕНЫ для аптовай замены фрагментаў у зыходным тэксце па зададзеным спісе літаратуры. Вынік павінен выглядаць так:
Тыя. на нашай функцыі СПІС ЗАМЕНЫ будзе тры аргументы:
- ячэйка з тэкстам для апрацоўкі (крынічны адрас)
- першая вочка слупка са значэннямі для пошуку з пошуку
- першая вочка слупка са значэннямі замены з пошуку
Функцыя павінна ісці зверху ўніз у каталогу і паслядоўна замяняць усе параметры з левага слупка Для таго, каб знайсці да адпаведных опцый з правага слупка сурагат. Вы можаце рэалізаваць гэта з дапамогай наступнай рэкурсіўнай лямбда-функцыі:
Зрух уніз на кожнай ітэрацыі рэалізаваны стандартнай функцыяй Excel ВЫКАРЫСТАННЕ (ЗРУШЭННЕ), які ў дадзеным выпадку мае тры аргументы – зыходны дыяпазон, зрух радка (1) і зрух слупка (0).
Што ж, як толькі мы дасягаем канца каталога (n = «»), мы павінны скончыць рэкурсію - мы перастаем выклікаць сябе і вывесці тое, што назапасілася пасля ўсіх замен у зыходнай тэкставай зменнай t.
Гэта ўсе. Ніякіх хітрых макрасаў або запытаў Power Query - уся задача вырашаецца адной функцыяй.
- Як выкарыстоўваць новыя функцыі дынамічнага масіву Excel: FILTER, SORT, UNIC
- Замена і ачыстка тэксту з дапамогай функцыі SUBSTITUTE
- Стварэнне макрасаў і вызначаных карыстальнікам функцый (UDF) у VBA