Kurs Excela cz.2.doc

22. Funkcje

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Cwiczenia.xls, zachęcam do ich samodzielnego wykonania.

 Funkcje w Excelu uruchamiamy klikając na ikonę .

 Istnieje kilka sposobów, aby znaleźć interesującą nas funkcje, w oknie ‘Wyszukaj funkcję’ możemy wpisać nazwę funkcji (jeśli ją znamy) lub jednym słowem, to co funkcja powinna robić.

 Możemy też wybrać kategorię, w której może znajdować się interesująca nas funkcja, np. Finansowe lub Matematyczne.

 Klikając na nazwę funkcji w oknie ‘Wybierz funkcję:’ pod listą nazw widzimy opis funkcji.

W większości przypadków opis ten bez problemów pozwala zrozumieć, do czego służy dana funkcja.

 Po wybraniu funkcji, (w poniższym przykładzie SUMA.ILOCZYNÓW), wyświetlane jest okno ‘Argumenty funkcji’, klikając na kolejnych oknach w które wstawiamy dane, opis tego jakie argumenty można wprowadzić i co funkcja z nimi zrobi.

 Opisy najczęściej używanych funkcji

Poniżej przedstawiam opisy często używanych funkcji z przykładami użycia zaczerpniętymi z doświadczeń w korporacjach. Lista opisanych funkcji oczywiście nie wyczerpuje wszystkich możliwości Excela, ale tak jak wspominałem wcześniej, dla większości funkcji sposób ich działania można zrozumieć z opisów zawartych w Excelu.

 JEŻELI

W poniższym przykładzie w kolumnie E chcielibyśmy uzyskać wzrost sprzedaży dla długiej listy produktów. Ponieważ część produktów nie była sprzedawana w 2006 roku zwykła formuła dzielenia dałaby w ich przypadku wynik ‘#DZIEL/0!’, który nie wygląda zbyt profesjonalnie, dlatego wolelibyśmy mieć w takich przypadkach wprowadzony znak „-----„.

W poniższej funkcji wprowadzono test logiczny: jeżeli wartość komórki ‘C5’ jest większa od zera, wtedy oblicz wzrost procentowy pomiędzy komórkami D5 i C5 (formuła D5/C5-1), w przeciwnym razie wpisz: „-----„.

Formułę taką można skopiować dla wszystkich produktów w tabeli.

Poniżej przykład użycia funkcji ‘Jeżeli’ i funkcji tekstowej ‘Prawy’.

Jeżeli ostatnia litera imienia (pierwsza od Prawej litera pola Imię) jest równa a wtedy z bardzo dużym prawdopodobieństwem (w Polsce) możemy przypuszczać, że właścicielką imienia jest kobieta.

Często istnieje konieczność zapętlenia kilku funkcji ‘jeżeli’ jedna w drugiej, niestety w Excelu ilość takich zapętleń jest ograniczona do 7.

 LUB

W poniższym przykładzie w kolumnie F przy użyciu funkcji LUB sprawdzamy czy w danym miesiącu sprzedaż w którymkolwiek z oddziałów przekroczyła wartość 100. Funkcja zwraca wartości logiczne: ‘PRAWDA’/’FAŁSZ’, ‘PRAWDĘ’ jeśli choć jeden z warunków jest spełniony, gdy żaden z nich nie jest spełniony ‘FAŁSZ’.

Funkcja lub bardzo często używana jest jako funkcja wewnętrzna innych funkcji, np. JEŻELI, kiedy musi być spełniony chociaż jeden z warunków.

 ORAZ

Funkcja zwraca wartości logiczne ‘PRAWDA’/‘FAŁSZ’, wartość ‘PRAWDA’, jeżeli wszystkie warunki zostały spełnione.

W poniższym przykładzie w kolumnie F uzyskaliśmy wartość prawda dla miesięcy, w których sprzedaż we wszystkich oddziałach byłą poniżej 50.

 SUMA

Prawdopodobnie najczęściej używana funkcja Excela, oraz najprostszą w użyciu.

Można jej używać na kilka sposobów, najbezpieczniejszym będzie zaznaczenie całego obszaru, który chcemy zsumować wraz z komórką poniżej lub po prawej, gdzie ma znaleźć się suma i kliknięcie symbolu sumy na pasku ikon.

Drugim sposobem jest ustawienie jako aktywnej komórki, tej w której ma znaleźć się suma i pozwolenie Excelowi domyślić się jaki obszar ma zsumować, nie zadziałałoby to w poniższym przykładzie ponieważ brak jest wartości dla produktu 3, a nawet gdyby wartość tam była Excel zasugerowałby zsumowanie także nagłówka w którym znajduje się rok.

 Znacznie więcej możliwości daje funkcja SUMY.POŚREDNIE.

 ŚREDNIA

Funkcja oblicza średnią dla wprowadzonych liczb, bądź zakresów. jej dużą zaletą jest to że można do niej wprowadzać adresy pojedynczych komórek i jednocześnie całe zakresy.

Średnia dostępna jest też w pasku stanu. Więcej informacji na ten temat w lekcji: Pasek Stanu.

 Do obliczania średniej ważonej najprościej wykorzystywać funkcję SUMA.ILOCZYNÓW, a do obliczania średniej geometrycznej funkcję ŚREDNIA.GEOMETRYCZNA

 ZAOKR

W Excelu jest wiele funkcji służących do zaokrąglania, funkcja ZAOKR() jest najczęściej używaną z nich.

Ma 2 argumenty:

- liczbę, którą będziemy zaokrąglać (lub adres komórki w której ta liczba się znajduje)

- ilość cyfr która być pokazana po przecinku

W poniższym przykładzie liczba 0,49 została zaokrąglona do 1 miejsca po przecinku czyli do liczby 0,5.

 SUMA.ILOCZYNÓW

Funkcja Suma.Iloczynów bywa często przydatna do obliczania średniej ważonej. W poniższym przykładzie moglibyśmy mozolnie wprowadzić formułę przemnażającą sprzedaż z każdego kraju przez marżę w kraju tym uzyskaną i podzielić to przez sumę sprzedaży, dzięki sumie iloczynów formuła ta zostanie jednak wprowadzona szybciej, ograniczymy także ryzyko błędu. Funkcja ta ma w tym przypadku także taką przewagę, że jeśli zostanie dodana dodatkowa kolumna z wartościami pomiędzy już istniejącymi, zostanie ona automatycznie dodana do sumy iloczynów.

 Po wybraniu funkcji Suma.Iloczynów i wprowadzeniu adresów tak jak w poniższym przykładzie należy już tylko dopisać dzielenie przez sumę sprzedaży znajdującą się w komórce G7.

Uzyskamy wtedy formułę: =SUMA.ILOCZYNÓW(C6:F6;C7:F7)/G7

 MAX, MIN

Funkcje wybierają najmniejszą (MIN) lub największą liczbę (MAX) z podanego zakresu bądź zakresów.

Tekst i puste komórki nie są brane pod uwagę.

W poniższym przykładzie komórka B8 jest pusta, czyli jej wartość to 0, jednak jako najmniejsza wartość wybrana została komórka B12 zawierająca liczbę ¾.

 MODUŁ.LICZBY

Moduł liczby to funkcja używana w matematyce, nazywana jest także wartością bezwzględną. Funkcja ta ma jeden argument, który może być tylko liczbą (lub adresem, lub inną funkcją dającą w wyniku liczbę). Funkcja ta dla liczb dodatnich nie zmienia ich wartości a dla liczb ujemnych zmienia znak na dodatni.

 Przykład: MODUŁ.LICZBY(-5)=5

 Jest to jedna z wielu funkcji w Excelu, które można łatwo zastąpić używając prostych symboli matematycznych. W tym przypadku potęgowania. Najpierw należy podnieść liczbę do potęgi 2 (lub innej parzystej) a potem wynik pierwiastkować pierwiastkiem 2 stopnia (lub innego takiego samego jak potęga).

(-5)^2^(1/2)=5

 TRANSPONUJ

Funkcja TRANSPONUJ jest prostym przykładem Funkcji Tablicowych, których Excel oferuje dość dużo. Ponieważ są one dość rzadko używane w biznesie a są pomocne przede wszystkim w przypadku zaawansowanych obliczeń matematycznych i w statystyce, pozostałe funkcje tablicowe nie będą omawiane w ramach tego kursu.

 Zaznaczamy obszar D3:F3, w którym ma znaleźć się tablica wynikowa, wprowadzanie funkcji tablicowej zawsze zaczynamy od naciśnięcia klawisza F2.

 Wprowadzamy funkcję tablicową =TRANSPONUJ(B3:B5) po wprowadzeniu funkcji tablicowej zawsze zakańczamy wprowadzanie wciśnięciem Ctrl+Shift+Enter.

 W efekcie powyższych działań w komórkach D4:F4 mamy funkcję tablicową zapisaną w nawiasach klamrowych {}.

Pomimo tego że funkcja ‘wygląda jakby była wprowadzona w 3 komórkach jest to jedna funkcja i wprowadzenie zmian w którejkolwiek komórce powoduje zmiany we wszystkich komórkach.

Zmiany wprowadzamy także rozpoczynając od wciśnięcia F2, a po ich wprowadzeniu ‘wychodzimy’ z funkcji tablicowej wciskając Ctrl+Shift+Enter.

W tym przypadku zamiast funkcji tablicowej można skopiować zakres ‘B3:B5’, z menu: ‘Wklej specjalne’ wybrać ‘Wklej linki’, dodać znaki $ do linków, po czym skopiować linki i ponownie z menu ‘Wklej specjalne’ wybrać Transpozycja. To rozwiązanie ma tą przewagę, że w przypadku tablic zmiana części tablicy nie jest możliwa (np. w powyższym przykładzie dodanie kolumny pomiędzy ‘D’ i ‘E’.

 SUMA.JEŻELI

ZŁĄCZ.TEKSTY

Poniżej zamieszczam dwa przykłady użycia funkcji Suma.Jeżeli.

W pierwszym przedstawionym na poniższym rysunku w komórkach L3:L5 sumujemy wydatki w podziale na kategorie.

Argumenty funkcji Suma.Jeżeli to:

Zakres – czyli co ma być porównywane z kryterium sumowania, inaczej mówiąc gdzie funkcja ma szukać.

Kryteria – czyli czego funkcja ma szukać

Suma_zakres – dane która funkcja będzie sumować jeżeli w tej samej linijce Zakresu wartość komórki jest zgodna z wartością Kryteria.

 W pola Zakres i Suma_zakres często wygodnie jest wprowadzać całe kolumny gdyż łatwiej wtedy będzie kopiować tą formułę do komórek niżej. Należy tego unikać jeżeli plik jest bardzo duży i wolno się przelicza, ponieważ może go to dodatkowo spowolnić.

 W drugim przykładzie przygotowano tabelę sumującą wydatki z podziałem na kategorie i miesiące jednocześnie.

Aby sumowanie przy użyciu funkcji Suma.Jeżeli mogło działać dla 2 lub większej ilości kryteriów należy je połączyć, zostało to zrobione w kolumnie ‘B’. Na poniższym rysunku jest to widoczne, aby łatwiej było zrozumieć działanie funkcji, w rzeczywistym raporcie kolumnę taką można by ukryć, lub zmienić kolor czcionki na biały (lub inny kolor tła).

 Funkcja szuka komórek spełniających kryteria (odpowiednia kategoria i miesiąc) w kolumnie ‘$B:$B’ (wprowadzone w polu Zakres).

Kryteria to

 Proszę o dokładne zwrócenie uwagi na znaki $, dzięki którym adresy we wszystkich polach zostały zamienione na adresy bezwzględne i ‘częściowo’ bezwzględne, co umożliwiło przegranie raz wprowadzonej funkcji do wszystkich pól tabeli wynikowej bez konieczności ręcznego wprowadzania zmian.

Rzeczywiste tabele opisujące procesy biznesowe są z reguły znacznie większe i przygotowywanie formuł tylko raz jest niezbędne.

 SUMA.JEŻELI wykorzystanie jako funkcji tablicowej

Tylko dla ambitnych.

 Funkcje SUMA oraz funkcja JEŻELI mogą być wykorzystana do utworzenia naszego własnego odpowiednika funkcji SUMA.JEŻELI która będzie sumowała dane z wielu kolumn.

 W poniższym przykładzie sumujemy wiele wyników funkcji: JEŻELI, które przyjmują wartości 0 jeśli Kategoria jest inna niż ‘Kategoria 3’ lub wartość wydatków jeśli jest jej równa.

 {=SUMA(JEŻELI($D$4:$D$14=$K$23;$F$4:$G$14;0))}

 Wprowadzanie funkcji tablicowej zawsze rozpoczynamy od naciśnięcia przycisku ‘F2’ a kończymy naciskając Ctrl+Shift+Enter, nie wprowadzamy z klawiatury symboli nawiasów klamrowych, Excel je sam dopisze po wciśnięciu Ctrl+Shift+Enter.

 CZY.BŁĄD

Funkcja ta jest z reguły używana jako funkcja ‘wewnętrzna’ innych funkcji tak, aby gdy jeden z argumentów funkcji głównej będzie miał niewłaściwy format lub okaże się błędem, funkcja ta pomijała go zamiast wskazywać błąd.

Najprostszym przykładem będzie użycie ten funkcji w dodawaniu komórek.

 W powyższym przykładzie innym rozwiązaniem tego problemu może być użycie funkcji =SUMY.POŚREDNIE(9;D5:D11)

 SZUKAJ.TEKST

FRAGMENT.TEKSTU

 W poniższym przykładzie przedstawiam fragment Price Survey (lista z cenami konkurencji), jaki pewna firma otrzymała od agencji badawczej. Niestety pojemności produktów były częścią nazwy, co znacznie utrudniło porównywanie cen produktów. Ponieważ produktów objętych badaniem było kilka tysięcy zdecydowano się użyć funkcji tekstowych, aby wyodrębnić pojemność.

Użyto dwóch funkcji tekstowych najpierw użyto funkcję SZUKAJ.TEKST() - wyszukiwany jest tekst ml, który następuje po pojemności danego produktu, wynikiem działania na funkcję SZUKAJ.TEKST(), nałożona jest funkcja FRAGMENT.TEKSTU(), która wycina 3 znaki z nazwy produktu znajdujące się o 3 w lewo od miejsca w którym został znaleziony tekst ‘ml’.

 Wynikiem działania powyżej opisanych funkcji jest nie liczba lecz tekst, który w przypadku produktów o pojemności poniżej 100ml zaczyna się od spacji.

Aby móc dokonywać operacji matematycznych na pojemności, należy całą kolumnę C zamienić na wartości (skopiować i wkleić w to samo miejsce wartości), po czym korzystając z operacji zamień (Ctrl+H) zamienić znak spacja na nic (niczego nie wpisując w okienko „Zamień na:”).

 LOS()

Funkcja losuje wartość z przedziału od 0 do 1. Ponieważ losowanie odbywa się od nowa przy każdym przeliczeniu arkusza, najlepiej jest od razu zamienić ją na wartości, w przeciwnym razie losowanie nowych wartości przy każdej zmianie danych w pliku, może znacznie spowolnić działanie komputera. Często wynik tej funkcji przemnaża się je lub dzieli aby uzyskać rząd wielkości liczb który nas interesuje np. =los()*1000 lub =0,5+los()/10

 DZIŚ()

Funkcja zwraca dzisiejszą dane, jest używana głównie w różnego rodzaju formularzach, oraz w logistyce np. do obliczania ile dni zostało od daty dostawy/transportu do dziś.

 ILE.NIEPUSTYCH

Poniżej prezentuje zaczerpnięty z praktyki biznesowej przykład użycia funkcji =ILE.NIEPUSTYCH. W wierszu ‘15’ znajdują się formuły wykorzystujące tą funkcję i zliczające ile było klientów którym przyznano rabat w każdym z miesiąców (wiersz 15), a w kolumnie ‘J’ w ilu miesiącach przyznano rabat danemu klientowi.

 SUMY.POŚREDNIE

W poniższym przykładzie zamieszczonym w pliku Cwiczenia.xls prezentuje porównanie wykorzystania funkcji SUMY.POŚREDNIE i funkcji SUMA oraz ich połączenia.

W kolumnie C używam funkcji SUMA dla regionów, a także dla sumy końcowej opisanej jako TOTAL. W tym przypadku obliczany jest błędny wynik ponieważ oprócz sprzedaży dla klientów sumowane są także pośrednie sumy, właściwym rozwiązaniem dla tego typu problemu byłoby zsumowanie komórek sum pośrednich (odpowiednia formuła znajduje się w komórce C27).

W kolumnie D prezentuję użycie funkcji SUMY.POŚREDNIE, zarówno w sumach dla regionów, jak i Total. Wykorzystanie tej funkcji da nam pewność że sumy dla regionów (sumy wewnętrzne) będą pomijane. Wynik jest poprawny.

W kolumnie E używam funkcji SUMY.POŚREDNIE w wariancie, który pomija ukryte wiersze. Ponieważ wiersz 4 jest ukryty, wynik nie będzie poprawny, chyba że zależało nam właśnie na pominięciu wartości w ukrytych komórkach.

W ostatnim przykładzie pokazuję, że funkcja SUMY.POŚREDNIE pomija jedynie inne SUMY.POŚREDNIE, sumuje natomiast zwykłą funkcję SUMA. Wynik jest błędny.

 =SUMY.POŚREDNIE(9;D3:D24)

W składni funkcji pierwsza cyfra jest oznaczeniem funkcji jaka ma być wykorzystana, dla cyfry ‘9’ lub ‘109’ będzie to suma, ale są także inne możliwości, przedstawiam je w tabeli poniżej.

Drugim argumentem jest obszar który ma być sumowany.

NPV, IRR

Funkcja NPV służy do obliczania Wartości Bieżącej Netto inwestycji czyli mówiąc wprost ile da się na tym zarobić.

W poniższym przykładzie na przestrzeni lat 2010-2012 dokonujemy inwestycji, która będzie nam przynosić przychody od 2012 do 2030.

Stopa dyskontowa w całym okresie wynosi 5%. Zakładamy że wszystkie wydatki i przypływy zachodzą na koniec okresu.

Gdybyśmy chcieli wyliczyć wartość na dzień dzisiejszy (zakładamy 1 stycznia 2010) wydatków moglibyśmy skorzystać z poniższego wzoru:

=F7/(1+C5)+G7/(1+C5)^2+H7/(1+C5)^3

Każdy z wydatków dzielimy przez 1+stopa dyskontowa aby wyliczyć jego wartość na dziś (wartość pieniądza w czasie jest zmienna). Wydatek z 2011 roku dzielimy dwukrotnie stąd potęga ^2, a tą z 2012 trzykrotnie (potęga ^3).

Powyżej opisany wzór został użyty w komórce C7.

 W komórce C8 wykorzystano funkcję NPV, dzięki której uzyskano dokładnie ten sam wynik.

 W przypadku obliczania bieżącej wartości wydatków możliwe było wprowadzenie wzoru na NPV ponieważ wydatki są ponoszone tylko w 3 okresach i wzór nie był zbyt długi. Gdybyśmy chcieli tak samo wyliczyć bieżącą wartość przypływów musielibyśmy wykazać się dużą cierpliwością a mimo to wprowadzenie tak długiego wzoru mogłoby okazać się niemożliwe ze względu na maksymalną liczbę znaków w formule, która w Excelu 2003 wynosi 1000 znaków.

W komórce C10 użyto funkcji NPV do obliczenia bieżącej wartości przypływów. Warto podkreślić, że w komórkach dla których przypływy nie występują (F8 i G8) muszą być wpisane zera, w przeciwnym razie funkcja pominie te komórki i wynik będzie niepoprawny.

 Aby obliczyć bieżącą wartość przewidywanego zysk na tym projekcie wystarczy odjąć od bieżącej wartości przypływów (C10) bieżącą wartość wydatków (C8). Taka formuła zapisana jest w komórce C12.

Zysk możemy także wyliczyć bezpośrednio korzystając z danych znajdujących się w tabeli w wierszu 9, gdzie od przypływów odjęto wydatki.

Funkcja ta została zapisana w komórce C13.

 Funkcja IRR oblicza wewnętrzną stopę zwrotu, która może być rozumiana jako graniczna wielkość oprocentowania przy której zysk na danym projekcie wynosi 0.

Jeżeli stopa dyskontowa jest niższa niż IRR zysk jest dodatni, jeżeli wyższa projekt przyniesie stratę. Można ją także rozumieć jako najwyższe oprocentowanie kredytu jakie możemy zaakceptować aby projekt nie przyniósł strat.

Funkcja IRR dla tego przykładu wykorzystana jest w komórce C18.

Jeżeli jej wynik (8,886%) wpiszemy w komórce C5, Zysk dla tego projektu będzie wynosił zero.

 ADRES, ADR.POŚR

 Sposób używania pola kombi wykorzystanego w poniższym przykładzie został opisany w lekcji Profesjonalne raporty podstawy, funkcja WYSZUKAJ.PIONOWO została opisana w lekcji Funkcja wyszukaj przed przerobieniem tego ćwiczenia 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 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 funkcji Wyszukaj.Pionowo wprowadzamy formułę która będzie wyszukiwać jaki rodzaj danych wybrano.

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, które mogą pochodzić 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   ...