Теоретичні відомості. Тема. Використання спеціальних інструментів аналізу та оптимізації

Практична робота 11

Тема. Використання спеціальних інструментів аналізу та оптимізації.

Мета. Дати поняття про використання в програмі Microsoft Ехсеl інструментів аналізу та оптимізації. Навчити використовувати можливості аналізу “що-як” для обчислень.

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

Учні повинні уміти: використовувати можливості аналізу “що-як” для обчислень.

Програмне забезпечення: табличний процесор Microsoft Ехсеl.

Однією з найважливіших переваг програми Excel є можливість виконувати аналіз та оптимізацію даних. Наприклад, аналіз "що-як" дозволяє простежити залежність кінцевого результату від зміни вихідних параметрів. Можна також виконати протилежну операцію – знайти вихідні дані, що після підстановки у формули дадуть певні результати. Такий аналіз даних виконується методами підбору параметра та пошуку розв'язання.

Аналіз "що-як"

Аналіз "що-як" – це процес зміни значень комірок і дослідження впливу цих змін на результат обчислення формул на робочому аркуші.

Існує три способи виконання аналізу "що-як":

1. вручну;

2. за допомогою таблиць підстановки;

3. за допомогою диспетчера сценаріїв.

Метод виконання аналізу "що-як" вручну

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

Недоліком даного методу є те, що в конкретний момент часу можна спостерігати результати обробки лише одного набору вихідних значень.

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

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

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

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

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

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

Приклад

Сім’я вирішила купити квартиру, для чого їй необхідно взяти в банку позику на суму $45 000 на 10 років (тобто на 120 місяців). Обчислити розмір щомісячних виплат і загальну суму виплат по цій позиці, для декількох відсоткових ставок (наприклад, 5%, 5,5%, 6%, 6,5%, 7%, 7,5%, 8% і 8,5%).

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

Для розрахунку щомісячних виплат можна скористатися функцією ППЛАТ (повертає суму чергової виплати за позикою на основі постійних періодичних виплат і постійної відсоткової ставки; належить до категорії Фінансові). В комірку В5 введена наступна формула обчислення щомісячної виплати: =ППЛАТ(В4/12; ВЗ; В2)

В комірку В6 введена формула розрахунку загальної суми виплат: =В5*ВЗ.

Тепер можна починати створювати таблицю підстановки. Для цього потрібно виконати такі дії:

В діапазон А10:А17 ввести значення відсоткових ставок, для яких потрібно обчислити розмір щомісячних виплат та загальної суми виплат.

В комірки В8 і С8 ввести написи з комірок А5 і А6 (Щомісячна виплата і Загальна сума). А в комірки В9 і С9 – посилання на комірки з формулами, за якими буде обчислено результат. В нашому випадку потрібно виконати посилання на комірки В5 і В6.

Виокремити діапазон таблиці підстановки. Це мінімальний діапазон комірок, що містить формули та всі значення діапазону вихідних даних (у даному прикладі – діапазон А9: С17).

Вибрати команду Данные/Таблица подстановки, після чого з'явиться діалогове вікно Таблица подстановки.

Вказати комірку робочого аркуша, куди слід підставляти вихідні дані. У нашому випадку вихідні дані – це значення відсоткових ставок, тому потрібно вказати комірку В4. Оскільки вихідні дані містяться в стовпці, ввести адресу комірки в поле Подставлять значения по строкам в.

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

Зверніть увагу, що ліва верхня комірка цієї таблиці не використовується.

Щоб дізнатися, якими будуть щомісячні виплати для відсоткових ставок 5%, 5,5%, 6%, 6,5%, 7%, 7,5%, 8% і 8,5% при строках 5, 10, 15 та 20 років (тобто 60, 120, 180 та 240 місяців), доведеться створити таблицю підстановки із двома входами.

Щоб створити таблицю підстановки із двома входами, слід виконати наступні дії.

1. Ввести у діапазон комірок А10:А17 значення відсоткових ставок, для яких слід визначити розмір щомісячних виплат.

2. В діапазон В9: Е9 ввести строки позики: 60, 120, 180 і 240, а в комірку В8 – напис Строк погашення позики, місяці.

3. В комірку, що розміщується на перетині рядка і стовпця з вихідними значеннями, тобто в комірку A9, ввести посилання на формулу розрахунку щомісячних виплат (ця формула в нашій таблиці знаходиться в клітинці В5).

4. Виокремити діапазон таблиці підстановки, це буде діапазон А9:Е17 (мінімальний діапазон комірок, що містить два діапазони й вихідних значень та формулу).

5. Вибрати команду Данные/Таблица подстановки.

6. В отриманому діалоговому вікні Таблица задати відповідні комірки.

7. У нашому прикладі в поле Подставлять значения по столбцам в слід ввести посилання на комірку В3, а в поле Подставлять значения по строкам в - посилання на комірку В4.

8. Клацнути на кнопці OK, щоб створити таблицю підстановки.

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

Дата добавления: 2015-05-24