Применение MS Excel в решение логистических задач

NovaInfo 7, скачать PDF
Опубликовано
Раздел: Технические науки
Просмотров за месяц: 40
CC BY-NC

Аннотация

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

Ключевые слова

MS EXCEL, РЕШЕНИЕ, ЛОГИЧЕСКИЕ ЗАДАЧИ

Текст научной работы

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

Оптимальным считается маршрут, по которому возможно доставить логистический объект, в кратчайшие сроки (или предусмотренные сроки) с минимальными затратами, а также с минимальным вредом для объекта доставки.

Одним из способов нахождения такого оптимального маршрута может служить решение транспортных задач в MS Excel.

Транспортная задача является частным типом задачи линейного программирования и формулируется следующим образом: имеется m пунктов отправления (или пунктов производства) А_i,\dots,А_m, в которых сосредоточены запасы однородных продуктов в количестве a_1,\dots,а_m единиц. Имеется n пунктов назначения (или пунктов потребления) В_1,\dots,В_m, потребность которых в указанных продуктах составляет b_1,\dots,b_n единиц. Известны также транспортные расходы Сij, связанные с перевозкой единицы продукта из пункта Ai в пункт Вj, i=1,\dots,m; j=1,\dots,n.

Общая постановка транспортной задачи состоит в определении оптимального плана перевозок некоторого однородного груза из т пунктов отправления А_1,А_2,\dots,А_n в n пунктов назначения В_1,В_2,\dots,В_n.

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

Обозначим Cij тарифы перевозки единицы груза из i-го пункта отправления в j-й пункт назначения, через ai — запасы груза в i -ом пункте отправления, через bj — потребности в грузе в j-ом пункте назначения, а через xi — количество единиц груза, перевозимого из i-го пункта отправления в j-й пункт назначения. Тогда математическая модель транспортной задачи состоит в определении минимального значения функции:

F=\sum_{i=1}^m\sum_{j=1}^nc_{ij}x_{ij}\rightarrow min, (1)

при условиях

\sum_{i=1}^mx_{ij}=b_j, j = 1,2,…,n, (2)

\sum_{j=1}^nx_{ij}=a_i, i = 1,2,…,m (3)

x_{ij}\geq0, i=1,2,\dots,m, j=1,2,\dots,n. (4)

Поскольку переменные x_{ij}\geq0, i=1,2,\dots,m, j=1,2,\dots,n. удовлетворяют системам линейных уравнений (2) и (3) и условию неотрицательности (4), обеспечиваются доставка необходимого количества груза в каждой из пунктов назначения, вывоз всего имеющегося груза из всех пунктов отправления, а также исключаются обратные перевозки.

Суммарное количество груза у поставщиков равно \sum_{i=1}^ma_i суммарная потребность в грузе в пунктах назначения равна \sum_{j=1}^nb_j. Если суммарная потребность в грузе в пунктах назначения равна суммарному запасу груза в пунктах отправления, т. е.

\sum_{i=1}^ma_i=\sum_{j=1}^nb_j, (5)

то такая транспортная задача называется закрытой или сбалансированной. В противном случае — открытой или несбалансированной.

В случае превышения суммарного запаса над суммарной потребностью, т.е. если

\sum_{i=1}^ma_i>\sum_{j=1}^nb_j,

вводится фиктивный n+1-й потребитель (или пункт назначения) с потребностью, равной:

b_{n+1}=\sum_{i=1}^ma_i-\sum_{j=1}^nb_j

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

В случае превышения суммарной потребности в грузе над суммарными запасами поставщиков, т. е. если

\sum_{i=1}^ma_i<\sum_{j=1}^nb_j

вводится фиктивный m+l-й пункт отправления с запасом груза в нем, равным:

a_{m+1}=\sum_{i=1}^ma_i-\sum_{j=1}^nb_j

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

Предположим, что \sum_{i=1}^ma_i=\sum_{j=1}^nb_j, т. е. общий объем производства равен общему объему потребления. Требуется составить такой план перевозок (откуда, куда и сколько единиц продукта везти), чтобы удовлетворить спрос всех пунктов потребления за счет реализации всего продукта, произведенного всеми пунктами производства, при минимальной общей стоимости всех перевозок.

Рассмотрим алгоритм решения таких задач на конкретном примере:

Продукты, находящиеся на четырех складах — С1, С2, С3, С4 необходимо развезти по пяти магазинам — Ml, М2, МЗ, М4 и М5. Потребности этих магазинов в продуктах равны соответственно 15, 14, 25, 5 и 9 ед. Запасы продуктов на складах С1, С2, СЗ, С4 составляют соответственно 23, 15, 45, 15 ед. Тарифы по доставке продуктов (руб./ед. груза) приведены в таблице:

Таблица 1. Тарифы по доставке продуктов (руб./ед. груза)

Склады

Магазины

М1

М2

М3

М4

М5

С1

10

3

4

5

6

С2

2

1

11

14

3

С3

4

13

3

2

8

С4

5

13

4

12

7

Составьте план перевозок продуктов, при котором суммарные расходы по их доставке будут минимальными.

Так как данная задача относится к задачам линейного программирования, то решение начинается с построения математической модели:

\begin{cases}x_{11}+x_{12}+x_{13}+x_{14}=23 \\ x_{21}+x_{22}+x_{23}+x_{24}=15 \\ x_{31}+x_{32}+x_{33}+x_{34}=45 \\ x_{41}+x_{42}+x_{43}+x_{44}=15 \\ x_{11}+x_{12}+x_{13}+x_{14}=15 \\ x_{21}+x_{22}+x_{23}+x_{24}=14 \\ x_{31}+x_{32}+x_{33}+x_{34}=25 \\ x_{41}+x_{42}+x_{43}+x_{44}=5 \\ x_{51}+x_{52}+x_{53}+x_{54}=9 \\ xij\geq0,i=1,2,3,4,j=1,2,3,4,5\end{cases}

При данном плане перевозок суммарная их стоимость составит (целевая функция):

F=10x_{11}+3x_{12}+4x_{13}+5x_{14}+6x_{15}+

+2x_{21}+1x_{22}+11x_{23}+14x_{24}+3x_{25}+

+4x_{31}+13x_{32}+3x_{33}+2x_{34}+8x_{35}+

+5x_{41}+13x_{42}+4x_{43}+12x_{44}+7x_{45}+

+0x_{51}+0x_{52}+0x_{53}+0x_{54}+0x_{55}\rightarrow min

Дальнейшее решение задачи ведётся в MS Excel.

Составляется таблица с исходными данными (рис. 1)

Исходные данные
Рисунок 1. Исходные данные

Затем заполняем столбец с ограничениями, при помощи функции СУММ (для ячейки Н4 =СУММ (В4:F4)), а формулу СУММПРОИЗВ мы вводим в строке с целевой функцией (=СУММПРОИЗВ (B4:F8;B15:F19)).

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

В поле изменяя ячейки выбираем ячейки с неизвестными переменными (B4:F8), а поле ограничения вводим наложенные нами ограничения.

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

После чего получаем ответ к задаче (рис. 2)

Результаты поиска решений
Рисунок 2. Результаты поиска решений

Решив математическую модель, пришли к выводу, что

из пункта 1-го необходимо поставлять 8 ед. продукции на 2-й склад, из 2-го необходимо 6 ед. на 2-й и 9 на 5-й, из 3-го 15 ед. на 1-й, 25 на 3-й, 5 на 4-й, из 4-го продукции не требуется, при этом затраты на перевозку составят 202 ден. ед.

Как видно из примера, решение задачи с помощью MS Excel быстрое и удобное, так как:

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

Читайте также

Список литературы

  1. Акулич И.Л. Математическое программирование в примерах и задачах: учебное пособие для ВУЗов. - М.: Высшая школа, 2004
  2. Красс М. Математика для экономических специальностей. Учебник. 3-е изд., перераб и доп. М, Экономист, 2004.

Цитировать

Князева, А.А. Применение MS Excel в решение логистических задач / А.А. Князева, Н.П. Лыкова. — Текст : электронный // NovaInfo, 2011. — № 7. — URL: https://novainfo.ru/article/1364 (дата обращения: 16.08.2022).

Поделиться