| Przykład 2. Rozwiązywanie układów równań liniowych niejednorodnych metodą odwracania macierzy i mnożenia macierzy za pomocą odpowiednich funkcji MS Excela |
|
|
| Układ równań niejednorodnych - oznaczony |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Stosunek maksymalny : |
1,0000E+10 |
|
|
|
Element maksymalny : |
9,0000 |
|
|
|
|
|
|
|
|
|
|
Element maksymalny macierzy odwróconej : |
9,0000E+10 |
|
|
|
|
|
ai,j |
yi |
|
Wartości bezwzględne współczynników |
|
|
|
|
|
|
|
2,0000 |
1,0000 |
3,0000 |
9,0000 |
|
2,0000 |
1,0000 |
3,0000 |
9,0000 |
|
|
|
|
1,0000 |
-2,0000 |
1,0000 |
-2,0000 |
|
1,0000 |
2,0000 |
1,0000 |
2,0000 |
|
|
|
|
3,0000 |
2,0000 |
2,0000 |
7,0000 |
|
3,0000 |
2,0000 |
2,0000 |
7,0000 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Macierz odwrotna A-1 |
|
|
|
|
Jest to ulepszona wersja wykorzystania funkcji MS Excela MACIERZ.ODW() i MACIERZ.ILOCZYN() do rozwiązywania układu równań niejednorodnych metodą odwracania macierzy.
1. W komorkach B7:E9 należy wpisać współczynniki układu równań niejednorodnych - ostatnie kolumna jest kolumną wyrazów wolnych.
2. W komórce C4 należy wpisać akceptowany maksymalny stosunek bezwzględnych wartości elementów maksymalnych macierzy pierwotnej oraz macierzy do niej odwrotnej - na podstawie testów ustalono, że dla niezbyt dużych układów równań o niezbyt różnych współczynnikach jest to wartość rzędu 1010.
3. Na podstawie wartości bezwzględnych macierzy rozszerzonej wyznaczana jest wartość maksymalna elementów (z pominięciem znaku), która pomnożona przez wartość maksymalnego akceptowanego stosunku (komórka C4) daje akceptowaną maksymalną wartość elelentu macierzy odróconej (wartość ta zostaje wyświetlona w komórce I5)
4. Wartość macierzy odwrotnej zwraca funkcja MACIERZ.ODW().
5. Wartości bezwzględne elementów macierzy odwrotnej są porównywane z wartością wyświetloną w komórce I4 - jeżeli są one większe to są zastępowane tekstem "brak", w przeciwnym razie są kopiowane - w ten sposób jest tworzona "macierz odwrotna skorygowana"
3. Wektor pierwiastków jest obliczany za pomocą funkcji MACIERZ.ILOCZYN() z wykorzystaniem macierzy odwrotnej skorygowanej.
UWAGA. Obie wymienione funkcje są funkacjami tablicowymi. Przed ich wywołaniem należy zaznaczyć odpowiednie bloki komórek. Dla funkcji MACIERZ.ODW() jest to blok kwadratowy o wymiarach identycznych z wymiarami macierzy odwracanej, natomiast dla funkcji MACIERZ.ILOCZYN() jest to pionowy wektor o liczbie wierszy równej liczbie wierszy (i kolumn) kwadratowej macierzy współczynników aij (tzn. równej liczbie równań rozwiązywanego układu)
Opisane postępowanie jest skuteczne w stosunku do niezbyt dużego układu równań - MUSI TO BYĆ UKŁAD OZNACZONY. W takim przypadku macierz współczynników aij nie jest osobliwa i można ją odwrócić. Wyniki porównać z uzyskanymi na arkuszu "Macierz_1a" - jest to taki sam układ równań.
|
|
|
|
|
|
|
|
-4,6154E-01 |
3,0769E-01 |
5,3846E-01 |
|
|
|
|
|
|
|
|
|
|
7,6923E-02 |
-3,8462E-01 |
7,6923E-02 |
|
|
|
|
|
|
|
|
|
|
6,1538E-01 |
-7,6923E-02 |
-3,8462E-01 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Macierz odwrotna A-1 skorygowana |
|
|
|
Wektor x |
|
|
|
|
|
|
|
|
-4,6154E-01 |
3,0769E-01 |
5,3846E-01 |
|
-1,0000E+00 |
|
|
|
|
|
|
|
|
7,6923E-02 |
-3,8462E-01 |
7,6923E-02 |
|
2,0000E+00 |
|
|
|
|
|
|
|
|
6,1538E-01 |
-7,6923E-02 |
-3,8462E-01 |
|
3,0000E+00 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Przykład 2. Rozwiązywanie układów równań liniowych niejednorodnych metodą odwracania macierzy i mnożenia macierzy za pomocą odpowiednich funkcji MS Excela |
|
|
| Układ równań niejednorodnych - nieoznaczony |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Stosunek maksymalny : |
1,0000E+10 |
|
|
|
Element maksymalny : |
9,0000 |
|
|
|
|
|
|
|
|
|
|
Element maksymalny macierzy odwróconej : |
9,0000E+10 |
|
|
|
|
|
ai,j |
yi |
|
|
|
|
|
|
|
|
|
2,0000 |
1,0000 |
3,0000 |
9,0000 |
|
2,0000 |
1,0000 |
3,0000 |
9,0000 |
|
|
|
|
1,0000 |
-2,0000 |
1,0000 |
-2,0000 |
|
1,0000 |
2,0000 |
1,0000 |
2,0000 |
|
|
|
|
3,0000 |
-1,0000 |
4,0000 |
7,0000 |
|
3,0000 |
1,0000 |
4,0000 |
7,0000 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Macierz odwrotna A-1 |
|
|
|
|
Jest to ulepszona wersja wykorzystania funkcji MS Excela MACIERZ.ODW() i MACIERZ.ILOCZYN() do rozwiązywania układu równań niejednorodnych metodą odwracania macierzy.
1. W komorkach B7:E9 należy wpisać współczynniki układu równań niejednorodnych - ostatnie kolumna jest kolumną wyrazów wolnych.
2. W komórce C4 należy wpisać akceptowany maksymalny stosunek bezwzględnych wartości elementów maksymalnych macierzy pierwotnej oraz macierzy do niej odwrotnej - na podstawie testów ustalono, że dla niezbyt dużych układów równań o niezbyt różnych współczynnikach jest to wartość rzędu 1010.
3. Na podstawie wartości bezwzględnych macierzy rozszerzonej wyznaczana jest wartość maksymalna elementów (z pominięciem znaku), która pomnożona przez wartość maksymalnego akceptowanego stosunku (komórka C4) daje akceptowaną maksymalną wartość elelentu macierzy odróconej (wartość ta zostaje wyświetlona w komórce I5)
4. Wartość macierzy odwrotnej zwraca funkcja MACIERZ.ODW().
5. Wartości bezwzględne elementów macierzy odwrotnej są porównywane z wartością wyświetloną w komórce I4 - jeżeli są one większe to są zastępowane tekstem "brak", w przeciwnym razie są kopiowane - w ten sposób jest tworzona "macierz odwrotna skorygowana"
3. Wektor pierwiastków jest obliczany za pomocą funkcji MACIERZ.ILOCZYN() z wykorzystaniem macierzy odwrotnej skorygowanej.
UWAGA. Obie wymienione funkcje są funkacjami tablicowymi. Przed ich wywołaniem należy zaznaczyć odpowiednie bloki komórek. Dla funkcji MACIERZ.ODW() jest to blok kwadratowy o wymiarach identycznych z wymiarami macierzy odwracanej, natomiast dla funkcji MACIERZ.ILOCZYN() jest to pionowy wektor o liczbie wierszy równej liczbie wierszy (i kolumn) kwadratowej macierzy współczynników aij (tzn. równej liczbie równań rozwiązywanego układu)
Opisane postępowanie jest skuteczne w stosunku do niezbyt dużego układu równań - Ponieważ jest to UKŁAD NIEOZNACZONY, to macierz współczynników aij JEST OSOBLIWA i nie można jej odwrócić. Z powodu złej stabilnośći numerycznej algorytmu i kumulacji błędów zaokrągleń funkcja MACIERZ.ODW() "odwraca formalnie" również niekróre macierze osobliwe. W takim przypadku elementy macierzy odwrotnej mają wartości absurdalnie duże i elementy macierzy odwrotnej skorygowanej są zastępowane tekstem "brak", co powoduje błąd mnożenia. Wyniki porównać z uzyskanymi na arkuszu "Macierz_1b" - jest to taki sam układ równań.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Macierz odwrotna A-1 skorygowana |
|
|
|
Wektor x |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Przykład 2. Rozwiązywanie układów równań liniowych niejednorodnych metodą odwracania macierzy i mnożenia macierzy za pomocą odpowiednich funkcji MS Excela |
|
|
| Układ równań niejednorodnych - sprzeczny |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Stosunek maksymalny : |
1,0000E+10 |
|
|
|
Element maksymalny : |
5,0000 |
|
|
|
|
|
|
|
|
|
|
Element maksymalny macierzy odwróconej : |
5,0000E+10 |
|
|
|
|
|
ai,j |
yi |
|
|
|
|
|
|
|
|
|
2,0000 |
3,0000 |
-1,0000 |
1,0000 |
|
2,0000 |
3,0000 |
1,0000 |
1,0000 |
|
|
|
|
1,0000 |
-1,0000 |
1,0000 |
2,0000 |
|
1,0000 |
1,0000 |
1,0000 |
2,0000 |
|
|
|
|
3,0000 |
2,0000 |
0,0000 |
5,0000 |
|
3,0000 |
2,0000 |
0,0000 |
5,0000 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Macierz odwrotna A-1 |
|
|
|
|
Jest to ulepszona wersja wykorzystania funkcji MS Excela MACIERZ.ODW() i MACIERZ.ILOCZYN() do rozwiązywania układu równań niejednorodnych metodą odwracania macierzy.
1. W komorkach B7:E9 należy wpisać współczynniki układu równań niejednorodnych - ostatnie kolumna jest kolumną wyrazów wolnych.
2. W komórce C4 należy wpisać akceptowany maksymalny stosunek bezwzględnych wartości elementów maksymalnych macierzy pierwotnej oraz macierzy do niej odwrotnej - na podstawie testów ustalono, że dla niezbyt dużych układów równań o niezbyt różnych współczynnikach jest to wartość rzędu 1010.
3. Na podstawie wartości bezwzględnych macierzy rozszerzonej wyznaczana jest wartość maksymalna elementów (z pominięciem znaku), która pomnożona przez wartość maksymalnego akceptowanego stosunku (komórka C4) daje akceptowaną maksymalną wartość elelentu macierzy odwróconej (wartość ta zostaje wyświetlona w komórce I5)
4. Wartość macierzy odwrotnej zwraca funkcja MACIERZ.ODW().
5. Wartości bezwzględne elementów macierzy odwrotnej są porównywane z wartością wyświetloną w komórce I4 - jeżeli są one większe to są zastępowane tekstem "brak", w przeciwnym razie są kopiowane - w ten sposób jest tworzona "macierz odwrotna skorygowana"
3. Wektor pierwiastków jest obliczany za pomocą funkcji MACIERZ.ILOCZYN() z wykorzystaniem macierzy odwrotnej skorygowanej.
UWAGA. Obie wymienione funkcje są funkacjami tablicowymi. Przed ich wywołaniem należy zaznaczyć odpowiednie bloki komórek. Dla funkcji MACIERZ.ODW() jest to blok kwadratowy o wymiarach identycznych z wymiarami macierzy odwracanej, natomiast dla funkcji MACIERZ.ILOCZYN() jest to pionowy wektor o liczbie wierszy równej liczbie wierszy (i kolumn) kwadratowej macierzy współczynników aij (tzn. równej liczbie równań rozwiązywanego układu)
Opisane postępowanie jest skuteczne w stosunku do niezbyt dużego układu równań - Ponieważ jest to UKŁAD SPRZECZNY, to macierz współczynników aij JEST OSOBLIWA i nie można jej odwrócić. Z powodu złej stabilnośći numerycznej algorytmu i kumulacji błędów zaokrągleń funkcja MACIERZ.ODW() "odwraca formalnie" również niekróre macierze osobliwe. W takim przypadku elementy macierzy odwrotnej mają wartości absurdalnie duże i elementy macierzy odwrotnej skorygowanej są zastępowane tekstem "brak", co powoduje błąd mnożenia. Wyniki porównać z uzyskanymi na arkuszu "Macierz_1c" - jest to taki sam układ równań.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Macierz odwrotna A-1 skorygowana |
|
|
|
Wektor x |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Przykład 2. Rozwiązywanie układów równań liniowych niejednorodnych metodą odwracania macierzy i mnożenia macierzy za pomocą odpowiednich funkcji MS Excela |
| Badanie stabilności numerycznej funkcji MACIERZ.ODW() |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Macierz |
|
Macierz - wartości bezwzględne |
|
Element maksymalny |
|
1,0000 |
2,0000 |
3,0000 |
|
1,0000 |
2,0000 |
3,0000 |
|
|
|
4,0000 |
5,0000 |
6,0000 |
|
4,0000 |
5,0000 |
6,0000 |
|
9,0000 |
|
5,0000 |
7,0000 |
9,0000 |
|
5,0000 |
7,0000 |
9,0000 |
|
|
|
|
|
|
|
|
|
|
|
|
|
Macierz odwrotna A-1 |
|
Macierz odwrotna A-1 - wartości bezwzględne |
|
|
|
Err:502 |
Err:502 |
Err:502 |
|
Err:502 |
Err:502 |
Err:502 |
|
|
|
Err:502 |
Err:502 |
Err:502 |
|
Err:502 |
Err:502 |
Err:502 |
|
Err:502 |
|
Err:502 |
Err:502 |
Err:502 |
|
Err:502 |
Err:502 |
Err:502 |
|
|
|
|
|
|
|
|
|
|
|
Stosunek elementów maksymalnych |
|
|
|
|
|
|
|
|
|
Err:502 |
|
|
|
|
|
|
|
|
|
|
|
Arkusz został przygotowany do badania stabilności numerycznej funkcji MS Excela MACIERZ.ODW().
1. W komorkach B5:D6 należy wpisać dowolne liczby (dodatnie lub ujemne np. z przedziału (-1000, 1000) - zarówno o bliskich sobie wartościach bezwzględnych, jak i różniące się o wiele rzędów.
2. Wartości w komórkach B7:D7 (trzeci wiersz macierzy zaznaczony kolorem niebieskim) są obliczane automatycznie jak suma odpowiednich elementów w wierszu pierwszym i drugim.
3. Taka macierz jest osobliwa i "z definicji" nie istnieje macierz do niej odwrotna.
4. Macierz odwrotną zwraca funkcja matematyczna MACIERZ.ODW().
5. Elementy macierzy są kopiowane automatycznie - z pominięciem znaku; funkcja MAX() zwraca wartość elementu maksymalnego macierzy (z pominięciem znaku) pierwotnej (komórka J6) oraz macierzy odwrócojej (komórka J11)
6. W komórce J14 szacowany jest stosunek elementów maksymanlnych (z pominięciem znaku) macierzy odwróconej i pierwotnej.
7. Zwracamy uwagę, że dla niektórych zbiorów współczynników funkcja zwraca komunikat błędu "LICZBA ?" (co wynika z niemożliwości wykonywana operacji dzielenia przez zero, które pojawia się jako wynik odejmowania podczas realizacji algorytmu odwracania macierzy. Formalne "odwrócenie macierzy osobliwej" prowadzi do absurdalnych wyników przy zastosowaniu tej.metody do obliczania pierwiastków układu równań niejednorodnych w sposób "automatyczny".
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|