Линейно програмиране на 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 Options. Следвайте стъпките по-долу, за да активирате Solver в Excel.
Стъпка 1: Придвижете се към менюто Файл и кликнете върху Опции, което е последното нещо в този списък.
Стъпка 2: Ще се появи нов прозорец, наречен Excel Options. Кликнете върху Добавки от списъка с опции в лявата част на прозореца.
Стъпка 3: Под раздел Управление в долната част на прозореца изберете Excel Добавки от падащия списък и кликнете върху бутона Go…, поставен освен.
Стъпка 4: Щом кликнете върху бутона Go…, в нов прозорец ще можете да видите списъка с всички добавки, налични под excel. Отметнете, за да изберете добавката Solver, така че да можете да я използвате в раздела Данни за решаване на уравненията. Щракнете върху бутона OK, след като изберете отметката Solver Add-in.
По този начин можете да активирате Excel Solver под Microsoft Excel.
Метод №2 - Решаване на проблем с линейното програмиране с помощта на Excel Solver
Сега ще се опитаме да разрешим проблема с линейното програмиране с помощта на Excel Solver инструмент.
Пример: Химическо предприятие произвежда два продукта, а именно А и Б. Тези два продукта се нуждаят от суровини, както е показано по-долу: Продукт А се нуждае от три вида суровини - Материал_1 20 кг, Материал_2 30 кг, Материал 3 за 5 кг. По подобни линии, продукт B изисква 10 кг материал Материал_1, 30 кг материал Материал_2 и 10 кг материал на материал_3. Производителят изисква минимум 460KG или Material_1, 960KG от Material_2 и 220KG от Material_3. Ако цената на единица продукт за продукт A е 30 долара, а цената на продукт B е 35 долара, колко продукти производителят трябва да смеси, за да отговори на минималните изисквания за материали с възможно най-ниската цена? Нека използваме информацията, предоставена в този пример за моделиране на уравненията.
Стъпка 1: Можем да видим всички ограничения на уравнението, които можем да формираме, използвайки информацията, предоставена в горния пример.
Стъпка 2: Използвайте тези уравнения, за да добавите ограниченията в клетката под Excel през A2: C8 на даден лист. Вижте екрана, както е показано по-долу:
Стъпка 3: Сега трябва да използваме формулата Количество * на единица цена и да я обобщим за двата продукта, за да получим действителните изисквания за материали. Можете да видите това формулирано в колона D за всички клетки, които съдържат ограничения B3, B4, C3). Моля, вижте приложената екранна снимка по-долу:
Ако ще разгледате по-подробно тази формула, ние използвахме B3 и C3 като фиксирани членове за всяка формула в различните клетки в колона D. Това е така, защото B3 и C3 са клетките, обозначаващи съответно количества за Продукт А и Продукт Б. Тези количества ще се появят, след като системата от уравнения е решена с помощта на Excel Solver.
Стъпка 4: Щракнете върху раздела Данни и след това върху Solver, който присъства в раздел Анализ в раздела.
Стъпка 5: След като щракнете върху Solver, ще се отвори нов раздел, наречен „Параметър на Solver“, под който трябва да зададете параметрите на този набор от уравнения, който ще бъде решен.
Стъпка 6: Първото нещо, което трябва да идентифицираме, е да зададем цел: Тъй като нашата цел е да определим общите разходи, за да може да бъде сведена до минимум, задайте това на D4.
Стъпка 7: Тъй като трябва да сведем до минимум разходите с възможно най-високо производство, задайте следващия параметър като Мин. Можете да направите това, като кликнете върху радио бутона Min.
Стъпка 8: под Промяна на променливи клетки:, трябва да споменем B3 и C3, тъй като тези клетки ще бъдат тези, които съдържат съответно Количество за продукт А и продукт Б, след като проблемът бъде решен.
Стъпка 9: Сега добавете ограниченията. Кликнете върху бутона Добавяне в раздел Обект на ограниченията и той ще отвори нов прозорец за добавяне на ограничения. Под този прозорец - B3: C3 като препратка към клетки, > = и 0 като ограничения. Това правим, тъй като основното ограничение във всеки LPP е, че X и Y трябва да са по-големи от нула.
Стъпка 10: Кликнете отново върху бутона Добавяне и този път използвайте B3: C3 като референция на клетка и F6: F8 като ограничения с неравенство като> =. Щракнете върху бутона OK, за да добавите и това ограничение под решението.
Сега Solver има всички параметри, необходими за решаване на този набор от линейни уравнения и изглежда по-долу:
Стъпка 11: Сега кликнете върху бутона Решаване в долната част на прозореца, за да решите това линейно уравнение и стигнете до оптималното решение.
Веднага щом кликнем върху бутона за решаване, системата започва да търси оптимално решение за проблема, който сме предоставили, и получаваме стойностите за B3, C3, използвайки които получаваме и стойностите в колона F за F4, F6: F8. Кои са оптималните разходи и материални стойности, които могат да бъдат използвани за продукт А и продукт Б.
Това решение ни информира, че ако трябва да сведем до минимум разходите за производство на продукт А и продукт Б с оптимално използване на материал_1, материал_2 и материал_3, трябва да произвеждаме 14 количества продукт А и 18 количества продукт Б.
Това е от тази статия. Нека увием нещата с някои точки, които трябва да се запомнят:
Неща, които трябва да запомните за линейното програмиране в Excel
- Задължително е да се решават проблеми с линейното програмиране с помощта на Excel Solver. Няма друг метод, чрез който можем да направим това.
- Винаги трябва да имаме ограничения и обектна променлива, за да бъдем готови при нас.
- Ако Solver не е активиран, можете да го активирате в опциите за добавки в Excel.
Препоръчителни статии
Това е ръководство за линейно програмиране в Excel. Тук обсъждаме как да използваме линейно програмиране в Excel, заедно с практически примери и шаблон за excel за сваляне. Можете да разгледате и другите ни предложени статии -
- Интерполирайте в Excel
- Програмиране в Excel
- Преместване на колони в Excel
- Обратна матрица в Excel