Дані задачі ЛП у робочому аркуші EXCEL

Данізадачі ЛП

Лінійного програмування

Використання EXCEL для розв’язання задач

Задачі лінійного програмування можна розв’язувати за допомогою програмиSolver, яка входить у надбудови EXCEL пакета Microsoft Office. Але спочатку треба впевнитись у тому, що ця програма завантажена: меню Tools (Сервис) → Add-Ins (Надстройки) → діалогове вікно Add-Ins (Надстройки), встановити прапорець VSolver Add-in, OK → Програма Solver завантажується. Якщо завантаження не виконується, то це означає, що треба виконати з CD ROM більш повне завантаження EXCEL.

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

ПРИКЛАД. Розглянемо задачу ЛП у стандартній формі: отримати максимальний виторг від виробництва Товару 1 і Товару 2. При вказаних у табл. 1 даних отримуємо математичну модель.

Таблиця 1

Матеріал	Ресурс	Норма витрат, кг/шт.
Товар 1	Товар 2
Сіно	30 кг	0,5 кг/шт	0,3 кг/шт.
Силос	50 кг	0,4 кг/шт	0,8 кг/шт.
Кількість, шт.	-	Х1	Х2
Вартість, грн.	-

F → 10 X₁ + 15 X₂ → max; (1)

0,5 X₁ + 0,3 X₂ ≤ 30; (2)

0,4 X₁ + 0,8 X₂ ≤ 50. (3)

Цю математичну модель ми повинні оформити у вигляді табл. 2 на робочому аркуші EXCEL, якщо завантажена програма Solver. На базі моделі (1) – (3) у нижченаведених рівняннях (4) – (7) ми спочатку визначаємо суми відповідних виразів (зліва у цих рівняннях указані адреси комірок, у які вводяться відповідні формули; обмеження у вигляді “F → max”, “ ≤30”, ” ≤50”-спочатку не враховуються):

$C$7 = F = $C$4 + $C$5 = 10X1 + 15X2; (4)

$D$7 = $D$4 + $D$5 = 0,5X1 + 0,3X2; (5)

$E$7 = $E$4 + $E$5 = 0,4X1 + 0,8X2; (6)

$В$7 = $B$4 + $B$5 = X1 + X2. (7)

Формули (4) – (7) ми вносимо у відповідні комірки

Таблиця2

	A	B	C	D	E
	Рішення задачі лінійного програмування
	Назва	Кількість	Витрати	Cіно	Силос
	Товар 1	= X1	= 10 SB$5	= 0,5 $B$4	= 0,4 $B$4
	Товар 2	= X2	= 15 SB$5	= 0,3 $B$5	= 0,8 $B$5
	Підсумок	=$B$4+$B$5	=$С$4+$С$5	=$D$4+$D$5	=$B$4+$B$5

Але у дійсності у комірки $B$4 та $B$5 (табл.2) ми повинні внести не “= X₁” та “= X₂” (як це показано на них), а відповідно “= 0” та “= 0” (це означає, що початкові значення змінних “X₁ = 0” та “X₂ = 0”, а потім програма Solver надасть їм оптимальні величини; значення “= X₁” та “= X₂” введені у комірки $B$4 та $B$5 навмисно – з метою інформації, де будуть виводитись рішення системи Solver щодо оптимальної величини цих змінних). Після заповнення комірок табл. 2формулами всі комірки вказують нульові значення (це тому, що X₁ = 0 та X₂ = 0).

Далі переходимо до введення обмежень на отримані суми у табл.2

виділити отриману таблицю на робочому аркуші EXCEL → Меню Tools → Solver → ДВ SolverParameters. У ДВ SolverParameters виконати такі дії:

- у вікні SetTargetCell ввести адресу функції мети ($C$7);

- серед перемикачів “EqualTo:” вибрати перемикач Max;

- у вікні ByChangingCells вказати, у яких комірках треба, щоб

програма змінювала значення для отримання оптимального

результату; з цією метою введіть курсор у це вікно і на робочому

аркуші виділіть комірки $B$4, $B$5, щоб вони автоматично

вписались у це вікно як $B$4:$B$5;

- при натисненні кн. Guess виділяється діапазон комірок, на які є

посилання у функції мети; кн. Change, Delete змінюють або вилучають

введені обмеження;

- у вікні SubjecttotheConstraints треба ввести потрібні обмеження

задачі ЛП: для цього 1ЛКМ на кн. Add → ДВ AddConstraint (Додати

обмеження), у полі CellReference ввести адресу комірки, у якій

зберігається сума функції мети ($C$7), у полі Constraint ввести MAX –

в результаті ми повинні отримати у цьому ДВ вигляд “$C$7 → MAX”.

Кн. Add. →

- У попередньому ДВ SolverParameters з’явиться таке ж обмеження

“$C$7 → MAX”

Далі ДВ AddConstraint залишається відкритим, і ми повинні ввести у ньому наступне обмеження: у полі CellReference зробити посилання на комірки $B$4, $B$5 і вибрати операцію INT (ціле число), кн. Add →

У ДВ SolverParameters з’являться обмеження “$B$4: $B$5 =

INTEGER”.

Таким же чином за допомогою ДВ AddConstraint ми задаємо обме-ження$D$7 <= 30; $E$7 < = 50.

Для збереження заданих параметрів у ДВ треба зберегти книгу EXСEL.

Для ініціалізації розрахунків виконати дії: виділити отриману табли-цю на робочому аркуші EXCEL → Меню Tools → Solver → ДВ SolverParameters, кн. Solver → у табл. 2 з’являються результати розрахунків з повідомленням про виконання роботи. На цьому розрахунки завершуються.

Якщо потрібно внести у табл.2 нові розрахунки для нових даних, то виконують дії: виділити отриману таблицю на робочому аркуші EXCEL → Меню Tools → Solver→ ДВ SolverParameters, кн. Options → ДВ SolverOptions, встановити опцію KeepSolverSolution. У результаті таблиця буде оновлена, але початкові значення зберігаються і їх можна відновити (але при встановленні опції RestoreOriginalValueі відсутності завдання на складання звіту попередні дані будуть вилучені).

У результаті розрахунків ми отримали інформацію про початкові і

розраховані значення параметрів; зберігаються також всі попередні розрахунки.

Рішення Solverможна зберегти як сценарій: виділити отриману

таблицю на робочому аркуші EXCEL → Меню Tools → Solve → ДВ SolverParameters, кн. Option→ ДВ SolverOptions: Кн. SaveModel– зберігається сукупність параметрів та обмежень сумісно з робочим листом: відкривається ДВ SaveModel, у якому вказують область адрес моделі. Модель зберігається у вертикальному інтервалі комірок, який починається з виділеної комірки.

Кн. LoadModel завантажує модель.

Додаток А

Дата добавления: 2014-11-29