Programowanie liniowe Excel (spis treści)
- Wprowadzenie do programowania liniowego w programie Excel
- Metody rozwiązywania programowania liniowego za pomocą programu Excel Solver
Wprowadzenie do programowania liniowego w programie Excel
Programowanie liniowe jest najważniejsze, a także fascynujący aspekt matematyki stosowanej, który pomaga w optymalizacji zasobów (minimalizując straty lub maksymalizując zysk przy danych zasobach). Jeśli mamy ograniczenia, a funkcja celu jest dobrze zdefiniowana, możemy użyć systemu, aby przewidzieć optymalne rozwiązanie dla danego problemu. W programie Excel mamy program Excel Solver, który pomaga nam rozwiązywać problemy z programowaniem liniowym, czyli LPP. W tym artykule zobaczymy, jak używać programu Excel Solver do optymalizacji zasobów związanych z problemami biznesowymi za pomocą programowania liniowego.
Najpierw pierwsza rzecz. Zobaczmy, jak możemy włączyć Excel Solver (kluczowy składnik LPP w Excelu).
Metody rozwiązywania programowania liniowego za pomocą programu Excel Solver
Zobaczmy, jak korzystać z programowania liniowego za pomocą solvera Excela z niektórymi metodami.
Możesz pobrać ten szablon Excel Programowanie liniowe tutaj - Szablon Excel Programowanie liniowe
Metoda nr 1 - Włączanie Solvera w Microsoft Excel
W Microsoft Excel możemy znaleźć Solver w zakładce Dane, którą można znaleźć na wstążce Excel umieszczonej w górnej części, jak pokazano poniżej:
Jeśli nie widzisz tam tego narzędzia, musisz włączyć je w Opcjach programu Excel. Wykonaj poniższe czynności, aby włączyć Solver w programie Excel.
Krok 1: Przejdź do menu Plik i kliknij Opcje, co jest ostatnią rzeczą na tej liście.
Krok 2: Pojawi się nowe okno o nazwie Opcje programu Excel. Kliknij Dodatki na liście opcji znajdujących się po lewej stronie okna.
Krok 3: W sekcji Zarządzaj w dolnej części okna wybierz Dodatki Excel z rozwijanej listy i kliknij przycisk Przejdź… umieszczony obok.
Krok 4: jak tylko klikniesz przycisk Go…, będziesz mógł zobaczyć listę wszystkich dodatków dostępnych w programie Excel w nowym oknie. Zaznacz, aby wybrać dodatek Solver, aby móc go używać w zakładce Dane do rozwiązywania równań. Kliknij przycisk OK po zaznaczeniu opcji Solver Add-in.
W ten sposób możesz włączyć Excel Solver w Microsoft Excel.
Metoda nr 2 - Rozwiązanie problemu programowania liniowego przy użyciu programu Excel Solver
Teraz spróbujemy rozwiązać problem programowania liniowego za pomocą narzędzia Excel Solver.
Przykład: Zakład chemiczny wytwarza dwa produkty, a mianowicie A i B. Te dwa produkty potrzebują surowców, jak pokazano poniżej: Produkt A potrzebuje trzech rodzajów surowców - Materiał_1 20 kg, Materiał_2 30 kg, Materiał_3 jako 5 kg. Na podobnych liniach produkt B wymaga 10 kg materiału_1, 30 kg materiału_2 i 10 kg materiału_3. Producent wymaga co najmniej 460 kg lub materiału_1, 960 kg materiału_2 i 220 kg materiału_3. Jeśli koszt jednostkowy produktu A wynosi 30 USD, a koszt produktu B wynosi 35 USD, ile produktów producent powinien wymieszać, aby spełnić minimalne wymagania materiałowe przy możliwie najniższych kosztach? Wykorzystajmy informacje podane w tym przykładzie do modelowania równań.
Krok 1: Możemy zobaczyć wszystkie ograniczenia równania, które możemy utworzyć, korzystając z informacji podanych w powyższym przykładzie.
Krok 2: Użyj tych równań, aby dodać ograniczenia komórkowe w programie Excel w A2: C8 danego arkusza. Zobacz zrzut ekranu jak poniżej:
Krok 3: Teraz musimy użyć wzoru Ilość * na koszt jednostkowy i zsumować go dla obu produktów, aby uzyskać rzeczywiste wymagania materiałowe. Możesz zobaczyć to sformułowane w kolumnie D dla wszystkich komórek, które zawierają ograniczenia B3, B4, C3). Zobacz załączony zrzut ekranu poniżej:
Jeśli przyjrzysz się bliżej tej formule, użyliśmy B3 i C3 jako stałych elementów dla każdej formuły w różnych komórkach w kolumnie D. Jest tak, ponieważ B3 i C3 są komórkami oznaczającymi ilości odpowiednio dla produktu A i produktu B. Ilości te pojawią się po rozwiązaniu układu równań za pomocą programu Excel Solver.
Krok 4: Kliknij zakładkę Dane, a następnie Solver, który jest obecny w sekcji Analizuj w zakładce.
Krok 5: Po kliknięciu Solver otworzy się nowa zakładka o nazwie „Solver Parameter”, w której należy ustawić parametry tego zestawu równań do rozwiązania.
Krok 6: Pierwszą rzeczą, którą musimy zidentyfikować, jest Ustal Cel: Ponieważ naszym celem jest ustalenie całkowitego kosztu, aby można go było zminimalizować, ustaw to na D4.
Krok 7: Ponieważ musimy zminimalizować koszt przy najwyższej możliwej produkcji, ustaw następny parametr na Min. Możesz to zrobić, klikając przycisk opcji Min.
Krok 8: w części Zmieniając zmienne komórki :, musimy wspomnieć o B3 i C3, ponieważ komórki te będą zawierać ilości odpowiednio dla produktu A i produktu B po rozwiązaniu problemu.
Krok 9: Teraz dodaj ograniczenia. Kliknij przycisk Dodaj w sekcji Podlegające ograniczeniom: sekcja, a otworzy się nowe okno, aby dodać ograniczenia. W tym oknie - B3: C3 jako odwołanie do komórki, > = i 0 jako ograniczenia. Robimy to, ponieważ podstawowym ograniczeniem w każdym LPP jest to, że X i Y powinny być większe od zera.
Krok 10: Kliknij ponownie przycisk Dodaj i tym razem użyj B3: C3 jako odwołania do komórki i F6: F8 jako ograniczenia z nierównością jako> =. Kliknij przycisk OK, aby dodać to ograniczenie również pod solwerem.
Solver ma teraz wszystkie parametry wymagane do rozwiązania tego zestawu równań liniowych i wygląda następująco:
Krok 11: Teraz kliknij przycisk Rozwiąż w dolnej części okna, aby rozwiązać to równanie liniowe i znaleźć optymalne rozwiązanie.
Jak tylko klikniemy przycisk rozwiązywania, system zaczyna szukać optymalnego rozwiązania podanego przez nas problemu i otrzymujemy wartości dla B3, C3, za pomocą których otrzymujemy również wartości w kolumnie F dla F4, F6: F8. Jakie są optymalne koszty i wartości materiałów, które można zastosować dla produktu A i produktu B.
To rozwiązanie informuje nas, że jeśli musimy zminimalizować koszty produkcji produktu A i produktu B przy optymalnym wykorzystaniu materiałów_1, materiałów_2 i materiałów_3, powinniśmy wyprodukować 14 ilości produktu A i 18 ilości produktu B.
To jest z tego artykułu. Podsumujmy te rzeczy, pamiętając o kilku punktach:
O czym należy pamiętać o programowaniu liniowym w programie Excel
- Rozwiązywanie problemów z programowaniem liniowym za pomocą Solvera Excel jest obowiązkowe. Nie ma innej metody, w której moglibyśmy to zrobić.
- Zawsze powinniśmy mieć ograniczenia i zmienne obiektowe, abyśmy byli gotowi z nami.
- Jeśli Solver nie jest włączony, możesz go włączyć w opcjach dodatku Excel.
Polecane artykuły
Jest to przewodnik po programowaniu liniowym w programie Excel. Tutaj omawiamy, jak korzystać z programowania liniowego w programie Excel, wraz z praktycznymi przykładami i szablonem programu Excel do pobrania. Możesz także przejrzeć nasze inne sugerowane artykuły -
- Interpoluj w programie Excel
- Programowanie w Excelu
- Przenoszenie kolumn w programie Excel
- Odwrotna macierz w programie Excel