Переключение дэшборда между днями и неделями в Google Таблицах — с помощью функции SEQUENCE

Итак, вы хотите создать простой дэшборд, в котором будете агрегировать данные по неделям или дням.
И при этом хотите легко переключать режим “недели / дни" (или изменение любого другого параметра), не залезая в формулы.

В голубых ячейках выбираем период, флажок отвечает за величину интервала

Решение: сделаем флажок для переключения и будем использовать его как аргумент в формуле. Для формирования заголовком будем использовать SEQUENCE, которая позволяет возвращать таблицу с последовательностью чисел. В нашем случае числами будут даты (вы наверняка уже помните, что в Таблицах и Excel даты = числа). Начало и конец периода будет указывать в ячейках пользователь.

Выводим заголовки с датами

У функции SEQUENCE следующие аргументы. Разберем их сразу на нашем примере:

число_строк — в нашем случае одна строка, так как мы формируем заголовки из дат.

[число_столбцов] — столько, сколько дней (или недель) в периоде. Вычисляем длину периода, вычитая из последней даты первую и прибавляя единицу. В случае, если мы выбрали представление по неделям, а не дням, то все это нужно поделить на 7.

[первое_значение] — точка отсчета; берем первую дату указанного пользователем периода. Дополнительно в нашем примере мы добавили формулу, которая начинает отсчет всегда с понедельника, если выбрана опция “по неделям”. То есть если мы выбрали 16.06.2020, то отсчет начнется с этого дня при отображении по дням, но с 15.06.2020 (понедельник на этой неделе) при отображении по неделям.

[шаг] — если мы решили представлять данные по дням, то 1, иначе - 7.

На общем уровне вся наша формула будет иметь следующую структуру:
=ЕСЛИ(ячейка с флажком - переключателем периодичности;
SEQUENCE(выводящая даты по дням с шагом 1 начиная со стартовой даты);
SEQUENCE(выводящая даты по неделям с шагом 7 начиная с предшествующего выбранной дате понедельника))

То есть мы сначала проверяем, стоит ли флажок, и в зависимости от этого используем либо функцию для вывода по дням, либо функцию для вывода по неделям.

SEQUENCE по дням:
SEQUENCE(1;конец периода-начало периода+1;начало периода;1)

Формула у нас в оранжевой ячейке, она выводит массив данных в 1 строку. Так это выглядит по дням

SEQUENCE по неделям:
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)))

Первая функция ЕСЛИ проверяет наличие даты в верхней строке с заголовками, чтобы в пустых столбцах ничего не выводить. Далее мы просто суммируем нужный нам столбец за нужный период - от даты в текущем столбце (включительно) до даты в следующем (не включительно). Такая схема обеспечивает корректную сумму и когда у вас дни (берется только сумма за день из текущего столбца) и когда недели (берется от понедельника включительно до следующего понедельника не включительно).

Функция ЕСЛИ в конце этой формулы проверяет, есть ли дата справа. Она нужна только для крайнего столбца в таблице (ведь заданный вами период рано или поздно закончится, но даже для последнего столбца нужны критерии, а в ячейке справа в заголовках их не будет — так что тут мы создаем их виртуально).

Ну а главное — что теперь в вашей таблице можно менять периодичность и диапазон дат вывода данных, не меняя формул.

Файл с примером

Гифка с демонстрацией работы


Канал Google Таблицы

контакты в Телеграме:
@namokonov
@renat_shagabutdinov