Задача 8. Поиск решения

"На фирме могут работать директор, главный бухгалтер, ведущие инженеры и специалисты.

Известно, что директора и главного бухгалтера может быть только по 1 человеку, ведущих инженеров может быть не более 5, но один – обязательно, специалистов – не более 10 и не менее 2 человек.

Зарплата каждого сотрудника зависит от разряда. Таблица разрядов и окладов задана. Известно, что директор тарифицируется по 9 разряду, главный бухгалтер – по 8 разряду, ведущий инженер – по 7 разряду, специалист – по 5 разряду.

Требуется определить количество сотрудников фирмы так, чтобы суммарная заработная плата не превысила 13000 рублей."

Для решения создадим таблицу с начальными данными (Рис. 108.):

Рис. 108. Проект таблицы с начальными данными

Количество ставок по должностям в начале принимает значение 0.

Зарплата каждого сотрудника зависит от разряда и количества ставок. Поэтому в ячейку I3 занесем формулу:

=ПРОСМОТР(H3;$B$3:$B$11;$C$3:$C$11)*G3

В остальные ячейки столбца "Зар.плата" скопируем эту формулу.

В ячейку G7 занесем формулу суммирования:

=СУММ(G3:G6)

В ячейку I8 занесем формулу:

=СУММ(I3:I6)

Теперь, используя специальную возможность Excel "Поиск решения", найдем решение задачи.

Зададим целевую ячейку I8 – общая сумма зарплат. Можно задать ее значение как максимальное, как минимальное или равное определенному значению. В нашей задаче общая сумма зарплат не должна превышать 13000, поэтому выберем переключатель "максимальное значение" и зададим условие отбора $I$8<=13000

Изменять надо ячейки, содержащие количество ставок, -G3:G6.

Зададим ограничения, используя кнопку "Добавить" (Рис. 109.).

Все значения ячеек G3:G6 – должны быть целочисленными:

Рис. 109. Окно запроса для добавления ограничений

В ячейках G3 и G4 должно быть число 1.

Значение в ячейке G5 должно быть больше и равным 1 и меньшим или равным 5. Значение в ячейке G6 должно быть больше и равным 2 и меньшим или равным 10. На рис. 110 приведено окно запроса "Поиск решения" с установленными параметрами задачи.

Рис. 110. Параметры задачи

Чтобы получить результат, достаточно нажать кнопку "Выполнить":. После чего Excel, используя алгоритм перебора, подбирает значения в изменяемых ячейках так, чтобы в целевой ячейке было максимальное значение. Если решение будет найдено (Рис. 111.), то надо сохранить найденное решение и Excel выдаст результат поиска (Рис. 112.)

Рис. 111. Решение найдено

Рис. 112. Результат решения задачи