Федеральное агентство по сельскому хозяйству

Федеральное государственное образовательное учреждение высшего профессионального образования

Государственный университет по землеустройству

Кафедра информатики

Методические указания к Excel-2007.

Решение сложных задач с использованием надстройки Поиск решения

Выполнила:

студентка 1 курса 12-э группы

Донченко Д.Д.

Проверил:

доцент Кулешов В.А.

Москва-2009

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

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

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

Преимущества надстройки Поиск решения

В основе надстройки Поиск решения, как и в инструменте Подбор параметра, находятся итеративные алгоритмы. Это означает, что надстройка начинает с некоторого стартового решения, анализирует результат, пробует другое решение и т.д. Тем не менее, эти циклические итерации — не просто перебор всех возможных значений с помощью компьютера. В программе учитывается, как изменяется результат после каждой итерации, а также (с помощью специальных математических вычислений) определяется оптимальное направление изменения значений для достижения результата.

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

■ Поиск решения позволяет определить несколько ячеек в качестве исходных переменных. В общей сложности можно указать до 200 изменяемых ячеек.

■ Поиск решения дает возможность определить ограничения на значения изменяемых ячеек. Например, можно найти решение задачи, которое не только максимизирует прибыль, но и удовлетворяет другим условиям, например, рентабельность продукции должна находиться в диапазоне от 20% до 30% или общие издержки не превышать 100000. Эти условия называют ограничениями оптимизационной задачи.

■ Поиск решения выполняет поиск не просто желаемого результата ("цель" при использовании инструмента Подбор параметра), а опти­мального результата. Это означает, что в итоге будет получено макси­мальное или минимальное решение.

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

Типы задач, для которых используется надстройка Поиск решения

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

■ В задаче есть одна целевая ячейка, содержащая формулу, для которой необходимо найти максимум, минимум или установить определенное значение. Эта формула может также выполнять вычисления, например общих затрат на перевозку или чистой прибыли.

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

■ Иногда задача может включать одну или несколько ячеек ограничений, которые должны удовлетворять некоторому критерию. Например, можно поставить условие, чтобы затраты на рекламу составляли не бо­лее 10% от общих издержек или скидка, предоставляемая покупателям, была от 40% до 60%.

Какие типы задач имеют эти характеристики? Как ни странно, таких задач очень много, и в их числе следующие.

■ Транспортная задача. Это задача минимизации затрат на перевозку от нескольких производственных точек к нескольким складам (или ко­нечным покупателям) при условии удовлетворения спроса.

■ Проблема распределения ресурсов. Задача минимизации затрат на ре­сурсы, например на персонал, при условии удовлетворения всех крите­риев набора персонала.

■ Проблема поиска оптимальной комбинации продукции. Это задача получения максимальной прибыли от производства комбинации различных продуктов при условии удовлетворении запросов потребителей. Эта задача производства нескольких видов продукции с различными затратами на единицу выпуска, различной рентабельностью и кривыми спроса.

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

■ Задачи линейной алгебры. Это оптимизационные задачи, ограничения в которых представляют собой систему линейных уравнений.