АвтоАвтоматизацияАрхитектураАстрономияАудитБиологияБухгалтерияВоенное делоГенетикаГеографияГеологияГосударствоДомДругоеЖурналистика и СМИИзобретательствоИностранные языкиИнформатикаИскусствоИсторияКомпьютерыКулинарияКультураЛексикологияЛитератураЛогикаМаркетингМатематикаМашиностроениеМедицинаМенеджментМеталлы и СваркаМеханикаМузыкаНаселениеОбразованиеОхрана безопасности жизниОхрана ТрудаПедагогикаПолитикаПравоПриборостроениеПрограммированиеПроизводствоПромышленностьПсихологияРадиоРегилияСвязьСоциологияСпортСтандартизацияСтроительствоТехнологииТорговляТуризмФизикаФизиологияФилософияФинансыХимияХозяйствоЦеннообразованиеЧерчениеЭкологияЭконометрикаЭкономикаЭлектроникаЮриспунденкция

РОЗВ’язування оптимізаційних задач за допомогою електонних таблиць Excel

Читайте также:
  1. I СИТУАЦИОННЫЕ ЗАДАЧИ ПО ПРОФИЛЬНЫМ РАЗДЕЛАМ
  2. I. МЕТА І ЗАДАЧІ ВИВЧЕННЯ НАВЧАЛЬНОЇ ДИСЦИПЛІНИ
  3. I. ОСНОВНЫЕ ЦЕЛИ, ЗАДАЧИ И ПРИНЦИПЫ ДЕЯТЕЛЬНОСТИ КПРФ, ПРАВА И ОБЯЗАННОСТИ ПАРТИИ
  4. I. Постановка организационных задач предприятия.
  5. I. Цель и задачи изучения дисциплины
  6. II. Розв’язати задачу № 1, 2 (3, 4).
  7. II. ЦЕЛИ И ЗАДАЧИ
  8. II. Цели и задачи Конкурса
  9. II. Цели и задачи учебно-ознакомительной практики
  10. II. ЦЕЛИ, ЗАДАЧИ И НАПРАВЛЕНИЯ ДЕЯТЕЛЬНОСТИ КЛУБА
  11. II. ЦЕЛИ, ЗАДАЧИ, ПРЕДМЕТ И ВИДЫ ДЕЯТЕЛЬНОСТИ ОРГАНИЗАЦИИ
  12. III. Задачи ОЦП

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

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

Таблиця 6.1

 

Ресурс Прод1 Прод2 Прод3 Прод4 Наявність ресурсу
Прибуток          
Трудові 0,03 0,04 0,05 0,04  
Сировина 1,4 1,2   1,9  
Фінанси          

 

 

Таблиця 6.2

 

Вид продукції Потреба
Прод1  
Прод3  
Прод4  

 

 

◆ Розв’язування.

Введемо позначення:

Прод1= x 1, Прод2 = x 2, Прод3 = x 3, Прод4 = x 4, z – прибуток.

Тоді математична модель задачі матиме наступний вигляд:

 

 


z = 2 x 1 + 3 x 2


+ 6 x 3+ 3 x 4


→ max,


0,03 x 1+ 0,04 x 2


+ 0,05 x 3 + 0,04 x 4


≤ 500,


1,4 x 1 + 1,2 x 2


+ 2 x 3 + 1,9 x 4


≤ 26500,


4 x 1+ 6 x 2


+ 4 x 3+ 7 x 4


≤ 10090,


x 1≥ 500,

x 3≥ 200,

x 4≥ 800,

x 2≥ 0.

 

 

Розв’яжемо цю задачу лінійного програмування з допомогою стандартної офісної програми Excel. Спочатку зробимо форму для вводу умов задачі у вигляді таблиці 6.3. Весь текст у подальшому є коментарем і на розв’язок задачі не впливає.

Таблиця 6.3

  A B C D E F G H
        Змінні        
  ім’я Прод1 Прод2 Прод3 Прод4      
  значення              
  нижн. гр.              
  верхн. гр.         ЦФ напр  
  коеф.в ЦФ              
    Обмеження
  вид         ліва част знак права част
  Трудові              
  Сировина              
  Фінанси              
                       

 

 

Другим кроком є введення вихідних даних в форму табл. 6.3 і залежностей математичної моделі. Необхідні дані представлені в табл. 6.1 та 6.2 Спочатку заносимо числові дані, потім залежності математичної моделі. Залежності вводяться таким чином.

 

1. Ввід залежності для цільової функції:

• Курсор в F6 (тут буде формуватися значення цільової функції).

• Курсор на кнопку «Мастер функций», ЛК (ліва клавіша).

• У вікні «Категория» вибираємо курсором категорію

«Математические», ЛК.

• Направляємо курсор у вікно функції «СУММПРОИЗВ», ЛК.

• Далее.

На екрані появляється діалогове вікно «Мастер функций – шаг 2

из 2».

• В «массив 1» ввести B$3:E$3 (в цих комірках будемо формувати значення продукції).

• В «массив 2» ввести B6:E6.

• Готово.

На екрані отримаємо дані у вигляді таблиці 6.4 (в формульному режимі – таблиця 6.5) в F6 буде значення цільової функції.

Таблиця 6.4

  A B C D E F G H
        Змінні        
  ім’я Прод1 Прод2 Прод3 Прод4      
  значення              
  нижн. гр.              
  верхн. гр.         ЦФ напр  
  коеф.в ЦФ              
    Обмеження
  вид         ліва част знак права част
  Трудові 0,03 0,04 0,05 0,04   <=  
  Сировина 1,4 1,2   1,9   <=  
  Фінанси           <=  

 

 

2. Ввід залежностей для лівих частин обмежень:

• Курсор в F9.

• Курсор на кнопку «Мастер функций», ЛК.

• У вікні «Категория» вибираємо курсором категорію «Математические», ЛК.

• Направляємо курсор в вікно функції «СУММПРОИЗВ», ЛК.

• Далее.

На екрані появляється діалогове вікно «Мастер функций – шаг 2 из 2».

• В масив 1 ввести B$3:E$3.

• В масив 2 ввести B9:E9.

• Готово.

Цю ж процедуру можна виконати копіюванням формули з F6 в F9:

• Курсор в F6.

• Курсор на кнопку «Копировать в буфер», ЛК.

• Курсор в F9.

• Вставить из буфера.

Аналогічно копіюємо формулу в F10:F11.

На цьому введення даних закінчується.

Таблиця 6.5

  A B C D E F G H
    Змінні      
  ім’я Прод1 Прод2 Прод3 Прод4      
  значення              
  нижн. гр.              
  верхн. гр.         ЦФ напр  
  коеф.в ЦФ         =СУММПРОИЗВ(B$3:E$3;B6:E6)    
  Обмеження
  вид         ліва част знак права част
  Трудові 0,03 0,04 0,05 0,04 =СУММПРОИЗВ(B$3:E$3;B9:E9) <=  
  Сировина 1,4 1,2   1,9 =СУММПРОИЗВ(B$3:E$3;B10:E10) <=  
  Фінанси         =СУММПРОИЗВ(B$3:E$3;B11:E11) <=  

Далі переходимо до знаходження оптимального розв’язку.

Алгоритм 1. Знаходження оптимального розв’язку.

1. Сервис, Поиск решения...

На екрані: діалогове вікно «Поиск решения».

2. Назначаємо цільову функцію.

Для цього курсор у вікно «Установить целевую ячейку» і вводимо адресу F6.

Вводимо напрямок цільової функції: «Максимальному значению».

3. Вводимо адресу невідомих змінних:

Курсор у вікно «Изменяя ячейки», вводимо адресу B3:E3.

4. Вводимо обмеження:

• Добавить

На екрані: діалогове вікно «Добавление ограничения».

Спочатку вводимо обмеження невід’ємності змінних для Прод2 і мінімального обсягу для решти змінних:

• У вікні «Ссылка на ячейку» вводимо В3.

• Курсор на стрілку, ЛК (ліва клавіша).

• На екрані: «знаки для ввода ограничений».

• Курсор на знак «>=», ЛК.

• Курсор в праве вікно.

• Вводимо В4.

• Добавить.

На екрані знову вікно «Добавление ограничения». Аналогічно вводимо інші обмеження: C3>=C4, D3>=D4, E3>=E4.

Так само вводяться обмеження F9<=H9, F10<=H10, F11<=H11.

• Після вводу останнього обмеження натискаємо ОК.

На екрані: діалогове вікно «Поиск решения з введеными условиями».

З допомогою команд «Изменить» і «Удалить» можна за необхідності коректувати обмеження.

Оптимальний розв’язок знаходимо за таким алгоритмом:

Алгоритм 2. Розв’язування задачі.

1. Після вводу задачі, коли на екрані є вікно «Поиск решения», вибираємо «Параметры».

На екрані: діалогове вікно «Параметры поиска решения».

2. Встановлюємо відмітку «Линейная модель».

3. ОК.

На екрані: діалогове вікно «Поиск решения».

4. Выполнить.

На екрані: діалогове вікно «Результаты поиска решения» з повідомленням про те, що розв’язок знайдено. В комірках F6, В3:Е3 знаходиться оптимальний розв’язок (табл. 6.6).

Максимальний прибуток становить 7135 грн., обсяги виготовлення продукції: Прод1=500, Прод2=0, Прод3=622,5, Прод4=800.

Ресурси використовуються в такій кількості:

• трудові (див. F9) = 78,125;

• сировина = F10 = 3465;

• фінанси = F11 = 10090.

Таблиця 6.6

  A B C D E F G H
    Змінні      
  ім’я Прод1 Прод2 Прод3 Прод4      
  значення     622,5        
  нижн. гр.              
  верхн. гр.         ЦФ напр  
  коеф.в ЦФ              
    Обмеження
  вид         ліва част знак права част
  Трудові 0,03 0,04 0,05 0,04 78,125 <=  
  Сировина 1,4 1,2   1,9   <=  
  Фінанси           <=  
                   

 


1 | 2 | 3 | 4 | 5 | 6 | 7 |

Поиск по сайту:

Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав. Студалл.Орг (0.01 сек.)