Решение транспортных задач в MS Excel

Рассмотренная выше методика решения транспортных задач «вручную» не слишком часто применяется на практике. Это связано с тем, что реальные транспортные задачи имеют большую размерность. Например, компании «Тойота» принадлежит 12 заводов в Японии и более 53 производственных подразделений по всему миру; продукция этих заводов поставляется в сотни автоцентров (только в России их – 39)7. Очевидно, что ручное решение такой задачи потребует большого количества времени.

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

В пакет MS Excel входит надстройка «Поиск решения» (Solver), которая позволяет решать транспортные задачи, содержащие до 200 переменных. Для решения задач, имеющих большую размерность, следует использовать специализированные программы.

«Поиск решения» не появляется в меню Excel автоматически. Для того чтобы активизировать это приложение, необходимо запустить Excel, зайти в меню Сервис/Надстройки и в открывшемся диалоговом окне поставить флажок в строке «Поиск решения».

После этого в меню Сервис появится пункт «Поиск решения»; при выборе этого пункта появляется окно (рис. 1.6), в котором нужно задать ячейки, содержащие целевую функцию и ограничения, а также – область вывода матрицы объемов транспортировки xij.

Решим задачу (табл. 4.10) с использованием надстройки «Поиск решения». Прежде всего, необходимо перенести на лист MS Excel исходные данные и задать формулы, по которым будут определяться целевая функция и ограничения. Результат этой работы представлен на рис. 4.7. Серым цветом обозначены ячейки, в которые будет выведена матрица xij.

Целевая функция задана в ячейке G5. Она равняется сумме произведений ячеек В2:D4 на ячейки B9:D11. Для большего удобства в работе целевая функция рассчитывается поэтапно. Так, например, в ячейку G2 введена формула СУММПРОИЗВ(B2:D2;B9:D9). Это значение представляет собой сумму транспортных расходов, которые необходимо понести, чтобы вывезти всю продукцию с завода А. Аналогично заполняются ячейки G3 и G4. Совокупные транспортные расходы (G5) находятся как сумма ячеек G2:G4.

В ячейках Е9:Е11 и B13:D13 записываются выражения, предполагающие, что вся продукция заводов будет распределена и все потребности клиентов будут удовлетворены. Так, например, формула для ячейки Е9 выглядит следующим образом: СУММ(B9:D9)-E2.

Подготовив исходную таблицу, вызываем окно «Поиск решения» (рис. 4.8). Целевая функция у нас задана в ячейке G5. Так как она представляет собой сумму транспортных издержек, в данной задаче мы стремимся ее минимизировать. Ставим отметку в строке «равной минимальному значению». В поле «Изменяя ячейки» мы должны задать область вывода матрицы xij, объемов перевозки продукции между предприятиями. Эта область на рис. 4.7 обозначена серым цветом и включает в себя ячейки B9:D11.

Введение ограничений происходит после нажатия кнопки «Добавить». Прежде всего, мы должны задать ограничение неотрицательности значений xij, а затем – ограничения полного удовлетворения потребностей и полного распределения продукции предприятий.

Форма «Поиск решения» с заданными ограничениями приведена на рис. 4.8. Если Вы ошиблись в формулах ограничений, их всегда можно убрать (кнопка «Удалить») или исправить (кнопка «Изменить»). После заполнения всех полей нажимаем кнопку «Выполнить».

После проведения расчетов надстройка «Поиск решения» в заданных ячейках выдает результирующую матрицу xij. На рис. 4.9 представлен результат решения нашей задачи.

Значением 5Е-07 или 1Е-06 Excel обозначает очень малые величины, неотличимые от нуля. Таким образом, ограничения в ячейках Е10, Е11 и В13 можно считать выполненными.

Как можно отметить, результат, полученный с помощью надстройки «Поиск решения», не отличается от результатов вычислений вручную (табл. 4.16). Значение целевой функции (ячейка G5) равно 7800 тыс. руб.

 

 

Узнай цену консультации

"Да забей ты на эти дипломы и экзамены!” (дворник Кузьмич)