5. Представление исходных данных в таблице excel
|
|
A |
B |
С |
D |
E |
F |
G |
H |
I |
J |
K |
L |
|
1 |
Номер (i) бриг. |
Выходные дни бригады |
Количество работников |
|
Вс |
Пн |
Вт |
Ср |
Чт |
Пт |
Сб | |
|
2 |
|
|
xi |
|
Дни
(j)
работы
бригады
| |||||||
|
3 |
1 |
Воскресенье, понедельник |
x1 |
|
0 |
0 |
1 |
1 |
1 |
1 |
1 | |
|
4 |
2 |
Понедельник, вторник |
x2 |
|
1 |
0 |
0 |
1 |
1 |
1 |
1 | |
|
5 |
3 |
Вторник, среда |
x3 |
|
1 |
1 |
0 |
0 |
1 |
1 |
1 | |
|
6 |
4 |
Среда, четверг |
x4 |
|
1 |
1 |
1 |
0 |
0 |
1 |
1 | |
|
7 |
5 |
Четверг, пятница |
x5 |
|
1 |
1 |
1 |
1 |
0 |
0 |
1 | |
|
8 |
6 |
Пятница, суббота |
x6 |
|
1 |
1 |
1 |
1 |
1 |
0 |
0 | |
|
9 |
7 |
Суббота, воскресенье |
x7 |
|
0 |
1 |
1 |
1 |
1 |
1 |
0 | |
|
10 |
|
|
|
|
Плановое количество работников в день, | |||||||
|
11 |
Всего постоянных работн. |
=СУММ(D3:D9) |
|
=D3*F3+D4*F4+…+D9*F9 |
… |
=D3*L3+D4*L4+…+D9*L9 | ||||||
|
12 |
|
|
|
|
Ежедневная
потребность в работниках,
| |||||||
|
13 |
|
|
|
|
22 |
17 |
13 |
14 |
15 |
18 |
24 | |
|
14 |
|
|
|
|
Превышение потребности | |||||||
|
15 |
Дневная оплата работников |
1000 |
|
=F11-13 |
=G11-13 |
… |
… |
… |
… |
=L11-L13 | ||
|
16 |
Дневной фонд зарплаты |
=D11*D15 |
|
|
|
|
|
|
|
| ||
Пример №1: Задача «Планирование мобильной численности персонала на предприятии» представлена на рис. 1:
Обратить внимание: Количество работников берем произвольно
Рис. 1. Ввод исходных данных.
В таблице в ячейке D11 вычисляется количество работников во всех бригадах.
В ячейке D16 вычисляется дневной фонд заработной платы. Это целевая функция – критерий качества плана, он подлежит минимизации. При равенстве зарплаты сотрудников, также будет минимизироваться и их численность.
В каждой ячейке диапазона F11:L11 вычисляется общее количество работников, занятых в конкретный день недели.
6. Алгоритм поиска оптимального плана с помощью excel
1. Откройте меню Сервис и убедитесь, что в Вашей программе установлена надстройка Поиск решения. Программа «Поиск решения» является надстройкой Excel, которая доступна после установки Microsoft Office или Microsoft Excel. Если же она отсутствует в Вашей установке, то для того чтобы использовать эту надстройку в Excel, необходимо прежде загрузить ее:
в меню Сервисвыберите командуНадстройки.
в поле Список надстроекустановите флажок рядом с элементомПоиск решения, а затем нажмите кнопкуОК.
Совет.Если в списке отсутствует элементПоиск решения, нажмите кнопкуОбзор, чтобы найти надстройку самостоятельно.
в случае появления сообщения о том, что надстройка «Поиск решения» не установлена на компьютере, нажмите кнопку Да, чтобы установить ее.
нажмите кнопку Сервисв строке меню. После загрузки надстройки «Поиск решения» в менюСервисдобавляется командаПоиск решения.
2. Вызовите команду меню Сервис→Поиск решения. Появится диалоговое окно оптимизатора.
3. В диалоговом окне Поиск решенияв полеУстановить целевуювведите адресD16 дневного фонда зарплаты. Установите флажокМинимальному значению.
4. В поле Изменяя ячейкивведите адреса диапазона искомого плана количества работников в бригадахD3:D9.
5. В поле Ограничениявведите три строки условий, ограничивающий область допустимых решений задачи:
D3:D9=целое
D3:D9>=0
F11:L11>F13:L13
Первое условие является сигналом для EXCELприменения метода целочисленного программирования, так как невозможно использовать бригаду в количестве 3,4 человека. Второе условие говорит о том, что число людей в бригаде не может быть отрицательным. Третье условие гарантирует, что плановое количество работников будет не меньше требуемого.
6. Осуществить решение, нажав кнопку ВыполнитьокнаПоиск решения.
РЕШЕНИЕ задачи (см рисунки):
