Вирішення задачі про оптимальне виробництво фарб

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

1. Виберіть команду Сервис -> Поиск решения. Вікно Поиск решения має такі елементи:

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

В прикладі із завданням про фарби в поле Установить целевую ячейку уводимо С4.

Група Равной - тип взаємозв'язку між рішенням і цільовою коміркою встановлюється шляхом вибору перемикача в цій групі. Для відшукання максимального значення цільової функції вибирається перемикач максимальному значению, мінімального – перемикач минимальному значению; якщо відшукуються значення змінних, для яких значення функції із цільової комірки дорівнює встановленому в поле групи Равной значенню, то вибирається перемикач значению.

У завданні про фарби виберіть перемикач максимальному значению, тому що находимо план виробництва фарб із максимальними прибутками.

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

У нашому випадку введемо в поле Изменяя ячейки діапазон А3:В3.

Список Ограничения – допускаються обмеження у вигляді рівностей, нерівностей, вимоги того, що невідомі можуть приймати тільки цілі значення, або тільки значення 0 чи 1; обмеження додаються по одному за раз і відображаються у вікні Добавление ограничения, щовикликається натисканням кнопки Добавить.

У поле Ссылка на ячейкувведіть ліву частину обмежень – А3:В3, у поле Ограничения– праву частину, у нашому випадку – 0. Список дозволяє задати тип співвідношення між лівою й правою частинами обмеження. У нашому випадку виберіть співвідношення >=. У такий спосіб ми задаємо вимогу незаперечності змінних.

Натисніть кнопку Добавитьі за допомогою діалгового вікна Добавление ограничениявведіть другу групу обмежень, що накладають на змінні А7:А10 <= В7:В10. Натисніть кнопку ОКдля завершення введення обмежень. На екрані знову відобразиться вікно Поиск решения, але тепер вже заповнене. Натисніть кнопку Параметры – на екрані відобразиться діалогове вікно Параметры поиска решения (мал. 7.3.).

Мал.7.2. Заповнення вікна Поиск решения

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

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

Поле Максимальное время –служить для обмеження часу, що відпускається на пошук рішення задачі.

Поле Предельное число итераций–служить для обмеження числа проміжних обчислень.

Поля Относительная погрешностьіДопустимое отклонение– використовуються для завдання точності, з якої шукається рішення. Рекомендується після знаходження рішення з величинами даних параметрів заданих за замовчуванням, повторити обчислення з більшою точністю й меншим припустимим відхиленням та порівняти з початковим методом. Використання подібної перевірки особливо рекомендується для задач із цілочисленими обмеженнями на змінні.

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

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

Прапорець Неотрицательные значения – дозволяє встановити нульову нижню границю для тих комірок, що впливають, для яких вона не була зазначена у поліОграничение діалогового вікна Добавить ограничение.

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

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

У нашому випадку встановіть прапорець Линейная модель, а інші значення варто залишити без змін. Натисніть кнопку ОК. На екрані знову відобразиться вікно Поиск решения.

3. Натисніть кнопку Выполнить. На екрані відобразиться вікно Результаты поиска решения, у якому буде зазначено, що рішення знайдене (мал. 7.4.).

4. Після натискання кнопки ОК, результати будуть внесені в робочий лист.

Зі знайденого рішення видно, що оптимальним є виробництво в добу 3,333 т фарби Е та 1,333 т фарби I. Цей обсяг виробництва принесе максимально можливі 12666,67 грн. добового прибутку.

Мал. 7.4. Результати пошуку рішення задачі