Овсієнко Ю.І., Боровик О.Ю. РОЗРАХУНОК ОПТИМАЛЬНОГО ПЛАНУ ПОСІВУ СІЛЬСЬКОГОСПОДАРСЬКИХ КУЛЬТУР ЗАСОБАМИ MS EXCEL

Овсієнко Юлія Іванівна
кандидат педагогічних наук, доцент, Полтавська державна аграрна академія, ovsienkoyulia@online.ua
Боровик Олена Юріївна
студентка інженерно-технологічного факультету, Полтавська державна аграрна академія

РОЗРАХУНОК ОПТИМАЛЬНОГО ПЛАНУ ПОСІВУ СІЛЬСЬКОГОСПОДАРСЬКИХ КУЛЬТУР ЗАСОБАМИ MS EXCEL

Соціально-економічні зміни в агропромисловому комплексі України, перехід до ринкових відносин вимагають від сучасного фахівця-аграрія нових методів господарювання, спрямованих на оптимізацію використання наявних ресурсів і покращення соціально-економічних показників. Особливістю діяльності аграрних підприємств є велика кількість зовнішніх факторів, що впливають на прийняття рішень, націлених на отримання максимальних прибутків за умови мінімальних витрат на виробництво. Використання сучасних методів оптимізації діяльності підприємств у аграрному секторі економіки практично неможливе без застосування економіко-математичних, аналітичних, імітаційних й інших моделей оптимізації та прийняття управлінських рішень.
Побудова економіко-математичних моделей – складний процес, що вимагає від майбутнього фахівця-аграрія змістовних знань економічної теорії і предмету дослідження, володіння математичним апаратом і програмними засобами як інструментарієм для розв’язування задач прикладного змісту, володіння інформаційно-комунікаційними технологіями.
Застосування економіко-математичного моделювання виробничо-фінансових процесів у сільському господарстві досліджували науковці: Р. Белман, В. Вітлінський, Г. Іноземцев, В. Козирський, Л. Забуранна, Н. Попрозман, Н. Клименко, О. Попрозман, С. Забуранний, І. Поспєлова, Д. Форестер, Дж. Франс, Дж. Х. М. Торнли, Г. Чабан та ін.
Оптимальність – властивість, що забезпечує найбільшу відповідність даному завданню або умовам. Оптимальний – найкращий із можливих варіантів, найвідповідніший заданому завданню або умовам. Оптимізація – процес надання найвигідніших характеристик і співвідношень [4].
Таблиця 1.
Площі ділянок, площі культур, врожайність культур на ділянці
Площа культур,
сотень га Площа ділянок, сотень га




S-ділянок
S-культур 6 4 2 7

8 21 30 29 25

3 16 28 26 20

7 18 29 25 21
Завданням роботи є розрахунок оптимального розміщення ділянок сільськогосподарських культур засобами MS EXCEL із використанням математичної моделі транспортної задачі (ТЗ).
У таблиці (табл. 1) представлена умова наступної задачі: у господарстві є чотири земельні ділянки відведені під посів трьох видів зернових культур; відомі показники врожайності культур на кожній із ділянок. Необхідно визначити план посіву вказаних культур, що забезпечить максимальний валовий збір зерна.
Використаємо загальноприйняті позначення: – площа i-ї ділянки (у сотнях га (ст. га)), – площа, відведена під j-ту культуру (ст. га), – урожайність і-ої культури на j-ій ділянці ( ; ) (центнерів із сотні гектар (ц/ст. га)), – площа (ст. га), запланована під посів і-ої культури на j-ій ділянці ( ; ).
Згідно цих позначень, валовий збір зерна (у ст. га) виражає цільова функція, що має наступний вид [2, с. 42-44]:
. (1)
При цьому, за умовою задачі, відносно невідомих , , мають виконуватися такі обмеження:
, , (2)
, . (3)
Співвідношення (1)-(3), що є відомою математичною моделлю ТЗ, у розгорнутому вигляді мають наступний вид, відповідно:

;
(4)

(5)

Вираз (4) – цільова функція – виражає валовий збір зерна (ст. ц). Ліві частини перших трьох обмежень у (5) – посівні площі відповідних культур; ліві частини наступних чотирьох обмежень (5) – посівні площі по окремих ділянках. Останні дванадцять обмежень (5) відображають той факт, що площі земельних ділянок можуть бути лише невід’ємними.
За умовою задачі загальна площа ділянок не дорівнює загальній площі культур . Отже, дана ТЗ є відкритою.
Класична математична постановка ТЗ має наступне формулювання: скласти оптимальний план перевезення деякого однорідного вантажу з пунктів відправлення до пунктів призначення так, щоб загальна вартість перевезень була мінімальною [1, с. 39]. Щоб звести нашу задачу до такого формулювання, перейдемо від задачі на максимум до еквівалентної задачі на мінімум. Для цього помножимо на (–1) цільову функцію (4). При цьому обмеження (5) зберігаються. У результаті одержимо наступний вираз для оптимізації:

(6)
Для відшукання розв’язку одержаної задачі скористаємося алгоритмом пошуку розв’язку ТЗ відкритого типу за допомогою надбудов MS EXCEL [1, с. 43-45].


а) б)
Рис. 1. Робочий аркуш MS EXCEL перед запуском надбудови «Поиск решения» (а); фрагмент діалогового вікна «Поиск решения» (б)

Робочий аркуш MS EXCEL для розв’язування ТЗ відкритого типу має наступну структуру: 1) числові дані, що відповідають умові задачі розміщені у комірках, окреслених рамками (рис. 1, а); 2) імена діапазонів комірок, що обрамлені лініями: В3:В5 – «Площі_культур» – площі кожного з трьох видів зернових культур; G3: G5 – «Врожайність_ділянок» – сумарні врожайності кожної ділянки; С2: F2 – «Площі_ділянок» – наявні площі земельних ділянок; С6: F6 – «Врожайність_культур» – сумарні врожайності кожного виду зернових культур; С3: F5 – «Врожайність_культур_ділянок» – врожайності культур на кожній із ділянок; С10: F12 – «План» – оптимальний план посіву кожного із трьох видів зернових культур на кожній земельній ділянці; В7 – «ЗагВрожКульт» – загальна врожайність культур; Н2 – «ЗагВрожДіл» – загальна врожайність ділянок; G6 – «Валовий_збір» – валовий збір зернових культур, що відповідає цільовій функції ТЗ (6) (рис. 1, а; 2); 3) розрахункові формули розміщені у відповідних комірках (рис. 2); 4) за умовою , що відповідає відкритому типу ТЗ, для якого введено обмеження у діалоговому вікні «Поиск решения» (рис. 1, б); 5) результати обчислень площ посіву кожної культури, на окремих ділянках щоб валовий збір зерна був мінімальним (рис. 3) [3, с. 30-41].


Рис. 2. Формули робочого аркуша

Результатом розв’язування задачі є мінімальний валовий збір зернових культур. Врахувавши, що з метою спрощення міркувань для розв’язування ТЗ відкритого типу на відшукання мінімуму цільової функції [1, с. 43-45] було введено еквівалентну функцію (6) приходимо до висновку, що максимальний валовий збір із чотирьох ділянок трьох видів зернових культур становить 435 ц/сот. га.


Рис. 3. Результати обчислень надбудови «Поиск решения»

Згідно виконаного розрахунку, оптимальний план посіву зернових культур буде таким: доцільно 1 га першого виду культур висіяти на І-ій ділянці і 7 га на IV-ій; другого виду культур посіяти 1 га на ІІ-ій ділянці і 2 га на третій; третього виду – 4 га на І-ій ділянці, 3 га на ІІ-ій ділянці. Таким чином, згідно умови задачі план посіву трьох видів культур виконано повністю, а щодо площ посіву, то 1 га І-ої ділянки залишився не використаним під посів зернових, що не суперечить умові задачі.
На практиці для розв’язування прикладних задач рідко використовують класичний алгоритм ТЗ, оскільки її економіко-математичну модель складно застосовувати без додаткових міркувань і обмежено, зокрема: 1) розподіл здійснюється між об’єктами як неоднорідних товарів, так і різними видами транспортних засобів, урахування чого вимагає формулювання додаткових умов, що ускладнює алгоритм розв’язування ТЗ; 2) допускається, що витрати на транспортування (виробництво, розподіл продукції й ін.) лінійно залежать від обсягів перевезень, тоді як подібні зв’язки, як правило, є нелінійними або стохастичними; 3) умови додаткових обмежень можуть полягати, наприклад у врахуванні тривалості виконання заданих обсягів робіт (перевезення сировини або продукції, що має обмежений термін придатності); оптимізація тривалості і ресурсів на проведення військових операцій тощо). Урахування зазначених додаткових обмежень (1-3), що є складовими умов і вимог задач прикладного змісту розв’язування яких доцільно здійснювати згідно алгоритму ТЗ засобами інформаційно-комунікаційних технологій є перспективою подальших досліджень.
Відшукання оптимальних планів виробництва, продажу, закупівель, перевезень, агрегатного планування, управління запасами й проектами, організація роботи й оцінка ефективності систем масового обслуговування, розподіл сільськогосподарських культур на земельних ділянках із різною врожайністю – це далеко не повний перелік застосування алгоритму ТЗ. А використання, наприклад, засобів MS EXCEL, дозволяє застосовувати готові алгоритми й розроблені класичні математичні моделі до вирішення виробничих задач, аналізу результатів господарської діяльності, прогнозування й прийняття управлінських рішень. Фахівець-аграрій має знати як класичну економіко-математичну модель адаптувати до розв’язування задач прикладного змісту, як перекласти виробничу проблему на мову математики і комп’ютерної техніки, як визначити вхідні й вихідні параметри, вміти аналізувати й приймати рішення.
Список використаних джерел
1. Зюков М. Є. Оптимізація засобами EXCEL : [навч. посіб. для студ. економ., інженер. та технолог. спец.] / М. Є. Зюков, Л. О. Флегантов. – Полтава : РВВ ПДАА, 2002. – 64 с.
2. Колчин А. Д. Математическое программирование : [методические указания] / А. Д. Колчин, Г. И. Новиков. – М. : Редакционно-издательский отдел Всесоюзного сельскохозяйственного института заочного образования, 1984. – 52 с.
3. Шадрина Н. И. Решение задач оптимизации в Microsoft Excel 2010 : учеб. пособие / Н. И. Шадрина, Н. Д. Берман ; [науч. ред. Э. М. Вихтенко]. – Хабаровск : Изд-во Тихоокеан. гос. ун-та, 2016. – 101 с.
4. Оптимальність – Вікіпедія [Електронний ресурс] // Оптимальність. – Режим доступу : https://uk.wikipedia.org/wiki/ (дата звернення 02.02.2018 р.). – Назва з екрана.