Давайте посмотрим на пример. Допустим, у нас есть формула для поиска по названию товара и заголовку — функция ИНДЕКС / INDEX, аргументами которой выступают две функции ПОИСКПОЗ / MATCH. Мы рассматривали такой пример выше — см. «Двумерный поиск: ИНДЕКС + ПОИСКПОЗ».
С функцией LET ее можно переписать так:
=LET(Поиск_Товара;ПОИСКПОЗ([@Номенклатура];Прайс[Номенклатура];0);
Поиск_Заголовка;ПОИСКПОЗ(C$1;Прайс[#Заголовки];0);
ИНДЕКС(Прайс;Поиск_Товара;Поиск_Заголовка))
В итоговом вычислении в функции ИНДЕКС не будет большого числа скобок и точек с запятой — только имена; сначала — имя таблицы, как и раньше (Прайс), а далее — имена переменных, которые вычисляются на предыдущем шаге. В данном примере у нас нет ничего, что вычисляется несколько раз в формуле, мы лишь делаем ее более читабельной с помощью LET.
В следующем случае у нас есть фрагмент формулы, который вычисляется несколько раз, — это функция ВПР, возвращающая число остатков с другого листа. Логика формулы следующая: «Если заказано больше товаров, чем остатков на складе, то возвращается текст “Не хватает” с недостающим количеством (Заказ — Остаток). Если остатков хватает, то возвращаются текст “Останется на складе” и разница между остатками и заказанным количеством».
Получается довольно громоздкая формула, где трижды повторяется функция ВПР (то есть остаток на складе, который мы ищем этой функцией на другом листе), — она выделена серым в формуле:
=ЕСЛИ([@Заказ]>ВПР([@Номенклатура];Прайс[[Номенклатура]: [Остатки]];3;0);
"Не хватает "&([@Заказ]-ВПР([@Номенклатура];Прайс[[Номенклатура]: [Остатки]];3;0));
"Останется на складе: "&(ВПР([@Номенклатура];Прайс[[Номенклатура]: [Остатки]];3;0)-[@Заказ]))
И если присвоить ей имя с помощью LET и дальше обращаться в формуле по этому имени — будет гораздо компактнее (и важно даже не количество символов в формуле, а простота восприятия):
=LET(Остаток;ВПР([@Номенклатура];Прайс[[Номенклатура]: [Остатки]];3;0); ЕСЛИ([@Заказ]>Остаток; "Не хватает "&([@Заказ]-Остаток); "Останется на складе: "&(Остаток-[@Заказ])))
Итак, преимущества LET:
• легче воспринимать формулу, особенно спустя несколько недель или месяцев после того, как вы ее сочинили; или если с вашей формулой имеют дело другие люди — тут ввод переменных и обращение к ним по имени тоже может сделать чтение формулы более простым;
• вычисление быстрее работает (так как промежуточное выражение вычисляется только один раз).
В Google Таблицах функция LET появилась как раз в процессе написания этой книги — в феврале 2023-го.
Функция LAMBDA и вспомогательные функции
Файл с примером: LAMBDA.xlsx
Если раньше в Excel нужно было создавать собственные пользовательские функции с помощью макросов (VBA), то теперь можно делать это функцией, не залезая в код, если в вашей версии появилась новая (можно сказать, революционная) функция LAMBDA. А появилась она в Microsoft 365 в 2020–2021 годах (в зависимости от пакета обновлений), ее нет в «коробочной» версии Excel 2021.
Функция LAMBDA появилась и в Google Таблицах в 2022 году.
Синтаксис у нее такой:
=LAMBDA([переменная]; …; [переменная]; формула)
Переменных может и не быть (хотя тогда LAMBDA не имеет особого смысла, можно просто присвоить имени Excel формулу без аргументов и вызывать ее по этому имени), может быть одна или несколько. В конце последним аргументом всегда будет формула с этими переменными.
Рассмотрим на простом примере с отклонением «факт — план». Допустим, план в столбце B, факт в столбце C. Обычная формула будет выглядеть так:
=C2 / B2 — 1
А в случае с LAMBDA мы указываем переменные и формулу в общем случае:
=LAMBDA(план; факт; факт / план — 1)
После чего можно сохранить ее в диспетчере имен (Ctrl + F3) под любым именем, какое вы хотите присвоить этой функции, например “ТемпПрироста”. И дальше использовать эту функцию в пределах книги (а если хочется перенести ее в другую, можно создать пустой лист в книге и скопировать/переместить его в другую книгу — это приведет к переносу имен, а значит, и функции).
Вся мощь LAMBDA раскрывается с ее вспомогательными функциями, которые позволяют обрабатывать все значения в массиве или все строки/столбцы в массиве и применять к ним одно и то же вычисление. То есть теперь можно посчитать, например, среднее значение по каждой строке одной формулой. Или собирать данные с разных листов одной формулой, притом что этот список листов будет меняться. Давайте рассмотрим такие примеры применения LAMBDA со вспомогательными функциями.
Файл с примером: LAMBDA.xlsx
Функция MAP повторяет вычисление, описанное в функции LAMBDA, для каждого элемента в массиве. Соответственно, она возвращает массив того же размера, что и массив на входе:
=MAP(массив; LAMBDA(переменная для обозначения каждого элемента массива; вычисление))
Например, мы можем взять массив с суммами сделок (из таблицы) и умножить каждое значение на 10%. Первый аргумент функции MAP — массив с данными (здесь ссылка на столбец "Сумма" таблицы Сделки). Второй — функция LAMBDA, у которой первый аргумент — это переменная (произвольное имя, у нас — стоимость) для каждого элемента массива, а второй — вычисление с этой переменной (что мы делаем с каждым элементом из массива).
Конечно, такую задачу можно решить и обычной формулой, и формулой массива — это лишь пример, показывающий, что позволяет делать MAP.
Если массив двумерный, то и результат будет такого же размера. MAP применяет вычисление к каждому элементу массива.
Массивов может быть и несколько, тогда они перечисляются в MAP как отдельные аргументы, а последним аргументом всегда будет LAMBDA.
Файл с примером: Собираем данные с разных листов.xlsx
Google Таблица с примером:Собираем данные с разных листов
https://mif.to/hsCGH
Наша задача — собирать данные с нескольких листов, причем список листов может меняться: листы могут меняться, могут удаляться из списка, могут добавляться новые.
Пока на сводном листе, куда мы хотим собирать все данные (в примере он назван «Движение ДС»), — четыре счета, идущих не подряд в таблице. Могут добавиться новые, могут удалиться какие-то из этих.
В Google Таблицах список листов можно сделать и открытым $A$2:$A, но это может сказаться на быстродействии.
В Excel разумно делать его таблицей и ссылаться на ее столбец в формуле.
Чтобы обработать несколько листов и с каждым проделывать какие-то манипуляции, будем забирать названия листов из массива с помощью функции MAP и передавать в LAMBDA, где будет нужное нам вычисление с каждым элементом из диапазона (первого аргумента MAP).
В общем виде для нашей задачи:
=MAP(список листов; LAMBDA(sh; вычисление))
где sh — просто имя переменной для имен листов, можно задать и другое.
А что будет за вычисление, какой алгоритм?
Во-первых, нам надо будет проверять каждое значение в списке: если там пусто, то никаких манипуляций производить не нужно, можно возвращать ноль. Это можно сделать с помощью функции ЕПУСТО / ISBLANK (она возвращает ИСТИНА, если ее аргумент пустой):
=MAP(список листов; LAMBDA(sh; ЕСЛИ(ЕПУСТО(sh); 0; вычисление))
Во-вторых, надо получить ссылку на лист и на нужный диапазон на каждом листе. Чтобы сделать действующую ссылку из текста (а у нас sh — текст, название листа), нужно использовать ДВССЫЛ / INDIRECT. Допустим, нам нужно будет использовать данные в столбцах A: N на каждом листе. Соберем ссылку следующим образом: апостроф (это вполне себе текст из одного символа, так что берем его в кавычки) & название листа (sh) & (апостроф & восклицательный знак & диапазон):
ДВССЫЛ("'" & sh & "'!A: N")
Наконец, надо с полученным диапазоном произвести манипуляции: подтянуть данные с помощью ВПР / VLOOKUP, или просуммировать, или сделать еще что-то. Или просто сослаться на нужные ячейки, если структура одинаковая везде и не будет меняться. В общем, функция может быть любая, в примере ВПР по названию статьи:
=MAP(список листов;
LAMBDA(sh;
ЕСЛИ(ЕПУСТО(sh); 0;
функция(ДВССЫЛ("'" & sh & "'!A: N")))
В нашем случае с VLOOKUP / ВПР в общем виде:
=MAP(список листов;
LAMBDA(sh;
ЕСЛИ(ЕПУСТО(sh);0;
ВПР(название статьи;ДВССЫЛ("'"&sh&"'!A: N");номер столбца;0))))
С конкретными ссылками:
=MAP($A$2:$A20;
LAMBDA(sh;
ЕСЛИ(ЕПУСТО(sh);0;
ВПР($B2;ДВССЫЛ("'"&sh&"'!A: N");СТОЛБЕЦ()-1;0))))
СТОЛБЕЦ()-1 — здесь мы просто берем номер столбца, в котором стоит формула, и уменьшаем на единицу, чтобы получить номера столбцов на листе с данными (у нас там на один столбец меньше; так как другая структура данных — нет списка листов в первом столбце; понятно, что у вас структура может быть какая-то еще).
Остается просуммировать (СУММ / SUM; если, конечно, вам нужна сумма, а не среднее или что-то еще) все полученные значения, которые ВПР / VLOOKUP нам принесет со всех листов:
=СУММ(MAP($A$2:$A20;
LAMBDA(sh;
ЕСЛИ(ЕПУСТО(sh);0;
ВПР($B2;ДВССЫЛ("'"&sh&"'!A: N");СТОЛБЕЦ()-1;0)))))
Файл с примером: BYROW.xlsx