Теоретичні відомості

Засіб “Поиск решения” призначений для виконання складних обчислень, які важко виконати звичайним чином. Перед тим, як почати пошук рішень потрібно встановити відповідний засіб (якщо він не встановлений) за допомогою команди: кнопка Microsoft Office , потім Параметры Excel, Надстройки, у області «Надстройки» вибрати Имя: Поиск решения, у області Управление: Надстройки Excel, Перейти, у вікні «Надстройки» встановленням прапорця Поиск решения.

Після завантаження пакету аналізу у групі Анализ на вкладці Данные з’явиться команда Поиск решения.

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

Розглянемо можливості пошуку рішень на прикладі. Необхідно визначити при яких значеннях ціни і об'єму продажу прибуток буде максимальним за умови, що собівартість не залежить від об'єму виробництва і складає 450 грн., а між ціною і попитом існує залежність: у = –10×х +15000, де у – попит, х – ціна. Прибуток розраховується за формулою: прибуток = (ціна–собівартість)*попит.

Під час пошуку рішення необхідно врахувати такі обмеження:

· об'єм продажу за період, що розглядається, не повинен перевищувати 15000 одиниць товару;

· ціна не може бути вищою за верхню межу в 1499 грн. (якщо встановити вищу ніхто не захоче купити товар);

· ціна не повинна бути нижчою за собівартість виробу.

Процедура пошуку рішення – визначення, при яких значеннях ціни і об'єму продажу прибуток буде максимальним, така:

1. Створити таблицю з початковими даними, причому попит і прибуток розрахувати за формулами:

2. Виконати команду Данные, Анализ: Поиск решения.

3. В діалоговому вікні “Поиск решения” в полі “Установить целевую ячейку” вказати адресу цільової комірки, значення якої повинно відповідати поставленим вимогам. В нашому випадку – це комірка, що містить формулу розрахунку прибутку, тобто В8. Необхідно зазначити, що цільова комірка завжди повинна містити формулу з посиланням на комірки, в яких данні будуть змінюватися.

4. В групі опцій “Равно” вибрати опцію “Максимальному значению”, (коли потрібно знайти не максимальне значення, а якесь конкретне, активізуюють опцію “Значению”).

5. В полі “Изменяя ячейки” встановити адреси комірок, в яких дані будуть змінюватися. В нашому випадку – це комірки зі значеннями попиту та ціни, тобто в поле треба ввести адреси В6; В7.

6. Наступний етап – це введення обмежень. Для цього необхідно натиснути кнопку “Добавить”. На екрані з'явиться діалогове вікно “Добавление ограничения”.

7. В полі “Ссылка на ячейку” вказати адресу комірки, на яку повинно діяти обмеження. В нашому випадку перше обмеження – попит не може бути більшим за максимальний об'єм виробництва, тобто: в полі “Ссылка на ячейку” вводимо адресу В7, в наступному полі вибираємо <=, а в полі “Ограничения” – адресу комірки обмеження з верхньою границею В3. Натиснути кнопку “Добавить”.

8. Залишилося ще два обмеження, що стосуються ціни. Для введення додаткових обмежень (обмеження - ціна не більше максимальної ціни 1499 грн.,) необхідно повторно виконати команду “Добавить”, в полі “Ссылка на ячейку” ввести адресу комірки В6, в наступному полі оператор <= (не більше), в полі “Ограничения” – адресу комірки з верхньою межею обмеження, тобто адресу В4. Натиснути кнопку “ Добавить”.

9. В діалоговому вікні “Добавление ограничений” в полі “Ссылка на ячейку” задати адресу комірки В6, в наступному полі вибрати >=, в полі “Ограничения” вказати адресу комірки В5. Натиснути “ОК”. Таким чином ми задали останнє обмеження – ціна не повинна бути нижчою від собівартості товару. Задані обмеження з'являться в списку “Ограничения” діалогового вікна “Поиск решения”. Якщо в заданих обмеженнях є помилка для виправлення необхідно натиснути “Изменить”.

10. У випадку, якщо необхідно змінити параметри пошуку рішення, потрібно натиснути кнопку “Параметры”. Після чого з'явиться вікно діалогу “Параметры поиска решения”, в якому можна змінювати параметри пошуку, що були задані по замовчуванню. Натиснути “ОК”.

11. Після того, як всі параметри і обмеження задані, потрібно запустити пошук. Для цього потрібно натиснути кнопку “Выполнить” діалогового вікна “Поиск решения”.

12. Після того, як пошук рішення буде завершено, в таблицю буде внесено нові значення, а також з'явиться діалогове вікно “Результаты поиска решения” (рис.3).

13. Для збереження отриманого результату потрібно активізувати опції “Сохранить найденное решение” – в цьому випадку таблиця буде оновлена у відповідності до найденого рішення.

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

· звіт “Результаты” – містить інформацію про початкові і поточні значення цільової комірки та змінних комірок, а також про відповідність значень заданим обмеженням;

· звіт “Устойчивость” – відображає знайдений результат, а також нижній та верхні граничні значення для змінних комірок;

· звіт “Пределы” – показує залежність рішень від зміни формули або обмежень.

15. Якщо в майбутньому планується використовувати створену модель пошуку, знайдене рішення можна зберегти як сценарій. Для цього необхідно натиснути кнопку “Сохранить сценарий”. Якщо дана процедура пошуку використовуватися в подальшому не буде – натиснути кнопку “ОК”.