МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИ

ДНІПРОПЕТРОВСЬКИЙ ДЕРЖАВНИЙ АГРАРНО-ЕКОНОМІЧНИЙ УНІВЕРСИТЕТ

МЕТОДИЧНІ ВКАЗІВКИ ДЛЯ ВИРІШЕННЯ ОПТИМІЗАЦІЙНИХ ЗАДАЧ ВЕТЕРИНАРІЇ У СЕРЕДОВИЩІ MS EXCEL

для самостійної роботи студентів

з дисципліни Інформаційні технології у ветеринарній медицині

Галузь знань: 11001 Ветеринарія

Спеціальність: 8.11010101 Ветеринарна медицина

ОКР: магістр

Термін навчання: повний

ДНІПРОПЕТРОВСЬК 2014

Укладач канд. техн. наук, доцент Харченко Є.М.

Рецензент доктор екон. наук, професор Васильєва Н К.

У вказівках наведені основні поняття та приклади застосування електронних таблиць для вирішення оптимізацій них задач лінійного програмування у середовищі електронних таблиць MS Excel.

Вказівки призначені для студентів ветеринарних спеціальностей денної та заочної форм навчання. Наведені приклади та завдання для вирішення задач оптимізації сполучення голів сільськогоспо-дарських тварин та визначення оптимального варіанта добового раціону годівлі тварин.

Вказівки призначені для самостійної роботи студентів ветеринарних спеціальностей.

Затверджено на засіданні кафедри інформаційних систем і технологій.

Протокол №1

від 27.08.2014 року

Вирішення оптимізаційних задач лінійного програмування в середовищі

електронних таблиць Excel.

1.Основні зведення про математичне моделювання процесів.

Під моделюванням розуміється відтворення поводження якої-небудь реальної системи на побудованому за визначеними правилами її аналогу.

Математична модель на відміну від фізичної не зберігає геометричної подібності з об'єктом, вона абстрактна.

Економічна модель представляє вираження загальних взаємозв'язків і закономірностей явищ у математичній формі.

Математичне моделювання систем і процесів дозволяє експериментувати, не прибігаючи до досвіду – досить побудувати математичну модель і ”програти” її на персональному комп'ютері. Для цього всі характеристики і властивості досліджуваного процесу треба записати математичною мовою за допомогою математичних символів.

Основні етапи моделювання

1. Вивчення процесу. Приступаючи до моделювання процесу, необхідно вивчити його по літературних джерелах чи у натурі. При цьому виявляються внутрішні характеристики якісних і кількісних сторін процесу і взаємозв'язку між ними.

2. Постановка задачі. На цьому етапі формулюється словесна постановка задачі. Вона повинна бути, чіткою і містити ясні відповіді на питання: що відомо, на які питання необхідно відповісти, яка мета рішення задачі і при яких умовах вона досягається.

3. Побудова математичної моделі. Оптимізаційні моделі представляються задачами досягнення чи мінімуму чи максимуму цільової функції (Z_min(max)). На етапі моделювання ціль задачі, економічні умови, зовнішні і внутрішні зв'язки виражаються в алгебраїчній формі за допомогою економічної символіки. Модель задачі лінійного програмування в загальному виді записується в такий спосіб.

Визначити значення функції мети:

Z_min(max) = C₁X₁ + C₂X₂ + … + C_nX_n

при умовах - обмеженнях :

a₁₁x₁ + a₁₂x₂ + a₁₃x₃ + …. + a_1nx_n <= b₁;

a₂₁x₁ + a₂₂x₂ + a₂₃x₃ + …. + a_2nx_n >= b₂;

a₃₁x₁ + a₃₂x₂ + a₃₃x₃ + …. + a_3nx_n = b₃;

………………………………………….

a_m1x₁+a_m2x₂ + a_m3x₃ + …. + a_mnx_n <= b_m;

де x₁ >=0; x₂ >=0; x₃ >=0; …...; x_n >= 0.

У моделі використані позначення:

x₁, x_2, x₃, ….... x_n – шукані (незалежні) перемінні_.

C₁, C₂, …....Cn - коефіцієнти при шуканих перемінних у критерії оптимальності.

a₁₁, …..., a_1n , a₂₁…...,a_2n, …...,a_m1…....,a_{mn –} техніко-економічні коефіцієнти при перемінних в обмеженнях задачі.

4. Підготовка вхідної інформації і вирішення задачі на комп'ютері.

На цьому етапі вхідна інформація вводиться в комп'ютер і зважується оптимізаційна задача за допомогою стандартних програм наближеними методами (симплекс-метод, метод Ньютона і т.д.)

5. Аналіз результатів рішення задачі. Тут аналізується отримане рішення задачі. У випадку незадовільних чи негативних результатів рішення змінюються вхідні дані (шукаються помилки) і процес рішення повторюється. Підготовку вхідної інформації і рішення задачі в середовищі електронних таблиць можна розділити на шість основних етапів.

1. Завдання таблиці вхідної інформації.

2. Завдання діапазону чарунок для незалежних перемінних.

3. Завдання чарунок і формул для цільової функції.

4. Завдання чарунок і формул для обмежень.

5. Робота в діалоговому вікні Поиск решения.

6. Аналіз рішення задачі.

2.Оптимізація сполучення голів сільськогосподарських тварин

Розглянемо приклад підготовки вхідної інформації і рішення задачі оптимізації сполучення голів сільськогосподарських тварин.

Умова задачі

У господарстві маються наступні кормові ресурси (ц к. ед.): концентрованих - 12000, соковитих - 13500, грубих - 9000 і зелених - 12000.

Визначити, яке поголів'я великої рогатої худоби, свиней і овець дозволить одержати максимум доходу при наявній кормовій базі, витратах ресурсів і доходів в розрахунку на одну голову тварин (табл. 1).

Таблиця 1.

Річні норми годівлі (ц к. од.) і доход (грн.)у розрахунку

на одну голову тварин

1. Складання математичної моделі задачі

Позначимо через х_1, х₂ і х₃ кількість великої рогатої худоби, свиней і овець відповідно.

Складемо систему обмежень.

По річній потребі в концентратах: 10х₁ + 5х₂ + х₃  12000.

По річній потребі в соковитих кормах: 20х₁ + 3х₂ + 2х₃  13500.

По річній потребі в грубих кормах: 10х₁ + 3х₃  9000.

По річній потребі в зелених кормах: 20х₁ + 2х₂ + 2х₃  12000.

Складемо функцію цілі:

Zmax = 200x₁ + 90x₂ + 25x₃

Завдання таблиці вхідної інформації в середовищі Excel.

Вхідна інформація заноситься в робочий лист Excel (Рис. 1).

Так, коефіцієнти при незалежних перемінних в обмеженнях знаходяться в чарунках B4:D7; значення правих частин обмежень - в чарунках B11:B14; коефіцієнти цільової функції - в осередках B8:D8.

Р ис. 1

Завдання діапазону чарунок для незалежних змінних

У розглянутому прикладі для одержання значень незалежних перемінних х_1, х_2, і х₃ обраний діапазон осередків G3:I3.

Завдання чарунки і формули для функції цілі

В чарунці H6 задана формула для цільової функції. Вона відображена на Рис.1. у рядку формул. Тут використовується функція СУММПРОИЗВ. Для вибору цієї функції і роботи з нею випливає:

а) Клацніть на чарунку H6, а потім на кнопці f_x стандартної панелі інструментів. Розкриється діалогове вікно Мастер функций.

б) Виберіть в списку Категории  Математические, а в списку Функции  СУММПРОИЗВ.

в) Натисніть кнопку ОК. Розкриється діалогове вікно СУММПРОИЗВ для введення і визначення сум добутків масивів чисел.

г) Клацніть в поле Массив1 і введіть коефіцієнти функції цілі (діапазон чарунок B8:D8).

д) Клацніть в поле Масив2 і введіть діапазон чарунок G3:I3 з незалежними змінними х_1, х_2, х_3.

е) Клацніть на кнопці ОК.

. Завдання обмежень

У діапазон чарунок B11:B14 введені значення правих частин обмежень. В чарунки С11:С14 введені формули для лівих частин обмежень. Тут також використовується функція СУММПРОИЗВ.

Для її вибору і роботи з нею варто зробити дії, зазначені в п.4.:

Так, в чарунку С11 варто ввести:

= СУММПРОИЗВ(B4:D4;G3:I3)

в чарунку С12: = СУММПРОИЗВ(B5:D5;G3:I3) і т.д.

Робота в діалоговому вікні Поиск решения

Для роботи в діалоговому вікні Поиск решения треба:

а) Виберіть в меню Сервис  Поиск решения. Розкриється діалогове вікно Поиск решения (Рис. 2).

б ) В полі Установить целевую введіть адресу чарунки з цільовою функцією Н6.

в) У поле Равной встановіть перемикач у положення Максимальному значенню.

г) В полі Изменяя ячейку введіть діапазон чарунок з незалежними змінними G3:I3. ( Рис. 2)

Рис. 2.

д) Задайте праві частини обмежень таким чином:

• У діалоговому вікні Поиск решения клацніть на кнопці Добавить. Відкриється

• діалогове вікно Добавление ограничений (Рис. 3.).

Рис. 3.

• В полі Ссылка на ячейку введіть адресу чарунки з обмеженням (у розглянутому прикладі клацніть на чарунку С11). В полі <= ввести необхідний знак обмеження, натиснувши кнопку.

• В полі Ограничение введіть адресу чарунки з правою частиною обмеження, клацнувши на ній мишею (у розглянутому прикладі це чарунка В11).

• Клацніть на кнопці Добавить й аналогічно введіть інші обмеження.

• Тому що в розглянутій задачі потрібно знайти цілі значення незалежних змінних (х_1, х_2, х₃), необхідно додати 3 обмеження, що вказують на цілі значення х_1, х_2, х_3. Для цього в діалогове вікно Добавление ограничений необхідно ввести адресу чарунки з х₁ (G3). Клацніть на кнопці і зі списку, що розкрився, вибрати "целое". Далі клацніть на кнопці Добавить й аналогічно введіть два обмеження, що залишилися.

• Після введення всіх обмежень клацніть на кнопці ОК.

е ) Для установки параметрів пошуку рішення варто клацнути на кнопці Параметры діалогового вікна Поиск решения. Відкриється діалогове вікно Параметры поиска решения (Рис. 4).

• Установіть прапорці в полі Линейная модель і в полі Неотрицательные значения, клацнувши по них мишею.

• У категорії Оценки клацніть в полі Линейная ( Рис. 5).

Рис.4.

Якщо Ви згодні з іншими параметрами, клацніть на кнопці ОК. Відкриється діалогове вікно (Рис. 5).

• ж ) Клацніть на кнопці Выполнить діалогового вікна Поиск решения Після закінчення розрахунку з'явиться діалогове вікно Результаты поиска решения (Рис.5.)

з) Клацніть на кнопці ОК, якщо потрібно зберегти знайдене рішення.

Рис. 5.

и) Клацніть в полі Восстановить исходные значения, якщо потрібно перейти до рішення іншої задачі.

Аналіз рішення задачі

Результати рішення приведені на рис.1 у вигляді незалежних змінних (G3:I3), цільової функції (H6) та додаткових змінних (F11:F14).

• Оптимальний (максимальний) дохід від реалізації тварин складає 237000грн (чарунка Н6)

• Чисельність тварин для одержання максимального доходу складає:

х₁ ( ВРХ) - 0 (чарунка G3)

х₂ (свині) - 1800 (чарунка H3)

х₃ ( вівці ) - 3000 (чарунка I3)

У такий спосіб господарству для одержання максимального доходу вигідно вирощувати свиней і овець.

• Значення обмежень по оптимальному рішенню виконуються цілком і складають 12000 ц к. ед., 11400 ц к. ед., 9000 ц к. ед. і 9600 ц к. ед. відповідно і знаходяться в чарунках С11:С14.

• Значення додаткових змінних S1, S2, S3 і S4 приведені в чарунках (F11:F14). Вони визначаються як різниця між заданими й отриманими значеннями обмежень і показують на резерв кормових ресурсів. Так, концентровані корми використовуються цілком (S1=0). Резерв по соковитих кормах S2 складає 2100 ц к. ед. Грубі корми використовуються цілком (S3=0), а резерв по зелених кормах S4 складає 2400 ц к. ед.

3.Визначення оптимального варіанта добового раціону годівлі тварин.

Умова задачі

Визначити оптимальний варіант добового раціону годівлі дійних корів молочного напрямку в стійловий період при середньому змісті жиру в молоці 3,7 - 3,9%, середньодобовому удої 18 кг, живій вазі корови 450 кг. Господарство має у своєму розпорядженні корми трьох видів: концентровані, грубі (сіно багаторічних трав і солома зернових) і силосні. Мінімально припустима потреба в живильних речовинах, розрахована відповідно до ваги корови, її продуктивність і жирність молока приведені в таблиці. Там же приведено середній зміст основних живильних речовин в одиниці корму з урахуванням виду і структури кормів і собівартість одиниці корму. У добовому раціоні необхідно мати не менш 5 кг сіна.

За критерій оптимальності взято показник мінімальна вартість раціону.

Таблиця 2.

Математична модель задачі.

Позначимо кількість концентрованих кормів (ц) у раціоні через х₁, сіна - через х₂, соломи - х₃, силосу - х₄ та складемо систему обмежень.

1. По змісту в раціоні кормових одиниць: х₁+0,6х₂+0,3х₃+0,2х₄0,13

2. По змісту в раціоні протеїну: 10х₁+4х₂+2х₃+0,8х₄1,2

3. По змісту в раціоні сіна: х₂0,5

Цільова функція: Z=6х₁+2х₂+0,4х₃+0,6х₄

Підготовка вихідної інформації і рішення задачі

З адача вирішена в середовищі електронних таблиць Ехсеl за допомогою меню Сервис=>Поиск решения. Докладний опис підготовки вихідної інформації і рішення задачі в середовищі Ехсе1 приведені в задачі 2.

Рис.6.

Аналіз рішення задачі

У раціон за оптимальним планом входить два види корму - сіно (х₂) в обсязі 5 кг (0,05 ц в чарунці Н4) і солома (х₃) в обсязі 50 кг (0,5 ц в чарунці I4). У цьому випадку дотримані всі обмеження. Причому, умова по змісту протеїну в раціоні використовується цілком (чарунка I9, S=0), а умова по змісту кормових одиниць припускає перевитрату на 5 кг к.ед. (чарунка I8, S = -0.05). Умова по змісту сіна в раціоні використовується цілком (чарунка I10).

Задачі для самостійного рішення

Варіант 1

Визначити оптимальний варіант добового раціону годівлі дійних корів молочного напрямку в стійловий період при середньому змісті жиру в молоці 3,8 - 4,0%, середньодобовому удої 18 кг, живій вазі корови 450 кг. Господарство має у своєму розпорядженні корми трьох видів: концентровані, грубі (сіно багаторічних трав і солома зернових) і силосні. Мінімально припустима потреба в живильних речовинах, розрахована відповідно до ваги корови, її продуктивність і жирність молока, задана. Відомо середній зміст основних живильних речовин в одиниці корму з урахуванням виду і структури кормів і собівартість одиниці корму (табл.3).

Таблиця 3

Варіант 2

Визначити оптимальний варіант добового раціону годівлі стойностільних корів молочного напрямку. Середня жива вага корів 500 кг. Господарство розташовує трьома видами кормів: грубі (сіно багаторічних трав і солома зернових), концентровані і силосні. Мінімально припустима потреба в живильних речовинах задана. Відомі середній зміст основних живильних речовин в одиниці корму і собівартість кормів (табл. 4).