Lineární programování v Excelu (obsah)
- Úvod do lineárního programování v Excelu
- Metody řešení lineárního programování pomocí aplikace Excel Solver
Úvod do lineárního programování v Excelu
Lineární programování je nejdůležitější a také fascinující aspekt aplikované matematiky, který pomáhá při optimalizaci zdrojů (buď minimalizaci ztrát nebo maximalizaci zisku s danými prostředky). Pokud máme omezení a objektivní funkci dobře definovanou, můžeme pomocí systému předpovídat optimální řešení daného problému. V Excelu máme Excel Solver, který nám pomáhá řešit problémy lineárního programování aka LPP. V tomto článku uvidíme, jak pomocí aplikace Excel Solver optimalizovat zdroje spojené s obchodními problémy pomocí lineárního programování.
První věc první. Podívejme se, jak můžeme povolit Excel Solver (klíčová součást LPP v Excelu).
Metody řešení lineárního programování pomocí aplikace Excel Solver
Pojďme pochopit, jak používat lineární programování pomocí excel řešiče s některými metodami.
Tuto šablonu lineárního programování Excel si můžete stáhnout zde - šablonu lineárního programování Excel
Metoda # 1 - Povolení řešitele v aplikaci Microsoft Excel
V Microsoft Excelu najdeme Solver na kartě Data, kterou najdete na pásu karet Excelu umístěném v horní části, jak je ukázáno níže:
Pokud tam tento nástroj nevidíte, musíte jej povolit prostřednictvím Možnosti aplikace Excel. Postupujte podle následujících kroků a povolte Řešitele v Excelu.
Krok 1: Přejděte k nabídce Soubor a klikněte na Možnosti, což je poslední položka v tomto seznamu.
Krok 2: Otevře se nové okno s názvem Možnosti aplikace Excel. V seznamu možností na levé straně okna klepněte na doplňky.
Krok 3: V části Spravovat v dolní části okna vyberte Doplňky aplikace Excel z rozevíracího seznamu a klikněte na tlačítko Přejít … umístěné vedle.
Krok 4: Jakmile kliknete na tlačítko Přejít…, uvidíte v novém okně seznam všech doplňků dostupných v aplikaci Excel. Zaškrtněte, chcete-li vybrat doplněk Řešitele, abyste jej mohli použít na kartě Data pro řešení rovnic. Po zaškrtnutí políčka Doplněk Řešitele klikněte na tlačítko OK.
Tímto způsobem můžete v aplikaci Microsoft Excel povolit program Excel Solver.
Metoda # 2 - Řešení problému lineárního programování pomocí Excel Excelu
Nyní se pokusíme vyřešit problém lineárního programování pomocí nástroje Excel Solver.
Příklad: Chemická továrna vyrábí dva produkty, a to A a B. Tyto dva produkty potřebují suroviny, jak je ukázáno níže: Produkt A potřebuje tři druhy surovin - materiál_1 20 kg, materiál_2 30 kg, materiál_3 jako 5 kg. Na podobných řádcích vyžaduje produkt B 10 kg materiálu_1, 30 kg materiálu_2 a 10 kg materiálu_3. Výrobce vyžaduje minimálně 460 kg nebo materiál_1, 960 kg materiálu_2 a 220 kg materiálu_3. Pokud je cena za jednotku A 30 USD a cena B je 35 USD, kolik produktů by měl výrobce smíchat, aby splnil minimální požadavky na materiál při co nejnižší ceně? Použijeme informace uvedené v tomto příkladu pro modelování rovnic.
Krok 1: Vidíme všechna omezení rovnic, která můžeme vytvořit pomocí informací uvedených v příkladu výše.
Krok 2: Pomocí těchto rovnic přidejte vazby po buňkách pod Excel v rámci A2: C8 daného listu. Viz obrázek níže:
Krok 3: Nyní musíme použít vzorec Množství * za jednotkovou cenu a sečíst ji pro oba produkty, abychom získali skutečné materiálové požadavky. Můžete to vidět ve sloupci D pro všechny buňky, které obsahují vazby B3, B4, C3). Viz přiložený screenshot níže:
Pokud se blíže podíváte na tento vzorec, použili jsme B3 a C3 jako pevné členy pro každý vzorec napříč různými buňkami ve sloupci D. Je to proto, že B3 a C3 jsou buňky označující množství pro produkt A respektive produkt B. Tato množství se objeví, jakmile je systém rovnic vyřešen pomocí Excel Excelu.
Krok 4: Klikněte na kartu Data a poté na Řešitel, který se nachází v části Analýza na kartě.
Krok 5: Jakmile kliknete na Řešitel, otevře se nová záložka nazvaná „Parametr Řešitele“, pod kterou musíte nastavit parametry pro tuto množinu rovnic, která má být vyřešena.
Krok 6: První věc, kterou musíme identifikovat, je Stanovit cíl: Protože naším cílem je zjistit celkové náklady, které je třeba minimalizovat, nastavte toto na D4.
Krok 7: Protože potřebujeme minimalizovat náklady s nejvyšší možnou produkcí, nastavte další parametr na Min. To lze dosáhnout kliknutím na přepínač Min.
Krok 8: V části Změnou proměnných buněk: musíme zmínit B3 a C3, protože tyto buňky budou tím, kdo obsahuje kvantity pro produkt A a produkt B, jakmile se problém vyřeší.
Krok 9: Nyní přidejte omezení. Klikněte na tlačítko Přidat v části Předmět do sekce Omezení: a otevře se nové okno pro přidání omezení. V tomto okně - B3: C3 jako reference buňky, > = a 0 jako omezení. Děláme to, protože základní omezení v každém LPP je, že X a Y by měly být větší než nula.
Krok 10: Klikněte znovu na tlačítko Přidat a tentokrát použijte B3: C3 jako referenční buňku a F6: F8 jako omezení s nerovností jako> =. Klepnutím na tlačítko OK přidáte toto omezení také pod řešitele.
Řešitel má nyní všechny parametry potřebné k vyřešení této sady lineárních rovnic a vypadá takto:
Krok 11: Nyní klikněte na tlačítko Řešit ve spodní části okna a vyřešte tuto lineární rovnici a vytvořte optimální řešení.
Jakmile klikneme na tlačítko vyřešit, systém začne hledat optimální řešení problému, který jsme poskytli, a dostaneme hodnoty pro B3, C3, pomocí kterých také získáme hodnoty ve sloupci F pro F4, F6: F8. Jaké jsou optimální náklady a materiálové hodnoty, které lze použít pro produkt A a produkt B.
Toto řešení nás informuje, že pokud potřebujeme minimalizovat výrobní náklady pro produkt A a produkt B s optimálním využitím materiálů Material_1, Material_2 a Material_3, měli bychom vyrobit 14 množství produktu A a 18 množství produktu B.
Toto je z tohoto článku. Pojďme zabalit věci několika body, které je třeba mít na paměti:
Zajímavosti o lineárním programování v Excelu
- Je nutné řešit problémy lineárního programování pomocí programu Excel Solver. Neexistuje žádná jiná metoda, kterou bychom to mohli udělat pomocí.
- Měli bychom mít vždy omezení a objektovou proměnnou, abychom byli připraveni s námi.
- Pokud není řešič povolen, můžete jej povolit v možnostech doplňku Excel.
Doporučené články
Toto je průvodce lineárním programováním v Excelu. Zde diskutujeme Jak používat lineární programování v Excelu spolu s praktickými příklady a šablonou Excel ke stažení. Můžete si také prohlédnout naše další doporučené články -
- Interpolovat v Excelu
- Programování v Excelu
- Přesouvání sloupců v Excelu
- Inverzní matice v Excelu