Лаб раб Оптимизация штатного расписания
.pdf
Рисунок 14 - Отчет по результатам
Рисунок 15 - Отчет по пределам
Сценарий - это набор значений, которые сохраняются и могут автоматически подставляться в рабочий лист. Существует возможность создать и сохранить в листе различные группы значений в виде сценариев, а затем переключаться на любой из них, чтобы просматривать
|
различные результаты. Если у не- |
|
|
|
скольких пользователей есть оп- |
|
ределенные данные, которые не- |
|
обходимо использовать в сцена- |
|
риях, то можно собрать эти дан- |
Рисунок 16 - До Сохранение |
ные в отдельные книги и объеди- |
сценария |
нить сценарии из нескольких книг |
в одной. После создания всех необходимых сценариев можно создать сводный отчет по ним, включающий сведения из всех сценариев.
Для просмотра промежуточных результатов Поиска решения надо в ДО Параметры поиска решения (рис. 10) установить флажок Показывать результаты итераций - 
, а затем нажать кнопку ОК. Затем в ДО Поиск решения нажать кнопку Выполнить. За-
20
тем в ДО Текущее состояние поиска решения выполнить одно из следующих действий:
-чтобы остановить поиск решения и вывести на экран ДО Результаты поиска решения, следует нажать кнопку Стоп;
-чтобы продолжить процесс поиска решения и просмотреть его результаты, нажать кнопку Продолжить.
Для изменения способа поиска решения в случае неудачи в ДО Поиск решения нажать кнопку Параметры, изменить настройки и метод поиска.
Для получения дополнительных сведений следует нажать в ДО
Параметры поиска решения кнопку Справка.
Для сохранения или загрузки модели оптимизации надо выполнить следующие действия:
-нажать кнопку Параметры;
-нажать кнопку Сохранить модель (или Загрузить модель).
При сохранении модели ввести ссылку на верхнюю ячейку столбца, в котором следует разместить модель оптимизации. При сохранении модели введите ссылку на весь диапазон ячеек, содержащий модель оптимизации.
Чтобы использовать на листе несколько моделей оптимизации, нужно сохранить их с помощью кнопки Сохранить модель.
3.Технология решения задачи §Оптимизация штатного расписания фирмы¨
3.1.Постановка задачи
Задан фонд заработной платы (ФЗП). Определить сколько сотрудников, на каких должностях и с каким окладом нужно принять на работу, если ФЗП составляет 5600000 руб. в месяц.
При расчетах используется линейная модель, описывающая зависимость величины заработной платы сотрудника в виде:
f ( C ) a Z b , где Z - оклад специалиста, являющийся минимальным, a - коэффициент, определяющий во сколько, b - коэффициент, определяющий на сколько, зарплата других сотрудников больше оклада специалиста.
21
3.2.Решение задачи с помощью инструмента Поиск решения
1.В поле Установить целевую ячейку ввести ссылку на ячейку $F$13 (рис. 17), в которой хранится сумма зарплат всех сотрудников фирмы.
Рисунок 17 - До Поиск решения с заполненными полями
2.Для целевой ячейки задать конкретное значение: установить переключатель в положение 
значению и ввести рядом в поле число 5600000.
3.В поле Изменяя ячейки ввести ссылку на ячейку $B$12, в которой хранится зарплата помощника менеджера. Через это значение вычисляются в соответствии с моделью величины заработной платы других сотрудников.
3.В поле Ограничения ввести необходимые ограничения согласно
|
рис. 18. Для добавления ог- |
||
|
|||
|
раничения в группе Огра- |
||
|
ничения ДО Поиск решения |
||
|
(рис. |
17) нажать |
кнопку |
|
Добавить. Открывается ДО |
||
|
- рис. 18. |
|
|
Рисунок 18 - Добавление ограничения на |
В |
поле Ссылка на |
|
ячейку |
ячейку |
необходимо |
ввести |
|
ссылку на ячейку согласно |
||
|
|||
рис. 18, на значения которой накладывается ограничения. Затем выбрать в раскрывающемся списке условный оператор ž>=£. В поле Ог- 22
раничение ввести число ноль, так как зарплата - величина положительная. Затем нажать кнопку Добавить. Затем ввести ограничения на диапазон ячеек согласно рис. 19. Чтобы принять ограничение и вернуться в ДО Поиск решения, нажать кнопку ОК.
Рисунок 19 - Добавление ограничения на диапазон ячеек
4.Нажать кнопку Выполнить. Получим результат - рис. 20.
Рисунок 20 - Рабочий лист с результатами решения задачи
|
Чтобы увидеть рабо- |
||
|
чий лист в |
режиме |
|
|
отображения формул, |
||
|
надо открыть ДО ок- |
||
|
но Параметры Excel |
||
|
(рис. |
7), |
выбрать |
|
вкладку |
Дополни- |
|
Рисунок 21 - Вкладка Дополнительно |
тельно |
(рис. |
21) и |
|
|
|
|
23
включить флажок Показывать формулы, а не их значения. Рабочий лист в режиме отображения формул приведен на рис. 22.
Рисунок 22 - Рабочий лист в режиме отображения формул
3.3. Решение задачи с помощью инструмента Подбор параметра
1. На вкладке Данные в группе Средства обработки данных выбрать команду Анализ ™что-еслиš, Подбор параметра (рис. 2). Открывается соответствующее ДО - рис. 23. В поле Установить в ячейке надо ввести ссылку на ячейку, содержащую формулу, параметры которой требуется подобрать. В данном примере это ячейка F13. Затем ввести искомый результат формулы в поле Значение число ž5600000£ - величину ФЗП.
2. В поле Изменяя значение ячейки ввести ссылку на ячейку, значение которой нужно подобрать – это ячейка $B$12. Формула в ячейке, указанной в поле Установить в ячейке, должна ссылаться на эту ячейку. На-
жать кнопку ОК.
Рисунок 23 - До Подбор параметра с заполненными полями Средство Подбор параметра
проанализирует данные и выдаст результат - рис. 24. Для сохранения результата - нажать кнопку ОК.
24
Рисунок 24 - ДО Результат подбора параметра
4. Задание для выполнения лабораторной работы
Задание. Сформировать штатное расписание своей фирмы с учетом лимита ФЗП. Использовать указанную модель (Z - наименьшая зарплата работника, a и b - соответствующие коэффициенты). Варианты заданий приведены в табл. 6. Пример оформления рабочего листа с результатами решения задачи приведен в Приложении.
Таблица 6 - Варианты заданий
№ вари- |
ФЗП, руб. |
Количество |
Модель |
анта |
|
сотрудников |
|
1. |
5600000 |
5 |
f ( C ) a Z b |
2. |
6000000 |
10 |
f ( C ) a Z |
3. |
7000000 |
15 |
f ( C ) a Z b |
4. |
7500000 |
20 |
f ( C ) a Z |
5. |
6500000 |
12 |
f ( C ) a Z b |
6. |
8700000 |
16 |
f ( C ) a Z |
7. |
6800000 |
18 |
f ( C ) a Z b |
8. |
9200000 |
20 |
f ( C ) a Z |
9. |
10000000 |
25 |
f ( C ) a Z b |
10. |
12000000 |
24 |
f ( C ) a Z |
11. |
13000000 |
30 |
f ( C ) a Z b |
12. |
15000000 |
30 |
f ( C ) a Z |
13. |
9000000 |
25 |
f ( C ) a Z b |
14. |
8500000 |
17 |
f ( C ) a Z |
15. |
2500000 |
8 |
f ( C ) a Z b |
16. |
5400000 |
12 |
f ( C ) a Z b |
25
5. Контрольные вопросы
1.Назовите определение формулы.
2.Укажите средства MS Excel для решения оптимизационных за-
дач.
3.Перечислите типы ссылок.
4.Укажите средства финансового анализа типа žчто-если£.
5.Каково назначение финансовых функций?
6.Назовите определение сценария.
7.Для чего нужны сценарии?
8.Каково назначение инструмента Диспетчер сценариев?
9.Чем отличаются функционально инструменты Подбор параметра и Поиск решения?
10.Что такое žоператор£?
11.Каков приоритет выполнения операторов в формуле?
12.Какие виды операторов Вы знаете?
13.Назовите определение функции.
14.Укажите порядок действий при копировании формулы.
15.Укажите порядок действий при перемещении формулы.
16.Как меняется абсолютная ссылка при копировании или перемещении формулы?
17.Как меняется смешанная ссылка при копировании или перемещении формулы?
18.Как меняется относительная ссылка при копировании или перемещении формулы?
19.Укажите порядок действий при использовании инструмента Поиск решения.
20.Укажите порядок действий при использовании инструмента Подбор параметра.
21.Как загрузить надстройку Поиск решения?
22.Перечислите основные параметры инструмента Поиск реше-
ния.
23.Какие методы оптимизации реализованы в Поиске решения?
24.Назовите определение формулы.
25.Для чего используется žмаркер заполнения£.
26.Какие действия надо выполнить, чтобы отобразить рабочий лист в режиме формул?
27.Какие действия надо выполнить, чтобы отобразить рабочий лист в режиме значений?
26
Список рекомендуемой литературы
1.Степанов, А.Н. Информатика. Базовый курс: учеб. пособие для студентов вузов/ А.Н. Степанов . -СПб.: Питер, 2010. -720 с.
2.Информатика. Базовый курс: учеб. пособие для студентов втузов/ под ред. С.В. Симоновича.- СПб.: Питер, 2010. -640 с.
3.Таганов, Л.С. Информатика: учеб. пособие / Л.С. Таганов, А.Г. Пимонов; Кузбас. гос. техн. ун-т. – Кемерово, 2010.-330 с.
4.Колдаев, В. Д. Сборник задач и упражнений по информатике [Текст]: учеб. пособие для студентов учреждений сред. проф. образования / В. Д. Колдаев, Е. Ю. Павлова; под ред. Л. Г. Гагариной. – М. : Форум, 2010. – 256 с.
27
Приложение Пример оформления отчета
28
Составитель Валентина Валентиновна Крюкова
ОПТИМИЗАЦИЯ ШТАТНОГО РАСПИСАНИЯ СРЕДСТВАМИ MS EXCEL (ПОДБОР ПАРАМЕТРА, ПОИСК РЕШЕНИЯ)
Методические указания к лабораторной работе по дисциплине žЭкономическая информатика£ для студентов направления 080100.62 Экономика£, профиль žБухгалтерский учет, анализ и аудит£
очной формы обучения
Печатается в авторской редакции
Рецензент Пимонов А. Г.
Подписано в печать 12.01.2012. Формат 60 84/16. Бумага офсетная. Отпечатано на ризографе. Уч.-изд. л. 1.5. Тираж 81экз. Заказ КузГТУ 650000, Кемерово, ул. Весенняя, 28.
Типография КузГТУ 650000, Кемерово, ул. Д. Бедного, 4 а.
29