Завдання - Зразок вирішення задачі оптимізації кормового раціону засобами Excel

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

Розглянемо рішення найпростішоїзавдання оптимізації кормового раціонузасобами Excel.

Для вирішення оптимізаційних завдань необхідно, щоб був встановлений інструмент "Пошук рішення", який не встановлюється за стандартної установки MS Office, а лише при вибірковій. Якщо в меню Сервіс відсутній пункт "Пошук рішення", подивіться, може він не актуалізований. Для цього в меню Сервіс - Надбудови встановлюємо прапорець на Пошук рішення (якщо є). Якщо й у Надбудовах відсутня Пошук рішення, цей компонент доведеться встановити додатково(.

На фермі як корм для тварин використовуються два продукти - M і N. Збалансоване харчування передбачає, що кожна тварина повинна отримувати в день не менше 200 ккалорій, причому споживана кількість жиру не повинна перевищувати 14 одиниць. Підраховано, що в 1 кг кожного продукту міститься:

  • у продукті M - 150 ккалорій та 14 одиниць жиру;
  • у продукті N - 200 ккалорій та 4 одиниці жиру.
Розробити максимально дешевий раціон відгодівлі тварин, що відповідає цим умовам, якщо вартість 1 кг продукту М становить 1,5 руб, а 1 кг продукту N – 2,3 руб.

Економіко-математична постановка задачі:

x1 - кількість продукту М раціоні; x2 - кількість продукту N у раціоні.

  • Обмеження за кількістю ккалорій: 150x1+200x2=200
  • Обмеження кількості жиру: 14x1+4x2 =0; x2>=0

Цільова функція - мінімум вартості раціону: 1,5x1+2,3x2->min

Введення вихідних даниху комірки Excel:

зразок

Отже, в коміркиА2таА3вводимо початкові значення x1 та x2 - нулі. В коміркиА4іА5вводимо ліві частини обмежень (спочатку вийдуть нулі), в коміркиВ4таВ5- праві частини відповідних обмежень. У коміркуА6вводимо цільову функцію. Введення вихідних даних завершено.

Послідовністю команд менюСервіс-Пошук рішеннявикликаємо інструмент "Пошук рішення".

кормового

Отже, з використанням червоної стрілки (перехід на робочий лист) Встановлюємо цільову комірку -$A$6Рівною мінімального значення Змінюючи комірки -$A$2:$A$3з використанням кнопки Додати послідовно додаємо три вихідні обмеження. Натискаємо кнопку Виконати.

Після обчислень на робочому аркуші отримали такі результати (див. мал. нижче):

зразок

Під час годування

  • 0,909 кг продукту М та
  • 0,318 кг продукту N
потреби тварини у харчуванні будуть задоволені, у своїй вартість раціону буде мінімальною і становитиме 2,10 крб.