Excel: групування у зведених таблицях
Вітаю всіх працівників «Б & K»! У мене запитання щодо зведених таблиць Excel. Чи можна в межах одного зведеного звіту перераховувати дані з різним ступенем деталізації? Наприклад, побудувати таблицю з підсумками за днями, а потім перерахувати її за місяцями, кварталами, в цілому за рік тощо. І як це зробити? Наперед завдячую.
З повагою, С. Цимбалюк, м. Харків
Відповідає Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства
Для вирішення такого завдання є кілька способів. Перший — додати до вихідних даних робоче поле, так званий робочий ключ, і лише тоді будувати зведений звіт. Наприклад, у вас є база даних з колонкою «
Дата». Інформацію в ній подано з точністю до дня. Ви створюєте колонку «Месяц», де записуєте формулу, що виділяє з дати номер місяця. І тільки після цього будуєте зведений звіт, вибравши як поле групування «Месяц». Таким чином, у результаті ви отримаєте підсумкові дані з деталізацією за місяцями. Це один підхід, що має багато переваг, але є й недолік: конкретна реалізація такої схеми залежить від вихідних даних. Не бачачи реальної бази даних, важко запропонувати універсальний метод побудови робочого ключа.Але є й інший спосіб, що спирається виключно на можливості зведених таблиць MS Excel. У цьому підході теж є свої обмеження, але вони (на мій погляд) незначні. Саме на способі групування засобами зведених таблиць ми й зупинимося докладніше.
Отже, є база даних, зображена на рис. 1. У ній наведено відомості про реалізацію оргтехніки (а конкретніше, друкувальних пристроїв). Кожен рядок бази описує один продаж. З кожного продажу нам відомі:
— номер замовлення (поле «
Заказ»);— дата оформлення замовлення (поле «
Дата»);— назва пристрою (поле «
Наименование»);— вартість пристрою в конкретному замовленні (поле «
Цена»);— кількість замовлених примірників (поле «
Кол.»);— загальна сума по рядку замовлення (поле «
Сумма»).Я хочу дізнатися, товари якої цінової групи мають максимальний попит. Для цього я маю вибрати з бази всі ціни, розбити їх на групи (!) і підрахувати обсяг замовлень за кожною групою. Для вирішення такого завдання нам якраз і знадобиться функція групування, причому групуватимемо числові значення.
Групування числових значень
Перш ніж застосувати групування, потрібно мати, що групувати. Тому насамперед будуємо зведену таблицю:
1) викликаємо «
Данные → Сводная таблица»;2) на запит про джерело даних вибираємо варіант «
в списке или базе данных Microsoft Office Excel»;3) у відповідь на пропозицію вказати діапазон із даними виділяємо на робочому листі «
БД» (рис. 1) колонки з «А1» по «F8200» включно. Жодного порожнього рядка в базі даних бути не повинно!4) натискуємо на кнопку «
Далее». Excel покаже вікно з пропозицією визначити параметри зведеної таблиці чи побудувати макет. Натискуємо на кнопку «Макет»;5) в області рядків ставимо поле «
Цена», до області даних переносимо поле «Сумма»;6) двічі клацаємо мишею на полі «
Сумма по полю Сумма». З’явиться вікно «Вычисление поля сводной таблицы» зі списком доступних операцій. Вибираємо зі списку операцію «Сумма»;7) у полі «
Имя:» записуємо назву «Сумма, грн.». У вікні «Вычисление поля сводной таблицы» натискуємо на «ОК»». Форма макета набере вигляду, як показано на рис. 2; 
8) у вікні створення макета натискуємо на «
ОК», потрапляємо в основне вікно Майстра зведених таблиць;9) тут натискуємо на кнопку «
Параметры», відкриється вікно «Параметры сводной таблицы»;10) у цьому вікні відключаємо прапорці «
общая сумма по столбцам», «общая сумма по строкам», «автоформат»;11) натискуємо на «
ОК», щоб повернутися в основне вікно Майстра зведених таблиць;12) натискуємо на «
Готово».У результаті ми отримали зведений звіт, форму якого показано на рис. 3. Тепер можна починати групування.
Важливо!
При роботі зі зведеними таблицями база даних може містити порожні рядки. Цим часто користуються на практиці. Наприклад, замість точного блока значень з вихідними даними вводять лише індекси колонок. Це зручно, оскільки не потрібно перебудовувати таблицю при поповненні бази даних новими записами. У разі роботи з інтервальними групуваннями за числами (або датами) такий підхід незастосовний! База з групуваннями не повинна містити порожніх записів. Тому при формуванні зведеної таблиці доведеться явно вказувати блок комірок з вихідними даними. Звичайно, у цьому є певна незручність, але з цим доведеться змиритися.Наше завдання — визначити, на яких товарах (дешевих, дорогих чи таких, що перебувають у середньому ціновому діапазоні) підприємство отримує основну частину товарообороту. Для цього потрібно розбити всю множину цін на інтервали і потім перерахувати підсумкові значення зведеного звіту для кожного інтервалу окремо. У нашому прикладі вартість принтерів коливається від 455 грн. (модель «
Printer Canon pixma ip1900») до 3585 грн. (модель «Printer Epson Stylus Photo 1410 A3»). Розіб’ємо цей діапазон на вісім інтервалів із кроком 500 грн. Виконуємо такі дії:1) ставимо покажчик на будь-якому значенні поля «
Цена» зведеного звіту, наприклад на комірку «A5»;2) клацаємо правою кнопкою миші. З’явиться контекстне меню, як показано на рис. 4 (див. с. 63);
3) із контекстного меню вибираємо «
Группа и структура», потім — «Группировать…». З’явиться вікно, як показано на рис. 5. Це — головне вікно групування. У ньому можна задати початкове та кінцеве значення інтервалу і крок зміни. Зверніть увагу на положення прапорців ліворуч від полів «начиная с:» і «по:». Якщо вони включені, Excel вибере інтервал групування цін самостійно, автоматично. У нашому прикладі в полі «начиная с:» він поставить мінімальне значення ціни (455 грн.), у полі «по:» — максимальне значення (3585 грн.). У більшості випадків це неправильно. Тому групування краще за все зробити самому. Виправимо значення у вікні «Группирование»; 
4) клацаємо лівою кнопкою всередині поля «
начиная с: ». Уводимо значення «0»;5) клацаємо лівою кнопкою по полю «
с шагом:» і вводимо сюди «500»;6) натискуємо на «
ОК». Excel згрупує товари та підрахує сумарну виручку за кожним інтервалом. Результат групування зведеної таблиці показано на рис. 6. 
З отриманого звіту видно, що основну частину обороту формують товари середньої цінової категорії в діапазоні від 1000 до 2000 грн. (понад 48 % від загальної суми).
Щоб скасувати групування і повернутися до вихідної форми зведеної таблиці, виконуємо такі дії:
1) ставимо покажчик активної комірки на будь-якому значенні поля «
Цена»;2) клацаємо правою кнопкою миші;
3) з контекстного меню (рис. 4) вибираємо «
Группа и структура → Разгруппировать». Таблиця набуде первісного вигляду.
Групування дат
Можливості групування даних у зведених таблицях залежить від типу вихідних даних. Досі ми групували числа. Зараз подивимося, що можна зробити з датами. Наше завдання буде таким: перерахувати обсяги товарообороту за місяцями та кварталами. Виконуємо такі дії:
1) відкриваємо документ зі зведеною таблицею, як показано на рис. 3;
2) стаємо на область зведеного звіту, клацаємо правою кнопкою миші;
3) з контекстного меню (рис. 4) вибираємо пункт «
Мастер сводных таблиц». Відкриється вікно Майстра на третьому кроці;4) натискуємо на кнопку «
Макет»;5) ставимо покажчик миші на поле «
Цена» (зараз воно знаходиться в області рядків);6) утримуючи натисненою ліву кнопку миші, перетягуємо поле за межі області макета і відпускаємо кнопку миші;
7) у лівій частині макета знаходимо поле «
Дата» і переносимо його до області рядків. Остаточно макет звіту має виглядати, як показано на рис. 7 (див. с. 64); 
8) натискуємо на кнопку «
ОК», потім — «Готово». Результат формування зведеної таблиці показано на рис. 8 (див. с. 64). Поки що з цього звіту ми можемо побачити обсяги товарообороту за днями; 
9) ставимо покажчик активної комірки на будь-яке значення поля «
Дата». Наприклад на комірку «A5»;10) клацаємо правою кнопкою миші, з контекстного меню (рис. 4) вибираємо «
Группа и структура → Группировать…». На екрані з’явиться вікно, як показано на рис. 9. Це — вікно для групування дат. Призначення полів «начиная с:» і «по:» не змінилося. Вони визначають мінімальне та максимальне значення для інтервалу групування. А ось крок групування при роботі з датами оформлено у вигляді списку із семи значень: «Секунды», «Минуты», «Часы», «Дни», «Месяцы», «Кварталы», «Годы»; 
11) вибираємо значення «
Месяцы». Натискуємо на «ОК». Зведена таблиця набуде форми, як показано на рис. 10. У ній інформацію вже зображено за місяцями. У такому форматі набагато зручніше аналізувати динаміку товарообороту за тривалий період часу. Одного погляду на звіт достатньо, щоб побачити істотний приріст показника у травні — червні 2009 року. Ідемо далі; 
12) повертаємося до зведеного звіту. Ставимо активну комірку на «
A5». Викликаємо контекстне меню та вибираємо «Группа и структура → Группировать…»;13) у настройках параметрів групування (рис. 9) вибираємо «
Кварталы». Форма зведеного звіту зміниться. Вона виглядатиме, як показано на рис. 11: обсяги товарообороту Excel перегрупував за кварталами; 
14) повертаємося до зведеного звіту. Викликаємо контекстне меню та вибираємо «
Группа и структура → Группировать…»;15) у вікні настройки параметрів групування зі списку «
с шагом:» вибираємо ДВА значення. Наприклад, «Месяцы» і «Кварталы». Excel виконає подвійне групування даних, унаслідок чого ми зможемо побачити сумарні значення обсягів реалізації як за місяцями, так і за кварталами. Приклад звіту з таким групуванням показано на рис. 12. 
Важливо!
Подвійне клацання лівою кнопкою по будь-якому значенню поля групування згортає групу. Повторне подвійне клацання розгортає групу (показує її склад докладно). Спробуйте зробити це на прикладі звіту, зображеного на рис. 12. У цьому випадку клацати потрібно по будь-якій комірці в межах колонки «Наименование».
Довільне групування
Насправді групувати у зведених таблицях можна не тільки числа чи дати, а все що завгодно. Для прикладу побудуємо стислий звіт про продажі за категоріями товарів. Виконуємо такі дії:
1) формуємо зведену таблицю, поставивши на область рядків поле «Наименование
», в області даних залишаємо поле «Сумма». Отримуємо таблицю, як показано на рис. 13; 
2) у колонці «
Наименование» (стовпець «А») виділяємо всі елементи, що стосуються принтерів. У прикладі — це рядки «A5:A24»;3) по виділеному блоку клацаємо правою кнопкою миші;
4) із контекстного меню вибираємо «
Группа и структура → Группировать…». Ліворуч від колонки «Наименование» з’явиться ще один стовпець із назвою «Наименование2». Першим елементом у цьому стовпці буде об’єднана комірка з назвою «Группа1»;5) замість тексту «
Группа1» друкуємо «Принтеры». Натискуємо на «Enter»;6) двічі клацаємо мишею по згрупованому полю. Excel згорне всі записи про принтери в один рядок. Є інший спосіб згорнути групу: клацнути правою кнопкою миші та вибрати з контекстного меню «
Группа и структура → Скрыть детали»;7) повторюємо такі самі дії для рядків із записами про багатофункціональні пристрої. Ці елементи я назвав у згрупованому звіті «
МФУ»;8) двічі клацаємо мишею по заголовку «
Наименование2». Відкриється вікно «Вычисление поля сводной таблицы»;9) в області «
имя:» друкуємо текст «Группа тов.», натискуємо на «ОК». Результат нашої роботи показано на рис. 14. У цьому звіті підраховано обсяг товарообороту за кожною з двох категорій товарів. Для принтерів він склав 3387195 грн., для МФУ — 2029970 грн. 
Важливо!
При ручному групуванні у вихідній базі даних можуть бути порожні рядки.Як бачите, групування даних — винятково зручний та гнучкий спосіб модифікації зведених таблиць. Раджу всім користуватися ним на практиці якомога активніше.
Чекаю ваших запитань, зауважень та пропозицій на
bk@id.factor.ua, nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum . Успішної роботи!
