Zastosowanie Excela (VBA)
w inżynierii finansowej
Tomasz Węgrzyn
Katedra Matematyki Stosowanej
1
Informacje wstępne
Konsultacje: wtorek 13.30  15.00 (118P)
Email: [e-mail]
Strona www Katedry: www.kms.ue.katowice.pl
2
Informacje wstępne
Egzamin przedmiotu:
dwa testy w trakcie semestru. Oba muszą być zaliczone.
Ocena to średnia z ocen. W przypadku, gdy nie ma oceny równej
średniej (np. 4,75) o zaokrągleniu w górę lub w dół decyduje wynik
drugiego testu:
- wyższa ocena z drugiego testu prowadzi do zaokrąglenia oceny w górę
- niższa ocena z drugiego testu prowadzi do zaokrąglenia oceny w dół
Dane do zadań będą dostępne poprzez platformę e-learningową.
3
Informacje wstępne
ćwiczenia:
Zadania dostępne na portalu e-moodle.
wykłady:
Dostępne na portalu e-moodle.
4
Cele przedmiotu
Zdobycie umiejętności tworzenia własnych makr
Zdobycie umiejętności korzystania z zaawansowanych funkcji Excela
(m.in. tabele przestawne, solver, szukaj wyniku)
Zdobycie umiejętności zastosowania Excela i VBA do rozwiązywania
problemów omawianych w ramach inżynierii finansowej
5
Literatura:
Obowiązkowa:
J. Korol: Visual Basic w Excelu 2000. Programowanie dla każdego,
MIKOM, Warszawa 2001
M.Jackson, M.Staunton: Zaawansowane modele finansowe z
wykorzystaniem Excela i VBA, Helion, Gliwice 20004
J. Walkenbach: Excel 2003 PL. Programowanie w VBA. Vademecum
profesjonalisty, Helion, Gliwice 2004
Zalecana:
M. Lewandowski: Tworzenie makr w VBA dla Excela 2003/2007,
Helion, Gliwice 2008
P. Czarny: VBA dla Excela 2007 PL, Helion, Gliwice 2008
6
dr Tomasz Węgrzyn
Plan wykładu
1. Formatowanie warunkowe
2. Filtrowanie
3. Sortowanie
4. wyszukaj.pionowo
5. Estymacja parametrów modelu liniowego
6. Tabele przestawne
7. Szukaj.wyniku
8. Operacje na datach/tworzenie dat
9. Solver
7
dr Tomasz Węgrzyn
Formatowanie warunkowe
Format | Formatowanie warunkowe
Przykład
8
dr Tomasz Węgrzyn
Filtrowanie
Dane | Filtr | AutoFiltr
Przykład
9
dr Tomasz Węgrzyn
Sortowanie
Dane | Sortuj
Przykład
10
dr Tomasz Węgrzyn
wyszukaj.pionowo
Składnia:
wyszukaj.pionowo(odniesienie ; tablica ; nr_kolumny ; zakres)
- odniesienie  wartość, która ma zostaćwyszukana w 1 kolumnie tablicy
- tablica  zakres, w którym znajduje sięprzeszukiwana tabela z wynikami, które
majązostaćdopasowane
- nr kolumny  nr kolumny w której znajdująsiędane, które majązostać
dopasowane
- zakres  wartośćlogiczna, 0 oznacza, że szukana wartośćmusi sięznalez
ćw 1
kolumnie tablicy aby została zwrócona wartość, 1 oznacza, że może zostać
zwrócona najbliższa wartośćnie większa od szukanej.
Przykład1
11
dr Tomasz Węgrzyn
Estymacja parametrów modelu liniowego
Funkcje Excela:
odcięta (y, x)  wyraz wolny modelu (parametr alfa)
nachylenie (y, x)  parametr stojący przy zmiennej zależnej (parametr beta)
r.kwadrat (y, x)
regbłstd (y, x)  odchylenie standardowe reszt, błąd standardowy
reglinp(y, x)  funkcja tablicowa, zwraca:
Parametr beta Parametr alfa
Błąd standardowy parametru beta Błąd standardowy parametru alfa
R kwadrat Odchylenie standardowe reszt
Średni błąd standardowy
Statystyka F Liczba stopni swobody
2 2
"(y - y) "(y - w
)
Przykład 7.
12
dr Tomasz Węgrzyn
Estymacja parametrów modelu liniowego
Procedura regresja z dodatku Analiza danych:
13
dr Tomasz Węgrzyn
Tabele przestawne
Tabela przestawna  służy do zestawiania danych pochodzących z bazy
danych lub tablicy umieszczonej w arkuszu. W przypadku tablicy
umieszczonej w arkuszu każda z kolumn musi zawierać nazwę
(określającą rodzaj umieszczonych w niej danych), natomiast w kolejnych
wierszach umieszczane są kolejne rekordy opisujące ten sam przypadek
np. klienta.
Za pomocą tabeli przestawnej dane zawarte w bazie danych mogą być
dowolnie zestawiane, umożliwiając w ten sposób poznanie przez
użytkownika struktury danych.
Przykład1.
14
dr Tomasz Węgrzyn
Szukaj wyniku
Funkcja szukaj wyniku pozwala na
znalezienie takiej wartości
parametru, aby zmienna zależna
przyjęła oczekiwanąwartość.
Pola funkcji szukaj wyniku:
1. Ustaw komórkę adres komórki,
której wartośćma zostaćokreślona
2. Wartość szukana wartość
3. Zmieniając komórkę adres
komórki zawierającej parametr.
Przykład 2.
15
dr Tomasz Węgrzyn
Solver
Sovler jest narzędziem pozwalającym na rozwiązanie bardziej
skomplikowanych problemów niż funkcja szukaj wyniku.
Cechy solvera:
- pozwala określić wiele komórek wejściowych (zawierających
parametry)
- pozwala wprowadzić ograniczenia dla parametrów
- pozwala szukać maksimum, minimum, określonej wartości
16
dr Tomasz Węgrzyn
Solver
Przykład 3.
17
dr Tomasz Węgrzyn
Operacje na datach (1)
Data  zwraca liczbę reprezentującą określoną datę w kodzie Excela
Składania
Data( rok ; miesiąc ; dzień)
Dzień.tyg  zwraca liczbę reprezentującą dzień tygodnia
Składania
Dzień.tyg( data ; typ)
1  niedziela=1, sobota=7
2  poniedziałek=1, niedziela=7
3  poniedziałek=0, niedziela=6
18
dr Tomasz Węgrzyn
Operacje na datach (2)
Rok  zwraca rok z daty
Składania
Rok( data)
miesiąc  zwraca miesiąc z daty
Składania
miesiąc( data)
dzień  zwraca dzień z daty
Składania
Przykład 4.
dzień( data)
19