Уменьшение/лямбда-итерация в Excel
Я хотел бы повторить формулу с помощью сокращения/лямбды, чтобы вы могли просто изменить имя первого листа, и оно обновится соответствующим образом.
В настоящее время я использую формулу следующим образом:
=DROP(UNIQUE(VSTACK(
FILTER('2023-12'!A:A,'2023-12'!A:A<>""),
FILTER('2023-11'!A:A,'2023-11'!A:A<>""),
FILTER('2023-10'!A:A,'2023-10'!A:A<>""),
FILTER('2023-09'!A:A,'2023-09'!A:A<>""),
FILTER('2023-08'!A:A,'2023-08'!A:A<>""),
FILTER('2023-07'!A:A,'2023-07'!A:A<>""),
FILTER('2023-06'!A:A,'2023-06'!A:A<>""),
FILTER('2023-05'!A:A,'2023-05'!A:A<>""),
FILTER('2023-04'!A:A,'2023-04'!A:A<>""),
FILTER('2023-03'!A:A,'2023-03'!A:A<>""),
FILTER('2023-02'!A:A,'2023-02'!A:A<>""),
FILTER('2023-01'!A:A,'2023-01'!A:A<>""))),5)
Подобно тому, как вы можете СУММИРОВАТЬ по нескольким страницам, например =SUM('2023-12:2023-01'!A1), я хотел бы использовать REDUCE LAMBDA, чтобы можно было обновить первый лист с 2023-12 до 2024-01. и т. д., и результат будет соответствующим образом обновлен.
Идея состоит в том, что листы содержат все элементы в столбце A, где новые элементы добавляются почти ежемесячно, а результирующий столбец должен содержать все результаты со всех листов в уникальном списке.
Я надеюсь, что это имеет достаточный смысл.
Я попробовал приведенную выше формулу, и хотя она работает, мне приходится добавлять новую строку в формулу каждый раз, когда я добавляю новый период. В конечном итоге это станет очень длинным и сделает формулу огромной.
В идеале мы хотели бы сохранить отдельные страницы для данных. Есть ли хороший способ создать агрегированную страницу? =FILTER(VSTACK('2024-1:2023-1'!A:A),VSTACK('2024-1:2023-1'!A:A)<>"") выдаст #NUM! из-за превышения количества строк в части формулы VSTACK.
Ответы 1
Я не знаю, как сделать это без использования изменчивой функции Indirect, но если это разрешено, с помощью сокращения это будет выглядеть так:
=LET(startDate,DATE(2023,12,1),
seq,SEQUENCE(3,1,0,-1),
DROP(UNIQUE(REDUCE("",seq,LAMBDA(a,c,VSTACK(a,TOCOL(INDIRECT("'"&TEXT(EDATE(startDate,c),"YYYY-MM")&"'!"&"A:A"),1))))),1))
где дату начала и количество листов можно изменить по мере необходимости.
Использование SheetNames через диспетчер имен в Excel позволило нам полностью автоматизировать процесс, избавив от необходимости вручную корректировать формулы. Используя диспетчер имен для определения «Имен листов» по формуле =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),""), мы создали массив, содержащий все имена листов. Отфильтровав этот массив так, чтобы он включал только имена со знаком «-», мы можем легко интегрировать его в вашу формулу, упрощая процесс. Я ценю вашу помощь в этом и спасибо, что указали нам правильное направление.
Можете ли вы вместо этого добавить новые данные в существующую таблицу? Возможно, сначала потребуется склеить их все в одну, но в сочетании со столбцом за период это значительно упростит написание и обслуживание формул.