Zaawansowane możliwości arkusza kalkulacyjnego - warsztaty problemowe.

1.        Funkcje bazodanowe - część 1

Funkcje bazodanowe pozwalają na wykorzystanie mechanizmów bazodanowych do analizy zestawu danych (bazy danych). Jakkolwiek groźnie to brzmi jest bardzo proste i ich opanowanie pozwoli na realizację zaawansowanych potrzeb użytkownika.

Wszystkie funkcje bazy danych mają podobną składnię.

BD.XXXXXX(baza_danych;pole;kryteria).

Parametr baza_danych to zakres komórek zawierający wszystkie dane (kolumny i wiersze) tworzące analizowane bazę. Nasza baza danych znajduje się arkuszu baza w pliku funkcje_BD.xls.

Zakres komórek jaki obejmuje to: A1:G1187. Ponieważ będziemy wykorzystywać przeciąganie warto zdefiniować zakres poprzez odwołania bezwzględne. Czyli $A$1:$G$1187. Dla przejrzystości wiele operacji wykonywanych będzie w innych arkuszach dlatego odwołanie do bazy danych będzie musiało zawierać jeszcze nazwę arkusza, w którym się znajduje, stąd odwołanie do bazy danych będzie miało postać baza!$A$1:$G$1187.

Parametr pole jest nazwą pola bazy danych (kolumny). W testowej bazie danych jest 7 pól.

Parametr pole może być odwołaniem do komórki, w której znajduje się nazwa pola.

Parametr kryteria to zakres komórek zawierających kryteria ich składnia jest podobna do składni kryteriów stosowanej w filtrowaniu zaawansowanym.

Ćwiczenie 1.                  Średni przychód i średni rozchód.

q       Wprowadź do odpowiednich komórek wartość jak na rysunku poniżej (Rysunek 1‑1).

Rysunek 1‑1. Przykład wykorzystania funkcji BD.ŚREDNIA.

q       Do komórki L2 wprowadź formułę =BD.ŚREDNIA(baza!$A$1:$G$1187;I3;$A$2:$G$3).

q       Skopiuj formułę z L2 do L3.

baza!$A$1:$G$1187 – jest zakresem bazy danych.

I3 – jest odwołaniem do komórki z nawą pola, które będzie przetwarzane.

$A$2:$G$3 – jest zakresem kryteriów.

Zakres kryteriów obejmuje dwa wiersze: wiersz tytułów i wiersz kryteriów, który jest pusty. Obliczona w ten sposób średnia jest średnią z wszystkich rekordów. Chcąc obliczyć średnią dla jednego z managerów należy wprowadzić imię managera do odpowiedniej komórki.

Ćwiczenie 2.                  Średnia dla managera.

q       Wpisz w G3 wartość Aurelia.

Wartości w komórkach L3:L4 zmieniły się.

Uzyskanie średniej dla kilku managerów wymaga poprawienia zakresu kryteriów w formułach w L3:L4.

q       Wpisz w G4 wartość Joanna.

Wartości w komórkach L3:L4 nie zmieniły się.

Popraw formuły w L3:L4 rozszerzając zakres kryteriów z $A$2:$G$3 na $A$2:$G$4.

Otrzymana średnie są średnimi obu managerów razem wziętych.

Ćwiczenie 3.                  Ile rekordów ma baza.

W poprzednich ćwiczeniach odwołanie do bazy danych zawierało zakres komórek, w których faktycznie były wpisane dane. Prowadzi do problemu aktualizacji formuł zawierających funkcje bazodanowe. Dotychczas odwołanie to miało postać: baza!$A$1:$G$1187, czyli obejmowało 1186 rekordów wpisanie w wierszu 1188 (1187 rekord, pierwszy wiersz jest wierszem tytułów pól) danych nie zmieni wartości funkcji odwołujących się do tego zakresu.

Rozwiązaniem jest rozszerzenie odwołania do bazy danych o dodatkowe puste wiersze poniżej. Ponieważ ostatni wiersz w arkuszu MS Excel ma numer 65536, odwołanie do bazy danych może mieć postać baza!$A$1:$G$65536.

i         Należy pamiętać by w pustych wierszach nie wpisywać danych, które nie są właściwymi danymi bazy danych. Bardzo przydatne będą tu mechanizmy sprawdzania poprawności danych.

To samo odwołanie może mieć prostą postać: baza!$A:$G. Bierzemy pod uwagę całe kolumny. Można powiedzieć, że jest ona o tyle lepsza, że w razie wydłużenia arkuszy przez producenta, np. do miliona wierszy, formuła zawsze będzie obejmować wszystkie wiersze.

q       Wprowadź do I5 tekst Przychód.

q       Wprowadź do K5 tekst ile rekordów.

q       Wprowadź do L5 formułę =BD.ILE.REKORDÓW(baza!$A:$G;I5;$A$2:$G$3)

q       Jeżeli trzeba zmień format liczbowy na ogólny.

Wynikiem jest 1186 rekordów.

q       Wprowadź dowolne dane do bazy danych w wierszu 1188.

Tym razem rekordów jest 1187. Tak naprawdę wystarczyłoby wpisać tylko liczbę w komórce C1188 i nasza formuła wskazywałaby „poprawną” wartość. Jednak nie wprowadzenie wszystkich danych do rekordu (wiersza) bazy może się zemścić później. Dlatego dla kontroli bazy danych należałoby zliczać ilość rekordów dla wszystkich pól i porównać je ze sobą. Najlepiej, jeżeli wszystkie pola są wypełnione, wtedy suma rekordów dla każdego pola będzie taka sama.

q       Utwórz nowy arkusz i nazwij go ilość rekordów.

q       Zbuduj arkusz jak na przykładzie (Rysunek 1‑2).

Rysunek 1‑2. Przykład zastosowania funkcji BD.ILE.REKORDÓW i BD.ILE.REKORDÓW.A.

Kolumna C zawiera wartości zwrócone prze funkcję BD.ILE.REKORDÓW. Pole Typ i Manager mają wartości 0, dlatego, że funkcja ta nie zlicza rekordów ze zmienną tekstową. Kolumna D zawiera wartości obliczone przez funkcję BD.ILE.REKORDÓW.A.

Zakres kryteriów jest pusty więc analizowana jest cała baza danych, jeżeli wpiszemy dowolny zestaw warunków (np. Przychód <1000) liczba rekordów będzie się zmieniać.

Ćwiczenie 4.                  Inne funkcje BD.

Wykorzystaj wszystkie dostępne funkcje BD do analizy danych.

2.        Funkcje finansowe

Arkusz kalkulacyjny ma wbudowanych wiele funkcji podzielonych na kategorie. Jedną z takich kategorii są funkcje finansowe. Kategoria ta może być poszerzona o dodatkowe funkcje, jeżeli uaktywni się dodatek Analysis ToolPack (NarzędziaàDodatki). Kolejne ćwiczenia przedstawią sposób użycia części z tych funkcji.

Ćwiczenie 5.                  Oprocentowanie kredytu.

RATE(liczba_rat;rata;wa;wp;typ;przypuszczenie)

Funkcja RATE pozwala na wyliczenie stopy procentowej pojedynczego okresu renty (kredytu, lokaty) przy zdanym kapitale początkowym (wa), ilości rocznych rat (liczba_rat), wartości raty rocznej (rata) i końcowej wartości renty (wp, 0 - jeżeli spłacony zostaje kredyt). Dodatkowymi parametrami są: typ (0 – płatność na końcu okresu, 1 – płatność na początku okresu) i przypuszczenie (przypuszczalna wartość stopy procentowej, domyślna wartość 10%).

Rata może być traktowana jako miesięczna lub kwartalna należy wtedy wartość raty rocznej (rata) podzielić prze 12 dla miesięcy lub 4 dla kwartałów.

q       Przeanalizuj cztery przykłady z arkusza rate w pliku funkcje_finansowe.xls.

q       Wyznacz oprocentowanie dla kredytu 160 000 zł, spłacanego miesięcznie przez 30 lat po 800 zł miesięcznie.

Ćwiczenie 6.                  Ile trzeba zapłacić odsetek?

IPMT(stopa;okres;liczba_rat;wa;wp;typ)

Zwraca wysokość spłaty odsetek dla danego okresu (okres) w kredycie (wa) spłacanym przez liczba_rat okresów równą ratą roczną dla oprocentowania danego parametrem stopa. Wp to wartość końcowa kredytu a typ określa, kiedy następuje spłata (0 – płatność na końcu okresu, 1 – płatność na początku okresu).

Przykład: pożyczamy 20 000 na 20 lat na 7%, spłacamy ratę na koniec roku. Odsetki do zapłacenia po pierwszym roku wynoszą 1400 zł (zobacz arkusz odsetki w funkcje_finansowe.xls).

Funkcja ta zwraca odsetki dla jednego okresu chcąc wyliczyć ich wartość we wszystkich okresach należy wykorzystać kopiowanie formuł.

q       W B11 wpisz 1.

q       W C11 wpisz formułę =IPMT($B$4;B11;$D$4;$E$4;$F$4;$G$4).

q       Zaznacz B11:C11, złap uchwyt wypełnienia i przeciągnij w dół do 33 wiersza.

Ostatnie komórki pokazują błąd, jest tak dlatego, że kredyt zosta...