NARZ DZIE SOLVER APLIKACJI MS EXCEL

Grzegorz Ginda

[e-mail]

wersja pliku: 0.0.1 z dnia 2 listopada 2006 r.

W niniejszym dokumencie przedstawiono podstawowe informacje, dotycz ce narz dzia

Solver aplikacji MS Excel ™ firmy Microsoft

®

. Opis dotyczy wersji MS Excel 2002

(10.2614.2625 wg systemu pomocy aplikacji).

Narz dzie Solver rozprowadzane jest razem z aplikacj MS Excel. Mo na z niego korzysta

po zainstalowaniu, polegaj cym na zaznaczeniu odpowiedniej opcji i u yciu przycisku

„OK”w oknie dialogowym, uruchamianym dzi ki menu „Narz dzia” (opcja „Dodatki...”):

rys.1.

Rys. 1. Okno dialogowe opcji „Dodatki...”, dost pne w menu „Narz dzia”

Moduł Solver pozwala dokonywa optymalizacji, tzn. poszukiwa warto ci argumentów, dla

których warto ci funkcji kryterium tzw. funkcji celu jest najmniejsza, najwi ksza lub osi ga

okre lon warto . W celu przbli enia mo liwo ci modułu, utworzono skoroszyt MS Excel,

zawieraj cy arkusz umo liwiaj cy wyznaczanie pierwiastków funkcji kwadratowej o postaci :

c

x

b

x

a

x

f

+

⋅

+

⋅

=

2

)

(

.

(1)

G. Ginda: Narz dzie Solver aplikacji MS Excel

Plik skoroszytu nosi nazw „ SolverIntro.xls” .

Rys.2. Arkusz „ SolverIntro.xls” skoroszytu MS Excel

W celu wykonania odpowiednich oblicze nale y w pierwszej kolejno ci zakodowa

zawarto arkusza. Zało ono, e poszukiwane b d miejsca zerowe funkcji kwadratowej:

9

3

2

)

(

2

−

⋅

−

⋅

=

x

x

x

f

. Warto ci parametrów a, b, c funkcji f zakodowano w komórkach

zakresu B7:B9 arkusza. Natomiast warto argumentu x zakodowano jako zawarto komórki

D12. Poniewa oszacowano wst pnie granice przedziału, zawieraj cego miejsca zerowe

funkcji jako -5 (komórka A12) i +5 (komórka F12), warto x przyj to wst pnie na poziomie

odpowiadaj cym dolnej granicy przedziału, czyli x = -5. W komórce B4 zawarto formuł ,

reprezentuj c bie c warto funkcji f (x) (rys.3), odpowiadaj c bie cej warto ci
zmiennej x (pocz tkowo równej -5). Poniewa : f (x = -5) = 2

⋅(-5)

2

- 3

⋅(-5) – 9 = 56 ; st d

zawarto komórki funkcji celu wynosi wła nie 56 (rys.4).

Rys.3. Kod formuły na obliczanie warto ci funkcji f

Wersja dokumentu: 0.0.1

2 listopada 2006

- 3 -

Rys.4. Obliczeniowa cz

arkusza

W nast pnej kolejno ci, z menu „ Narz dzia” nale y wybra opcj „ Solver” (rys.5),

wy wietlaj c okno dialogowe, pokazane na rys.6. Słu y ono okre leniu warto ci

parametrów, niezb dnych do uzyskania rozwi zania.

Rys.5. Uruchamianie narz dzia Solver

G. Ginda: Narz dzie Solver aplikacji MS Excel

Rys.6. Okno dialogowe modułu Solver

Zawarto okna z rys.6 otrzymano w nast puj cy sposób:

a)

zawarto komórki celu okre lono, wskazuj c w pierwszej cz ci okna komórk B6 –

rys.7;

b)

okre lono kierunek optymalizacji jako poszukiwanie zwi zane z warto ci funkcji

równ zeru (zero odpowiada pierwiastkowi funkcji) – rys. 8;

c)

zdefiniowano zakres komórek modyfikowanych przez Solver, reprezentowany przez

adres komórki zawieraj cej aktualn warto zmiennej x (komórka D12) – rys.9;

d)

okre lono warunki ograniczaj ce, u ywaj c przycisku „ Dodaj” (na rys.10

przedstawiono jak zakodowa ograniczenie x

≥ -5, a na rys. 11 – ograniczenie x ≤ 5 ).

Rys.7. Okno dialogowe „ Solver-parametry” – okre lenie kryterium celu

Wersja dokumentu: 0.0.1

2 listopada 2006

- 5 -

Rys.8. Okre lenie kierunku optymalizacji

Rys.9. Okre lenie zakresu modyfikowanych komórek

Rys.10. Definiowanie warunku x

≥ -5

Rys.11. Definiowanie warunku x

≤ 5

G. Ginda: Narz dzie Solver aplikacji MS Excel

Miejsce zerowe mo na wyznaczy , korzystaj c z przycisku „ Rozwi ” . Jego wybór powoduje

wy wietlenie kolejnego okna dialogowego (rys.12), pozwalaj cego m.in. na wybór poziomu

szczegółowo ci raportów wyników oraz zachowania zmian dokonanych przez Solver

(zawarto komórek zmienianych oraz funkcji celu) w bie cym arkuszu. Ostatecznie

uzyskano miejsce zerowe x = -1,5 (rys.13).

Rys.12. Okno dialogowe opcji prezentacji wyników

Rys.13. Rozwi zanie (I miejsce zerowe x = -1,5)

Jak wiadomo, funkcja kwadratowa w postaci (1) ma generalnie co najwy ej 2 pierwiastki

w dziedzinie liczb rzeczywistych. W celu wyznaczenia drugiej warto ci miejsca zerowego,

nale y powtórzy obliczenia, zakładaj c inn ni poprzednio pocz tkow warto argumentu

x. Tym razem zało ono, e pocz tkowo warto argumentu odpowiada prawej granicy

przedziału, tj. x = +5. Tak wi c po zmianie zawarto ci komórki D12 nale y ponownie

wywoła okno modułu Solver. Poniewa odwołuje si ono do bie cej zawarto ci komórki

zmiennej x (D12), nie ma potrzeby modyfikacji zawarto ci okna dialogowego „ Solver-

parametry” . Miejsce zerowe wynosi: x = +3 (rys.14).

Wersja dokumentu: 0.0.1

2 listopada 2006

- 7 -

Rys.14. Rozwi zanie (II miejsce zerowe x = +3)