RÓŻNE PRZYDATNE DODATKI DO EXCELA

 

Szanowni Państwo! Będą tu pozamieszczane różne dodatki i przykładki mające nam umilić korzystanie z MS Excela i rozmaitego softu tzn. wprowadzić więcej elementów które mogłyby się zepsuć J

Są tu działy:

 

Zbiory procedur

Funkcje

Makra

 

ZBIORY PROCEDUR

 

Polecam zbiory Foxes Team – bo tylko takie znalazłem – ale na razie działają OK. i nie mam zgłoszeń o błędach.

LINK: http://digilander.libero.it/foxes/SoftwareDownload.htm

Szczególnie interesuje nas dodatek MATRIX

 

Zainstalowanie go pozwala na wyliczanie wartości własnych macierzy (patrz zajęcia 16, 19)

Ponadto dużo wygodniej są tu napisane funkcje macierzowe, i tak:

Transpozycję wykonujemy poleceniem M_T(A)

Mnożenie macierzy: M_Prod(A;B;C...)

(tu – inaczej niż w Excelowskiej funkcji MACIERZ.ILOCZYN(A:B) – można pomnożyć więcej (do 9 chyba) macierzy, co jest bardzo wygodne przy dłuższych formułach)

            Odwracanie macierzy M_INV(A) itd.

Wygodniej się więc pisze wzór z klawiatury – nazwy poleceń są bardzo krótkie!

 

W nowszych wersjach matrixa nazwy funkcji są skrócone – nie ma w nich znaku „_” – np. MPROD(.;.)

 

Żeby zainstalować zbiór, trzeba go rozpakować; potem w Excelu wybrać Narzędzia->Dodatki->Przeglądaj i skierować do pliku matrix.xla. Jeżeli robią to Państwo w laboratorium, na pytanie „Czy skopiować pliki?” odpowiadamy NIE, bo Excel próbuje je zapisać na dysku sieciowym na którym nie ma praw zapisu. Ale mimo NIE będzie działać; gdy jest zainstalowany, we wstaw->funkcję pojawia się nowa grupa „Matrix”.

Gdy przenosimy plik z jednego komputera na inny, może pojawić się informacja, że „dokument zawiera łącza itd.”. Gdy otworzymy go w innym komputerze niż ten, na którym był robiony, w formułach pokaże się ścieżka dostępu w apostrofach i z wykrzyknikiem na końcu, a ponieważ nie jest aktualna, zamiast wartości w komórkach może być #ARG. Wtedy trzeba 1. zainstalować matrix 1.5. 2. z menu wybrać Edycja->zamień; w okienko „znajdź” wkopiowujemy z formuł ścieżkę dostępu ‘....’! i zamieniamy ją wszędzie na puste miejsce (klikamy Zamień wszystko). Powinno zacząć działać.

 

FUNKCJE

 

Tu będą przydatne funkcje, których nie ma w Zbiorach powyżej. Jest to robota chałupnicza i byle jaka, ale działa mam nadzieję J. Jeśli ktoś napisze lepiej, zamieszczę!

Jak dodać funkcję?

Trzeba iść do Narzędzi->Makra->Edytor Visual Basic [lewy alt i F11]

W edytorze najpierw Insert->Module (chyba że chcemy dodać do istniejącego modułu –lepiej nie mieć zbyt dużo modułów - wtedy do niego wchodzimy z okienka po lewej na górze)

A potem do kodu modułu można ctrl+C i ctrl +V wkopiować poniżej wybrany tekst

Jeśli dodaliśmy do innego modułu, to kliknijmy ikonkę SAVE

Funkcja nie pojawi się we wstaw funkcje, ale wpisana z klawiatury będzie działać...

UWAGA: podane niżej funkcje można składać, czyli stosować po kilka w formule!

 

 

Iloczyn Kroneckera

 

A. ILOCZYN KRONECKERA DOWOLNYCH MACIERZY

 

Robienie w Excelu na piechotę iloczynu Kroneckera jest okropnie uciążliwe, więc lepiej sobie napisać funkcję. Poniżej jest przykład, ale UWAGA! TA FUNKCJA NIE RADZI SOBIE Z DUŻYMI MACIERZAMI (np. wynik 100 na 100)! Jeśli uparcie zwraca ARG zamiast liczb, to mógł wystąpić ten problem.

TRZEBA WTEDY PIERWSZĄ MACIERZ z iloczynu ROZPISAĆ NA BLOKI (tak, aby rozmiar po wymnożeniu [blok Ä cała druga macierz] był jak ok. 50 na 100 lub 70 na 70 ), WYMNOŻYĆ OSOBNO (dla każdego bloku zrobić Kron(blok Ä druga macierz) ; REZULTAT UŁOŻYĆ W CAŁOŚĆ (macierz wynikowa będzie złożona z poszczególnych [Kron(blok Ä druga macierz)] na tej zasadzie na jakiej dzieliliśmy pierwszą macierz, tylko rozmiary będą proporcjonalnie większe ). Cały wynik trzeba nazwać i nazwy używać w formułach.

 

 

 

Function Kron(MatA, MatB)

 

Dim D, E, C()

D = MatA

E = MatB

 

m = UBound(D, 1)

n = UBound(D, 2)

o = UBound(E, 1)

p = UBound(E, 2)

 

ReDim C(m * o - 1, n * p - 1)

 

For i = 0 To m - 1

For j = 0 To n - 1

    For H = 0 To o - 1

    For g = 0 To p - 1

        C(i * o + H, j * p + g) = D(i + 1, j + 1) * E(H + 1, g + 1)

    Next g

    Next H

Next j

Next i

Kron = C

End Function

 

 

Przykład:

Kron(A;B) [jak mamy zdefiniowane nazwy A i B; na szczęście iloczyn Kroneckera działa niezależnie od wymiarów macierzy]]

Oczywiście obowiązkowo F2, SHIFT+CTRL+ENTER jest potrzebne bo to funkcja macierzowa.

 

B. ILOCZYN KRONECKERA: DOWOLNA MACIERZ Ä MACIERZ JEDNOSTKOWA

(MAKRO)

 

W szczególnym przypadku iloczynu Kroneckera z drugą macierzą będącą macierzą jednostkową bardziej niż funkcję opłaca się stosować makro. Wynika to z faktu, że macierz wynikowa zawiera wtedy bardzo dużo zer. Gdy wymagane jest wielokrotne wyliczanie iloczynu Kroneckera (jak w przypadku estymacji MNW modelu SURE, gdzie iloczyn Kroneckera jest wyliczany w każdym kroku solvera. Wtedy komórki mające wartość zero są też przesyłane poprzez funkcję (zupełnie niepotrzebnie), co zajmuje wiele czasu. Lepiej więc wykorzystać podane poniżej makro. Wykonuje ono iloczyn kroneckera dowolnej macierzy KWADRATOWEJ razy macierz jednostkowa ustalonego stopnia.

Do treści makra trzeba wpisać „namiary” na odnośne macierze. Uruchomienie Makra bez wpisania wartości może nam dużo w arkuszu napsuć. Argumenty których wartości trzeba wpisać do makra to:

(*)Współrzędne początku (lewego górnego wierzchołka) pierwszej macierzy oraz jej stopień (jeden wymiar, bo ma być to macierz kwadratowa) (*)Współrzędne początku (lewego górnego wierzchołka) wynikowej macierzy będącej iloczynem Kroneckera oraz stopień macierzy jednostkowej będącej drugim składnikiem iloczynu. Współrzędne podajemy jako numer wiersza/ kolumny. Aby znaleźć numer wiersza i kolumny dowolnej komórki najszybciej jest zacząć rozciągać zaznaczony obszar (SHIFT + STRZAŁKI) od A1 aż do szukanej komórki, wymiar obszaru się wyświetli i będą to jednocześnie jej współrzędne. OTO MAKRO tu jest link do wersji txt która powinna być lepsza: MAKRO

A COMMENT: SZANOWNI PAŃSTWO TO MAKRO JEST W WERSJI BETA. CZYLI DZIAŁA-NIE DZIAŁA. W związku z tym: A) proszę je odpalić w innym arkuszu niż ten gdzie się ma robotę, ale zadać dobre współrzędne i potem przekleić wynik – bo makro robi tylko strukturę. B) proszę mi w miarę możliwości mailować jak wyglądają problemy; gdyby ktoś jeszcze wiedział co trzeba zrobić żeby ich nie było, to help will be appreciated.

Powyższe makro trzeba sobie skopiować do modułu i uruchomić”

A)(można najpierw włączyć „zarejestruj nowe makro” , wybrać skrót, potem od razu nacisnąć „zatrzymaj rejestrowanie”, wejść do edytora VB (lewy ALT F11) i do treści nowego makra wkleić powyższy tekst (uważając, żeby Sub i End Sub się nie powtarzały). Wtedy możemy je odpalić skrótem który wybraliśmy wcześniej.

B) lub po prostu wkleić do modułu a potem uruchomić z menu narzędzia->makra – przycisk uruchom

 

MACIERZ OMEGA (kowariancji składników losowych)

 

Macierz OMEGA do autokorelacji składnika losowego typu AR(1)

 

Jest to macierz (funkcja macierzowa parametru j) o postaci:

 

 

Funkcję można napisać tak:

 

 

Function OM_ar1(fi, scalar)

Dim C()

k = scalar

m = fi

ReDim C(k - 1, k - 1)

For i = 1 To k

    For j = 1 To k

        C(i - 1, j - 1) = (m ^ (((j - i) ^ 2) ^ 0.5)) / (1 - m ^ 2)

    Next j

Next i

OM_ar1 = C

End Function

SKŁADNIA:

Zwraca ona macierz W (T na T) ; argumenty to wartość j oraz T (wymiar)

PRZYKŁAD:

OM_ar1(0,5; 39) – generuje macierz 39 na 39 według wzoru na W(j) powyżej z j = 0,5.

 

Przydatna jest funkcja odwrotności tej macierzy (występuje we wzorach):

 

Co można by zapisać jako:

 

Function OM_ar1_inv(fi, scalar)

Dim C() As Double

Dim k As Integer

k = scalar

m = -fi

 

If m = -0 Then m = 0

ReDim C(0 To k - 1, 0 To k - 1)

 

For i = 2 To k - 1

        C(i - 2, i - 1) = m

        C(i - 1, i - 1) = (1 + m ^ 2)

        C(i, i - 1) = m

Next i

C(0, 0) = 1

C(k - 1, k - 1) = 1

C(1, 0) = m

C(k - 2, k - 1) = m

OM_ar1_inv = C

End Function

SKŁADNIA:

Zwraca ona macierz W-1 (T na T) ; argumenty to wartość j oraz T (wymiar)

PRZYKŁAD:

OM_ar1_inv(0,5; 39) – generuje macierz 39 na 39 według wzoru na W-1(j) powyżej z j = 0,5.

 

Jest to macierz odwrócona analitycznie, co (szczególnie dla j bliskiego zeru) powinno być dokładniejsze niż odwracanie numeryczne.

 

Macierz OMEGA do modelowania heteroskedastyczności składnika losowego

 

Funkcje:

 

 

'funkcja zwraca macierz OMEGA^ dla testowania heteroskedastyczności.

'jest to macierz diagonalna stopnia T zawierająca

'na głównej przekątnej najpierw T1 jedynek a potem T-T1 wartości ratio

 

Function OM_hs(ratio, T_, T1)

Dim C() As Double

 

k1 = ratio

T = T_

t_1 = T1

 

ReDim C(0 To T - 1, 0 To T - 1)

 

For i = 1 To t_1

C(i - 1, i - 1) = 1

Next i

 

For i = t_1 + 1 To T

C(i - 1, i - 1) = k1

Next i

 

OM_hs = C

End Function

 

Składnia: OM_hs(fi; T; T1) zwraca macierz kwadratową stopnia T mającą zera poza przekątną, na przekątnej T1 jedynek a potem T-T1 wartości fi.

 

 

Funkcja zwracająca odwrotność OMEGA:

 

 

Function OM_hs_inv(ratio, T_, T1)

 

Dim C() As Double

 

k1 = ratio

T = T_

t_1 = T1

 

ReDim C(0 To T - 1, 0 To T - 1)

 

For i = 1 To t_1

C(i - 1, i - 1) = 1

Next i

 

For i = t_1 + 1 To T

C(i - 1, i - 1) = 1 / k1

Next i

 

OM_hs_inv = C

End Function

 

Składnia: OM_hs_inv(fi; T; T1) zwraca macierz kwadratową stopnia T mającą zera poza przekątną, na przekątnej T1 jedynek a potem T-T1 wartości 1/fi. Do obydwu funkcji posyła się te same argumenty; argument fi to s22 / s21 – proszę uważać żeby to się zgadzało!

 

Diagonalizacja Macierzy

 

Funkcja wyciągająca z macierzy kwadratowej elementy na jej głównej przekątnej. Przydatna do liczenia błędów średnich szacunku – żeby nie trzeba było na piechotę każdego osobno. Treść funkcji:

Function diag_(A)

 

Dim C, D

Dim k As Integer

D = A

k = UBound(D, 1)

ReDim C(1 To k, 0)

 

For ii = 1 To k

C(ii, 0) = D(ii, ii)

Next ii

 

diag_ = C

End Function

 

Przykład:

=diag_(W115:AK129)^0.5

zwróci wektor-kolumnę zawierający pierwiastki kwadratowe z elementów przekątniowych macierzy W115:AK129. [uwaga – mam ustawiony jako separator dziesiętny kropkę, normalnie trzeba by napisać ^0,5].

W matrixie jest diagonalizacja macierzy – funkcja MatDiagExtr(.)

 

Wektoryzacja Macierzy

 

Funkcja tworząca z macierzy wektor – ustawia kolumny zadanej macierzy jedna pod drugą. Zwraca wektor – kolumnę.

 

 

Function M_VEC(A1)

'funkcja wektoryzuje zadana macierz

'tzn. ustawia kolumny tej macierzy jedna pod druga tworzac wektor

    Dim i&, j&, k&

    Dim n1&, m1&

    Dim a, A2()

    a = A1

    n1 = UBound(a, 1)

    m1 = UBound(a, 2)

    k = m1 * n1

    ReDim A2(1 To k, 0)

    k = 1

    For i = 1 To m1

    For j = 1 To n1

            A2(k, 0) = a(j, i)

            k = k + 1

    Next j, i

    M_VEC = A2

End Function

 

 

 

 

MAKRA

 

jak zrobić makro do estymacji f. CES algorytmem Gaussa-Newtona?

Pomyślmy, co ma robić nasze makro? Dodawać poprawkę do ocen.

Więc robimy tak:

Narzędzia->makra->zarejestruj nowe makro

Wciskamy w okienku klawisz do skrótu (bez shift który jest defaultowo)

Zaznaczamy komórki z wartościami poprawki

Wciskamy ctrl+C

Klikamy w pierwszej komórce z wartościami ocen parametrów (uwaga: w tym miejscu do którego odwołujemy się wyliczając wartości A(b) i g(x,b)) klikamy prawym przyciskiem myszy, wklej specjalnie, zaznaczamy „wartości” oraz „dodaj” OK.

Klikamy przycisk zatrzymaj rejestrowanie (taki kwadracik czarny jak stop na CD. Jeśli go nie widać, to wybieramy z menu narzędzia->makra)

I koniec. Uwaga! nie radzę w tym czasie wykonywać nadprogramowych ruchów – scrollować ekranu, zaznaczać innych komórek czy cokolwiek, bo się wpiszą do makra.

Jeśli uruchomimy makro i wartości poprawki się nie zmieniają, to znaczy, że dodajemy oceny np. do punktów startowych zamiast do wartości z których się wyliczają A(b) i g(x,b)