Группировка в 3-часовые группы
= ОКРВНИЗ(время; «3:00»)
Если вам нужно группировать время в группы (т. е. Группу на 2 часа, группу на 3 часа и т. д.), Вы можете сделать это с помощью функции округления, называемой ОКРВНИЗ.
В показанном примере у нас есть несколько транзакций, каждая с меткой времени. Допустим, вы хотите сгруппировать эти транзакции в блоки по 3 часа.
Это означает, что в конечном итоге у вас будут такие группы, как:
00:00 — 3: 00
3:00 — 6:00
6:00 — 9:00
9:00 — 12:00
Так, например, время 2:30 утра, необходимо перейти в 00:00 — 3:00. Время 8:45 должно идти в 6:00 — 9: 00 группу, и так далее.
Если вы думаете об этом, один из способов сделать это — обходить каждый раз, пока он не войдет в правильную группу. Однако, в отличие от обычного округления, где мы можем округлить до ближайшего кратного, в этом случае мы хотим округлить до ближайшего кратного, начиная с полуночи.
Поскольку время — это всего лишь десятичные числа, вы можете легко сделать это с помощью функции ОКРВНИЗ, которая округляется до нескольких, которые вы предоставляете (ОУРВНИЗ вызывает аргумент, который представляет несколько значений «значимости»). Еще лучше, ОКРВНИЗ понимает круговое время.
В показанном примере формула в E5:
= ОКРВНИЗ(D5; «3:00»)
ОКРВНИЗ знает, как читать время, поэтому он интерпретирует 3:00, как десятичный эквивалент, 0,125. Затем он просто округляется каждый раз до ближайшего кратного 0.125
Вы можете использовать этот же подход для группирования времени в любом стандартном сегменте, который вам нравится.
Если у вас есть времена, которые охватывают один или несколько дней, вы можете использовать функцию ОСТАТ для извлечения только времени, как описано здесь.
Сводные таблицы автоматически группируются в периоды 1 час, но они не могут автоматически группироваться в другие временные блоки. Тем не менее, используя описанный здесь подход, вы можете группировать по своему усмотрению, а затем выполнять данные через сводную таблицу для суммирования.
Группировка времени в неравные блоки
= ВПР (время; таблица блоков; столбец; ИСТИНА)
Если вам нужно группировать время в блоки, но они не равны (т. Ее. С 12:00 до 7:00, с 7:00 до 12:00 и т. д.), Вы можете сделать это с помощью функции ВПР, настроенной на примерное соответствие.
Есть несколько способов сгруппировать время в Excel. Если вам просто нужно сгруппировать время по часам, сводная таблица будет очень быстрой и легкой. Если вам нужно группировать время в другие равные блоки в течение нескольких часов (то есть 3 часа, 4 часа и т. д.), Хорошим решением является использование функции ОКРВНИЗ. Однако, если вам нужно группировать время в неодинаковые сегменты, вам необходимо использовать более индивидуальный подход. ВПР, в режиме приблизительного совпадения, позволяет группировать интервалы времени в пользовательские интервалы любого размера.
Решение состоит в том, чтобы создать таблицу поиска, которая «отображает» каждый раз нужный блок. В первом столбце введите время начала для сегмента. Во втором столбце введите имя блока, который вы хотите использовать. Таблица должна быть отсортирована по времени начала, от наименьшей до наибольшей.
Наконец, сконфигурируйте ВПР для поиска каждый раз в таблице блока с приблизительным соответствием.
В показанном примере формула в E5:
=ВПР(D5;$G$5:$H$8;2;1)
D5 — это значение поиска, «блоки» — именованный диапазон для G5: H8, 2 — это индекс столбца, а 1 — флаг, который обеспечивает примерное соответствие. (Вы также можете использовать ИСТИНА).
Когда ВПР находится в режиме приблизительного совпадения, он соответствует ближайшему значению, которое меньше или равно значению поиска. Таким образом, вы можете думать о том, что входящее время поиска будет «округлено вниз» в правильный блок.
Эта формула является отличным примером того, как вы можете использовать ВПР для группировки данных в полностью настраиваемых способах.