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

1. Зробити форму для задачі у вигляді двох взаємопов’язаних таблиць (табл. 1, табл. 2) (рис. 6.1).

2. Ввести вихідні дані задачі та залежності математичної моделі (6.3) - (6.4) в таблиці (рис. 6.2):

2.1. Ввести вартості виконання робітником виду робіт у діапазон даних В4:Е7 табл. 1.

2.2. Ввести обмеження на можливу кількість запропонованих робітників у діапазон даних F4:F7 табл. 1.

2.3. Ввести обмеження на можливу кількість запропонованих посад у діапазон даних В8:Е8 табл. 1.

2.4. У діапазон даних В12:Е15 табл. 2 ввести одиничні кількості відповідності посад працівникам.

2.5. Комірки в рядку Замовлення В16:Е16 табл. 2 зв’язати функцією суми з комірками відповідних стовпчиків діапазону В16=СУММ(В12:В15).

2.6. Комірки стовпчика Можливості F12:F15 табл. 2 зв’язати функцією суми з комірками відповідних рядків F12= СУММ(В12:Е12).

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

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

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

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

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

· “ОК”;

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

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

· в масив 2 ввести В12:Е15;

· "ОК";

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

Встановлення функціональних зв’язків між комірками закінчено.

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

· курсор в цільову комірку F18;

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

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

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

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

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

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

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

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

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

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

· аналогічно вводимо наступні обмеження (В12:E15 <= 1); (В12:E15 = целое) (після кожного обмеження – "Добавити");

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

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

· аналогічно вводимо адресу F4:F7, в полі "Обмеження" обираємо знак =, в праве поле вводимо F12:F15. Отримуємо обмеження: F4:F7 = F12:F15;

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

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

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

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

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

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

· “ОК”;

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

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

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

· “ОК”.

5. Так як задача має не одне оптимальне рішення, один з результатів розв’язання задачі наводиться на рис. 6.3. Діапазон В12:Е15 табл. 2 заповнений значеннями оптимального розподілу посад за працівниками, а в цільовій комірці F18 з’явилася сумарна мінімальна вартість виконання робіт.

Видно, що значення цільової функції складе 210 у.о. (комірка F18), а посади будуть розподілені наступним чином: Іванов І.П. – економіст (комірка B12); Петров С.М. – касир (комірка D13); Сидоров В.Н. – бухгалтер (комірка C14); Федотов Р.Д. – менеджер (комірка E15).

Задані обмеження щодо призначення кожного працівника тільки на одну посаду (комірки F12, F13, F14, F15) та забезпечення всіх посад працівниками (комірки B16, C16, D16, E16) виконуються.

 

	A	B	C	D	E	F
						Таблиця 1
	Працівники	Посади	Можливість
	Економіст	Бухгалтер	Касир	Менеджер
	Іванов І.П.
	Петров С.М.
	Сидоров В.Н.
	Федотов Р.Д.
	Замовлення
						Таблиця 2
	Працівники	Посади	Можливість
	Економіст	Бухгалтер	Касир	Менеджер
	Іванов І.П.
	Петров С.М.
	Сидоров В.Н.
	Федотов Р.Д.
	Замовлення
						ЦФ

Рис. 6.1. Форма для розв’язання задачі вибору (1)

 

 

	A	B	C	D	E	F
						Таблиця 1
	Працівники	Посади	Можливість
	Економіст	Бухгалтер	Касир	Менеджер
	Іванов І.П.
	Петров С.М.
	Сидоров В.Н.
	Федотов Р.Д.
	Замовлення
						Таблиця 2
	Працівники	Посади	Можливість
	Економіст	Бухгалтер	Касир	Менеджер
	Іванов І.П.
	Петров С.М.
	Сидоров В.Н.
	Федотов Р.Д.
	Замовлення
						ЦФ

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

 

	A	B	C	D	E	F
						Таблиця 1
	Працівники	Посади	Можливість
	Економіст	Бухгалтер	Касир	Менеджер
	Іванов І.П.
	Петров С.М.
	Сидоров В.Н.
	Федотов Р.Д.
	Замовлення
						Таблиця 2
	Працівники	Посади	Можливість
	Економіст	Бухгалтер	Касир	Менеджер
	Іванов І.П.
	Петров С.М.
	Сидоров В.Н.
	Федотов Р.Д.
	Замовлення
						ЦФ

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