Лінійне програмування Excel (Зміст)
- Вступ до лінійного програмування в Excel
- Методи вирішення лінійного програмування за допомогою Excel Solver
Вступ до лінійного програмування в Excel
Лінійне програмування є найважливішим, а також захоплюючим аспектом прикладної математики, який допомагає в оптимізації ресурсів (мінімізуючи втрати або максимізуючи прибуток із заданих ресурсів). Якщо у нас є обмеження і чітко визначена цільова функція, ми можемо використовувати систему для прогнозування оптимального рішення даної проблеми. У Excel у нас є Excel Solver, який допомагає нам вирішувати проблеми лінійного програмування, відомі як LPP. У цій статті ми побачимо, як використовувати Excel Solver для оптимізації ресурсів, пов’язаних із бізнес-проблемами, за допомогою лінійного програмування.
Перше, що спочатку. Подивимося, як ми можемо включити Excel Solver (ключовий компонент LPP під Excel).
Методи вирішення лінійного програмування за допомогою Excel Solver
Давайте розберемося, як використовувати лінійне програмування через excel solver за допомогою деяких методів.
Ви можете завантажити цей шаблон Excel для лінійного програмування тут - Лінійне програмування шаблону Excel
Спосіб №1 - Увімкнення Solver в Microsoft Excel
У Microsoft Excel ми можемо знайти Solver на вкладці Дані, який можна знайти на стрічці Excel, розміщеній у верхній частині, як показано нижче:
Якщо ви не можете побачити цей утилітний інструмент там, вам потрібно ввімкнути його через Параметри Excel. Виконайте наведені нижче дії, щоб увімкнути Solver у програмі Excel.
Крок 1: Перейдіть до меню «Файл» і натисніть «Параметри», що останнє в цьому списку.
Крок 2: З'явиться нове вікно з назвою Параметри Excel. Клацніть на надбудови зі списку параметрів, наявних у лівій частині вікна.
Крок 3: У розділі Керування в нижній частині вікна виберіть надбудови Excel зі спадного списку та натисніть кнопку Go…, розміщену поруч.
Крок 4: Як тільки ви натиснете кнопку Go …, ви зможете побачити список усіх надбудов, доступних у програмі excel, у новому вікні. Поставте галочку для вибору надбудови Solver, щоб ви могли використовувати її на вкладці Дані для вирішення рівнянь. Натисніть кнопку ОК після вибору пункту Додаток Solver.
Таким чином, ви можете включити Excel Solver в Microsoft Excel.
Спосіб №2 - Вирішення проблеми лінійного програмування за допомогою програми Excel Solver
Тепер ми спробуємо вирішити задачу лінійного програмування за допомогою інструменту Excel Solver.
Приклад: Хімічний завод виробляє два продукти, а саме А і В. Ці два продукти потребують сировини, як показано нижче: Продукт А потребує трьох видів сировини - Матеріал_1 20 кг, Матеріал_2 30 кг, Матеріал 3 як 5 кг. На подібних лініях для продукту B потрібно 10 кг матеріалу_1, 30 кг матеріалу_2 і 10 кг матеріалу_3. Виробнику потрібно мінімум 460 кг або Матеріал_1, 960 кг Матеріал_2 і 220 кг Матеріал_3. Якщо вартість продукту A становить 30 доларів, а вартість продукту B становить 35 доларів, то скільки продуктів виробник повинен змішати для задоволення мінімальних вимог до матеріалів за найменшої вартості? Давайте використаємо інформацію, представлену в цьому прикладі, для моделювання рівнянь.
Крок 1: Ми можемо побачити всі обмеження рівнянь, які ми можемо сформувати, використовуючи інформацію, подану в наведеному вище прикладі.
Крок 2: Використовуйте ці рівняння для додавання комірок обмежень у Excel через A2: C8 даного аркуша. Дивіться скріншот, як показано нижче:
Крок 3: Тепер нам потрібно використовувати формулу Кількість * За одиницю вартості та підсумувати її для обох продуктів, щоб отримати фактичні потреби в матеріалі. Ви можете бачити це, сформульоване в колонці D для всіх комірок, які містять обмеження B3, B4, C3). Дивіться доданий скріншот нижче:
Якщо ви детальніше ознайомитеся з цією формулою, ми використовували B3 і C3 як фіксованих членів для кожної формули в різних осередках стовпця D. Це тому, що B3 і C3 є клітинками, що позначають величини для продукту A і продукту B відповідно. Ці величини з’являться, як тільки система рівнянь буде вирішена за допомогою Excel Solver.
Крок 4: Перейдіть на вкладку Дані, а потім на Solver, який присутній у розділі Аналіз на вкладці.
Крок 5: Після натискання на Solver відкриється нова вкладка «Параметр Solver», під якою вам потрібно встановити параметри для цього набору рівняння, який потрібно вирішити.
Крок 6: Перше, що нам потрібно визначити, це встановити ціль: Оскільки наша мета полягає в тому, щоб визначити загальну вартість витрат, щоб її можна було мінімізувати, встановіть це на D4.
Крок 7: Оскільки нам потрібно мінімізувати витрати з максимально можливим виробництвом, встановіть наступний параметр як Min. Ви можете зробити це, натиснувши на радіо-кнопку Min.
Крок 8: У розділі Зміна змінних комірок:, нам потрібно згадати B3 і C3, оскільки ці клітини будуть містити Кількість для продукту A і продукту B відповідно після вирішення проблеми.
Крок 9: Тепер додайте обмеження. Натисніть кнопку Додати у розділі "Тема обмежень": це відкриє нове вікно для додавання обмежень. Під цим вікном - B3: C3 як посилання на клітинку, > = і 0 як обмеження. Це ми робимо, оскільки основне обмеження в будь-якому LPP полягає в тому, що X і Y повинні бути більшими за нуль.
Крок 10: Клацніть ще раз на кнопці Додати і цього разу використовуйте B3: C3 як посилання на клітинку та F6: F8 як обмеження з нерівністю як> =. Натисніть кнопку ОК, щоб додати це обмеження також під вирішувачем.
Тепер Solver має всі параметри, необхідні для вирішення цього набору лінійних рівнянь, і виглядає так:
Крок 11: Тепер натисніть кнопку Розв’язати внизу вікна, щоб вирішити це лінійне рівняння і підійти до оптимального рішення.
Як тільки ми натискаємо кнопку вирішити, система починає пошук оптимального рішення заданої нами проблеми, і ми отримуємо значення для B3, C3, використовуючи які, ми також отримуємо значення у стовпці F для F4, F6: F8. Які оптимальні витрати та матеріальні цінності, які можна використовувати для Продукту А та Продукту Б.
Це рішення інформує нас про те, що, якщо нам потрібно мінімізувати витрати на виробництво продукту A і продукту B при оптимальному використанні Material_1, Material_2 та Material_3, ми повинні виробляти 14 кількостей продукту A та 18 кількості продукту B.
Це воно з цієї статті. Давайте обговоримо речі, які слід запам'ятати:
Що слід пам’ятати про лінійне програмування в Excel
- Вирішувати проблеми лінійного програмування обов'язково потрібно за допомогою Excel Solver. Немає іншого методу, яким ми можемо це зробити, використовуючи.
- У нас завжди повинні бути обмеження та змінна об'єкта, щоб бути готовою до нас.
- Якщо Solver не ввімкнено, ви можете ввімкнути його за допомогою параметрів надбудови Excel.
Рекомендовані статті
Це посібник з лінійного програмування в Excel. Тут ми обговорюємо, як використовувати лінійне програмування в Excel разом з практичними прикладами та шаблоном Excel, який можна завантажити. Ви також можете ознайомитися з іншими запропонованими нами статтями -
- Інтерполювати в Excel
- Програмування в Excel
- Переміщення стовпців в Excel
- Зворотна матриця в Excel