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

Функция 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))))