Новини освіти і науки:

G+

Тлумачний словник
Авто
Автоматизація
Архітектура
Астрономія
Аудит
Біологія
Будівництво
Бухгалтерія
Винахідництво
Виробництво
Військова справа
Генетика
Географія
Геологія
Господарство
Держава
Дім
Екологія
Економетрика
Економіка
Електроніка
Журналістика та ЗМІ
Зв'язок
Іноземні мови
Інформатика
Історія
Комп'ютери
Креслення
Кулінарія
Культура
Лексикологія
Література
Логіка
Маркетинг
Математика
Машинобудування
Медицина
Менеджмент
Метали і Зварювання
Механіка
Мистецтво
Музика
Населення
Освіта
Охорона безпеки життя
Охорона Праці
Педагогіка
Політика
Право
Програмування
Промисловість
Психологія
Радіо
Регилия
Соціологія
Спорт
Стандартизація
Технології
Торгівля
Туризм
Фізика
Фізіологія
Філософія
Фінанси
Хімія
Юриспунденкция

Аналіз даних засобами 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 із проведеними в ньому розрахунками, рядок формул містить функцію ПС, яка повертає поточний обсяг вкладу, тобто суму, яку складають майбутні платежі.

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

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

Наприклад, необхідно знайти загальну суму кожного виду вкладів кількох філіалів одного банку. Дані розташовані на трьох робочих листках (Листі, Лист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.

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

Читайте також:

<== попередня сторінка	\|	наступна сторінка ==>
Фінансовий аналіз	\|	Підсумки дослідження академіком С. Рудницьким чисельності українців у світі в 1914 р.

Не знайшли потрібну інформацію? Скористайтесь пошуком google:

Генерація сторінки за: 0.005 сек.