Programowanie w VBA

Programowanie
w VBA
Metody numeryczne część 1.
Rozwiązywanie układów równań liniowych.
Krztyna teorii
Układ równań liniowych niejednorodnych:
a11x1 
a1m xn
 b1
a21x1  a22 x2  ...  a2 j x j  ...  a2 m xn
 b2
...
ai1 x1 
...
a12 x2  ... 
...
...
ai 2 x2  ... 
...
...
an1 x1  an 2 x2  ... 
a1 j x j  ... 
...
...
...
...
aij x j 
... 
aim xn
 bi
...
...
...
...
anj x j  ...  anm xn
 bn
x – niewiadoma
a i b – współczynniki wyliczane lub uzyskiwane
doświadczalnie
Krztyna teorii c.d.
Analityczne rozwiązanie polega na sprowadzeniu układu
równań do postaci:
x1  c1
x2
 c2
...
...
xi
 ci
...
...
xn
 cn
zwykle za pomocą mniej lub bardziej zorganizowanych
działań na równaniach (mnożenie obu stron przez stałe,
odejmowanie stronami, podstawianie jednych wyliczonych
niewiadomych do innych równań itd.).
Krztyna teorii c.d.
 a11 a12
a
 21 a22
 ... ...

 ai1 ai 2
 ... ...

an1 an 2
... a1 j
... a1m
... a2 j
... a2 m
...
...
...
...
aij
... aim
...
...
...
... anj
...
...
... anm
Macierz współczynników
 a11 a12
a
 21 a22
 ... ...

 ai1 ai 2
 ... ...

an1 an 2
... a1 j
... a1m
... a2 j
... a2 m
...
...
...
...
aij
... aim
...
...
...
... anj
...
...
... anm









 x1 
x 
 2
 ... 
 
 xi 
 ... 
 
 xn 
Macierz rozwiązań
b1 
b2 
... 

bi 
... 

an 
Macierz rozszerzona układu równań
 b1 
b 
 2
 ... 
 
 bi 
 ... 
 
bn 
Macierz wyrazów wolnych
AX  B
Równanie macierzowe
Metoda Gaussa-Jordana
Polega na przekształceniu macierzy rozszerzonej
do macierzy jednostkowej (tzw. metoda eliminacji macierzy)
poprzez działanie na wszystkich wierszach następująco:
1. Normowanie wiersza względem wyrazu głównego
(położonego na przekątnej – aii): wszystkie elementy wiersza
i są dzielone przez element aii, aby był on równy 1.
2. Jeśli aii=0, szukamy najbliższego elementu akl≠0 o k>i
i l>i, i zamieniamy wiersze i i k ze sobą. Jeśli taki element nie
zostanie znaleziony, dalsza eliminacja niemożliwa.
3. Odejmujemy wiersz i od pozostałych wierszy tyle razy,
żeby w całej kolumnie i wszystkie wyrazy poza aii były
równe 0.
Na koniec zostaje macierz jednostkowa, a wektor wyrazów
wolny jest równy macierzy rozwiązań.
Zbieżność i cyfrowy zapis liczb
Zadanie:
1. Wpisz w komórkę A1 cyfrę "1";
2. Wpisz w komórkę A2 formułę "=A1/3"
3. Zaznacz obie powyższe komórki, sformatuj ich
zapis liczbowy tak, żeby miały 20 cyfr po
przecinku.
4. Zmodyfikuj formułę w komórce A2 na: "A1/11"
Zbieżność i cyfrowy zapis liczb
Liczba rzeczywista jest zapisana w pamięci
komputera w postaci binarnej z ograniczoną ilością
bitów przeznaczoną na zapis cyfr znaczących (dla
Double – 52 bitów, reszta przeznaczona jest na znak
i potęgę "przesunięcia" zapisu, tak jak w zapisie
naukowym liczb: 1,2E02 oznacza 120).
W praktyce można więc zapisać zaledwie 15-16 cyfr
znaczących. Stąd każdy zapis jest obarczony błędem
na "końcu" zapisu cyfr, który może rosnąć w miarę
obliczeń. Dlatego przyjęcie pożądanej dokładności
wyniku na mniej niż 10^-10 może spowodować, że
algorytm numeryczny nigdy nie osiągnie tej
dokładności – sam błąd wynikający z błędu zapisu
może być podobnego rzędu. Dodatkowo można
ograniczyć liczbę iteracji (np. 100 – dlaczego tyle?).
Metoda odwracania macierzy
Pamiętając o tym, że iloczyn macierzy nie jest
przemienny i że macierzowy iloczyn "A A-1 = I"
(gdzie I to macierz jednostkowa), można wyliczyć
wektor rozwiązań X za pomocą przekształcenia
równania "A X = B" do równania "X = A-1 B".
W arkuszu kalkulacyjnym mamy funkcje
tablicowe bloków:
- MACIERZ.ODW(macierz)
- MACIERZ.ILOCZYN(macierz1;macierz2)
Uwaga na błędy zaokrągleń zapisu binarnego
liczb! Liczba 10-16 to numeryczne zero (rząd
ostatniego bitu)!
Metoda Cramera
a11 ... ai1 ... am1
Wyznacznik macierzy A (DetA)
zapisujemy tak:
...
det A  a1 j
...
...
...
...
... aij
... amj
...
...
...
a1n
... ain ... amn
...
...
DetA≠0 dla układu równań liniowo niezależnych,
co jest warunkiem koniecznym rozwiązania układu
równań, z którego współczynników jest macierz A.
Macierz A z kolumną i zastąpioną
wektorem wyrazów wolnych układu
równań oznaczamy jako macierz Ai:
a11 ... b1
...
det Ai  a1 j
... am1
... ... ...
... b j
...
... amj
...
... ... ...
a1n
... bn
...
... amn
Metoda Cramera c.d.
Kolejne elementy wektora rozwiązań X są
równe: xi = detAi/detA .
Metoda Cramera pozwala na identyfikacje
problemów przy rozwiązywaniu układu
równań. Dla DetA=0 układ jest nieoznaczony
lub sprzeczny, a konkretnie:
- Jeśli detAi=0 dla wszystkich i, to układ jest
nieoznaczony;
- Jeśli dla przynajmniej jednego i detAi≠0,
to układ jest sprzeczny.
Programowanie
w VBA
Metody numeryczne część 2.
Rozwiązywanie równań nieliniowych.
Krztyna teorii
Rozwiązanie równania to w formie graficznej
znalezienie przecięcia wykresy funkcji z osią
X. Dana jest ciągła w otoczeniu pierwiastka
funkcja, gdzie rozwiązanie ma ogólną
postać: f(x)=0
Nie dla każdego rodzaju funkcji wystarczają
do rozwiązania metody analityczne
(algebraiczne). W większości przypadków
potrzebne są metody numeryczne do
otrzymania przybliżonego wyniku.
Metoda bisekcji
Przypadek, gdzie f(x)*f(a)<0
czyli między x i a zmienił się znak,
czyli jest miejsce zerowe.
Y
Y
Przypadek, gdzie f(x)*f(a)>0,
czyli między x i a nie zmienił się
znak, czyli nie ma miejsca zerowego.
Nie ma miejsca zerowego
między x i a, więc x to nowe a
f(b)
f(x)
a
x=(a+b)/2 b
a:=x
X
a
b:=x
f(a)
Nie ma miejsca zerowego
między x i b, więc x to nowe b
f(b) X
x=(a+b)/2 b
f(x)
f(a)
Metoda bisekcji
1. Wyznaczamy przedział [a,b] z tylko jednym miejscem
zerowym;
2. Obliczamy f(a) i f(b), gdzie f(a)*f(b)<0
(musi być tylko jeden pierwiastek);
3. Przybliżenie to środek przedziału: xi=(a+b)/2;
4. Liczymy f(xi) - jeśli wartość jest mniejsza niż założona
dokładność docelowa rozwiązania εx, to xi jest
numerycznym rozwiązaniem, jeśli f(xi) jest większa niż
docelowa dokładność, to sprawdzamy f(a)*f(xi)<0 – jeśli
prawda, to b:=xi, jeśli nie, to a:=xi;
5. Powtarzamy punkty 3 i 4, aż przedział a-b będzie
węższy niż docelowa dokładność εx.
Metoda bisekcji
START
f(a)∙f(b)<0
NIE
TAK
xi=(a+b)/2
TAK
|xi-xi-1|<εx
NIE
f(a)∙f(x)<0
NIE
TAK
b:=x
NIE
b-a<εx
TAK
KONIEC; x=xi
a:=x
KONIEC,
błąd;
Metoda bisekcji (połowienia
przedziału) jest zawsze
zbieżna, o ile w początkowym
przedziale funkcja ma
dokładnie jedno miejsce
zerowe i jest w tym obszarze
ciągła. Każda iteracja (krok w
pętli) zmniejsza dwukrotnie
przedział [a,b] – dając pewność
równomiernego zwiększenia
dokładności w iteracji.
Metoda interpolacji liniowej
Przypadek, gdzie f(x)*f(a)<0
czyli między x i a zmienił się znak,
czyli jest miejsce zerowe.
Y
Y
Przypadek, gdzie f(x)*f(a)>0,
czyli między x i a nie zmienił się
znak, czyli nie ma miejsca zerowego.
Nie ma miejsca zerowego
między x i a, więc x to nowe a
f(b)
f(x)
a
X
x
f(a)
a:=x
b
x
a
b:=x
f(a)
Nie ma miejsca zerowego
między x i b, więc x to nowe b
f(x)
f(b) X
b
Metoda interpolacji liniowej
1. Wyznaczamy przedział [a,b] z tylko jednym miejscem
zerowym;
2. Obliczamy f(a) i f(b), gdzie f(a)*f(b)<0
(musi być tylko jeden pierwiastek);
3. Przybliżenie to środek przedziału:
xi=b-f(b)*(b-a)/(f(b)-f(a));
4. Liczymy f(xi) - jeśli wartość jest mniejsza niż założona
dokładność docelowa rozwiązania εx, to xi jest
numerycznym rozwiązaniem, jeśli f(xi) jest większa niż
docelowa dokładność, to sprawdzamy f(a)*f(xi)<0 – jeśli
prawda, to b:=xi, jeśli nie, to a:=xi;
5. Powtarzamy punkty 3 i 4, aż przedział a-b będzie
węższy niż docelowa dokładność εx.
Metoda interpolacji liniowej
START
f(a)∙f(b)<0
NIE
TAK
xi=b-f(b)∙(b-a)/(f(b)-f(a)
TAK
|xi-xi-1|<εx
NIE
f(a)∙f(x)<0
NIE
TAK
b:=x
NIE
b-a<εx
TAK
KONIEC; x=xi
a:=x
KONIEC,
błąd;
Metoda interpolacji liniowej jest
co do zasady identyczna z
metodą bisekcji, poza
przybliżeniem, które wynika z
interpolacji liniowej. Metoda ta
jest zazwyczaj szybciej zbieżna
niż metoda bisekcji, aczkolwiek
w nielicznych przypadkach
może być wolniejsza – nie daje
gwarancji równomiernego
zwiększania dokładności.
Metoda Newtona
Y
Metoda Newtona jest potencjalnie
najszybciej zbieżna z przedstawionych
f'(x0)
metod. Wymaga ona jednak
f(x0)
znajomości pierwszej pochodnej danej
f'(x1)
f(x1)
funkcji, oraz ciągłych i nie
X
zmieniających znaku pierwszej i drugiej
x2 x1 x0
pochodnej między miejscem zerowym
a punktem startowym. Czyli funkcja
musi być w całym zakresie działania
START
monotoniczna i albo tylko wklęsła albo
x:=x0-f(x0)/f'(x0)
tylko wypukła. Dodatkowo w punkcie
TAK KONIEC startowym musi być spełnione
|x-x0|<εx
x=xi
równanie f(x)∙f'(x)>0, aby przybliżanie w
NIE
miarę iteracji było na osi X w kierunku
x0:=x
miejsca zerowego, a nie w przeciwnym.
Metoda Newtona
1. Podajemy wartość początkową
pierwiastka (x0), w którym jest spełnione
równanie f(x)*f'(x)>0;
2. Pochodna w punkcie startowym to:
f'(x)=f(x0)/(x0-x1), więc: x1=x0-(f(x0)/f'(x0))
3. Obliczona wartość x1 to punkt startowy
dla następnej iteracji: x0=x1.
4. Powtarzamy punkty 3 i 4 aż |xi-xi-1|<εx