Собираем данные с разных листов в Excel и Google Таблицах (список листов - динамический)

Показали мне тут клиенты шаблон ДДС, в котором данные собираются с разных листов (где каждый лист = движение ДС по одному из счетов компании), и листов (счетов) в шаблоне 10, а может быть меньше или больше. Формула выглядит громоздкой, хотя идея правильная — берем названия листов из ячеек и через ДВССЫЛ / INDIRECT превращаем в ссылки на нужные ячейки. Непонятно, зачем для адресов самих ячеек используется некий промежуточный «Технический» лист (там просто номера столбцов и строк на нем — можно ведь было функциями СТРОКА / ROW и СТОЛБЕЦ / COLUMN пользоваться, например), ну да ладно.

Все работает на 10 листов и если их будет меньше, все тоже будет работать корректно (функция ISBLANK проверит, есть ли в ячейке название листа). Можно сделать и на большее количество листов. Но длина этой формулы будет увеличиваться пропорционально — на скриншоте всего 10 листов максимум (их названия в ячейках A3: A12.

Теперь можно такого монстра сделать гораздо компактнее, и длина формулы не будет зависеть от размера диапазона с названиями листов. Конечно, с помощью LAMBDA.

Итак, наша задача — собирать данные с нескольких листов, причем список листов может меняться — листы могут меняться, могут удаляться из списка, могут добавляться новые. Этот и следующий скриншоты в Google Таблицах (и пример формулы там же), но в Excel все аналогично (разве что список листов можно сделать в виде «умной» таблицы, а не открытого диапазона). Что еще можно сделать в Excel — скажу ниже.

Чтобы обработать несколько листов и с каждым проделывать какие-то манипуляции, будем забирать названия листов из массива (можно взять с запасом, например, A2: A20, а можно вообще открытый A2: A, если не знаете даже примерно, сколько листов может быть — но в последнем случае формулы могут подтормаживать) с помощью MAP и передавать в LAMBDA, где будет нужное нам вычисление.

В общем виде:

=MAP (список листов; LAMBDA (sh; вычисление))

где sh — просто название переменной для имен листов.

А что будет за вычисление, какой алгоритм?

Во-первых, нам надо будет проверять каждое значение в списке — если там пусто, то никаких манипуляций производить не нужно, можно возвращать ноль. Это можно с помощью ISBLANK / ЕПУСТО:

=MAP (список листов; LAMBDA (sh; IF (ISBLANK (sh); 0; вычисление))

=MAP (список листов; LAMBDA (sh; ЕСЛИ (ЕПУСТО (sh); 0; вычисление))

Во-вторых, надо получить ссылку на лист и на нужный диапазон на каждом листе. Чтобы сделать действующую ссылку из текста (а у нас sh — текст, название листа), нужно использовать INDIRECT / ДВССЫЛ. Допустим, нам нужно будет использовать данные в столбцах A: N на каждом листе. Соберем ссылку следующим образом: апостроф (это вполне себе текст из одного символа, так что берем его в кавычки) & название листа (sh) & (апостроф & восклицательный знак & диапазон).

INDIRECT ("'" & sh & "'!A:N")

ДВССЫЛ ("'" & sh & "'!A:N")

Наконец, надо с полученным диапазоном произвести манипуляции — «подтянуть» данные с помощью ВПР / VLOOKUP, или просуммировать, или еще что сделать. Или просто сослаться на нужные ячейки, если структура одинаковая везде и не будет меняться. В общем, функция может быть любая, в примере ВПР’им по названию статьи (обратите внимание: порядок статей на разных листах разный, поэтому ВПР, а не прямая ссылка на ячейку).

=MAP (список листов;
LAMBDA (sh;
IF (ISBLANK (sh); 0; функция (INDIRECT ("'" & sh & "'!A:N")))

В нашем случае с VLOOKUP / ВПР в общем виде:

=MAP (список листов;
LAMBDA (sh;
IF (ISBLANK (sh);0;
VLOOKUP (название статьи;INDIRECT ("'"&sh&"'!A:N");номер столбца;0))))

С конкретными ссылками:

=MAP ($A$ 2:$A20;
LAMBDA (sh;
IF (ISBLANK (sh);0;
VLOOKUP ($B2;INDIRECT ("'"&sh&"'!A:N");column ()-1;0))))

Остается просуммировать (SUM / СУММ; если вам нужна сумма, а не среднее или что-то еще, конечно) все полученные значения, которые VLOOKUP нам принесет со всех листов:

=SUM (MAP ($A$ 2:$A20;
LAMBDA (sh;
IF (ISBLANK (sh);0;
VLOOKUP ($B2;INDIRECT ("'"&sh&"'!A:N");column ()-1;0)))))

Повторюсь — список листов можно сделать и открытым $A$ 2:$A, но на это может сказаться на быстродействии.

В Excel можно колдовать точно так же, но вот только LAMBDA нет в старых версиях.

Чем можно воспользоваться в любой версии Excel — так это ссылкой на несколько листов.

Следующая формула суммирует числа из ячеек B2 на листах от «$ счет» и до «Счет в юанях» включительно:

=СУММ ('$ счет: Счет в юанях'!B2)

Вводить руками названия листов, апострофы и все прочее не нужно: щелкните на ярлык первого листа, зажмите Shift, щелкните на ярлык последнего и нам нем выделяйте диапазон/ячейку — все сформируется автоматом.

Но это не такая гибкая история, конечно. «От и до» значит, что эти листы (их ярлыки) идут слева направо в книге. Если какой-то из них переместится за пределы этого набора листов — ячейка с него перестанет вычисляться. Хотя есть и плюс (для каких-то ситуаций): наоборот, если после первого или до последнего появится новый лист — он попадет в вычисление.

Есть и еще один менее известный прием Excel, который может пригодиться в отдельных случаях — возможность применять символ подстановки * в ссылках на листы.

Допустим, у вас в книге много листов со словом «Расходы» в названии («Расходы январь», «Расходы февраль»,.. .). Следующая формула позволит просуммировать ячейки A1 со всех этих листов:

=СУММ ('Расходы*'!A1)

Правда, в отличие от ссылки с двоеточием, звездочка в формуле не сохранится — после ввода такой формулы ссылка на лист со звездочкой превратится в формулу с отдельными ссылками:

=СУММ ('Расходы январь'!A1;'Расходы февраль'!A1;'Расходы март'!A1;…)

---
Магия табличных формул: от A1 до LAMBDA - мой курс по подписке на Sponsr.ru. Присоединяйтесь! Новые подробные уроки с примерами - каждую неделю.