Задания для самостоятельной работы:
Найти корни уравнения:
3. Поиск решения
Мы только что познакомились с использованием команды Подбор параметра, позволяющей найти подходящее значение одной переменной, при котором формула принимает нужное значение. Зачастую значения формул зависят от большого числа переменных, и при этом чаще всего требуется отыскать наилучшее, оптимальное решение (например, дающее максимальную прибыль или обеспечивающее минимальные затраты), удовлетворяющее при этом целому ряду дополнительных условий на значения используемых параметров. Для решения таких задач, намного более сложных, чем только что рассмотренные, требуется и намного более мощный инструмент. Именно таким могучим оружием, позволяющим решить сложные задачи, требующие применения математического аппарата линейного и нелинейного программирования и методов исследования операций, и является имеющаяся в Excel надстройка "Поиск решения".
Отметим, что в основе надстройки "Поиск решения" также лежат итерационные методы. Однако эта надстройка использует гораздо более сложные методы, чем рассмотренный выше подбор параметра. Укажем здесь некоторые отличия этих двух инструментов:
"Поиск решения" позволяет использовать одновременно большое количество (в общей сложности до 200) изменяемых ячеек;
"Поиск решения" позволяет задавать ограничения для изменяемых ячеек. Например, при поиске решения, обеспечивающего максимальную прибыль, вы можете задать дополнительные условия, скажем, потребовать, чтобы при этом общий доход находился в диапазоне между 20% и 30% , или чтобы расходы не превосходили 1 000 000 рублей. Подобного рода условия называются ограничениями для решаемой задачи.
"Поиск решения" доставляет не заранее известный конкретный результат для целевой функции, как в случае использования метода подбора параметра, но отыскивает оптимальное (минимальное или максимальное), т. е. наилучшее из возможных решение.
Наконец, для сложных задач "Поиск решения" может генерировать множество различных решений. При этом вы можете сохранить варианты этих решений, определив для них соответствующие сценарии (для работы со сценариями в Excel имеется еще один полезный инструмент — Диспетчер сценариев.
Задачи, для решения которых можно воспользоваться надстройкой "Поиск решения", имеют ряд общих свойств:
Имеется единственная целевая ячейка, содержащая формулу, значение которой должно быть сделано максимальным, минимальным или же равным какому-то конкретному значению. Эта формула может, например, служить для вычисления чистой прибыли или общих транспортных расходов.
Формула в этой целевой ячейке содержит ссылки (прямые или косвенные) на ряд изменяемых ячеек (содержащих неизвестные, или переменные решаемой задачи). Поиск решения заключается в том, чтобы подобрать такие значения этих переменных, которые бы давали оптимальное значение для формулы в целевой ячейке. Изменяемые ячейки могут содержать, например, себестоимость или цену товаров, транспортные тарифы или налоговые ставки.
Кроме того, может быть задано некоторое количество ограничений — условий или соотношений, которым должны удовлетворять некоторые из изменяемых ячеек. Например, можно потребовать, чтобы общие затраты не превосходили 100 000 000 рублей или чтобы затраты на рекламную кампанию составляли от 10 до 15 % от общих расходов.
Какие же задачи могут быть сформулированы подобным образом? Круг подобных задач широк. Приведем лишь несколько классических примеров.
Транспортная задача. Ее формулировка состоит в следующем: имеется несколько пунктов производства и пунктов потребления некоторого продукта. Для каждого из пунктов производства задан объем производства, а для каждого пункта потребления — объем потребления. Известна также стоимость перевозки из каждого пункта производства в каждый пункт потребления единицы продукта. Требуется составить план перевозок продукта, в котором все пункты потребления были бы обеспечены необходимыми продуктами, ни из какого пункта производства не вывозилось бы продуктов больше, чем там производится, а стоимость перевозки была бы минимальной.
Задача о выборе оптимального меню. Имеется набор некоторых продуктов, обладающих некоторой калорийностью, а также известны количества белков, жиров и углеводов для каждого из этих продуктов и их стоимость. Требуется составить меню, удовлетворяющее требованиям калорийности и сбалансированности питательных продуктов, и при этом минимизирующее суммарную стоимость3.
Задача о назначениях. Имеется несколько должностей и соответствующее количество претендентов на эти должности. Назначение i-того претендента на j-тую должность связано с затратами C[i,j]. Требуется распределить претендентов по должностям так, чтобы суммарные затраты были бы минимальны.
Задача о выборе портфеля ценных бумаг. Вкладчик хочет выбрать портфель ценных бумаг, при этом известны средние значения доходов от каждого вида ценных бумаг и ожидаемая дисперсия этих доходов. Требуется отыскать оптимальный портфель, обеспечивающий максимальный ожидаемый доход при минимальном рассеянии, и, следовательно, минимальном риске.
Задачи линейной алгебры. С помощью этих же методов можно решать различные системы линейных (и не только линейных) уравнений.