![]() |
Главная Случайная страница Контакты | Мы поможем в написании вашей работы! | |
|
Тема. Табличний процесор 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 - Гістограма аналізу продажу товарів по зведеній таблиці.
Завдання 3. Виконайте фільтрацію даних у таблиці за допомогою авто фільтру.
Методичні вказівки.
1. Виділить будь-яку чарунку початкової таблиці та виконайте команди: Данные – фільтр – автофильтр.
2. Із списку, що розчиняється, наприклад, рядка «Період» виберіть «березень».
В результаті маємо таблицю, що містить дані продажів у березні.
Для відновлення всіх рядків початкової таблиці необхідно виконати команди: Данные – Фильтр – Отобразить все.
Увага! Рядки таблиці, що відібрані при фільтрації можна редагувати, форматувати, створювати на їх основі діаграми, виводити на друк.
Індивідуальні завдання для виконання лабораторної роботи №5
1. Заповніть таблицю згідно варіанту.
2. Розрахуйте чарунки де містяться зірочки (*).
3. Створіть зведені таблиці згідно варіантам завдань.
4. Для третьої зведеної таблиці побудуйте об’ємну діаграму.
5. Виконайте фільтрацію даних у початковій таблиці за допомогою авто фільтру.
6. Роздрукуйте таблиці і діаграму.
7. Збережіть файл на диску, надавши йому ім’я (прізвище студента).
Варіант № 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
Відомість обсягу продажу
Місяць | Обсяг продажу | Замовник | Найменування товару | Відсоток знижки | Сума знижки | |
Червень 2011 | $47 926 | Амстор | Апельсини | * | ||
Липень 2011 | $45 875 | БУМ | Лимони | * | ||
Серпень 2011 | $28 800 | БУМ | Мандарини | * | ||
Вересень 2011 | $60 000 | Метро | Грейпфрут | * | ||
Жовтень 2011 | $58 051 | Сокіл | Банани | * | ||
Листопад 2011 | $53 438 | Атлант | Ананаси | * | ||
Березень 2012 | $72 938 | Метро | Апельсини | * | ||
Квітень 2012 | $44 000 | Сокіл | Лимони | * | ||
Травень 2012 | $85 900 | Атлант | Мандарини | * | ||
Червень 2012 | $82 253 | Амстор | Грейпфрут | * | ||
Грудень 2012 | $84 000 | Амстор | Апельсини | * | ||
Січень 2013 | $62 900 | БУМ | Лимони | * | ||
Лютий 2013 | $76 500 | БУМ | Мандарини | * | ||
Березень 2013 | $67 100 | Метро | Грейпфрут | * | ||
Квітень 2013 | $59 700 | Сокіл | Банани | * | ||
Травень 2013 | $82 041 | Атлант | Ананаси | * | ||
Червень 2013 | $61 300 | Амстор | Папайя | * | ||
Липень 2013 | $80 700 | БУМ | Авокадо | * | ||
Разом: | * | |||||
Використовуючи майстер зведених таблиць створіть зведені таблиці: | ||||||
a) Загального відсотку знижки та середнього відсотку знижки по замовникам у розрізі місяців. | ||||||
b) Загальної суми знижки та мінімальної суми знижки по найменуванням товарів у розрізі місяців. | ||||||
c) Максимального обсягу продажів та максимальної суми знижки по найменуванням товарів у розрізі замовників. |
Варіант № 4
Дата публикования: 2014-11-04; Прочитано: 557 | Нарушение авторского права страницы | Мы поможем в написании вашей работы!