Optymalizacja za pomocą dodatku Excel Solver

Solver to dodatek do programu Microsoft Excel, którego można używać do optymalizacji w analizie typu „co, jeśli”.

Według O'Brien i Marakas, optimization analysisjest bardziej złożonym rozszerzeniem analizy w poszukiwaniu celu. Zamiast ustalać określoną wartość docelową dla zmiennej, celem jest znalezienie optymalnej wartości dla jednej lub większej liczby zmiennych docelowych, przy pewnych ograniczeniach. Następnie co najmniej jedna inna zmienna jest zmieniana wielokrotnie, z zastrzeżeniem określonych ograniczeń, aż do znalezienia najlepszych wartości zmiennych docelowych.

W programie Excel możesz użyć Solver znaleźć optimal value (maksymalna lub minimalna lub określona wartość) dla formuły w jednej komórce zwanej komórką celu, z zastrzeżeniem pewnych ograniczeń lub limitów, wartości innych komórek formuły w arkuszu.

Oznacza to, że Solver współpracuje z grupą komórek zwanych zmiennymi decyzyjnymi, które są używane do obliczania formuł w komórkach celu i komórek ograniczeń. Solver dostosowuje wartości w komórkach zmiennych decyzyjnych, aby spełnić ograniczenia komórek ograniczających i wygenerować żądany wynik dla komórki docelowej.

Możesz użyć dodatku Solver, aby znaleźć optymalne rozwiązania różnych problemów, takich jak -

Określenie miesięcznego asortymentu produktów dla jednostki produkującej leki, który maksymalizuje rentowność.
Planowanie siły roboczej w organizacji.
Rozwiązywanie problemów transportowych.
Planowanie finansowe i budżetowanie.

Aktywowanie dodatku Solver

Przed przystąpieniem do znajdowania rozwiązania problemu z Solver, upewnij się, że Solver Add-in jest aktywowany w programie Excel w następujący sposób -

Kliknij kartę DANE na Wstążce. PlikSolver polecenie powinno pojawić się w grupie Analiza, jak pokazano poniżej.

Jeśli nie znajdziesz polecenia Solver, aktywuj je w następujący sposób -

Kliknij kartę PLIK.
Kliknij Opcje w lewym okienku. Pojawi się okno dialogowe Opcje programu Excel.
Kliknij Dodatki w lewym okienku.
Wybierz Dodatki programu Excel w polu Zarządzaj i kliknij Przejdź.

Pojawi się okno dialogowe Dodatki. CzekSolver Add-ini kliknij OK. Teraz powinieneś być w stanie znaleźć polecenie Solver na Wstążce w zakładce DANE.

Metody rozwiązywania używane przez dodatek Solver

Możesz wybrać jedną z trzech następujących metod rozwiązywania obsługiwanych przez dodatek Excel Solver, w zależności od typu problemu -

LP Simplex

Używany do problemów liniowych. ZASolver model jest liniowy w następujących warunkach -

Komórka docelowa jest obliczana przez dodanie do siebie warunków w postaci (zmiana komórki) * (stała).
Każde ograniczenie spełnia wymagania modelu liniowego. Oznacza to, że każde ograniczenie jest oceniane przez dodanie do siebie warunków postaci (zmiana komórki) * (stała) i porównanie sum ze stałą.

Nieliniowy uogólniony zredukowany gradient (GRG)

Służy do łagodnych problemów nieliniowych. Jeśli komórka docelowa, którekolwiek z ograniczeń lub oba zawierają odniesienia do zmieniających się komórek, które nie mają postaci (zmiana komórki) * (stała), masz model nieliniowy.

Ewolucyjny

Zrozumienie oceny dodatku Solver

Solver wymaga następujących parametrów -

Komórki zmiennych decyzyjnych
Komórki ograniczeń
Celowe komórki
Metoda rozwiązywania

Ocena dodatku Solver jest oparta na następujących zasadach -

Wartości w komórkach zmiennych decyzyjnych są ograniczone wartościami w komórkach ograniczeń.
Obliczenie wartości w komórce celu obejmuje wartości z komórek zmiennych decyzyjnych.
Solver używa wybranej metody rozwiązywania, aby uzyskać optymalną wartość w komórce celu.

Definiowanie problemu

Załóżmy, że analizujesz zyski firmy, która produkuje i sprzedaje określony produkt. Zostaniesz poproszony o podanie kwoty, którą możesz wydać na reklamę w ciągu najbliższych dwóch kwartałów, z zastrzeżeniem maksymalnie 20 000. Poziom reklamy w każdym kwartale wpływa na:

Liczba sprzedanych sztuk, pośrednio determinująca wielkość przychodów ze sprzedaży.
Powiązane wydatki i
Zysk.

Możesz przystąpić do zdefiniowania problemu jako -

Znajdź koszt jednostkowy.
Znajdź koszt reklamy na jednostkę.
Znajdź cenę jednostkową.

Następnie ustaw komórki dla wymaganych obliczeń, jak podano poniżej.

Jak widać, obliczenia wykonywane dla kwartału 1 i kwartału 2, które są brane pod uwagę, to:

Liczba lokali dostępnych do sprzedaży w pierwszym kwartale to 400, aw drugim kwartale 600 (komórki - C7 i D7).
Początkowe wartości budżetu reklamowego to 10000 na kwartał (komórki - C8 i D8).
Liczba sprzedanych jednostek zależy od kosztu reklamy na jednostkę, a zatem jest to budżet na kwartał / Adv. Koszt jednostkowy. Zauważ, że użyliśmy funkcji Min, aby upewnić się, że nie. sprzedanych sztuk w <= l. dostępnych jednostek. (Komórki - C9 i D9).
Przychód jest obliczany jako cena jednostkowa * liczba sprzedanych jednostek (komórki - C10 i D10).
Wydatki są obliczane jako koszt jednostkowy * liczba dostępnych jednostek + zaliczka. Koszt za ten kwartał (komórki - C11 i D12).
Zysk to przychód - wydatki (komórki C12 i D12).
Całkowity zysk to zysk w kwartale 1 + zysk w kwartale 2 (komórka - D3).

Następnie możesz ustawić parametry dodatku Solver, jak podano poniżej -

Jak widać, parametry dodatku Solver to -

Komórka celu to D3, która zawiera całkowity zysk, który chcesz zmaksymalizować.
Zmienna decyzyjna to komórki C8 i D8 zawierające budżety na dwa kwartały - Kwartał1 i Kwartał2.
Istnieją trzy komórki ograniczenia - C14, C15 i C16.
- Komórka C14, która zawiera całkowity budżet, ma ustawić ograniczenie na 20000 (komórka D14).
- Komórka C15 zawierająca nie. jednostek sprzedanych w kwartale 1 ma na celu ustawienie ograniczenia <= nie. jednostek dostępnych w Kwartale1 (komórka D15).
- Komórka C16 zawierająca nie. jednostek sprzedanych w Kwartale2 ma na celu ustawienie ograniczenia <= nie. jednostek dostępnych w Kwartale2 (komórka D16).

Rozwiązywanie problemu

Następnym krokiem jest użycie dodatku Solver do znalezienia rozwiązania w następujący sposób -

Step 1- Przejdź do DATA> Analysis> Solver na Wstążce. Zostanie wyświetlone okno dialogowe Solver - Parametry.

Step 2 - W polu Ustaw cel wybierz komórkę D3.

Step 3 - Wybierz Maks.

Step 4 - Wybierz zakres C8: D8 w By Changing Variable Cells pudełko.

Step 5 - Następnie kliknij przycisk Dodaj, aby dodać trzy zidentyfikowane ograniczenia.

Step 6- Pojawi się okno dialogowe Dodaj ograniczenie. Ustaw ograniczenie całkowitego budżetu, jak podano poniżej, i kliknij Dodaj.

Step 7- Ustaw ograniczenie dla całkowitej nie. jednostek sprzedanych w 1 kwartale, jak podano poniżej i kliknij Dodaj.

Step 8- Ustaw ograniczenie dla całkowitej nie. sztuk sprzedanych w drugim kwartale, jak podano poniżej i kliknij OK.

Zostanie wyświetlone okno dialogowe Solver - Parametry z trzema wiązaniami dodanymi w polu - Poddaj ograniczenia.

Step 9 - W Select a Solving Method wybierz opcję Simplex LP.

Step 10- Kliknij przycisk Rozwiąż. Zostanie wyświetlone okno dialogowe Solver - wyniki. WybierzKeep Solver Solution i kliknij OK.

Wyniki pojawią się w arkuszu.

Jak można zauważyć, optymalne rozwiązanie, które daje maksymalny całkowity zysk, z zastrzeżeniem określonych ograniczeń, jest następujące -

Całkowity zysk - 30000.
Adv. Budżet na kwartał 1 - 8000.
Adv. Budżet na kwartał 2 - 12000.

Przejście przez rozwiązania testowe Solvera

Możesz przejść przez rozwiązania próbne Solver, patrząc na wyniki iteracji.

Step 1 - Kliknij przycisk Opcje w oknie dialogowym Parametry dodatku Solver.

Plik Options pojawi się okno dialogowe.

Step 2 - Zaznacz pole Pokaż wyniki iteracji i kliknij OK.

Step 3 - The Solver Parameterspojawi się okno dialogowe. KliknijSolve.

Step 4 - The Show Trial Solution pojawi się okno dialogowe z komunikatem - Solver paused, current solution values displayed on worksheet.

Jak widać, aktualne wartości iteracji są wyświetlane w komórkach roboczych. Można zatrzymać akceptowanie przez dodatek Solver bieżących wyników lub kontynuować znajdowanie rozwiązania przez Solver w dalszych krokach.

Step 5 - Kliknij Kontynuuj.

Plik Show Trial Solutionna każdym kroku pojawia się okno dialogowe, a na koniec po znalezieniu optymalnego rozwiązania pojawia się okno dialogowe Solver Results. Twój arkusz roboczy jest aktualizowany na każdym kroku, ostatecznie pokazując wartości wyników.

Zapisywanie wyborów dodatku Solver

Masz następujące opcje zapisywania problemów rozwiązywanych za pomocą dodatku Solver:

Możesz zapisać ostatnie wybory w oknie dialogowym Parametry dodatku Solver wraz z arkuszem, zapisując skoroszyt.
Każdy arkusz w skoroszycie może mieć własne wybory dodatku Solver i wszystkie zostaną zapisane po zapisaniu skoroszytu.
W arkuszu można także zdefiniować więcej niż jeden problem, każdy z własnymi opcjami dodatku Solver. W takim przypadku można wczytywać i zapisywać problemy indywidualnie za pomocą polecenia Wczytaj / Zapisz w oknie dialogowym Parametry dodatku Solver.
- Kliknij Load/Saveprzycisk. Pojawi się okno dialogowe Załaduj / Zapisz.
- Aby zapisać model problemu, wprowadź odwołanie do pierwszej komórki z pionowego zakresu pustych komórek, w których chcesz umieścić model problemu. Kliknij Zapisz.

Model problemu (zestaw parametrów dodatku Solver) pojawia się, zaczynając od komórki podanej jako odniesienie.

Aby wczytać model problemu, wprowadź odniesienie dla całego zakresu komórek, które zawierają model problemu. Następnie kliknij przycisk Załaduj.