С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+3°). Нулевые значения 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