Dodatek Excel Solver przeprowadza optymalizację matematyczną. Jest to zwykle używane do dopasowywania złożonych modeli do danych lub znajdowania iteracyjnych rozwiązań problemów. Na przykład możesz chcieć dopasować krzywą przez niektóre punkty danych za pomocą równania. Solver może znaleźć w równaniu stałe, które najlepiej pasują do danych. Innym zastosowaniem jest sytuacja, w której trudno jest zmienić układ modelu, aby wymagane dane wyjściowe były przedmiotem równania.
Gdzie jest Solver w programie Excel?
Dodatek Solver jest dołączony do programu Excel, ale nie zawsze jest ładowany jako część instalacji domyślnej. Aby sprawdzić, czy jest załadowany, wybierz kartę DATA i poszukaj ikony Solver w sekcji Analiza.
Jeśli nie możesz znaleźć dodatku Solver w zakładce DANE, musisz załadować dodatek:
-
Wybierz kartę PLIK, a następnie wybierz Opcje.
Image -
W oknie dialogowym Options wybierz Dodatki z zakładek po lewej stronie.
Image -
Na dole okna wybierz Excel Add-ins z menu Manage i wybierz Idź…
Image -
Zaznacz pole wyboru obok Solver Add-in i wybierz OK.
Image -
Polecenie Solver powinno teraz pojawić się na karcie DATA. Jesteś gotowy do korzystania z dodatku Solver.
Image
Korzystanie z dodatku Solver w programie Excel
Zacznijmy od prostego przykładu, aby zrozumieć, co robi Solver. Wyobraź sobie, że chcemy wiedzieć, jaki promień da okrąg o powierzchni 50 jednostek kwadratowych. Znamy równanie na pole koła (A=pi r2). Moglibyśmy oczywiście zmienić to równanie, aby uzyskać promień wymagany dla danego obszaru, ale dla przykładu załóżmy, że nie wiemy, jak to zrobić.
Utwórz arkusz kalkulacyjny z promieniem w B1 i oblicz powierzchnię w B2 używając równania =pi()B1^2.
Możemy ręcznie dostosować wartość w B1, aż B2 pokaże wartość bliską 50. W zależności od tego, jak dokładnie musi być, może to być praktyczne podejście. Jeśli jednak musimy być bardzo dokładni, wprowadzenie wymaganych poprawek zajmie dużo czasu. Właściwie to właśnie robi Solver. Dokonuje korekty wartości w niektórych komórkach i sprawdza wartość w komórce docelowej:
- Wybierz kartę DANE i Solver, aby załadować Parametry solvera okno dialogowe
-
Ustaw cel jako obszar, B2. Jest to wartość, która zostanie sprawdzona, dostosowując inne komórki, aż ta osiągnie prawidłową wartość.
Image -
Wybierz przycisk dla Wartość: i ustaw wartość 50. Jest to wartość, którą powinien osiągnąć B2.
Image -
W polu zatytułowanym Zmieniając komórki zmiennych: wprowadź komórkę zawierającą promień, B1.
Image -
Pozostaw pozostałe opcje bez zmian i wybierz Rozwiąż. Przeprowadzana jest optymalizacja, wartość B1 jest dostosowywana, aż B2 wynosi 50 i wyświetlane jest okno dialogowe Solver Results.
Image -
Wybierz OK, aby zachować rozwiązanie.
Image
Ten prosty przykład pokazuje, jak działa solwer. W tym przypadku moglibyśmy łatwiej uzyskać rozwiązanie na inne sposoby. Następnie przyjrzymy się kilku przykładom, w których Solver podaje rozwiązania, które trudno byłoby znaleźć w inny sposób.
Dopasowywanie złożonego modelu za pomocą dodatku Excel Solver
Excel ma wbudowaną funkcję wykonywania regresji liniowej, dopasowującej linię prostą do zestawu danych. Wiele popularnych funkcji nieliniowych można zlinearyzować, co oznacza, że regresję liniową można wykorzystać do dopasowania funkcji, takich jak wykładniki. W przypadku bardziej złożonych funkcji Solvera można użyć do wykonania „minimizacji metodą najmniejszych kwadratów”. W tym przykładzie rozważymy dopasowanie równania postaci ax^b+cx^d do danych pokazanych poniżej.
To obejmuje następujące kroki:
- Ułóż zbiór danych z wartościami x w kolumnie A i wartościami y w kolumnie B.
- Utwórz 4 wartości współczynników (a, b, c i d) gdzieś w arkuszu kalkulacyjnym, mogą one otrzymać dowolne wartości początkowe.
-
Utwórz kolumnę dopasowanych wartości Y, używając równania postaci ax^b+cx^d, które odwołuje się do współczynników utworzonych w kroku 2 i wartości x w kolumnie A. Zauważ, że aby skopiować formułę w dół kolumny, odniesienia do współczynników muszą być bezwzględne, a odniesienia do wartości x muszą być względne.
Image -
Chociaż nie jest to konieczne, możesz uzyskać wizualne wskazanie, jak dobre jest dopasowanie równania, wykreślając obie kolumny y względem wartości x na pojedynczym wykresie punktowym XY. Sensowne jest użycie znaczników dla oryginalnych punktów danych, ponieważ są to wartości dyskretne z szumem, oraz użycie linii dla dopasowanego równania.
Image -
Następnie potrzebujemy sposobu na określenie różnicy między danymi a naszym dopasowanym równaniem. Standardowym sposobem na to jest obliczenie sumy kwadratów różnic. W trzeciej kolumnie dla każdego wiersza oryginalna wartość danych dla Y jest odejmowana od wartości dopasowanego równania, a wynik jest podnoszony do kwadratu. Tak więc, w D2, wartość jest podana przez =(C2-B2)^2 Następnie obliczana jest suma wszystkich tych kwadratów wartości. Ponieważ wartości są podniesione do kwadratu, mogą być tylko dodatnie.
Image -
Możesz teraz przeprowadzić optymalizację za pomocą dodatku Solver. Należy dostosować cztery współczynniki (a, b, c i d). Masz również jedną wartość celu do zminimalizowania, sumę kwadratów różnic. Uruchom solver, jak powyżej, i ustaw parametry solvera tak, aby odnosiły się do tych wartości, jak pokazano poniżej.
Image -
Odznacz opcję Ustaw zmienne nieograniczone jako nieujemne, to zmusi wszystkie współczynniki do przyjęcia wartości dodatnich.
Image -
Wybierz Rozwiąż i przejrzyj wyniki. Wykres zostanie zaktualizowany, dając dobry wskaźnik dobroci dopasowania. Jeśli solver nie zapewni dobrego dopasowania przy pierwszej próbie, możesz spróbować uruchomić go ponownie. Jeśli dopasowanie poprawiło się, spróbuj rozwiązać z bieżącymi wartościami. W przeciwnym razie możesz spróbować ręcznie poprawić dopasowanie przed rozwiązaniem.
Image - Po uzyskaniu dobrego dopasowania możesz wyjść z solvera.
Iteratywne rozwiązywanie modelu
Czasami istnieje stosunkowo proste równanie, które daje wynik w postaci pewnych danych wejściowych. Jednak gdy próbujemy odwrócić problem, nie jest możliwe znalezienie prostego rozwiązania. Na przykład moc zużywana przez pojazd jest w przybliżeniu wyrażona wzorem P=av + bv^3 gdzie v jest prędkością, a jest współczynnikiem oporu toczenia, a b jest współczynnikiem opór aerodynamiczny. Chociaż jest to dość proste równanie, nie jest łatwo przełożyć je na równanie prędkości, jaką pojazd osiągnie przy danym poborze mocy. Możemy jednak użyć Solvera, aby iteracyjnie znaleźć tę prędkość. Na przykład znajdź prędkość osiągniętą przy poborze mocy 740 W.
-
Utwórz prosty arkusz kalkulacyjny z prędkością, współczynnikami a i b oraz obliczoną z nich mocą.
Image -
Uruchom Solver i wprowadź moc, B5, jako cel. Ustaw wartość celu 740 i wybierz prędkość, B2, jako komórki zmiennych do zmiany. Wybierz rozwiąż, aby rozpocząć rozwiązanie.
Image -
Solwer dostosowuje wartość prędkości, aż moc będzie bardzo bliska 740, zapewniając wymaganą prędkość.
Image - Rozwiązywanie modeli w ten sposób często może być szybsze i mniej podatne na błędy niż odwracanie złożonych modeli.
Zrozumienie różnych opcji dostępnych w solverze może być dość trudne. Jeśli masz trudności z uzyskaniem rozsądnego rozwiązania, często przydatne jest zastosowanie warunków brzegowych do zmiennych komórek. Są to wartości graniczne, powyżej których nie należy ich korygować. Na przykład w poprzednim przykładzie prędkość nie powinna być mniejsza od zera i możliwe byłoby również ustawienie górnej granicy. Byłaby to prędkość, z którą na pewno pojazd nie może jechać szybciej. Jeśli jesteś w stanie ustawić granice dla zmiennych komórek zmiennych, dzięki temu inne bardziej zaawansowane opcje działają lepiej, takie jak multistart. Spowoduje to uruchomienie szeregu różnych rozwiązań, zaczynając od różnych wartości początkowych zmiennych.
Wybór metody rozwiązywania również może być trudny. Simplex LP nadaje się tylko do modeli liniowych, jeśli problem nie jest liniowy, zakończy się niepowodzeniem z komunikatem, że ten warunek nie został spełniony. Dwie pozostałe metody są odpowiednie dla metod nieliniowych. GRG Nonlinear jest najszybszy, ale jego rozwiązanie może w dużym stopniu zależeć od początkowych warunków początkowych. Ma elastyczność, że nie wymaga, aby zmienne miały ustawione granice. Solver ewolucyjny jest często najbardziej niezawodny, ale wymaga, aby wszystkie zmienne miały zarówno górną, jak i dolną granicę, co może być trudne do wcześniejszego ustalenia.
Dodatek Excel Solver to bardzo potężne narzędzie, które można zastosować do wielu praktycznych problemów. Aby w pełni wykorzystać możliwości programu Excel, spróbuj połączyć dodatek Solver z makrami programu Excel.
