Садржај
У овом тренутку, Мицрософт Екцел има скоро пет стотина функција радног листа које су доступне преко прозора Чаробњак за функције – дугме fx у траци формуле. Ово је веома пристојан сет, али, ипак, скоро сваки корисник пре или касније наиђе на ситуацију да ова листа не садржи функцију која му је потребна – једноставно зато што није у Екцел-у.
До сада, једини начин за решавање овог проблема били су макрои, односно писање сопствене кориснички дефинисане функције (УДФ = Усер Дефинед Фунцтион) у Висуал Басиц-у, што захтева одговарајуће вештине програмирања и понекад није нимало лако. Међутим, са најновијим ажурирањима Оффице 365, ситуација се променила на боље – у Екцел је додата посебна функција „замотавања“. ЛАМБДА. Уз његову помоћ, задатак креирања сопствених функција сада се решава лако и лепо.
Погледајмо принцип његове употребе у следећем примеру.
Као што вероватно знате, Екцел има неколико функција за рашчлањивање датума које вам омогућавају да одредите број дана, месеца, недеље и године за дати датум. Али из неког разлога не постоји функција која одређује број четвртине, што је такође често потребно, зар не? Хајде да поправимо овај недостатак и стварамо са ЛАМБДА сопствену нову функцију за решавање овог проблема.
Корак 1. Напишите формулу
Почнимо са чињеницом да ћемо ручно на уобичајени начин написати формулу у ћелију листа која израчунава шта нам је потребно. У случају броја квартала, то се може урадити, на пример, овако:
Корак 2. Завршавање у ЛАМБДА и тестирање
Сада је време да применимо нову ЛАМБДА функцију и умотамо нашу формулу у њу. Синтакса функције је следећа:
=ЛАМБДА(Променљива1; Променљива2; … ВариаблеН ; израз)
где су имена једне или више променљивих наведена прва, а последњи аргумент је увек формула или израчунати израз који их користи. Имена променљивих не би требало да изгледају као адресе ћелија и не би требало да садрже тачке.
У нашем случају, постојаће само једна варијабла – датум за који израчунавамо број квартала. Назовимо променљиву за то, рецимо, д. Затим умотавање наше формуле у функцију ЛАМБДА и заменом адресе оригиналне ћелије А2 са фиктивним именом променљиве, добијамо:
Имајте на уму да је након такве трансформације наша формула (у ствари, тачна!) почела да производи грешку, јер се сада оригинални датум из ћелије А2 не преноси у њу. За тестирање и самопоуздање, можете му проследити аргументе тако што ћете их додати после функције ЛАМБДА у загради:
Корак 3. Направите име
Сада за лак и забаван део. Отварамо Наме Манагер табулатор формула (Формуле — Менаџер имена) и креирајте ново име помоћу дугмета Створити (Креирај). Осмислите и унесите име за нашу будућу функцију (нпр. Номквартала), и на терену линк (Референца) пажљиво копирајте са траке формуле и налепите нашу функцију ЛАМБДА, само без последњег аргумента (А2):
Све. Након што кликнете на OK креирана функција се може користити у било којој ћелији на било ком листу ове радне свеске:
Користите у другим књигама
ЛАМБДА и динамички низови
Прилагођене функције креиране помоћу функције ЛАМБДА успешно подржава рад са новим динамичким низовима и њиховим функцијама (ФИЛТЕР, УНИК, РАЗРЕД) додато у Мицрософт Екцел 2020.
Рецимо да желимо да креирамо нову кориснички дефинисану функцију која би упоредила две листе и вратила разлику између њих – оне елементе са прве листе који нису у другој. Животно дело, зар не? Раније су за ово користили било коју функцију а ла ВПР (ВЛООКУП), или заокретне табеле или Повер Куери упити. Сада можете да урадите са једном формулом:
У енглеској верзији биће:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Овде је функција ЦОУНТИФ броји број појављивања сваког елемента прве листе у другој, а затим функцију ФИЛТЕР бира само оне од њих који нису имали ове појаве. Умотавањем ове структуре у ЛАМБДА и креирање именованог опсега на основу њега са именом, на пример, ПРЕТРАГА ДИСТРИБУЦИЈА – добићемо згодну функцију која враћа резултат поређења две листе у облику динамичког низа:
Ако изворни подаци нису обичне, већ „паметне“ табеле, наша функција ће се такође носити без проблема:
Други пример је динамичко цепање текста конвертовањем у КСМЛ и затим рашчлањивање ћелије по ћелију помоћу функције ФИЛТЕР.КСМЛ коју смо недавно рашчланили. Да не бисте сваки пут ручно репродуковали ову сложену формулу, биће лакше да је умотате у ЛАМБДА и креирате динамички опсег на основу ње, односно нову компактну и згодну функцију, дајући јој име, на пример, РАЗДТЕКСТ:
Први аргумент ове функције биће ћелија са изворним текстом, а други – знак за раздвајање, а резултат ће вратити у облику хоризонталног динамичког низа. Код функције ће бити следећи:
=ЛАМБДА(t;d; ТРАНСПОСЕ(ФИЛТЕР.КСМЛ(“
Листа примера је бескрајна – у свакој ситуацији у којој често морате да унесете исту дугачку и гломазну формулу, ЛАМБДА функција ће знатно олакшати живот.
Рекурзивно набрајање карактера
Сви претходни примери су показали само једну, најочигледнију, страну ЛАМБДА функције – њену употребу као „омотача“ за умотавање дугих формула у њу и поједностављивање њиховог уноса. У ствари, ЛАМБДА има још једну, много дубљу страну која га претвара у готово пуноправни програмски језик.
Чињеница је да је фундаментално важна карактеристика ЛАМБДА функција могућност њихове имплементације рекурзија – логика прорачуна, када се у процесу израчунавања функција позива сама. Из навике, можда звучи језиво, али у програмирању је рекурзија уобичајена ствар. Чак и у макроима у Висуал Басиц-у, можете га имплементирати, а сада је, као што видите, дошао у Екцел. Покушајмо да разумемо ову технику са практичним примером.
Претпоставимо да желимо да креирамо кориснички дефинисану функцију која би уклонила све дате знакове из изворног текста. Корисност такве функције, мислим, не треба да доказујете – било би веома згодно да уз њену помоћ очистите затрпане улазне податке, зар не?
Међутим, у поређењу са претходним, нерекурзивним примерима, очекују нас две тешкоће.
- Мораћемо да смислимо име за нашу функцију пре него што почнемо да пишемо њен код, јер ће се у њој ово име већ користити за позивање саме функције.
- Уношење такве рекурзивне функције у ћелију и њено отклањање грешака навођењем аргумената у заградама после ЛАМБДА (као што смо радили раније) неће радити. Мораћете да креирате функцију одмах „од нуле“. Наме Манагер (Менаџер имена).
Назовимо нашу функцију, рецимо, ЦЛЕАН и желели бисмо да има два аргумента – текст који треба очистити и листу искључених знакова као текстуални низ:
Креирајмо, као што смо раније урадили, на картици формула в Име менаџер именовани опсег, назовите га ЦЛЕАР и унесите у поље Домет следећа конструкција:
=ЛАМБДА(т;д;ИФ(д=””;т;БРИШИ(ЗАМЕНИ(т;ЛЕВО(д);””);СРЕДИНА(д;2;255))))
Овде је променљива т оригинални текст који треба обрисати, а д је листа знакова за брисање.
Све функционише овако:
понављање КСНУМКС
Фрагмент СУБСТИТУТЕ(т;ЛЕФТ(д);””), као што можете да претпоставите, замењује први знак левог знака из скупа д који треба да се брише у изворном тексту т празним текстуалним низом, односно уклања „ А”. Као средњи резултат добијамо:
Всх зкз н 125 руб.
понављање КСНУМКС
Тада функција позива саму себе и као улаз (први аргумент) прима оно што је остало након чишћења у претходном кораку, а други аргумент је низ искључених знакова који почиње не од првог, већ од другог карактера, односно „БВГДЕЕГЗИКЛМНОПРСТУФХТСЦХСХСХЦХИИИИИА. ,” без почетног „А” – то ради МИД функција. Као и раније, функција узима први знак са леве стране од преосталих (Б) и замењује га у тексту који јој је дат (Зкз н 125 рубаља) празним низом - добијамо као средњи резултат:
125 ру.
понављање КСНУМКС
Функција поново позива саму себе, примајући као први аргумент оно што је остало од текста за брисање на претходној итерацији (Бсх зкз н 125 ру.), а као други аргумент, скуп искључених знакова скраћен за још један знак да би лево, тј. „ВГДЕЕГЗИКЛМНОПРСТУФХТСЦХСХСХЦХИИИИУИА.“, без иницијал „Б“. Затим поново узима први знак са леве стране (Б) из овог скупа и уклања га из текста – добијамо:
сх зкз н 125 ру.
И тако даље – надам се да сте схватили идеју. Са сваком итерацијом, листа знакова које треба уклонити биће скраћена са леве стране, а ми ћемо тражити и заменити следећи знак из скупа празнином.
Када понестане свих знакова, мораћемо да изађемо из петље – ову улогу управо обавља функција IF (АКО), у који је умотан наш дизајн. Ако нема преосталих знакова за брисање (д=””), функција више не би требало да позива саму себе, већ би једноставно требало да врати текст који треба да се обрише (променљива т) у његовом коначном облику.
Рекурзивна итерација ћелија
Слично томе, можете имплементирати рекурзивно набрајање ћелија у датом опсегу. Претпоставимо да желимо да креирамо ламбда функцију под називом ЗАМЕНСКА ЛИСТА за велепродајну замену фрагмената у изворном тексту према датој референтној листи. Резултат би требао изгледати овако:
Оне. на нашој функцији ЗАМЕНСКА ЛИСТА биће три аргумента:
- ћелија са текстом за обраду (изворна адреса)
- прва ћелија колоне са вредностима за претрагу из претраживања
- прва ћелија колоне са вредностима замене из претраживања
Функција треба да иде од врха до дна у директоријуму и да замени узастопно све опције из леве колоне Да пронађу на одговарајуће опције из десне колоне Субституте. Ово можете имплементирати са следећом рекурзивном ламбда функцијом:
Померање надоле при свакој итерацији се имплементира помоћу стандардне Екцел функције ОДЛАГАЊЕ (ОФСЕТ), који у овом случају има три аргумента – оригинални опсег, померање реда (1) и померање колоне (0).
Па, чим дођемо до краја директоријума (н = “”), морамо да прекинемо рекурзију – престајемо да се позивамо и приказујемо шта се накупило након свих замена у изворној текстуалној променљивој т.
То је све. Нема лукавих макроа или Повер Куери упита – цео задатак решава једна функција.
- Како користити нове функције динамичког низа Екцел-а: ФИЛТЕР, СОРТ, УНИЦ
- Замена и чишћење текста са функцијом СУБСТИТУТЕ
- Креирање макроа и кориснички дефинисаних функција (УДФ) у ВБА