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