|
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.
| 
