Аналіз даних засобами Excel

Розглянуті функції дають змогу проводити р₀ рахунки в електронних таблицях Excel для отримали * певних результатів. Водночас існує і потужний інструмент аналізу даних, за допомогою якого можна виявити тенденцію зміни тих чи інших величин, виконати прогнозування на базі виявлених тенденцій, досягти оптимальних результатів в економічній діяльності. До засо­бів аналізу даних належать такі інструменти, як підбір параметрів, таблиці підстановки, консолідація даних зведені таблиці, пошук рішень тощо.

Підбір параметрів

Щоб визначити значення однієї комірки при зміненні значення іншої (такі комірки мають бути пов'язані формулою), використовують підбір параметрів.

Наприклад, необхідно визначити термін кредиту, за якого перший внесок (поле «Всього до сплати, грн.») становить 500 грн. (у таблиці на рис. 5.35 — 566,7); формули для розрахунку відображені на рис. 5.36.

Для цього спочатку встановлюють курсор у комірку G2, після чого активізують команди Сервис, Подбор па­раметра. Унаслідок цих дій на екрані дисплея з'явля­ється вікно (рис. 5.37), в якому в полі Установить в ячейке: вводять адресу комірки, значення якої необхіднознайти; в полі Значение: — числове значення, яке потрібно знайти для активної комірки (G2); в полі Из-меняя значение ячейки: — адресу комірки (С2), значен­ня якої необхідно змінити, й активізують кнопку ОК-

Після цього в наступному вікні (рис. 5.38) виводить­ся результат виконання операції.

Якщо рішення знайдено, то при активізації кнопки ОК нове значення зали­шається в комірці (G2), а кнопки Отмена — відновлю­ється попереднє значення.

У результаті знайдений новий термін кредиту стано­вить 28,6 місяців (рис. 5.39).

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

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

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

Наприклад, куплено облігацію, номінальна вартість якої становить 2000 грн., виплата за нею — 100 грн. на рік, відсоткова ставка — 12%, термін дії облігації — 10 років. Необхідно проаналізувати зміну вартості облігації залежно від терміну погашення. Вартість облігації розраховують за допомогою такої функції:

ПС (Ставка; Кпер; Плт; Бс; Тип),

де Ставка — відсоткова ставка за період (у прикладі — 12%); Кпер — загальна кількість платежів (10 років). Плт — виплата за кожний період (100 грн.); Бс — вели­чина майбутньої вартості, якщо вона дорівнює 0, аргу­мент не враховують; Тип — число 0 або 1, яке означає коли відбувається поточна виплата: в кінці періоду (як­що за замовчуванням, то аргумент не беруть до уваги) або на початку.

Для розв'язання такої задачі в комірки (рис. 5.40) слід внести такі початкові значення: номінальна вар­тість — В2; термін погашення — ВЗ; виплата — В4; від­соткова ставка — В5.

Діапазон комірок А8:А18 заповнюють числами ВІД 10 до 0 (кількість років до погашення облігації), в ко­мірку В8 вносять формулу для розрахунку поточного значення виплат (=ПС(12%;ВЗ;100;2000). Після цього Ціляють діапазон комірок А8:В18, активізують команди Данные, Таблица подстановки. Внаслідок цих Нерадій на екрані дисплея з'являється вікно, в якому в полі Подставлять значение по строкам в вносять адресу «З і натискають на кнопку ОК. З'являється розрахована таблиця значень вартості облігації залежно від терміну погашення. На рис. 5.40 представлено робочий листок Excel із проведеними в ньому розрахунками, рядок фор­мул містить функцію ПС, яка повертає поточний обсяг вкладу, тобто суму, яку складають майбутні платежі.

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

Щоб знайти підсумки для даних, які можуть бути розташовані в різних місцях одного робочого листка, на різних робочих листках одного файла і навіть у різ­них файлах, застосовують консолідацію (лат. consolida­te — об'єднання) даних. Необхідною умовою її прове­дення є наявність однакових назв стовпців, у яких роз­міщуються значення для розрахунку.

Наприклад, необхідно знайти загальну суму кожно­го виду вкладів кількох філіалів одного банку. Дані роз­ташовані на трьох робочих листках (Листі, Лист2, ЛистЗ) і зображені на рис. 5.41.

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

У вікні Консолидация послідовно здійснюють такі операції:

• відкривають перелік списку Функция і вибирають потрібне (Сумма);

• у полі Ссьілка задають координати першого діапазону комірок із даними, які консолідуються (Лист1!$А $2:$В$9);

• натискають на кнопку Добавить, після чого визначений діапазон комірок з'являється в полі Список диапазонов;

• послідовно вводять адреси діапазонів комірок ІН' ших робочих листків, натискаючи після кожної на кнопку Добавить;

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

• якщо можливі зміни початкових даних у майбутньому, необхідно активізувати параметр Создавать связи с исходными данными (за цієї умови між початковими і консолідованими даними буде встановлено динамічний зв’язок, який забезпечує автоматичне оновленнярозрахованих підсумків);

• натискають на кнопку ОК.

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

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

Для виведення і аналізу підсумкових значень табли­ці використовують зведені таблиці.

Якщо, наприклад, необхідно розрахувати максима­льні значення поля «Сума кредиту» для банку і визна­чити відсоткове відношення суми поля «Сума комісії за видачу кредиту» кожного банку до загальної суми в Цьому полі, то для створення зведеної таблиці слід вико­нати такі дії:

• встановити курсор у будь-яку комірку таблиці й активізувати команди Данные, Сводная таблица. Після Цього у першому екранному вікні слід активізувати пе­ремикач В списке или базе данных Microsoft Excel і кнопку Далее;

• у наступному вікні ввести (або перевірити) діапазон комірок (тут A2:G9), на основі значень яких буду­ють таблицю, й активізувати кнопку Далее;

у вікні, що з'явиться на екрані дисплея (див. рис. 5.44), активізувати перемикач (2), тобто существующий лист, і визначити комірку, в якій починає створюватися зве­дена таблиця (наприклад, A11);

• активізувати кнопку Макет (1) для визначення діапазонів значень.

У наступному вікні (рис. 5.45) для визначення полів і підсумків, які міститимуться у зведеній таблиці, не­обхідно:

• мишею «перетягнути» кнопку з назвою стовпця із набору (2)у частину вікна Столбец ( 1 ) (у наведеному прикладі — поле «Банк»);

• у частину Данные «перетягнути» кнопки з назвами стовпців, значення яких розраховують (це поля «Сума кредиту» та «Сума комісії за видачу кредиту» (3);

• за замовчуванням підсумок, який буде розраховуватися, — це функція Сума, для визначення іншої функції треба двічі клацнути мишею на кнопці з назвою поля в частині Данные — поля «Сума кредиту»;

• після появи наступного вікна (рис. 5.46) у переліку Операция (1)вибрати потрібну функцію (Максимум);

• за допомогою кнопки Дополнительно (2) активізу­вати перелік Дополнительные вычисления (3) з іншими додатковими функціями. Для поля «Сума комісії за ви­дачу кредиту» вибрати операцію Доля от суммы по строке й послідовно активізувати кнопки OK, OK, Готово.

Внаслідок цих процедур зведена таблиця має вигляд, зображений на рис. 5.47.

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

Поиск по сайту: