Microsoft EXCEL як інструмент вирішення задач вибору оптимальних рішень

Реалізація економіко-математичних моделей, які використовують для вироблення оптимальних управлінських рішень, вимагає використання сучасних програмних продуктів. Одним з найрозповсюдженіших засобів розв’язання задач пошуку оптимальних рішень слугує система Microsoft EXCEL.

Більшість практичних завдань, які запропоновано для освоєння дисципліни і наведено у методичних вказівках, зводяться до моделей лінійного і цілочислового програмування.

Процедуру пошуку оптимальних рішень за допомогою табличного процесора EXCEL розглянемо на прикладі задачі лінійного програмування:

;

Для знаходження оптимального розв’язку скористаємося стандартними засобами системи EXCEL.

Першим етапом розв’язування задачі лінійного програмування за допомогою системи EXCEL є створення на екрані форми для задання умов задачі. Типовим прикладом форми наведеної вище моделі слугує рис.1.1.

Коефіцієнти ЦФ, границі змінних, поле результатів
імя	х1	х2	х3	х4
значення
ниж. гран.					ЦФ	напр.
верх.гран
коеф. ЦФ

Обмеження
					ліва част.	знак	права част.
1-е обмеження
2-е обмеження
3-е обмеження

Рис.1.1. Приклад екранної форми для задання умов задачі

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

Модифікацію, вилучення і додавання обмежень задачі можна здійснити у діалоговому вікні Добавление ограничений (рис.1.4), яке розгортається після натискання кнопки Добавить у діалоговому вікні Поиск решения.

Розв’язування задачі вимагає використання діалогового вікна Параметры поиска решений (кнопка Параметры у вікні Поиск решения), в якому вказують умови пошуку оптимального рішення (рис.1.5).

Встановлення прапорця Линейная модель і натискання кнопки ОК повертає користувача у вікно Поиск решения. Натискання кнопок Выполнить і ОК у вікні Поиск решения після виконання описаних вище кроків забезпечить розв’язування задачі симплексним методом (рис.1.6). Якщо умови задачі сумісні, то на екрані з’явиться діалогове вікно Результаты поискарешений (рис.1.7).

Після створення форми необхідно задати обмеження і цільову функцію задачі (рис.1.2).

Рис.1.2. Формування умов вихідної задачі

Рис.1.3. Діалогове вікно Поиск решения

Рис.1.4. Діалогове вікно Добавление ограничения

(Приклад завдання другого обмеження вихідної задачі)

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

Коефіцієнти ЦФ, границі змінних, поле результатів

імя х1 х2 х3 х4

Значення 1,8519 9,4444 94,25925925 мах

ниж. гран. ЦФ напр.

верх.гран

коеф. ЦФ

Обмеження

ліва част. знак права част.

1-е обмеження <=

2-е обмеження 37,59259259 <=

3-е обмеження 99,99999999 <=

Рис.1.6. Оптимальний план і значення цільової функції задачі

Рис.1.7. Діалогове вікно Результаты поиска решений

За допомогою цього діалогового вікна можна отримати звіти трьох типів:

· звіт про результати розв’язку задачі;

· звіт про аналіз моделі на стійкість;

· звіт про межі зміни невідомих задачі.

Звіт про результати розв’язку задачі подано у табл.1.2. Він містить таку інформацію:

· адреси кліток, які містять значення параметрів моделі;

· початкове і оптимальне значення цільової функції;

· значення початкового базового і оптимального плану.

Графа Формула містить залежності, що описують обмеження задачі. Величини використання і залишку ресурсів наведені відповідно у графах Значение і Разница. Якщо ресурс використовується повністю, то у графі Состояние вказують “связанное”, у противному випадку – “не связан”.

Результати аналізу моделі на стійкість (табл.1.3) містять таку інформацію:

· результат розв’язку задачі;

· граничні значення зміни коефіцієнтів цільової функції, при яких значення оптимального плану не зміняться;

· додаткові двоїсті оцінки (графа Редуц. стоимость), які вказують, на скільки змінюється значення цільової функції при “примусовому” збільшенні значення змінної на одиницю;

· значення двоїстих оцінок для оптимального плану;

· величини використання ресурсів;

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

Звіт про межі зміни невідомих задачі показаний у табл.1.4 і, крім оптимального плану і значення цільової функції, містить межі зміни невідомих, що входять в оптимальний план, за яких зберігається структура оптимального розв’язку.

Задачі оптимізації, для яких накладається умова цілочисловості змінних, мають назву задач цілочислового програмування. В системі Microsoft EXCEL задачі цілочислового програмування розв’язуються аналогічно до задач лінійного програмування. Основна відмінність полягає у створенні екранної форми для задавання вимог цілочисловості. Якщо накласти на всі змінні розглянутої вище задачі умову цілочисловості, то екранна форма набуде вигляду, показаного на рис.1.8.

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

Результати розв’язування цілочислової задачі показані на рис.1.9.

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

Таблиця 1.2