Strona główna  »  Excel w finansach  »  Porady  »  Definiowanie i rozwiązywanie zadań za pomocą dodatku Solver

Definiowanie i rozwiązywanie zadań za pomocą dodatku Solver

Autor porady: Andrzej Janowski (janowski11@poczta.onet.pl)

Zasady projektowania modeli poszukiwania rozwiązań za pomocą dodatku Solver

Jak działa dodatek Solver? Dodatek Solver pozwala zoptymalizować wartość formuły w jednej z komórek arkusza – nazywanej komórką celu. Zakresem działania jest grupa komórek związanych bezpośrednio lub pośrednio z formułą w komórce celu. Wartości w komórkach określonych przez użytkownika – nazywanych komórkami zmienianymi – są zmieniane tak, aby osiągnąć żądany wynik w komórce celu. Zakres zmian wartości występujących w modelu można ograniczyć, wprowadzając ograniczenia. Mogą one także dotyczyć innych komórek, które mają wpływ na formułę w komórce celu.

Z menu Narzędzia wybierz polecenie Solver

Jeżeli polecenie Solver nie jest dostępne w menu Narzędzia, należy zainstalować dodatek Solver. 

W polu Komórka celu podaj adres lub nazwę komórki docelowej. Komórka celu musi zawierać formułę.

Jeżeli chcesz, aby wartość w komórce była jak największa, kliknij opcję Maks. 
Jeżeli chcesz, aby wartość w komórce była jak najmniejsza, kliknij opcję Min. 
Aby określić wartość w komórce docelowej, kliknij opcję Wartość i wpisz wartość w polu obok. 

W polu Komórki zmieniane podaj nazwę lub adres każdej komórki zmiennej, oddzielając przecinkami adresy nie przylegających komórek. Komórki zmiennej muszą być bezpośrednio lub pośrednio związane z komórką docelową. Można określić maksymalnie 200 komórek zmienianych. 

Aby w dodatku Solver automatycznie zaproponować komórki zmieniane dla komórki celu, kliknij przycisk Odgadnij. 

W polu Warunki ograniczające [na dole strony] podaj wszystkie ograniczenia, które chcesz zastosować.

Kliknij przycisk Rozwiąż.

Aby zanotować wyniki w arkuszu, kliknij przycisk Przechowaj rozwiązanie w oknie dialogowym Solver - Wyniki.

Aby przywrócić pierwotne wartości, kliknij przycisk Przywróć wartości początkowe. 

Przykład obliczeń z użyciem dodatku Solver. 

W podanym dalej przykładzie, wydatki na "Reklamę" w poszczególnych kwartałach mają wpływ na liczbę "Sprzedanych jednostek", określając pośrednio "Przychód ze sprzedaży" [według równania =35*B2*(B8+3000)^0,5 czyli: 35*wskaźnik sezonowości*(reklama+3000)^0,5], wydatki ("Koszty zakupu", "Reklama" i "Koszt ogólnozakładowy") oraz "Zysk". Optymalizacja polega na zmienianiu kwartalnego budżetu na "Reklamę" (komórki B8:E8) do jego wartości maksymalnej, którą ogranicza całkowity budżet 40 000 (komórka F8), aż do osiągnięcia największego możliwego "Zysku". Wartości w komórkach zmienianych są używane do obliczenia "Zysku" w poszczególnych kwartałach i są związane z formułą w komórce celu F11, =SUMA(B11:E11).

 

A

B C D E F
1 Miesiąc Kw. I Kw. II Kw. III Kw. IV Razem
2 Sezonowość 0,9 1,1 0,8 1,2  
3 Sprzedane jednostki 3592 4390 3192 4789 15962
4 Przychód ze sprzedaży w zł 143662 175587 127700 191549 638498
5 Koszt zakupu 89789 109742 79812 119718 399061
6 Marża brutto 53873 65845 47887 71831 239437
7 Wydatki służbowe 8000 8000 9000 9000 34000
8 Reklama 10000 10000 10000 10000 40000
9 Koszt ogólnozakładowy 21549 26338 19155 28732 95775
10 Koszt całkowity 39549 44338 38155 47732 169775
11 Zysk z produktów w zł 14324 21507 9732 24099 69662
12 Rentowność sprzedaży 10% 12% 8% 13% 11%
13 Cena produktu 40        
14 Koszt produktu 25        

Komórka celu

Komórki zmieniane

Formuły w komórkach I kwartału.

 

B

1

Kw. I

2 0,9
3 =35*B2*(B8+3000)^0,5
4 =B3*$B$13
5 =B3*$B$14
6 =B4-B5
7 8000
8 10000
9 =0,15*B4
10 =SUMA(B7:B9)
11 =B6-B10
12 =B11/B4
13 40
14 25

Okno dla Solvera.

Wynik optymalizacji.

Reklama 7273 12346 5117 15263 40000
Koszt ogólnozakładowy 19156 28616 15136 34056 96965
Koszt całkowity 34430 48963 29253 58319 170965
Zysk z produktów w zł 13461 22578 8587 26820 71447

 


Dodawanie ograniczeń w dodatku Solver

W menu Narzędzia kliknij polecenie Solver.

Kliknij przycisk Dodaj.

W polu Odwołanie do komórki podaj nazwę lub adres zakresu komórek, których wartości chcesz ograniczyć.

Kliknij symbol relacji ( <=, =, >=, int lub bin ), która ma zachodzić pomiędzy wskazaną komórką, a wartością ograniczającą. Jeżeli klikniesz symbol int, w polu Warunki ograniczające pojawi się informacja "Liczba całkowita". Jeżeli klikniesz bin, w polu Warunki ograniczające pojawi się informacja "binary".

W polu Warunki ograniczające wpisz liczbę, nazwę lub adres komórki, albo formułę.

Aby potwierdzić warunek ograniczający i dodać następny, kliknij przycisk Dodaj. 

Aby zaakceptować warunek ograniczający i powrócić do okna dialogowego Solver - Parametry, kliknij przycisk OK. 

Uwagi 

Relacje int i bin mogą występować tylko w więzach nałożonych na komórki zmieniane. 

Jeśli w oknie dialogowym Opcje dodatku Solver jest zaznaczone pole wyboru Model liniowy, nie obowiązuje żaden limit liczby ograniczeń. W przypadku problemów nieliniowych każda komórka może zawierać, oprócz ograniczeń dla zmiennych, do 100 innych ograniczeń. Informacje o opcjach w oknie dialogowym Dodawanie warunku ograniczającego.

2001-03-23