Зведені таблиці в Excel

Як швидко проаналізувати первинні дані за допомогою традиційного офісного програмного пакета

Майже на всіх підприємствах періодично виникає потреба обробляти та аналізувати значні масиви інформації, наприклад щодо продажів товарів, виконання робіт тощо. Такі записи можуть містити тисячі чи навіть мільйони рядків, залежно від специфіки та розмірів підприємства. При цьому керівники хочуть бачити підсумкову інформацію в динаміці та в розрізі, наприклад, регіонів, груп товарів, місяців, постачальників, покупців тощо. Наскільки легко і швидко ви можете дістати підсумкову інформацію, на підставі якої прийматимете управлінські рішення? Такі інструменти Excel, як зведені таблиці та зведені діаграми, дозволяють створити звітність за кілька хвилин.

Павло Куратник,
заступник головного редактора
журналу «Справочник экономиста»

Підготовка даних

Використовуючи зведені таблиці (ЗТ), можна обчислити підсумкову інформацію, не написавши жодної формули і не скопіювавши ані клітинки. Величезною перевагою ЗТ є їхня динамічність: ви можете трансформувати таблицю і створити новий звіт.

Для створення ЗТ потрібний діапазон вихідних даних (таблиця), на основі якого буде сформована ЗТ. Дані аркуша Excel повинні бути у форматі списку із заголовком стовпчика у першому рядку. Решта рядків має містити аналогічні об’єкти в одному стовпчику.

Дані для аналізу можна завантажити з облікової програми, наприклад 1С. У деяких випадках їх потрібно дещо змінити, але здебільшого дані відразу готові до аналізу. Наприклад, щоб Excel обробляв дату, може знадобитися функція «ДАТАЗНАЧ», яка перетворює дату, що зберігається у вигляді тексту, на порядковий номер, який Excel сприймає як дату. Ця функція стане у нагоді, якщо є дати у текстовому форматі, які потрібно відфільтрувати, відсортувати або відформатувати у вигляді дат або використовувати в обчисленнях з датами. У деяких випадках під час обробки даних можна скористатися функцією «ЗНАЧЕН», яка перетворить на число рядок тексту, що відображає число. Іноді, наприклад під час обробки документів, можна скористатися командою: Дані → Текст за стовпчиками. Крім того, можуть бути корисними такі функції, як «ПСТР», «НАЙТИ», «ЛЕВСИМВ», «ПРАВСИМВ».

Перш ніж створювати зведені таблиці в Excel 2010, потрібно перетворити джерело даних у таблицю за допомогою команди: Вставка → Таблиці → Таблиця. Опція «Таблиця із заголовками» має залишатися включеною. Навіщо це робити? Використання таблиць Excel значно спрощує підтримку джерела даних ЗТ. У таблицях Excel 2010 під час додавання стовпчика або рядка вся нова інформація автоматично потрапляє до ЗТ. Але, якщо ЗТ базується на невідформатованому джерелі даних, нові рядки або стовпчики можуть бути не визначені. Тому потрібно буде або щоразу визначати їх вручну, або створювати динамічну область у вікні менеджера імен. Інакше ЗТ міститиме неправильні дані та результати.

Створення зведених таблиць

Розглянемо на умовному прикладі, як швидко проаналізувати дані. Приміром, підприємство реалізує будівельні матеріали з чотирьох власних складів у Києві, Черкасах, Житомирі та Вінниці. Як вихідну інформацію маємо звіт із продажів продукції за IV квартал 2012 року, що містить дані з реалізації 700 найменувань товарів. Необхідно отримати різноманітну підсумкову інформацію за містами, місяцями, товарними групами. Звіти мають бути простими й інформативними.

На вкладці «Вставка» у групі «Таблиці» розкриємо меню кнопки «Зведена таблиця». Виберемо пункт «Зведена таблиця». У вікні «Створення зведеної таблиці» у графі «Таблиця або діапазон» відобразиться Таблиця 1, якщо ви її не перейменували (див. рис. 1).

У групі «Зазначте, куди слід помістити звіт зведеної таблиці» потрібно вибрати один з варіантів. Відповідно на новому або наявному аркуші з’явиться порожня ЗТ у вигляді контуру макета, а праворуч аркуша відкриється поле «Список полів зведеної таблиці» для створення звіту, в яке можна вставляти нові поля ЗТ, відмічати їх, створювати макет і налаштовувати звіт.

У вікні «Список полів» відмітимо потрібні для створення ЗТ поля. Наприклад, виберемо «Місяць», «Група товару», «Виручка, грн», «Склад». Поля автоматично додаватимуться у ЗТ, їх можна перетягувати та міняти місцями, при цьому ЗТ змінюватиметься автоматично (див. рис. 2). У результаті за кілька секунд ви отримаєте звіт про результати продажів за місяцями, містами і групами товарів.

Коли курсор знаходиться у створеній ЗТ, стає доступним розділ «Знаряддя для зведених таблиць», за допомогою якого можна виконувати необхідні дії із ЗТ.

Щоб звіт автоматично оновлювався, потрібно виконати такі команди: Параметри → Зведена таблиця → Параметри. У вікні «Параметри зведеної таблиці» на вкладці «Дані» активуйте пункт «Оновити при відкритті файлу». Крім того, можна скористатися кнопкою «Оновити все» на стрічці Excel у розділі «Дані».

Фільтрація і зрізи

У версіях Microsoft Excel 2003, 2007 для фільтрації даних у звітах ЗТ можна було використовувати фільтри звітів, але під час фільтрації декількох елементів було складно переглядати поточний стан фільтрації. У Excel 2010 для фільтрації даних можна використовувати зрізи. Вони являють собою зручні у використанні компоненти для фільтрації даних у звітах ЗТ за допомогою набору кнопок.

Щоб створити зріз для поточної зведеної таблиці, потрібно виконати такі команди: Параметри → Сортування і фільтр → Вставити зріз. Для прикладу побудуємо ЗТ за місяцями та складами й додамо зрізи щодо виробників і найменувань товарів. Виберемо у зрізі якогось виробника, наприклад KNAUF. При цьому в сусідньому зрізі побачимо, як відразу ж у верхню його частину автоматично підтягнуться виділені заливкою найменування продукції саме цього виробника. Найменування інших виробників будуть розташовані нижче і без заливки. Якщо виділити одне або кілька найменувань, побачимо у ЗТ результати їх продажу за місяцями та містами (див. рис. 3).

Зріз можна відформатувати. Для цього виберіть «Зріз». На стрічці з’явиться панель «Інструменти для зрізу» із вкладкою «Параметри». На цій вкладці у групі «Стилі зрізів» виберіть стиль. Якщо зріз більше не потрібен, його можна відключити від звіту ЗТ або видалити.

Обчислення у зведених таблицях

Якщо до розділу «Значення» додати поле, Excel автоматично визначить функцію, на основі якої проводитимуться підсумкові обчислення. Програма застосовує здебільшого операцію підсумовування, яка додає всі значення, що є в полі. Щоб змінити тип обчислень, запропонований Excel, потрібно у розділі «Значення» списку полів ЗТ натиснути кнопку відповідного поля і вибрати команду «Параметри полів значень». У діалоговому вікні на вкладці «Операція» оберіть функцію для обчислення підсумкових значень. Виберемо функцію «Кількість» замість функції «Сума». У результаті побачимо, скільки разів було продано продукцію фірми KNAUF у кожному місті за місяцями. Крім того, за допомогою зрізів можна вибрати іншого виробника або взагалі підключити інший зріз і дістати необхідну інформацію (див. рис. 4).

Можна додати кілька полів до списку «Значення». У цьому випадку кожне поле обчислюється і відображається в окремому стовпчику ЗТ. Якщо потрібно виконати декілька обчислень для одного і того самого поля, перетягніть його двічі до списку «Значення». Ви отримаєте два окремі елементи, кожен з яких можна налаштувати незалежно.

Додаткові обчислення

Крім простих обчислень (сума, кількість, середнє, мінімальне, максимальне значення) у ЗТ можна швидко отримати іншу підсумкову інформацію. Припустімо, ви хочете побачити у відсотковому відношенні внесок кожного з міст до загальної суми продажів за місяцями. Для цього натисніть кнопку «Параметри поля» на панелі інструментів «Зведені таблиці». На екрані з’явиться діалогове вікно «Обчислення поля зведеної таблиці» (або у розділі «Значення» натисніть кнопку відповідного поля і виберіть команду «Параметри полів значень»). Далі натисніть кнопку «Додатково» у полі «Додаткові обчислення», оберіть опцію «% суми за батьківським стовпцем». У результаті з’являється звіт про частку кожного складу в сумі продажів за місяцями.

Помінявши місцями склад і місяць, можна побачити іншу картину — яким є внесок кожного місяця в кожному місті.

Якщо вибрати опцію «% суми за батьківським рядком», підсумок усіх рядків дорівнюватиме 100 % (див. рис. 5).

Цікавою є також опція функції «Додаткові обчислення» — «Відмінність». Якщо у двох списках «Поле» та «Елемент» вибрати, наприклад, «Місяць» і «Назад», отримаємо ЗТ, у якій буде показано динаміку продажів за кожною товарною групою у порівнянні з попереднім місяцем. Аналогічні результати, але у відносних величинах можна отримати, виконавши ті самі дії, але замість функції «Відмінність» вибравши «Приведена відмінність». Також у вікні «Елементи» можна вибрати не «Назад», а будь-який з місяців, тоді цей місяць буде базовим, а в інших місяцях відображатимуться відхилення у порівнянні з обраним базовим місяцем.

У Excel 2010 опцію «Додаткові обчислення» доповнено такими функціями, як «% суми за батьківськім рядком», «% суми за батьківським стовпцем», «% батьківської суми», «% накопичувального підсумка», «Сортувати від мінімального до максимального» і «Сортувати від максимального до мінімального».

Під час аналізу даних часто виникає потреба проведення спеціальних обчислень. Якщо підсумкова таблиця має містити поле з обчисленим значенням, отриманим на основі інших полів або спеціальних значень, можна створити обчислюване поле або обчислюваний елемент.

Створення зведених діаграм

У Excel 2010 зведені діаграми (ЗД) завжди будуються лише за даними зведених таблиць. Водночас Excel дає змогу створювати ЗД до побудови ЗТ, точніше, під час їх створення одночасно будується і зведена діаграма. Якщо ви користувалися звичайними діаграмами Excel, у вас не виникне проблем зі створенням та налаштуванням ЗД, оскільки всі можливості звичайних діаграм стосуються і ЗД. Зведену діаграму можна побудувати у два способи.

1. Коли курсор знаходиться на будь-якому осередку ЗТ, виконайте такі команди: Знаряддя для зведених таблиць → Параметри → Сервіс → Зведена діаграма.

2. Виконайте команди: Вставка → Таблиці → Зведена таблиця → Зведена діаграма.

Побудуємо за допомогою наведених вище команд ЗД, яка відобразить динаміку продажів трьох обраних груп товарів (гідроізоляція, добавки в бетон, гіпсокартон) за кожним із складів (див. рис. 6). Надалі можна вибирати перегляд результатів щодо інших груп товарів або складів за допомогою фільтра.

До речі, якщо для фільтрації додати зрізи, проведення аналізу стане простим, наочним і цікавим.

Нове у зведених таблицях Excel 2013

Розглянемо докладніше нові можливості, які з’явилися у версії Excel 2013. Команди і приклади наведено для ознайомлювальної версії Excel 2013, яку із середини листопада 2013 року можна завантажити з російськомовним інтерфейсом.

Відразу звернемо увагу на корисні спрощення для тих користувачів, які не знають усіх можливостей зведених таблиць або ніколи ними не користувалися, — це новий розділ «Рекомендовані зведені таблиці».

Після натискання кнопки «Вставка», а потім «Рекомендовані зведені таблиці» (Insert → Recommended PivotTables) (у дужках наведено команди для версії Excel 2013 Preview) відкривається вікно, у лівій частині якого наведено 10 зразків ЗТ, що підходять для заданого діапазону (див. рис. 7). Можна попередньо переглянути кожну з них, а потім вибрати ту, яка найбільше підходить. Excel 2013 додасть зведену таблицю на новий аркуш і відобразить список полів, за допомогою яких можна змінити дані у звіті.

Але рекомендації не завжди є правильними, у цьому прикладі в деяких випадках Excel 2013 рекомендує підсумовувати ціну, хоча підсумовувати, звісно, потрібно виручку. Якщо вибраний шаблон не виправдав ваших очікувань, ви можете його виправити, додаючи необхідні поля й обчислення.

Якщо ви раніше використовували PowerPivot в Excel 2010, у новій версії програми ви зможете легко побудувати зведену таблицю за різними джерелами даних. У Excel 2013 ця можливість з’явилася і у звичайних ЗТ. Щоб скористатися нею, потрібно діапазони, за якими будуються зведені таблиці, перетворити на таблиці (Вставка → Таблиця).

Наприклад, дані про продаж будматеріалів знаходяться на двох аркушах — «Місяць» і «Склад». На першому аркуші немає даних у розрізі складів, а на другому — у розрізі місяців. Потрібно побудувати зведену таблицю і за складами, і за місяцями.

Є два варіанти створення зведеної таблиці за кількома джерелами: перший — вибрати як джерело даних обидві таблиці; другий — попередньо пов’язати таблиці.

Розглянемо перший варіант. Натискаємо: Вставка → Зведена таблиця. У діалоговому вікні «Створення зведеної таблиці» у розділі «Вибрати дані для аналізу» клацніть «Використовувати зовнішнє джерело даних» і «Обрати підключення».

На вкладці «Таблиці» будуть доступні для вибору таблиці, що існують. У нашому випадку це «Table1» і «Table2». Вибравши кожну з них по черзі (Відкрити), натискаємо «Ок». Після цього в порожньому шаблоні списку полів зведеної таблиці з’являться дві вкладки — «Активна» і «Всі». Вибравши вкладку «Всі», дістанемо можливість вибирати поля з двох таблиць. Вибравши відповідні поля, побачимо необхідну нам зведену таблицю (див. рис. 8).

Другий варіант передбачає попереднє зв’язування простих таблиць. Звісно, діапазони з даними мають бути відформатовані як таблиці. Натиснувши Дані → Відносини (Data → Relationship), потрапляємо у вікно створення зв’язків. За допомогою кнопки «Створити» (New) зв’яжемо дві таблиці, наприклад стовпчиком «№».

Потім виконаємо дії, аналогічні першому варіанту: натискаємо Вставка → Зведена таблиця. У діалоговому вікні «Створення зведеної таблиці» у розділі «Вибрати дані для аналізу» клацніть «Використовувати зовнішнє джерело даних» та «Обрати підключення». На вкладці «Таблиці» буде доступна «Модель даних» (Tables in Workbook Data Model). Натисніть «Відкрити» й «Ок». Подальші дії аналогічні першому варіанту.

Ще один спосіб, якщо ви створили просту зведену таблицю за однією таблицею. Натисніть у списку полів «Інші таблиці» (More Tables). У результаті з’являться усі доступні таблиці і, відповідно, їхні поля. Для правильної побудови буде запропоновано створити зв’язок між таблицями.

Тимчасова шкала — це більш зручний і візуальний зріз. Якщо в даних, за якими будується зведена таблиця, є дані у форматі часу, то побудувати тимчасову шкалу дуже легко. Цей інструмент дає змогу фільтрувати дані за роками, місяцями, кварталами чи днями. На вкладці «Аналіз» натисніть «Вставка тимчасової шкали» (Analyze → Insert Timeline) або Вставка → Тимчасова шкала (Insert → Timeline) (див. рис. 9).

У зведених таблицях тепер можна швидко опускатися на рівень нижче, щоб докладніше вивчити конкретні цифри. Для цього потрібно скористатися значком «Експрес-перегляд» (Quick Explore), який з’являється поряд із клітинками.

Для прикладу скористаємося зведеною таблицею, побудованою на основі моделі даних за групами товару і складами. Якщо встановити курсор на непідсумкове значення, рядок або стовпчик, побачимо що з’являється значок із лупою «Експрес-перегляд». Подивимося, наприклад, яким чином сформувалися дані за групою товару «профілі для гіпсокартону». Натиснувши на значок «Експрес-перегляд», вибираємо «Тип продукції», потім натискаємо «Деталізація до» (Drill To) (див. рис. 10). У результаті бачимо зведену таблицю за типами обраного товару і складами, а група товару переміститься у фільтр зведеної таблиці. За бажання можна звірити підсумкові цифри, вони збігатимуться.

Можна продовжити деталізувати інформацію. Наприклад, нас цікавлять виробники. Перебуваючи у рядку, знову натиснемо значок «Експрес-перегляд»; вибравши «Виробник», натискаємо «Деталізація до». Тип продукції переміщається в область фільтра, а внизу формується зведена таблиця із даними, які нас цікавлять.

Цікаво, що користуватися функцією «Експрес-перегляд» можна не лише у зведених таблицях, а й у побудованій зведеній діаграмі. Зауважимо також, що у попередній версії Excel 2010, коли ми будували зведену діаграму, створювався додатковий аркуш із зведеною таблицею, на якій і базувалася така діаграма. У новій версії такий аркуш не створюється, а зведена діаграма не залежить від зведеної таблиці.

Побудуємо кругову зведену діаграму (Вставка → Зведена діаграма), яка відображає виручку за групами товару. Обравши одну із часток (в інших типах діаграм це може бути, наприклад, стовпчик) і натиснувши на «Експрес-перегляд», виберемо «Деталізація до група товару». У результаті отримаємо діаграму з відображенням продажів за групами товару (див. рис. 11).

Замість післямови

Такий інструмент, як зведені таблиці, може бути корисний для фахівців, які хочуть дістати підсумкову інформацію швидко та в наочній формі. Причому за допомогою зрізів можна швидко фільтрувати і виділяти частину необхідних даних для аналізу. Коли потрібно не лише аналізувати дані, а й, наприклад, планувати якісь показники, складати бюджети, можливо, замість зведених таблиць ефективніше застосування OLAP-куби. Якщо необхідно проаналізувати і скласти звіт за великими обсягами первинних даних (мільйони рядків), зручно застосовувати надбудову до Excel 2010 PowerPivot, яка швидко обробляє такі обсяги даних (в Excel 2013 вона уже встановлена). Цікаві результати можна також отримати, побудувавши інтерактивні звіти за допомогою тепер уже стандартної надбудови в Excel 2013 Power View.

Дата підготовки
30.04.2014