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

 

Що ж таке зведені таблиці, і навіщо вони потрібні? Ми часто стикаємося з ситуаціями, коли у нас є багато різноманітних даних (які можна назвати статистичними), але нас цікавлять якісь загальні висновки або проміжні підсумки.

Наприклад, у нас є інформація про продажі мобільних телефонів в мережі магазинів мобільного зв'язку. Всього в мережі є три магазини, які щодня повідомляють нам, які моделі телефонів вони продали, в якій кількості та за якою ціною.

За 17 днів продажів у нас вийшла велика таблиця на 350 записів. Але ця таблиця не вирішує наших проблем. Нам необхідно дізнатися обсяги продажів у грошовому та кількісному виразі по датах і по окремих магазинах, але як це зробити? Сортувати таблицю і підсумовувати окремі її частини? Це вимагає часу, а завтра надійдуть нові дані, і всю роботу потрібно буде знову повторити.

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

Як Ви бачите на ілюстрації, всі дані автоматично згрупувалися по датах. Тепер можна відразу побачити кількість проданих телефонів і загальну суму виторгу. Крім того, використовуючи фільтр - список, який знаходиться в лівому верхньому кутку сторінки, ми можемо відобразити узагальнені дані по окремо взятому магазину. Для цього достатньо натиснути на значок фільтра в правій частині клітинки В2, і вибрати потрібний нам магазин зі списку:

Таблиця відразу ж відобразить потрібні нам результати:

Цей приклад наочно демонструє переваги зведених таблиць, до яких відносяться:

 

-       Дуже простий спосіб створення такої таблиці, який не потребує багато часу;

-       Можливість консолідувати дані з різних таблиць і навіть з різних джерел;

-       Можливість оперативно доповнювати дані зведеної таблиці, просто розширивши вихідну таблицю і трохи змінивши вигляд зведеної.

 

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

Слід пам'ятати, що користувач не може змінити значення окремої клітинки у зведеній таблиці. Для цього потрібно змінити дані вихідної таблиці.

Для створення зведеної таблиці нам потрібна проста базова таблиця.

 

Алгоритм створення зведеної таблиці

1.            Для створення зведеної таблиці відкрийте вкладку Вставка, де в групі Таблиці виберіть команду Вільна таблиця.

 

2.       Відкриється наступне діалогове вікно:

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

І остання опція, яку потрібно встановити у цьому вікні - вибрати місце розташування зведеної таблиці: у новому вікні або на цьому ж листі. В останньому випадку потрібно вказати діапазон адрес, де повинна розташовуватися зведена таблиця.

 

 

 

 

3.  Натиснувши кнопку Ок після налаштування потрібних нам умов, ми отримуємо наступний робочий лист:

Для нашого прикладу спробуємо створити таблицю, яка буде підсумовувати дані Обсяг продажів, шт. і Сума виручки для кожного значення в стовпці Дата і для кожної Точки продажи. Для цього потрібно виконати наступні дії:

а) у верхній частині вікна налаштувань відзначаємо всі назви необхідних нам стовпців:

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

 

 

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

г) Поля Сума по полю Об’єм продаж, шт. і Сума по полю Сума виручки перетягуємо в область Значения. Дані всіх стовпців з цієї області Excel підсумує і відобразить в рядках зведеної таблиці.

Налаштування нашої таблиці повинне виглядати ось так:

 

Тоді наша зведена таблиця буде мати такий вигляд:

 

Тепер ми відразу можемо дізнатися обсяги продажів мобільних телефонів в грошовому та кількісному виразі на будь-яку потрібну нам дату як загалом по мережі, так і за окремим магазином

Розглянемо додаткову задачу. Припустимо, нам потрібно дізнатися дані з обсягів продажу не тільки в розрізі магазинів, але і в розрізі торгових марок, і навіть окремих моделей.

Для цього досить у вікні налаштування відзначити галочками два додаткові поля - Марка телефону та Модель телефону, і перетягнути ці поля в область Фільтр звіту. Excel додасть до зведеної таблиці два нових фільтра, які допоможуть нам швидко дізнатися потрібну інформацію:

 

Форматування зведеної таблиці.

При створенні нової зведеної таблиці Excel автоматично іменує її стовпці та заголовки. Однак це легко виправити - досить відредагувати клітинку заголовка стовпця або таблиці. Наприклад, ми перейменували попередній заголовок таблиці:

або в більш зрозумілій

Тепер ми спробуємо змінити зовнішній вигляд таблиці. Excel пропонує дуже зручний інструмент автоматичного форматування з використанням готових стилів. Для встановлення готового стилю таблиці необхідно виконати клік  по області розміщення зведеної таблиці - в панелі інструментів відкриються вкладки під загальною назвою Работа со сводными таблицами. Перейдіть на вкладку Конструктор і у групі Стили сводной таблицы виберіть той стиль, який відповідає Вашим смакам. Наприклад, ось такий:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Крім використання готового стилю Ви, звичайно ж, можете форматувати окремі комірки, рядки та стовпці зведеної таблиці звичайними засобами форматування Excel.

У групі Параметры стилей сводной таблицы на цій же вкладці можна налаштувати обраний стиль, включивши чергування рядків або чергування стовпців, а також додати або прибрати заголовки рядків і стовпців. Ми додали чергування рядків:

 

Група Макет вкладки Конструктор містить кнопки, які дозволяють налаштувати сам макет нашої таблиці, а саме - Макет звіту, проміжні результати, Загальні результати та Порожні рядки.

Команда Макет звіту пропонує такі варіанти:

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

 

Показати в табличній формі - виводить дані у форматі звичайної таблиці, в якому можна легко копіювати клітинки на інші сторінки:

 

Відмінність табличної форми від форми структури полягає тільки в тому, що форма структури виводить дані сходинками, а не порядково, що більш зручно для перегляду.

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

Команда Загальні результати пропонує вивести загальні підсумки лише для рядків, тільки для стовпців, для рядків і для стовпців одночасно, або не виводити їх взагалі.

Команда Порожні рядки додає в макет зведеної таблиці додатковий порожній рядок після кожної групи даних. Ось так виглядає таблиця до включення цієї опції:

 

а ось так після: