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

      Покажем, что представленная задача удовлетворяет  рассмотренным выше требованиям.

1. Поскольку тарифы одинаковые, то в качестве целевой функции следует выбрать эксплуатационные затраты. Эти затраты необходимо минимизировать путём оптимального распределения автомобилей по клиентам.
2. Поскольку в общем случае m¹n, то задачу необходимо сбалансировать путём введения фиктивных заказов или фиктивных автомобилей. Получим:

а) При  n>m заказов меньше, чем автомобилей (избыток провозных возможностей). В этом случае дополнительно вводятся n-m фиктивных клиентов с нулевыми объёмами заказов (т.е. Q_j=0 и L_j=0). Поскольку для фиктивных клиентов заказы нулевые, то для их выполнения будут назначаться самые неэффективные по затратам автомобили. Практически выполнение заказа фиктивного клиента означает резервирование автомобиля (автомобиль остаётся в парке).

б) При  n<m заказов больше, чем автомобилей (недостаток провозных возможностей). В этом случае дополнительно вводятся m-n фиктивных автомобилей с бесконечно большими удельными затратами (т.е. с_j ®¥). Практически это означает отказ от самых невыгодных в смысле затрат заказов.

3. Окончательно получим сбалансированную задачу, описываемую квадратной матрицей эксплуатационных затрат размерностью k´k, где k=max{m,n}.

     Алгоритм  решения данной задачи в Excel сводится к следующему.

    Количество  рейсов i-го автомобиля у j-го клиента вычисляется по формуле

      , для всех  i=1,2,…k; j=1,2,…k.

     Количество  рейсов - величина целочисленная, принимающая  значение большее или равное 1. Для  её вычисления следует воспользоваться  функцией округления частного от деления в большую сторону. Например, если исходные данные находятся в ячейках B29:C29 и D26:D27, то количество рейсов определяется функцией (второй параметр функции округления равен 0)

    =ОКРУГЛВВЕРХ($B6/D$5;0)

     Пробег  i-го автомобиля у j-го клиента вычисляется по формуле

      Эксплуатационные затраты вычисляются  по формуле

      ,

где c_i – удельные эксплуатационные затраты, связанные с назначением i-го автомобиля для обслуживания j-го клиента, т.е. для приведенного выше примера в ячейку D6 необходимо занести формулу

     =ОКРУГЛВВЕРХ($B6/D$3;0)*$C6*D$4

     Дополнительная  целочисленная переменная логического  типа принимает значения

     Целевая функция имеет вид

при ограничениях:

      ;       ;       целое для всех i,j =1,2,… k.

     Найдем  решение задачи 3.1 в Excel, используя следующие исходные данные.

     Автотранспортная  компания располагает 10 автомобилями разных марок: 0 автомобилей марки A; 4 автомобиля марки B; 3 автомобиля марки C; 2 автомобиль марки D; 1 автомобилей марки E.

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

Таблица 12 – Матрица затрат Sij  

     Введем  Матрицу X_ij, содержащую переменные логического типа x_ij  Матрица произведения S_ij*X_ij, в которой отразится результат оптимального закрепления автомобилей за клиентами и, соответствующие этому закреплению, минимальные затраты. Используя меню СервисÞПоиск решения открываем диалоговое окно Поиск решения, в котором устанавливаем целевую ячейку равной минимальному значению, определяем диапазон изменяемых ячеек со значениями логической переменной x_ij (Матрица X_ij) и ограничения, и запускаем процедуру вычисления, щелкнув по кнопке Выполнить. Результат поиска будет находиться в изменяемых ячейках Матрицы X_ij (i - автомобиль;  j - клиент) и в целевой ячейке (эксплуатационные затраты) (Таблица 13 и Таблица 14).  

Таблица 13 -  Оптимальное  закрепление автомобилей 

     Очевидно, что девятый автомобиль, назначенный фиктивному десятому клиенту, будет простаивать в парке.

Таблица 14 –  Затраты, соответствующие оптимальному закреплению автомобилей

Видно, что минимальные затраты на перевозки составят:

      - автомобиль №1 закреплен за 6-ым клиентом, минимальные затраты на перевозку составляют 44;

      - автомобиль №2 закреплен за 3-ым клиентом, минимальные затраты на перевозку составляют 116;

      - автомобиль №3 закреплен за  4-ым клиентом, минимальные затраты на перевозку составляют 59;

      - автомобиль №4 закреплен за 1-ым клиентом, минимальные затраты на перевозку составляют 192;

      - автомобиль №5 закреплен за 9-ым клиентом, минимальные затраты на перевозку составляют 69;

      - автомобиль №6 закреплен за 5-ым клиентом, минимальные затраты на перевозку составляют 14;

      - автомобиль №7 закреплен за 2-им клиентом, минимальные затраты на перевозку составляют 105;

      - автомобиль №8 закреплен за 8-ым клиентом, минимальные затраты на перевозку составляют 6;

      - автомобиль №10 закреплен за 7-ым клиентом, минимальные затраты на перевозку составляют 22. 

     Эксплуатационные  затраты составляют  627у.е.

Заключение

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

Список  использованной литературы

1. Бочкарев А.А. Решение задач транспортного типа в Excel: Учеб. пособие по спец. 062200 - Логистика / А.А. Бочкарев. СПбГИЭУ. - СПб., 2002. -     64 с.
2. Волков И.К., Загоруйко Е.А. Исследование операций: Учеб. для вузов / И.К. Волков, Е.А. Загоруйко. / Под ред. В.С. Зарубина, А.П. Крищенко. - М.: Изд-во МГТУ им. Н.Э. Баумана, 2000. - 436 с.
3. Кожин А.П. Математические методы в планировании и управлении автомобильными перевозками: Учеб. пособие для студентов экон. спец. вузов. / А.П. Кожин - М.: Высш. школа, 1979. -304 с.
4. Попов А.А. Excel: практическое руководство: Учеб. пособие для вузов. / А.А. Попов - М.: ДЕСС КОМ, 2001. -302с.
5. Таха, Хэмди, А. Введение в исследование операций, 6-е издание.: / Таха, Хэмди, / Пер. с англ. - М.: Издательский дом "Вильямс", 2001. -912 с.
6. Транспортная логистика: Учебник для транспортных вузов. / Под общей редакцией Л.Б. Миротина. - М.: Издательство "Экзамен", 2002. -512 с.