Загальна формула
=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 тощо), ви можете використовувати функцію VLOOKUP в режимі приблизного збігу.
Проблема
Є кілька способів групувати час у програмі Excel. Якщо вам просто потрібно згрупувати години по годинах, зведена таблиця дуже швидка і проста. Якщо вам потрібно згрупувати час за іншими рівними сегментами по кілька годин (тобто 3 години, 4 години тощо), хорошим рішенням є використання функції FLOOR. Однак, якщо вам потрібно згрупувати час у нерівні відра, вам слід застосувати більш індивідуальний підхід. VLOOKUP у своєму режимі приблизного збігу дозволяє групувати час за спеціальними інтервалами будь-якого розміру.
Рішення
Рішення полягає в побудові таблиці пошуку, яка кожного разу "відображається" у потрібному сегменті. У першому стовпці введіть час початку сегмента. У стовпець другий введіть назву відра, який ви хочете використовувати. Таблиця повинна бути відсортована за часом початку, від найменшого до найбільшого. Нарешті, налаштуйте функцію VLOOKUP на пошук кожного разу в таблиці сегментів із приблизним збігом.
У наведеному прикладі формула n E5 має вигляд:
=VLOOKUP(D5,buckets,2,1)
D5 - значення пошуку, "відра" - іменований діапазон для G5: H8, 2 - індекс стовпця, а 1 - прапор, що забезпечує приблизну відповідність. (Ви також можете використовувати TRUE). Повне пояснення див. На цій сторінці.
Коли VLOOKUP знаходиться в режимі приблизного збігу, він відповідає найближчому значенню, яке менше або дорівнює значенню пошуку. Таким чином, ви можете уявити час вхідного пошуку як "округлений" у потрібний сегмент.
Ця формула є чудовим прикладом того, як ви можете використовувати VLOOKUP для групування даних абсолютно власними способами. Я дізнався про це від Джона Акампора, який навчався в кампусі Excel, у своїй статті про три способи групування.
