Угруповання в Excel
Якщо вам потрібно групувати час у групи (тобто Групу на 2 години, групу на 3 години і т. д.), Ви можете зробити це за допомогою функції округлення, яка називається ОКРВНЗ.
У наведеному прикладі ми маємо кілька транзакцій, кожна з позначкою часу. Допустимо, ви хочете згрупувати ці транзакції в блоки по 3 години.
Це означає, що зрештою у вас будуть такі групи, як:
Так, наприклад, час 2:30 ранку, необхідно перейти о 00:00 - 3:00. Час 8:45 має йти о 6:00 - 9: 00 групу, і так далі.
Якщо ви думаєте про це, один із способів зробити це — обходити щоразу, доки він не увійде до правильної групи. Однак, на відміну від звичайного округлення, де ми можемо округлити до найближчого кратного, у цьому випадку ми хочемо округлити до найближчого кратного, починаючи з півночі.
Оскільки час – це лише десяткові числа, ви можете легко зробити це за допомогою функції ОКРВНІЗ, яка округляється до декількох, які ви надаєте (ОУРВНІЗ викликає аргумент, який представляє кілька значень «значущості»). Ще краще, ВКРВНЗ розуміє круговий час.
У наведеному прикладі формула E5:
ВКРВНЗ знає, як читати час, тому він інтерпретує три години, як десятковий еквівалент, 0,125. Потім він просто округляється щоразу до найближчого кратного 0.125
Ви можете використовувати цей самий підхід для групування часу в будь-якому стандартному сегменті, який вам подобається.
Якщо у вас є часи, які охоплюють один або кілька днів, ви можете використовувати функцію ЗАЛИШЕННЯ для вилучення тільки часу, як описано тут.
Зведені таблиці автоматично групуються в періоди 1 годину, але вони можуть автоматично групуватися в інші часові блоки. Тим не менш, використовуючи описаний тут підхід, ви можете групувати на свій розсуд, а потім виконувати дані через зведену таблицю для підсумовування.
Угруповання часу в нерівні блоки
= ВПР (час; таблиця блоків; стовпець; ІСТИНА)
Якщо вам потрібно групувати час у блоки, але вони не рівні (тобто з 12:00 до 7:00, з 7:00 до 12:00 і т. д.), Ви можете зробити це за допомогою функції ВПР, налаштованої на зразкову відповідність.
Є кілька способів згрупувати час у Excel. Якщо вам просто потрібно згрупувати час щогодини, зведена таблиця буде дуже швидкою і легкою. Якщо вам потрібно групувати час в інші рівні блоки протягом кількох годин (тобто 3 години, 4 години і т. д.), хорошим рішенням є використання функції ОКРВНЗ. Однак, якщо вам потрібно групувати час у різні сегменти, вам необхідно використовувати більш індивідуальний підхід. ВПР, в режимі приблизного збігу, дозволяє групувати інтервали часу в інтервали користувача будь-якого розміру.
Рішення полягає в тому, щоб створити таблицю пошуку, яка відображає кожен раз потрібний блок. У першому стовпці введіть час початку сегмента. У другому стовпці введіть ім'я блоку, який потрібно використовувати. Таблиця має бути відсортована за часом початку, від найменшої до найбільшої.
Нарешті, конфігуруйте ВПР для пошуку щоразу в таблиці блоку з приблизною відповідністю.
У наведеному прикладі формула E5:
D5 - це значення пошуку, "блоки" - іменований діапазон для G5: H8, 2 - це індекс стовпця, а 1 - прапор, який забезпечує зразкову відповідність. (Ви також можете використовувати ІСТИНА).
Коли ВВР перебуває в режимі приблизного збігу, він відповідає найближчому значенню, яке менше або дорівнює значенню пошуку. Таким чином, ви можете думати про те, що час пошуку буде «округлено вниз» в правильний блок.
Ця формула є чудовим прикладом того, як ви можете використовувати ВПР для групування даних у способах, що повністю настроюються.
Угруповання та розгрупування даних в Excel
При обробці великого обсягу даних досить часто потрібне їхнє впорядкування. Спеціально для цього в Excel передбачені різні функції, однією з яких є угруповання. З її допомогою, як випливає з назви, можна згрупувати дані, а також приховати неактуальну інформацію. Давайте розберемося, як це працює.
Налаштовуємо параметри функції
Щоб зрештою отримати бажаний результат, спочатку потрібно виконати налаштування самої функції. Для цього виконуємо такі кроки:
- Перейшовши у вкладку "Дані" клацаємо по кнопці "Структура" і в переліку команд, що відкрився - по невеликому значку у вигляді стрілки, спрямованої по діагоналі вниз.
- На екрані з'явиться маленьке вікно з параметрами функції. Тут ми можемо налаштувати відображення результатів. Ставимо галочки навпроти потрібних опцій (в т.ч. автоматичні стилі) і натискаємо кнопку OK. Примітка: розташування підсумкових даних у рядках під даними багатьом здається незручним, тому цей параметр можна вимкнути.
- Все готове, тепер можемо перейти, безпосередньо, до самого угруповання даних.
Групуємо дані по рядках
Для початку давайте розглянемо, як можна згрупувати рядки:
- Вставляємо новий рядок над або під рядками, які хочемо згрупувати (залежить від того, який вид розташування підсумків по рядках ми вибрали). Як це зробити, читайте у нашій статті – “Як додати новий рядок до Excel“.
- У самій лівій комірці доданого рядка пишемо назву, яку хочемо присвоїти групі.
- Будь-яким зручним способом, наприклад, за допомогою затиснутої лівої кнопки миші проводимо виділення осередків рядків (крім підсумкової), які потрібно згрупувати. У вкладці "Дані" клацаємо по кнопці "Структура" і в списку вибираємо функцію "Групувати". Натиснути потрібно саме за значком команди, а не за її назвою. Якщо натиснути на останнє (зі стрілкою вниз), відкриється ще одне підменю, в якому слід натиснути на однойменну кнопку.
- У вікні, що з'явилося, відзначаємо пункт "рядки" (має бути обраний за замовчуванням) і підтверджуємо дію натисканням OK. Примітка: Якщо замість осередків виділити всі рядки повністю на вертикальній панелі координат, а потім застосувати угруповання, то проміжного вікна з вибором рядка або стовпця не буде, оскільки програма відразу розуміє, що саме необхідно зробити.
- Група створена, про що свідчать смужка, що з'явилася на панелі координат, зі знаком "мінус". Це означає, що згруповані дані розкрито. Щоб їх приховати, натискайте по мінусу або кнопці з цифрою “1” (Найвищий рівень угруповання).
- Тепер рядки приховані. Щоб їх назад розкрити, натискаємо по значку "плюсу", який з'явився замість "мінуса" (або за кнопкою “2”).
Групуємо стовпці
Щоб згрупувати стовпці, дотримуємося приблизно такого ж алгоритму дій, як описано вище:
- Вставляємо стовпець праворуч або ліворуч від групованих – залежить від вибраного параметра в налаштуваннях функції. Докладніше про те, як це зробити, читайте у нашій статті – “Як вставити стовпець у таблицю Ексель“.
- Пишемо назву у найвищому осередку нового стовпця.
- Виділяємо комірки стовпців, що групуються (за винятком доданого) і застосовуємо функцію угруповання.
- Ставимо позначку напроти варіанта "стовпці" та клікам OK. Примітка: як і у випадку з групуванням рядків, при виділенні стовпців повністю на горизонтальній панелі координат, угруповання даних буде виконано відразу, минаючи проміжне вікно з вибором елементів.
- Завдання успішно виконано.
Створюємо багаторівневе угруповання
Можливості програми дозволяють виконувати як однорівневі, і багаторівневі угруповання. Ось як це робиться:
- У розкритому стані головної групи, усередині якої планується створити ще одну, виконуємо дії, розглянуті в розділах вище, залежно від того, з чим ми працюємо – з рядками або стовпцями.
- Таким чином, ми отримали багаторівневе угруповання.
Розгруповуємо дані
Якщо раніше виконане угруповання стовпців або рядків більше не потрібне або потрібно виконати його інакше, можна скористатися зворотною функцією – "Розгрупувати":
- Виробляємо виділення згрупованих елементів, після чого все в тій же вкладці "Дані" у групі інструментів "Структура" обираємо команду "Розгрупувати". Тиснемо саме за значком, а не за назвою.
- У вікні ставимо позначку навпроти необхідного пункту (у нашому випадку - "рядки") і натискаємо OK. Примітка: у разі багаторівневого угрупування чи наявності кількох груп даних, кожну з них необхідно розформувати окремо.
- Ось і все, що потрібно зробити.
Висновок
Угруповання даних виконується у кілька кліків і вимагає особливих навичок у роботі з програмою, проте, цей прийом дозволяє суттєво заощадити час, коли доводиться мати справу з великим обсягом інформації. Це робить функцію однією з найкорисніших і незамінних у Excel.