На этом шаге мы рассмотрим процедуру поиска решения.
В Excel предусмотрен мощный инструмент - Поиск решения, который позволяет расширить процедуру подбора параметра следующим образом:
- Указывать несколько изменяемых ячеек.
- Указывать ограничения на значения изменяемых ячеек.
- Находить решение, при котором значение в определенной ячейке рабочего листа достигает максимума или минимума.
- Получить несколько решений задачи.
Задачи, выполняемые с использованием процедуры поиска решения, относятся к сравнительно узкой области. Обычно они затрагивают случаи, удовлетворяющие следующим критериям:
- Значение в целевой ячейке зависит от других значений в ячейках и результатов расчета по формулам. Нужно определить все исходные параметры, при которых значение в целевой ячейке будет максимальным или заранее определенным.
- Целевая ячейка зависит от группы ячеек, которые называются изменяемыми ячейками. Их значения надо подобрать так, чтобы получить желаемый результат в целевой ячейке.
- Решение (значение изменяемых ячеек) должно находиться в определенных пределах или удовлетворять определенным ограничениям.
После соответствующей подготовки рабочего листа можно использовать процедуру поиска решения для подбора значений в изменяемых ячейках и получения в целевой ячейке нужного результата, который одновременно удовлетворяет всем установленным Вами ограничениям.
Знакомство с процедурой поиска решения начнем с простого примера. На рис. 1 показан рабочий лист, с помощью которого можно вычислить прибыль от продажи трех видов продукции. В столбце В отображено количество товара по каждому изделию, в столбце С - доход от продажи одной единицы изделия. В столбце D содержатся формулы, по которым вычисляется доход от продажи каждого вида товара путем умножения их количества на прибыль от продажи одной единицы изделия.
Рис. 1. Рабочий лист, иллюстрирующий пример расчета количества изделий, приносящих максимальную прибыль
У компании есть несколько ограничений, которые она должна учитывать:
- Общий объем производства - всего 300 единиц изделий в день.
- Компании нужно произвести 50 единиц изделия А для выполнения существующего заказа.
- Компании нужно произвести 40 единиц изделия В для выполнения планового заказа.
- Поскольку сбыт изделий С относительно небольшой, то должно быть изготовлено не больше 40 единиц этого изделия.
Процедура поиска решения выполняется следующим образом.
- Введите в рабочий лист исходные данные и формулы (рис. 1).
- Вызовите диалоговое окно Поиск решения.
- Укажите целевую ячейку.
- Укажите изменяемые ячейки.
- Задайте ограничения.
- При необходимости измените опции процедуры поиска решения.
- Позвольте процедуре поиска решения выполнить поставленную задачу.
Чтобы запуcтить процедуру поиска решения, выберите команду Сервис | Поиск решения.
Примечание. Если в пункте меню Сервис отсутствует опция Поиск решения, нужно выполнить команду Сервис | Надстройки и в открывшемся диалоговом окне Надстройки установить галочку рядом с опцией Поиск решения.
В результате выполнения указанных действий появится диалоговое окно Поиск решения (рис. 2).
Рис. 2. Диалоговое окно Поиск решения
В этом примере в целевой ячейке D6 вычисляется общая прибыль по трем видам изделий. В поле Установить целевую ячейку укажите адрес ячейки D6 или щелкните в рабочем листе на этой ячейке. Поскольку наша цель - максимизировать значение в этой ячейке, установите переключатель Равной максимальному значению. Затем определите изменяемые ячейки, которые в данном случае находятся в диапазоне В3:В5.
Дальше введите ограничения задачи. Ограничения добавляются по одному за один раз и отображаются в окне Ограничения. Для добавления ограничений щелкните на кнопке Добавить. Появится диалоговое окно Добавление ограничения (рис. 3).
Рис. 3. Диалоговое окно Добавление ограничения
В этом диалоговом окне нужно ввести ссылку на ячейку, оператор и значение. Первое ограничение - общий объем продукции должен быть равен 300 единицам изделий. Введите адрес ячейки B6, выберите оператор равно (=) из раскрывающегося списка операторов и введите в качестве значения число 300. Для добавления очередного ограничения щелкните на кнопке Добавить. В таблице 1 приведены все ограничения для этой задачи.
| Ограничения | Сокращенная запись | ||||||
|---|---|---|---|---|---|---|---|
| Объем выпуска 300 единиц | | Не меньше 50 единиц изделия А
| Не меньше 40 единиц изделия В
| Не больше 40 единиц изделия С
| |
После ввода последнего ограничения щелкните но кнопке OK. Вы вернетесь к диалоговому окну Поиск решения, которое в нашем примере должно содержать четыре ограничения. Теперь для выполнения процедуры поиска решения введены все исходные данные. Чтобы начать процесс решения задачи, щелкните на кнопке Выполнить. В строке состояния будет отображаться ход решения задачи. Через некоторое время на экране появится информация о том, что решение найдено. Диалоговое окно Результаты поиска решения показано на рис. 4.
Рис.4. Диалоговое окно Результат поиска решения
После решения задачи Вы можете выбрать одну из следующих возможностей:
- Заменить исходные данные значения в изменяемых ячейках на те, которые были найдены в результате решения задачи.
- Восстановить исходные значения в изменяемых значениях.
- Создать несколько отчетов о процедуре поиска решения (для выбора нескольких отчетов из списка нажмите клавишу Shift и щелкните на нужном типе отчета).
- Щелкнуть на кнопке Сохранить сценарий для сохранения решения в виде сценария, который может быть использован в средстве Диспетчер сценариев.
Если Вы указали, что нужно создать отчет, он будет помещен на новом рабочем листе, которому будет присвоено соответствующее имя. На рис. 5 показан отчет по результатам поиска решения. В разделе отчета Ограничения будет указано состояние всех ограничений. Тип связанный означает, что данное ограничение удовлетворено, но при этом соответствующий параметр принял свое предельное значение, которое уже нельзя изменять.
Рис. 5. Отчет по результатам поиска решения
Рассмотрим диалоговое окно Параметры поиска решения, которое вызывается щелчком на кнопке Параметры в диалоговом окне Поиск решения (рис. 6). С помощью данного диалогового окна можно контролировать многие аспекты процесса решения задачи, загружать и сохранять спецификации моделей, заданных в виде диапазона.
Рис. 6. Диалоговое окно Параметры поиска решения
Данное окно содержит следующие параметры процедуры поиска решения:
- Максимальное время - ограничение времени решения задачи.
- Предельное число итераций - максимальное число промежуточных решений, допускаемых при поиске.
- Относительная погрешность - точность выполнения ограничений и соответствия вычисленного значения в целевой ячейке с заданным.
- Допустимое отклонение - максимальное отклонение в процентах для целочисленных решений.
- Сходимость - применяется к нелинейным задачам. Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в поле Сходимость, поиск прекращается.
- Линейная модель - служит для ускорения поиска решения, применяя к задаче оптимизации линейную модель.
- Неотрицательные значения - устанавливает нулевую нижнюю границу для тех влияющих ячеек, для которых не заданы ограничения.
- Автоматическое масштабирование - автоматическая нормализация входных и выходных значений, значительно различающихся по величине.
- Показывать результаты итераций - если установить эту опцию, то после выполнения очередной итерации процесс поиска решения приостанавливается и на экране отображается найденный результат.
- Разделы Оценки, Разности, Метод поиска - используются для контроля некоторых технических аспектов решения задачи. В большинстве случаев нет необходимости их изменять.
- Загрузить модель - отображает диалоговое окно Загрузить модель, в котором можно задать ссылку на диапазон ячеек рабочего листа, содержащих параметры модели.
- Сохранить модель - отображает диалоговое окно Сохранить модель, в котором нужно определить ссылку на диапазон ячеек рабочего листа, где будут сохранены параметры модели.
На следующем шаге мы рассмотрим примеры задач поиска решений.
