Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов

отклонением «факт — план». Допустим, план в столбце B, факт в столбце C. Обычная формула будет выглядеть так:

=C2 / B2 — 1

А в случае с LAMBDA мы указываем переменные и формулу в общем случае:

=LAMBDA(план; факт; факт / план — 1)

После чего можно сохранить ее в диспетчере имен (Ctrl + F3) под любым именем, какое вы хотите присвоить этой функции, например “ТемпПрироста”. И дальше использовать эту функцию в пределах книги (а если хочется перенести ее в другую, можно создать пустой лист в книге и скопировать/переместить его в другую книгу — это приведет к переносу имен, а значит, и функции).

Вся мощь LAMBDA раскрывается с ее вспомогательными функциями, которые позволяют обрабатывать все значения в массиве или все строки/столбцы в массиве и применять к ним одно и то же вычисление. То есть теперь можно посчитать, например, среднее значение по каждой строке одной формулой. Или собирать данные с разных листов одной формулой, притом что этот список листов будет меняться. Давайте рассмотрим такие примеры применения LAMBDA со вспомогательными функциями.

ФУНКЦИЯ MAP: ОБРАБАТЫВАЕМ КАЖДЫЙ ЭЛЕМЕНТ МАССИВА

Файл с примером: LAMBDA.xlsx

Функция MAP повторяет вычисление, описанное в функции LAMBDA, для каждого элемента в массиве. Соответственно, она возвращает массив того же размера, что и массив на входе:

=MAP(массив; LAMBDA(переменная для обозначения каждого элемента массива; вычисление))

Например, мы можем взять массив с суммами сделок (из таблицы) и умножить каждое значение на 10%. Первый аргумент функции MAP — массив с данными (здесь ссылка на столбец "Сумма" таблицы Сделки). Второй — функция LAMBDA, у которой первый аргумент — это переменная (произвольное имя, у нас — стоимость) для каждого элемента массива, а второй — вычисление с этой переменной (что мы делаем с каждым элементом из массива).

Конечно, такую задачу можно решить и обычной формулой, и формулой массива — это лишь пример, показывающий, что позволяет делать MAP.

Если массив двумерный, то и результат будет такого же размера. MAP применяет вычисление к каждому элементу массива.

Массивов может быть и несколько, тогда они перечисляются в MAP как отдельные аргументы, а последним аргументом всегда будет LAMBDA.

Пример применения MAP: собираем данные с разных листов (список листов динамический)

Файл с примером: Собираем данные с разных листов.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: ОБРАБАТЫВАЕМ КАЖДУЮ СТРОКУ МАССИВА

Файл с примером: BYROW.xlsx

Функция BYROW позволяет последовательно обращаться к каждой строке в массиве.

Ее синтаксис:

=BYROW(диапазон; LAMBDA (переменная для обращения к каждой строке); вычисление с этой переменной))

То есть мы можем производить вычисления не с отдельной ячейкой и не со всем массивом, а с каждой строкой последовательно (на выходе мы получим массив значений в один столбец и с таким же числом строк, сколько в исходном диапазоне, — это будет результат вычисления с каждой строкой). Де-факто получается цикл — мы обрабатываем каждую строку последовательно.

Аналогично работает функция BYCOL — там, соответственно, будет обрабатываться каждый столбец массива.

Давайте посмотрим на пример. У нас диапазон C2:N13, в котором есть продажи за 12 месяцев. Посчитаем средние продажи в каждой строке одной формулой:

=BYROW(C2:N13;LAMBDA(строка;СРЗНАЧ(строка)))