4.7. Аналіз даних

У ЕТ є спеціаліні можливості для аналіза табличних даних, до них можно віднести Подбор параметров, Таблицы подстановки, Сценарии, Поиск решения, для великих таблиць – Сводные таблицы, Консолидация.

Подбор параметра.

Цей інструмент використовується для рішення задач аналіза даних, особливо має важливе значення для розв'язування задач зворотнього аналізу. Якщо треба розв’язати рівнення, накштал f(x)=а, де f(x) деяка функція, яка повинна набувати певного значення a шляхом зміни (перебіру) значень одного з залежних параметрів x. Процес рішення є ітераційним, тобто на точність рішення впливає кількість ітерацій та відносна погрешність, ці параметри можно встановити Сервис Параметры вкладка Вычисления (Рис.15).

Для рішення задачі треба в будь-яку клітинку занести формулу =f(х), вибрати цю клітинку і виконати команду Сервис  Подбор параметра...  в діалоговом вікні треба заповнити три поля:

• адресу клітинки з формулою (вона буде введена у поле автоматично, якщо клітинка з формулою була вибрана);

• бажане значення виразу у формулі, тобто а;

• адресу клітинки (изменяемой ячейки), де і отримаємо шуканий результат.

Рис.4.15. Робота інструмента Подбор параметра (Excel)

Таблица подстановки.

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

Рис.4.16. Результат використання Таблицы подстановки (Excel)

Для цього спочатку визначають місце під таблицю (А2:С5), клітинку А2 залишають порожнєю, тобто це місце шуканого значення х. У діапазоні А3:А5 вводять різні варіанти х, в В2 та С2 записують відповідні функції. Виокремлюють діапазон А2:С5 та обирають команди Данные Таблица подстановки… (Рис.16). У вікні у відповідному полі встановити адресу шукаємой клітинки А2. У результаті зя’виться таблиця з результатами подстановки різних значень змінних у одну чи декілько формул.

Сценарії

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

Рис.4.17. Вікно Диспетчера сценариев (Excel)

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

Звіт може бути наведений у вигляді структури (Рис.18) або зведеної таблиці (Рис.19).

Рис.4.18. Приклад Листа Структура сценария

Рис.4.19. Приклад Листа зведеної таблиці по сценарию

Поиск решения

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

Рис.4.20. Вікно інструмента Поиск решения (Excel)

Для того щоб скористатися цим інструментом, потрібно визначити деякі терміни.

Целевая ячейка – це клітинка, в якій розміщується формула, яка описує цільову функцію, для якої треба знайти максимальне, мінімальне чи задане значення.

Изменяемые ячейки – це клітинки, в яких знаходяться значення параметрів у вигляді або постійних чисел або формул, від яких зажить значення «целевой ячейки».

Поиск решения подбирає значення «изменяемых ячеек» доти, поки не буде знайдено рішення.

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

Спочатку обирають клітинки для майбутніх «изменяемых ячеек», та в будь яку клітинку вводять формулу «целевой функции» далі визивають Сервис Поиск решения… (Рис.20).

В вікні встановлюють адреси «целевой ячейки», «изменяемых ячеек», визначають яке треба отримати рішення - максимальне чи мінімальне.

Послідовно вводять «ограничения» скориставшись кнопкою Добавить у діалоговом вікні.

Змінювати та видаляти «ограничения» можно з допомогою кнопок Изменить, Удалить. Кнопкою Выполнить запустити на виконання.

Отримані результати будуть виведені на лист в обрані відповідні клітинки (Рис.21). Результати рішення для подальшого використання можно зберегти трьома способами:

• сохранить найденное решение или восстановить исходные значения на рабочем листе;

• сохранить решение в виде сценария;

• просмотреть любой из встроенных отчетов.

Рис.4.21. Приклад використання Поиска решения (Excel)

Зведені таблиці

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

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

Створення зведеної таблиці відбувається за допомогою Мастер сводных таблиц (Рис.22) командою Данные Сводная таблица (Ooo Calc ДанныеСводная таблицаЗапустить...)

Рис.4.22. Вікно Мастера сводной таблицы (Excel та Calc)

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

На першому етапі потрібно зазначити, на основі яких даних створюється таблиця. Як правило, це список, тому залиште перемикач в списке или в базе данньгх встановленим.

Друге вікно майстра, в якому пропонується ввести діапазон, що містить вихідні дані.

На третєм етапі майстра зведених таблиць формується макет (Рис.23) нової зведеної таблиці, створюється її структура та визначаються її функції. Для цього натисніть кнопку Макет і перемістіть кнопки з назвами полів у потрібну область рядків, стовпців або даних чи сторінок. Зони Строка, Столбец, Далее повинні мати відповідне поле. Інформація в зоні Далее, як правило, обробляється математично. Тому в ній розміщують числові поля. При переміщенні кнопки поля в область Далее програма вказує тип операції, яку здійснить з даними. За замовчуванням обчислюється сума. Для зміни типу операції слід перемістити кнопку Поля в область Данные і двічі клацнути лівою кнопкою миші на ній — відкриється вікно, в якому можна вибрати потрібну функцію обробки даних. Заповнювати область Страница не обов'язково.

Рис.4.23. Макет зведеної таблиці (Excel, Calc)

Виберіть місце розташування зведеної таблиці (наявний або новий робочий лист)  кнопка Параметри і введіть назву зведеної таблиці.

Майстер створить таблицю. Відкриється також панель інструментів Сводные таблицы. За допомогою цієї панелі можна змінювати вигляд і параметри вже побудованої таблиці (Рис. 4.24).

До готової таблиці можна застосувати будь-який автоформат, для цього потрібно виконати Формат Автоформат і вибрати відповідний формат таблиці. Якщо виб­раний автоформат не забезпечує потрібного числового форматування, можна змінити числовий формат даних зведеної таблиці. Для цього слід виділити будь-яку клітинку таблиці, на Панелі інструментів Сводные таблицы  кнопка Сводная таблица  Параметри поля  у вікні діалогу кнопка Формат, вибрати потрібний формат  дчі натиснути кнопку ОК.

Зміна розміщення полів. Вигляд таблиці можна змінити, перемістивши кнопки полів в інші зони, а також змінивши порядок відоб­раження поля в області стовпців або рядків зведеної таблиці. Для видалення поля із зведеної таблиці кнопку поля треба перемістити за межі макету.

Оновлення зведеної таблиці. Зведена таблиця динамічно пов'язана з базою даних, яку було використано при її створенні. Якщо значення в базі даних змінилися, треба виконати Данные Обновить данные або натиснути кнопку Обновить данные на панелі інструментів Сводные таблицы. Проте цей спосіб не підходить, якщо в базі даних з'являються нові рядки або стовпці. У цьому разі необхідно повернутися до майстра зведе­них таблиць і зазначити новий діапазон записів, що додаються до таблиці.

Рис.4.24. Приклад отриманої зведеної таблиці

Перейменування полів і елементів. Імена полів і елементів, що при­значені майстром зведених таблиць, можна змінити, відредагував­ши їх заголовки у зведеній таблиці.

Сортування елементів. Майстер зведених таблиць упорядковує елементи за їх збільшенням. Якщо треба впорядкувати елементи за їх зменшенням або за значеннями поля даних, необхідно скористатись Данные  Сортировка .

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

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

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

Групування елементів внутрішнього поля. Майстер зведених таблиць автоматично групує елементи внутрішнього поля для кожного заголовка зовнішнього поля і в разі потреби створює проміжні підсумки для кожної групи елементів внутрішнього поля. Проте іноді зручніше групувати елементи в інший спосіб. Щоб створити групу, слід виділити її елементи і виконати команду Данные  Структура Сгруппировать.

Створення діаграми на основі зведеної таблиці. Діаграму на основі зведеної таблиці можна побудувати так само, як і для будь-яких інших типів даних робочого аркуша, скориставшись кнопкою Мастер диаграмм на Панелі інструментів або Вставка Диаграмма. Щоб досягти найкращих результатів при створенні діаграми на основі зведеної таблиці, дотримуйтесь таких рекомен­дацій:

- видаліть з таблиці проміжні та загальні підсумки;

- прослідкуйте, щоб таблиця мала не більше двох полів у зоні стовпців і рядків;

- приховайте всі зайві елементи

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

Консолідація – це об’єднання даних з різних таблиць, розташованих у різних місцях, які мають однотипні дані в одну таблицю. Команда Данные Консолидация (Рис.24) може об'єднувати інформацію з вихідних листів (до 255) в один підсумковий лист. Вихідні листи можуть розміщуватись як у тій самій книзі, що й підсумковий лист, так і в інших.

Рис.4.24. Результат консолідації (Excel)

Команду Консолидация можна використовувати різними способами:

• створити зв'язки даних у підсумковому листі з вихідними даними, щоб наступні зміни у вихідних аркушах відображалися в підсумковому аркуші;

• консолідувати дані без створення зв'язків — за місцем розташування і категоріями. Розглянемо останні два способи консолідації.

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

- активізувати підсумковий лист і виділити в ньому діапазон клітинок, куди заноситимуть дані, що консолідуються далі виконати Данные Консолидация;

- вибрати функцію для опрацювання даних з кожного робочого листа, наприклад Среднее для усереднення значень з кожного робочого листа;

- опції у вікні діалогу залишити невстановленими;

- клацнути лівою кнопкою миші в полі Ссылка і перейти до першого робочого листа;

- виділити за допомогою миші область для початкового діапазону;

- натиснути кнопку Добавить;

- повторити попередні три пункти для кожного робочого листа.

Програма усереднює підсумкові дані й розміщує їх у підсумковому листі.

Консолідація за категоріями. За основу для об'єднання листів використовують заголовки стовпців або рядків. Наприклад, якщо стовпець "Січень" розміщується в одному робочому листі у стовпці В, а в іншому — у стовпці D, то все одно їх можна об'єднати. Зауважимо, що у підсумковому листі не зазначають заголовки рядків. Вони вставляться автоматично. Для консолідації даних, треба виконати такі дії:

- виділити в підсумковому листі область для розміщення даних. Вона повинна містити порожній стовпець А, щоб програма могла ввести заголовки консолідованих рядків. Ця область повинна містити стільки рядків, скільки унікальних елементів рядків (наприклад, прізвищ працівників) міститься в усіх таблицях Данные Консолидация;

- вибрати функцію для опрацювання даних з кожного робочого листа, наприклад Среднее для усереднення значень з кожного робочого листа;

- для консолідації за рядками встановити опцію значения левого столбца в секції Использовать в качестве имен;

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

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

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

Питання для самоперевірки

1. Для чого призначені сценарії?

2. Як побудувати сценарій?

3. Для розв'язання задач якого типу використовують Поиск решения?

4. Як запустити Поиск решения?

5. Призначення команди Консолидация.

6. Різновиди команди Консолидация.

7. Що таке зведені таблиці?

8. Як формується макет зведеної таблиці?

9. Які операції можна виконувати над даними у зведених таблицях?

10. Як побудувати діаграму на основі зведеної таблиці?