ТРАНСПОРТНАЯ ЗАДАЧА. РЕАЛИЗАЦИЯ ПО КРИТЕРИЮ ВРЕМЕНИ В ПРОГРАММЕ MS EXCEL Текст научной статьи по специальности «Математика»

2020

ВЕСТНИК ПЕРМСКОГО УНИВЕРСИТЕТА

Математика. Механика. Информатика

Вып. 4(51)

УДК 51:33(075.8)

Транспортная задача. Реализация по критерию времени в программе MS Excel

Г. О. Иванов

Пермский национальный исследовательский политехнический университет Россия, 614990, г. Пермь, ул. Комсомольский пр., 29 gleb_molodoi5@mail.ru; +7-919-47-55-592

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

Ключевые слова: транспортная задача; критерий времени; симплекс-метод; линейность; Microsoft Excel.

DOI: 10.17072/1993-0550-2020-4-51-56

Введение

Курс дисциплин магистров ПНИПУ, проходящих обучение на кафедре Автоматика и телемеханика, включает в себя исследование операций в системах управления и информационной безопасности [1-4]. В нем, в частности, рассматриваются задачи математического программирования [5]. По целевой функции и функциям ограничения эти задачи делятся либо на линейные, либо нелинейные. Существует перечень классических задач линейного программирования, в котором присутствует транспортная задача, направленная на поиск оптимального плана перевозок по критерию стоимости. Решение такой задачи может быть найдено симплекс-методом [6, 7]. Рассмотрение транспортной задачи по критерию времени переводит ее в разряд нелинейных задач, из-за чего метод решения становится сложнее, а точность ответа снижается.

В работе [8] приводится решение транспортной задачи в Excel по критерию стоимости перевозок.

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

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

В статье [10] описывается решение транспортной задачи на примере практической задачи. Поиск плана перевозок строится в программе Microsoft Excel, однако, дальнейшее решение по получению оптимального плана проводится уже вручную, с помощью разгрузки ячеек.

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

© Иванов Г. О., 2020

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

Целью данной статьи является реализация решения по поиску плана перевозок по критерию времени, производя при этом оптимизацию линейной целевой функции, что позволит использовать в решении линейный симплекс-метод.

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

1. Постановка задачи

Пусть есть m пунктов отправки груза A1..., Am, у которых определен запас товара

a...,®m и n пунктов назначения груза B,...,Bn из которых поступают заявки на эти товары

b...,bn. Суммарное количество заявок соответствует суммарному количеству запасов (1):

Из каждого пункта отправки Д. совершаются перевозки за время T в каждый пункт назначения Б., принимающие целое и

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

перевозки. Это означает, что груз любого объема может быть перевезен за одну операцию.

Балансное условие задается следующим образом (2):

Л

£ Е

т

г = 1ЛЧ" п X

J- 1лч

Xu > а

= а>_

= Ь,

0 = О

1,

= i,

.т) -п)

Я; > 0, bj > О

(2)

Суммарная продолжительность всех перевозок определяется длительностью самой долгой из них, время которой обозначим за Из всех времен Т, соответствующих ненулевым перевозкам, время I соответствует максимальному из них.

Получим формулу (3), где х^ > 0 означает, что если значение перевозки х^ = 0, то для нее Т = 0, т. е. это время не будет рассматриваться при поиске максимального £

(3)

t = шаг^ Tjj r Tjj = min .

Целью задачи является поиск плана оптимальных перевозок (x{-) [11, 12].

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

ДАНО

at bj

5 10 20 15

1 а 10 8 3 5 2

а 15 4 1 6 7

25 1 9 4 3 Tij

Рис. 1. Пример таблицы начальных данных для транспортной задачи

2. Построение таблиц решения

Начнем с построения дополнительной таблицы, в которой будет получен искомый план перевозок (рис. 2). Ячейки x^ отведены

под переменные, которые будут изменять в ходе использования модуля "Поиск решения" в Excel.

1 —-- —8— -С- —0— --— -1- G —!—1—-— -"- --- -u-5—

■1

bj

5 i 5 10 20 J5 (1) 5 10 20 15

fi 1С 8 J 5 2 ID

g 25 1 9 4 3 Tii 1 25

I

Рис. 2. Построение таблицы искомого плана перевозок

Добавленные ячейки отведены под балансное условие (2), которое запишем с помощью оператора СУММ, как показано на рис. 3 и рис. 4 соответственно.

ai bj

5 10 20 15

10 0 0 0

15

25

=СУММ(Ш8) 0 0 I

Рис. 3. Балансное условие для пунктов назначения

Теперь мы можем перейти к построению искомой таблицы, для которой значения

Рис. 4. Балансное условия для пунктов отправления

В транспортной задаче по критерию времени не имеет значения численное равенство ячеек Ху, поскольку все заявки отправляются либо одним транспортом, либо, если заявок нет, то не отправляются совсем. Это подводит нас к необходимости совершить преобразование таблицы искомого плана перевозок к таблице вида "Если Ху > 0, то

х =!; х =0, то х =0".

Для соблюдения условия линейности построение искомой таблицы начнем с промежуточной таблицы, как показано на рис. 5.

Рис. 5. Построение промежуточной таблицы

Значения х. из таблицы с искомым

У

планом перевозок мы уменьшим так, чтобы новые значения гарантированно находились в диапазоне [0;1]. Для этого каждую ячейку заявок х поделим на соответствующую этой

ячейке Ь , (рис. 6) и (рис. 7).

Рис. 6. Формула преобразование ячейки с соответствующим j = l

x,, е

{0;l}, (рис. 8).

Рис. 7. Формула преобразование ячейки с соответствующим j = 4

Рис. 8. Построение преобразованной таблицы искомого плана

В ячейках Х искомой преобразованной

таблицы содержится второй набор переменных, которые будут изменяться в ходе "Поиска решения". Потребовав от этого набора переменных соблюдать условие бинарности, ячейки Х смогут принимать значение только 0 или 1. Чтобы однозначно определить, в какой ячейке должен находиться 0, Х искомой

таблицы должен быть < х таблицы искомого

У

плана перевозок. Чтобы однозначно определить, в какой ячейке должна находиться 1, Х

У

искомой таблицы должен быть > х^ промежуточной таблицы. Таким образом, искомая таблица будет иметь вид "Если х^ > 0, то

х =!; х =0, то х =0".

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

Целевая функция (ЦФ), которая будет оптимизироваться для нахождения искомого плана перевозок, примет следующий вид (4):

I Т.. - .V.. = п^'л, где Л'.. = [0 1]. (4)

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

Как пример, допустим, имеется решение с затратами по времени 4+5+6+8=23 (максимальное время = 8). Оптимизируя данное решение, может возникнуть два варианта затрат по времени: 6+8=14 (макс. = 8) и 4+5+6=15 (макс. = 6). В соответствии с нашей ЦФ (4), лучшим решением будет 6+8=14, но по условию (3), лучшим решением является 4+5+6=15 (макс. = 6).

Оптимизация по минимуму подбирает значения переменных так, чтобы ЦФ приняла наименьшее значение, а если есть ограничения, по которым найденное решение не подходит, то значение ЦФ начинает увеличиваться до тех пор, пока не будет найден наилучший вариант. Исходя из этой логики, мы можем изменить Т..

У

(Ху определяет будет ли считаться Т или нет)

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

Таким образом, оптимизация такой ЦФ была направлена на поиск решения, при котором Т приблизилось к искомому по условию (3)

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

Для нашей транспортной задачи важно сохранять условие линейности, поэтому для значений Т^ из рис. 1 введем следующее переобозначение (5):

г 1 ^ (( +1)1-1 = 1 2^(1+л)2-1 = 12 - 3 - = 1-44 . (5)

Построим таблицу с введенным переобозначением по начальным данным (рис. 9).

СУММ | X ✓ А =12Л( 1)

А В С О Е Р Е н

3

4 5 6 7 8 Время м ЬГ (1)

5 10 20 15

10 8 3 5 2

15 4 1 6 7

25 1 9 4 3 щ

9 Л-

10

11 т Ъ]

12 5 10 20 15

13 10 З.бЕ+07 144 20736 12

14 15 15 1728 1 248832 ЗЕ+06

25 1 4Е+08 1728 =12 Ту

16 Р8-1)

Рис. 9. Построение таблицы с переобозна-

чением Гу

Теперь запишем ПФ (4), как показано на рис. 10.

Рис. 10. Ввод целевой функции

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

3. Параметры модуля поиска решения

Рис. 11. Задание параметров поиска решения

Опишем все строки на рис. 11:

1. "Оптимизировать целевую функцию".

Выбираем ячейку, в которой вводили

ЦФ;

2. "До". Выбираем оптимизацию ЦФ по

минимуму;

3. "Изменяя ячейки переменных". Указы-

ваем первый и второй наборы переменных;

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

• строка 1-3. Балансное условие для а;

• строка 4. Заявки х имеют целое значение;

• строка 5-8. Балансное условие для Ьу ;

• строка 9. Переменные из второго набора, равные 0;

• строка 10. Второй набор переменных должен быть бинарным;

• строка 11. Переменные из второго набора, равные 1.

5. "Выберите метод решения". Использоваться будет метод решения линейных задач, симплекс-метод.

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

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

Рис. 12. Построение таблицы с переобозначением г.

У

Полученная целевая функция на рис. 12 оптимизирована в соответствии с условием (3). Искомое время I = 4.

Вывод

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

Однако данный метод решения сталкивается с двумя ограничениями: за значение времени должно приниматься целое значение и быть достаточно небольшим. Условие целостности влияет на переобозначение, по сути, задавая шаг равный 1 между двумя ближайшими значениями времени. Это оказывает влияние на то, насколько быстро будет расти возведение в степень, что переводит нас к проблеме использования сверхбольших чисел. Достаточно небольшое числовое значение времени обусловлено неспособностью Excel поддерживать линейность для сверхбольших чисел. Используя в приведенной реализации решения возведение чисел 12 в степень, мы очень быстро увеличиваем каждое последующее число.

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

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

1. Тюрин С.Ф., Аляев Ю.А. Дискретная математика: практическая дискретная математика и математическая логика. М.: Финансы и статистика, 2010. 394 с. URL: http: //www .studentlibrary.ru/book/ISBN9785 279034635.html (дата обращения: 10.10.2020).

2. Тюрин С.Ф., Ланцов В.М. Дискретная математика & математическая логика / Перм. нац. исслед. политехн. ун-т. Пермь: Изд-во ПНИПУ, 2013. 271 с. (дата обращения: 12.10.2020).

3. Теория графов и ее приложения. Практикум: учеб. пособие / сост.: С.Ф. Тюрин. Пермь: Изд-во Перм. нац. иссл. политех. ун-та, 2017. 207с. URL: https://elib.pstu.ru/docview/?fDocumentId=35 76 (дата обращения: 13.10.2020).

4. Исследование операций и теория игр. Практикум: учеб. пособие / С.Ф. Тюрин. Пермь: Изд-во Перм. нац. иссл. политех. ун-та, 2017. 220 с. URL:

https: //elib.pstu.ru/docview/?fDocumentId=36 02 (дата обращения: 15.10.2020).

5. "ИНТУИТ', национальный открытый университет. URL:

https: //intuit.ru/studies/course s/1020/188/lectu re/4917 (дата обращения: 21.10.2020).

6. Линейное программирование. Транспортная

задача. Дискретная математика. Теория вероятностей и математическая статистика: учеб. пособие / В.С. Альпина [и др.]. Казань: Казанский национальный исследовательский технологический университет, 2017. 84 c. URL:

http://www.iprbookshop.ru/79316.html. ЭБС "IPRbooks" (дата обращения: 19.10.2020).

7. Литвин Д.Б. Линейное программирование. Транспортная задача: учеб. пособие / Литвин Д.Б., Мелешко С.В., Мамаев И.И. Ставрополь: Ставропольский государственный аграрный университет, Сервис-школа, 2017. 84 c. URL: http://www.iprbookshop.ru/76116.html. ЭБС "IPRbooks" (дата обращения: 17.10.2020).

8. Козлов Е.В. Решение транспортной задачи в Excel: сайт // Мир знаний. 2011. URL: https://smekni.com/a7314009/reshenie-

transportnoy-zadachi-v-excel/ (дата обращения: 23.10.2020).

9. Тынкевич М.А. Потоки в сетях и транспортная задача по критерию времени: сайт // Методические указания и задания к практическим занятиям по курсам "Исследование операций в экономике" и "Экономико-математические методы" 2001. URL: https://studfile.net/preview/416825/ (дата обращения: 218.10.2020).

10. Беккер Р.В., Тимофеева Ю.Н. Решение транспортной задачи в сетевой постановке по критерию времени с применением Ms Excel: сайт // Новая наука как результат инновационного развития общества: сб. статей. 2017. № 1.

URL:https://elibrary.ru/item.asp?id=29287290 / (дата обращения: 23.10.2020).

11. Михин М.Н. Экономико-математические методы. Транспортная задача: учеб. пособие / Михин М.Н., Смирнов В.Е., Белова Т.Б. Москва: Ай Пи Ар Медиа, 2020. 104 c. URL:

http://www.iprbookshop.ru/97413.html. ЭБС "IPRbooks" (дата обращения: 20.10.2020). 12. Вентцель Е.С. Исследование операций: // Исследование операций. 1972. URL: https: //scask.ru/q_book_rop.php?id=26 (дата обращения: 23.10.2020).

Implementation of a transportation linear programming problem by the time criterion in MS Excel

G. O. Ivanov

Perm National Research Polytechnic University; 29, st. Komsomolsky pr., Perm, 614990, Russia gleb_molodoi5@mail.ru; + 7-919-47-55-592

The article describes the implementation of solving a non-classical transport problem by the criterion of time using the linear simplex method in the Microsoft Excel software product. The found implementation shows that the problem can have a linear solution approach, which consists in obtaining the desired transportation plan and optimizing the linear function of the function that determines the total cost over time of all transportation. This approach allows you to get the most accurate answer due to the most common calculation program used in this mode.

Keywords: transportation problem; time criterion; simplex method; linearity; Microsoft Excel.