Ход работы
Для того чтобы составить модель задачи, запустите редактор электронных таблиц MS EXCEL и введите исходные данные как показано на рис. 6.1. Рабочий лист обязательно должен содержать целевую ячейку и изменяемые ячейки (на рис. 6.1 они выделены серым цветом). В целевой ячейке должна содержаться формула, значение которой зависит от значений в изменяемых ячейках.
Так как значения в ячейках (В8:В10) неизвестны, в эти ячейки нужно ввести любое разумное значение, потому что в процессе работы процедуры «Поиск решения», эти значения будут изменяться и после успешного выполнения поиска, в этих ячейках будет отражен ответ на поставленный вопрос к задаче.
Ячейка С8 рассчитывается по формуле =С2*В8, ячейки С9, С10 рассчитываются аналогично.
Ячейка D8 рассчитывается по формуле =B2*В8, ячейки D9, D10 рассчитываются аналогично.
Ячейка E8 рассчитывается по формуле =E2*В8, ячейки С9, С10 рассчитываются аналогично.
Рис. 6.1. Исходные данные для задачи 1
2. После того, как подготовлен лист с исходными данными, в меню Сервис «вызовите» надстройку «Поиск решения». Если эта надстройка установлена, появится окно (рис. 6.2), если нет – следует выполнить команду Сервис → Надстройки… и выставить флажок против надстройки «Поиск решения».
3. После того, как появится окно «Поиск решения», необходимо последовательно его «заполнить», т. е. задать все условия для решения задачи.
Для этого в поле Установить целевую ячейкуукажите ячейку (Е11), содержащую формулу для расчета суммарного дохода от производства.
В поле Равнойвыбираем переключательМаксимальному значению, так как по условию задачи необходимо составить план, который позволит получать максимальную прибыль.
Рис. 6.2. Окно «Поиск решения»
В поле Изменяя ячейкиуказываем адреса ячеек, значения которых будут меняться в процессе поиска решения, т. е. в нашей задаче – это ячейки В8:В10.
Заполняя поле Ограничения, необходимо учесть все ограничения данные в условии задачи.
Для того чтобы добавить ограничения нажмите кнопку Добавитьи введите необходимое ограничение в появившемся окне «Добавление ограничений» (рис. 6.3).
Рис. 6.3. Окно «Добавление ограничений»
После того, как ограничение введено, нажмите кнопку ОК, оно отобразится в поле Ограничения. Если в задаче не одно ограничение, тогда, после ввода первого ограничения, в окне «Добавление ограничений» нажмите кнопку Добавить и продолжайте вводить следующее ограничение, при этом все ограничения отразятся в поле Ограничения окна «Поиск решения».
Например, введем первое ограничение (рис. 6.4) – время работы не должно превышать 40 часов в неделю. В поле Ссылка на ячейкуукажите ячейку (D11), затем в раскрывающемся списке операторов выберите оператор меньше или равно (<=), в полеОграничениевведите значение 40.
Рис. 6.4. Добавление ограничений
Все остальные ограничения введите также.
Если при вводе ограничений была допущена ошибка, выделите это ограничение и щелкните по кнопке Изменить. Откроется окно «Изменение ограничения» (рис. 6.5), оно аналогично окну «Добавление ограничений». После исправления ошибок щелкните по кнопке ОК, чтобы вернуться в окно «Поиск решения».
Рис. 6.5. Окно «Изменение ограничений»
Чтобы удалить ограничение щелкните по кнопке Удалить.
Для ввода параметров щелкните кнопку Параметрыв окне «Поиск решения». Параметры, установленные по умолчанию в окне «Параметры поиска решения», подходят для большинства задач, а для данной задачи необходимо поставить две галочки, напротив строкЛинейная модель и Неотрицательные значения(рис. 6.6). Остальные параметры не нуждаются в корректировке.
Для того чтобы выполнить поиск решения, щелкните кнопку «Выполнить», через некоторое время на экране появится диалоговое окно «Результаты поиска» решения (рис. 6.7), в котором вы сможете выполнить следующие действия:
– сохранить найденное решение. Для этого щелкните на кнопке ОК. При этом исходные значения в целевой и изменяемых ячейках заменяются новыми значениями, найденными в процессе поиска решения;
– создать отчет о процедуре поиска решения. Выберите один или несколько отчетов в списке Тип отчета и щелкните на кнопке ОК. Каждый отчет будет помещен на новый рабочий лист с соответствующим именем;
– сохранить решение в виде сценария (кнопка Сохранить сценарий). Этот сценарий затем может быть использован в средствеДиспетчер сценариев;
– восстановить исходные значения в целевой и изменяемых ячейках. Если найденное решение вас не удовлетворяет, щелкните на кнопке Отмена.
Рис. 6.6. Окно ввода параметров поиска решения
Рис. 6.7. Окно «Результаты поиска решения»
В данной работе необходимо Сохранить найденное решение. В случае, если решение не может быть найдено, необходимо проверить введенные исходные данные и условия в окне «Поиск решения», исправить если есть необходимость и проделать процедуру поиска снова.
Результат работы представлен на рис. 6.8. Из него видно, что при производстве изделия А в количестве 2 шт., изделия В в количестве 30 шт., изделия С в количестве 30 шт., а максимальный доход при этом будет составлять 625 руб.
4. Следующим шагом является анализ полученного решения. Сделайте вывод по полученным результатам и напишите его на листе в вашей книге EXCEL.
Рис. 6.8. Результат работы
Задача 2.
Пусть известно, что для нормальной работы компании требуется:
5–6 программистов;
3–5 менеджеров проектов;
1 аналитик бизнес-процессов;
2 функциональных аналитика;
1 инженер по технической документации;
2 юриста;
1 бухгалтер;
2–5 начальников отделов;
1 технический директор;
1 генеральный директор.
Общий месячный фонд заработной платы должен быть минимальным. Необходимо определить, какими должны быть оклады сотрудников компании, при условии, что оклад программиста не должен быть меньше 3000 руб. Следует также учитывать что заработная плата рассчитывается по формуле: ЗП = Аi×х+Вi, где х – оклад программиста, Аi – во сколько раз превышается значение х; Вi– на сколько превышается значение х.