Система принятия решений и оптимизации в Excel, конструирование баз данных в Access

человек сколько и в пятницу, а в субботу и в воскресение в 2 раза меньше. Из этого следует, что в расписании X₅ и X₇ – равны и меньше чем X₆ в 2 раза(2*X₅ = 2*X₇ = X₆ , X₅ + 2*X₅ + X₅ =4* X₅ =80, X₅ = 20). Таким образом, X₁ + X₂ + X₃ + X₄=20; X₅ =20; X₆ =40; X₇ =20

В дальнейшем, изменяя эти значения, будем искать  оптимальное решение, обеспечивающее минимальный фонд зарплаты (т.е. минимальное число работающих) при соблюдении требований к рабочему графику.

Определение числа работников, выходящих на работу

каждый день согласно данному графику.

В столбцах «Рабочие дни» введем параметры A_IJ-индикаторы выхода на работу сотрудников, имеющих режим выхода в строке i в рабочий день j. Например, А₁₁ индикатор выхода сотрудника, имеющего выходной в первой строке, в понедельник. Так как этот день у него выходной, А₁₁=0. В среду у этого сотрудника рабочий день, поэтому А₁₃=1.

В строке «Всего» подсчитывается количество работников, выходящих на работу согласно составленному графику.

Во втором столбце «число имеющих этот график»

X₁ + X₂ + X₃ + X₄ + X₅ + X₆ + X₇=5+5+5+5+20+40+20=100

В столбце «Всего» для понедельника общее число работающих подсчитывается по формуле

X₁ * А₁₁ + X₂ * А₂₁ + X₃ * А₃₁ + X₄ * А₄₁ + X₅ * А₅₁ + X₆ * А₆₁ + X₇* А₇₁ =

 =5*0+5*1+5*1+5*1+20*1+40*1+20*0=75, т.е. находится сумма произведений элементов столбца «X» на элементы столбца «Пн».

Аналогичным образом, чтобы найти число работающих согласно данному графику во вторник, нужно найти сумму произведений элементов столбца «X» на элементы столбца «вт».

В последней строке «Требуется» записано число работников, которые должны работать каждый день недели согласно заданию (Табл1).

Таблица 2.

Исходя из полученного ответа требования задачи не выполнены, т.к. в понедельник требуется не 75, а 80 человек, поэтому к значению работников у которых выходной во вторник и в среду я прибавлю такое значение чтобы получить в понедельник 80 рабочих.

Минимальные требования к рабочей силе выполнены, расписание составлено так что каждый день работает штат необходимый для фирмы. Однако он превышает имеющийся по количеству на 5 человек.

Определение целевой функции задачи.

Как видим, в строке «Всего» удовлетворяются требования строки «Требуется» с избытком, т.е. план явно не оптимальный. Следует улучшить этот план, имея в виду нахождение минимума для целевой функции – фонда недельной зарплаты, которая считается следующим образом:

F = B * K, где   F – фонд заработной платы,

B– зарплата работников за день (согласно заданию, B=80 руб.),

K– число выходов сотрудника в течение недели (в нашем случае согласно табл2 строка «Всего»)

K=80+90+90+95+80+45+45=525

Таким образом,

F =80*525=42000

Разработка электронной таблицы.

1. Составим электронную таблицу, реализующую планирование списочного состава и графика работы фирмы (табл. 7 – показ вычислений, табл. 8 – показ формул);
2. Введем на рабочий лист необходимые исходные данные согласно табл. 6 – ячейки А1:К10;
3. Теперь можно приступить к вводу формул, описывающих количество работающих сотрудников. Поместим в ячейку С14 общее количество сотрудников, работающих по данному графику, а в интервал ячеек Е14:К14 – количество работающих сотрудников в соответствующий день недели. Для этого в ячейку С14 поместим формулу суммирования значений.

Воспользуемся «Мастером функций»

В формулах таблица имеет вид:

Оптимизация (улучшение) решения.

Для этого используем режим Поиск решения Excel

1) Откроем диалоговое окно Поиск решения

2) Установить целевую ячейку равной минимальному значению

3) Введем ограничения

4) Найдем решение

Результат оптимизации выглядит так:

Из решения видно что после оптимизации общая недельная заработная плата уменьшилась от 42 000 руб. до 40 000 руб., а количество сотрудников для данного штата работников остался в пределах условия задачи, т.е. 100 человек.

Вывод: В отличие от предложенного мною варианта рабочих смен