Как видим, в строке “ Всего ” удовлетворяются требования строки “ Требуется ” с избытком, т.е. план явно не оптимальный. Следует улучшить этот план, имея в виду нахождение минимума для целевой функции – фонда недельной зарплаты, которая считается следующим образом:
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 |
| В С Е Г О: | ||||||||
| ТРЕБУЕТСЯ |
Разработка электронной таблицы
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 | |
| График работы персонала фирмы | |||||||||||
| Имеющих этот график | |||||||||||
| Выходные дни | Пн | Вт | Ср | Чт | Пт | Сб | Вс | ||||
| Понедельник, вторник | |||||||||||
| Вторник, среда | |||||||||||
| Среда, четверг | |||||||||||
| Четверг, пятница | |||||||||||
| Пятница, суббота | |||||||||||
| Суббота, воскресенье | |||||||||||
| Воскресенье понедельник | |||||||||||
| По дням недели | |||||||||||
| Всего | |||||||||||
| Требуется | |||||||||||
| Дневная зарплата сотрудников | |||||||||||
| Число выходов за неделю | |||||||||||
| Общая недельная зарплата |
|
|
Теперь установим указатель мыши на маркер выделения в ячейке Е14, и отбуксируем его до ячейки К14 – и нужные формулы будут распространены на весь выделенный интервал. Осталось ввести данные о необходимом количестве работников в каждый из дней недели, которые будут использованы в качестве данных для ограничений решаемой задачи, формулу для общей заработной платы и необходимые текстовые комментарии, а также выполнить, если это нужно, дополнительное форматирование для окончательного оформления рабочего листа.
4) Введем комментарии в ячейки А16:А18, требуемое число работников в ячейки Е15:K15, дневную зарплату в С16.
В ячейку С17 введем число выходов за неделю: =СУММ(Е14:К14) (=SUM(Е14:К14)).
В С18 поместим формулу для вычисления недельной зарплаты работников
=С16*С17.
Оптимизация решения
Исходные данные подготовлены. Перейдем теперь к формулированию задачи для режима Поиск решения. В условиях нашей задачи целевой функцией будет функция в ячейке С18 (общая недельная зарплата), изменяемыми ячейками – интервал С4:С10, содержащий число сотрудников, имеющих данный график работы, и наконец, интервал Е14:К15 будет использоваться при определении ограничений.
|
|
Приступим к запуску режима Поиск решения, для этого введем команду:
· Сервис Þ Поиск решения (появится окно Поиск Решения (см. рис. 9).
1.1. Определим параметры окна Поиск решения для Excel Microsoft Office (для программы Calc установки проводятся аналогично):
· в поле Установить целевую ячейку (Целевая функция) ввести: =$C$18
· равной (Цель) установить минимальному значению(Минимум).
· в поле Изменяя ячейки (Параметры функции) ввести: =$C$4:$C$10 (можно использовать выделение данного интервалов мышью).
1.2. Далее приступим к вводу ограничений:
а) щелкнуть по кнопке Добавить (Ограничения значений параметра), появится окно Изменение ограничения (рис. 10);
б) в окне Изменение ограничения
· ввести ссылку на ячейки: $C$4:$C$10,
· выбрать пункт Ограничения цел,
· щелкнуть по кнопке Добавить (кнопка Настройки Þ установить флажок Только положительные значения), т.е. получаем ограничение С4:С10 целые (так как в ячейках С4:С10 хранится информация о количестве работающих, эти величины не могут быть дробными)
в) ссылка на ячейки: =$Е$14:$К$14;
· выбрать ограничение >=,
· в правой части выбрать Ограничение:=$E15:$K$15,
· щелкнуть по кнопке Добавить, т.е. получаем ограничение Е14:К14 ³ Е15:К15. (Число работающих по нашему графику не может быть меньше требуемого условием задачи).
г) ссылка на ячейки С4:С10
· выбрать ограничение >=
· в правой части ввести Ограничение: 0
(т.е. ввели ограничение С4:С10 ³ 0 – число работающих неотрицательно).
· щелкнуть по кнопке ОК, т.к ввод ограничений завершен. Происходит возврат к окну Поиск решения (рис. 9).
Окно Поиск решения в Excel Microsoft Office.
Окно Поиск решения в OpenOffice.org Calc
Рис. 9.
Excel Microsoft Office OpenOffice.org Calc
Рис. 10
1.3. Запускаем программу Поиск решения на выполнение
· в окне Поиск решения щелкнуть по кнопке Выполнить (Решить).
Дополнение Поиск решения приступает к итерациям. Затем, закончив вычисления, открывает диалоговое окно Результаты поиска решения (рис. 11), в котором выводится сообщение о том, найдено или нет решение поставленной задачи. Если найденное решение вас устраивает, можете сохранить его на рабочем листе:
· выбрать опцию Сохранить найденное решение, (Ок)
· щелкнуть по кнопке ОК.
 |
Excel Microsoft Office
OpenOffice.org Calc
Рис. 11
В результате получаем табл. 38, из которой видно, что теперь число работников 26 (вместо 30), следовательно, фонд зарплаты 6500 руб. (вместо 7500 руб.).
3.2.4. Задача планирования выпуска продукции
Предприятие выпускает два вида деталей. Оно закупает заготовки, подвергаемые обработке на станках трех типов S 1, S 2, S 3. Данные, характеризующие производительность станочного парка, а также стоимостные характеристики, приведены в табл. 39.
Предполагая, что можно выпустить любую комбинацию деталей А и В, найти план выпуска продукции, максимизирующий прибыль.
Решение задачи включает все этапы, описанные в пп. 3.2.2 и 3.2.3.
Таблица 38
| A | B | C | D | E | F | G | H | I | J | K | |||
| График работы персонала фирмы | |||||||||||||
| график | |||||||||||||
| Выходные дни | Пн. | Вт. | Ср | Чт | Пт | Сб | Вс | ||||||
| Понедельник, вторник | |||||||||||||
| Вторник, среда | |||||||||||||
| Среда, четверг | |||||||||||||
| Четверг, пятница | |||||||||||||
| Пятница, суббота | |||||||||||||
| Суббота, воскресенье | |||||||||||||
| Воскресенье, понедельник | |||||||||||||
| По дням недели | |||||||||||||
| Всего | |||||||||||||
| Требуется | |||||||||||||
| Дневная зарплата сотрудников | Пн | Вт | Ср | Чт | Пт | Сб | Вс | ||||||
| Число выходов за неделю | |||||||||||||
| Общая недельная зарплата | |||||||||||||
Таблица 39
| Станки | Производительность станков (шт/час) | ||
| Вид | Стоимость часа работы (у.е) | Деталь А | Деталь В |
| S1 | |||
| S2 | |||
| S3 | |||
| Стоимость одной заготовки (у.е) | |||
| Продажная цена одной детали (у.е) | 59,5 | 79,89 |