Funkcje dla Ekspertów

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Funkcje dla Ekspertów.xlsx tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji

Przykłady dla wszystkich lekcji szkolenia Excel 2013: ExcelSzkolenie.pl Cwiczenia Excel 2013.zip

Ta lekcja może być obejrzana lub przeczytana poniżej.

Film wygląda najlepiej jeśli będzie odtwarzany w rozdzielczości 720p HD, rozdzielczość można zmienić dopiero po uruchomieniu filmu klikając na ikonie trybika która pojawi się w prawym dolnym rogu poniższego ekranu. Po kilku sekundach od zmiany obraz wyostrzy się.

Przykład 1. ADRES, ADR.POŚR

(Arkusz z danymi: ‘Adres’)

(Arkusz z rozwiązaniem: ‘Adres 2’)

Sposób używania pola kombi wykorzystanego w poniższym przykładzie został opisany w lekcji ‘Formularze’, funkcja WYSZUKAJ.PIONOWO została opisana w lekcji ‘Funkcja Wyszukaj’ przed przerobieniem tego przykładu sugeruję zapoznanie się z tymi lekcjami.

Chcielibyśmy utworzyć wykres, który będzie przedstawiał dane w zależności od kraju i rodzaju danych wybranych przez użytkownika naszego raportu za pomocą dwóch pól kombi.

Poniżej tabela z zielonymi nagłówkami w której mają pojawiać się wybrane dane, oraz poniżej dane źródłowe z których będziemy korzystać.

Rozpoczniemy od przygotowania pól kombi, które pozwolą dokonywać wyboru.

Zaczynamy od pola pozwalającego na wybór kraju.

To co może zostać wybrane wprowadzamy pod tabelą z danymi. Łącze komórki ustawiamy przy polu kombi.

Analogicznie tworzymy pole wyboru rodzaju danych.

W komórce B6 korzystając z dwóch funkcji WYSZUKAJ.PIONOWO wprowadzamy formułę, która będzie wyszukiwać jaki rodzaj danych wybrano.

Funkcje zostały połączone symbolami &, a pomiędzy nimi umieszczono spację w cudzysłowie.

Komórka ta zostanie użyta w późniejszym etapie jako tytuł wykresu, który będzie zmieniał się w zależności od rodzaju wybranych danych.

Pod tabelą wprowadzamy liczby od 1 do 12, dzięki którym będziemy mogli użyć tej samej formuły dla wszystkich miesięcy.

Wyszukiwanie danych rozpoczniemy od wprowadzenia funkcji ADRES, która tworzy adres komórki na podstawie danych pochodzących z wielu źródeł.

Nr_wiersza Jeżeli wybrano Sprzedaż w komórce H3 pojawi się cyfra 1, sprzedaż jest w komórce C28, więc dodajemy jeszcze 27 aby trafić do tej komórki

Nr_kolumny pierwsza kolumna w której są interesujące nas dane to C, w tym miejscu wykorzystamy cyfry znajdujące się pod tabelą pod pierwszą z komórek tabeli jest cyfra 1 i odniesienie do niej wprowadzimy w tej formule ale musimy do niej dodać 2 aby była to kolumna ‘C’ (która jest 3 w arkuszu 2+1=3)

Typ_adresu wprowadzamy 1

A1 wprowadzamy 1

Tekst_arkusz wprowadzamy w cudzysłowie nazwę arkusza w którym znajdują się dane do których się odwołujemy.

Tak zapisana formuła będzie pokazywać adresy komórek z obszaru (C28:C30) w zależności od tego jaki rodzaj danych wybrano za pomocą pola kombi.

Formuła ta nie bierze jeszcze pod uwagę kraju który został wybrany i zawsze pokazuje adresy danych dla Polski.

Aby formuła ta pokazywała odpowiednie dane w zależności także od kraju należy w ‘Nr_wirsza’ wprowadzić także odwołanie do komórki E2. Ponieważ dane dla każdego kraju zajmują 3 linie, cyfra znajdująca się w E2 jest przemnażana przez 3.

Ponieważ już dla Polski, która jest pierwsza ta nowa dana przesuwa adres o 3 w dół, liczba 27 została zmniejszona do 24.

Pozostałe argumenty funkcji nie zmieniają się.

Po przeciągnięciu funkcji na całą tabelę uzyskujemy adresy danych właściwych dla kraju i rodzaju danych jakie zostaną wybrane za pomocą pół kombi.

Dla upewnienia się czy funkcja została dobrze wprowadzona sugeruję sprawdzić kilka kombinacji wyborów przed przejściem dalej.

Na funkcję ADRES nakładamy funkcje ADR.POŚR aby w miejsce adresu komórki pojawiła się dana która się w niej znajduje. Najwygodniej będzie dopisać ją ręcznie.

=ADR.POŚR(ADRES($E$2*3+$H$2+24;C7+2;1;1;"Adres"))

Zmienioną formułę kopiujemy do pozostałych pól.

Pozostało już tylko dodanie wykresu (np. liniowego), który korzysta z danych zgromadzonych w tabeli.

Łącza pól kombi, cyfry znajdujące się pod tabelą oraz dane źródłowe ukrywamy na przykład formatując ich czcionkę na kolor biały.

Nasz wykres jest już gotowy.

UWAGA:

Nazwy arkuszy zapisane w funkcjach ADRES i ADR.POŚR, nie zmieniają się automatycznie wraz ze zmianą nazwy arkusza. Po każdej zmianie nazwy arkusza musimy ręcznie zmienić odwołania we wszystkich funkcjach, w przeciwnym razie wyświetlone zostaną komunikaty o błędzie: #ADR!.

Przykład 2.

Funkcje Bazy Danych

(Arkusz: ‘Funkcje BD’)

Programem który służy do pracy na rozległych bazach danych jest Access.

Excel posiada funkcje Bazy Danych ale nie czyni to z niego narzędzia do obsługi relacyjnych baz danych.

Funkcje bazy danych znajdziemy w kategoriach okna ‘Wstawianie funkcji’.

Poniżej prezentuję funkcję BD.SUMA, która sumuje sprzedaż z bazy danych dla produktów wymienionych na liście ‘Warunki’ (komórki E6:E9).

Podając zakres zarówno bazy danych, jak i warunków, podajemy je wraz z nagłówkami kolumn.

Analogicznie działają funkcje BD.ŚREDNIA, BD. MAX, BD.MIN i BD.ILE.REKORDÓW. Poniżej prezentuję ostatnią z nich.

Warunki można także zapisywać używając znaków <, >, <=, =>, <>.

Poniżej prezentuję działanie funkcji BD.SUMA dla 2 warunków: sprzedaż większa od 100 i jednocześnie mniejsza od 350.

Przykład 3.

SUMA. WARUNKÓW

(Arkusz: ‘SUMA. WARUNKÓW’)

Funkcja SUMA.WARUNKÓW w niektórych przypadkach może być alternatywą dla powyżej opisanych funkcji bazy danych.

Funkcja sumuje komórki ze wskazanego zakresu o ile wiersze w których się one znajdują spełnią wszystkie wymienione w niej warunki.

Główną zaletą funkcji jest właśnie to że w łatwy sposób można zapisać w niej wiele różnych warunków dla wielu kolumn.

Funkcja może też być alternatywą dla popularnej SUMA.JEŻELI, nie wymaga łączenia wielu warunków w jeden.

W poniższym przykładzie chcemy zsumować wyłącznie zwroty zarejestrowane w oddziale w Warszawie.

Suma_zakres: Sumujemy komórki od C3 do C20 gdzie są sprzedaże i zwroty.

Kryteria_zakres1: Pierwsze kryterium dotyczy zwrotów, czyli zakres to te same komórki C3:C20

Kryteria1: Zwrot występuje gdy wartość jest mniejsza od zera „<0”

Kryteria_zakres2: Drugie kryterium dotyczy Oddziałów, które są w komórkach D3:D20

Kryteria2: Ma to być „Warszawa”.

Jeśli przesuniemy suwak w dół zobaczymy że kolejne zakresy i kryteria są puste.

Poniżej zrobimy dokładnie to samo ale jedną formułą od razu dla wszystkich oddziałów.

Jedyną różnicą jest wprowadzenie adresowania absolutnego oraz wprowadzenie adresu do komórki $F8 zamiast wpisywać „Warszawa” w funkcji.

Tak przygotowaną formułę kopiujemy w dół i w prawo.

Aby ta sama formuła sumowała wyłącznie sprzedaże zamiast zwrotów wystarczy zamienić znak mniejsze „<” na większe „>”.

W kolejnej formule dodamy jeszcze jeden warunek.

Kryteria3 dotyczą daty transakcji i ma to być 1 lipca lub data późniejsza.

Pozostała część funkcji nie zmienia się.

Podobnie dla sumy sprzedaży zmieniamy tylko znak < na >.

Po skopiowaniu formuł uzyskujemy wszystkie rozwiązania w tym przykładzie.

Przykład 4.

MIN.K MAX.K

(Arkusz: ‘MIN.K MAX.K’)

Funkcje MIN.K i MAX.K są dobrą alternatywą dla funkcji pozycja, która podaje miejsce danego elementu we wskazanym zbiorze.

MIN.K znajduje K-ty najmniejszy element we wskazanym zbiorze.

A MAX.K znajduje K-ty największy.

W tym przypadku znaleźliśmy 3 najlepsze i 3 najgorsze wyniki.

Jeśli funkcję połączymy z WYSZUKAJ.PIONOWO łatwo znajdziemy który pracownik osiągnął taki wynik.

Funkcja WYSZUKAJ.PIONOWO może znajdować i podawać tylko elementy w kolumnie lub na prawo od kolumny gdzie znalazła poszukiwaną wartość. Dlatego w komórkę D3 wpisujemy =B3, zmieniamy czcionkę na białą i kopiujemy tą formułę poniżej.

Alternatywą jest użycie funkcji WYSZUKAJ ale wymaga ona posortowania listy rosnąco.

Analogicznie wyszukujemy 3 najlepszych pracowników.

Jeżeli w funkcji MIN.K użyjemy zamiast jednego argumentu K, kilku wprowadzonych jako tablica, np. trzech najgorszych {1;2;3} i zsumujemy wyniki uzyskamy sumę K najgorszych elementów.

Postępując analogicznie dla MAX.K uzyskamy sumę K najlepszych wyników.

Więcej na temat Funkcji Tablicowych przeczytasz w lekcji ‘Funkcje Tablicowe’.

Przykład 5.

RZYMSKIE ARABSKIE

(Arkusz: ‘RZYMSKIE ARABSKIE’)

Funkcja RZYMSKIE przekształca liczby arabskie np. 1976 na rzymskie czyli MCMLXXVI.

Funkcja działa dla liczb od zera do 3999.

Jeśli wpiszemy liczbę nie całkowitą zostanie ona zaokrąglona w dół.

W liczbach rzymskich zero to puste miejsce i tak też zostanie przekształcone zero.

Funkcja =ARABSKIE działa dokładnie odwrotnie, przekształcając liczby rzymskie na arabskie.

Nie można w Excelu dokonywać operacji na liczbach rzymskich.

Przykład 6.

Funkcje Finansowe PV FV

(Arkusz: ‘Funkcje Finansowe PV FV)

Nazwy funkcji PV i FV są skrótami ich angielskich określeń:

PV Present Value – wartość obecna

FV Future Value – wartość przyszła

Poniżej mamy otrzymać 5 razy po 5000zł na koniec 5 kolejnych lat. Obecna stopa procentowa to 5%.

Argument Stopa podajemy zawsze w stosunku do ilości rat. Jeśli wypłata jest co roku i stopa procentowa 5% na rok, wpisujemy te właśnie 5%. Gdyby wypłaty były co miesiąc, należałoby wprowadzić jako stopę procentową 5%/12.

Wp to dodatkowa wpłata na koniec okresu, która nie występuje w tym przypadku.

Typ to rodzaj płatności domyślnie płatności są na koniec okresu i wtedy możemy go pominąć lub wprowadzić tam zero.

Pięć wypłat po 5000zł to 25000zł ale wartość obecna tych pieniędzy jest niższa i zgodnie z wyliczeniami tej funkcji wynosi 21 647zł.

Wartość podawana jest przez funkcję z minusem ponieważ założeniem tych funkcji jest to że wartości się równoważą do zera.

Gdybyśmy zamiast otrzymywać 5000zł raz w roku mieli je wpłacać, wynik byłby taką samą liczbą ale ze znakiem dodatnim.

W pewnym banku zaproponowano nam następującą transakcje, jeśli wpłacimy nasze oszczędności w kwocie 30 tyś zł to bank będzie nam wypłacać przez 4 lata co miesiąc po 500zł a dodatkowo po 4 latach otrzymamy jeszcze 8 tys. zł.

4 lata co miesiąc oznacza że otrzymamy 48 wypłat

Gdyby nie brać pod uwagę zmiany wartości pieniądza w czasie obliczylibyśmy że 48*500 = 24 000 dodajemy jeszcze 8 tyś i wychodzi 32 tyś czyli znacznie więcej niż wpłacamy.

Oszacujmy tą transakcje używając funkcji PV która biorze pod uwagę zmieniającą się wartość pieniądza w czasie.

Ponieważ wypłaty są co miesiąc wprowadzamy stopę procentową 5%/12

W pole Wp wpisujemy 8000zł czyli kwotę wypłacaną na koniec ostatniego okresu.

Wg. powyższego wyniku bylibyśmy stratni na tej transakcji.

W ostatnim z przykładów zastosowania funkcji PV, za 10 lat otrzymamy kwotę 100 000 zł.

W tym przypadku argument ‘Liczba rat’ w rzeczywistości mówi o długości okresu, ponieważ rata wynosi zero i jest tylko wypłata na koniec okresu.

Taka wypłata za 10 lat jest na dziś warta 61 391 zł.

FV zwraca przyszłą wartość inwestycji.

W poniższym przykładzie przyszła wartość 120 rat wypłacanych co miesiąc przy oprocentowaniu 5% rocznie 5%/12 miesięcznie, będzie wynosić 31 056 zł.

Podczas gdy zwykłe mnożenie 120 * 200 daje nam 24 000 zł.