ЛИНЕЙНОЕ И НЕЛИНЕЙНОЕ ПРОГРАММИРОВАНИЕ В EXCEL

Назначение и возможности надстройки «Поиск решения»

Большая часть задач, с которыми приходится сталкиваться в научной и финансовой деятельности, не столь просты, как кажутся на первый взгляд. Зачастую значения искомых функций зависят от большого числа переменных, и при этом чаще всего требуется отыскать наилучшее, оптимальное решение для данной задачи (например, дающее максимальную прибыль или обеспечивающее минимальные затраты), удовлетворяющее при этом целому ряду дополнительных условий на значения используемых параметров. Для решения таких задач, требующих применения математического аппарата линейного и нелинейного программирования и методов исследования операций, используется имеющаяся в Excel надстройка «Поиск решения» (Add-in «Solver»).

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

Круг задач линейного программирования довольно широк. Ниже приведены лишь несколько классических примеров.

1. Транспортная задача. Ее форма состоит в следующем: имеется несколько пунктов производства и пунктов потребления некоторого продукта. Для каждого из пунктов производства задан объем производства, а для каждого пункта потребления – объем потребления. Известна также стоимость перевозки единицы продукта из каждого пункта производства в каждый пункт потребления. Требуется составить план перевозок продукта, в котором все пункты потребления были бы обеспечены необходимыми продуктами, ни из какого пункта производства не вывозилось бы продуктов больше, чем там производится, а стоимость перевозки была бы минимальной.

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

3. Задача о назначениях. Имеется несколько должностей и соответствующее количество претендентов на эти должности. Назначение i-го претендента на j-ую должность связано с затратами С[i, j]. Требуется распределить претендентов на должности так, чтобы суммарные затраты были бы минимальными.

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

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

6. Задачи линейной алгебры. С помощью этих же методов можно решать различные системы линейных (и не только линейных) уравнений.

Задачи, для решения которых можно воспользоваться надстройкой “Поиск решения”, имеют ряд общих свойств.

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

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

§ Кроме того, может быть задано некоторое количество ограничений – условий или соотношений, которым должны удовлетворять некоторые из изменяемых ячеек. Например, можно потребовать, чтобы общие затраты не превосходили 100 тыс. руб. или чтобы затраты на рекламную кампанию составляли от 10 % до 15 % от общих расходов.

Использование надстройки «Поиск решения»