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.
___________________
Andrzej Janowski