Общая формула
=VLOOKUP(time,bucket_table,column,TRUE)
Резюме
Чтобы сгруппировать время в периоды, которые не равны (например, с 12:00 до 7:00, с 7:00 до 12:00 и т. Д.), Вы можете использовать функцию VLOOKUP. В показанном примере формула n E5 имеет вид:
=VLOOKUP(D5,buckets,2,1)
Объяснение
Если вам необходимо сгруппировать время в сегменты разного размера (например, с 12:00 до 7:00, с 7:00 до 12:00 и т. Д.), Вы можете использовать функцию ВПР в режиме приблизительного совпадения.
Эта проблема
Есть несколько способов группировать время в Excel. Если вам просто нужно сгруппировать время по часам, можно легко и быстро создать сводную таблицу. Если вам нужно сгруппировать время в другие равные периоды по несколько часов (например, 3 часа, 4 часа и т. Д.), Хорошим решением будет использование функции FLOOR. Однако, если вам нужно сгруппировать время в неравные сегменты, вам нужно использовать более индивидуальный подход. ВПР в режиме приблизительного совпадения позволяет группировать время в настраиваемые интервалы любого размера.
Решение
Решение состоит в том, чтобы создать справочную таблицу, которая каждый раз «отображается» в нужную корзину. В первом столбце введите время начала периода. Во втором столбце введите имя сегмента, который вы хотите использовать. Таблица должна быть отсортирована по времени начала от наименьшего к наибольшему. Наконец, настройте функцию ВПР, чтобы каждый раз искать в таблице сегментов приблизительное совпадение.
В показанном примере формула n E5 имеет вид:
=VLOOKUP(D5,buckets,2,1)
D5 - это значение подстановки, «buckets» - это именованный диапазон для G5: H8, 2 - индекс столбца, а 1 - флаг, позволяющий приблизительное соответствие. (Вы также можете использовать TRUE). См. Эту страницу для полного объяснения.
Когда функция VLOOKUP находится в режиме приблизительного соответствия, она соответствует ближайшему значению, которое меньше или равно значению поиска. Таким образом, вы можете думать о времени входящего поиска как о «округлении» до нужного сегмента.
Эта формула - отличный пример того, как вы можете использовать ВПР для группировки данных полностью настраиваемыми способами. Я узнал об этом от Джона Акампора из кампуса Excel в его статье о трех способах группировки времени.
