Аналіз “а що як…” з використанням макросів

 

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

 

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

 

Створення таблиць підстановки виконується через команди “Дані ð Таблиця підстановки” (Data ð Table). Таблиця містить варіанти значень за сценарієм для однієї або двох комірок вихідних параметрів і відповідні їм результати розрахунків одного параметра, що аналізується.

Для створення таблиці підстановки з одним вихідним параметром треба спочатку визначитись з місцем розташування таблиці — діапазоном комірок таблиці. Ліва верхня комірка діапазону у цьому випадку не використовується. Вихідні дані (варіанти значень вихідного параметра) повинні розташовуватись в першому стовпчику (або в першому рядку) визначеного діапазону, тоді в першому рядку (або, відповідно, в першому стовпчику) треба розташувати формули або посилання на комірку з формулами розрахунку параметрів, які аналізуються. Макет таблиці підстановки для одного вихідного параметра, варіанти значень якого розташовані у стовпчику, має вигляд:

 

	Комірки з формулами або посиланнями на них
Комірки
значень
вихідного
параметра

 

Далі треба виділити визначений діапазон таблиці, залучити команду “Дані ð Таблиця підстановки” і в діалоговому вікні, що з’явиться, у відповідному полі визначити комірку робочого листка, в яку за формулами повинні підставлятись значення вихідного даного. Для наведеного макету таблиці це буде поле “Підставляти значення по рядках в”. Потім слід клацнути на кнопці “ОК” і Excel заповнить таблицю відповідними результатами.

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

 

Формула	Комірки значень другого вихідного параметра
Комірки
значень
першого
вихідного
параметра

 

Далі треба виділити визначений діапазон, залучити команду “Дані ð Таблиця підстановки” і в діалоговому вікні, що з’явиться, у відповідних полях визначити одну і другу комірки робочого листка, в які повинні підставлятись вихідні дані. Потім слід клацнути на кнопці “ОК” і Excel заповнить таблицю відповідними результатами.

Недоліки:

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

® у випадку варіації двох параметрів аналізувати можна тільки результати розрахунків по одній формулі.

 

Диспетчер сценаріїв

 

За допомогою цього засобу можна створити кілька наборів вихідних даних для будь-якої кількості параметрів і привласнити ім’я кожному набору. В термінології засобу “Диспетчер сценаріїв” (Scenario manager)комірки з наборами вихідних даних називаються змінювані комірки (changing cells). Потім за ім’ям можна вибрати певний набір вихідних даних і Excel виведе результати обробки цих даних на робочому листку. Крім того, можна створити підсумковий звіт у вигляді структури або зведеної таблиці з результатами підстановок різних комбінацій вихідних параметрів.

Доступ до засобу “Диспетчер сценаріїв” можна отримати через команду “Сервіс ð Сценарії” (Tools ð Scenarios). Після залучення цієї команди з’явиться діалогове вікно “Диспетчер сценаріїв” (Scenario manager) з переліком існуючих для даного робочого листка назв сценаріїв або з повідомленням про їх відсутність (рис. 7).

 

 

Рис. 7. Діалогове вікно “Диспетчер сценаріїв”.

 

Щоб додати сценарій, треба клацнути на кнопці “Додати” в діалоговому вікні “Диспетчер сценаріїв”. В результаті на екрані з’явиться діалогове вікно “Додавання сценарію” (Add Scenario), яке має кілька полів:

® “Назва сценарію” (Scenario Name). В це поле вводиться надумана назва сценарію.

® “Змінювані комірки” (C hanging Cells). В цьому полі вказуються комірки, в яких знаходяться вихідні дані за сценарієм. Як вихідні дані можна вказувати абсолютну адресу комірки або її ім’я, можна обирати кілька комірок (необов’язково суміжних). В кожному іменованому сценарії можна використовувати одні й ті самі набори змінюваних комірок або різні змінювані комірки. Кількість змінюваних комірок для одного сценарію обмежено кількістю 32.

® “Примітка” (Comment). По умовчанню Excel заносить інформацію про тих, хто створив сценарій, та дату створення.

® “Захист” (Protection). Це поле містить дві опції, які дозволяють захистити сценарій від змін та сховати його. Ці опції можна активізувати, якщо робочий листок захищений і в діалоговому вікні “Захистити листок” (Protect Sheet) активізована опція “Сценарії” (Scenario).

Після заповнення вікна “Додавання сценарію” і клацання на кнопці “ОК” з’являється діалогове вікно “Значення комірок сценарію” (Scenario Values) з переліком всіх змінюваних комірок, які були визначені в попередньому вікні. Далі треба ввести значення для кожної комірки сценарію. Якщо клацнути на кнопці “ОК”, то відбудеться повернення до попереднього діалогового вікна “Диспетчер сценаріїв”, в якому вже буде знаходитись ім’я створеного сценарію. Якщо необхідно створити ще один чи кілька сценаріїв, треба знову клацнути на кнопці “Додати” і повторити описані дії.

Якщо в діалоговому вікні “Диспетчер сценаріїв” вибрати один з перерахованих в переліку сценаріїв і клацнути на кнопці “Вивести” (Show), то Excel підставить відповідні значення в змінювані комірки і в робочому листку відобразяться результати розрахунків.

Призначення інших кнопок у вікні “Диспетчер сценаріїв”:

® “Змінити” (Edit) — дозволяє редагувати обраний сценарій. Для появи на екрані вікна “Значення комірок сценарію” — клацнути на кнопці “ОК”. Після внесення необхідних змін — знову клацнути на кнопці “ОК”.

® “Об’єднати” (Merge) — дозволяє додати в перелік доступних сценаріїв інші сценарії, що розроблені і знаходяться в інших робочих листках та книгах. Потрібні при додаванні робочі книги повинні бути відкриті.

® “Звіт” (Summary) — дозволяє створювати підсумкові звіти. У вікні “Звіт за сценарієм” (Scenario Summary), яке з’являється після клацання на кнопці “Звіт”, можна вибрати тип звіту:

* “Структура” (Scenario Summary) — виведе підсумковий звіт у формі структурованого списку.

* “Зведена таблиця” (Scenario Pivot Table) —виведе підсумковий звіт у формі зведеної таблиці.

В діалоговому вікні “Звіт по сценарію” треба вказати комірки результату, в яких містяться значення, що аналізуються.

 

Аналіз даних з використанням засобів підбору параметрів та пошуку розв’язку

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

* “Підбір параметра” (Goal Seeking). Цей інструмент дозволяє визначити значення в одній вихідній комірці, яке забезпечує отримання бажаного результату в залежній комірці (комірці результату).

* “Пошук розв’язку” (Solver). Цей інструмент дозволяє визначити значення в кількох вихідних комірках, які забезпечують отримання бажаного результату. Крім того, накладаючи певні обмеження на поставлену задачу, можна отримати її розв’язок майже із стопроцентною вірогідністю.