Итак, вы хотите создать простой дэшборд, в котором будете агрегировать данные по неделям или дням.
И при этом хотите легко переключать режим «недели / дни» (или изменение любого другого параметра), не залезая в формулы.
И при этом хотите легко переключать режим «недели / дни» (или изменение любого другого параметра), не залезая в формулы.
В голубых ячейках выбираем период, флажок отвечает за величину интервала
Решение: сделаем флажок для переключения и будем использовать его как аргумент в формуле. Для формирования заголовком будем использовать SEQUENCE, которая позволяет возвращать таблицу с последовательностью чисел. В нашем случае числами будут даты (вы наверняка уже помните, что в Таблицах и Excel даты = числа). Начало и конец периода будет указывать в ячейках пользователь.
Выводим заголовки с датами
У функции SEQUENCE следующие аргументы. Разберем их сразу на нашем примере:
число_строк — в нашем случае одна строка, так как мы формируем заголовки из дат.
[число_столбцов] — столько, сколько дней (или недель) в периоде. Вычисляем длину периода, вычитая из последней даты первую и прибавляя единицу. В случае, если мы выбрали представление по неделям, а не дням, то все это нужно поделить на 7.
[первое_значение] — точка отсчета; берем первую дату указанного пользователем периода. Дополнительно в нашем примере мы добавили формулу, которая начинает отсчет всегда с понедельника, если выбрана опция «по неделям». То есть если мы выбрали 16.06.2020, то отсчет начнется с этого дня при отображении по дням, но с 15.06.2020 (понедельник на этой неделе) при отображении по неделям.
[шаг] — если мы решили представлять данные по дням, то 1, иначе — 7.
На общем уровне вся наша формула будет иметь следующую структуру:
=ЕСЛИ (ячейка с флажком — переключателем периодичности; SEQUENCE (выводящая даты по дням с шагом 1 начиная со стартовой даты); SEQUENCE (выводящая даты по неделям с шагом 7 начиная с предшествующего выбранной дате понедельника))
=ЕСЛИ (ячейка с флажком — переключателем периодичности; SEQUENCE (выводящая даты по дням с шагом 1 начиная со стартовой даты); SEQUENCE (выводящая даты по неделям с шагом 7 начиная с предшествующего выбранной дате понедельника))
То есть мы сначала проверяем, стоит ли флажок, и в зависимости от этого используем либо функцию для вывода по дням, либо функцию для вывода по неделям.
SEQUENCE по дням:
SEQUENCE (1;конец периода-начало периода+1;начало периода;1)
SEQUENCE (1;конец периода-начало периода+1;начало периода;1)
Формула у нас в оранжевой ячейке, она выводит массив данных в 1 строку. Так это выглядит по дням
SEQUENCE по неделям:
SEQUENCE (1;(конец периода-начало периода+1)/7+1;начало периода-ДЕНЬНЕД (начало периода;2)+1;7))
SEQUENCE (1;(конец периода-начало периода+1)/7+1;начало периода-ДЕНЬНЕД (начало периода;2)+1;7))
А так — по неделям
Выводим данные
Ну, а чтобы собственно вывести данные по полученным заголовкам, можно воспользоваться функциями СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН, FILTER и другими в зависимости от ваших задач. В примере — СУММЕСЛИМН (суммируем данные по нескольким показателям по выбранным датам).
=ЕСЛИ (D$ 5="";"";СУММЕСЛИМН ('Какие-то данные'!$B:$B;'Какие-то данные'!$A:$A;">="&D$ 5;'Какие-то данные'!$A:$A;"<"&ЕСЛИ (E$ 5="";D$ 5+(D$ 5-C$ 5);E$ 5)))
Первая функция ЕСЛИ проверяет наличие даты в верхней строке с заголовками, чтобы в пустых столбцах ничего не выводить. Далее мы просто суммируем нужный нам столбец за нужный период — от даты в текущем столбце (включительно) до даты в следующем (не включительно). Такая схема обеспечивает корректную сумму и когда у вас дни (берется только сумма за день из текущего столбца) и когда недели (берется от понедельника включительно до следующего понедельника не включительно).
Функция ЕСЛИ в конце этой формулы проверяет, есть ли дата справа. Она нужна только для крайнего столбца в таблице (ведь заданный вами период рано или поздно закончится, но даже для последнего столбца нужны критерии, а в ячейке справа в заголовках их не будет — так что тут мы создаем их виртуально).
Ну, а главное — что теперь в вашей таблице можно менять периодичность и диапазон дат вывода данных, не меняя формул.
