Зведена таблиця в Excel
Зведені таблиці є одним з найбільш могутніх засобів MS Excel з аналізу баз даних, розміщених у таблицях чи списках. Зведена таблиця не просто групує й узагальнює дані, але і дає можливість провести глибокий аналіз наявної інформації. Створюючи зведену таблицю, користувач задає імена полів, що розміщаються в її рядках і стовпцях. Допускається також завдання поля сторінки, що дозволяє працювати зі зведеною таблицею, як зі стопкою аркушів. Зведені таблиці зручні при аналізі даних з кількох причин:
- дозволяють створювати узагальнюючі таблиці, що надають можливість групування однотипних даних, підведення підсумків, підведення статичних характеристик записів;
- легко перетворюються;
- дозволяють виконувати автоматичний добір інформації;
- на основі зведених таблиць будуються діаграми, що динамічно перебудовуються разом зі зміною зведеної таблиці.
Опишемо покроковий процес створення зведеної таблиці на прикладі таблиці Замовлення (рис. 7.10). Для цього:
1. Виберіть команду Дані | Зведена таблиця. На екрані з'явиться перше вікно Майстра зведених таблиць (рис. 7.11).
Рис. 7.10 – Вихідні дані таблиці Замовлення
Рис. 7.11 - Перше вікно „Майстра зведених таблиць”
2. У першому вікні Майстра зведених таблиць під заголовком Створити таблицю на основі даних, що знаходяться треба вказати джерело даних для створення зведеної таблиці. Можливі чотири джерела даних, які вибираються за допомогою одного з перемикачів:
Перемикач Джерело даних
в списку чи бази даних Microsoft ExcelСписок чи таблиця, з позначеними стовпцями, розташована на робочому листі MS Excel
в зовнішньому джерелі данихФайли і таблиці, створені іншими програмами, наприклад, MS Access
у кількох діапазонах консолідаціїКілька списків чи таблиця з позначеними стовпцями, розташована на робочому листі MS Excel
в іншій зведеній таблиці чи діаграміІнша існуюча в активній робочій книзі зведена таблиця
Рис. 7. 12- Друге вікно „Майстра зведених таблиць”
Рис. 7.13 - Третє вікно „Майстра зведених таблиць”
Крім того, за допомогою перемикачів під заголовком Вид створюваного звіту можна задати вид зведеної таблиці: просто зведена таблиця чи зведена діаграма зі зведеною таблицею. У нашому випадку виберіть перемикачі в списку чи базі даних Microsoft Excel і зведена таблиця. Натисніть кнопку Далі.
Рис. 7.14 - Вікно „Майстер зведених таблиць і діаграм – макет”
3. На екрані з'явиться друге вікно Майстра зведених таблиць (мал. 7.12). На цьому кроці ви повинні вказати діапазон, що містить дані, по яких буде будуватися зведена таблиця. Якщо джерело даних знаходиться в іншій робочій книзі, то необхідно скористатися кнопкою Огляд. Отже, після введення посилання на діапазон даних натисніть кнопку Далі. На екрані з'явиться третє вікно Майстра зведених таблиць (мал. 7.13).
4. Спочатку натисніть кнопку Макет для створення структури зведеної таблиці. На екрані відобразиться вікно Майстер зведених таблиць і діаграм-макет (рис. 7.14). Перш ніж створювати макет зведеної таблиці, треба визначитися, яка інформація стане вводитися в області рядків, стовпців, даних і сторінок зведеної таблиці.
• Отже, для створення структури зведеної таблиці виберіть поле, що містить дані, по яких потрібно підвести підсумки, і перетягнете відповідну кнопку в область Дані. У нашому випадку, будемо підводити підсумки по Полю Вартість доставки. Для того щоб вибрати операцію, по якій підводяться підсумки, двічі клацніть по полю Вартість послуги, розташованої в області Дані. На екрані відобразиться вікно Обчислення поля зведеної таблиці (рис. 7.15).
Рис. 7.15. Вікно Обчислення поля зведеної таблиці
У списку Операція перераховані припустимі операцій: Сума, Кількість значень, Середнє, Максимум, Мінімум, Добуток, Кількість чисел, Зміщене відхилення, Незміщене відхилення, Зміщена дисперсія; Незміщена дисперсія. У даному випадку виберіть Сума. Список, що розкриває, Додаткові обчислення дозволяє розширити безліч припустимих операцій. Його значення: Ні, Відмінність, Частка, Приведена відмінність, З наростаючим підсумком у полі, Частка від суми по рядку, Частка від суми по стовпці, Частка від загальної суми, Індекс. У даному випадку виберіть Ні і натисніть кнопку ОК. Відбудеться повернення у вікно Майстер зведених таблиць і діаграм: макет.
• Для того, щоб помістити елементи поля в рядках, з міткою в лівій частині таблиці, перетягнете кнопку для обраного поля в область Рядок. У даному випадку ло рядкам будуть розміщатися дані про клієнтів, тому перетягнете поле Клієнт в область Рядок.
• Для того, щоб помістити елементи поля в стовпцях з міткою у верхній частині таблиці, перетягнете кнопку для обраного поля в область Стовпець. У даному випадку по стовпцях будуть розміщатися співробітники, що прийняли замовлення, тому перетягнете поле співробітники, що прийняли замовлення в область Стовпець.• Для того, щоб помістити елементи поля по сторінках, що дозволить працювати зі зведеною таблицею, як зі стопкою аркушів, перетягнете кнопку для обраного поля в область Сторінка. У даному випадку на кожній сторінці будемо виводити інформацію про клієнтів, яким здійснили виконання замовлення в один і той же день. Тому перетягніть поле дата виконання в область Сторінка.
Отже, у вікні Майстер зведених таблиць і діаграм - макет створена структура майбутньої зведеної таблиці (рис. 7.16). Натисніть кнопку ОК і, таким чином, поверніться до третього вікна Майстра зведених таблиць.
Рис. 7.16 - Вікно Майстер зведених таблиць і діаграм – макет зі структурою майбутньої зведеної таблиці
Встановимо параметри зведеної таблиці. Як правило, ті значення параметрів, що встановлені за замовчуванням і є оптимальними, їх не треба змінювати. Але для того, щоб краще розібратися з тим, як набудовується зведена таблиця, натисніть кнопку Параметри. На екрані відобразиться вікно Параметри зведеної таблиці (рис. 7.17). У вікні, під полем Ім'я, у яке вводиться ім'я зведеної таблиці, знаходяться дві групи параметрів Формат і Дані. З групи Формат відзначимо тільки прапорці, загальна сума по рядках і загальна сума по стовпцях, що визначають, чи необхідно підводити підсумки по рядках і стовпцях. Дамо характеристику прапорця зберегти дані разом з таблицею групи Дані. При побудові зведеної таблиці всі дані копіюються в сховану кеш-пам'ять. Зведена таблиця відображає зміст цієї кеш-пам'яті, а не вихідного діапазону даних. Якщо вже після того, як зведена таблиця побудована, ви зміните дані, на основі яких вона була побудована, то це не приведе до автоматичної зміни даних у зведеній таблиці. Зведена таблиця не є динамічною таблицею, що автоматично обновлюється при модифікації даних, на основі яких вона побудована. Для відновлення зведеної таблиці потрібно виділити будь-яку чарунку зведеної таблиці і вибрати команду Дані | Обновити дані. При цьому в кеш-пам'ять будуть знову занесені дані, на основі яких будується зведена таблиця. Прапорець зберегти дані разом з таблицею визначає, чи буде в кеш-пам'ять зберігатися старі дані, при змінах зведеної таблиці чи її обновлені.
Рис. 7.17 - Вікно Параметри зведеної таблиці
5. У групі Помістити таблицю в (див. рис. 7.13) є два перемикачі новий лист і існуючий лист, що задають місце розташування зведеної таблиці. Якщо обраний перемикач існуючий лист, то в поле треба привести посилання на верхню ліву чарунку діапазону, де буде розташовуватися таблиця. У нашому випадку виберіть перемикач новий лист і натисніть кнопку Готово.
Зведена таблиця побудована. Для того щоб зведена таблиця не була занадто громіздкої, обмежимося переглядом одержувачів, яким виконано поставку 18.01.2003р. (співробітники, що прийняли замовлення Антонюк і Дрозд) (рис. 7.18). Натиснувши кнопку Майстер діаграм панелі інструментів Зведена таблиця, до зведеної таблиці можна додати зведену діаграму (рис. 7.19).
Рис. 7.18 – Зведена таблиця
Рис. 7.19 – Зведена діаграма
ЛІТЕРАТУРА
1.Бухвалов А.В. и др. Финансовые вычисления для профессионалов.- СПб.: БХВ-Петербург, 2001.-320с. ил.
2.Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах.- СПб.: БХВ-Петербург, 2001.- 816с.:ил.
3.Евдокимов В.В. и др. Экономическая информатика. Учебник для вузов. Под ред. Д.э.н., проф. В.В.Евдокимова. – СПб.: Питер, 1997. – 592с.
4.Згуровський М.З., Коваленко І.І., Міхайленко В.М. Вступ до комп’ютерних інформаційних технологій: Навч.посіб. – К.: Вид-во Європ. ун-ту (фінанси, інформ. системи, менеджм. і бізнес), 2000.- 265 с.
5.Информатика. Базовый курс/ Симонович С.В. и др.- СПб.: Питер, 2000.- 640с.:ил.
6.Карлберг, Конрад. Бизнес-анализ с помощью Excel.: Пер с англ.- К.: Диалектика, 1997.- 448с.: ил.
7.Лук‘янова В.В. Комп‘ютерний аналіз даних: Посібник. – К.: Видавничий центр „Академія”, 2003. – 344с. (Альма-матер)