Экономико-математические модели и методы проектного менеджмента

В чарунку І8 введемо формулу для цільової функції. Згідно умовам задачі, значення цільової функції визначається формулою

.

Використовуючи позначення відповідних чарунок в Excel, цю формулу можна записати  так:   B8*B4+C8*C4+D8*D4+E8*E4+F8*F4+G8*G4.   Щоб   задати   цю

 

 

 

Рис. 4.1. Лист Excel з умовами задачі

 

 

формулу, введемо в  чарунку І8 вираз =СУММПРОИЗВ(B4:G4;B8:G8), як це зображено на рисунку 4.1. Функція СУММПРОИЗВ є вбудованою функцією Excel. Її можна викликати так: Мастер функций – Математические – СУММПРОИЗВ. У вікні, що з’явиться, необхідно задати вираз B4:G4 в рядку Массив1 і B8:G8 в рядку Массив2.

Аналогічно в чарунки  М13 – М21 введемо формули для  обмежень задачі (без знаків нерівностей  та правих частин). Для зручності  у першу чарунку М13 введемо  формулу =СУММПРОИЗВ($B$4:$G$4;G13:L13). Цю формулу скопіюємо в останні чарунки М14 – М21. Знак „$” вкаже, що при копіюванні чарунок їх імена не змінюються.

Знайдемо оптимальний  розв’язок задачі за допомогою надбудови Поиск решения.

Викличемо Сервис – Поиск решения. З’явиться вікно, зображене на рисунку 4.2.

Рис. 4.2. Вікно Поиск решения

У вікні Поиск решения поставимо курсор в полі Установить целевую ячейку та натиснемо лівою клавішею миші на цільову чарунку в екранній формі (чарунку І8). Введемо напрям оптимізації, натиснувши кнопку Максимальному значению. В поле Изменяя ячейки введемо імена чарунок, що відповідають значенням змінних (чарунки В4 – G4). У полі Ограничения натиснемо кнопку Добавить. З’явиться вікно Добавление ограничения, зображене на рисунку 4.3.

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

У поле Ссылка на ячейку введемо ім’я чарунки, в якої записано формулу першого обмеження – М13. Це можна зробити, натиснувши лівою клавішею миші на вказану чарунку в екранній формі. У поле знака виберемо „=”. У полі Ограничение вкажемо чарунку, в якої записано праву частину першого обмеження (О13). Інші обмеження задамо аналогічно. Зауважимо, що обмеження з другого по сьоме мають однакові знаки „ ”. Тому їх можна задати одним рядком, як це показано на рисунку 4.2.

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

Натиснемо у вказаному  вікні кнопку Параметры. Відкриється вікно Параметры поиска решения (рис. 4.4).

Рис. 4.4. Вікно Параметры поиска решения

Параметр Максимальное время служить для визначення часу (у секундах), що витрачається на розв’язання задачі. Стандартний час – 100 секунд. Параметр Предельное число итераций дозволяє обмежити кількість проміжних обчислень. Параметр Относительная погрешность задає точність, з якою визначається відповідь. Параметр Допустимое отклонение задає допуск на відхилення розв’язку від оптимального у цілочислових задачах. Параметр Сходимость застосовується при розв’язанні нелінійних задач. Казання позначення Линейная модель дозволяє застосувати при розв’язанні симплекс-метод, що значно скорочує час на пошук рішення. Вказані параметри підтверджуються натисненням кнопки ОК.

Запуск рішення задачі проводиться із вікна Поиск решения (рис. 4.2). Після запуску на рішення, за умов існування розв’язку задачі, з’явиться вікно Результаты поиска решения (рис. 4.5).

Рис. 4.5. Вікно Результаты поиска решения

У вікні Результаты поиска решения надано, окрім можливості зберегти знайдений розв’язок, три типи звітів: звіт про результати, звіт про стійкість, звіт про границі (рис. 4.5). Звіти використовуються при аналізі моделі на чутливість. Для того, щоб зберегти знайдений розв’язок, натиснемо ОК. Після чого в екранній формі з’явиться оптимальний розв’язок задачі (рис. 4.6), де у чарунці І8 надано оптимальне значення цільової функції, у чарунках В4 – G4 надано відповідні значення змінних.

Надамо відповідь у  термінах задачі.

Найбільший сумарний прибуток від інвестування дорівнює 6725 у. од. та забезпечується такими вкладами (табл. 4.2):

Таблиця 4.2

Назва об’єкту	А	В	С	D	E	F
Вклад	5000	25000	25000	5000	15000	25000

 

 

 

Рис. 4.6. Результати роботи надбудови Поиск решения

 

 

3. Знаходження оптимального рішення задачі

цілочисельного  програмування

 

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

У цьому випадку процес розв’язання задачі доповнюється введенням  відповідної команди у вікні Поиск решения так:

• для додавання обмеження у полі Ограничения натиснемо кнопку Добавить, після чого з’явиться вікно Добавление ограничения;
• в полі Ссылка на ячейку введемо імена чарунок змінних задачі, у наведеному прикладі це чарунки В4:G4 (рис. 4.6);
• в полі введення знака обмеження встановимо Цел, після чого у полі Ограничение з’явиться умова Целое;
• для підтвердження обмеження натиснемо ОК.

Вікно Добавление ограничения з додаванням умови цілочисельності зображено на рисунку 4.7.

Рис. 4.7. Вікно Добавление ограничения з додаванням умови

цілочисельності змінних

 

4. Контрольні питання

 

1. Які етапи побудови математичної моделі оптимізаційної задачі ЛП?
2. Які основні етапи розв’язання задачі ЛП у MS Excel?
3. Як задаються вихідні дані у листі Excel?
4. Як задаються формули для цільової чарунки і чарунок лівих частин обмежень?
5. Який порядок роботи із надбудовою Поиск решения?
6. Як додаються або змінюються обмеження у вікні Поиск решения?
7. Які значення параметрів надбудови Поиск решения?
8. Які особливості розв’язання задач цілочисельного програмування за допомогою надбудови Поиск решения?

 

5. Варіанти завдань для самостійного виконання

 

Завдання 1. Для виготовлення двох видів продукції П₁ та П₂ витрачається три види ресурсів – Р₁, Р₂, Р₃. Запаси ресурсів, норми їх затрат і прибуток від реалізації одиниці продукції задані у таблиці 4.3. Знайти такий план виробництва, який би забезпечував найбільший прибуток від реалізації продукції.

Таблиця 4.3

№ варіанта	Затрати ресурсів на одиницю продукції						Наявність ресурсів			Прибуток  від реалізації одиниці продукції
	Р1		Р2		Р3		Р1	Р2	Р3	П1	П2
	П1	П2	П1	П2	П1	П2
1	13	7	17	16	4	9	361	520	248	11	8
2	1	1	4	7	1	4	18	93	48	24	36
3	3	2	2	3	1	1	101	99	37	27	24
4	4	13	5	6	11	5	379	197	335	25	12
5	3	1	9	4	3	4	45	144	96	9	8
6	14	15	1	2	9	5	400	49	220	21	18
7	11	6	1	2	15	14	324	60	500	10	7
8	2	1	1	5	4	15	48	100	225	12	9
9	3	8	7	2	1	1	187	143	29	10	6
10	2	7	1	1	6	1	126	30	120	20	15
11	9	4	3	2	2	2	175	65	60	15	10

 

Продовж. табл. 4.3

№ варіанта	Затрати ресурсів на одиницю продукції						Наявність ресурсів			Прибуток  від реалізації одиниці продукції
	Р1		Р2		Р3		Р1	Р2	Р3	П1	П2
	П1	П2	П1	П2	П1	П2
12	2	3	2	2	3	2	80	58	75	10	12
13	5	2	2	3	1	8	125	83	152	12	10
14	3	2	4	1	7	8	65	70	235	30	20
15	2	2	7	2	3	8	58	143	197	15	21
16	3	1	8	4	3	4	45	145	95	9	8
17	14	15	2	2	7	5	400	49	220	21	18
18	10	6	1	2	14	15	328	60	500	10	7
19	2	1	2	5	4	15	45	100	220	12	9
20	3	8	5	2	1	1	185	140	29	10	6
21	2	7	1	2	5	1	125	30	120	20	14
22	7	4	3	2	2	2	175	65	60	15	11
23	2	3	2	1	3	2	80	50	75	10	12
24	5	2	1	3	1	8	125	85	152	12	10
25	3	2	4	1	6	7	65	70	235	30	20
26	3	8	5	2	3	1	75	144	96	9	8
27	2	3	2	5	4	12	400	249	220	21	18
28	2	7	1	3	6	1	324	360	500	10	7
29	4	1	2	5	4	10	148	100	225	12	9
30	2	4	1	3	1	7	187	143	129	10	6

 

Завдання 2. Для обладнання нової виробничої дільниці виділено Z тис. грн. Обладнання треба розмістити на площі, не більшій ніж S м². підприємство може замовити обладнання двох типів за такими даними: обладнання першого типу – вартість Z₁ тис. грн., потребує площі S₁ м², випускає продукції на Р₁ тис. грн. за зміну; другого типу – вартість Z₂ тис. грн., потребує площі S₂ м², випускає продукції на Р₂ тис. грн. за зміну. Ресурси, що виділено, та характеристики обладнання двох типів надані у таблиці 4.4. Розрахувати такий варіант придбання обладнання, який би забезпечив максимальну валову виручку.

Таблиця 4.4

№ варіанта	Ресурси, що виділено		Характеристики  обладнання І типу			Характеристики  обладнання ІІ типу
	кошти	площа	вартість	потрібна  площа	продуктивність	вартість	потрібна  площа	продуктивність
	Z	S	Z1	S1	Р1	Z2	S2	Р2
1	25	43	4	8	6	2	5	3
2	24	41	5	8	7	2	4	3
3	23	39	4	7	6	3	5	4
4	22	37	5	7	7	3	4	4
5	21	35	4	8	6	2	5	3
6	20	43	5	8	7	2	4	3
7	25	41	4	7	6	3	5	4
8	24	39	5	7	7	3	4	4
9	23	37	4	8	6	2	5	3
10	20	38	5	8	7	2	4	3
11	22	43	4	7	6	3	5	4
12	21	41	5	7	7	3	4	4
13	25	39	4	8	6	2	5	3
14	24	38	5	8	7	2	4	3
15	23	37	4	7	6	3	5	4
16	35	53	4	8	6	2	5	3
17	34	51	5	8	7	2	4	3
18	33	48	4	7	6	3	5	4
19	32	47	5	7	7	3	4	4
20	31	45	4	8	6	2	5	3
21	33	53	5	8	7	2	4	3
22	36	51	4	7	6	3	5	4
23	34	47	5	7	7	3	4	4