Рішення одноіндексних задач математичного програмування за допомогою надбудови «Пошук рішення» MS Excel 2010
Перш за все складемо таблицю параметрів оптимізаційної моделі в MS Excel (рис.4.5). В даному випадку під параметрами розуміються цільові коефіцієнти, коефіцієнти при КЗ в обмеженнях, праві частини обмежень.
Рис. 4.5 – Таблиця параметрів оптимізаційної моделі в Microsoft Excel
Другим етапом буде створення розрахункової таблиці (рис. 4.6). Для цього додамо рядок із назвою «Значення КЗ» до таблиці (рис. 4.5). Потім скопіюємо рядки з 7 по 12 включно, таблиці параметрів оптимізаційної моделі. Звільнимо створені рядки від числових даних. Далі у комірці В14 помножимо комірку В13, що відповідає змінній
=B$13*B7. (4.19)
Далі формулу (4.19) копіюємо, шляхом «протягування» у діапазон комірок В14: С19. У комірці D14 сумуємо відповідний рядок:
=СУММ(B14: C14). (4.20)
Потім формулу (4.20) копіюємо для діапазону комірок від D14 до D19. Закінчити введення формул необхідно виділенням комірки D19, що відповідає результуючому значенню ЦФ. В результаті визначеної вище послідовності дій, ми отримали розрахункову таблицю (рис.4.6) придатну для використання надбудови «Пошук рішення» Microsoft Excel.
Рис. 4.6 – Розрахункова таблиця
При виділеній комірці D19, в якій буде визначено максимальне значення ЦФ, необхідно активізувати надбудову «Пошук рішення». Для цього в MS Excel 2010 необхідно відкрити стрічку «Дані» та обрати «Пошук рішення» (рис. 4.7). При першому використанні надбудови, необхідно винести «Пошук рішення» на стрічку «Дані». Робота надбудови «Пошук рішення» починається із відкриття відповідного діалогового вікна (рис. 4.7).
Рис. 4.7 – Активізація надбудови «Пошук рішення» в MS Excel 2010
Рис. 4.8 – Діалогове вікно надбудови «Пошук рішення» в MS Excel 2010
У полі «Оптимізувати цільову функцію» за умовчанням вказана комірка $D$19 тому, що вона була виділена підчас запуску надбудови «Пошук рішення». У іншому випадку комірку у якій буде записане оптимальне значення цільової функції необхідно вказати вручну. В умовах нашої задачі напрямок оптимізації – максимізація, що і має бути відображено в діалоговому вікні. У вікні «Змінюючи комірки змінних» необхідно відмітити комірки які відповідають керованим змінним: $B$13: $С$13. Для додавання обмежень у вікно «У відповідності із обмеженнями» необхідно натиснути кнопку «Додати». У діалоговому вікні, яке з’явилося, необхідно ввести обмеження та натиснути кнопку «Додати» (рис.4.9). Також для нашої задачі необхідно обрати метод рішення – «Пошук рішення лінійних задач симплекс-методом» (рис. 4.8). Після заповнення усіх необхідних вікон в діалоговому вікні надбудови «Пошук рішення» натискають «Знайти рішення» (рис. 4.9). Якщо оптимальне рішення знайдене – з’явиться діалогове вікно (рис. 4.10) в якому необхідно виділити типи звітів: результати, стійкість, межі, після чого натиснути ОК. У таблиці (рис. 4.6) з’являться результати розрахунків (рис. 4.11), а також додадуться вказані звіти на окремих аркушах (рис. 4.12-4.14).
Рис. 4.9 – Діалогове вікно «Додавання обмеження»
Рис. 4.10 – Діалогове вікно «Результати пошуку рішення»
Рис. 4.11 – Результати рішення задачі
Рис. 4.12 – Звіт по результатах
Рис. 4.13 – Звіт по стійкості
Рис. 4.14 – Звіт по межах
4.5. Запитання для висновки по результатам рішення задачі оптимізації закупівель Спираючись на лекційну частину курсу за звітами по результатам, стійкості, межам необхідно дати економічну інтерпретацію отриманому рішенню та проаналізувати його чутливість. Для цього потрібно відповісти на наступні питання: 1. Які об’єми закупівель сировини у кожного із постачальників забезпечують підприємству максимальний прибуток за квартал при заданих обмеженнях? При заданих обмеженнях максимальний квартальний прибуток підприємству (94, 8 тис. грн.) забезпечать закупівлі сировини у першого постачальника в обсязі 2, 222 т, а у другого – 11, 222 т. 2. Які обмеження визначають максимальний прибуток підприємства? Максимальний прибуток підприємству забезпечують обмеження по максимальній реалізації продукції В та С. 3. Вказати кількість продукції кожного виду, яка буде забезпечувати максимальний прибуток підприємству? Максимальний прибуток підприємству буде забезпечувати виготовлення продукції в наступних обсягах: А – 1, 288 т, В – 2, 8 т, С – 4, 6 т, D – 1, 9 т, E – 2, 917 т. 4. Визначити попит на яку із видів продукції доцільно маркетинговими засобами намагатися збільшити в першу чергу при інших рівних економічних умовах? Перш за все доцільно збільшувати попит на продукцію В, що дасть найбільший приріст прибутку підприємства. 5. Вказати, закупівлі у якого із постачальників є збитковими, якщо такий був визначений при рішенні задачі. При умовах даної задачі, закупівлі сировини у обох постачальників увійшли в дане оптимізаційне рішення, тобто вони є прибутковими. 6. Вказати межі у яких можуть змінюватись праві частини обмежень так, щоб при незмінних інших числових характеристиках оптимізаційної моделі максимальне значення ЦФ залишалося не змінним. При усіх інших незмінних числових характеристиках оптимізаційної моделі максимальне значення ЦФ залишиться незмінним, якщо праві частини обмежень не вийдуть за наступні рамки:
7. Вказати межі у яких можуть змінюватись цільові коефіцієнти так, щоб при незмінних інших числових характеристиках оптимізаційної моделі максимальне значення ЦФ залишалося незмінним. При усіх інших незмінних числових характеристиках оптимізаційної моделі максимальне значення ЦФ залишиться незмінним, якщо цільові коефіцієнти не вийдуть за наступні рамки:
Питання позначені курсивом, а приклади відповідей на них за умов розглянутої задачі наведені під питаннями. Приклад виконання лабораторно-практичної роботи наведено в додатку 4.
|
на комірку В7, тобтона відповідний їй коефіцієнт в обмеженні на максимальну реалізацію продукції А, причому комірка В7 записується, як відносна, а В13 як змішана із зафіксованим рядком:
