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

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

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

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

3. Ввести залежності з математичної моделі (6.3) - (6.4) в форму (рис. 6.5):

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

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

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

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

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

· “ОК”;

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

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

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

· "ОК";

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

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

· курсор в R9;

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

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

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

· “OK”;

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

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

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

· "ОК";

(аналогічно – для R10, R11, R12, R13, R14, R15, R16);

· на екрані:

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

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

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

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

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

в R14 “=СУММПРОИЗВ(В3:Q3;В14:Q14)”;

в R15 “=СУММПРОИЗВ(В3:Q3;В15:Q15)”;

в R16 “=СУММПРОИЗВ(В3:Q3;В16:Q16)”;

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

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

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

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

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

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

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

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

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

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

· в поле "Посилання на комірку" вводимо адресу В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; N3 >= N4;

O3 >= O4; P3 >= P4; Q3 >= Q4; R9 = T9; R10 = T10; R11 = T11;

R12 = T12; R13 = T13; R14 = T14; R15 = T15; R16 = T16;

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

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

5. Розв’язання задачі про призначення персоналу:

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

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

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

· “ОК”;

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

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

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

· “ОК”.

6. Так як задача має не одне оптимальне рішення, один з результатів розв’язання задачі наводиться на рис. 6.6.

Видно, що значення цільової функції (мінімальна вартість виконання робіт) складе 210 у.о. (комірка R6) при наступних значеннях змінних: x₁₁ = 1, x₁₂ = 0, x₁₃ = 0, x₁₄ = 0, x₂₁ = 0, x₂₂ = 0, x₂₃ = 1, x₂₄ = 0, x₃₁ = 0, x₃₂ = 1, x₃₃ = 0, x₃₄ = 0, x₄₁ = 0, x₄₂ = 0, x₄₃ = 0, x₄₄ = 1.

Задані обмеження щодо призначення кожного працівника тільки на одну посаду та забезпечення всіх посад працівниками (комірки R9–R16) виконуються.

 

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

Змінні

ім’я

х11

х12

х13

х14

х21

х22

х23

х24

х31

х32

х33

х34

х41

х42

х43

х44

значення

ниж. границя

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

ЦФ

коеф. в ЦФ

Обмеження

вид

Коефіцієнти

л. ч.

знак

п. ч.

=

=

=

=

=

=

=

=

Рис. 6.4.Форма для розв’язання задачі про призначення персоналу (2)

 

 

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

Змінні

ім’я

х11

х12

х13

х14

х21

х22

х23

х24

х31

х32

х33

х34

х41

х42

х43

х44

значення

ниж. границя

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

ЦФ

коеф. в ЦФ

Обмеження

вид

Коефіцієнти

л. ч.

знак

п. ч.

=

=

=

=

=

=

=

=

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

 

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

Змінні

ім’я

х11

х12

х13

х14

х21

х22

х23

х24

х31

х32

х33

х34

х41

х42

х43

х44

значення

ниж. границя

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

ЦФ

коеф. в ЦФ

Обмеження

вид

Коефіцієнти

л. ч.

знак

п. ч.

=

=

=

=

=

=

=

=

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

Варіанти завдань для самостійного розв’язання задачі №6

В табл. 6.6 наводяться оцінки вартості c_іj призначення працівників 1, 2, 3, 4 на вакантні посади 1, 2, 3, 4. Потрібно знайти оптимальний розподіл працівників за робочими місцями, що забезпечує мінімальну вартість виконання робіт.

Таблиця 6.6