CC BY
51
УДК 378.14;355.235 DOI: 10.30977/BUL.2219-5548.2018.83.0.74
РОЗВ'ЯЗАННЯ ЗАДАЧ ЛОГ1СТИКИ ЗАСОБАМИ ТАБЛИЧНОГО ПРОЦЕСОРА
EXCEL
Лабенко Д.П., ХНАДУ
Анотаця. У cmammi розглядаеться питання використання електронног таблицi Excel для побудови моделi та розв 'язання задачi про розробку плану перевезення зерна при мтмальних затратах автомобтьним транспортом iз конкретних зерносховищ до конкретних користува-4ie, за наявностi вартостi перевезення 1 тонни зерна мiж постачальниками та споживачами.
Ключов1 слова: лттне програмування, електронна таблиця, модель, цтьова функщя, об-меження, матриця перевезень, план перевезень.
Вступ
Формування знань та умшь у галузi лопс-тики на автомобшьному транспорт е важли-вою складовою тдготовки висококв^фшо-ваних фахiвцiв. На сьогодшшнш день фахiвцю постiйно необхщно вирiшувати задачу управлiння перемщенням рiзних вантажiв мiж об'ектами та доставки 1х до конкретних споживачiв (управлiння транспортними потоками). Особливо це актуально в наших реаль ях: стан економши, автомобiльних дорк-, транспортних засобiв тощо.
При ухваленш рiшень у процесi розв'я-зання задач управлiння транспортними потоками вщповщальш особи (управлiнцi) в основному покладаються, головним чином, на свою штущда. Хоча штущш, особливо дос-вiдчених управлiнцiв, мае велике значення, вона за визначенням позбавлена ращональ-ного аналггичного начала. Керуючись при ухваленнi рiшень виключно шту!щею, управлiнець може робити висновки тшьки iз кiнцевих результатiв рашше ухвалених рi-шень, а таке навчання дуже дорого обходиться. Тому основною задачею тдготовки вiдповiдних фахiвцiв е, як один iз варiантiв, теоретичне обгрунтування та практичнi на-вички створення i використання моделей ль нiйного програмування i доступних засобiв 1х реалiзацil за допомогою 1Т-технологш, що дозволить автоматизувати процес ухвалення рiшень. Експертний аналiз одержаних результата моделювання створюе можливють вибору найкращого iз запропонованих ва-рiантiв [1, 2].
Створення та використання моделей ль нiйного програмування i доступних засобiв 1х реалiзацil за допомогою рiзних пакетiв прикладних (спещальних) програм дозволяе автоматизувати процес ухвалення рiшень для
вибору найкращого Bapiarna та допомогти фaхiвцю у вибоpi даного piшення.
Аналiз публжацш
На сьогоднiшнiй день icHye досить багато публшацш щодо розв'язання задач лшшного програмування з використанням методiв лiнiйного програмування piзними програм-ними засобами та пакетами прикладних програм [1, 2]. Але майже yci приклади побудо-ви моделей та розв'язання поставлених задач мають абстрактний шдхщ, не прив'язаний до конкретно!' галузг
Уci моделi лiнiйного програмування ма-ють двi зaгaльнi основш оcобливоcтi: перша
- нaявнicть обмежень, друга - у кожнш мо-делi лiнiйного програмування icнye единий покажчик ефективноcтi, який необхiдно мь нiмiзyвaти, або мaкcимiзyвaти.
Засоби Microsoft Office (електронна таблиця Microsoft Excel) дозволяе створювати тaкi моделi лiнiйного програмування та розв'язувати зaдaчi yпpaвлiння i планування для транспортних систем.
Мета i постановка завдання
Розглянемо задачу. Три зерносховища знаходяться на базах збертання (елеваторах) в мютах Миколаев^ Хapковi та Херсош, на яких збеpiгaeтьcя 715, 800 та 560 т зерна вщ-повщно. Це зерно повинно бути поставлене в таю мюта у вщповщнш кiлькоcтi: в м. Одесу
- 500 т, в м. Вшницю - 340 т, у м. Львiв -620 т, у м. Суми - 615 т.
Необхщно розробити план перевезення зерна iз конкретних зерносховищ до конкретних коpиcтyвaчiв. Вартють Cij перевезення 1 тони зерна мiж постачальниками та споживачами задаш у виглядi табл. 1.
План перевезень розробити з мшмальною вартютю затрат.
Таблиця 1 - Варт1сть перевезення 1 тонни зерна мiж постачальниками та споживачами
Математична модель задачi та ii розв'язання в Excel
Хай змшна Xj= 1, якщо i-м автомобшем перевозиться j-й вантаж, i Xj=0, якщо i-м автомобшем не перевозиться j-й вантаж. Тодi модель матиме наступний вигляд: мiнiмiзувати цiльову функцiю
z = I ICX; (!)
¿=1 ;=1
при обмеженнях:
I Xj = 1, i £ [1,4]; (2)
i=1
I X = 1 j £[1,4]; (3)
j=1 (3)
Xj £ {0,1}, i £ [1,4], j £ [1,4].
Для розв'язання ще! задачi використаемо табличний процесор Excel iз комплексу MS Office. Для цього:
• створюемо таблицю тарифiв та запаав: «Тарифи перевезень» та «План перевезень» (рис. 1).
А В С D Е F G
1 Таблиця тарифш тазапасш
2 Та рпфп переЕезення
3 Одеса ВшНИЦЯ Львш Суми Запаси
4 5 Мнкола1в 80 95 130 120 715
Харгав 100 110 120 90 800
6 Херсон 100 105 145 115 560
7 8 Потреби 500 340 620 615 Усього
Усього
9
10 План перевезень
11 Одеса Вшниця Львш Суми Запаси Використано
12 Миколгив 715
13 Харюв 800
14 15 16 17 Херсон 560
Потреби 500 340 620 615
Задоволено
Цшьова функщя
Рис. 1. Вихщш данi задачi
У таблицi «Тарифи перевезень» потрiбно записати вихiднi числовi данi, а в таблицi «План перевезень» необхщно визначити ко-
мiрки, в яких будуть записуватися шукаш результати (B12:E14), продублювати стовп-чики Запаси та Потреби, а також для зруч-носп роботи з таблицею додати стовпчик Використано та рядок Задоволено;
• тдрахуемо загальну суму Запаав (в комiрцi F8) та Потреб (в комiрцi G7). Напри-клад, для Усього Потреб в комiрку G7 слiд записати формулу
= СУММ(B7 : E7)
• задаемо формули для обчислення кшь-костей одиниць товару, яю були перевезет вщ кожного окремого постачальника. Для визначення кшькосп перевезених товарiв з м. Миколаева в комiрку G12 потрiбно запи-сати формулу
= СУММ(B12 : E12).
Аналопчно необхщно обчислити даш для мют Харкова та Херсона;
• обчислюемо кiлькiсть одиниць товарiв, якi отримали окремi споживачi. Так для того, щоб обчислити кшьюсть одиниць отримано-го товару м. Одесою, в комiрку В16 потрiбно ввести формулу
= СУММ(B12: B14)
Аналопчно необхщно обчислити даш для шших мiст-споживачiв;
• для задання цшьово! функци необхiдно врахувати, що загальна вартiсть усiх перевезень, яю необхiдно виконати для того, щоб доставити товар ушх постачальниюв до кожного споживача, вщповщае сумi добуткiв вiдповiдних тарифiв на перевезену кшьюсть товару. Тому введемо цшьову функцiю (1) загально! вартосп перевезень до комiрки F17 у виглядк
= СУММПРОИЗВ (B4 : E6;B12 : E14).
Використовуючи можливють Excel Поиск решения..., на цшьову функцда накладають-ся обмеження (2, 3).
Для цього:
■ виконати команду Данные ^ Поиск решения... i заповнити дiалогове вiкно засобу Поиск решения (рис. 2, адреси комь рок вщповщають нашому варiанту побудови таблиць):
Постачаль-ник Тарифи перевезення
Одеса Вшниця Льв1в Суми
Микола!в 80 95 130 120
Харшв 100 110 120 90
Херсон 100 105 145 115
Рис. 2. Вшно Поиск решения для введення обмежень
- у вшш «Оптимизировать целевую функцию»: вказати адресу комiрки, де записана цшьова функщя - $F$17 ;
- у вшш «Изменяя ячейки переменных»: вказати штервал адрес комiрок, де знаходиться план перевезень - $В$12:$Е$14;
- в областi «В соответствии с ограничениями»: необхщно послщовно додати на-ступнi обмеження
$В$12:$Е$14 = целое $В$12:$Е$14 >= 0 $В$15:$Е$15 = $В$16:$Е$16 $F$12:$F$14 = $G$12:$G$1
- у вiкнi «Выберите метод решения»: вибрати «Поиск решения линейных задач симплекс-методом».
^ тсля натискання кнопки Найти решение зашб Поиск решения знайде опти-мальне ршення (рис. 3):
__А I В I С I Р Е Р е
1 _Таблнця тарифов та запасш
2 Та рифи перевезення
3 Одеса Бшниця Львш Суми Запаси
4 Мнколав 80 95 130 120 715
5 Харюв 100 110 120 90 800
6 Херсон 100 105 145 115 560
7 Потреби 500 340 620 615 Усього 2075
8 Усього 2075
9
10 План перевезень
11 Одеса Вшниця Львш Суми Запаси Бикористано
12 Миколаш 500 0 215 0 715 715
13 Харюв 0 0 292 508 800 800
14 Херсон 0 340 113 107 560 560
15 Потреби 500 340 620 615
16 Задоволено 500 340 620 615
17 Цшьова функщя 213100
Рис.3. Результати розв'язання задачi про призначення
Висновки
Таким чином, використовуючи методи ль нiйного програмування за допомогою елект-ронно! таблицi Microsoft Excel пакета Microsoft Office, можна досить просто i шви-дко створювати лiнiйнi моделi та одержувати результати розв'язання транспортних задач у разi прийняття вiдповiдних рiшень.
Л^ература
1. Мур, Джеффри, Уэдерфорд, Лари и др. Эконо-
мическое моделирование в Microsoft Excel, - 6-е изд.; пер. с англ. - М.: Издательский дом «Вильямс», 2004. - 1024 с. : ил. - перевод с англ.
2. Лабенко Д.П. Використання середовища Excel
для розв'язання задач про призначення // Систематика, мехатрошка, телематика дорожшх машин i систем у навчальному процеа та наущ: зб1рник наукових праць за матер1алами м1жнародно! науково-практично! конференцп 16 березня 2017 р. - Харюв: ХНАДУ, 2017. -С. 44-47.
References
1. Mur, Djefri, Uederford, lari s dr. Economitcheskoe
modelirovanie v Microsoft Excel, 6-е izd.: Per. s ang. М.: Izdatelskij dom "Viljams", 2004. — 1024 с.
2. Labenko D.P. (2017). Vicoristanja seredovictha
Excel dlja rozvjazannja zadachi pro priznatchennja. Sistematika, mexatronika, telematika dorognix mashin I system u navchalnomu procesi ta nautchi. Zbirnik naurovix pratch za materialami mignarodnoji naukovo-practichnoi konferenzii 16 bereznja 2017 r. m.Kharkiv, XNADU, 44-47.
Лабенко Дмитро Петрович, к.т.н., доцент,
Харк1вський нацюнальний автомобшьно-
дорожнш ушверситет,
вул. Ярослава Мудрого, 25,
м. Харюв, 61002, Укра!на,
телефон +38 097-654-40-28,
labenko. 56@gmail .com
Solving logistic tasks with the tools of the tabular processor Excel
Labenko D.P.
Abstract. Goal. The article aims at solving the problem of using table processor Microsoft Excel for building a model of developing a plan for grain crop transportation using motor transport with minimum expenditures. Grain crop is transported from specific grain crop storage to specific users. The cost of transportation of 1 ton of grain crop from suppliers to consumers is set. Problem. Almost all examples of building such models and solving such tasks have an abstract approach, which doesn't deal with any branch of science, manufacturing or education. Methodology of solving such a problem is in using
methods of linear programming, which take into consideration two common basic features. The first feature is presence of restrictions. The second feature is that each model of linear programming has a single index, which must be minimized or maximized. Originality. Originality is in one of the Excel's possibilities of solving problems of linear programming - finding a solution. Practical value is in possibility of building linear models very simply and fast and getting the results of solving transportation problems by making appropriate decisions.
Key words: linear programming, spreadsheet, model, objective function, restriction, transportation matrix, transportation plan.
Решение задач логистики средствами табличного процессора Excel
Лабенко Д.П.
Аннотация. В статье рассматривается вопрос использования электронной таблицы Excel для построения модели и решения задачи разработки плана перевозки зерна при минимальных затратах автомобильным транспортом из конкретных зернохранилищ к конкретным пользователям, при наличии стоимости перевозки 1 тонны зерна между поставщиками и потребителями.
Ключевые слова: линейное программирование, электронная таблица, модель, целевая функция, ограничение, матрица перевозок, план перевозок.
