Розв’язування задач оптимізації.

Електронна таблиця МS Excelдозволяє розв’язувати задачі оптимізації. Для цього треба скористатися спеціальним засобомПоиск решения. Цей засіб дозволяє вирішувати задачі оптимізації при наявності ряду обмежень. При цьому по заданому результату може знаходитися не одне, а ряд значень вихідних параметрів, що автоматично заносяться в таблицю.

Розв'язання задачі за допомогою програми Поиск решения звичайно виконується у такій послідовності:

- постановка задачі та створення математичної моделі;

- запис задачі у табличній формі, придатній для введення даних;

- уведення даних і розв'язання задачі;

- економіко-математичний аналіз отриманого рішення.

Задача, що розв'язується програмою Поиск решения, у найбільш загальному вигляді формулюється таким чином:

Знайти вектор X=(x₁,x₂,…x_n), який мінімізує (або) максимізує функцію J(X) при обмеженнях

xkmin xkxkmax,k=1,…,n	(1)
Fimin gi(X)  Fimax , I=1,…,m	(2)
hi(X)=Fj , j=1,…,p	(3)

Функція J(X) називається цільовою функцією, умови (1) - прямими, а умови (2) і (3) - функціональними обмеженнями.

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

Методику практичного застосування засобу Поиск решения розглянемо на прикладі розв’язування такої задачі (таблиця 4.3): підприємство виробляє продукцію 3-х видів: електрочайники, кавоварки, праски. При виробництві кожного виду продукції витрачаються ресурси: електроенергія та метал, сумарні потреби яких обмежено величинами 600 грн. та 400грн. відповідно. Прибуток від продажу продукції та витрати ресурсів приведені у таблиці. Скільки виробів кожного виду продукції треба виготовити, щоб прибуток був максимальний?

Таблиця 4.2.3. Умова задачі

Найменування продукції	Витрати на одиницю, грн.		Прибуток на одиницю, грн.
	Енергія	Метал
Електрочайник	2	4	30
Кавоварка	3	2	20
Праска	5	7	55

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

Цільова функція (прибуток): P = 30*x₁+20*x₂+55*x₃,

де x₁, x₂, x₃ – відповідно кількості випущених електрочайників, кавоварок, прасок.

Обмеження:

1) затрати всіх видів ресурсів (електроенергії і металу) на план x₁, x₂, x₃ не повинні перевищувати наявних лімітів.

2*x₁+3*x₂+5*x₃ ≤ 600,

4*x₁+2*x₂,+7*x₃ ≤ 400;

2) властивості плану

x₁, x₂, x₃ ≥ 1; x₁, x₂, x₃ – цілі.

Математично задача, що розглядається, формулюється таким чином: знайти значення змінних x₁, x₂, x₃ , які задовольняють обмеженням і при яких цільова функція досягає максимального значення.

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

Рис. 3.21 - Основні області моделі

Для значень змінних x₁, x₂, x₃ виділено клітинки B11:B13. Клітинка B3 є цільовою клітинкою. B цю клітинку заносимо формулу для обчислення прибутку (значення цільової функції)

=CУMMПPOИ3B ($B$11:$B$13;$E$11:$E$13).

B області обмежень вводимо до клітинок B13, B19 формули для обчислення значень витрат на електроенергію та метал, а також до клітинок C13, C19 - задані у задачі обмеження цих значень.

B13: =CУMMПPOИ3B ($B$11:$B$13; $C$11:$C$13),

B19: =CУMMПPOИ3B ($B$11:$B$13; $D$11:$D$13).

Програма Поиск решения змінює значення к клітинок B11:B13 області змінних доти, доки у цільовій комірці B3 не з'явиться результат, який необхідно отримати.

Для запуску програми Поиск решения виконується команда Сервис - Поиск решения, яка викликає однойменне діалогове вікно (рис. 4.2.22).

Рис. 4.2.22 - Діалогове вікно програми Поиск решения

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

У полі Изменяя ячейки необхідно вказати діапазон, що містить клітинки, значення яких програма повинна змінити для отримання оптимального значення (діапазон B11:В13 області змінних). При натисканні кнопки Предположить програма виділяє клітинки, на які прямо або опосередковано посилається цільова клітинка.

Для того, щоб задати обмеження, треба натиснути кнопку Добавить. З'являється діалогове вікно (рис. 4.2.23).

У цьому вікні у полі Ссылка на ячейку вказується адреса клітинки або діапазону клітинок, вміст яких повинен відповідати одному із заданих типів обмежень.

Рис 4.2.23 - Вікно додавання обмеження

Поле Ограничение цього вікна призначене для введення значення обмеження у вигляді константи або у вигляді адреси клітинки, яка містить це значення. У нашому випадку до обмежень додаються значення клітинок В11:B13, які повинні бути цілими та перевищувати одиницю, а також в області обмежень значення витрат на електроенергію та метал не повинні перевищувати заданих лімітів. По закінченні введення всіх параметрів треба натиснути кнопку OK.

Запуск програми на виконання обчислень здійснюється натисканням кнопки Выполнить у діалоговому вікні програми Поиск решения (рис. 4.2.22). Якщо оптимальне рішення буде знайдено, отримані значення будуть вставлені в таблицю (рис. 4.25) і на екрані з'явиться вікно з інформацією про закінчення цього процесу (рис.4.2.24).

Рис. 4.24 - Визначення результатів пошуку рішення

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

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

Рис. 4.2.25 - Результат виконання програми Поиск решения

Аналіз отриманого рішення. Із аналізу таблиці з результатами розв’язування задачі (рис. 4.41) можна зробити такі висновки: для того, щоб отримати максимальний прибуток у сумі 4740 грн. треба виготовити електрочайників 123 штуки, кавоварок – 47 штук, прасок – 2 штуки. При такому плані ліміт металу буде вичерпано повністю, а по електроенергії залишиться запас 3 грн.