Пошук рішення.

Надбудова Microsoft Excel Solver (Пошук рішення) не встановлюється автоматично при звичайній установці:

ü В меню Tools (Сервіс) виберіть команду Add-Ins (Надбудови). Якщо діалогове вікно Add-Ins (Надбудови) не містить команди Solver (Пошук рішення), натискуйте кнопку Browse (Огляд) і вкажіть диск і теку, в якій міститься файл надбудови Solver.xla (як правило, це директорій Library\Solver) або запустите програму установки Microsoft Office, якщо знайти файл не вдається.

ü В діалоговому вікні Add-Ins (Надбудови) встановите прапорець Solver (Пошук рішення).

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

В діалоговому вікні Solver (Пошук рішення) так само, як і в діалоговому вікні Goal Seek (Підбір параметра), необхідно вказати цільову комірку, її значення і комірки, які слід змінювати для досягнення мети. Для вирішення задач оптимізації цільову комірку слід вказати рівній максимальному або мінімальному значенню.

Якщо Ви клацните на кнопці Guess (Припустити), Excel сам спробує знайти всі комірки, що впливають на формулу.

Ви можете додати граничні умови, кликнувши на клавіші Add (Додати).

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

Повернемося до попереднього прикладу: для того, щоб отримати друге (непозитивне) рішення, достатньо додати граничну умову A3<=-0.01. Так само як і при підборі параметра, на екрані з'явиться вікно, в якому буде відображений звіт про результати пошуку необхідного рішення. Саме рішення буде показано в призначених для нього комірках (в комірці A3 відобразиться значення -0.50).

Надбудова Microsoft Excel Solver (Пошук рішення) дозволяє, також, вирішувати системи рівнянь або нерівностей. Розглянемо простий приклад: спробуємо вирішити систему рівнянь

x + у = 2

x - у = 0

Введемо в комірки, призначені для вирішення (A1:A2) довільні величини, що лежать в області визначення (початкові значення).

ü В комірки B1 і B2 внесемо формули, по яких повинні обчислюватися праві частини рівнянь (= A1 + A2 і = A1 - A2).

ü Запустимо Solver (Пошук рішення) з меню Tools (Сервіс).

ü Виберемо одну з комірок, що містять формули, як цільову комірку (наприклад, B1), зробимо її рівною 2.

ü Кликнемо на кнопці Guess (Припустити) для того, щоб Excel визначив впливаючі комірки (A1:A2).

ü Додамо обмеження B2 = 0.

ü Кликнемо на клавіші Solve (Виконати).

Результати пошуку відобразяться в призначених для розв’язку комірках (A1:A2), звіт про результати з'явиться на екрані.

!!!! Доступ до даних засобів у версії 2007 відбувається через кнопки закладинки Данные. Якщо інструмент Пошук ріщення не відображається, виконайте команди Системна кнопка Ms Office - Параметры Excel – Надстройки – відмітити прапорцем надбудову Поиск решений.

Задача 1. Планування випуску продукції

Для виготовлення виробів х, у, z використовують три види сировини: І, II, III. У таблиці задано норми витрат сировини на один виріб кожного виду, ціна одного виробу, а також кількісті сировини кожного виду, яку можна використати. Скільки виробів кожного виду потрібно виготовити, щоб прибуток був максимальний (п — номер варіанта)?

Х у z Загальна к-сть сировини

I 18 15 12 360 -п

II 6 4 8 192

III 5 3 3 180 + п Ціна 9 10 16

Планування випуску продукції. Задача 1 є задачею лінійного програмування. Вона розв'язується за допомогою інструментаПошукРОЗВ'ЯЗКУ(Solver).

Математична модель задачі. Позначимо через х, у, z шукані кількості виробів трьох видів. Потрібно визначити х, у, z, для яких досягається максимум функції прибутку f = 9х +10у + 16z за таких обмежень:

18х + 15і/ + 12z<= 360 - п 6х + 4у + 8z <= 192 5х +3у + 3z <=180 + п х, у z >== 0; х_уу,г — цілі. Розв'язування. Потрібно виконати такий алгоритм:

1) клітинкам А1, В1, С1 присвоїти імена х, у, г командами Вставити => Ім'я => Присвоїти=> Ввести х => ОК і т.д.;

2) у клітинку D1 ввести формулу =9*x + 10*y+16*z;

3) запустити програму Пошук розв'язку з меню Сервіс;

4) задати адресу цільової клітинки D1 і зазначити дію до-сягнення максимуму функції (рис. 46);

5) задати клітинки, де має міститися розв'язок: х; у; г;

6) за допомогою кнопки Додати додати обмеження (рис. 47) у вигляді шести нерівностей (значення п підставити конкретне):

х <=(360 - п - 15*у - 12*z)/18

у <= (192 - 6*х - 8*2)/4

z <= (180 + п - 5*х - 3*у)/3

х >=0; у >= 0; z>= 0

х — ціле; у — ціле; z— ціле;

Рис. 46. Вікно програми Пошук розв'язку для задачі 1.

7)натиснути на кнопку Параметри і задати, що модель лінійна;

8) отримати розв'язок, натиснувши на кнопку Виконати.

Для п = 0 відповідь (у клітинках А1, В1, СІ, D1) така: х = 0 ; у= 8, z = 20; f = 400.

Рис. 47. Вікно для введення обмежень

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

На прикладі розв'язування задачі 10 розглянемо вплив змія ни одного параметра (кількості періодів позики) на дві величини щомісячну і сумарну виплати за кредит.

Основною формулою розв'язування задачі є =ППЛАТ(ІІС; КП; сума кредиту), яку розглядали в попередній роботі. Виконайте такий алгоритм (рис. 48.).

У діапазон А1:АЗ введіть вхідні дані: ставку (6%), кількість періодів (4) і суму кредиту, наприклад 3000.

У діапазон А5:А8 введіть можливі терміни позики: 4, 5, 6, 7.

3. У клітинку В5 введіть формулу =ППЛАТ(А1; А2; A3). У клітинку С5 введіть формулу =В5*А2. Ці формули мають бути першими у своїх стовпцях.

4. Виокремте діапазон А5:С8 і застосуйте команду Дані - > Таблиця підстановки (Table...). Параметром у цій задачі є кількість періодів з клітинки А2. Тому в отриманому діалоговому вікні друге поле Підставляти значення по рядках введіть А2. Натисніть на кнопку ОК. Отримаєте таблицю, придатну для прийняття рішень. Який термін позики вам найбільше підходить?

Для аналізу щомісячних виплат, залежних від двох параметрів (можливих сум і термінів позики), таблицю будують так: у клітинку D1 вводять формулу =ППЛАТ(А1; А2; A3). Клітинки праворуч Е1:Н1 заповнюють деякими можливими сумами позики: 2000, 2500, 3000, 3500, а клітинки знизу (D2:D5) - можливими термінами 4, 5, 6, 7 місяців. Вибирають прямокутний діапазон D1-.H5 і виконують команду - > Дані Таблиця підстановки. В отриманому діалоговому вікні у перше поле вводять A3, а в друге — А2. Отримаємо таблицю, аналіз якої дає змогу вибрати суму і термін позики, враховуючи щомісячну платоспроможність підприємця.