Funkcje Podstawy
Przykłady opisane w tej lekcji dostępne są w
arkuszu Excela: Funkcje
Podstawy.xlsx tylko ich samodzielne przerobienie daje gwarancję
zapamiętania tej lekcji
Przykłady dla wszystkich lekcji szkolenia
Excel 2007: ExcelSzkolenie.pl
Cwiczenia Excel 2007.zip
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 wielu przypadkach
opis ten pozwala zrozumieć, do czego służy dana funkcja.
Po wybraniu funkcji,
(w poniższym przykładzie SUMA), wyświetlane jest okno ‘Argumenty funkcji’, po
wprowadzeniu adresów komórek do pierwszego z okienek, poniżej zostanie
wyświetlony aktualny wynik funkcji (o ile jest możliwe jego obliczenie), pod
nim znany już nam opis funkcji, a jeszcze poniżej opis argumentu, który można
wprowadzić do pola ‘Liczba1’ i tego 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. Funkcje
bardziej skomplikowane zostały omówione w lekcjach: ‘Funkcje dla
Zaawansowanych’ i ‘Funkcje Tablicowe’.
SUMA
(Arkusz: ‘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 lub wciśnięcie kombinacji klawiczy
Alt+=.
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ć, zawsze należy sprawdzić czy
program zaproponował właściwy zakres i wcisnąć enter.
UWAGA:
Nawet w przypadku tak
prostej funkcji bardzo wielu użytkownikom Excela zdarzają się błędy,
najpopularniejsze z nich polegają na ustawieniu aktywnej komórki na końcu
sumowanych liczb i wciśnięciu ikony ‘Autosumowania’, po zasugerowaniu przez
Excela zakresu, użytkownicy, bez spojrzenia co zostanie zsumowane, wciskają
enter i zakres sumy jest niewłaściwy.
Dzieje się tak
najczęściej w 2 przypadkach:
- kiedy brak jest
jednej z danych Excel zatrzyma się przy pierwszej pustej komórce.
- gdy w nagłówku
kolumny znajduje się liczba np. rok także on zostanie zsumowany.
Znacznie więcej
możliwości daje funkcja SUMY.CZĘŚCIOWE,
opisane w dalszej części tej lekcji.
ŚREDNIA
(Arkusz: ‘ŚREDNIA’)
Funkcja oblicza
średnią dla wprowadzonych liczb, bądź zakresów.
UWAGA:
Funkcja ta podobnie
jak wszystkie inne funkcje Excela inaczej traktuje pustą komórkę lub tekst a inaczej
liczbę zero, co dobrze ilustruje poniższy przykład.
W pierwszej z tabel
średnia liczona jest tylko dla 6 elementów tabeli. Gdybyśmy chcieli obliczyć
ile wynosi średnia sprzedaż na produkt dla poniższych 7 produktów, w pustą
komórkę należy wprowadzić zero. Tekst tak samo jak puste komórki jest pomijany
w kalkulacjach.
Zaletą tej funkcji
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
Funkcja JEŻELI
(Arkusz: ‘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 2009 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 „-----„.
Proponuje wykasować
tą formułę i ustawić jako aktywną komórkę E4, po czym wybrać ikonę funkcji.
W okienku ‘Wyszukaj
funkcję’ wpisujemy ‘jeżeli’ i
zatwierdzamy enterem.
Po wybraniu funkcji
JEŻELI i kliknięciu OK na powyżej przedstawionym oknie ‘Wstawianie funkcji’,
zostanie wyświetlone poniżej pokazane okno ‘Argumenty funkcji’.
W okienku
‘Test_logiczny’ wprowadzamy C4>0, czyli jeżeli sprzedaż w 2009 była większa
od zera to…
W okienku
‘Wartość_jeżeli_prawda’ wprowadzamy formułę na wzrost procentowy, który można
wyliczyć gdy sprzedaż w 2009 była większa od zera.
W okienku
‘Wartość_jeżeli_fałsz’ wprowadzamy -----, symbol ten ma być wyświetlany gdy nie
jest możliwe obliczenie procentowego wzrostu.
Formułę zatwierdzamy
kliknięciem w przycisk OK, po czym kopiujemy ją z komórki E4 do poniższych
komórek tabeli.
W efekcie powinniśmy
uzyskać poniższy wygląd tabeli.
ZAOKR
(Arkusz: ‘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 ma
być pokazana po przecinku (lub adres komórki w której ilość ta jest wpisana)
W poniższym
przykładzie w komórce C5 liczba 1,23456789 została zaokrąglona do 2 miejsca po
przecinku czyli do liczby 1,23.
Cyfry od 1 do 4 są
zaokrąglane w dół, od 5 do 9 w górę, co łatwo zauważyć przeglądając wyniki
powyższego przykładu.
MAX, MIN
(Arkusz: ‘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 C9 jest pusta, czyli jej wartość to 0, jednak jako
najmniejsza wartość wybrana została komórka C13.
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ęcy, a w kolumnie ‘J’ w
ilu miesiącach przyznano rabat danemu klientowi.
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ś.
Funkcja ta, jak mało która, nie posiada
argumentów.
Jej poprawny wynik zależy od prawidłowo
ustawionej daty (i godziny) w systemie Windows.
Funkcje Logiczne:
LUB, ORAZ
(Arkusz: ‘ORAZ LUB’)
Funkcja logiczne
rzadko są używane samodzielnie, znacznie częściej w połączeniu z innymi
funkcjami, o łączeniu funkcji możesz przeczytać w dalszej części tej lekcji.
ORAZ
Funkcja zwraca
wartości logiczne ‘PRAWDA’/‘FAŁSZ’, wartość ‘PRAWDA’, jeżeli wszystkie warunki zostały spełnione,
w przeciwnym razie ‘FAŁSZ’.
W poniższym
przykładzie w kolumnie F uzyskaliśmy wartość prawda dla miesięcy, w których
sprzedaż we wszystkich oddziałach była poniżej 50.
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’.
ZAGNIEŻDŻANIE FUNKCJI
(Arkusz: ‘Zagnieżdżanie funkcji’)
Dość często występuje
potrzeba użycia kilku funkcji na raz, co jest nazywane także zagnieżdżaniem
funkcji.
W poniższym
przykładzie chcielibyśmy na podstawie dużej ilości imion ustalić płeć osób
noszących te imiona. Zdecydowana większość imion kobiet używanych w Polsce
kończy się na literę „a”, litera ta nie występuje natomiast na końcu imion
męskich.
Poniżej rozwiązanie
tego problemu przy użyciu funkcji ‘JEŻELI’ i funkcji tekstowej ‘PRAWY’.
Funkcję JEŻELI,
poznaliśmy już w tej lekcji, zacznijmy więc od funkcji PRAWY, wycina ona
określoną ilość liter od prawej strony tekstu.
W poniższym
przykładzie wycina 1 literę z prawej strony tekstu znajdującego się w komórce
B3. (Ostatnia litera z tekstu ‘Agata’ to „a”.)
W komórce D3 zgodnie
z poniższym rysunkiem wprowadzamy funkcję JEŻELI, która gdy w komórce C3
znajduje się litera „a” daje rezultat: „Kobieta”, a przeciwnym wypadku
„Mężczyzna”.
Rozwiązaliśmy już
dane nam zadanie, ale postarajmy się to zrobić przy użyciu jednej a nie dwóch
komórek.
Wchodzimy w komórkę
C3 i zaznaczamy znajdującą się w niej formułę pomijając znak równości.
Kopiujemy zaznaczoną
formułę (wciskając Ctrl+C), po czym wychodzimy z komórki wciskając klawisz Esc.
Wchodzimy do komórki
D3, gdzie zaznaczamy adres C3 i wklejamy skopiowaną wcześniej formułę w jego
miejsce (adres zostanie automatycznie usunięty).
W ten sposób
uzyskaliśmy funkcję zagnieżdżoną. Kolumna C nie jest już dłużej nam potrzebna.
Po skopiowaniu
formuły do poniższych wierszy i skasowaniu kolumny C z ostatnimi literami
powinniśmy uzyskać widok taki jak poniżej.
Zaawansowani
użytkownicy Excela od razu wprowadzają wiele zagnieżdżonych funkcji,
zaprezentowany powyżej sposób jest najbardziej użyteczny dla początkujących i pozwala
zrozumieć logikę łączenia funkcji.
SUMA.ILOCZYNÓW
(Arkusz ‘SUMA.ILOCZYNÓW’)
W poniższym przykładzie mamy dane wartości sprzedaży i marży procentowej
dla 4 krajów, obliczyliśmy już sumę sprzedaży, teraz chcemy obliczyć marżę
procentową dla sumy tych krajów. Oczywiście użycie funkcji ŚREDNIA nie dałoby
właściwego rozwiązania ze względu na to że sprzedaż w krajach ma różne
wielkości i kraje te w rożny sposób wpływają na marżę całościową.
W tym 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, formuła
taka jest pokazana w komórce I4.
Dzięki sumie
iloczynów formuła ta zostanie wprowadzona szybciej, ograniczymy ryzyko błędu, a
w przypadku dodania kolejnego kraju nie będzie konieczności dokonywania zmian w
formule.
Stajemy w komórce G4,
wybieramy funkcję SUMA.ILOCZYNÓW i wprowadzamy adresy tak jak na poniższym
rysunku.
Aby uzyskać średnią
ważoną wystarczy na końcu formuły dopisać dzielenie przez sumę sprzedaży
znajdującą się w komórce G5.
Właściwy wynik to
32%, taki sam jak uzyskaliśmy korzystając z formuły.
Na koniec dodajmy
jeszcze kolumnę pomiędzy Polską a Rosją wprowadzając do niej nowe dane.
Średnia ważona marża
w H4 aktualizowana jest na bieżąco.
SZUKAJ.TEKST
FRAGMENT.TEKSTU
(Arkusz ‘Funkcje Tekstowe’)
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:”).
CZY.BŁĄD
(Arkusz ‘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 i w połączeniu z
funkcją JEŻELI.
SUMY.CZĘŚCIOWE
(Arkusz: ‘SUMY.CZĘŚCIOWE)
W poniższym
przykładzie prezentuje porównanie wykorzystania funkcji SUMY.CZĘŚCIOWE 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 sumy dla regionów, właściwym rozwiązaniem dla tego typu
problemu byłoby zsumowanie komórek sum dla regionów (odpowiednia formuła
znajduje się w komórce C27).
W kolumnie D
prezentuję użycie funkcji SUMY.CZĘŚCIOWE,
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.CZĘŚCIOWE 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.CZĘŚCIOWE pomija jedynie
inne SUMY.CZĘŚCIOWE, sumuje natomiast zwykłą funkcję SUMA. Wynik jest błędny.
=SUMY.CZĘŚCIOWE(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.
|
Funkcja
nr |
Funkcja_nr |
Funkcja |
|
1 |
101 |
ŚREDNIA |
|
2 |
102 |
ILE.LICZB |
|
3 |
103 |
ILE.NIEPUSTYCH |
|
4 |
104 |
MAKSIMUM |
|
5 |
105 |
MINIMUM |
|
6 |
106 |
ILCZYN |
|
7 |
107 |
ODCH.STANDARDOWE |
|
8 |
108 |
ODCH.STANDARD.POPUL |
|
9 |
109 |
SUMA |
|
10 |
110 |
WARIANCJA |
|
11 |
111 |
WARIANCJA.POPUL |
Funkcje Finansowe
NPV, IRR, CAGR
(Arkusz: ‘Funkcje Finansowe’)
NPV
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
(Symbol ^ oznacza podniesienie do potęgi i
można go wpisać z klawiatury przytrzymując Shift i wciskając cyfrę 6.)
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 wartość z
2012 trzykrotnie (trzecia potęga czyli ^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 ryzyko błędu byłoby bardzo duże.
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.
IRR
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.
Dla sprawdzenia poprawności obliczeń możemy
wynik funkcji IRR wpisać w komórce C5 (wprowadzamy z klawiatury 8,886%), zysk
dla tego projektu będzie wynosił zero (komórki C12 i C13).
CAGR
CAGR to średni roczny wzrost/spadek obliczany
dla okresu określonej liczby lat. W Excelu nie ma funkcji która by go
obliczała, warto więc zapamiętać formułę dzięki której możemy to zrobić.
W poniższym przykładzie sprzedaż w 2010 roku
wynosiła
Skorzystamy z następującego wzoru:
Średni wzrost = (Wartość Końcowa / Wartość
Początkowa) ^ (1/(ilość okresów -1))-1
W poniższym przykładzie:
Wartość Końcowa
=23,6
(wartość w roku 2019)
Wartość Początkowa
= 10 (wartość w roku 2010)
ilość okresów
= 10
(w tabeli pokazane są
dane dla 10 lat )
W poniższym przykładzie formuła będzie wyglądać
następująco:
Średni wzrost =(O27/F27)^(1/9)-1
WYBIERZ
(Arkusz: ‘WYBIERZ)
Działanie funkcji WYBIERZ jest bardzo proste,
spośród listy wartości, które należy wprowadzić oddzielnie, wybiera tą, której
numer pojawi się w pozycji Nr_arg.
W poniższym przykładzie wprowadzono 5
wysokości rabatów, które zależą od ‘klasy klienta’ przyjmującej wartości od 1
do 5.
Po wprowadzeniu cyfry do komórki B4 rabat
zostaje wyszukany i podany w komórce C4.
Funkcja ta jest podobna w swym działaniu do
funkcji WYSZUKAJ.PIONOWO, choć oferuje mniej możliwości.
MODUŁ.LICZBY
(Arkusz ‘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
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
Funkcja bywa używana najczęściej do tworzenia
różnorodnych danych, które wyglądają realistycznie, większość danych w tym
szkoleniu powstała przy użyciu tej funkcji. Często też w dyskusjach w wielkich
korporacjach niektóre osoby sugerują tą funkcję jako możliwe źródło danych,
które otrzymały od innych działów, krajów lub partnerów biznesowych.
Więcej o funkcjach Excela w lekcji ‘Funkcje
dla Zaawansowanych’.