Собираем данные с разных листов в Excel и Google Таблицах (список листов - динамический)
Вступительное словцо
Показали мне тут клиенты шаблон ДДС, в котором данные собираются с разных листов (где каждый лист = движение ДС по одному из счетов компании), и листов (счетов) в шаблоне 10, а может быть меньше или больше. Формула выглядит громоздкой, хотя идея правильная - берем названия листов из ячеек и через ДВССЫЛ / INDIRECT превращаем в ссылки на нужные ячейки. Непонятно, зачем для адресов самих ячеек используется некий промежуточный "Технический" лист (там просто номера столбцов и строк на нем - можно ведь было функциями СТРОКА / ROW и СТОЛБЕЦ / COLUMN пользоваться, например), ну да ладно.
Все работает на 10 листов и если их будет меньше, все тоже будет работать корректно (функция ISBLANK проверит, есть ли в ячейке название листа). Можно сделать и на большее количество листов. Но длина этой формулы будет увеличиваться пропорционально - на скриншоте всего 10 листов максимум (их названия в ячейках A3:A12.
Теперь можно такого монстра сделать гораздо компактнее, и длина формулы не будет зависеть от размера диапазона с названиями листов. Конечно, с помощью LAMBDA.
Решение задачи через 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))))column()-1 - это мы просто берем номер столбца, в котором стоит формула, и уменьшаем на единицу, чтобы получить номера столбцов на листе с данными (у нас там на один столбец меньше; так как нет списка листов; понятно, что у вас структура может быть какая-то еще)
Остается просуммировать (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?
В Excel можно колдовать точно так же, но вот только LAMBDA нет в старых версиях.
Чем можно воспользоваться в любой версии Excel - так это ссылкой на несколько листов.
Следующая формула суммирует числа из ячеек B2 на листах от "$ счет" и до "Счет в юанях" включительно:
=СУММ('$ счет:Счет в юанях'!B2)Вводить руками названия листов, апострофы и все прочее не нужно: щелкните на ярлык первого листа, зажмите Shift, щелкните на ярлык последнего и нам нем выделяйте диапазон/ячейку - все сформируется автоматом.
Но это не такая гибкая история, конечно. "От и до" значит, что эти листы (их ярлыки) идут слева направо в книге. Если какой-то из них переместится за пределы этого набора листов - ячейка с него перестанет вычисляться. Хотя есть и плюс (для каких-то ситуаций): наоборот, если после первого или до последнего появится новый лист - он попадет в вычисление.
Есть и еще один менее известный прием Excel, который может пригодиться в отдельных случаях - возможность применять символ подстановки * в ссылках на листы.
Допустим, у вас в книге много листов со словом "Расходы" в названии ("Расходы январь", "Расходы февраль", . . . ). Следующая формула позволит просуммировать ячейки A1 со всех этих листов:
=СУММ('Расходы*'!A1)Правда, в отличие от ссылки с двоеточием, звездочка в формуле не сохранится - после ввода такой формулы ссылка на лист со звездочкой превратится в формулу с отдельными ссылками:
=СУММ('Расходы январь'!A1;'Расходы февраль'!A1;'Расходы март'!A1;...)