- •3.2.2 Решение транспортной задачи
- •3.2.2.1. Построение математической модели
- •3.2.2.2 Разработка эт с начальным планом решения
- •1. Подготовим блок ячеек с исходными данными
- •2. Построим начальный план перевозок
- •3. Вычислим количество перевозимой продукции
- •4. Определим стоимость перевозок в каждый из магазинов
- •5. Определим общую стоимость перевозок (целевую функцию цф)
- •3.2.2.3. Улучшение (оптимизация) плана перевозок
- •3.2.3. Решение задачи о штате фирмы
- •Определение возможных режимов работы
- •Определение возможного графика работы
- •Определение числа работников, выходящих на работу каждый день согласно данному графику
- •Определение целевой функции задачи
- •3.2.3.2. Разработка электронной таблицы
- •3.2.3.3. Оптимизация решения
- •3.2.4. Задача планирования выпуска продукции
- •3.2.4.1. Построение математической модели
- •3.2.4.2. Разработка начального плана выпуска продукции в эт
- •3.2.4.3. Оптимизация плана выпуска
- •3.2.5. Задача о распределении ресурсов
- •3.2.5.1. Построение математической модели
- •3.2.5.2. Построение начального плана решения
- •3.2.5.3. Оптимизация плана решения
- •3.2.6 Задача об оптимальном составе сплава
- •3.6.1. Построение математической модели
- •3.2.6.2. Построение начального плана решения
- •3.6.3. Оптимизация плана решения
- •3.2.7. Задача о производстве красок
- •3.2.7.1. Построение математической модели
- •3.2.7.2. Построение начального плана решения
- •3.2.7.3. Оптимизация плана решения
Определение целевой функции задачи
Как видим, в строке “Всего” удовлетворяются требования строки “Требуется” с избытком, т.е. план явно не оптимальный. Следует улучшить этот план, имея в виду нахождение минимума для целевой функции – фонда недельной зарплаты, которая считается следующим образом:
F = B K,
где F – фонд заработной платы,
В – зарплата работника за день (согласно заданию, В = 500 руб.),
K – число выходов сотрудника в течение недели (в нашем случае согласно табл. 34 строка “Всего”)
К = 22 + 18 + 18 + 18 + 22 + 26 + 26 = 150
Таким образом,
F = B K = 500 150 = 75 000 (руб.)
Таблица34
Выходные дни |
Число имеющих этот график |
Рабочие дни | ||||||
Пн |
Вт |
Ср |
Чт |
Пт |
Сб |
Вс | ||
Понедельник, вторник |
Х1 = 6 |
а11=0 |
а12=0 |
а13=1 |
а14=1 |
а15=1 |
а16=1 |
а17=1 |
Вторник, среда |
Х2 = 6 |
а21=1 |
а22=0 |
а23=0 |
а24=1 |
а25=1 |
а26=1 |
а27=1 |
Среда, четверг |
Х3 = 6 |
а31=1 |
а32=1 |
а34=0 |
а34=0 |
а35=1 |
а36=1 |
а37=1 |
Четверг, пятница |
Х4 = 6 |
а41=1 |
а42=1 |
а43=1 |
а44=0 |
а45=0 |
а46=1 |
а47=1 |
Пятница, суббота |
Х5 = 2 |
а51=1 |
а52=1 |
а53=1 |
а54=1 |
а55=0 |
а56=0 |
а57=1 |
Суббота, воскресенье |
Х6 = 2 |
а61=1 |
а62=1 |
а63=1 |
а64=1 |
а65=1 |
а66=0 |
а67=0 |
Воскресенье, понедельник |
Х7 = 2 |
а71=0 |
а72=0 |
а73=0 |
а74=0 |
а75=1 |
а76=1 |
а77=0 |
|
|
|
|
|
|
|
|
|
В С Е Г О : |
30 |
22 |
18 |
18 |
18 |
22 |
26 |
26 |
ТРЕБУЕТСЯ |
30 |
16 |
12 |
14 |
16 |
16 |
25 |
25 |
3.2.3.2. Разработка электронной таблицы
1) Составим электронную таблицу (табл. 35 – показ вычислений, табл. 36 и 37 – показ формул), реализующую планирование списочного состава и графика работы фирмы.
2) Введем на рабочий лист необходимые исходные данные согласно табл. 34 – ячейки А1:К10.
3) Теперь можно приступить к вводу формул, описывающих количество работающих сотрудников. Поместим в ячейку С14 общее количество сотрудников, работающих по данному графику, а в интервал ячеек Е14:К14 – количество работающих сотрудников в соответствующий день недели. Для этого в ячейку С14 нужно поместить формулу суммирования значений интервала С4:C10, =СУММ(C4:C10) (SUM(C4:C10)), а в ячейках интервала Е14:К14 следует просуммировать произведения ячеек из столбца С на значения соответствующего столбца из интервала Е:К, заполненного нулями и единицами.
Для этого:
щелкнуть по кнопке Мастер функции, выбрать функцию СУММПРОИЗВ (SUMPRODUCT). В первом окне набрать $C$4:$C$10, во втором Е4:Е10;
щелкнуть по кнопке ОК.
Дадим здесь некоторые пояснения. Поскольку мы собираемся распространить формулу из ячейки Е14 в другие ячейки интервала F14:К14, но при этом постоянно должны ссылаться на интервал С4:C10, необходимо, чтобы эта ссылка была абсолютной (используем знак абсолютного адреса $ для ячеек С4:С10, получаем $С$4:$С$10).
Таблица 35
|
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
1 |
График работы персонала фирмы | ||||||||||
2 |
|
|
Имеющих этот график |
|
|
|
|
|
|
|
|
3 |
Выходные дни |
|
|
|
Пн |
Вт |
Ср |
Чт |
Пт |
Сб |
Вс |
4 |
Понедельник, вторник |
|
6 |
|
0 |
0 |
1 |
1 |
1 |
1 |
1 |
5 |
Вторник, среда |
|
6 |
|
1 |
0 |
0 |
1 |
1 |
1 |
1 |
6 |
Среда, четверг |
|
6 |
|
1 |
1 |
0 |
0 |
1 |
1 |
1 |
7 |
Четверг, пятница |
|
6 |
|
1 |
1 |
1 |
0 |
0 |
1 |
1 |
8 |
Пятница, суббота |
|
2 |
|
1 |
1 |
1 |
1 |
0 |
0 |
1 |
9 |
Суббота, воскресенье |
|
2 |
|
1 |
1 |
1 |
1 |
1 |
0 |
0 |
10 |
Воскресенье понедельник |
|
2 |
|
0 |
1 |
1 |
1 |
1 |
1 |
0 |
11 |
|
|
|
|
|
|
|
|
|
|
|
12 |
|
|
|
|
|
|
|
|
|
|
|
13 |
|
|
|
По дням недели | |||||||
14 |
|
Всего |
30 |
|
22 |
18 |
18 |
18 |
22 |
26 |
26 |
15 |
|
Требуется |
30 |
|
18 |
12 |
14 |
16 |
16 |
25 |
25 |
16 |
Дневная зарплата сотрудников |
500 |
|
|
|
|
|
|
|
|
|
17 |
Число выходов за неделю |
150 |
|
|
|
|
|
|
|
|
|
18 |
Общая недельная зарплата |
75000 |
|
|
|
|
|
|
|
|
|
Теперь установим указатель мыши на маркер выделения в ячейке Е14, и отбуксируем его до ячейкиК14– и нужные формулы будут распространены на весь выделенный интервал. Осталось ввести данные о необходимом количестве работников в каждый из дней недели, которые будут использованы в качестве данных для ограничений решаемой задачи, формулу для общей заработной платы и необходимые текстовые комментарии, а также выполнить, если это нужно, дополнительное форматирование для окончательного оформления рабочего листа.
4) Введем комментарии в ячейки А16:А18, требуемое число работников в ячейки Е15:K15, дневную зарплату в С16.
В ячейку С17введем число выходов за неделю: =СУММ(Е14:К14)(=SUM(Е14:К14)).
В С18поместим формулу для вычисления недельной зарплаты работников
=С16*С17.