1.1. Аналіз даних
Для аналізу великих списків даних у програмі Excel передбачені спеціальні засоби.
Під аналізом даних тут розуміються методи, що дозволяють краще зрозуміти тенденції і закономірності, яким підкоряються табличні дані. Опорні таблиці (Pivot Table), включені в п'яту версію програми Excel, призначені для аналізу співвідношень між даними в списку. Опорні таблиці будуються за допомогою Конструктора таблиць за чотирьох кроку. Структуру опорної таблиці можна легко змінити в інтерактивному режимі шляхом переміщення назв поля даних з однієї частини таблиці в іншу.
Крім опорних таблиць, у програмі Excel є й інші методи аналізу наборів даних. Дуже корисної є директива пошуку розв’язку рівнянь, що по заданих значеннях поляж знаходить інші значення, що задовольняють визначеним співвідношенням.
Опорні таблиці.
Директивою PivotTable із меню Data відкрийте діалогове вікно Конструктора опорних таблиць. У цьому вікні за чотирьох кроку потрібно побудувати опорну таблицю.
На першому кроку варто зазначити джерело надходження даних. Якщо вихідні дані знаходяться в списку, то клацніть по селекторній кнопці Microsoft Excel List or Database і перейдіть до наступного кроку за допомогою командної кнопки Next.
На другому кроку потрібно зазначити область робочого листа, де знаходиться список. По умовчанню програма пропонує список, що розташований на активному робочому листі. Тут можна зазначити тільки частину списку, якщо для побудови опорної таблиці інша частина не потрібна. Клацніть по командній кнопці Next, щоб перейти до наступного кроку.
На третьому кроку задається структура (Layout) опорної таблиці. У правому полі розташовані назви поляж списку, а в середньому - представлення структури опорної таблиці. У це поле включається поле назв рядків, поле назв стовпців і поле даних. Щоб задати структуру таблиці, варто відбуксирувати мишею назви поляж списку у відповідні поля таблиці. При побудові опорної таблиці притримуйтеся наступних правил. Назви поляж, за значеннями яких буде проводиться аналіз даних, відбуксируйте в поле даних Data. У поле назв рядків Row і в поле назв стовпців Column перемістите назви полів, за значеннями яких класифікуються (розбиваються) дані. Потім клацніть по командній кнопці Next, щоб перейти до наступного кроку.
На четвертому кроку можна установити деякі опції для побудови таблиці. У поле введення Pivot Table Starting Cell вказується адреса, із якого починається побудова таблиці (лівий верхній кут). У поле введення Pivot Table Name можна зазначити назву таблиці. По умовчанню програма використовує назву PivotTable1 із наступним збільшенням номера таблиці. Якщо включити контрольні індикатори Grand Totals for Columns і Grand Totals for Rows, те в кожному рядку й у кожному стовпці опорної таблиці будуть обчислюватися загальні суми. Контрольний індикатор Save Data With Table Layout служить для зберігання опорної таблиці з заданою структурою. Вмикання контрольного індикатора AutoFormat Table дозволяє автоматично сформувати опорну таблицю. Після закриття діалогового вікна Конструктора командною кнопкою Finish програма Excel розміщає опорну таблицю в зазначеній позиції.
Між опорною таблицею і вихідним списком виникає статичний зв'язок, тобто зв'язок, що існує тільки в момент побудови таблиці. При зміні даних у списку опорна таблиця не буде змінюватися. Щоб привести у відповідність уміст таблиці і вміст списку, потрібно викликати директиву Refresh Data із меню Data.
Редагування опорних таблиць.
Назви полів, по яких класифікуються дані, можна переміщати за допомогою миші, щоб змінити структуру таблиці. Якщо в опорну таблицю необхідно додати нові поля, то викликайте директиву PivotTable із меню Data або клацніть по піктограмі Конструктора таблиць на опорної (Pivot) панелі. Після цього з'явиться третє вікно Конструктора опорних таблиць, у якому можна змінити розміщення значень поляж у таблиці. Стежите за тим, щоб при виклику цієї директиви покажчик комірок знаходився усередині таблиці, у противному випадку Конструктор почне побудову нової таблиці.
Перебування значень.
Іноді буває заздалегідь відомо, яким повинний бути результат обчислення деякої функції, але невідомо, при яких значеннях аргументу він досягається.
Припустимо, що нам треба знайти максимальну суму кредиту, що можна дати на трьох року по фіксованій процентній ставці, якщо клієнт готовий повертати по 2000$ щомісяця.
У програмі Excel є функція PMT, що за значеннями суми кредиту, періоду і процентної ставки обчислює розмір щомісячного внеску для погашення кредиту. Нам потрібно вирішити зворотну задачу, коли заздалегідь відомий розмір що щомісяця повертається суми і потрібно знайти суму кредиту. Для розв’язку цієї задачі викликайте директиву Goal Seek із меню Tools.
Відкриється діалогове вікно цієї директиви. У поле введення Set Cell: цього вікна зазначте адресу комірки, у якій розташоване задане значення функції (у нашому випадку це комірка, де знаходиться функція PMT). У поле введення To Value зазначте значення функції, що у нашому випадку дорівнює 2000$. У поле введення By changing Cell зазначте адресу комірки, у якій програма зможе підбирати потрібне значення, що задовольняє заданим умовам. У нашому випадку тут треба зазначити адресу комірки, де знаходиться значення суми кредиту. Закінчивши установки поля, клацніть по командній кнопці OK.
Шляхом добору значень у зазначеній комірці програма Excel намагається знайти максимальне наближення до заданого цільового значення функції. Результат з'являється в діалоговому вікні. Якщо Вас задовольняє розбіжність між знайденим і цільовим значенням, то закрийте діалогове вікно щигликом по кнопці OK, після чого знайдене значення з'явиться в таблиці. У противному випадку клацніть по командній кнопці Cancel, щоб не передавати значення в таблицю.