Excel 2007: групування даних у зведених таблицях
 | Зазвичай головною метою створення зведеного звіту є зображення даних у компактній, узагальненій формі, але так виходить не завжди. Якщо кількість різних значень ключового поля в рядку або колонці завелика, то зведена таблиця може виявитися надто об’ємною. Аналізувати її буде складно, а в деяких випадках і зовсім безглуздо. У такій ситуації можна застосувати так зване групування даних. Докладніше про цей корисний інструмент щодо зведених звітів Excel 2007 поговоримо в цій статті. Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства |
Операція групування застосовується для того, щоб об’єднати кілька рядків (або колонок) таблиці в одне ціле, скоротивши цим обсяг аналізованої інформації. Групування можна застосувати до будь-якої множини рядків або колонок робочого листа. Працює групування і стосовно фрагментів зведеної таблиці. Причому в кожному з цих випадків є свої особливості, з якими нам і потрібно розібратися. Розпочнемо з найпростішого.
Групування даних на робочому листі
Перший досвід використання групування ми отримаємо на прикладі бази даних продажів, зображеної на рис. 1. Виконуємо такі дії:
1) відкриваємо документ, переходимо на лист «
Продажи»;2) виділяємо кілька рядків таблиці. Наприклад, з «
2» по «13» (продажі за «03/01/09»);3) викликаємо меню «
Данные»;4) у групі «
Структура» клацаємо по іконці «Группировать» (рис. 2). Ліворуч від виділених комірок з’явиться символ структури. У нижній частині його можна побачити невеликий прямокутник зі знаком «-» (рис. 1);
5) клацаємо по цьому значку лівою кнопкою миші. Рядки з «
1» по «13» стануть невидимими. Замість них у лівій частині листа залишиться значок із символом «+». Клацання по цьому символу знову покаже приховані рядки.Важливо!
Групування даних на робочому листі не виконує жодних обчислень над елементами електронної таблиці. У цьому випадку групування — один зі зручних способів організації даних, і не більше того.Де можна використовувати групування? Практично в будь-якій таблиці, що має певну структуру. Наприклад, ви оформляєте прайс-лист, в якому записано значну за обсягом номенклатуру товарів. Ці товари за їх призначенням можна організувати у групи. Тоді цілком логічно внести до прайсу окремі рядки з назвами груп, нижче розмістити конкретні ТМЦ з їх характеристиками та цінами, а потім згрупувати рядки з товарами нижнього рівня. У цьому випадку при роботі з прайсом з’явиться можливість розкрити групу товарів, що вас цікавить, і працювати лише з нею. Друга сфера застосування групування — підготовка звітів до друку. Ви можете зробити докладні обчислення, кінцеві результати винести до окремого рядка, а проміжні дані згрупувати. Якщо згорнути групи, у вас залишиться стислий звіт. Розкривши вміст груп, ви отримаєте доступ до всіх обчислень.
Порада
Щоб швидко згрупувати виділений блок рядків чи колонок, використовуйте комбінацію клавіш «Shift + Alt + →». Розгрупувати виділений блок можна поєднанням клавіш «Shift + Alt + ←».Тепер виконуємо такі дії:
1) виділяємо блок
колонок на листі «Продажи». Нехай це будуть стовпці «C», «D», «E»;2) у меню «
Данные» знаходимо групу іконок «Структура»;3) клацаємо по іконці «
Группировать». Над виділеними колонками з’явився елемент групування даних (рис. 3). За його допомогою ви можете приховати або розгорнути згруповані стовпці робочого листа.
Важливо!
Операцію групування можна застосувати як до рядків, так і до стовпців робочого листа. Крім того, можна згрупувати рядки та колонки таблиці одночасно.Повернемося до таблиці, зображеної на рис. 3. У верхньому лівому кутку її розташовано чотири елементи управління: два — для рядків і два — для колонок. На рисунку вони виглядають як прямокутники з цифрами «
1» і «2». За допомогою цих елементів можна управляти ступенем деталізації відображення згрупованих даних. Клацання по значку з цифрою «1» згортає групу (показує перший рівень деталізації). Клацання по значку з цифрою «2» розвертає групу.Нумерацію цих елементів управління розробники Excel використали не випадково. Справа в тому, що для елементів робочого листа можна застосувати багаторазове групування даних, наприклад, виділити рядки «
1-13» та згрупувати їх. Потім усередині створеної групи виділити рядки «5-8» і знову їх згрупувати. У результаті вийде трирівневе групування даних, в якому одну групу вкладено до іншої. Відповідно з’явиться і додатковий елемент управління з цифрою «3».Щоб скасувати групування даних, виконуємо такі дії:
1) виділяємо блок рядків чи колонок;
2) викликаємо меню «
Данные»;3) у групі «
Структура» клацаємо по іконці «Разгруппировать» або натискаємо на комбінацію клавіш «Shift + Alt + ←».Отже, з принципом групування ми розібралися. Але зробили це стосовно комірок
робочого листа. Тепер розглянемо особливості групування даних щодо зведених таблиць.
Групування у зведених таблицях
Спочатку створимо невеликий зведений звіт, форму якого показано на рис. 4. У ньому в рядках розташовано найменування товарів (поле «
НаимТов»). В області даних підраховано суму по полю «Сумма» основної бази даних. Виконуємо такі дії:
1) виділяємо кілька комірок у зведеному звіті. Це може бути будь-який блок комірок, що знаходиться всередині зведеної таблиці, наприклад, «
A3:A4»;2) викликаємо меню «
Данные»;3) у групі «
Структура» клацаємо по іконці «Группировать». Зведений звіт набуде форми, як показано на рис. 5.
Ліворуч у звіті з’явилася додаткова колонка з назвою «
НаимТов2». У третьому та четвертому рядках цієї колонки Excel 2007 сформував групу з назвою «Группа1». До неї він включив два елементи — «Блокнот» і «Бумага оф.». Усе, як ми і вказали. Інші елементи зведеної таблиці теж перетворилися на групу. Про це свідчить значок «згорнути/розгорнути групу» зі знаком «-». Але всі ці групи складаються тільки з одного елемента. Далі виконуємо такі дії:1) ставимо покажчик на значок згортання групи біля елемента «
Группа 1»;2) клацаємо лівою кнопкою миші. Третій та четвертий рядки зведеного звіту об’єднаються в один із назвою «
Группа 1» (рис. 6), а підсумкове значення суми продажів за цією групою стане 55589,05 грн. (4441,05 грн. + 51148,00 грн.).
Важливо!
При групуванні рядків у зведеному звіті Excel автоматично формує підсумкові значення (суму) за всіма елементами групи. Це — перша відмінність групування комірок у зведеній таблиці від такої самої операції над комірками робочого листа.Другою видимою відмінністю є наявність назви у створюваній групі (у прикладі на рис. 5 це «
Группа 1»). Щоправда, власне назва ця малоприваблива. Виконуємо такі дії:1) ставимо покажчик активної комірки на «
А3» (рис. 5);2) друкуємо будь-яку назву групи;
3) натискаємо на «
Enter».Важливо!
Редагувати назву групи зведеного звіту можна як звичайну комірку робочого листа — через клавішу «F2», у рядку формул чи безпосередньо на робочому листі. Але є одна особливість. Друкувати дані безпосередньо в комірці з назвою групи можна, але вставити дані з буфера обміну не можна.Тепер про те, як скасувати групування.
Залишаючись у комірці «
А3», клацаємо по іконці «Расгруппировать» меню «Данные». Excel видалить групування у зведеному звіті. До речі, виконати таку операцію можна тільки для тих комірок, де створено реальну групу. Наприклад, якщо поставити активну комірку в «A5» (рис. 5), то скасування групування не дасть жодних результатів.Отже, зараз перед нами вихідна зведена таблиця без групувань, як показано на рис. 4. Виконуємо такі дії:
1) виділяємо кілька комірок, що
стоять окремо (несуміжних). Наприклад, приладдя для писання «Карандаш» (рядок «5») і «Ручка гел.» (рядок «7»);2) викликаємо меню «
Данные»;3) у групі «
Структура» клацаємо по іконці «Группировать». Зведений звіт набуде форми, як показано на рис. 7, — Excel сформував групу із зазначених елементів та правильно визначив сумарні обсяги реалізації.
Важливо!
Групування несуміжних комірок можливе тільки при роботі зі зведеними таблицями. Виконати таку дію над комірками робочого листа в Excel 2007 не можна.Як і у випадку з робочим листом, зведені таблиці можна групувати за колонками. Виконуємо такі дії:
1) відкриваємо зведений звіт, як показано на рис. 4;
2) якщо вікно «
Список полей сводной таблицы» недоступне, відкриваємо його (це зручно зробити через контекстне меню правої кнопки миші);3) знаходимо поле «
Дата» і перетягуємо його в область «Названия столбцов». Тепер таблиця показує обсяги реалізації кожного ТМЦ за датами;4) виділяємо у зведеному звіті (це мають бути саме комірки звіту!) перші п’ять елементів. У нашому прикладі — це дати з «
01/01/09» по «05/01/09», розташовані в комірках «B2:D2»;5) клацаємо на кнопці «
Группировать» меню «Данные»;6) стаємо на комірку «
B2»;7) друкуємо назву групи «
I»;8) повторюємо групування для решти п’ятиденок зведеної таблиці. Результат показано на рис. 8.
Як бачимо, усі відмітні особливості групування зведених таблиць у цьому випадку залишилися ті самі. Excel сформував групи, присвоїв їм імена, проте елементів управління рівнями групування він не створив. При згортанні групи Excel показує для неї підсумкові значення.
Убудовані інструменти групування зведених таблиць
Залежно від типу поля, за яким виконується групування у зведеній таблиці, можна виділити два випадки: коли групують числа і коли групують дати. Обидва ці варіанти відрізняються один від одного не набагато, але все-таки в кожному є свої особливості.
ГРУПУВАННЯ ЧИСЛОВИХ ЗНАЧЕНЬ
Щоб дослідити це питання, перебудуємо зведений звіт, як показано на рис. 9. У цьому звіті в області рядків я розмістив два поля: «
НаимТов» (назва ТМЦ) і дату. Тепер виконуємо такі дії:
1) ставимо покажчик активної комірки на будь-яку дату у зведеному звіті, наприклад на «
B3»;2) клацаємо правою кнопкою миші, з контекстного меню (рис. 10) вибираємо «
Группировать…». З’явиться вікно настройок параметрів групування, як показано на рис. 11 (на с. 26).
Важливо!
Часто при формуванні зведених таблиць завчасно не відома точна кількість записів у вихідній базі даних. Тому при визначенні області з даними прийнято вказувати лише індекси колонок. І це — звичайна практика, оскільки вона дозволяє не перебудовувати таблицю кожного разу при поповненні бази новими записами. У разі роботи з інтервальними групуваннями за числами (або датами) такий підхід застосовувати неможливо! База даних для побудови зведеного звіту не повинна містити порожніх записів. Якщо це не так, то за командою групування вікно «Группирование» не з’явиться. Отже, якщо ви збираєтеся використовувати інтервальні групування, указуйте блок комірок з даними для створення зведеного звіту явно. У цьому є певна незручність, але з нею доведеться миритися.Повернемося до вікна «
Группирование» (рис. 11) і подивимося на його вміст докладніше. У верхній частині вікна розташовано два параметри: «начиная с:» і «по:», що визначають діапазон дат, у межах якого Excel виконуватиме групування у зведеному звіті. За допомогою прапорців ліворуч від кожного параметра їх дію можна активувати або відмінити.
У центральній частині (нижче підзаголовка «
с шагом:», рис. 11) перераховано сім можливих варіантів групувань по датах. Це «Секунды», «Минуты», «Часы», «Дни», «Месяцы», «Кварталы», «Годы». У нижній частині вікна «Группирование» знаходиться параметр «количество дней:». За його допомогою можна вказати довільний крок групування за датами з точністю до одного дня. Як взаємодіють перелічені параметри, ми побачимо, продовживши роботу з таблицею на рис. 9. Наше завдання буде таким: перерахувати обсяги товарообороту за місяцями та за кварталами. Виконуємо такі дії:1) відкриваємо документ зі зведеною таблицею, як показано на рис. 9;
2) ставимо покажчик активної комірки на будь-яке значення поля «
Дата», наприклад на «B3»;3) клацаємо правою кнопкою миші, відкриваємо контекстне меню (рис. 10), вибираємо пункт «
Группировать…». На екрані з’явиться вікно «Группирование», як показано на рис. 11;4) у цьому вікні параметри «
начиная с:» і «по:» залишаємо без змін. Вони визначають мінімальне та максимальне значення для інтервалу групування (з «03/01/2009» по «01/10/2009»), і нас це повністю влаштовує;5) у списку «
с шагом:» вибираємо варіант «Кварталы»;6) натискаємо на «
ОК». Звіт набув форми, як показано на рис. 12. У ньому кількість значень, що відображаються в колонці «Дата», скоротилося до трьох (це перші три квартали 2009 року), а всі обсяги товарообороту Excel перегрупував за кварталами.
У такому форматі набагато зручніше аналізувати динаміку товарообороту за тривалий період часу. Одного погляду на звіт вистачить, щоб побачити майже дворазовий приріст обсягу в III кварталі 2009 року практично за всіма позиціями. Ідемо далі. Тепер уточнимо, в якому місяці досягається максимальне збільшення товарообороту в III кварталі. Для цього, зрозуміло, потрібно отримати зведений звіт, згрупувавши в ньому дати за місяцями. Виконуємо такі дії:
1) повертаємося у зведеній таблиці на рис. 12;
2) клацаємо правою кнопкою миші на будь-якій комірці в колонці «
Дата». Відкриється контекстне меню, як показано на рис. 10;3) вибираємо пункт «
Группировать…», відкриється вікно, як показано на рис. 11. Причому активним елементом у списку «с шагом:» буде «Кварталы»;4) клацаємо по елементу «
Месяцы». Зверніть увагу, що при цьому елемент «Кварталы» теж залишився підсвіченим, тобто ми вказали два рівні групування для поля «Дата»!5) у вікні «
Группирование» натискаємо на «ОК». Звіт матиме вигляд, як показано на рис. 13: Excel виконав подвійне групування даних за значеннями «Дата». Унаслідок цього ми можемо бачити обсяги реалізації як за місяцями, так і за кварталами.
Раджу звернути увагу на варіант групування дат
за днями. При його використанні стає доступним прапорець «количество дней:» (рис. 11). Цей прапорець дозволяє встановити довільний інтервал часу, для якого Excel згрупує дати. Таким чином, можна отримати підсумкові значення, наприклад, за декаду, за кожні 10 днів тощо.
ГРУПУВАННЯ ЧИСЛОВИХ ЗНАЧЕНЬ
Отже, при роботі з полем типу «
Дата» у зведених таблицях Excel є кілька призначених варіантів групувань. Логічно припустити, що саме така ситуація має місце при роботі з іншими типами полів зведеної таблиці, наприклад, із числовими. У принципі, так воно і є, тільки як варіанти групування Excel запропонує скористатися інтервалами значень ключового поля. Подивимося, як це виглядає на прикладі такого завдання. У нас є база даних щодо обсягів реалізації ТМЦ (рис. 1). Ми хочемо дізнатися, товари якої цінової категорії мають максимальний попит. Для цього нам потрібно буде вибрати з бази всі ціни, розбити їх на кілька груп (!) і визначити обсяги реалізації (у кількісному чи вартісному виразі) за кожною групою. Інакше кажучи, нам знадобиться функція групування, причому ми групуватимемо числові значення.Почнемо з того, що відкриваємо файл із базою даних та формуємо зведений звіт, як показано на рис. 14. У цьому звіті в області рядків ставимо поле «
Цена». До області «Значения» переносимо поле «Сумма», для якого вибираємо операцію підсумовування. Зі звіту видно, що товарів вартістю «0,1 грн.» за весь період продано на суму 506,9 грн. А всього діапазон цін охоплює значення від «0,1 грн.» до «32 грн.». Поділимо цей діапазон на чотири категорії та оцінимо сумарні обсяги реалізації в кожній із них. Виконуємо такі дії:
1) клацаємо правою кнопкою миші по будь-якому значенню в колонці «
Цена»;2) з контекстного меню (рис. 10) вибираємо пункт «
Группировать…». Відкриється вікно, зображене на рис. 15. У ньому, як і раніше, є параметри «начиная с:» і «по:». За їх допомогою ми визначимо початкове та кінцеве значення (інтервал) ключового поля. А от параметр «с шагом:» тепер має значення в однині. Відразу перевіряємо положення прапорців ліворуч від полів «начиная с:» та «по:». Якщо вони включені , Excel вибере інтервал групування самостійно. У нашому прикладі в полі «начиная с:» він поставить мінімальну ціну (0,1 грн.), а в полі «по:» — максимальну ціну (32 грн.). Загалом помилки тут немає, але здебільшого групувати значення краще самому. Що ми зараз і зробимо;
3) клацаємо лівою кнопкою всередині поля «
начиная с:». Уводимо значення «0»;4) поле «
по:» залишаємо без змін, зараз тут знаходиться ціна «32»;5) клацаємо лівою кнопкою по полю «
с шагом:», уводимо сюди значення «1»;6) натискаємо на «
ОК». Excel згрупує товари та підрахує сумарну виручку щодо кожного інтервалу. Результат групування зведеної таблиці показано на рис. 16. 
З отриманого звіту видно, що основну частину обороту формують товари цінової категорії в діапазоні від 31 до 32 грн. (понад 60 % від загальної суми). На другій позиції — товари з цінами 1 — 2 грн. (19 % у загальному обсязі продажів) і так далі. Щоб отримати докладні відомості про ці продажі, виконуємо такі дії:
1) ставимо покажчик активної комірки на «
B6»;2) двічі клацаємо мишею на підсумковому значенні (на рис. 16 це сума «
91729»). На окремому листі Excel сформує фрагмент бази даних продажів ТМЦ вартістю від 31 до 32 грн. У нашому прикладі — це позиція «Бумага оф.».І останнє зауваження. Воно стосується одного цікавого способу послідовної деталізації даних при роботі зі зведеними звітами. Розпочнемо з таблиці на рис. 16. Виконуємо такі дії:
1) стаємо на комірку «
A3». У ній записано інтервал цін «0-1»;2) двічі клацаємо мишею. На екрані з’явиться вікно «
Показать детали» (рис. 17), в якому наведено список полів вихідної бази даних;
3) вибираємо будь-яке потрібне нам поле, наприклад «
НаимТов»;4) натискаємо на «
ОК». Ліворуч від поля «Цена» з’явилися символи групування даних. До зведеного звіту Excel запровадив поле «НаимТов» (рис. 18). Тепер у цьому звіті можна побачити не лише інтервали цін, а й товари, що знаходяться в зазначених цінових діапазонах, а також обсяги реалізації цих товарів.
Продовжуючи діяти за описаною схемою, ми можемо двічі клацнути по комірці «
B3» (найменування товару). Потім у вікні «Показать детали» вибрати, наприклад, поле «Покупатель». Після цього у зведеному звіті буде показано відомості щодо обсягів реалізації ТМЦ стосовно кожного покупця тощо.Як бачите, групування даних — дуже зручний та гнучкий спосіб модифікації зведених таблиць. Раджу користуватися ним на практиці якомога активніше.
А ми на цьому завершимо тему групування даних і наступного разу подивимося, які способи обчислень пропонують зведені таблиці, у чому їх особливості, як можна запровадити формулу до зведеного звіту і що це дає бухгалтеру на практиці.
Чекаю ваших запитань, листів, пропозицій та зауважень на bk@id.factor.ua, nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum. Успішної роботи!
