Ячейка Формула Копировать в

С16 =С4*С9 С16: F18

G19 =СУММ(С19Т19)

Чтобы из ячейки С16 копировалась только сама формула (без формата ячейки, к которому относятся левая и верхняя границы), нужно воспользоваться специ­альной вставкой. Для этого щелкните ячейку С16 правой кнопкой мыши и в контекстном меню выберите команду Копировать, затем выделите диапазон С16:F 18, щелкните его правой кнопкой мыши и в контекстном меню выберите:

1.133. в Excel 2010 — параметр вставки Формулы;

1.134. в предыдущих версиях Excel — команду Специальная вставка (затем в открывшемся окне выберите параметр формулы и щелкните кнопку ОК).

Фон всех пустых ячеек вокруг нашей модели можно сделать темным, вы­брав соответствующий цвет заливки (это помогает сконцентрировать внимание на значимых частях модели). Некоторые ячейки (Н9:Н11 и С13:F 13) выполняют двойную функцию: содержат значение правой части ограничения и знак нера­венства. Знаки <= и >=, присутствующие в этих ячейках, не являются их со­держимым, это часть формата ячеек. Такой формат позволяет отображать вме­сте с числом заданные пользователем строки символов, например обозначения денежных единиц. Чтобы задать такой формат сделайте следующее:

• Выделите ячейки для форматирования и щелкните их правой кнопкой мыши.

• В появившемся контекстном меню выберите команду Формат ячеек.

• В открывшемся окне на вкладке Число в списке Числовые форматы выберите пункт (все форматы).

• В списке Тип по умолчанию будет выбран формат Основной.

• В поле Тип измените код этого формата на "<="ОсновноЙ или ">="Основной (то есть перед словом Основной введите в кавычках нужный знак).

• Щелкните кнопку ОК. Созданный формат будет применен к выделен­ным ячейкам и сохранится в разделе (все форматы) для дальнейшего исполь­зования.

Использование средства Поиск решения подробно описано в разделе 1.3. Ввод данных для поиска решения в Excel 2010 показан на рис. 3.17, а в преды­дущих версиях — на рис. 3.18.

Полученное оптимальное решение отображается в рамке во второй табли­це (см. рис. 3.16). В третьей таблице выводятся оптимальные затраты на пере­возку по всем направлениям, а в ячейке G19 — оптимальное значение целевой функции (минимальные общие затраты).

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

Оптимизировать целевою функцию: SGS19

До: Q Максимум (О) Минимум Q Значения: Изменяя ячейки переменных:

SCS9:SFS11

№

т\

В соответствии с ограничениями:

Г71 Сделать переменные без ограничений неотрицательными

Выберите метод решения:

Метод решения

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

Поиск решения линейных задач симплекс-методом ▼

Справка

Найти решение

Закрыть

Рис. 3.17. Задание данных для поиска решения транспортной задачи в Excel 2010 Анализ чувствшпельности

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

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

Установить целевую ячейку:

Равной: (' максимальному значению С

Р Линейная модель Г/ Неотрицательное значения

|$C$9:$F*11

Ограничения:

минимальному значению

Изменяя ячейки:

$C$12:$F$12 >= $C$13:$F$13 $G^9:$G$11 <=$Н|.9:$Н$11

Рис. 3.18. Задание данных для поиска решения транспортной задачи в Excel 2007 и

более ранних версиях

за исключением одного, остаются неизменными. На рис. 3.19 показано, как вы­глядит этот отчет в Excel 2010. Он состоит из двух таблиц, заголовки которых отличаются в разных версиях программы. (Далее сначала дается вариант назва­ния из Excel 2010, за которым в скобках — вариант нз предыдущих версии.)

В первой таблице отчета наибольший интерес представляет приведенная (нормированная) стоимость. Она показывает:

1.135. Насколько, как минимум, нужно снизить тариф нулевой перевозки, что­бы она стала положительной (выгодной).

[1] Насколько увеличатся общие затраты, если ввести в план перевозку единицы груза в невыгодном направлении, не меняя тарифов. Например, затра­ты на перевозку единицы груза нз 1-го пункта отправления во 2-й пункт назна­чения должны быть сокращены, как минимум, на 1 р., чтобы это направление стало привлекательным.

Допустимое увеличение и уменьшение показывают, в каких пределах можно изменять тариф перевозки, чтобы сохранялся прежний оптимальный план. При наличии ненулевой приведенной (нормированной) стоимости она совпадает с допустимым уменьшением, тогда как допустимое увеличение равно +оо (вместо символа +оо в Excel выводится 1Е+30, что означает 10⁺³°). Нулевые значения 9допустимого увеличения или уменьшения — признак наличия аль­тернативных оптимальных решений.

icrosoft Excel 14.0 Отчет об устойчивости

Я чейки переменных

		Окончательное Приведенн. Целевая функция	Допустимое	Допустимое
Ячейка	ИМЯ	Значение	Стоимость	Коэффициент	Увеличение Уменьшение
	П.отпр.1 П.назн.1				1Е+30
тэ	П.отпр.1 П.назн.2				1Е+30
$Е$9	П.отпр.1 П.назн.З				1Е+30
$F$9	П.отпр.1 П.назн.4					1Е+30
$С$10	П.отпр.2 П.назн.1				1Е+30
•таю	П.отпр.2 П.назн.2
$Е$10	П.отпр.2 П.назн.З				1Е+30
$F$10	П.отпр.2 П.назн.4
$С$11	П.отпр.З П.назн.1
$D$11	П.отпр.З П.назн.2
$Е$11	П.отпр.З П.назн.З
$F$11	П.отпр.З П.назн.4				1Е+30
граничения
		Окончательное	Тень	Ограничение	Допустимое	Допустимое
Ячейка	Имя	Значение	Цена	Правая сторона	Увеличение Уменьшение
$С$12	Всего П.назн.1
$D$12	Всего П.назн.2
$Е$12	Всего П.назн.З
$F$12	Всего П.назн.4
$G$9	П.отпр.1 Всего		-2
$G$10	П.отпр.2 Всего		-2
$G$11	П.отпр.З Всего				1Е+30

Рис. 3.19. Отчет об устойчивости решения транспортной задачи в Excel 2010

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

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

Например, если на несколько единиц (до 150) уменьшить потребности в 3-м пункте назначения, то на каждую единицу общие затраты будут снижаться на 7 р. А если на несколько единиц (до 50) увеличить запасы во 2-м пункте от­правления, то на каждую единицу общие затраты будут сокращаться на 2 р.

Варианты транспортной задачи

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

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

1.137. Если суммарные запасы груза превышают суммарные потребности, формулировка модели не меняется.

1.138. Если суммарные потребности превышают суммарные запасы груза, можно действовать двумя способами. Первый способ: в ограничениях на коли­чество отправляемых грузов поменяйте знак < на = (все запасы будут израсхо­дованы), а в ограничениях на количество доставляемых грузов — знак > — на < (не все потребности будут удовлетворены). Второй способ: введите фиктивный пункт отправления, условно приписав ему недостающий запас грузов. Тарифы на перевозки из этого пункта в любой пункт назначения можно положить рав­ными нулю или штрафу за недопоставку единицы груза в этот пункт назначе­ния (возможность минимизации штрафных санкций за недопоставку грузов — преимущество второго способа действий). Любая поставка из фиктивного пункта отправления, полученная в оптимальном решении, трактуется как недо­поставленный груз.

1.139. Если по условию задачи какая-либо перевозка выполнена быть не мо­жет, то в качестве соответствующего тарифа перевозки введите: в задаче на ми­нимум — большое число, значительно превышающее тарифы других перево­зок, а в задаче на максимум — наоборот, маленькое число, значительно мень­шее остальных (можно даже отрицательное).

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

Глава 4