- •51. Общие указания
- •Библиографический список
- •Разработка систем принятия решений
- •Краткие сведения о системах принятия решения (экспертных системах)
- •Разработка системы принятия решения об аттестации знаний абитуриента
- •2.4. Разработка системы принятия решений о диагностике неисправности телевизора
- •Оптимизация управленческих и экономических задач
- •Принципы решения задач оптимизации
- •Решение транспортной задачи
- •Подготовим блок ячеек с исходными данными.
- •Построим начальный план перевозок.
- •Вычислим количество перевозимой продукции
- •Определим стоимость перевозок в каждый из магазинов
- •Определим общую стоимость перевозок (целевую функцию цф)
- •Решение задачи о штате фирмы
- •Определение возможного графика работы
- •Согласно данному графику
- •Определение целевой функции задачи
- •Задача планирования выпуска продукции
- •Определение целевой функции
- •3.7. Задача о производстве красок
- •Определение целевой функции
- •Определение ограничений на переменные
- •4. Проектирование баз данных (бд)
- •Основные понятия
- •Основные этапы проектирования, создания и ведения баз данных
- •4.3. Пример выполнения курсовой работы по проектированию баз данных
- •Курсовая работа по информатике
Определение целевой функции задачи
Как видим, в строке “Всего” удовлетворяются требования строки
“Требуется” с избытком, т.е. план явно не оптимальный. Следует улучшить этот план, имея в виду нахождение минимума для целевой функции - фонда недельной зарплаты, которая считается следующим образом:
F = B K, где F - фонд заработной платы,
В - зарплата работника за день (согласно заданию, В=500 руб.),
K - число выходов сотрудника в течение недели (в нашем случае согласно табл.19 строка “Всего”)
К = 22 + 18 + 18 + 18 + 22 + 26 + 26 =150
Таким образом,
F = B • K = 500 • 150 = 75 000 (руб.)
Разработка электронной таблицы
1) Составим электронную таблицу (табл.20 - показ вычислений, табл.21 - показ формул), реализующую планирование списочного состава и графика работы фирмы.
Введем на рабочий лист необходимые исходные данные согласно табл. 19
ячейки А1:К10.
Теперь можно приступить к вводу формул, описывающих количество работающих сотрудников. Поместим в ячейку С14 общее количество сотрудников, работающих по данному графику, а в интервал ячеек Е14:К14 - количество работающих сотрудников в соответствующий день недели. Для этого в ячейку С14 нужно поместить формулу суммирования значений интервала С4:С10, =СУММ(С4:С10), а в ячейках интервала Е14:К14 следует просуммировать произведения ячеек из столбца С на значения соответствующего столбца из интервала Е: К, заполненного нулями и единицами.
Для этого:
щелкнуть по кнопке Мастер функции, выбрать функцию СУММПРОИЗВ. В первом окне набрать $C$4:$C$10, во втором Е4:Е10;
щелкнуть по кнопке ОК.
Дадим здесь некоторые пояснения. Поскольку мы собираемся распространить формулу из ячейки Е14 в другие ячейки интервала F14: К14, но при этом постоянно должны ссылаться на интервал С4:00, необходимо, чтобы эта ссылка была абсолютной (используем знак абсолютного адреса $ для ячеек С4:С10, получаем $С$4: $С$10).
Теперь установим указатель мыши на маркер выделения в ячейке Е14, и отбуксируем его до ячейки К14 - и нужные формулы будут распространены на весь выделенный интервал. Осталось ввести данные о необходимом количестве работников в каждый из дней недели, которые будут использованы в качестве данных для ограничений решаемой задачи, формулу для общей заработной платы и необходимые текстовые комментарии, а также выполнить, если это нужно, дополнительное форматирование для окончательного оформления рабочего листа.
Введем комментарии в ячейки А16:А18, требуемое число работников в ячейки Е15:К15, дневную зарплату в С16.
В ячейку С17 введем число выходов за неделю: =СУММ(Е14К14).
В С18 поместим формулу для вычисления недельной зарплаты работников =С16*С17.
Таблица
20 |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
К |
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 |
|
|
|
|
|
|
|
|
|
Таблица
21 |
А |
Б |
с |
D |
Е |
Г |£ и I J I К |
|||||||
1 |
График районы персонала федомы |
||||||||||||
2 |
|
|
имею-щих этот график |
|
|
|
|
|
|
|
|
||
з |
Bi |
|
|
|
Пн. |
Вт. |
-F |
Чт |
Пт |
DG |
Вс |
||
л |
П( |
|
6 |
|
0 |
0 |
1 |
1 |
1 |
1 |
1 |
||
S |
в- |
|
6 |
|
1 |
0 |
0 |
1 |
1 |
1 |
1 |
||
Ё |
С| |
|
6 |
|
1 |
1 |
0 |
0 |
1 |
1 |
1 |
||
7 |
4l |
|
6 |
|
1 |
1 |
1 |
0 |
0 |
1 |
1 |
||
9 |
П) |
|
2 |
|
1 |
1 |
1 |
1 |
0 |
0 |
1 |
||
9 |
Cl |
|
2 |
|
1 |
1 |
1 |
1 |
1 |
0 |
0 |
||
10 |
Bi |
|
2 |
|
0 |
1 |
1 |
1 |
1 |
1 |
0 |
||
11 |
|
|
|
|
|
|
|
|
|
|
|
||
12 |
|
|
|
|
|
|
|
|
|
|
|
||
13 |
|
|
|
По д: |
1ням недели |
||||||||
и |
|
се |
=CVMM(C4:C10) |
=СШ М ПРО ИЗ всю® 4: $0$10;Е4: Е10) |
=CWMnPOH3B($0$4:$C$10;F4:F10) |
= |
=( |
=0 |
=( |
=С |
|||
15 |
|
зу |
|
|
16 |
12 |
1- |
16 |
16 |
2£ |
25 |
||
16 |
Дне |
500 |
|
|
|
|
|
|
|
|
|||
17 |
ло вых |
=СУМ М (F14: К14) |
Пн |
Вт |
3 |
Чт |
Пт |
DG |
Вс |
||||
13 |
о 1 |
=016*017 |
|
|
|
|
|
|
Оптимизация решения
Исходные данные подготовлены. Перейдем теперь к формулированию задачи для режима Поиск решения. В условиях нашей задачи целевой функцией будет функция в ячейке С18 (общая недельная зарплата),
изменяемыми ячейками - интервал С4:С10, содержащий число сотрудников, имеющих данный график работы, и наконец, интервал Е14:К15 будет использоваться при определении ограничений.
Приступим к запуску режима Поиск решения, для этого введем команду:
Сервис^Поиск решения (появится окно Поиск Решения (см. рис. 6)
Определим параметры окна Поиск решения
в поле установить целевую ячейку ввести: =$C$18
равной установить минимальному значению.
в поле Изменяя ячейки ввести: =$C$4:$C$10 (можно использовать выделение данного интервалов мышью).
Далее приступим к вводу ограничений:
а) щелкнуть по кнопке Добавить, появится окно Изменение ограничения (рис. 7);
б) в окне Изменение ограничения
ввести ссылку на ячейки: $C$4:$C$10,
выбрать пункт Ограничения цел,
щелкнуть по кнопке Добавить, т.е. получаем ограничение С4:С10 целые (так как в ячейках С4:С10 хранится информация о количестве работающих, эти величины не могут быть дробными)
в) ссылка на ячейки: =$Е$14:$К$14;
выбрать ограничение >=,
в правой части выбрать Ограничение:=$Е15:$К$15,
щелкнуть по кнопке Добавить, т.е. получаем ограничение Е14:К14 > Е15:К15. (Число работающих по нашему графику не может быть меньше требуемого условием задачи).
г) ссылка на ячейки С4:С10
выбрать ограничение >=
в правой части ввести Ограничение: 0
(т.е. ввели ограничение С4:С10 > 0 - число работающих неотрицательно).
щелкнуть по кнопке ОК, т.к ввод ограничений завершен. Происходит возврат к окну Поиск решения (рис.6)
.Установить
целевую ячейку:
Равной: Г"
максимальному значению
_?jxj ]
Поиск решения
Выполнить
: [Т
значению
Закрыть
$С|4:|С|Ю = целое $Сф4: 10 >= □ |
|
Добавить |
$Е|14:$К|14 >= |Е|15:|К|15 |
|
Изменить Удалить |
|$С|4:|С|10
-Ограничения:
^1 Предположить |
Параметры
J
Восстановить
Справка
Рис.
6
Рис. 7
Изменение ограничени
я
Ссылка на ячейку: |$С$4:$С$10
Ограничение:
3] |г
3J Р"
ок
Отмена
Справка
Добавить
В
результате получаем табл. 22, из которой
видно, что теперь число работников
26 (вместо 30), следовательно, фонд
зарплаты 6500 руб. (вместо 7500).
Таблица
22 |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
1 |
График работы персонала фирмы |
||||||||||
2 |
|
|
график |
|
|
|
|
|
|
|
|
3 |
Выходные дни |
|
|
|
Пн. |
Вт. |
Ср |
Чт |
Пт |
Сб |
Вс |
4 |
Понедельник, вторник |
|
5 |
|
0 |
0 |
1 |
1 |
1 |
1 |
1 |
5 |
Вторник, среда |
|
9 |
|
1 |
0 |
0 |
1 |
1 |
1 |
1 |
6 |
Среда, четверг |
|
1 |
|
1 |
1 |
0 |
0 |
1 |
1 |
1 |
7 |
Четверг, пятница |
|
9 |
|
1 |
1 |
1 |
0 |
0 |
1 |
1 |
8 |
Пятница, суббота |
|
1 |
|
1 |
1 |
1 |
1 |
0 |
0 |
1 |
9 |
Суббота, воскресенье |
|
0 |
|
1 |
1 |
1 |
1 |
1 |
0 |
0 |
10 |
Воскресенье, понедельник |
|
1 |
|
0 |
1 |
1 |
1 |
1 |
1 |
0 |
11 |
|
|
|
|
|
|
|
|
|
|
|
12 |
|
|
|
|
|
|
|
|
|
|
|
13 |
|
|
|
По дням недели |
|||||||
14 |
|
Всего |
26 |
|
20 |
12 |
16 |
16 |
16 |
25 |
25 |
15 |
|
Требуется |
|
|
16 |
12 |
14 |
16 |
16 |
25 |
25 |
16 |
|
|
|
|
|
|
|
|
|
|
|
17 |
Дневная зарплата сотрудников |
500 |
|
|
Пн |
Вт |
Ср |
Чт |
Пт |
Сб |
Вс |
18 |
Число выходов за неделю |
124 |
|
|
|
|
|
|
|
|
|
19 |
Общая недельная зарплата |
65000 |
|
|
|
|
|
|
|
|
|