Лабораторна робота 5 (6 год.)

Тема роботи: „Аналіз даних в EXCEL 2000”

 

Мета роботи: використовуючи функції надбудов, навчитись здійснювати аналіз даних в програмі

Теоретична частина

1. Налаштування функцій надбудов

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

Мал. 4.1. Діалогове вікно Надстройки.

Після вибору потрібної опції у цьому вікні необхідно натиснути кнопку ОК, у результаті чого пункти меню доповняться відповідними командами.

2. Підведення підсумків

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

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

Для підведення підсумків у списку перш за все, потрібно відсортувати записи за категоріями (Данные Þ Сортировка).

Після впорядкування записів можна переходити безпосередньо до проведення підсумкових обчислень. Для цього необхідно активізувати одну з комірок таблиці та вибрати в меню Данные команду Итоги. При цьому відкриється діалогове вікно Промежуточные итоги (мал. 4.2), в якому необхідно задати назву стовпця, який буде визначати поділ списку на групи, визначити функцію, за якою будуть проводитись підсумкові обчислення, задати назву стовпця, дані якого будуть аналізуватись.

 

Мал. 4.2. Діалогове вікно Промежуточные итоги

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

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

Якщо потрібно роздрукувати кожну групу даних з підсумковими обчисленнями, потрібно встановити опцію Конец страницы между группами. Для знищення рядків з підсумками призначена кнопка Убрать все.

Зауваження. Створити підсумкову таблицю можна і за допомогою зви­чайних формул, проте цей процес буде набагато складнішим.

Результатом виконання функції на екрані з’являється структурований список (мал. 4.3). Отриману структуру можна використати для того, щоб:

l переглянути тільки загальні підсумки, зафіксувавши вказівник мишки на символі рядка рівня 1;

l вивести тільки проміжні і загальні підсумки зафіксувавши вказівник мишки на символі рядка рівня 2;

l побачити всі рівні деталізації списку, зафіксувавши вказівник мишки на символі рядка рівня 3.

 

Мал. 4.3. Зразок структурованого списка

Аналогічні дії виконують кнопки зі знаками ” + ” і ” – ”.

3. Створення зведеної таблиці

Таблиця, на основі якої створюється зведена, повинна містити заголовки рядків чи стовпців, які необхідно відобразити у результуючій таблиці. Ці заголовки утворюють поля даних. Створення зведених таблиць здійснюється за допомогою майстра зведених таблиць, який завантажується за допомогою команди Данные Þ Сводная таблица.

Дії майстра об’єднані в три основні кроки.

На третьому кроці необхідно створити макет зведеної таблиці. Для цього, натиснувши кнопку Макет, користувач повинен створити структуру таблиці, яка би найкраще відображала потрібну інформацію (мал. 4.4).

Мал.4.4. Створення макету зведеної таблиці

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

Майстер зведених таблиць дає можливість змінювати параметри таблиці, а саме форматувати вхідні та вихідні дані.

Кнопка Параметры, яка знаходиться у вікні третього кроку майстра зведених таблиць, дозволяє відкрити діалогове вікно для зміни параметрів таблиці або діаграми. Тут можна змінити параметри форматування та вигляд зведеної таблиці, а також параметри джерела даних таблиці. Відкрити це вікно можна також після створення таблиці, викликавши команду Параметры з меню (мал. 4.5).

Мал. 4.5. Панель інструментів Сводная таблица

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

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

4. Консолідація даних

Для створення зведеної таблиці на основі даних з різних таблиць використовується команда Консолидация меню Данные.

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

Мал. 4.6. Діалогове вікно Консолидация

Спочатку необхідно вибрати функцію (поле Функция), яка використовуватиметься при об’єднанні даних. Далі необхідно задати координати усіх діапазонів комірок з даними, які потрібно консолідувати (поле Ссылка). Якщо консолідовані діапазони розміщено на різних аркушах книги в однакових комірках, то після введення адреси першого діапазону немає потреби виділять інші діапазони, достатньо перейти на новий аркуш та натиснути кнопку. Під час виділення діапазону вікно Консолидация згортається до розмірів поля вводу. Цього самого результату можна досягти за допомогою кнопки мінімізації вікна . Після виділення кожного з діапазонів потрібно натискати кнопку Добавить. У діапазон комірок, який має консолідуватися з іншими, необхідно включати і заголовки рядків, інакше можна втратити орієнтацію в таблиці.

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

Якщо потрібно встановити зв’язок з вихідними даними, необхідно включити опцію Создавать связи с исходными данными. При цьому відбувається структурування таблиці. Консолідовані значення обчислюються на основі даних другого рівня структури

5. Створення таблиці підстановки

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

Основні прийоми застосування цієї процедури проілюстру­ємо на прикладах.