Kompletny przewodnik po używaniu Solvera w programie Excel do rozwiązywania problemów

Microsoft Excel jest wyposażony w szereg funkcji, które usprawniają proces wykonywania obliczeń i rozwiązywania równań, zwiększając tym samym produktywność. Jedną z tych funkcji jest narzędzie Solver, które jest podobne do funkcji Goal Seek.

Zazwyczaj wykorzystywany do analizy „Co by było, gdyby”, Solver pozwala użytkownikom ustalić wartość konkretnej komórki przy wielu ograniczeniach. Może być używany do określania konkretnej wartości, minimalnego progu lub maksymalnego limitu dla liczby. Chociaż może nie rozwiązywać każdego problemu, Solver jest nieocenionym zasobem dla scenariuszy optymalizacji, w których znalezienie najlepszej możliwej decyzji jest kluczowe.

To narzędzie działa poprzez dostosowywanie wartości określonych komórek znanych jako zmienne decyzyjne w arkuszu kalkulacyjnym w celu zidentyfikowania maksymalnej lub minimalnej wartości innej komórki, zwanej komórką celu. Solver jest odpowiedni do różnych typów programowania, w tym programowania liniowego i nieliniowego, programowania całkowitoliczbowego i zadań dążących do celu.

Typowe zastosowania Solvera obejmują minimalizację kosztów transportu, tworzenie optymalnych harmonogramów pracy, ustalanie najlepszego budżetu na inicjatywy reklamowe i maksymalizację zwrotu z inwestycji, aby wymienić tylko kilka.

Aktywacja Solvera w programie Excel

Aby zacząć używać Solver, musisz najpierw włączyć ten dodatek, ponieważ nie jest on domyślnie aktywowany, tak jak funkcja Goal Seek. Na szczęście proces jest dość prosty.

Na początek wybierz menu Plik znajdujące się na górze ekranu i kliknij „Opcje”.

Następnie kliknij „Dodatki” znajdujące się po lewej stronie okna Opcje.

Teraz wybierz „Dodatki programu Excel” z menu rozwijanego „Zarządzaj” u dołu i kliknij „Przejdź”.

W następnym oknie dialogowym zaznacz pole wyboru obok opcji „Dodatek Solver”, aby go włączyć, a następnie kliknij „OK”.

Teraz po kliknięciu zakładki „Dane” w programie Excel program Solver powinien być widoczny.

Kluczowe komponenty Solvera

Zanim Solver będzie mógł zidentyfikować optymalną wartość dla dowolnego problemu, konieczne jest ustalenie trzech głównych komponentów:

Komórka celu: Ta komórka zawiera formułę reprezentującą cel lub wartość docelową problemu, czy to minimalizację, maksymalizację, czy osiągnięcie określonej wartości.
Komórki zmiennych: Te komórki zawierają zmienne, które Solver dostosuje, aby spełnić cel. W Solver można wyznaczyć maksymalnie 200 komórek zmiennych.
Ograniczenia: Ograniczenia to parametry, w ramach których Solver musi działać, aby osiągnąć pożądany wynik. Definiują one warunki, które muszą zostać spełnione podczas określania wymaganych wartości.

Stosowanie Solvera

Po dodaniu Solvera do programu Excel możesz przystąpić do jego wykorzystania. W tym przykładzie wykorzystamy Solver do obliczenia zysku firmy produkującej palety na podstawie znanych wartości zasobów, takich jak zasoby potrzebne na paletę wraz z dostępnością różnych typów palet.

Komórki od B3 do E3 zawierają listę różnych typów palet, które firma musi wyprodukować. Wiersz bezpośrednio poniżej przedstawia liczbę palet, które mają zostać wyprodukowane dla każdego typu, zainicjowaną zerem. Następny wiersz szczegółowo opisuje zysk związany z każdym typem palety. Naszym celem jest określenie liczby palet do wyprodukowania dla każdego rodzaju, przy czym całkowity zysk jest wyświetlany w komórce F5. Ograniczeniami tutaj są dostępne zasoby, które dyktują, ile palet firma może realnie wyprodukować.

Aby rozpocząć, kliknij „Solver” w prawym górnym rogu, co spowoduje wyświetlenie okna dialogowego Solver. Wprowadź nazwę lub odwołanie do komórki celu, upewniając się, że zawiera ona formułę. W tym scenariuszu komórka F5 pełni funkcję celu, która daje całkowity zysk dla wszystkich typów palet łącznie, uwzględniając zarówno dostępne zasoby, jak i palety do wyprodukowania.

W polu „By Changing Variable Cells” wybierz zakres B4:E4, przeciągając myszkę lub wpisując nazwy komórek bezpośrednio. Komórki te reprezentują liczbę palet na typ i są obecnie ustawione na zero. Solver dostosuje te wartości podczas wykonywania.

Następnie kliknij przycisk „Dodaj”, aby wprowadzić ograniczenia. Solver obliczy, ile palet firma może wyprodukować na podstawie dostępności materiałów, takich jak klej, prasowanie, wióry sosnowe i wióry dębowe. Zauważysz, że wartości w kolumnie „Używane”, obecnie wynoszące zero, zmieniają się po uruchomieniu Solver.

Wpisz F8:F11 dla „Odwołanie do komórki”, co odpowiada kolumnie „Used”, i G8:G11 dla kolumny „Available” w polu Constraint. Upewnij się, że relacja jest ustawiona na <=default, wskazując, że wartości w kolumnie Used powinny być mniejsze lub równe wartościom w kolumnie Available.

Po wprowadzeniu wszystkich zmiennych i ograniczeń kliknij ponownie „Dodaj” w oknie dialogowym „Dodaj ograniczenie”, a następnie je zamknij. Zauważysz również, że opcja „Uczyń zmienne nieskrępowane nieujemnymi” jest domyślnie włączona w oknie dialogowym Solver Parameters, zapewniając, że wszystkie zmienne pozostaną nieujemne, nawet jeśli nie zostaną ustawione określone ograniczenia.

Po wprowadzeniu danych w oknie dialogowym Parametry programu Solver kliknij przycisk „Rozwiąż” i poczekaj, aż program Excel dostarczy wyniki.

Po wygenerowaniu wyników pojawi się okno dialogowe Solver Results, ujawniające nowe wartości w komórkach B4 do E4. Pamiętaj, że Solver zmienia Twoje dane; jeśli wolisz powrócić do oryginalnych wartości, możesz wybrać opcję „Restore Original Values”. Po podjęciu decyzji, czy zachować rozwiązanie, czy powrócić do oryginalnych danych, upewnij się, że opcja „Answer” jest zaznaczona po prawej stronie, a następnie kliknij „OK”, aby zamknąć okno dialogowe.

Jeśli zdecydujesz się zachować nowe rozwiązanie, zostanie ono odzwierciedlone w arkuszu kalkulacyjnym po zamknięciu okna dialogowego Solver. Produkcja firmy będzie obejmować 23 palety Tahoe, 15 palet Pacific, 39 palet Savannah i żadnych palet Aspen, które zostaną wskazane w wierszu Palety od B4 do D4. Ponadto komórka całkowitego zysku zostanie zaktualizowana z zera do 58 800 USD.

Ważne uwagi

Podobnie jak funkcja szukania wyniku programu Excel, Solver wymaga wstępnego skonfigurowania niezbędnych formuł, aby działał prawidłowo.
Możesz wpłynąć na metodę rozwiązywania problemu, wybierając przycisk „Opcje” w oknie dialogowym Parametry rozwiązania, w którym możesz określić wartości dla „Wszystkie metody”, „GRG nieliniowa” i „Ewolucyjna”.
Ponadto Solver pozwala na zapisywanie i ładowanie modeli do późniejszego wykorzystania. Podczas ładowania istniejących modeli upewnij się, że wprowadzasz odniesienie do całego zakresu komórek istotnych dla danego problemu.
Podczas korzystania z programu Solver zaleca się pracę na kopii danych, ponieważ po uruchomieniu program modyfikuje oryginalne dane, a po wprowadzeniu zmian odzyskanie tych danych może się okazać niemożliwe.

Źródło