Лабораторная работа №7

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

Цель: Освоить практические приемы поиска оптимальных решений в электронных таблицах с использованием поиска решения.

7.1. Назначение средства «Поиск решения»

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

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

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

Типичными задачами, решаемыми с помощью средства Поиск решения, являются:

• Ассортимент продукции. Максимизация выпуска товаров при ограниче­ниях на сырье для производства этих товаров.

• Штатное расписание. Составление штатного расписания для достижения наилучших результатов при наименьших расходах.

• Планирование перевозок. Минимизация затрат на транспортировку това­ров.

• Составление смеси. Получение заданного качества смеси при наимень­ших расходах.

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

Под ограничениями понимаются соотношения типа А1<=В1, А1=А2, А1>=0. По меньшей мере, одна из ячеек в соотношении, задающем ограничение, должна зависеть от переменных задачи, в противном случае это ограничение не влияет на процесс решения. Часто ограничения записываются сразу для групп ячеек, например: А1:А10<=В1:В10 или A1:E1>=0. Правильная формулировка ограничений является самой ответственной ча­стью при создании модели для поиска решения. Вот некоторые примеры ограничений та­кого типа.

• В модели с несколькими периодами времени величина материального ресурса на начало следующего периода должна равняться величине этого ресурса на конец предыдущего периода.

• В модели поставок величина запаса на начало периода плюс количество полученного должна равняться величине запаса на конец периода плюс количество отправленного.

• Многие величины в модели по своему физическому смыслу не могут быть отрицательными, например, количество полученных единиц товара или сумма денежных средств в кассе.

Ограничения имеют тот же синтаксис, что и логические формулы, но используются по-разному. В найденном решении логические формулы будут выполнены точно, а ограничения – с некоторой возможной погрешностью. Величина этой погрешности задается параметром Относительная погрешность, по умолчанию значение этого параметра равно 0,000001. По этой причине не используются ограни­чения типа А1>0, поскольку подобные ограничения из-за наличия погреш­ности неотличимы от А1>=0.

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

X=A*Y1+B*Y2+C*Y3+...,

где А, В и С – константы, Y1, Y2, Y3 – переменные, Х – ре­зультирующее значение.

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

Средство Поиск решения можно использовать при нелинейны­х зависимостях и ограничениях. Нелинейные зависимости широко распространены: например, зависимость суммарной стоимости проданного товара от объема продаж может не быть прямо пропорциональной из-за скидок оптовым покупателям и т. п. Для успешного использования средства Поиск решения желательно, чтобы зависимости были «глад­кими» или, по меньшей мере, непрерывными. Наиболее часто разрывные зависимости возникают при использовании функции ЕСЛИ(), среди аргу­ментов которой имеются переменные величины модели. Некоторые про­блемы могут возникнуть и при использовании в модели функций типа ABS(), ROUND() и т.п.

Если средство Поиск решения не было установлено при первона­чальной установке Excel, то следует запустить процесс установки Excel по­вторно и выбрать только эту надстройку. После завершения установки Поиск решения появляется в списке доступных надстроек Excel.

Для того, чтобы средство Поиск решения загружалось сразу при запуске Excel необходимо:

1. Выбрать команду Сервис, Надстройки.

2. В диалоговом окне Надстройки, в списке надстроек установить флажок Поиск реше­ния.

3. Нажать кнопку ОК.