УДК 004.94

, кандидат технічних наук, доцент,

, магістрант

ЗАСТОСУВАННЯ MS EXCEL 2010 ДЛЯ РОЗВ’ЯЗАННЯ ЗАДАЧ, ЩО ПРИВОДЯТЬ ДО ЛІНІЙНОЇ ЦІЛЬОВОЇ ФУНКЦІЇ

У статті розглянуто розв’язання задач оптимізації за допомогою MS EXCEL 2010.

Ключові слова: цільова функція, обмеження, розв'язувач.

Існує коло практичних задач, розв’язання яких зводиться до визначення найбільшого або найменшого значення деякої лінійної функції. Такими завданнями є [1]:

· Асортимент продукції. Максимізація випуску товарів при обмеженнях на сировину для виробництва цих товарів.

· Штатний розклад. Складання штатного розкладу для досягнення найкращих результатів при найменших витратах.

· Планування перевезень. Мінімізація витрат на транспортування товарів.

· Складання суміші. Досягнення заданої кількості суміші при найменших витратах.

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

Розглянемо розв’язання за допомогою MS Excel 2010 задачі «Планування перевезень».

Умова задачі

Нехай на дві бази А і В прибуло 30 вагонів з деяким продуктом, по 15 вагонів на кожну базу. Всі вагони потрібно доставити в пункти споживання С і D: в пункт С необхідно доставити 10 вагонів, а в пункт D - 20. Відомо, що транспортування одного вагону з бази А в пункти С і D коштує відповідно 1 і 3 грошові одиниці, а з бази В - відповідно 2 і 5 одиниць. Скласти план транспортування мінімальний за вартістю [2].

НЕ нашли? Не то? Что вы ищете?

Математична модель

Припустимо, що в пункт С прибуло «х» вагонів з бази А і «y» вагонів з бази В. У пункт D прибуло «z» вагонів з бази А і «u» вагонів з бази В. Умову задачі наведемо у вигляді таблиці, в якій в дужках вказана вартість перевезення одного вагона (табл. 1).

Таблиця 1

План транспортування

Всього

Пункт С

Пункт D

База А

Х (1)

Z (3)

15

База В

Y (2)

U (5)

15

Всього

10

20

У відповідності з умовою задачі (табл. 1) система обмежень має вигляд:x+y=10; z+u=20; x+z=15; y+u=15 (1)

x ≥ 0; y ≥ 0; z ≥ 0; u ≥ 0 (2)

Загальна вартість транспортування F визначається залежністю:

F = 1*x + 3*z + 2*y + 5*u (3)

Розв’язання задачі за допомогою MS EXCEL 2010.

1. У робочому вікні MS Excel 2010 необхідно створити таблиці вихідних даних і результатів розрахунків (рис. 1).

2. У комірку «D18» введіть цільову функцію, яка визначає вартість перевезення вагонів. Для цього використовуйте залежність (3):

= A9 * В15 + В9 * С15 + С9 * В16 + D9 * С16

3. Виконаєте команду: Данi - Розв'язувач. З'явиться вікно «Параметри розв'язувача»

Рис. 1.

4. У вікні «Параметри Розв'язувача»:

· Вкажіть комірку з цільовою функцією «$D$18» в текстовому полі «Оптимізувати цільову функцію»;

· Увімкніть перемикач «Мiнiмум»;

· У текстовому полі «Змінюючи комірки змінних» вкажіть діапазон клітин, в яких знаходяться значення змінних «х», «y», «z», «u»: $В$15: $С$16.

· Натисніть кнопку «Додати». З'явиться вікно «Додати обмеження».

5. У вікні «Додати обмеження» введіть обмеження відповідно до залежностей (2) (рис. 2).

Рис. 2

6. У вікні «Додати обмеження» введіть обмеження відповідно до залежностей (1) (рис. 3).

Рис. 3

7. У вікні «Параметри розв'язувача» клацніть кнопку «Розв'язати». З'явиться вікно «Результати розв'язувача»

8. У вікні «Результати розв'язувача» клацніть кнопку «ОК». План перевезень з'явиться в таблиці «Результати розрахунків» (рис. 4).

Рис. 4

З таблиці випливає, що з бази «А» всі вагони треба відправити в пункт «D», а з бази «В» 10 вагонів треба відправити в пункт «С», а 5 - в пункт «D». Транспортні витрати при цьому складуть 90 грошових одиниць. Оптимальний план побудований без використання самого вигідного на перший погляд маршруту А - С (перевезення з «А» в «С» найдешевше). Включення цього самого вигідного маршруту в план перевезень не призводить до його поліпшення. Якщо, наприклад, включити в план перевезення хоча б один вагона з «А» в «С», то в пункт «D» з «А» доведеться відправити 14 вагонів, з «В» до «С» - 9, з «В »в« D »- 6. Витрати на транспортування при цьому складуть 91 грошову одиницю, що дорожче, ніж при оптимальному плані (рис. 4).

Наведені розрахунки показують, що за допомогою MS Excel 2010 досить просто визначається план оптимальних перевезень і мінімальна вартість перевезень.

Список використаних джерел

1. MicrosoftExcel 97 в подлиннике: В 2 т.: пер. с англ. / Р. Персон. – СПб.: BHV – Санкт-Петербург, 1997. Том 2. – 640 с., ил.

2. Монахов оптимизации. Применение математических методов в экономике: Пособие для учителей /, , . - М., Просвещение, 19с.

3. Первый взгляд на MicrosoftOffice 2010. MicrosoftPress/ К. Мюррей, 20с.

The article deals with solving optimization problems using MS EXCEL 2010.

Keywords: objective function, restriction, solver.