Funkcje w MS Excel

Arkadiusz Banasik
[e-mail]

2/27

Plan prezentacji



Wprowadzenie



Funkcje matematyczne



Funkcje logiczne



Funkcje finansowe



Podsumowanie

3/27

Wprowadzenie

Funkcje:



Są elementami wbudowanymi w arkusz
kalkulacyjny



Upraszczają skomplikowane operacje
matematyczne



Są narzędziami nie wymagającymi
dokładnej znajomości aparatu
matematycznego

4/27

Funkcje matematyczne

Funkcja SUMA:



Funkcja matematyczna SUMA zapewnia
szybkie automatyczne sumowanie

wartości zapisanych w komórkach arkusza



Najprostszy zapis formuły funkcji

matematycznej SUMA jest następujący:

=SUMA(liczba_1;liczba_2; ... ;liczba_30)

5/27

Funkcje matematyczne

Przykład:



Dokonać sumowania trzech wartości
liczbowych: 2, 3, 4, używając odpowiedniej
funkcji matematycznej

Rozwiązanie:

6/27

Funkcje matematyczne

Funkcja SUMA.JEŻELI:



Kolejna funkcja matematyczna

SUMA.JEŻELI jest specyficzną funkcją,

gdyż jej struktura zawiera w sobie również

elementy dwóch różne: matematycznej

(SUMA)i logicznej (JEŻELI)



Składnia formuły odpowiadającej funkcji

SUMA.JEŻELI jest następująca:

=SUMA.JEŻELI(zakres; kryteria; suma_zakres)

7/27

Funkcje matematyczne

Przykład:



Wykonać sumę wartości spełniających
podane kryterium, wykorzystując funkcję
matematyczną SUMA.JEŻELI



Rozwiązanie:

8/27

Funkcje matematyczne

Funkcja ILOCZYN:



Działanie funkcji ILOCZYN usprawnia
i przyspiesza wykonanie działania
mnożenia



Składnia formuły tej funkcji ma
następującą postać:

=ILOCZYN(liczba_1;liczba_2;...;liczba_30)

9/27

Funkcje matematyczne

Przykład:



Obliczyć wartość iloczynu pięciokrotności
liczby 2, stosując funkcję ILOCZYN



Rozwiązanie:

10/27

Funkcje logiczne

Funkcja JEŻELI:



Funkcja JEŻELI pozwala zdefiniować warunek,
a następnie w zależności od tego czy warunek
jest spełniony przypisać komórkom
odpowiednie wartości



Składnia formuły tej funkcji ma następującą
postać:

=JEŻELI(warunek logiczny; wartość jeżeli

prawda

; wartość jeżeli fałsz)

11/27

Funkcje logiczne

Przykład:



Napisać formułę obliczającą premię pracownikom o stażu powyżej 1
roku. Wysokość premii to 10% miesięcznej kwoty. Należy tak
sformułować formułę, że w przypadku pracowników o stażu
mniejszym niż 1 rok w odpowiedniej komórce kolumny „Premia”
powinien pojawić się napis „brak”



Rozwiązanie:

12/14

Funkcje logiczne

Funkcja LUB:



Funkcja ta wyświetla wartość logiczną prawda,
jeżeli przynajmniej jeden argument ma wartość
logiczną prawda; jeśli wszystkie argumenty
mają wartość fałsz, funkcja podaje wartość
fałsz



Składnia formuły tej funkcji ma następującą
postać:

=LUB(wartość_logiczna_1; wartość_logiczna_2;...)

13/27

Funkcje logiczne

Przykład:



Pewna firma ogłosiła, że będzie sponsorem wyjazdu na letnie
wakacje dla dzieci, które zajęły jedno z trzech pierwszych miejsc
w Igrzyskach Sportowych zorganizowanych w pewnym mieście.
Należy utworzyć zestawienie dzieci wyjeżdżających na wakacje.
W kolumnie wakacje sponsorowane należy wyświetlić informację
TAK dla dzieci, które spełniły warunek firmy, a dla dzieci, które go
nie spełniły informację NIE



Rozwiązanie:

14/27

Funkcje logiczne

Funkcja ORAZ:



Funkcja ta wyświetla wartość logiczną prawda,
jeżeli wszystkie argumenty mają wartość
logiczną prawda; jeśli przynajmniej jeden
argument ma wartość fałsz, funkcja wyświetla
wartość logiczną fałsz



Składnia formuły tej funkcji ma następującą
postać:

=ORAZ(wartość_logiczna_1;wartość_logi

czna_2;...)

15/27

Funkcje logiczne

Przykład:



Handlowcom pewnej firmy, którzy sprzedali 50 sztuk oferowanego
przez firmę wyrobu i nie są pracownikami na stażu, czyli pracują
dłużej niż jeden miesiąc przysługuje nagroda wysokości 5000 zł.
Należy utworzyć zestawienie pracowników i w kolumnie nagroda
przyznać nagrodę tym, którzy spełnili warunek postawiony przez
firmę



Rozwiązanie:

16/27

Funkcje finansowe

Funkcja FV:



Funkcja ta oblicza przyszłą wartość lokaty, przy
założeniu stałych płatności i stałej stopie procentowej.



Składnia formuły tej funkcji ma następującą postać:

=FV(stopa; liczba_rat; rata; wa; typ

stopa

stopa procentowa

liczba_rat

całkowita liczba płatności w czasie spłaty pożyczki lub oszczędzania

rata

okresowa wpłata nie ulegająca zmianie w czasie

wa

kapitał początkowy; jeżeli argument ten jest pominięty to przyjmuje

wartość 0

typ

to cyfra 0 lub 1 wskazująca, kiedy płatność ma miejsce (0 na końcu

okresu, 1-

na początku okresu)

17/27

Funkcje finansowe

Przykład:



Pani X chce wpłacić na rachunek oszczędnościowy kwotę 1500 zł.
Dodatkowo planuje wpłacać co miesiąc 200 zł przez rok. Bank
proponuje jej oprocentowanie w wysokości 15% w skali roku. Ile
pieniędzy w ciągu roku zgromadzi Pani X?



Rozwiązanie:



stopa= 15%/12, bo stopa wynosi 15% w skali roku a wpłaty
dokonywane będą co miesiąc



liczba rat= 12



rata= -200 (znak (-

) oznacza odpływ gotówki



wa= -

1500, bo taki jest kapitał początkowy Pani X



Pani X zgromadzi w ciągu roku kwotę 4 313,20 zł, gdyż
FV(15%/12;12;-200;-1500).

18/27

Funkcje finansowe

Funkcja PV:



Funkcja ta oblicza wartość bieżącą przyszłych
płatności, przy założeniu stałych płatności i stałej stopie
procentowej



Składnia formuły tej funkcji ma następującą postać:

=VP(stopa; liczba_rat; rata; wp; typ)

stopa

stopa procentowa

liczba rat

całkowita liczba płatności i kapitalizacji

rata

okresowa

wpłata nie ulegająca zmianie w czasie

typ

to cyfra 0 lub 1

wskazująca, kiedy płatność ma miejsce (0 na

końcu okresu, 1-na początku okresu)

19/27

Funkcje finansowe

Przykład:



Panu X bank proponuje lokatę terminową z kwartalną
kapitalizacją odsetek oprocentowaną na 15% w skali
roku. Pan X chciałby wiedzieć, jaka kwotę powinien
zdeponować, by po trzech latach zgromadzić 10 000zł



Rozwiązanie:



Pan X musiałby zdeponować 6 428,99 zł, ponieważ
PV(15%/4;12;0;10000;0)

20/27

Funkcje finansowe

Funkcja PMT:



Za pomocą tej funkcji obliczana jest wartość
raty przy spłacaniu pożyczki, przy założeniu,
że stopa procentowa oraz raty w kolejnych
okresach są stałe. Może być również użyta
do wyliczania kwoty, jaka należy okresowo
wpłacać na konto, aby po pewnej liczbie
okresów zgromadzić na nim określoną kwotę

21/27

Funkcje finansowe

Funkcja PMT:



Składnia formuły tej funkcji ma następującą
postać:

=PMT(stopa; liczba_rat; wa; wp; typ)

stopa

stopa procentowa

liczba_rat

całkowita liczba płatności w czasie spłaty pożyczki lub

oszczędzania

wa

aktualna wartość zaciągniętej pożyczki

wp

kwota, którą zamierzamy zgromadzić na koncie po dokonaniu

ostatniej płatności

typ

to cyfra 0 lub 1 wskazująca, kiedy płatność ma miejsce (0 na

końcu okresu, 1-na początku okresu)

22/27

Funkcje finansowe

Przykład:



Pan X zaciągnął pożyczkę w wysokości 20 000zł na 2
lata, oprocentowanie wynosi 25% w skali roku i
pożyczka będzie spłacana w miesięcznych ratach.
Obliczyć miesięczną ratę spłaty pożyczki



Rozwiązanie:



stopa

= 25%/4, ponieważ stopa wynosi 25% w skali

roku, a spłat dokonuje się co kwartał



liczba rat

= 2* 4 czyli 2 lata * 4 kwartały



wa= 20 000



Wysokość miesięcznej raty wynosi =PMT(25%/4; 2*4;
20000;0;0) czyli

–3 252,66 zł

23/27

Funkcje finansowe

Funkcja IPMT:



Funkcja ta oblicza wysokość odsetek, które
należy spłacić w danym okresie zakładając, że
stopa procentowa oraz raty w kolejnych
okresach są stałe. Odsetki wraz z kwotą
spłacanej pożyczki stanowią okresową ratę
obliczaną przy pomocy funkcji PMT

24/27

Funkcje finansowe

Funkcja IPMT:



Składnia formuły tej funkcji ma następującą
postać:

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

stopa

stopa procentowa

okres

okres, dla którego obliczane są odsetki

liczba_rat

całkowita liczba płatności w czasie spłaty pożyczki lub

oszczędzania

wa

aktualna wartość zaciągniętej pożyczki

wp

kwota, którą zamierzamy zgromadzić na koncie po

dokonaniu ostatniej płatności

25/27

Funkcje finansowe

Przykład:



Pan X zaciągnął pożyczkę w wysokości 20 000zł na 2 lata,
oprocentowanie wynosi 25% w skali roku i pożyczka będzie
spłacana w miesięcznych ratach. Obliczyć odsetki



Rozwiązanie:



stopa= 25%/4, ponieważ stopa wynosi 25% w skali roku, a spłat
dokonuje się co kwartał



okres=1 dla pierwszego (2 dla drugiego itd.)



liczba rat= 2* 4 czyli 2 lata * 4 kwartały



wa= 20 000



Wysokość odsetek w pierwszym kwartale wynosi
IPMT(25%/4;1;8;20000;0) czyli

–1250,00 zł

.

26/27

Podsumowanie



Każda funkcja usprawnia działania
matematyczne



Stosowanie funkcji zależne jest od ilości
danych oraz specyfiki zadania



Funkcje w arkuszu rozpoczynamy zawsze
od znaku „=”

27/27

Dziękuję

[e-mail]