Алгоритм розв’язання задачі №5

За допомогою табличного процесора MS Excel (2)

1. Зробити форму для задачі (рис. 5.4).

2. Ввести вихідні дані задачі в форму (рис. 5.5).

3. Ввести залежності з математичної моделі (5.3) – (5.4) в форму:

3.1.Ввести залежність для цільової функції (5.3):

· курсор в комірку N6;

· курсор на кнопку ”Майстер функцій” f_x;

· на екрані: діалогове вікно "Майстер функцій – крок 1 з 2";

· курсор у вікно функції на СУММПРОИЗВ (категорія: Математичні);

· “ОК”;

· на екрані: діалогове вікно СУММПРОИЗВ;

· в масив 1 ввести В3:М3 (виділити мишкою);

· в масив 2 ввести В6:М6;

· "ОК";

· на екрані: в N6 введені значення цільової функції “=СУММПРОИЗВ(В3:М3;В6:М6)”.

3.2.Ввести залежності для лівих частин обмежень (5.4):

· курсор в комірку N9;

· курсор на кнопку ”Майстер функцій” f_x;

· на екрані: діалогове вікно "Майстер функцій – крок 1 з 2";

· курсор у вікно функції на СУММПРОИЗВ (категорія: Математичні);

· “OK”;

· на екрані: діалогове вікно СУММПРОИЗВ;

· в масив 1 ввести В3:М3 (виділити мишкою);

· в масив 2 ввести В9:М9;

· "ОК";

(аналогічно - для N10, N11, N12, N13, N14, N15);

· На екрані:

в N9 маємо “=СУММПРОИЗВ(В3:М3;В9:М9)”;

в N10 “=СУММПРОИЗВ(В3:М3;В10:М10)”;

в N11 “=СУММПРОИЗВ(В3:М3;В11:М11)”;

в N12 “=СУММПРОИЗВ(В3:М3;В12:М12)”;

в N13 “=СУММПРОИЗВ(В3:М3;В13:М13)”;

в N14 “=СУММПРОИЗВ(В3:М3;В14:М15)”;

в N15 “=СУММПРОИЗВ(В3:М3;В15:М15)”.

Введення вихідних даних закінчено.

4. Робота в діалоговому вікні Пошук рішення:

· курсор в меню "Сервіс";

· команда "Пошук рішення";

· на екрані: діалогове вікно"Пошук рішення";

· курсор в поле "Установити цільову комірку", ввести адрес цільової функції: N6;

· ввести направлення цільової функції: "Мінімальному значенню";

· курсор в поле "Змінюючи комірки", ввести адрес В3:М3 (виділити мишкою);

· курсор в "Добавити";

· на екрані: діалогове вікно “Добавлення обмежень”;

· в поле "Посилання на комірку" вводимо адресу В3, в полі "Обмеження" обираємо знак >=, в праве поле вводимо адресу В4. Отримуємо обмеження: В3 >= В4;

· "Добавити";

· аналогічно вводимо наступні обмеження (після кожного обмеження – "Добавити"):

С3 >= С4; D3 >= D4; Е3 >= Е4; F3 >= F4; G3 >= G4; H3 >= H4;

I3 >= I4; J3 >= J4; K3 >= K4; L3 >= L4; M3 >= M4; N9 = P9;

N10 = P10; N11 = P11; N12 = P12; N13 = P13; N14 = P14; N15 = P15;

· в кінці останнього обмеження замість "Добавити" ввести "ОК";

· на екрані: діалогове вікно "Пошук рішення" з введеними умовами.

5.Розв’язання транспортної задачі:

· курсор в "Параметри";

· на екрані: діалогове вікно "Параметри пошуку рішення";

· ввести задані параметри задачі ("Лінійна модель", оцінка "Лінійна");

· “ОК”;

· курсор в "Виконати";

· на екрані: діалогове вікно "Результати пошуку рішення";

· зберегти знайдене рішення;

· “ОК”.

6. Результати розв’язання задачі наводяться на рис. 5.6.

Видно, що значення цільової функції (мінімальні витрати) складає
67000 у.о. (комірка N6) при наступних значеннях змінних: X₁₁=0 (В3); X₁₂=150 (C3); X₁₃=0 (D3); X₁₄=0 (E3); X₂₁=0 (F3); X₂₂=0 (G3); X₂₃=150 (H3); X₂₄=100 (I3); X₃₁=50 (J3); X₃₂=0 (K3); X₃₃=0 (L3); X₃₄=50 (M3). Таким чином, із Кривого Рогу до Дніпропетровська треба перевезти 150 т вантажу; із Харкова до Києва 150 т вантажу; із Харкова до Сімферополя 100 т; з Одеси до Запоріжжя 50 т; та з Одеси до Сімферополя 50 т вантажу.

Задані обмеження на пропозицію підприємств-постачальників (комірки N9, N10, N11) та попит підприємств-споживачів (комірки N12, N13, N14, N15) виконуються.

 

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Змінні

ім’я

х11

х12

х13

х14

х21

х22

х23

х24

х31

х32

х33

х34

значення

ниж. границя

верх. границя

ЦФ

коеф. в ЦФ

Обмеження

вид

Коефіцієнти

лів. ч.

знак

пр. ч.

=

=

=

=

=

=

=

Рис. 5.4. Форма для розв’язання транспортної задачі (2)

 

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Змінні

ім’я

х11

х12

х13

х14

х21

х22

х23

х24

х31

х32

х33

х34

значення

ниж. границя

верх. границя

ЦФ

коеф. в ЦФ

Обмеження

вид

Коефіцієнти

лів. ч.

знак

пр. ч.

=

=

=

=

=

=

=

Рис. 5.5. Введення вихідних даних у форму (2)

 

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Змінні

ім’я

х11

х12

х13

х14

х21

х22

х23

х24

х31

х32

х33

х34

значення

ниж. границя

верх. границя

ЦФ

коеф. в ЦФ

Обмеження

вид

Коефіцієнти

лів. ч.

знак

пр. ч.

=

=

=

=

=

=

=

Рис. 5.6. Знаходження оптимального рішення (2)