Переключение дэшборда между днями и неделями в Google Таблицах — с помощью функции SEQUENCE
Итак, вы хотите создать простой дэшборд, в котором будете агрегировать данные по неделям или дням.
И при этом хотите легко переключать режим “недели / дни" (или изменение любого другого параметра), не залезая в формулы.
Решение: сделаем флажок для переключения и будем использовать его как аргумент в формуле. Для формирования заголовком будем использовать SEQUENCE, которая позволяет возвращать таблицу с последовательностью чисел. В нашем случае числами будут даты (вы наверняка уже помните, что в Таблицах и Excel даты = числа). Начало и конец периода будет указывать в ячейках пользователь.
Выводим заголовки с датами
У функции SEQUENCE следующие аргументы. Разберем их сразу на нашем примере:
число_строк — в нашем случае одна строка, так как мы формируем заголовки из дат.
[число_столбцов] — столько, сколько дней (или недель) в периоде. Вычисляем длину периода, вычитая из последней даты первую и прибавляя единицу. В случае, если мы выбрали представление по неделям, а не дням, то все это нужно поделить на 7.
[первое_значение] — точка отсчета; берем первую дату указанного пользователем периода. Дополнительно в нашем примере мы добавили формулу, которая начинает отсчет всегда с понедельника, если выбрана опция “по неделям”. То есть если мы выбрали 16.06.2020, то отсчет начнется с этого дня при отображении по дням, но с 15.06.2020 (понедельник на этой неделе) при отображении по неделям.
[шаг] — если мы решили представлять данные по дням, то 1, иначе - 7.
На общем уровне вся наша формула будет иметь следующую структуру:=ЕСЛИ(ячейка с флажком - переключателем периодичности;
SEQUENCE(выводящая даты по дням с шагом 1 начиная со стартовой даты);
SEQUENCE(выводящая даты по неделям с шагом 7 начиная с предшествующего выбранной дате понедельника))
То есть мы сначала проверяем, стоит ли флажок, и в зависимости от этого используем либо функцию для вывода по дням, либо функцию для вывода по неделям.
SEQUENCE по дням:SEQUENCE(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)))
Первая функция ЕСЛИ проверяет наличие даты в верхней строке с заголовками, чтобы в пустых столбцах ничего не выводить. Далее мы просто суммируем нужный нам столбец за нужный период - от даты в текущем столбце (включительно) до даты в следующем (не включительно). Такая схема обеспечивает корректную сумму и когда у вас дни (берется только сумма за день из текущего столбца) и когда недели (берется от понедельника включительно до следующего понедельника не включительно).
Функция ЕСЛИ в конце этой формулы проверяет, есть ли дата справа. Она нужна только для крайнего столбца в таблице (ведь заданный вами период рано или поздно закончится, но даже для последнего столбца нужны критерии, а в ячейке справа в заголовках их не будет — так что тут мы создаем их виртуально).
Ну а главное — что теперь в вашей таблице можно менять периодичность и диапазон дат вывода данных, не меняя формул.
контакты в Телеграме:
@namokonov
@renat_shagabutdinov