ТОП 10:

Тема. Табличний процесор 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







Последнее изменение этой страницы: 2016-12-17; Нарушение авторского права страницы

infopedia.su Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав. Обратная связь - 3.233.215.196 (0.005 с.)