1.2 Надстройка Поиск решения

Решение задач оптимизации может быть существенно облегчено с помощью надстройки электронной таблицы Excel Поиск решения. Поиск решений является частью блока задач, который иногда называют анализом «что-если». Это процесс изменения значений ячеек на листе и анализа влияния этих изменений на результат вычисления формул, например, изменение процентной ставки, используемой в таблице амортизации для определения сумм платежей.

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

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

задачи необходимо начинать с построения соответствующей модели.

1.2.1 Назначение надстройки Поиск решения

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

• ассортимент продукции: максимизация выпуска товаров при ограничениях на сырье (или другие ресурсы) для производства изделий;

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

• планирование перевозок: минимизация затрат на транспортировку;

• составление смеси: получение заданного количества смеси при наименьших расходах;

• оптимальный раскрой материалов (ограничения – количество деталей различной формы и размеров);

• оптимизация финансовых показателей (например, максимизация доходов за счет оптимизации средств на разные инвестиционные проекты).

Задачи, лучше всего решаемые данным средством, имеют три свойства:

• имеется единственная максимизируемая или минимизируемая цель;

• имеются ограничения, выражающиеся, как правило, в виде неравенств;

• имеется набор входных значений-переменных, прямо или косвенно влияющих на ограничения и на оптимизируемые величины.

1.2.2 Ограничения в задачах

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

Часто ограничения записываются сразу для групп ячеек, например: A1:А10<=В1:В10 или А1:Е1>=0.

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

Примеры ограничений такого типа:

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

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

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

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