Поиск решения

Инструмент Поиск решения может применяться для решения задач, которые включают много изменяемых ячеек, и помогает найти комбина­ции переменных, которые устанавливают целевую ячейку в требуемое значение (например, максимальное или минимальное). Он также позво­ляет задать одно или несколько ограничений - условий, которые должны выполняться при поиске решений.

Ниже приведены примеры задач, которые могут быть решены, с помощью средства Поиск решения'.

- транспортная задача,

- задача о назначениях,

- составление оптимального плана производства,

- решение систем нелинейных уравнений,

- решение уравнения регрессии.

Формулировка задачи

Прежде чем обращаться к инструменту Поиск решения, нужно проанализировать задачу и построить математическую модель. Для по­строения модели необходимо:

а) определить, каковы переменные модели;

б) выбрать целевую функцию;

в) задать ограничения, которым должны удовлетворять переменные.

При подготовке рабочего листа к решению задачи нужно:

1) отвести диапазон ячеек для хранения переменных величин;

2) в отдельную ячейку ввести функцию цели. Функция цели всегда зависит от переменных, поэтому в ячейке с целевой функцией будут использованы ссылки на ячейки, где хранятся переменные;

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

Элементы диалогового окна Поиск решения

После построения математической модели можно обратиться к средству Поиск решения. Для этого нужно воспользоваться командой Сервис|Поиск решения. (Если эта команда недоступна, то сначала нужно выполнить следующую последовательность действий: вызвать диалоговое окно Надстройки с помощью команды Сервис|Надстройки и установить флажок Поиск решения.) На экране появится окно диалога Поиск решения (рис. 7.6).

В поле Установить целевую нужно указать ссылку на ячейку с целевой функцией. Если перед вызовом инструмента Поиск решения выделить ячейку с целевой функцией (рекомендуется), то это поле уже будет заполнено.

Ниже расположены элементы управления - переключатели, - по­зволяющие задать, какое значение целевой функции должно быть дос­тигнуто при решении задачи: минимальное, максимальное или некоторое конкретное значение.

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

Список Ограничения представляет все ограничения, накладывав кнопкой Добавить. На экране появится диалоговое окно (рис. 7,7).

Поле Ссылка на ячейку появившегося окна предназначено для ука­зания ссылки на ячейки, где хранятся переменные или формулы, исполь­зуемые для задания ограничений. В поле Ограничение можно задать константу, ссылку на ячейки со значениями или формулами. Значения из полей Ссылка на ячейку и Ограничение сравниваются с помощью операции («>=», «<=», «=», «цел», «двоич»), которою можно выбрать из списка, расположенного между этими двумя полями.

Кнопка Добавить позволит задать несколько ограничений, кнопка ОК добавляет ограничение и закрывает это окно.

Кнопка Изменить диалогового окна Поиск решения позволяет мо­дифицировать выделенное в списке Ограничения ограничение. При этом на экране появится диалоговое окно Добавление ограничение (рис. 7.7) с уже заполненными элементами управления.

Кнопка Удалить диалогового окна Поиск решения позволяет уда­лить выделенное ограничение.

После того как все данные для инструмента Поиск решения будут заданы, следует воспользоваться кнопкой Выполнить. Если решение

будет найдено, Excel выдаст об этом сообщение, и результаты расчета задачи будут размещены в соответствующие ячейки. Если решение не может быть найдено, то Excel также выдаст об этом сообщение.

Параметры поиска решения

Чтобы изменить параметры поиска решения исследуемой задачи, нужно воспользоваться кнопкой Параметры диалогового окна Поиск решения (рис. 7.6) для вызова окна диалога Параметры поиска реше­ния (рис. 7.8).

Примеры задач Транспортная задача

Задача. В области имеются два завода и три потребителя их продук­ции - домостроительные комбинаты. В таблице указаны суточные объе­мы производства цемента, суточные потребности в нем комбинатов и стоимость перевозки 1т цемента от каждого завода к каждому комбинату.

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

После подготовки рабочего листа нужно вызвать Поиск решения и заполнить диалоговое окно, как показано на рис. 7.10. Кроме того, нужно воспользоваться кнопкой Параметры и затем установить флажок Линейная модель.

Замечание. В рассмотренном примере задача является сбалансиро­ванной: суммарный объем произведенной продукции равен суммарному объему потребности в ней. Поэтому в этой модели не надо учитывать издержки, связанные со складированием (при перепроизводстве) или с недопоставками (при дефиците). В противном случае в модель нужно было бы ввести:

— (в случае перепроизводства] фиктивный комбинат, стоимость пе­ревозок единицы продукции в который будет равна стоимости складирования, а потребности в продукции - объемам складиро­вания излишков продукции на заводах;

- (в случае дефицита) фиктивный завод, стоимость перевозок с ко­торого будет равна стоимости штрафов за недопоставку продук­ции, а объемы перевозок - объемам недопоставок продукции в пункты распределения.

Решение системы нелинейных уравнений

Система содержит уравнение окружности и уравнение прямой. Решением этой системы являются точки пересечения окружности с прямой. При этом прямая может пересечь окружность в двух точках, коснуться окружности в одной точке или не пересечь окружность вовсе. Таким образом, решений

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Назовите известные Вам средства анализа «что-если».

2. Для каких задач можно использовать инструмент Подбор параметра?

3. Опишите процесс решения задачи с помощью инструмента Подбор параметра.

4. Для решения каких задач применяют средство Поиск решения?

5. Опишите процесс подготовки задачи к решению с помощью средства Поиск решения.

6. Опишите элементы диалогового окна Поиск решения.

7. Какие параметры можно задать в окне диалога Параметры поиска решения?