Wydawnictwo Helion
ul. Kociuszki 1c
44-100 Gliwice
tel. 032 230 98 63
e-mail: helion@helion.pl
Excel 2007 PL.
Leksykon kieszonkowy.
Wydanie II
Autor: Curt Frye
T³umaczenie: Zbigniew Smogur
ISBN: 978-83-246-1551-3
Tytu³ orygina³u:
Excel 2007
Pocket Guide, 2nd edition
Format: 115x170, stron: 192
Podrêczne kompendium wiedzy dla u¿ytkowników Excela
Excel nale¿y do grupy tych aplikacji, których u¿ytkownicy wykorzystuj¹ niewielk¹ czêæ
mo¿liwoci, nie zdaj¹c sobie sprawy, jak ogromny potencja³ narzêdzia pozostaje jeszcze
nieodkryty. Program kojarzy siê z grupowaniem danych, tworzeniem zestawieñ
i wykresów, wystawianiem faktur i rachunków. Jednak Excel to znacznie wiêcej.
Ogrom jego mo¿liwoci powinno odkrywaæ siê z grub¹ ksiêg¹ zawieraj¹c¹ opisy
narzêdzi oferowanych przez tê aplikacjê, ale w codziennej pracy opas³e tomiska okazuj¹
siê kompletnie niepraktyczne W takich sytuacjach nieocenion¹ pomoc¹ staj¹ siê
podrêczne zestawienia najwa¿niejszych wiadomoci.
Ksi¹¿ka Excel 2007 PL. Leksykon kieszonkowy. Wydanie II to w³anie takie
zestawienie. Zebrano w niej najistotniejsze dla u¿ytkownika informacje dotycz¹ce
najnowszej wersji Excela, nosz¹cej oznaczenie 2007. Czytaj¹c j¹, poznasz nowy
interfejs u¿ytkownika, budowê dokumentów Excela i narzêdzia autokorekty.
Nauczysz siê wprowadzaæ dane, formatowaæ je i wykorzystywaæ do obliczeñ.
Skorzystasz z przydatnych funkcji i formu³, stworzysz wykresy i prawid³owo
wydrukujesz arkusz na drukarce. Przeczytasz tak¿e o tabelach przestawnych,
sterowaniu sposobem wywietlania danych i publikowaniu arkuszy w sieci.
Interfejs u¿ytkownika
Pasek narzêdzi
Struktura dokumentu Excela
Praca z plikami
Drukowanie
Wprowadzanie i formatowanie danych
Formu³y i funkcje
Wykresy
Tabele przestawne
Pracuj efektywniej. Korzystaj z praktycznych porad
zebranych w jednej, porêcznej ksi¹¿ce.
3
Spis tre
ļci
1. Zrozumie
ë Excela ..........................................................................5
Co nowego w Excelu 2007
6
Interfejs Excela
7
Budowa pliku Excela
20
Formatowanie
24
Menu podröczne i minipasek narzödzi
33
Sposób, w jaki Excel próbuje pomóc
36
2. Zadania w Excelu ........................................................................39
Praca z plikami
40
Drukowanie
44
Poruszanie siö po skoroszycie lub arkuszu
50
Operacje wykonywane na skoroszytach i arkuszach
54
Operacje wykonywane na wierszach, kolumnach
i komórkach
56
Wprowadzanie i edytowanie danych
60
Formatowanie komórek
76
Praca z hiperäñczami
87
Praca z nagäówkami i stopkami
89
Tworzenie podsumowaþ danych
92
Stosowanie zakresów nazwanych
97
Definiowanie alternatywnych zestawów danych
99
Sterowanie sposobem wyĈwietlania danych
101
Zabezpieczanie czöĈci lub caäoĈci skoroszytu
107
Pisownia i inne narzödzia
108
Dostosowywanie Excela
112
4
_
Spis tre
ļci
Wspóäpraca
115
Praca na potrzeby sieci WWW
120
Podsumowywanie danych przy uĔyciu wykresów
121
Podsumowywanie danych przy uĔyciu
tabel przestawnych oraz wykresów przestawnych
124
3. Opis formu
ĥ ...............................................................................128
Nowe funkcje w Excelu 2007
128
Funkcje matematyczne
129
Funkcje daty i godziny
137
Funkcje finansowe
139
Funkcje wyszukiwania i adresu
145
Funkcje logiczne
149
Funkcje tekstowe
151
Funkcje statystyczne
152
4. Informacje na temat Excela ...................................................... 162
Spis poleceþ
163
Formaty wbudowane
164
Przeäñczniki startowe
165
Symbole wieloznaczne w filtrowaniu i wyszukiwaniu
166
DomyĈlne lokalizacje plików
167
Skróty klawiaturowe
169
5. Zasoby dotycz
éce Excela .......................................................... 179
Witryny internetowe
179
KsiñĔki
180
Narzödzia dla Excela
181
Skorowidz ................................................................................. 185
128 _
Excel 2007 PL. Leksykon kieszonkowy
Rozdzia
ĥ 3. Opis formuĥ
Niniejszy rozdziaä dostarcza szybkich, ale dokäadnych opisów
najbardziej uĔytecznych funkcji zaimplementowanych w Excelu
2007. Prawdopodobnie czöĈci z nich kaĔdy z uĔytkowników do
tej pory uĔywaä, ale na pewno znajdzie siö kilka, które nie byäy
uĔytkownikowi znane. W niniejszym rozdziale znajdujñ siö na-
stöpujñce sekcje:
x Nowe funkcje w Excelu 2007.
x Funkcje matematyczne.
x Funkcje daty i godziny.
x Funkcje finansowe.
x Funkcje wyszukiwania i adresu.
x Funkcje logiczne.
x Funkcje tekstowe.
x Funkcje statystyczne.
Nowe funkcje w Excelu 2007
W odpowiedzi na Ĕñdania uĔytkowników zespóä pracujñcy nad
Excelem doäñczyä szeĈè nowych funkcji arkuszowych:
ćREDNIA.JEēELI
Oblicza Ĉredniñ arytmetycznñ dla komórek, które speäniajñ
pojedyncze kryterium.
ćREDNIA.WARUNKÓW
Oblicza Ĉredniñ arytmetycznñ dla komórek, które speäniajñ
zestaw kryteriów.
Rozdzia
ĥ 3. Opis formuĥ _ 129
SUMA.JE
ēELI
Oblicza sumö komórek, które speäniajñ wiele kryteriów
(funkcja
SUMA.JE
ŠELI
obliczajñca sumö komórek speänia-
jñcych jedno kryterium dostöpna byäa we wczeĈniejszych
wersjach Excela).
LICZ.JE
ēELI
Oblicza liczbö komórek, które speäniajñ wiele kryteriów
(funkcja
LICZ.JE
ŠELI
obliczajñca liczbö komórek speänia-
jñcych jedno kryterium dostöpna byäa we wczeĈniejszych
wersjach Excela).
RANDBETWEEN
Zwraca losowñ liczbö caäkowitñ z zakresu podanego przez
uĔytkownika.
JE
ēELI.BãðD
WyĈwietla komunikat uĔytkownika, jeĔeli w komórce wy-
stñpi bäñd.
Szczegóäowe opisy kaĔdej z nowych funkcji znajdujñ siö w dal-
szej czöĈci tego rozdziaäu.
Uwaga
W Excelu 2003 i w wersjach wczeĈniejszych, aby uĔywaè wielu
zaawansowanych funkcji statystycznych, trzeba byäo zainsta-
lowaè najpierw dodatek Analysis ToolPak. Funkcje te, obej-
mujñce rozkäad chi-kwadrat czy analizö ANOVA, wbudowane
zostaäy bezpoĈrednio do Excela 2007.
Funkcje matematyczne
Aby na arkuszu danych przeprowadziè obliczenia matematyczne,
naleĔy skorzystaè z poniĔszych funkcji.
130 _
Excel 2007 PL. Leksykon kieszonkowy
KOMBINACJE
KOMBINACJE(liczba;liczba_wybrana)
Zwraca liczbö moĔliwych kombinacji, kiedy wybierze siö
liczb
ö_wybranñ elementów ze zbioru o rozmiarze liczba. Pod-
czas obliczania liczby moĔliwych kombinacji nie ma zna-
czenia, w jakiej kolejnoĈci sñ one wybierane (np. 1, 2, 3 jest
toĔsame z 3, 2, 1 oraz 2, 1, 3). JeĔeli uĔytkownikowi zaleĔy
na kolejnoĈci wyboru, powinien uĔyè funkcji
PERMUTACJE
,
aby obliczyè liczbö moĔliwych permutacji.
Przykäad:
=KOMBINACJE(52,2)
zwraca wartoĈè 2.598.960.
SILNIA
SILNIA(liczba)
Funkcja silnia zwraca silniö z podanej liczby, która jest ilo-
czynem kaĔdej z narastajñcych o jeden liczb caäkowitych,
poczñwszy od 1, a koþczñc na wartoĈci liczby (np. silnia
z 4 wynosi 1*2*3*4 lub 24).
Przykäad:
=SILNIA(8)
zwraca wartoĈè 40.320.
LICZBA.CA
ãK
LICZBA.CA
ĩK(liczba)
Funkcja
LICZBA.CA
ĩK
zwraca czöĈè caäkowitñ liczby, usuwajñc
(odcinajñc) czöĈè dziesiötnñ liczby, jeĔeli taka wystöpuje.
Przykäad:
=LICZBA.CA
ĩK(14,7)
zwraca wartoĈè 14.
PERMUTACJE
PERMUTACJE(liczba;wybrana_liczba)
Funkcja
PERMUTACJE
znajduje liczbö moĔliwych permutacji,
gdy wybrana zostanie wybrana_liczba elementów ze zbioru
o rozmiarze liczba. Podczas obliczania moĔliwej liczby per-
mutacji waĔna jest ich kolejnoĈè wyboru (np. 1, 2, 3 nie jest
Rozdzia
ĥ 3. Opis formuĥ _ 131
toĔsame z 2, 3, 1). JeĔeli kolejnoĈè wyboru elementów nie
ma graè roli, naleĔy uĔyè funkcji
KOMBINACJE
, aby obliczyè
liczbö moĔliwych kombinacji.
Przykäad:
=PERMUTACJE(10,4)
zwraca wartoĈè 5.040.
LOS
LOS()
Funkcja LOS, która zawsze uĔywana jest bez wpisywanego
w nawiasach argumentu, generuje uäamkowñ wartoĈè losowñ
z dokäadnoĈciñ do 15 miejsc po przecinku. WartoĈci tej
moĔna nastöpnie uĔywaè w formule.
Przykäad:
=JE
ŠELI((LOS())>=0,95; "Audyt"; "Brak au-
dytu")
.
RANDBETWEEN
RANDBETWEEN(warto
Łð_dolna;wartoŁð_górna)
Funkcja
RANDBETWEEN
, która jest nowoĈciñ w Excelu 2007,
zwraca losowñ liczbö caäkowitñ z przedziaäu zdefiniowa-
nego przez wartoĈè_dolnñ i wartoĈè_górnñ (wäñcznie). Argu-
ment wartoĈè_dolna musi byè mniejszy niĔ wartoĈè_górna.
Przykäad:
=RANDBETWEEN(1;100)
Wskazówka
Funkcje
LOS
oraz
RANDBETWEEN
sñ funkcjami niestabilnymi,
co oznacza, Ĕe Excel przelicza je za kaĔdym razem, kiedy
przelicza arkusz. JeĔeli zachodzi potrzeba wygenerowania
liczby losowej, która póĒniej nie bödzie ulegaäa zmianie, naleĔy
uĔyè funkcji
LOS
lub
RANDBETWEEN
w pasku formuäy, nacisnñè
klawisz F9, a nastöpnie Enter. NaciĈniöcie F9 powoduje usuniö-
cie z komórki formuäy, ale zapamiötanie wartoĈci wyniku.
132 _
Excel 2007 PL. Leksykon kieszonkowy
ZAOKR.
ZAOKR(liczba;liczba_cyfr)
Funkcja
ZAOKR
. zaokrñgla z prawej strony argument liczba
do liczby miejsc po przecinku zdefiniowanych przez licz-
ba_cyfr. Funkcja
ZAOKR
. zaokrñgla kaĔdñ cyfrö powyĔej 5 do
najbliĔszej wyĔszej wartoĈci (np. zaokrñgla wartoĈè 1,45 do
1,5 oraz 1,43 do 1,4). JeĔeli argument liczba_cyfr jest wartoĈciñ
ujemnñ, formuäa zaokrñgla wartoĈci o odpowiedniñ liczbö
miejsc na lewo od znaku rozdzielajñcego czöĈci dziesiötne.
Przykäad 1:
=ZAOKR(192,486;2)
zwróci wartoĈè 192,49.
Przykäad 2:
=ZAOKR(192,486;-1)
zwróci wartoĈè 190.
ZAOKR.GÓRA
ZAOKR.GÓRA(liczba;liczba_cyfr)
Funkcja
ZAOKR.GÓRA
zaokrñgla argument liczba do liczby
miejsc po przecinku okreĈlonej przez argument liczba_cyfr.
W przeciwieþstwie do funkcji
ZAOKR.
funkcja
ZAOKR.GÓRA
zawsze zaokrñgla wartoĈci w górö. JeĔeli argument liczba_cyfr
zawiera wartoĈè ujemnñ, formuäa zaokrñgla wartoĈè do tylu
miejsc na lewo od znaku uäamku dziesiötnego, ile wynosi
wartoĈè argumentu.
Przykäad 1:
=ZAOKR.GÓRA(192,40001;1)
zwróci wartoĈè
192,5.
Przykäad 2:
=ZAOKR.GÓRA(182,486;-1)
zwróci wartoĈè 190.
ZAOKR.DÓ
ã
ZAOKR.DÓ
ĩ(liczba,liczba_cyfr)
Funkcja
ZAOKR.DÓ
ĩ
zaokrñgla argument liczba do liczby
miejsc po przecinku okreĈlonej przez argument liczba_cyfr.
W przeciwieþstwie do funkcji
ZAOKR.
funkcja
ZAOKR.DÓ
ĩ
zawsze zaokrñgla wartoĈci w dóä. JeĔeli argument liczba_cyfr
zawiera wartoĈè ujemnñ, formuäa zaokrñgla wartoĈè do tylu
Rozdzia
ĥ 3. Opis formuĥ _ 133
miejsc na lewo od znaku uäamku dziesiötnego, ile wynosi
wartoĈè argumentu.
Przykäad 1:
=ZAOKR.DÓ
ĩ(192,49999;1)
zwróci wartoĈè 192,4.
Przykäad 2:
=ZAOKR.DÓ
ĩ(182,486;-1)
zwróci wartoĈè 180.
SUMY.CZ
õćCIOWE
SUMY.CZ
āŀCIOWE(nr_funkcji;zakres)
Funkcja
SUMY.CZ
āŀCIOWE
sumuje okreĈlony przez uĔytkow-
nika zakres, uĔywajñc jednej z jedenastu dostöpnych funk-
cji. Przewagñ korzystania z funkcji
SUMY.CZ
āŀCIOWE
nad
powiedzmy funkcjñ
SUMA
jest to, Ĕe moĔna okreĈliè, czy
funkcja
SUMY.CZ
āŀCIOWE
powinna obejmowaè, czy pomijaè
wartoĈci ukryte przez dziaäanie filtra lub komórki ukryte
po klikniöciu prawym przyciskiem myszy w wiersz lub
kolumnö i wybraniu polecenia Ukryj.
PoniĔsza tabela zawiera moĔliwe wartoĈci argumentu
nr_funkcji i funkcje, które one reprezentujñ. Szczegóäowe
informacje o tych funkcjach dostöpne sñ w podrozdziale
„Funkcje statystyczne”
znajdujñcej siö na koþcu tego roz-
dziaäu.
Przykäad 1: W arkuszu pokazanym na rysunku 3.1 formuäa
=SUMY.CZ
āŀCIOWE(9;A2:A11)
zwraca wartoĈè 55.
Przykäad 2: W arkuszu pokazanym na rysunku 3.2, w którym
wiersze 6 i 7 sñ ukryte, formuäa
=SUMY.CZ
āŀCIOWE(109;A2
:A11)
zwraca wartoĈè 44.
Uwaga
Formuäa w przykäadzie 1.
=SUMY.CZ
āŀCIOWE(9;A2:A11)
obej-
muje w swoich obliczeniach wartoĈci ukryte, tak wiöc zwra-
caäaby wartoĈè 55, nawet gdyby uĔyta byäa na arkuszu z ukry-
tymi wierszami, który widoczny jest na rysunku 3.2.
134 _
Excel 2007 PL. Leksykon kieszonkowy
Funkcja nr
(uwzgl
ýdnianie
warto
ļci ukrytych)
Funkcja_nr
(ignorowanie
warto
ļci ukrytych)
Funkcja
1
101
ĻREDNIA
2
102
ILE.LICZB
3
103
ILE.NIEPUSTYCH
4
104
MAKSIMUM
5
105
MINIMUM
6
106
ILOCZYN
7
107
ODCH.STANDARDOWE
8
108
ODCH.STANDARD.POPUL
9
109
SUMA
10
110
WARIANCJA
11
111
WARIANCJA.POPUL
Rysunek 3.1. Formu
äa SUMY.CZõćCIOWE obliczajñca sumö wartoĈci
z komórek A2:A11
Rozdzia
ĥ 3. Opis formuĥ _ 135
Rysunek 3.2. Formu
äa SUMY.CZõćCIOWE obliczajñca sumö wartoĈci
z widocznych komórek z zakresu A2:A11
SUMA
SUMA(liczba1;liczba2;…)
Funkcja
SUMA
dodaje do siebie wartoĈci z zakresów okreĈlo-
nych przez argumenty liczba1, liczba2 itp.
Przykäad:
=SUMA(A1;A5)
oblicza sumö wartoĈci z komórek
A1
i
A5
.
SUMA.JE
ēELI
SUMA.JE
ŠELI(zakres;kryteria;[suma_zakres])
Funkcja
SUMA.JE
ŠELI
oblicza wartoĈè sumy z komórek w za-
kresie, który speänia podane kryteria. Jako przykäad rozwaĔmy
listö widocznñ na rysunku 3.3.
Formuäa
=SUMA.JE
ŠELI(A2:A11;">=100")
oblicza sumö
z wartoĈci z komórek w zakresie
A2:A11
, których wartoĈè
jest wiöksza lub równa 100.
136 _
Excel 2007 PL. Leksykon kieszonkowy
Rysunek 3.3. Pokazana formu
äa z funkcjñ SUMA.JEēELI oblicza sumö
komórek z zakresu A2:A11, których warto
Ĉci sñ wiöksze lub równe 100
MoĔna równieĔ uĔywaè opcjonalnego argumentu suma_zakres,
aby funkcja
SUMA.JE
ŠELI
dodawaäa wartoĈci w grupie komó-
rek o adresach innych niĔ zdefiniowane w argumencie zakres.
W takim przypadku formuäa
=SUMA.JE
ŠELI(A2:A10;">=100"
;B2:B10)
doda wartoĈci z kolumny
B
, które znajdujñ siö
w tym samym wierszu, co komórki kolumny
A
speäniajñce
warunek podany w argumencie kryteria.
Przykäad: Przy wykorzystaniu danych widocznych na rysun-
ku 3.3 formuäa
=SUMA.JE
ŠELI(A2:A11;"<100")
zwróci war-
toĈè 306.
SUMA.WARUNKÓW
SUMA.WARUNKÓW(suma_zakres;kryteria_zakres1;
kryteria1; kryteria_zakres2;kryteria2…)
Funkcja
SUMA.WARUNKÓW
,
bödñca nowoĈciñ w Excelu 2007,
oblicza sumö wartoĈci komórek z zakresu suma_zakres, które
Rozdzia
ĥ 3. Opis formuĥ _ 137
speäniajñ wiele kryteriów. Jako przykäad rozwaĔmy listö po-
kazanñ na rysunku 3.4.
Rysunek 3.4. Pokazany na rysunku arkusz
Ĉledzi liczbö osób, które odwiedziäy
poszczególne wystawy w zoo
Dla takiego zestawu danych formuäa
SUMA.WARUNKÓW(C2:C8;
A2:A8;"=Poniedzia
Īek";B2:B8;"=Lwy")
obliczy sumö war-
toĈci komórek z kolumny
C
, której wiersze pokrywajñ siö
z wartoĈciñ Poniedziaäek w kolumnie
A
oraz Lwy w kolum-
nie
B
.
Przykäad: korzystajñc z danych widocznych na rysunku 3.4,
formuäa
=SUMA.WARUNKÓW(C2:C8;A2:A8;"=Poniedzia
Īek";
B2:B8;">=700")
zwraca wartoĈè 1.397.
Funkcje daty i godziny
Excel prezentuje daty i godziny, uĔywajñc liczb reprezentujñcych
liczbö dni, które minöäy od 1 stycznia 1900 roku (01.01.1900 jest
dniem 1.). Na przykäad 15 stycznia 2008 jest reprezentowany
przez liczbö 39.462.
138 _
Excel 2007 PL. Leksykon kieszonkowy
Liczby na prawo po przecinku oddzielajñcym czöĈci dziesiötne
uĔywane sñ do okreĈlania godzin, minut i sekund. Na przykäad
liczba 39462,5 oznacza godzinö 12:00.
PoniĔsze funkcje säuĔñ do manipulacji datami i godzinami w ko-
mórkach arkusza.
GODZINA
GODZINA(kolejna_liczba)
Funkcja
GODZINA
zwraca czöĈè okreĈlajñcñ godzinö i uĔywa
do tego 24-godzinnego formatu czasu (np. godzina 1:00 po
poäudniu to 13).
Przykäad: jeĔeli komórka
A4
zawiera czas i datö o wartoĈci
02.04.2008 7:14, formuäa
=GODZINA(A4)
zwróci wartoĈè 7.
MINUTA
MINUTA(kolejna_liczba)
Funkcja
MINUTA
zwraca czöĈè okreĈlajñcñ minuty zdefinio-
wanego czasu.
Przykäad: jeĔeli komórka
A4
zawiera czas i datö o wartoĈci
02.04.2008 7:14, formuäa
=MINUTA(A4)
zwróci wartoĈè 14.
MIESI
ðC
MIESI
íC(kolejna_liczba)
Funkcja
MIESI
íC
zwraca liczbö okreĈlajñcñ miesiñc zdefinio-
wanego czasu.
Przykäad: jeĔeli komórka
A4
zawiera czas i datö o wartoĈci
02.04.2008 7:14, formuäa
=MIESI
íC(A4)
zwróci wartoĈè 2.
TERAZ
TERAZ()
Funkcja
TERAZ()
zwraca bieĔñcñ datö i godzinö. Excel zmie-
nia wynik funkcji za kaĔdym razem, kiedy przeliczany jest
arkusz zawierajñcy tö funkcjö.
Rozdzia
ĥ 3. Opis formuĥ _ 139
SEKUNDA
SEKUNDA(kolejna_liczba)
Funkcja SEKUNDA zwraca czöĈè okreĈlajñcñ liczbö sekund
zdefiniowanego czasu.
Przykäad: jeĔeli komórka
A4
zawiera czas i datö o wartoĈci
02.04.2008 7:14:31, formuäa
=SEKUNDA(A4)
zwróci wartoĈè 31.
DZIE
ý.TYG
DZIE
ī.TYG(kolejna_liczba; zwracany_typ)
Funkcja
DZIE
ī.TYG
zwraca liczbö reprezentujñcñ dzieþ tygo-
dnia zawarty w dacie i godzinie podanej w argumencie
kolejna_liczba. JeĔeli argument zwracany_typ ma wartoĈè 1 lub
zostaä pominiöty, niedziela jest dniem 1, a sobota dniem 7.
JeĔeli argument zwracany_typ ma wartoĈè 2, wówczas po-
niedziaäek jest dniem 1, a niedziela dniem 7. JeĔeli zaĈ argu-
ment zwracany_typ ma wartoĈè 3, wówczas poniedziaäek jest
dniem 0, a niedziela dniem 6.
Przykäad: jeĔeli komórka
A4
zawiera datö 02.04.2008 (dzieþ ten
to poniedziaäek), formuäa
=DZIE
ī.TYG(A4)
zwróci wartoĈè 2,
a formuäa
=DZIE
ī.TYG(A4;2)
zwróci wartoĈè 1.
ROK
ROK(kolejna_liczba)
Funkcja
ROK
zwraca wartoĈè okreĈlajñcñ rok podanej warto-
Ĉci czasu.
Przykäad: jeĔeli komórka
A4
zawiera datö 02.04.2008, to for-
muäa
=ROK(A4)
zwróci wartoĈè 2008.
Funkcje finansowe
Dane finansowe uĔywane w arkuszach moĔna analizowaè za
pomocñ poniĔszych funkcji.
140 _
Excel 2007 PL. Leksykon kieszonkowy
FV
FV(stopa;liczba_rat;rata;wa;typ)
Funkcja
FV
oblicza przyszäñ wartoĈè inwestycji, do której
regularnie dokäada siö Ĉrodków. Wpäaty nie mogñ siö róĔniè
kwotñ ani okresem (muszñ wystöpowaè co miesiñc, kwar-
talnie lub z innñ regularnñ czöstotliwoĈciñ), a stopa musi byè
staäa przez caäy czas trwania inwestycji.
OkreĈlenie poprawnej wartoĈci argumentu stopa, który za-
wiera stopö inwestycji, moĔe byè nieco podchwytliwe. Argu-
ment stopa odzwierciedla stopö inwestycji podzielonñ przez
liczbö wpäat w roku. Na przykäad jeĔeli dokonuje siö czte-
rech wpäat rocznie na inwestycjö o stopie 8%, poprawna
wartoĈè argumentu stopa powinna wynosiè 2%.
Argument liczba_rat zawiera liczbö okresów päatnoĈci w cza-
sie trwania caäej inwestycji. Na przykäad jeĔeli dokonuje
siö czterech wpäat rocznie przez 15 lat trwania inwestycji,
argument liczba_rat powinien wynosiè 60. Argument rata
zawiera wartoĈè kwoty wpäacanej na inwestycjö co kaĔdy
dany okres i wyraĔona jest jako liczba ujemna.
Uwaga
Argument rata wyraĔany jest jako wartoĈè ujemna, poniewaĔ
odzwierciedla wypäatö Ĉrodków z konta uĔytkownika.
Argument rata moĔna pominñè lub wpisaè wartoĈè 0, ale
wówczas naleĔy zdefiniowaè wartoĈè argumentu wa (wartoĈè
aktualna). Analogicznie, jeĔeli wartoĈè argumentu wa zosta-
nie pominiöta lub ustawiona na 0, co oznacza, Ĕe inwestycja
nie ma wartoĈci do momentu wpäacenia pierwszej raty, trzeba
okreĈliè wartoĈè argumentu rata.
Ostatni argument, czyli typ, okreĈla, czy wpäaty nastöpujñ na
poczñtku, czy na koþcu okresu wpäaty. DomyĈlnñ wartoĈciñ
jest 0, co oznacza, Ĕe wpäata nastöpuje na koniec kaĔdego
Rozdzia
ĥ 3. Opis formuĥ _ 141
okresu. JeĔeli argument typ zostanie ustawiony na 1, bödzie
to oznaczaè, Ĕe Excel zaäoĔy, iĔ wpäata nastöpowaè bödzie
na poczñtku kaĔdego okresu.
Przykäad 1: Dla inwestycji o stopie zwrotu wynoszñcej 8%
w skali roku, o czterech wpäatach rocznie wynoszñcych 1000
PLN i majñcej trwaè przez 15 lat formuäa
=FV(2%;60;-1000)
zwróci wartoĈè 114.051,54 PLN.
Przykäad 2: Dla inwestycji o stopie zwrotu wynoszñcej 8%
w skali roku, o czterech wpäatach rocznie wpäacanych na
poczñtku kaĔdego okresu i wynoszñcych 1000 PLN, majñ-
cej trwaè przez 15 lat, formuäa
=FV(2%;60;-1000;1)
zwróci
wartoĈè 116.332,57 PLN.
IRR
IRR(warto
Łci;wynik)
Funkcja
IRR
oblicza wewnötrznñ stopö zwrotu lub efekty-
wnñ stopö zwrotu uzyskanñ dla serii przepäywów Ĉrodków
pieniöĔnych. Inaczej niĔ to jest w przypadku w funkcji
NPV
(Net Present Value — wartoĈè bieĔñca netto inwestycji),
przepäywy Ĉrodków pieniöĔnych nie muszñ mieè tej samej
wartoĈci. Funkcja
IRR
wymaga przynajmniej jednej wartoĈci
ujemnej (zazwyczaj reprezentujñcej poczñtkowñ inwestycjö)
i jednej wartoĈci dodatniej do tego, aby obliczyè wynik.
Argument wartoĈci zawiera odwoäania do komórek zawie-
rajñcych kwoty przepäywu Ĉrodków pieniöĔnych. Dodat-
kowy argument wynik zawiera najlepsze przybliĔenie do
wartoĈci aktualnej wewnötrznej stopy zwrotu. JeĔeli argu-
ment wynik zostanie pominiöty, co zazwyczaj ma miejsce,
Excel zaäoĔy 10-procentowy punkt startowy. JeĔeli nie bödzie
w stanie wyliczyè funkcji
IRR
w 20 krokach (Excel uĔywa
metody iteracyjnej do wyliczania wyniku), wyĈwietli bäñd
#LICZBA!
. JeĔeli to nastñpi, naleĔy okreĈliè wartoĈè argu-
mentu wynik i nacisnñè klawisz Enter, aby ponownie obli-
czyè funkcjö.
142 _
Excel 2007 PL. Leksykon kieszonkowy
PoniĔszy przykäad uĔywa wartoĈci danych pokazanych na
rysunku 3.5.
Rysunek 3.5. Arkusz zawiera przep
äywy pieniöĔne w inwestycji,
gdzie liczby ujemne reprezentuj
ñ wpäaty do inwestycji
Przykäad:
=IRR(C2:C6)
zwróci wartoĈè 22%.
Uwaga
Wewnötrznñ stopö zwrotu inwestycji moĔna porównaè z bez-
piecznñ inwestycjñ o staäej stopie zwrotu, aby okreĈliè, czy
inwestycja jest godna rozwaĔenia.
NPV
NPV(stopa;warto
Łð1;wartoŁð2;…)
W przypadku funkcji
NPV
argument stopa okreĈla szacowanñ
stopö zwrotu z inwestycji, a argument wartoĈè1 (i kolejne
argumenty wartoĈè) definiuje komórki zawierajñce informacje
o przepäywie Ĉrodków pieniöĔnych.
Rozdzia
ĥ 3. Opis formuĥ _ 143
Funkcja
NPV
oblicza wartoĈè bieĔñcñ netto inwestycji w opar-
ciu o seriö przepäywów Ĉrodków pieniöĔnych. Funkcja
NPV
jest podobna do funkcji
PV
(Present Value — wartoĈè bie-
Ĕñca), ale funkcja
NPV
umoĔliwia, by przepäyw Ĉrodków
pieniöĔnych wystöpowaä na poczñtku lub na koþcu okresu.
Na przykäad funkcja
NPV
pozwala oprzeè obliczenia o pod-
stawö wynoszñcñ 100.000 PLN wpäacone dziĈ lub w opar-
ciu o 100.000 PLN, które zostanñ wpäacone w ciñgu nastöp-
nego roku.
Wszystkie inwestycje i straty (ujemne przepäywy Ĉrodków
pieniöĔnych) muszñ byè wyraĔone w formie wartoĈci ujem-
nych. Ponadto jeĔeli poczñtkowa inwestycja dokonywana jest
na poczñtku projektu, naleĔy dodaè ujemnñ wartoĈè do wy-
niku funkcji
NPV
, aby obliczenia odzwierciedlaäy fakt wy-
dania pieniödzy jako kwotö straconñ (czyli bez potencjaäu na
zarobienie odsetek do czasu zrealizowania caäej inwestycji).
PoniĔsze przykäady korzystajñ z danych arkusza pokazanych
na rysunku 3.6.
Rysunek 3.6. Lista danych zawieraj
ñca poczñtkowñ wpäatö i planowane
zwroty z inwestycji
144 _
Excel 2007 PL. Leksykon kieszonkowy
Przykäad 1: JeĔeli inwestycja wynoszñca 500.000 PLN ma
miejsce przed rozpoczöciem naliczania zwrotu z inwesty-
cji, naleĔy uĔyè formuäy
=NPV(E2;C3:C5)+C2
, aby obliczyè
bieĔñcñ wartoĈè netto inwestycji. Formuäa zwróci wartoĈè
163.215,27 PLN.
Przykäad 2: JeĔeli wpäata poczñtkowa wykonywana jest
przez pierwszy rok inwestycji i wynosi 100.000 PLN, bieĔñcñ
wartoĈè netto inwestycji naleĔy obliczyè, uĔywajñc formuäy
=NPV(E2;C2:C5)
. Zwróci ona wartoĈè 153.976,67 PLN.
Uwaga
Wewnötrzna stopa zwrotu z inwestycji to stopa zwrotu, dla
której bieĔñca wartoĈè netto inwestycji wynosi 0.
PV
PV(stopa;liczba_rat;rata;wp;typ)
Funkcja
PV
zwraca bieĔñcñ wartoĈè inwestycji. Na przykäad
jeĔeli uĔytkownik zaciñgnñä kredyt na kupno domu, poĔy-
czajñcy moĔe uĔyè funkcji
PV
, aby okreĈliè wartoĈè przy-
szäych rat w przypadku, gdyby chciaä odsprzedaè kredyt
innemu poĔyczajñcemu.
Argument stopa okreĈla stopö inwestycji, a liczba_rat defi-
niuje liczbö okresów päatnoĈci. JeĔeli päatnoĈci dokonywane
sñ regularnie, argument rata reprezentuje kwotö tychĔe wpäat.
Kwota musi byè taka sama i bödzie miaäa wartoĈè ujemnñ
(reprezentujñcñ odpäyw Ĉrodków z konta). Argument rata
moĔna ustawiè na wartoĈè 0, jeĔeli do inwestycji nie dopäaca
siö Ĕadnych rat.
Argument wp reprezentuje wartoĈè przyszäñ inwestycji (na
przykäad docelowy bilans gotówki). JeĔeli argument wp usta-
wiony zostanie na wartoĈè 0, naleĔy zdefiniowaè wartoĈè
argumentu rata.
Rozdzia
ĥ 3. Opis formuĥ _ 145
Argument typ moĔe przyjmowaè wartoĈè 0 (domyĈlnñ) lub 1.
JeĔeli argument typ ustawiony jest na 0 lub zostanie pomi-
niöty, Excel zaäoĔy, Ĕe opäaty bödñ nastöpowaäy na koþcu
kaĔdego okresu. JeĔeli bödzie wynosiè on 1, Excel zaäoĔy,
Ĕe opäaty bödñ wprowadzane na poczñtku kaĔdego okresu.
Przykäad: Formuäa
=PV(0,5%;60;-1000)
reprezentuje wartoĈè
bieĔñcñ piöcioletniej poĔyczki (60 miesiöcy) o stopie 6%
i miesiöcznej racie wynoszñcej 1.000 PLN. Formuäa ta zwraca
wartoĈè 51.725,56 PLN.
Funkcje wyszukiwania i adresu
Do wyszukiwania wartoĈci w arkuszach säuĔñ poniĔsze funkcje
programu Excel.
WYBIERZ
WYBIERZ(nr_arg;warto
Łð1;wartoŁð2;…)
Funkcja
WYBIERZ
pozwala na uĔywanie komórek wejĈcio-
wych do okreĈlenia, których z kilku wartoĈci uĔywaè w for-
mule. Argument nr_arg okreĈla, która wartoĈè ma zostaè
wybrana. Argumenty wartoĈè1, wartoĈè2 i kolejne okreĈlajñ
listö wartoĈci, z której dokonywany bödzie wybór. MoĔna
uĔywaè listy zawierajñcej do 255 wartoĈci.
Przykäad:
=WYBIERZ(2;10%;15%;20%)
zwróci wartoĈè 15%.
WYSZUKAJ.PIONOWO
WYSZUKAJ.PIONOWO(szukana_warto
Łð;tabela_tablica;
nr_kolumny;kolumna)
Aby zobrazowaè, jak dziaäa funkcja WYSZUKAJ.PIONOWO,
uĔyta zostanie tabela danych widoczna na rysunku 3.7.
Funkcja
WYSZUKAJ.PIONOWO
pozwala na wyszukanie wartoĈci
w pierwszej kolumnie tabeli po to, aby odnaleĒè wartoĈè
z innej kolumny. Na przykäad moĔna uĔyè IDCzöĈci do
146 _
Excel 2007 PL. Leksykon kieszonkowy
Rysunek 3.7. Niniejsza tabela Excela zawiera warto
Ĉci IDCzöĈci säuĔñce
do identyfikacji cz
öĈci podlegajñcych inwentaryzacji w sklepie z czöĈciami
samochodowymi
wyszukania opisu tejĔe czöĈci. Argument szukana_wartoĈè
funkcji
WYSZUKAJ.PIONOWO
zawiera albo wartoĈè, albo adres
komórki, która zawiera wartoĈè do odnalezienia w tabeli.
Argument tabela_tablica zawiera adres tabeli, argument
nr_kolumny jest liczbñ okreĈlajñcñ, która z kolumn zawiera
wartoĈci zwracane przez formuäö, a argument kolumna wska-
zuje, czy funkcja ma znaleĒè dopasowanie dokäadne, aby
zwróciè wynik.
Uwaga
MoĔna uĔywaè nazwy tabeli Excela jako wartoĈci argumentu
tabela_tablica, ale nie moĔna uĔywaè nazwy kolumny tabeli
jako wartoĈci argumentu nr_kolumny. W tym przypadku musi
byè uĔywana liczba caäkowita.
Rozdzia
ĥ 3. Opis formuĥ _ 147
Argument kolumna ma domyĈlnñ wartoĈè
PRAWDA
, co po-
zwala funkcji na znalezienie dokäadnego dopasowania wzglö-
dem argumentu szukana_wartoĈè albo pozwala zwróciè naj-
bliĔszñ wartoĈè, która jest mniejsza od wartoĈci argumentu
szukana_warto
Ĉè. Na przykäad jeĔeli lista danych zawiera
daty 1 lipca, 2 lipca i 5 lipca, to argument szukana_wartoĈè
o wartoĈci 4 lipca zwróciäby wpis dla wartoĈci 2 lipca.
Ustawienie argumentu kolumna na
FA
ĩSZ
zmusza funkcjö
do znalezienia dokäadnego dopasowania do wartoĈci argu-
mentu szukana_wartoĈè.
Wa
żne
JeĔeli argument kolumna ustawiony zostanie na
PRAWDA
, tabela
musi zostaè posortowana w kolejnoĈci rosnñcej w oparciu
o wartoĈci w pierwszej kolumnie tabeli.
PoniĔsze przykäady uĔywajñ prostej tabeli z danymi, która
widoczna jest na rysunku 3.7.
Przykäad 1:
=WYSZUKAJ.PIONOWO(N1;Tabela1;2)
z wartoĈciñ
PI0001 w komórce
N1
zwróci wartoĈè ćwieca zapäonowa.
Przykäad 2:
=WYSZUKAJ.PIONOWO(N1;Tabela1;2;FA
ĩSZ)
z wartoĈciñ PI0005 w komórce
N1
zwróci bäñd #ND!, gdyĔ
formuäa nie znajdzie dokäadnego dopasowania.
WYSZUKAJ.POZIOMO
WYSZUKAJ.POZIOMO(szukana_warto
Łð;tabela_tablica;
nr_wiersza;wiersz)
Funkcja
WYSZUKAJ.POZIOMO
jest bardzo podobna do funkcji
WYSZUKAJ.PIONOWO
. RóĔnica polega na tym, Ĕe zamiast uĔy-
wania funkcji
WYSZUKAJ.PIONOWO
do przeszukiwania tabel
zorientowanych pionowo, w których dane zgromadzone
sñ w kolumnach, uĔywa siö funkcji
WYSZUKAJ.POZIOMO
do
tabel poziomych, w których dane zgromadzone sñ w wier-
szach (rysunek 3.8).
148 _
Excel 2007 PL. Leksykon kieszonkowy
Rysunek 3.8. Tabela zorientowana poziomo zawiera dane o cz
öĈciach
przedstawione w nietypowy sposób
Argument szukana_wartoĈè funkcji
WYSZUKAJ.POZIOMO
zawiera
albo wartoĈè, albo adres komórki, która zawiera wartoĈè do
odnalezienia w tabeli. Argument tabela_tablica zawiera adres
tabeli; argument nr_wiersza jest liczbñ okreĈlajñcñ, który
z wierszy zawiera wartoĈci zwracane przez formuäö; argu-
ment wiersz wskazuje, czy funkcja ma znaleĒè dopasowanie
dokäadne, aby zwróciè wynik.
Argument wiersz ma domyĈlnñ wartoĈè
PRAWDA
, co pozwala
funkcji na znalezienie dokäadnego dopasowania wzglödem
argumentu szukana_wartoĈè albo pozwala zwróciè najbliĔ-
szñ wartoĈè, która jest mniejsza od wartoĈci argumentu
szukana_warto
Ĉè. Na przykäad jeĔeli lista danych zawiera
godziny 10:00, 12:00, 14:00, to argument szukana_wartoĈè
o wartoĈci 13:30 zwróciäby wpis dla wartoĈci 12:00. Usta-
wienie argumentu wiersz na
FA
ĩSZ
zmusza funkcjö do znale-
zienia dokäadnego dopasowania do wartoĈci argumentu
szukana_warto
Ĉè, co oznacza, Ĕe szukanie godziny 13:30 zwró-
ciäoby bäñd.
Rozdzia
ĥ 3. Opis formuĥ _ 149
Wa
żne
JeĔeli argument wiersz ustawiony zostanie na
PRAWDA
, tabela
musi zostaè posortowana w kolejnoĈci rosnñcej w oparciu
o wartoĈci w pierwszym wierszu tabeli.
PoniĔsze przykäady uĔywajñ prostej tabeli z danymi, która
widoczna jest na rysunku 3.8.
Przykäad 1:
=WYSZUKAJ.POZIOMO(B4;A1:E2;2)
z wartoĈciñ
PI0002 w komórce
B4
zwróci wartoĈè GaĒnik.
Przykäad 2:
=WYSZUKAJ.POZIOMO(B4;A1:E2;2;FA
ĩSZ)
z war-
toĈciñ PI0005 w komórce
B4
zwróci bäñd #ND!, gdyĔ for-
muäa nie znajdzie dokäadnego dopasowania.
Funkcje logiczne
PoniĔsze funkcje säuĔñ do wykonywania róĔnych obliczeþ, w za-
leĔnoĈci od tego, czy okreĈlone warunki zostanñ speänione.
JE
ēELI
JE
ŠELI(tekst_logiczny;wartoŁð_gdy_prawda;
warto
Łð_gdy_faĪsz)
Funkcja
JE
ŠELI
okreĈla, czy komórka wejĈciowa speänia okre-
Ĉlony tekst_logiczny, na przykäad czy zamówienie jest na
kwotö wyĔszñ niĔ 1.000 PLN. JeĔeli warunek zwraca wartoĈè
PRAWDA
, funkcja zwraca wartoĈè_gdy_prawda. JeĔeli warunek
zwraca wartoĈè
FA
ĩSZ
, funkcja zwraca wartoĈè_gdy_faäsz.
Jako przykäad rozwaĔmy nastöpujñcñ formuäö:
=JE
ŠELI(A5>100;"WysyĪka gratis";"WysyĪka standardowa")
Wskazówka
WaĔne: Dowolny tekst, który ma byè zwracany przez funkcjö
JE
ŠELI
, musi byè wpisany w cudzysäowach.
150 _
Excel 2007 PL. Leksykon kieszonkowy
Umieszczenie w komórce A5 jakiejkolwiek wartoĈci wiök-
szej niĔ 100 spowoduje, Ĕe formuäa zwróci tekst Wysyäka
gratis. WartoĈè mniejsza lub równa 100 spowoduje, Ĕe for-
muäa zwróci tekst Wysyäka standardowa.
Argumenty wartoĈè_gdy_prawda oraz wartoĈc_gdy_faäsz mogñ
równieĔ przyjmowaè postaè formuäy. JeĔeli dla kaĔdego
zamówienia powyĔej 100 PLN uĔytkownik oferuje rabat 10%,
to formuäa
=JE
ŠELI(A5>100; A5/10;A5)
zwróci kwotö
rabatu.
Funkcjö
JE
ŠELI
moĔna zagnieĔdĔaè, dziöki czemu moĔna
sprawdzaè wiöcej niĔ jeden warunek (umieszczajñc jednñ
funkcjö
JE
ŠELI
w argumencie wartoĈè_gdy_faäsz drugiej funk-
cji
JE
ŠELI
). PoniĔej znajduje siö przykäad, który umoĔliwia
realizacjö darmowej wysyäki dla zamówieþ powyĔej 100 PLN
i pobrania 5 PLN za wysyäkö w przypadku zamówieþ powy-
Ĕej 80 PLN, ale mniejszych niĔ 100 PLN:
=JE
ŠELI(A5>100;"Darmowa wysyĪka";JEŠELI(A5>80;
"5 PLN";"Wysy
Īka standardowa"))
Uwaga
Funkcjö
JE
ŠELI
moĔna zagnieĔdĔaè do 64 poziomów, ale ko-
rzystanie z funkcji
WYSZUKAJ.PIONOWO
i
WYSZUKAJ.POZIOMO
jest äatwiejsze, gdy do sprawdzenia jest wiöcej warunków niĔ
trzy lub cztery.
JE
ēELI.BãðD
JE
ŠELI.BĩíD(wartoŁð;wartoŁð_gdy_bĪîd)
Zespóä pracujñcy nad Excelem stworzyä funkcjö
JE
ŠELI.BĩíD
w odpowiedzi na Ĕñdania uĔytkowników, którzy oczekiwali
äatwiejszego sposobu na wyĈwietlanie spersonalizowanych
komunikatów bäödów. Argument wartoĈè odnosi siö do adresu
komórki lub obliczenia, na przykäad
A11/A10
, które ma byè
Rozdzia
ĥ 3. Opis formuĥ _ 151
sprawdzone pod kñtem bäödu. Argument wartoĈè_gdy_bäñd
moĔe zawieraè odwoäanie do komórki z wartoĈciñ, która
ma byè wyĈwietlona, lub tekstem (który musi byè wprowa-
dzony w cudzysäowach).
Przykäad: JeĔeli w komórce
A5
znajduje siö wartoĈè 100,
a w komórce
A6
wartoĈè 0, to formuäa
=JE
ŠELI.BĩíD(A5/A6;
"B
Īîd dzielenia przez zero!")
wyĈwietli tekst Bäñd
dzielenia przez zero!.
Funkcje tekstowe
PoniĔsze formuäy säuĔñ do wykonywania operacji na tekĈcie znaj-
dujñcym siö w arkuszach.
Z
ãðCZ.TEKSTY
Z
ĩíCZ.TEKSTY(tekst1;tekst2;tekst3;…)
Funkcja
Z
ĩíCZ.TEKSTY
wyĈwietla äaþcuchy tekstowe zawarte
w argumentach tekst funkcji (tekst1, tekst2 itp…). Argu-
menty tekst mogñ zawieraè albo odwoäania do komórek, albo
äaþcuchy tekstowe zapisane w cudzysäowach.
Przykäad: JeĔeli komórka
A1
zwiera wartoĈè ksiñĔki, a komórka
B2
zawiera wartoĈè 3, formuäa
=Z
ĩíCZ.TEKSTY(A3;" ";B3)
zwróci äaþcuch 3 ksiñĔki.
LEWY
LEWY(tekst;liczba_znaków)
Funkcja
LEWY
zwraca liczbö_znaków, poczñwszy od poczñtku
äaþcucha tekstowego zdefiniowanego przez argument tekst.
Przykäad: JeĔeli komórka
A1
zawiera wartoĈè PT301, formuäa
=LEWY(A1;3)
zwróci wartoĈè PT3.
152 _
Excel 2007 PL. Leksykon kieszonkowy
FRAGMENT.TEKSTU
FRAGMENT.TEKSTU(tekst;liczba_pocz
îtkowa;
liczba_znaków)
Funkcja
FRAGMENT.TEKSTU
zwraca znaki ze Ĉrodka äaþcucha
tekstowego okreĈlonego przez argument tekst. Argument
liczba_pocz
ñtkowa okreĈla pierwszy znak, który ma zostaè
zwrócony, a argument liczba_znaków okreĈla, ile znaków ma
zostaè zwróconych.
Przykäad: JeĔeli komórka
A1
zawiera wartoĈè BR549ALT,
formuäa
=FRAGMENT.TEKSTU(A1;3;3)
zwróci wartoĈè 549.
PRAWY
PRAWY(tekst;liczba_znaków)
Funkcja
PRAWY
zwraca liczbö_znaków, poczñwszy od koþca
äaþcucha tekstowego zdefiniowanego przez argument tekst.
Przykäad: JeĔeli komórka
A1
zawiera wartoĈè BR549ALT,
formuäa
=PRAWY(A1;3)
zwróci wartoĈè ALT.
Funkcje statystyczne
PoniĔsze funkcje statystyczne säuĔñ do podsumowywania danych
znajdujñcych siö w arkuszach.
ćREDNIA
ŀREDNIA(liczba1;liczba2;…)
Funkcja
ŀREDNIA
znajduje Ĉredniñ arytmetycznñ wartoĈci
w zakresach zdefiniowanych w argumentach liczba. KaĔdy
argument liczba moĔe byè albo liczbñ, albo odwoäaniem do
komórki.
Przykäad: JeĔeli komórki
A1:A3
zawierajñ odpowiednio war-
toĈci 7, 8 i 9, wówczas formuäa
=
ŀREDNIA(A1:A3)
zwróci
wartoĈè 8.
Rozdzia
ĥ 3. Opis formuĥ _ 153
ćREDNIA.JEēELI
ŀREDNIA.JEŠELI(zakres;kryteria; [Łrednia_zakres])
Funkcja
ŀREDNIA.JEŠELI
oblicza wartoĈè Ĉredniñ z warto-
Ĉci speäniajñcych podane kryteria. W przykäadzie posäuĔymy
siö danymi pokazanymi na rysunku 3.9.
Rysunek 3.9. Funkcja SUMA.JE
ēELI moĔe byè uĔyta do obliczenia,
ile osób odwiedzi
äo zoo w dniach o duĔym natöĔeniu odwiedzin
Formuäa
=
ŀREDNIA.JEŠELI(C2:C6;">=500")
obliczy Ĉredniñ
z wartoĈci komórek
C2:C6
, które zawierajñ wartoĈci wiöksze
lub równe 500.
MoĔliwe jest równieĔ skorzystanie z argumentu Ĉrednia_zakres,
aby funkcja
ŀREDNIA.JEŠELI
obliczaäa Ĉredniñ wartoĈè
z komórek innych niĔ zdefiniowane w argumencie zakres.
W tym przypadku formuäa
=
ŀREDNIA.JEŠELI(A2:A16;
"=Lwy”; B2:B16)
obliczyäaby wartoĈè Ĉredniñ wartoĈci
w kolumnie
B
, które wystöpujñ w tych samych wierszach,
w których wystöpujñ komórki z zakresu
A2:A16
speäniajñce
podany warunek.
154 _
Excel 2007 PL. Leksykon kieszonkowy
Przykäad:
=
ŀREDNIA.JEŠELI(B2:B6;"=Lwy";C2:C6)
zwraca
wartoĈè 801,6667.
ćREDNIA.WARUNKÓW
ŀREDNIA.WARUNKÓW(Łrednia_zakres;kryteria_zakres1;
kryteria1;kryteria_zakres2; kryteria2;…)
Funkcja
ŀREDNIA.WARUNKÓW
, która jest nowñ funkcjñ zaimple-
mentowanñ w Excelu 2007, oblicza Ĉredniñ wartoĈè z war-
toĈci komórek, które speäniajñ wiele kryteriów jednoczeĈnie.
Jako przykäad rozwaĔymy dane pokazane na rysunku 3.10.
Rysunek 3.10. Funkcja SUMA.WARUNKÓW mo
Ĕe byè uĔyta
do obliczenia
Ĉredniej okreĈlajñcej, ile osób odwiedziäo poszczególne
wystawy w dni o du
Ĕym natöĔeniu odwiedzin
W przypadku tego zestawu danych formuäa
=
ŀREDNIA.
WARUNKÓW(C2:C6;A2:A6;"=Poniedzia
Īek";C2:C6;
">=1000")
zwróciäaby wartoĈè Ĉredniñ z wartoĈci komórek
kolumny
C
, które znalezione byäyby w wierszach, w których
w kolumnie
A
wystöpuje Poniedziaäek i dla których wartoĈci
w kolumnie
C
sñ wiöksze lub równe 1000.
Przykäad:
=
ŀREDNIA.WARUNKÓW(C2:C6;A2:A6;
"=Poniedzia
Īek"; C2:C6;">=1000")
zwraca wartoĈè 1404.
Rozdzia
ĥ 3. Opis formuĥ _ 155
ILE.LICZB
ILE.LICZB(warto
Łð1;wartoŁð2;…)
Funkcja
ILE.LICZB
oblicza w zakresie liczbö komórek, które
zawierajñ liczby, daty lub tekstowñ reprezentacjö liczb.
Argumenty wartoĈè zawierajñ odwoäania do komórek lub
wartoĈci do sprawdzenia.
Przykäad: JeĔeli komórki
A1:A3
zawierajñ daty, komórka
A4
jest pusta, a komórki
A5:A6
zawierajñ nazwy, to formuäa
=ILE.LICZB(A1:A6)
zwróci wartoĈè 5.
LICZ.PUSTE
LICZ.PUSTE(zakres)
Funkcja
LICZ.PUSTE
zwraca liczbö pustych komórek w poda-
nym zakresie.
JeĔeli komórki
A1:A3
zawierajñ daty, komórka
A4
jest pusta,
a komórki
A5:A6
zawierajñ nazwy, to formuäa
=LICZ.PUSTE
(A1:A6)
zwróci wartoĈè 1.
LICZ.JE
ēELI
LICZ.JE
ŠELI(zakres;kryteria)
Funkcja
LICZ.JE
ŠELI
oblicza w zakresie liczbö komórek,
które speäniajñ podane kryteria. Jako przykäad rozwaĔmy
arkusz widoczny na rysunku 3.11.
Korzystajñc z tego arkusza, formuäa
=LICZ.JE
ŠELI(B2:B11;
"
ŀwieca zapĪonowa")
zwróciäaby wartoĈè 4.
Uwaga
Kiedy tworzone jest kryterium funkcji
LICZ.JE
ŠELI
, musi ono
byè wpisywane w cudzysäowach.
Aby tworzyè bardziej elastyczne kryteria, moĔna korzystaè
z masek zbudowanych ze znaków zapytania (?) i gwiazdek
(*). Znak zapytania odpowiada dowolnemu, pojedynczemu
156 _
Excel 2007 PL. Leksykon kieszonkowy
Rysunek 3.11. Funkcja LICZ.JE
ēELI pozwala okreĈliè, ile razy podana
warto
Ĉè lub zestaw wartoĈci wystöpuje na liĈcie danych
znakowi, podczas gdy gwiazdka pasuje do dowolnej liczby
znaków. Na przykäad kryterium
"=K*"
pasuje do imienia
Karol. Kryterium
"K?"
, które sprawdza dokäadnie jeden
znak po literze „K” nie pasuje do tego imienia.
JeĔeli zachodzi potrzeba policzenia komórek, które zawierajñ
gwiazdkö lub znak zapytania, naleĔy uĔyè znaku tyldy przed
odpowiednim znakiem (np.
"=~?"
lub
"=Ctrl+~*"
).
Przykäad 1:
=LICZ.JE
ŠELI(B2:B11; "=Filtr oleju")
zwróci
wartoĈè 3.
Przykäad 2:
=LICZ.JE
ŠELI(B2:B11; "=Filtr *")
zwróci
wartoĈè 6 (policzy wszystkie wpisy, które rozpoczynajñ siö
od säowa Filtr).
Rozdzia
ĥ 3. Opis formuĥ _ 157
LICZ.WARUNKI
LICZ.WARUNKI(zakres1;kryteria1;zakres2;
kryteria2;…)
Funkcja
LICZ.WARUNKI
oblicza liczbö komórek w zakresie,
który speänia wiele kryteriów jednoczeĈnie. Argumenty zakres
zawierajñ zakresy komórek, podczas gdy argumenty kryte-
ria przechowujñ reguäy do sprawdzania wartoĈci przecho-
wywanych w arkuszu. Podobnie jak w przypadku funkcji
LICZ.JE
ŠELI
kryteria funkcji
LICZ.WARUNKI
naleĔy umieĈciè
w cudzysäowach.
PoniĔsze przykäady opierajñ siö na danych z arkusza widocz-
nego na rysunku 3.12.
Rysunek 3.12. Funkcja LICZ.WARUNKI umo
Ĕliwia policzenie wystñpienia
warto
Ĉci na liĈcie danych w oparciu o wartoĈci w wielu kolumnach
158 _
Excel 2007 PL. Leksykon kieszonkowy
Przykäad 1:
=LICZ.WARUNKI(B2:B11;"Filtr oleju"; C2:
C11;"=Lokalna")
.
Przykäad 2:
=LICZ.WARUNKI(B2:B11;"Filtr *";C2:C11;
"=W magazynie")
.
REGLINX
REGLINX(x;znane_y;znane_x)
Funkcja
REGLINX
korzysta z mechanizmu regresji liniowej
do przewidywania przyszäych wartoĈci w oparciu o dane
bieĔñce. Jedna z takich serii danych widoczna jest na ry-
sunku 3.13.
Rysunek 3.13. Funckja REGLINX estymuje przysz
äe wartoĈci w oparciu
o przesz
äe wyniki
Argument znane_y reprezentuje zaleĔnñ macierz wartoĈci.
W tym przypadku sñ to wartoĈci reprezentujñce wielkoĈè
sprzedaĔy. Argument znane_x zawiera niezaleĔnñ macierz
Rozdzia
ĥ 3. Opis formuĥ _ 159
wartoĈci, w tym przypadku kolejne lata. Argument x za-
wiera odwoäanie do komórki, dla której ma zostaè przewi-
dziana przyszäa wartoĈè w oparciu o istniejñce dane.
Przykäad: Przy wykorzystaniu danych z rysunku 3.13
formuäa
=REGLINX(E2;B2:B10;A2:A10)
zwróci wartoĈè
718.055,56 PLN dla roku 2010 wpisanego w komórce
E2
.
MAX
MAX(liczba1;liczba2;…)
Funkcja
MAX
zwraca najwiökszñ wartoĈè z liczb podanych
w argumentach liczba. Argumenty liczba mogñ zawieraè
wartoĈci liczbowe lub odwoäania do komórek, które je za-
wierajñ.
Przykäad:
=MAX(1;5;3;7;2)
zwróci wartoĈè 7.
MEDIANA
MEDIANA(liczba1;liczba2;…)
Funkcja
MEDIANA
zwraca wartoĈè Ĉrodkowñ ze zbioru war-
toĈci zdefiniowanych przez argumenty liczba. JeĔeli liczba
argumentów jest parzysta, funkcja
MEDIANA
zwraca Ĉred-
niñ z dwóch Ĉrodkowych wartoĈci zbioru.
Przykäad 1:
=MEDIANA(1;3;5;7;8;9;11)
zwróci wartoĈè 7.
Przykäad 2:
=MEDIANA(1;3;5;7;8;9;11;14)
zwróci war-
toĈè 7,5.
MIN
MIN(liczba1;liczba2;…)
Funkcja
MIN
zwraca najmniejszñ wartoĈè opisanñ przez argu-
menty liczba. Argumenty liczba mogñ zawieraè wartoĈci licz-
bowe lub odwoäania do komórek, które je zawierajñ.
Przykäad:
=MIN(5;3;7;2)
zwraca wartoĈè 2.