Приклад виконання роботи

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

Визначити в Microsoft Excel план випуску продукції з умови максимізації прибутку від її реалізації.

Розв'язок. Створимо математичну модель даної задачі. Для даної задачі:

– кількість видів сировини (ресурсів) m = 3;

– кількість видів продукції n = 4;

Змінні:

х₁ – кількість виробів виду А, яке необхідно виробити;

х₂ – кількість виробів виду Б, яке необхідно виробити;

х₃ – кількість виробів виду В, яке необхідно виробити;

х₄ – кількість виробів виду Г, яке необхідно виробити.

Математична модель даної задачі:

Цільова функція:

L(Х)=5x₁+3x₂+7x₃+8x₄ → max.

Обмеження моделі:

2x₁+x₂+3x₃+2x₄ ≤ 200;

x₁+2x₂+4x₃+8x₄ ≤ 160;

2x₁+4x₂+x₃+x₄ ≤ 170;

x₁, x₂, x₃, x₄ ≥ 0;

x₁, x₂, x₃, x₄ - цілі.

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

Рис. 1.1 Вихідний вигляд форми для введення даних і залежностей

Далі потрібно ввести у відповідні комірки формули для обчислення результатів. У комірки стовпця "Использовано сырья, кг" уводимо формули, відповідні лівим частинам обмежень для видів сировини I, II, III відповідно. Ці значення надбудова обчислить сама.

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

1. У комірка із значенням цільової функції вводимо формулу. Можна скористатися стандартною функцією СУММПРОИЗВ, для якої як аргументи вказати комірки рядка "Цена изделия, грн" і комірки із значеннями змінних. Таким чином, перед зверненням до надбудови, коли введені всі формули, комірки із значеннями змінних порожні, а у комірці зі значенням цільової функції і у комірках стовпця "Использовано сырья, кг" будуть нулі.

2. Для розв'язання завдань оптимізації досить зручним і простим у використанні є засіб Microsoft Ехсеl Поиск решения. Для виклику цього засобу в меню Сервис виберіть команду Поиск решения.

Якщо команда Поиск решения відсутня в меню Сервис, то треба встановити цю надбудову. У меню Сервис виберіть команду Надстройки. У діалоговому вікні Надстройки встановить прапорець Поиск решения.

Для розв'язання завдання необхідно в діалоговому вікні Поиск решения (рис. 1.2) увести модель.

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

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

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

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

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

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

Кнопка Добавить служить для відображення діалогового вікна Добавление ограничения (рис. 1.3).

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

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

Виберіть зі списку, що розкривається, умовний оператор, який необхідно розмістити між посиланням та її обмеженням. Це знаки операторів "не більш", не "менше", "дорівнює" і так далі.

Умовні оператори цілого і двійкового типу можна застосовувати лише при накладенні обмежень на змінні комірки.

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

Щоб приступити до набору нової умови, натисніть кнопку Добавить.

Щоб повернутися в діалогове вікно "Поиск решения", натисніть кнопку ОК.

Команда Изменить діалогового вікна "Поиск решения" служить для відображення діалогового вікна Изменение ограничения.

Команда Удалить служить для зняття вказаного курсором обмеження.

Команда Выполнить служить для запуску пошуку рішення поставленої задачі.

Команда Закрыть служить для виходу з вікна діалогу без запуску пошуку рішення поставленої задачі. При цьому зберігаються установки, зроблені у вікнах діалогу, які з'являлися після натискань на кнопки Параметры, Добавить, Изменить або Удалить.

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

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

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

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

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

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

Вигляд вікна Поиск решения для розглянутого прикладу наведений на рис. 1.6, робочого аркуша з результатами – на рис. 1.7.

Рис. 1.6 Вікно Поиск решения з уведеними параметрами для випадку чотирьох змінних (виробів А, Б, В, Г)

Рис. 1.7 Вигляд робочого аркуша з результатами