Информационная технология поиска решения

Для реализации оптимизационных расчетов в Microsoft Excel используется надстройка Поиск решения. Если она не установлена – т.е. в меню Сервис отсутствует строка Поиск решения, то с помощью команды Сервис ► Надстройки следует установить соответствующий фла­жок в перечне надстроек MS Excel. Кроме того, следует правильно подготовить данные оптимизационной модели в таблице MS Excel. Модель оптимизационной задачи задается в диалоговом окне Поиск решения:

Модель использует целевую функцию, которая записывается в виде формулы в отдельной ячейке. Для целевой функции указывается: максимизация, минимизация или равенство фиксированному значению. В процессе поиска решения изменяются значения в указанных ячейках, соответствующих переменным при соблюдении ограничений. Дополнительные настройки оптимизации задаются в диалоговом окне Параметры поиска решения.

Задание 4. Магазин работает по пятидневному графику с двумя выходными. Необходимое число сотрудников приведено в таб­лице 1:

Таб­лица 1.

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

Последовательность выполнения задания:

1. Создайте новый рабочий лист (Вставка > Лист), дважды щелкните на его ярлыке и присвойте ему имя Заработная плата. Создайте таблицу по образцу:

2. В 1-ю строку рабочего листа введите заголовки столбцов: в ячейку А1 — Выходные дни, в ячейку С1 — Работники, в ячейки E1-K1 — дни недели (Пн,Вт,Ср,Чт,Пт,Сб,Вс).

3. В ячейки А2-А8 введите разрешенные пары выходных (от Пн,Вт до Вс,Пн). В ячейке А10 укажите заголовок Штат сотрудников. Ячейка А14 должна содержать фразу Дневная оплата работника, а ячейка А15 — текст Общая недельная зарплата.

4. В ячейке В12 напишите Требуется ежедневно. Введите в ячейках E12-K12 требования к минимальному числу работников согласно заданной таблице 1. В ячейку C14 введите фиксированную дневную оплату — 300 рублей.

5. В ячейках диапазона E2:K8 укажите 1, если для данного расписания день явля­ется рабочим, и 0 — если выходным. В ячейки С2-С8 введите нулевые значения. В дальнейшем эти значения будут вычислены автоматически в результате решения оптимизационной задачи.

6. В ячейку E10 введите следующую формулу:

=$C$2*E2+$C$3*E3+$C$4*E4+$C$5*E5+$C$6*E6+$C$7*E7+$C$8*E8

По ней рассчитывается число сотрудников, занятых в понедельник. Элементы абсолютной адресации исполь­зованы для того, чтобы формулу можно было копировать.

7. Методом заполнения скопируйте формулу в ячейки диа­пазона F10-K10.

8. В ячейку C10 введите формулу для расчета общего количества сотрудников: =СУММ(C2:C8). В ячейку C15 введите формулу для исчисления итоговых расходов на заработ­ную плату за пятидневную рабочую неделю: = C10*C14*5. Именно это значение необходимо свести к мини­муму.

9. Запустите надстройку Поиск решения (Сервис > Поиск решения).

10. В поле Установить целевую ячейку выберите ячейку C15.

11. Для переключателя Равной выберите вариант минимальному значению.

12. Щелкните на поле Ограничения и затем — на кнопке Добавить. Введите ограничения, показанные на рис.1. Данные ограничения необходимы для получения положительного и целочисленного результата – количества сотрудников, работающих в тот или иной день недели, которое должно быть больше (или равно) требуемого значения.

13. Щелкните на кнопке Выполнить, чтобы провести поиск оптимального варианта:

Результаты расчетов показывают, что штат сотрудников магазина должен быть 10 человек, и все они должны иметь выходные в те дни, когда магазин не работает.