EXCEL

Wstęp do lab. 4

Szukaj wyniku• Prosta procedura iteracyjnego znajdowania

niewiadomej spełniającej warunek będący jej funkcją

• Metoda:– Wstążka Dane: narzędzia danych/ Analiza

symulacji/szukaj wyniku

– Wskazać komórkę celu – tę w której ma pojawić się pożądana wartość (Ustaw komórkę)

– Podać wartość jaką ma przyjąć komórka (Wartość:)

– Wskazać komórkę, której zmienianie powoduje zmianę w komórce celu (Zmieniając komórkę:)

Szukaj wyniku

• Komórki pośrednie– Pomiędzy komórką zmienianą a komórką celu– Zależą od komórki zmienianej lub od

obliczonych wcześniej komórek pośrednich

• Inspekcja formuł– Pozwala śledzić powiązania między komórkami

• Śledź poprzedniki – w „górę” od komórki

• Śledź zależności = w „dół” od komórki

Szukaj wyniku

x

x

ey22

22 xz

x

zh hey

Solver• Zaawansowane narzędzie

pozwalające na rozwiązywanie skomplikowanych problemów optymalizacyjnych i układów równań nieliniowych.

• Po instalacji Excela jest niedostępny. Należy go dodać: Plik/Opcje programu Excel/Dodatki/ Zarządzaj: Dodatki-Przejdź

• Pojawia się na wstążce Dane grupa Analysis

• Set Objective: komórka, która stanowi warunek

• To: określa cel procedury: – Max - znalezienie maksymalnej wartości jaką może

przyjąć komórka celu– Min - minimalnej wartości– Value of: - konkretnej wartości wpisanej w okienko

• By Changing Variable Cells: (Komórki zmieniane) jedna lub więcej komórek wpływających na wartość w komórce celu

• Subject to the constraints (Warunki ograniczające) – pozwalają na poszukiwanie rozwiązania w ograniczonych przedziałach zmienności zmiennych niezależnych (komórek zmienianych)

• Warunek ograniczający– Umożliwia określenie zakresu zmienności w komórce

zmienianej. Komórka zmieniana może być• <= od pewnej wartości

• >= od pewnej wartości

• = pewnej wartości

• Int – całkowita

• Bin - binarna

– „Warunek ograniczający" może być liczbą lub odwołaniem do komórki

– Przycisk [OK] dodaje warunek i zamyka okno [Dodaj] nie zamyka okna.

• OPCJE Umożliwiają:– Ograniczenie czasu obliczeń– Ograniczenie liczby iteracji– Określenie dokładności –różnicy

między wartością obliczoną o docelową

– Określenie tolerancji – na ile procent rozwiązanie odbiega od wartości pożądanej; dotyczy zagadnień z wartościami całkowitymi w komórkach

– Określenie zbieżności – względnej zmiany wyniku w 5 ostatnich krokach; dotyczy zagadnień nieliniowych

– Przyjmij liniowy – przyspiesza znalezienie rozwiązania zagadnień liniowych

– Przyjmij nieujemne – wartości w komórkach zmienianych są >=0

– Automatycznie skaluje dane jeżeli różnią się o wiele rzędów od wyników

– Pokaż wyniki iteracji – zatrzymuje po każdym kroku iteracyjnym

• OPCJE Umożliwiają:– Określenie dokładności – – Automatyczne skalowanie– Pokazywanie każdego kroku

rozwiązania– Dokładność osiągnięcia

warunków całkowitych– Ograniczenie liczby iteracji– Ograniczenie czasu obliczeń

• Zbieżność

• Pochodne: sposób obliczania pochodnej– W przód szybsze ale

mniej dokładne– Centralne – więcej

obliczeń ale większa dokładność metody. Do stosowania przy szybkich zmianach wartości

Metoda stosowania Solvera1. Jako pierwsze należy wpisać wartości startowe

poszukiwanych zmiennych (uwaga na liczby w zapisie zmiennoprzecinkowym, poprawnie np. 1,1E5)

2. Wpisać odpowiednie równanie/a, prowadzące do jednej wartości liczbowej (wyniku) zależnej od wszystkich komórek z wartościami startowymi

3. Uruchomić Solvera. Wskazać jako komórkę celu komórkę zawierającą wartość z p. 2

4. Wybrać rodzaj operacji (min., max., wartość)5. Wskazać wszystkie komórki wpływające na komórkę celu (z

p.1) (jeżeli nie są obok siebie wskazując kolejne trzymać naciśnięty [ctrl])

6. Opcjonalnie wpisać ograniczenia i ustawić OPCJE7. Uruchomić Solvera.

Przykład

• Znaleźć rozwiązanie układu równań:

W zakresie x od –2 do 0

22

122

xy

xxy

Solver vs. Iinia trendu

Aproksymacja średniokwadratowa

odchyłka

0

5

10

15

20

25

30

0 2 4 6 8 10 12 14

xi

yi

iii xfyy

Suma kwadratów odchyłek

xi

yi

i

iyE 2

Warunek poprawnego dopasowania

0

5

10

15

20

25

30

0 2 4 6 8 10 12 14