☰

1. Получить названия первого и последнего листов.

tag-icon • tag-icon microsoft-excel worksheet-function
1. Получить названия первого и последнего листов.

У меня есть файл, в котором данные вводятся в отдельные рабочие листы (TAB) каждый день. Макет идентичный, я ищу решение, чтобы взять динамическое среднее значение за последние 1-3 месяца из этих отдельных TAB. Каждый день создается новый TAB, делать это вручную каждый месяц занимает слишком много времени. Есть предложения?

решение1

Вы можете попробовать с данными > Консолидация или 3D-формула

Пример: у вас есть следующие листы Янв Фев Мар Резюме

Предположим, что на каждом листе у вас есть некоторые числа в формате A1.

Перейти к сводному листу, ввести следующую формулу в ячейку A1 =average('Jan:Mar'!A1)

решение2

Для этого выполним 2 шага:

1. Получить названия первого и последнего листов.

Насколько я понимаю, вы добавляете новый лист для каждого дня. Вам нужно будет убедиться, что названия листов отформатированы одинаково. Например, предположим, что формат: ДД-ММ-ГГ (то есть 1 августа 2020 года будет выглядеть как 01-08-20).

На этом этапе мы хотели бы создатьтекст диапазона листаза последние 3 месяца (например:01-03-20:01-01-20), который будет использоваться формулой INDIRECT для создания 3D-формулы (на втором шаге).

Предположим, чтоДАТАдержитпоследнийсоответствующая дата (так что это может быть ссылка на другую ячейку или формула типа СЕГОДНЯ() или СЕГОДНЯ()-1 и т. д.). Теперь решите, что такоепервыйсоответствующая дата. Если это 3*30 дней до последней даты, используйте:

=LDATE-3*30

Или, если это должно быть ровно за 3 месяца до ДАТЫ, используйте:

=DATE(YEAR(LDATE),MONTH(LDATE)-3,DAY(LDATE))

Давайте назовем это первое свиданиеФДАТЕ.

Наконец, это будет наш отформатированный текст диапазона листов (обратите внимание, что предполагается, что вы отформатировали имена листов как ДД-ММ-ГГ, просто измените на любой другой формат, если необходимо):

="'"&TEXT(LDATE,"DD-MM-YY")&":"&TEXT(FDATE,"DD-MM-YY")&"'!"

Теперь значение должно выглядеть так:«14-07-20:14-04-20»!

Давайте назовем весь диапазон какШИТРАНЖ.

2. КОСВЕННАЯ + 3d Формула

Функция INDIRECT возвращает допустимую ссылку из заданной текстовой строки, а 'Лист1:Лист4'!A1:B2 — это диапазон всех ячеек A1:B2 на всех листах от Sheet1 до Sheet4.

Итак, следующее выражение даст вам среднее значение вашего динамического диапазона (предположим, например, что вам нужно среднее значение всех ячеек A3):

=AVERAGE(INDIRECT(SHEETRANGE&"A3"))

Или, если мы хотим, чтобы все это происходило в ячейке (за исключением LDATE, которую вы должны определить самостоятельно):

=AVERAGE(INDIRECT("'"&TEXT(LDATE,"DD-MM-YY")&":"&TEXT(DATE(YEAR(LDATE),MONTH(LDATE)-3,DAY(LDATE)),"DD-MM-YY")&"'!"&"A3"))

Удачи!

Связанный контент