Ламбда функция в Excel: пълно ръководство и практически примери

Информатек Дигитал » Ресурси » Ламбда функция в Excel: пълно ръководство и практически примери

Функцията Lambda в Excel революционизира начина, по който работим с формули в електронните таблици на Microsoft. Тя ви позволява да създавате свои собствени персонализирани функции, използвайки само езика за формули на Excel, без да докосвате нито един ред от VBA или традиционното програмиране . Все едно имате възможността да добавяте нови, персонализирани, вградени функции към програмата.

Освен това, около LAMBDA са се появили редица свързани функции, като BYROW, BYCOL, MAP, SCAN, REDUCE и MAKEARRAY , предназначени да работят с диапазони и матрици по много по-гъвкав и мощен начин. Тези функции се държат, най-общо казано, като малки цикли, които итерират през данните и прилагат трансформация, дефинирана от LAMBDA, отваряйки широк набор от възможности за разширен анализ директно в електронната таблица.

Какво представлява функцията LAMBDA в Excel и за какво се използва?

Функцията Lambda е инструмент, който ви позволява да дефинирате персонализирани функции, използвайки само формули на Excel. Вместо да програмирате във VBA или да разчитате на макроси, можете да капсулирате всяко сложно изчисление в една единствена, многократно използваема функция, със собствени параметри и ясен, чист краен резултат.

На практика LAMBDA превръща всяка формула във функция , която можете да използвате повторно толкова пъти, колкото искате. Основното му предимство е, че се интегрира безпроблемно с останалата част от изчислителната система на Excel и може да се комбинира със стандартни функции, препратки, дефинирани имена и динамични масиви.

Основният синтаксис, когато се използва директно в клетка, е:

=LAMBDA(параметър1; параметър2; …; параметърN; изчисление)(стойност1; стойност2; …; стойностN)

В тази структура, параметър1, параметър2, ..., параметърN са имената, които давате на променливите във функцията, докато изчисление е формулата, която използва тези параметри, за да генерира резултат. Накрая, във втория набор от скоби, подавате действителните стойности , които тези параметри ще приемат, когато функцията се изпълни.

Ако използвате диспечера на имена на Excel , за да създадете постоянна LAMBDA функция, синтаксисът се променя леко, защото дефинирате функцията там, но все още не я извиквате. В този случай форматът би бил:

=LAMBDA(променлива1; променлива2; …; променливаN; изчисление)

По-късно ще извикате тази функция, използвайки името, което сте ѝ дали в „Мениджър на имена“, като просто въведете името и аргументите, точно както бихте направили със SUM, AVERAGE или всяка друга стандартна функция.

Най-добри практики при създаване и тестване на ламбда функции

Когато започнете да работите с ламбда функции, е важно да следвате няколко насоки, за да сте сигурни, че те се държат както се очаква и че няма да губите време за отстраняване на грешки при сложни грешки. Един от най-практичните начини да започнете е да създадете и тествате ламбда функцията директно в клетка.

Обичайната процедура е първо да се напише пълната формула с дефиницията на LAMBDA и извикването в един и същ израз, така че да можете веднага да видите дали резултатът е такъв, какъвто се очаква. По този начин можете да откриете синтактични или логически грешки, преди да я запазите като именувана функция.

Например, една много типична структура на теста би била:

=LAMBDA(); изчисление)(тестови_стойности)

За да проверите нещо много просто, като например добавяне на 1 към число, можете да използвате:

=LAMBDA(число; число + 1)(1)

В този случай функцията ще върне стойността 2. Това е много прост пример, но служи за илюстриране на механиката: първо дефинирате параметрите и изчислението, а след това извиквате тази функция, като ѝ предавате съответния аргумент.

Ключова препоръка за избягване на грешката #CALC! е да се уверите, че вашата LAMBDA функция винаги връща резултат . Това се постига чрез ясно включване на израз в края, който произвежда единична стойност или масив, в зависимост от вашите нужди. Ако видите грешката #CALC! по време на тестване, проверете дали формулата действително генерира нещо, което Excel може да покаже като резултат.

След като сте тествали функцията LAMBDA в клетка и видите, че тя работи правилно, е подходящ момент да преместите тази логика в диспечера на имена и да я превърнете в персонализирана функция за многократна употреба в целия лист или работна книга.

Връзка на LAMBDA с новите матрични функции

Около LAMBDA се появиха редица усъвършенствани функции, като BYROW, BYCOL, MAP, SCAN, REDUCE и MAKEARRAY (последната се превежда в някои версии като ARCHIVOMAKEARRAY), които разчитат на LAMBDA, за да прилагат трансформации към диапазони и пълни матрици.

Общата идея е, че тези функции итерират през диапазони от данни (по редове, по колони или елемент по елемент) и за всеки елемент или група елементи изпълняват дефинирана от вас ламбда функция. С други думи, те работят като цикли, но интегрирани във формулния език на Excel.

Това ви позволява да извършвате операции, които преди това са изисквали помощни колони, междинни таблици или дори макроси, директно с една единствена формула за масив, която се разширява и връща резултати за целия диапазон наведнъж.

Сред функциите, свързани с LAMBDA, се открояват REDUCE, MAP, SCAN, BYCOL, BYROW и MAKEARRAY , всяка от които има специфична цел: преминаване през редове, прилагане на трансформации по колони, натрупване на резултати, създаване на матрици, изчислени от нулата и др. Всички те имат общото, че използват LAMBDA като вътрешен „двигател“, на който предават стойности и акумулатори, докато се движат през матрицата.

Функция BYROW: итерация през редове и връщане на резултати ред по ред

Функцията BYROW се използва за прилагане на LAMBDA функция към всеки ред в диапазон и връщане на масив с една стойност за всеки обработен ред. Това е много ефикасен начин за изчисляване на междинни суми или статистически данни ред по ред, без да се налага вертикално копиране на формули.

Общият му синтаксис е:

=BYROW(матрица; LAMBDA(ред; израз))

Първият аргумент е масивът или диапазонът, през който искате да итерирате (например B2:D7), а вторият е LAMBDA функция, която приема всеки ред в този диапазон като параметър, един по един. LAMBDA функцията връща стойността, която искате да свържете с този ред (евентуално сума, средна стойност, логическа проверка и т.н.).

Представете си, че имате таблица с данни в диапазона B2:D7 и искате да получите междинна сума за всеки ред. Можете да напишете нещо подобно в клетка E2:

=BYROW(B2:D7; LAMBDA(ред; SUM(ред)))

Резултатът ще бъде изходен вектор с по една стойност за всеки ред на матрицата B2:D7, където всяка стойност представлява сумата от елементите в този ред. По този начин не е нужно да пишете SUM ред по ред: BYROW го прави вместо вас и извежда резултата надолу.

Функция BYCOL: прилагане на LAMBDA по колони

Много подобно на BYROW, функцията BYCOL е проектирана да обхожда масив по колони, вместо по редове. Тя прилага LAMBDA функция към всяка колона в диапазона и връща масив от резултати, където всеки елемент съответства на колона.

Типичният му синтаксис е:

=BYCOL(масив; LAMBDA(колона; израз))

В този случай, параметърът, получен от LAMBDA, е цялата колона на матрицата, обработвана на всяка стъпка. Подобно на BYROW, функцията връща вектор, но тази е проектирана да работи с общи суми или индикатори по колона.

Продължавайки с предишния пример, ако искате да изчислите средната стойност на всяка колона в диапазона B2:D7 , можете да поставите формула като тази в клетка B8:

=BYCOL(B2:D7; LAMBDA(колона; AVERAGE(колона)))

Резултатът ще бъде матрица със същия брой колони като B2:D7, където всяка позиция съдържа средната стойност на тази колона . По този начин получавате всички средни стойности наведнъж, без да се налага да плъзгате и пускате формули или да се притеснявате за относителни препратки.

Функция MAKEARRAY (MAKEARRAYFILE): създаване на изчислени масиви

Функцията MAKEARRAY (понякога показвана като ARCHIVOMAKEARRAY) ви позволява да генерирате напълно нов масив, като укажете броя на редовете и колоните и изчислите всеки елемент с помощта на LAMBDA функция. Тя не започва от съществуващ диапазон, а изгражда масива от нулата.

Общият му синтаксис е:

=MAKEARRAY(редове; колони; LAMBDA(ред; колона; израз))

Аргументът „rows“ показва колко реда ще има изходната матрица, „columns“ определя броя на колоните, а функцията LAMBDA получава индексите на редове и колони, изчислявани във всяка итерация, като параметри. С тази информация можете да конструирате практически всеки числов или текстов шаблон.

Много илюстративен пример е създаването на матрица, където всеки елемент показва собствената си позиция. Във всяка клетка можете да напишете нещо подобно:

=MAKEARRAYFILE(3; 2; LAMBDA(ред; колона; -(ред и колона)))

Резултатът ще бъде матрица с 3 реда и 2 колони , където всяка стойност представлява комбинация от ред и колона (например 11, 12, 21, 22, 31, 32), трансформирана според въведеното от вас изчисление (в този случай, отрицателният знак се прилага към конкатенацията на ред и колона).

Друго интересно приложение на MAKEARRAY е преобразуването на вектор в масив, като същевременно контролирате броя на елементите, които приемате. Да предположим, че искате да създадете масив с първите 6 стойности на вертикален диапазон. Първо можете да създадете масив от позиции с MAKEARRAYFILE, да получите k-те най-малки стойности на позициите и накрая да използвате INDEX, за да извлечете действителните елементи от оригиналния диапазон.

Пример за формула, комбинираща няколко функции, може да има следната структура:

=LET(arrPos; MAKEARRAYFILE(3; 2; LAMBDA(ред; колона; -(ред и колона))); arrPosF; MATCH(arrPos; LeastK(arrPos; SEQUENCE(6))); INDEX(G8:G13; arrPosF))

Тук LET се използва за дефиниране на междинни имена (arrPos, arrPosF), масив от позиции се конструира с ARCHIVOMAKEARRAY (3×2), 6-те най-малки позиции се избират с SMALLEST и SEQUENCE и накрая INDEX се използва за връщане на съответните стойности от диапазона G8:G13. Това е мощен пример за това как да се комбинират LAMBDA и динамични функции за масиви, за да се извършват сложни трансформации без макроси.

Функция MAP: трансформация елемент по елемент

Функцията MAP се използва за едновременно обхождане на един или повече масиви и връщане на нов масив, където всеки изходен елемент се изчислява чрез прилагане на LAMBDA функция към съответния(ите) входен(и) елемент(и). Тя е еквивалентна на класическа функция „map“ във функционалното програмиране.

Основният синтаксис е:

=MAP(матрица1; LAMBDA_или_още_матрици)

В най-простата си форма, тя приема един масив и LAMBDA функция, която получава всяка стойност от този масив. Тази LAMBDA функция трансформира стойността и връща новата версия, която ще формира част от изходния масив, запазвайки същите размери като оригиналния масив.

Например, ако искате да итерирате през вертикален диапазон A21:A26 и да оставите оригиналното число, ако е четно, или тире, ако е нечетно, можете да използвате нещо подобно:

=MAP($A$21:$A$26; LAMBDA(параметър1; АКО(ES.PAR(параметър1); параметър1; «-«)))

В този случай MAP анализира всеки елемент от A21:A26. LAMBDA проверява с IS.EVEN дали числото е четно. Ако е така, връща самото число; в противен случай връща тире. Резултатът е масив със същия размер като оригиналния диапазон, но с приложена трансформация към всеки елемент.

Този подход е много полезен, когато искате да приложите условна логика, преобразуване на текст, нормализиране на стойности или друга проста операция, като избягвате помощни колони и повтарящи се формули.

Функция SCAN: кумулативни и междинни резултати

Функцията SCAN се използва за изследване на масив чрез прилагане на LAMBDA функция към всяка стойност и генериране на изходен масив, показващ всички междинни стойности от процеса на натрупване. Тя е много подобна на REDUCE, но вместо да връща само крайния резултат, тя запазва всяка стъпка.

Общият му синтаксис е:

=SCAN(; масив; LAMBDA(акумулатор; стойност))

Първият аргумент, който е по избор, е началната стойност на акумулатора (например 0, ако сумирате). Вторият аргумент е масивът или диапазонът, през който искате да итерирате. Накрая, функцията LAMBDA получава два параметъра: акумулатора (частичният резултат до тази точка) и текущата стойност на масива, който обработвате.

На всяка стъпка SCAN оценява LAMBDA стойността, актуализира акумулатора и генерира нов елемент в изходната матрица с получената стойност. По този начин се получава поредица от натрупани стойности или прогресивни трансформации.

Типичен пример е изчисляването на кумулативна сума (текуща сума) върху набор от стойности и оттам получаването на относителната кумулативна честота. Представете си, че имате данни в A31:A36 и искате абсолютната кумулативна сума:

=SCAN(0; A31:A36; LAMBDA(accum; param1; accum + param1))

Тази формула итерира през A31:A36, добавяйки всяка стойност към предишната сума. Резултатът е масив със същия брой елементи като оригиналния диапазон, но всяка позиция показва кумулативната сума до тази точка.

От тази кумулативна сума е лесно да се изчисли кумулативната процентна честота, като се раздели всяка кумулативна сума на общата сума. Можете например първо да дефинирате общата сума, използвайки SUM, и след това да приложите отново SCAN:

=LET(общо; SUM(A31:A36); SCAN(0; A31:A36; LAMBDA(accum; param1; (accum + param1)))/total)

В този случай, LET присвоява на сумата сумата от целия диапазон A31:A36. След това SCAN генерира поредицата от кумулативни стойности и чрез разделянето ѝ на сумата, се получава относителната кумулативна честота за всяка стъпка , всичко това в една матрична формула.

Функция REDUCE: редукция до една натрупана стойност

Функцията REDUCE също итерира през масив, като прилага LAMBDA функция към всеки елемент, но за разлика от SCAN, тук се интересувате само от получаването на крайния резултат от процеса на натрупване. Тоест, тя извършва същия тип итерация като SCAN, но връща само последната стойност в акумулатора.

Синтаксисът му е:

=REDUCE(; масив; LAMBDA(акумулатор; стойност))

Точно както в SCAN, initial_value задава началната точка на акумулатора, матрицата е диапазонът, който ще се обработва, а LAMBDA има като параметри текущия акумулатор и стойността, която се чете в този момент.

Много типична употреба е за изчисляване на текуща сума или кумулативна операция, където се интересувате само от последния резултат . Например, за да сумирате A1:A6, използвайки REDUCE, можете да напишете:

=НАМАЛЯВАНЕ(0; A1:A6; LAMBDA(accum; param1; accum + param1))

Тук REDUCE итерира през A1:A6 и на всяка стъпка актуализира кумулативната стойност , като добавя стойността на текущата клетка (param1). Накрая връща една стойност: общата сума. Концептуално е подобно на използването на SUM, но с REDUCE можете да дефинирате всяка по-сложна логика на натрупване, не само прости суми.

Силата на REDUCE се крие в способността му да работи с предишния резултат на всяка стъпка и да продължава да прилага операции, докато процесът не завърши. Това ви позволява да реализирате сложни персонализирани изчисления, които традиционно се обработваха с цикли в макросите.

Създаване на персонализирани функции с LAMBDA и Name Manager

Една от най-мощните функции на LAMBDA е способността му да преобразува всяка формула в потребителска функция, използвайки мениджъра на имена на Excel. Това позволява на вашата функция да има собствено име и да се използва като всяка друга вградена функция в програмата.

Типичният работен процес е следният: първо, тествайте функцията LAMBDA в клетка , включително както дефиницията, така и извикването ѝ с примерни аргументи. След като проверите, че работи правилно и връща очаквания резултат, копирайте частта, съответстваща на дефиницията на LAMBDA (без последното извикване), и я поставете в диспечера на имена.

В „Мениджър на имена“ създавате ново име (например, MyVATFunction, MyDiscount, MyWeightedAverage и т.н.) и в полето „Refers to“ въвеждате:

=LAMBDA(променлива1; променлива2; …; променливаN; изчисление)

От този момент нататък, във всяка клетка на вашата работна книга можете да извикате функцията, като напишете името ѝ, сякаш е вградена функция , предавайки стойностите на параметрите в същия ред, в който сте ги дефинирали.

Това има две ясни предимства: първо, прави електронните ви таблици по-четливи (вместо дълги формули, виждате функция с описателно име); второ, централизира логиката на едно място. Ако по-късно искате да промените изчислението, просто променете дефиницията в Мениджъра на имена и всички формули, които я използват, ще бъдат актуализирани автоматично.

Практически аспекти и допълнителни съображения

За да използвате наистина LAMBDA и свързаните с него функции, е полезно да разберете някои практически аспекти на тяхното поведение и изисквания . Първо, тези функции са част от съвременните функции на Excel, така че ви е необходима версия, която вече включва динамични масиви и по-новите функции LAMBDA, BYROW, BYCOL и други. Те обикновено са налични в най-новите издания на Microsoft 365.

Друг важен въпрос е производителността : въпреки че LAMBDA и traversal функциите са много мощни, ако ги приложите към огромни диапазони с много сложна логика, преизчисляването на книгата може да отнеме повече време. Препоръчително е LAMBDA функциите да се проектират с оглед на ефективността, като се избягват излишни изчисления и се използват структури като LET за дефиниране на междинни стойности за многократна употреба.

Също така е изключително важно да се поддържа последователна конвенция за именуване на параметри и функции . Използването на описателни имена ви помага да разберете логиката, когато посетите файла отново месеци по-късно или когато някой друг трябва да работи с вашите работни книги. Параметър с име amount, rate, dataRow или valuesCol е много по-ясен от просто x или yoa.

Що се отнася до грешката #CALC!, тя обикновено се появява, когато Excel не може да изчисли израза за масив или когато функцията LAMBDA не връща валиден резултат. Винаги проверявайте дали формулата ви има добре дефиниран изход и, ако работите с функции за масиви, дали размерите са последователни (например, дали не комбинирате несъвместими диапазони от размери без правилна трансформация).

И накрая, макар LAMBDA да елиминира нуждата от VBA в много случаи, той не го замества напълно. Има ситуации, в които автоматизацията на макроси остава най-добрият вариант, но за голям брой персонализирани изчисления и трансформации на данни, LAMBDA и свързаните с него функции ви позволяват да запазите цялата си работа в рамките на конвенционалната среда за формули на Excel.

Благодарение на тези възможности, тези, които работят ежедневно с електронни таблици, вече разполагат с много по-гъвкави инструменти за проектиране на собствени изчисления, обобщаване на информация по редове или колони, пълно или частично преминаване през матрици, генериране на подробни суми и създаване на нови матрици, изчислени в движение, всичко това без да напускат езика за формули, който вече познават.