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

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

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

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

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

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

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

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

 

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

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

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

 

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

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

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

 

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

1.            Для створення зведеної таблиці відкрийте вкладку Вставка, де в групі Таблиці виберіть команду Вільна таблиця.
2.       Відкриється наступне діалогове вікно: У цьому вікні Excel пропонує нам вказати вихідну таблицю або діапазон значень, на підставі яких буде будуватися зведена таблиця. Якщо Ви виконали команду Зведена таблиця, попередньо встановивши курсор на листі, де знаходяться будь-які дані, Excel автоматично заповнить це поле. Якщо ж на листі дані відсутні, або вони знаходяться в іншому місці, Вам потрібно буде вказати адресу діапазону даних вручну. І остання опція, яку потрібно встановити у цьому вікні - вибрати місце розташування зведеної таблиці: у новому вікні або на цьому ж листі. В останньому випадку потрібно вказати діапазон адрес, де повинна розташовуватися зведена таблиця.
3.  Натиснувши кнопку Ок після налаштування потрібних нам умов, ми отримуємо наступний робочий лист: Для нашого прикладу спробуємо створити таблицю, яка буде підсумовувати дані Обсяг продажів, шт. і Сума виручки для кожного значення в стовпці Дата і для кожної Точки продажи. Для цього потрібно виконати наступні дії:
а) у верхній частині вікна налаштувань відзначаємо всі назви необхідних нам стовпців:
б) Поле Точка продаж перетягуємо в область Фильтр отчета. У цьому випадку Excel додає на робочий лист фільтр, за допомогою якого ми встановлюємо умову для виведення підсумкових даних. Вибравши в нашому прикладі точку продажу, ми зможемо виводити підсумки з продажу для окремого магазину.     в) Поле Дата перетягуємо в область Названия строк. Excel використовує значення зі стовпця Дата для того, щоб озаглавити рядки нашої таблиці. Таким чином, ми будемо підсумовувати потрібні нам поля по кожній даті нашого звіту. г) Поля Сума по полю Об’єм продаж, шт. і Сума по полю Сума виручки перетягуємо в область Значения. Дані всіх стовпців з цієї області Excel підсумує і відобразить в рядках зведеної таблиці.
Налаштування нашої таблиці повинне виглядати ось так:
Тоді наша зведена таблиця буде мати такий вигляд:

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

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

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