Тема. Табличний процесор MS Excel. Аналіз даних за допомогою зведених таблиць.

Мета роботи: навчитися проводити аналіз даних за допомогою зведених таблиць в табличному процесорі MS Excel.

 

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

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

Завдання 1. Підготуйте початкові дані для побудови зведеної таблиці, або завантажте готовий файл общие_сводные.xls. Створіть зведену таблицю об'єму продажу по товарам і регіонам (використовуючи майстер зведених таблиць).

У таблиці приведені дані про об'єми продажу телевізорів різних виробників по регіонам за три місяці 2005 року.

Увага!!! Всі стовпці мають заголовки - найменування, які активно використовуватимуться в зведеній таблиці. Заголовки таблиці не повинні містити об'єднаних чарунок. При наборі цієї таблиці активно користуйтеся буфером обміну для копіювання ідентичних назв товарів, регіонів, періодів.

 

Методичні вказівки.

1. Виберіть пункт меню Данные – Сводная таблица…. Буде запущений майстер.

2. На першому кроці запрошуються початкові дані для зведеної таблиці - виберіть перемикач в списке или базе данных Microsoft Excel і натисніть кнопку Далее>.

3. На другому кроці виділіть діапазон чарунок A2:D29. Натисніть Далее>.

Увага!!! При виділенні діапазону, захоплюємо заголовок таблиці, а нижній рядок з підсумками не беремо.

4. На третьому кроці, безпосередньо створюється зведена таблиця - на макет, показаний на рис. 1, перетягуємо заголовки таблиці в потрібні області. ОК.

Рисунок 1. Макет для створення зведених таблиць

Увага!!! Якщо необхідно вибрати операцію для поля, яке обчислюється клацніть на ньому двічі. Відкриється діалогове вікно Вычисление поля сводной таблицы. Виберіть потрібну операцію (рис.2).

 

 

Рис.2. Діалогове вікно Вычисление поля сводной таблицы.

Увага!!! Можна перетягувати кнопки (назв стовпців) як на макет, так і з макету на Панель инструментов Сводная таблица. Таким чином, зведена таблиця завжди може бути реорганізована. Поля із списками в зведеній таблиці дозволяють одержувати зрізи даних.

 

4. На третьому кроці вибираємо місце розташування таблиці.

 

 

Зведена таблиця може мати вигляд, як на рис. 3. Як видно, в ній стовпець Об'єм продажу підрахований і узагальнений по стовпцях Регіон і Товар по всім місяцях. Поля Період, Регіони і Товари є списки, які дозволяють вибирати і проглядати зрізи даних по деяких критеріях. У полі Сума по полю Об'єм продажу можна задавати інші операції.

Рисунок 3. - Зведена таблиця по товарах і регіонах.

Готова зведена таблиця легко реорганізується простим перетягуванням назв стовпців. Наприклад, поля Період і Регіон можна поміняти місцями і т.ін.

Завдання 2. Проаналізуйте зрізи даних по місяцях, розкривши поле із списком Період. Розгляньте основні кнопки: панелі інструментів Сводная таблица.

Увага!!! Якщо ви випадково закрили Панель інструментів Сводная таблица, то для її відображення клацніть правою кнопкою миші по Панелі інструментів Excel і в списку, що розкрився, виберіть еп1ёяґпотрібну панель.

Разом із зведеною таблицею на екран виводиться Панель інструментів Сводная таблица. Розглянемо її основні кнопки:

- Формат отчета дозволяє додати зведеній таблиці "презентабельний" для друку вигляд;

- Мастер диаграмм дозволяє автоматично побудувати діаграму по зведеній таблиці;

- Отобразить детали дозволяє деталізувати зведену таблицю по деякому параметру. Наприклад, виділіть поле Товар, натисніть кнопку, далі в окремому вікні Показати деталі виберіть Період і натисніть ОК - товари будуть деталізовані по всіх періодах;

- Отключить детали відміняє деталізацію;

- Восстановить данные дозволяє відновити узагальнені дані в зведеній таблиці у разі зміни початкових даних. Змініть числові дані в стовпці Об'єми продажів початкової таблиці. У зведеній таблиці і на діаграмі зміни відбудуться тільки після оновлення даних, тобто натиснення кнопки;

- Параметры поля дозволяє встановити узагальнену операцію по вибраному полю. Наприклад, виділіть на зведеній таблиці поле Сума по полю Об'єм продажів і клацніть по кнопці. У вікні, що з'явилося, замість операції Сума задайте операцію Мінімум (або іншу). Натисніть ОК, проаналізуйте результат, а потім відмініть результат кнопкою.

 

Завдання 3. Д ля одержаної зведеної таблиці побудуйте об’ємну діаграму.

Методичні вказівки.

1. Клацніть по зведеній таблиці для її виділення. На панелі інструментів Сводная таблица натисніть кнопку Мастер диаграмм. Буде побудована гістограма з накопиченням.

2. Змініть тип гістограми: ще раз натисніть кнопку Мастер диаграмм (можна клацнути правою кнопкою миші по самій гістограмі і із списку, що з'явився, вибрати Тип диаграммы, але тоді гістограма буде побудована на окремому листі). У вікні, що з'явилося, виберіть вигляд - об’ємна гістограма і натисніть Далее.

3. У наступному вікні Мастер диаграмм натисніть Далее.

4. У наступному, останньому вікні вкажіть, куди ви хочете помістити діаграму: виберіть перемикач окремому. Натисніть кнопку Готово. Гістограма буде перетворена в об’ємний вигляд, як на рис. 4. Списки на діаграмі дозволяють вибирати і проглядати зрізи даних так само, як на зведеній таблиці.

 

Рисунок 4 - Гістограма аналізу продажу товарів по зведеній таблиці.

 

Індивідуальні завдання для виконання лабораторної роботи №5

 

1. Заповніть таблицю згідно варіанту.

2. Розрахуйте чарунки де містяться зірочки (*).

3. Створіть зведені таблиці згідно варіантам завдань.

4. Для третьої зведеної таблиці побудуйте об’ємну діаграму.

5. Роздрукуйте таблиці і діаграму.

6. Збережіть файл на диску, надавши йому ім’я (прізвище студента).

 

Варіант № 1 Відомість поставки продукції
Дата	Кількість	Продукт	Країна постачальник	Ціна за одиницю	Сума
Січень	30 000	Ґудзики	Франція		*
Січень	240 000	Застібки	Італія		*
Лютий	121 000	Ґудзики	Франція	1,7	*
Січень	98 000	Ґудзики	Франція	1,7	*
Березень	345 000	Застібки	Італія		*
Лютий	215 000	Ґудзики	Канада		*
Березень	480 000	Застібки	Франція	6,8	*
Січень	212 000	Ґудзики	Італія	3,6	*
Квітень	420 000	Ґудзики	Канада	4,5	*
Травень	540 000	Ґудзики	Франція	1,7	*
Травень	311 000	Ґудзики	Італія	3,6	*
Червень	120 000	Застібки	Канада		*
Травень	98 000	Ґудзики	Франція	1,7	*
Травень	300 000	Ґудзики	Італія	3,6	*
Червень	120 000	Застібки	Італія		*
Разом:					*
Використовуючи майстер зведених таблиць створіть зведені таблиці:
a) загальної суми поставки продуктів у розрізі країн постачальників;
b) кількості продукції по товарах і країнах у розрізі дат;
c) загальної суми поставки і середньої ціни продукції по датах
Варіант № 2 Данні про прибуток за рік
Місяць	Відділ	Дохід	Витрати	Рентабельність (гр.3/гр.4)	Прибу-ток (гр.3 - гр.4)
Січень	взуття дитяче	127 735	45 495	*	*
Лютий	взуття дитяче	127 246	47 710	*	*
Березень	взуття дитяче	127 289	48 402	*	*
Квітень	взуття дитяче	127 169	47 217	*	*
Травень	взуття дитяче	131 330	49 082	*	*
Червень	взуття дитяче	130 996	49 862	*	*
Липень	взуття дитяче	131 054	51 872	*	*
Серпень	взуття дитяче	135 284	61 427	*	*
Вересень	взуття дитяче	138 903	62 342	*	*
Жовтень	взуття дитяче	136 368	62 353	*	*
Листопад	взуття дитяче	135 199	60 571	*	*
Грудень	взуття дитяче	135 144	59 848	*	*
Січень	Одяг жіночий	400 000	256 354	*	*
Лютий	Одяг жіночий	558 654	425 862	*	*
Разом					*
Використовуючи майстер зведених таблиць створіть зведені таблиці:
a) загальної суми прибутку по відділах і місяцях;
b) середньої суми доходів по відділах у розрізі місяців;
c) загальної суми витрат і прибутку по відділах у розрізі місяців.

 

Варіант № 3

Відомість обсягу продажу

Місяць	Обсяг продажу	Замовник	Найменуван-ня товару	Відсоток знижки	Сума знижки
Червень 2004	$47 926	Амстор	Апельсини		*
Липень 2004	$45 875	БУМ	Лимони		*
Серпень 2004	$28 800	БУМ	Мандарини		*
Вересень 2004	$60 000	Метро	Грейпфрут		*
Жовтень 2004	$58 051	Сокіл	Банани		*
Листопад 2004	$53 438	Атлант	Ананаси		*
Грудень 2004	$52 920	Амстор	Папайя		*
Січень 2005	$66 247	БУМ	Авокадо		*
Лютий 2005	$55 453	БУМ	Яблука		*
Березень 2005	$72 938	Метро	Апельсини		*
Квітень 2005	$44 000	Сокіл	Лимони		*
Травень 2005	$85 900	Атлант	Мандарини		*
Червень 2005	$82 253	Амстор	Грейпфрут		*
Липень 2005	$61 300	Амстор	Банани		*
Серпень 2005	$76 000	БУМ	Ананаси		*
Жовтень2005	$92470	Сокіл	Авокадо		*
Листопад2005	$60 000	Атлант	Яблука		*
Грудень 2005	$84 000	Амстор	Апельсини		*
Січень 2006	$62 900	БУМ	Лимони		*
Лютий 2006	$76 500	БУМ	Мандарини		*
Березень 2006	$67 100	Метро	Грейпфрут		*
Квітень 2006	$59 700	Сокіл	Банани		*
Травень 2006	$82 041	Атлант	Ананаси		*
Червень 2006	$61 300	Амстор	Папайя		*
Липень 2006	$80 700	БУМ	Авокадо		*
Разом:					*
1) Використовуючи майстер зведених таблиць створіть зведені таблиці:
a) загального обсягу продажів по найменуваннях товарів у розрізі замовників;
b) суми знижки замовникам у розрізі товарів;
c) середнього відсотка знижки по товарах і датах у розрізі замовників.

 

Варіант № 4