~ar a:
........ . ....
~
B;z barvy_ _
.: ., "'... r
1
. .:0. :' . . . .
ZÁLOŽKA VZORKY Záložka Vzorkydovoluje nastavit pouze pozadí jedné nebo více oznacených bunek (nikoliv barvu písma). Jako pozadí mužete nastavit libovolnou barvu a prípadne i jeden z osmnácti preddefinovaných vzorku. Použití nevhodného vzorku nekdy zpusobí necitelnost textu bunky, proto doporucuji používat vzorky jen ve výjimecných prípadech.
ZÁLOŽKA ZÁMEK P'
~aml<11out bunky
r
~I
Excel disponuje tzv. funkcí zamykání. Pokud si uživatel z nejakého duvodu nepreje, aby nekteré bunky byly editovány (manipulace a zmena jejich obsahu), pak takové bunky uzamkne (lze nastavit i heslo). Pozor, zaškrtávací box Zamknoutbunkypouze urcí, zda pri zamcení celého listu bude zamcena i tato konkrétní bunka. Volba sama o sobe nic nezamyká. K zamcení listu je treba klepnout na položku Nástrojev hlavní nabídce a tam zvolit položku Zámeka v podnabídce položku Zamknout list. Teprve po zamcení listu budou uzamknuty všechny bunky, které mají zaškrtnuto Zamknout bunky.
Vzorce Velmi mocnou oblastí Excelu je oblast vzorcu. Excel má k dispozici funkce matematické, statistické, logické, financní, databázové, datové a casové, vyhledávací a informacní. Krome uvedených má uživatel možnost vytvorit i nescetné množství svých vlastních funkcí. Vzorce v Excelu lze zadávat dvema hlavními zpusoby. Bud známe presný zápis vzorce a prímo ho do bunky napíšeme, nebo použijeme takzvaného pruvodce pri tvorbe vzorce (zejména u složitejších funkcí).
TVORBA VZORCE NAPSÁNíM Nejjednodušší možnost zadání vzorce je jeho napsání do bunky,v níž má být zobrazen výsledek Tato metoda se používá vetšinou u jednoduchých vzorcu, kde není zápis príliš složitý. Na obrázku vidíte jednoduchý príklad vzorce, který secte hodnotu bunek C2 a C3 a celý soucet vynásobi koeficientem 1,S v bunce C4. Postup vytvorení takového vzorce by vypadal následovne. 1. Nastavte se na bunku, ve které má být zobrazen výsledek (v našem prípade na CS). 2. Zacnete psát vzorec stisknutím znaménka =. 3. Napište celý vzorec a stisknete klávesu Enter.
Tvorba vzorce napsáním Vždy, když se nastavíte na bunku, zobrazí se vzorec na rádku vzorcu.
1 2
2 4
~ 6
I
7
Na tomto príkladu je nutné secíst hodnotu 500 + 400 a vynásobit výsledek koeficientem 1,5. Vzorec tedy vypadá =(C2+C3)*C4. A to proto, že hodnoty se nachází v techto bunkách.
Po stisku klávesy Enter se vzorec zmenil na konkrétní hodnotu. Ve skutecnosti však bunka CS i nadále obsahuje vzorec. Jestliže zmeníte jakékoliv císlo, s nímž vzorec kalkuluje, okamžite po zmene bude vzorec prepocítán a zobraz, se aktuální hodnota. Tato možnost, ac se to né první pohled nezdá, ciní z Excelu mocný nástro, zejména pri výpoctech rozsáhlých a provázanýct tabulek, kde je na jeden výsledek vázán dalš výpocet a na nej zase další výsledek atd. Samotný vzorec lze prohlédnout pomocí rádkL vzorcu. Nastavte kurzor na vzorec
- obsah
vzorce
se zobrazí na rádku vzorcu. Obsah vzorce je možné kdykoliv upravit stiskem klávesy F2.
Jak je patrné z príkladu, ve vzorcích mužete používat i závorky. Jejich použití je v podstate obdobné jako v matematice. Dokonce je možné závorky do sebe norit, a to do mnoha úrovní V takovém prípade se ale nepoužívají ruzné druhy závorek [ { (, ale stále jeden druh - kulaté.
126
SUMA Nejpoužívanejší funkcí v Excelu je bezesporu soucet urcitého poctu bunek, resp. SUMA. Proto má suma na panelu nástroju i samostatné tlacítko L. Sumu lze aplikovat nekolika zpusoby, zrejme nejjednodušší je následující. 1. Oznacte do bloku bunky, které budou následne scítány funkcí suma. Pri oznacování oznacte i jednu bunku navíc - do ní bude umísten soucet Uakje videt na obrázku vpravo). 2. Klepnete na tlacítko sumy. Do poslední oznacené bunky Excel umístí soucet všech predchozích oznacených bunek.
h~'§J
~2!{!/
B
C
IE)
1280 3650 2890 5460 1901
Leden Únor Brezen Duben Kveten
Poznámka: Tlacítko suma lze použít i mnoha jinými zpusoby. Pokud v oznaceném bloku bunek bude jedna z bunek textového charakteru, Excel ji preskocí a bude secítat pouze bunky obsahující císla.
TVORBA VZORCE POMocí PRUVODCE Podobne jednoduše jako sumu bohužel není možné nadefinovat i ostatní vzorce, nebot jsou vetšinou podstatne složitejší. Proto tvurci Excelu vymysleli takzvaného pruvodce funkcemi, s jehož pomocí lze nadefinovat vzorec bez jediného klepnutí na klávesnici! Dejme tomu, že budete chtít zjistit prumerný pocet návštevníku obchodního domu za mesíc. K dispozici máte údaje za leden až kveten a výsledek budete chtít zobrazit pod hodnoty ve sloupci C. 1. Postavte se na bunku, ve které má být zobrazen výsledek (ve které bude vzorec).
.J..
2. Na panelunástrojuklepnetenatlacítkoVložitfunkci f,..
. 4~.
3. Excel zobrazí okno Vložitfunkci.V levé cásti okna jsou kategorie funkcí. Na jakou kategorii klepnete v levé cásti, taková odpovídající funkce se zobrazí v pravé cásti. 4. Vyberte konkrétní funkci (v tomto prípade kategorii Statistické a funkce PRUMER)a klepnete na tlacítko OK. Vzhledem k velkému množství funkcí, které Excel nabízí, nemusí být orientace v názvech funkcí snadná. Proto Excel po klepnutí na konkrétní funkci zobrazuje v dolní cásti okna strucnou nápovedu o významu vybrané funkce.
Eunl
V levé cásti pruvodce se zobrazuje kategorie funkcí.
1 ~,
T
6'
C Leden Únor Brezen Duben Kveten
1280 3650 2890 5460, 1901
.I
I
R
Pruvodce funkcemi
!'iázev funl
&
vše financní datum a cas matematické ~miTi" vyhledávací databáze
-t"ext
logické , informacní
J
PEARSON PERCENTIl PERCENTRANK PERMUTACE POCET POCET2
..J
POISSON PROB PRUMODCHVlKA
..:J QUAR TIl
..:J
PROMER(císlol;CísloZ;,..) Vrátí prumernou hodnotu (aritmetický prumer) argumentu. Argumenty mohou být císla nebo názvy, matice nebo odkazy, které obsahují císla. OK
L~dJ2
V pravé cásti jsou zobrazeny konkrétní funkce z kategorie, která byla zvolena vlevo.
Storno
5. Excel zobrazí další okno, ve kterém je treba zadat oblast, k níž se bude funkce vztahovat. Podobne jako oznacujete bunky do bloku, oznacte oblast bunek, na které se bude vzorec vztahovat.IOznacená oblastl(C2:C6) se objeví v okne. Oznacením oblasti bunek se tyto hodnoty "prenesou" do dialogu v pruvodci. Muže se stát, že zobrazené okno prekrývá bunky s daty. Okno proto mužete uchopit kdekoliv za jeho obsah a klasickým zpusobem levým tlacítkem
myšipremístitnajinoupozici.
127
6.
Potvrdte správnost údaju klepnutím na tlacítko OK.
Vytvorení vzorce prostrednictvím pruvodce je dokonceno. Podobným zpusobem mužete v Excelu vytvorit vzorec s libovolnou funkcí. Prumer je pomerne jednoduchá funkce, a proto jsme v bode 5 zadali pouze jednu oblast bunek, se kterou bude vzorec pocítat. Ne u všech funkcí probíhá zadávání oblastí tak snadno. Zejména financní funkce casto vyžadují zadání minimálne trí hodnot - nabízí tri dialogová okna. Tvorba vzorcu je jedním ze základních predpokladu pro zdárné zvládnutí Excelu. Proto si vzorce pred postupem do další kapitoly radeji opakovane vyzkoušejte.
PODMíNKA Pri práci s Excelem muže nastat prípad, ve kterém je treba, aby se vzorec sám rozhodl mezi dvema alternativami a podle toho vykonal urcitou funkci. K rešení podobných problému existuje v Excelu
logická
funkce
- podmínka.
--.'
A. '. .
~
definují.
li
.
.
.
~
B
1... c..
..
Jt
Príklad: Pokud bude prospech žáka menší nebo roven 3, pak at Excel do pripravené bunky napíše OK. Bude-Ii prospech horší než 3, at napíše skutecný prospech. "' . . Na tom to pn kl ad u SI z kuSlme ukaza,t Jak se po dmin ky
.
~.
5 ---g-t
D
L_ E
.
~:~il
prospech31
Vlastimil
1
Martin
2
Jindra
4
I
·
1. Klepnetena bunku,ve kterébudevyhodnocenípodmínky. 2. Klepnete na tlacítko Vložitfunkci
f-.
3. V okne Vložitfunkcizvolte typ funkce Logickéa následne v pravé cásti klepnete na podminovací funkci KDYž.Poté klepnete na OK. 4. Excel zobrazí okno, ve kterém je treba doplnit tri údaje. a. Podmínka - doplnte samotné znení podmínky.V našem prípade obsah bunky C3 musi být menší nebo roven 3 (C3<=3). b. Ano - napište, co požadujete provést, pokud výše uvedená podmínka bude splnena V našem prípade napište text OKumístený v uvozovkách. c. Ne - doplnte, co se má provést, pokud výše uvedená podmínka nebude splnena V našem prípade se vypíše obsah bunky C3.
Tvorba podmínky 1. ~.~.
11 ~, ~6
-r: 8.1
a ~
Žák Kamil Vlastimil Martin Jindra
c.
D
Definice samotné podmínky
E_,
I
fKDYŽ
prosPech3IJK";C3)
i
1 2 4
L-
9] 10~ 11. 121' 13 'TIJ
Podmínkale3<-3
-
AnoI.OK"
_ 3
Nele3
Vrátí jednu hodnotu, jestI!e je výsIed<em zadaného kritéria I-odnota PRA\IDA, a jinou výstedkem hodnota NEPRAVDA. Ne je hodnota o;rácená, je-I hodnota arg
-
Výsledek
. . :TI]
.~'
Definice vzorce ci požadavku, který se aktivuje v prípade splnení podmínky
'I,j - PRA\IDA
01(
01(
j
Definice vzorce ci požadavku, který se aktivuje v prípade nesplnení podmínky
Storno
5. Klepnete na tlacítko OK- podmínka je zadána a vyhodnocena. V bunce 03 by se mel objeví text OK.Jestliže zkusíte zmenit hodnotu v bunce C3 na císlo 4, pak se okamžite v bunce m objeví také císlo 4 - zacne pracovat druhá cást podmínky.
Uvedený príklad demonstroval použití podmínky v nejjednodušší podobe. Do dialogu Anonebe Ne lze zadat prakticky libovolnou funkci Excelu nebo uživatelem vytvorený vzorec. Dokonce jE možné vnorit do sebe i nekolik podmínek najednou (vytvorit podmínku v podmínce).
128
-
-
----
..
PODMíNENÉ FORMÁTOVÁNí Podmínená funkce nezbytne potrebovala ke své existenci bunku, ve které vyhodnocení podmínky probehlo. Excel nabízí ješte jednu formu podmínky - podmínené formátování. Na rozdíl od podmínené funkce je u podmíneného formátování výsledným efektem odlišnost ve formátu (vzhledu) bunky, nikoliv výsledná hodnota nebo text. Pokud napríklad v seznamu císelných bunek nekteré nevyhoví podmínce, Excel zabarví pozadí techto bunek cervene. 1. Oznacte do bloku oblast bunek, na které se podmínené formátování bude vztahovat.
2. V hlavní nabídceklepnetena Formát, Podmínené formátování ,.M'.x,:m:.:, "',"".; tam na položkuPodmínené formátování. ~!..podmínl
.
v
'
3 Exce I Ot evre Okno, ve kterem Ize . ' " ' h bd k t d tv
-
L
IHodnotabunky
~
~~
~ ~
Náhled forrnátu použitého
pros re mc lm rozevlraclc na I e nadefinovat vlastní podmínku.
pri splnenípodmínky:
--
4. Nadefinujte podmínku a poté klepnete
~~
~
..:J ftoooo
..:J Ije vetšínež
~.,.,...
~
~-~
ITW
'
erodat»
I
Q9straniL
I
OK
na tlacítkolFormát.1 . I
5. Tlacítko Formátotevrelo okno s možnostmi zmenit formát bunky - pozadí, ohranicení, písmo. Pokud bude splnena podmínka, zobrazí se bunka s takovými parametry, jaké nadefinujete v okne Formátbunek.Po definici formátu klepnete na OK. 6. V okne Podmínenéformátováníje již k dispozici náhled na nadefinovaný formát. Klepnutím na tlacítko OKpotvrdíte provedené operace a podmínené formátování se stane aktivním. Okno Podmínenéformátováníumožnuje nadefinovat pouze tri podmínky soucasne. Druhou a tretí podmínku aktivujete tlacítkem Pridat» v okne Podmínenéformátování.
KopíROVÁNí VZORCE TAŽENíM MYŠI Casto vzniká situace, kdy v tabulce existuje nekolik (vetšinou desítek) rádku a u každého je potreba vyrobit vzorec, napr. prumer. Vytváret tabulku s 200 položkami a zadávat napr. prumer u každého rádku zvlášt by bylo velmi nárocné. Excel proto nabízí možnost nakopírování prvního rádku v tabulce do ostatních rádku, pritom vždy prizpusobí hodnoty ve vzorcích potrebám konkrétního rádku. 1. Klepnete levým tlacítkem myši na bunku, kterou si prejete kopírovat. 2. Nastavte myš na ravý dolní roh bunky Ue zvýraznen malým cerným ctvereckem). Tvar myši se zmení na cerný krížek (viz obrázek). 3. Stisknete a držte levé tlacítko myši. ,4. Táhnete myší požadovaným smerem (v našem prípade dolu). Po stranách tažené oblasti Excel zobrazuje polopruhlednou cáru. 5. Na požadované pozici uvolnete levé tlacítko myši
-
vzorce budou nakopírovány. ,
NEKOLIK PRAVIDEL PRO ZADÁVÁNí VZORCU · Každý vzorec musí nutne zacínat znaménkem = (rovnítkem), jinak jej Excel nebere jako vzorec, ale jako textovou bunku. Pokud je vzorec tvoren pruvodcem, pak je = doplneno automaticky.
·
Vzorec musí být zapsán syntakticky správne, jinak Excel nedovolí ukoncit editaci bunky
(napr.nesmíchybetzávorka).
·
Promenné použité ve vzorci se mohou odkazovat na výsledky jiných vzorcu.
·
Ve vzorcích, které budou ve vetší míre rozkopírovány, se snažte používat co nejvíce
promenných,nikolivkonstant.Je to výhodnépro další prípadnéúpravy. ~-
129 --
--
-
-
ABSOLUTNí A RELATIVNí ADRESOVÁNí Aby mel excelový vzorec smysl, používá témer ve všech prípadech minimálne jeden odkaz na promennou bunku, napr. =CS*1,S. Jiste jste si pri kopírování bunek v predchozím odstavci všimli, že se spolu s každým rádkem mení i promenné (prizpusobují se rádku, na nemž práve jsou). Tato situace není vždy žádoucí. Proc? To ukazuje následující príklad.
Absolutní a relativní adresování ..A . ,
ri 2.
8
C
Žák Kamil
Prospech 15800
Vlastimil Martin Jindra
6
I
I
Koeficient
18960 12400 8900 0,9
Q
I
.E . 4
I.
..A
21
14220=C3*C8 O =C4*C9 O =C5*C10 O =C6*C11
.5' 6
t
J
Ižák
C
... L..P
I
E
...1
Prospech
Kamil Vlastimil Martin Jindra
15800 18960 12400 8900
Koeficient
14220 17064 11160 8010
=C3*$C$8 =C4*$C$8 =C5*$C$8 =C6*$C$8
0,9
V techto vzorcích je odkaz na bunku ve vzorci "zaaretovaný" znakem $. Díkytomu je možné vzorec kopírovat, ale odkaz na CS zustává nemenný.
Klasický rozkopírovaný vzorec zmení u každého rádku první I druhou hodnotu, což v tomto prípade není potreba. Požadujeme, aby se ve všech rádcích vzorec odkazoval na
Na obrázku vlevo jsou mesícní výdelky ctyr zamestnancu. Je treba odecíst dan, která se prubežne mení, proto je pod tabulkou zapsána formou koeficientu. Výsledek - násobek príjmu a koeficientu - je vypocten do sloupce D. Sloupec E zobrazuje, jak se vzorce kopírovaly. Pokud nadefinujete první rádek, vše je v porádku a výpocet je proveden.Jestliže kopírujete vzorec do druhého rádku a dalších, výsledkem bude nula. Chyba se stala v okamžiku, kdy se u druhého rádku (bunka D4) vzorec posunul z puvodního =C3*C8na =C4*C9.To by v normálním prípade bylo v porádku, ale nyní potrebujete, aby Excel hodnotu C8 neposouval. Je treba ji "zmrazit". Excel disponuje absolutním a relativním adresováním bunek. U relativního adresování se adresy (odkazy) na bunky mení podle toho, jak se mení samotná bunka - pokud bunku, na kterou jsou odkázány vzorce, presunete na jinou pozici, Excel na ni automaticky predefinuje i všechny odkazy. U absolutního adresování se adresa definuje bez možnosti jakékoliv pozdejší automatické zmeny. Absolutní adresování spocívá v pridání znaku $ pred prvek, který potrebujete absolutne adresovat. Má-Ii být absolutne adresován sloupec, pridá se $ pred sloupec ($C3),pokud rádek, pridá $ pred nej (C$3),jestliže rádek i sloupec - $C$3. Znak $ je možné do vzorce pridat také tak, že se ve vzorci nastavíte k adrese bunky, pred kterou si prejete znak vložit, a stisknete klávesu F4. Každé následující stisknutí klávesy F4 upravuje nastavení absolutní adresace odkazu na bunku. Celkem jsou k dispozici 4 stavy.
KopíROVÁNí BUNEK POMocí SCHRÁNKY Bunka nebo oznacená oblast bunek muže být nakopírována na tentýž nebo jiný list ci sešit. Ke kopírování bunek je nejvhodnejší použít schránku, která podobne jako ve Windows a Wordu disponuje na panelu nástroju tremi tlacítky. Do schránky lze vložit libovolnou tabulku a umístit ji na jakoukoliv pozici na jiném listu nebo v jiném sešitu. Upozornení: Excel má pri práci se schránkou ponekud odlišné vlastnosti, než jaké známe z Windows nebo Wordu. Informace se ze schránky mažou okamžite po jejich "odznacení" v tabulce klávesou Esc (po zrušení blikajícího oznacení).
1. Oznacte do bloku bunky, které budou kopírovány (vloženy do schránky). 2. Klepnete na tlacítko dvou za sebou jdoucích listu - tlacítko kopírovat (mužete rovnež použít klávesovou kombinaci Ctrl+C). Okolo oznacené oblasti zacne blikat tenká prerušovaná cára. Ta znázornuje, které bunky jsou umísteny ve schránce.
130 --
--
T 3. Prejdete na bunku, jež bude tvorit levý horní roh kopírované oblasti. Mužete prejít i na jiný list nebo do jiného souboru. 4. Na požadované poziciklepnete na tlacítkovložení ze schránky (je umísteno na panelu nástroju, hned vedle dvou papíru zprava). Mužete rovnež použít klávesovou kombinaci Ctrl+V. Bunky budou zkopírovány. Podobným zpusobem bunky presunete. Rozdíl u presunutí spocívá
v použitítlacítkanužekmístodvoupapíruv bode2. Presouváte-Iivšakdatav rámcijednoholistu, budevhodnejšípoužítnásledujícípostup.
PRESUN BUNEK V JEDNOM LISTU POMocí MYŠI Každou cást bunek oznacených do bloku je možné presunout na jinou pozici v tabulce. Pritom vazby a adresování vzorcu budou prizpusobeny novým pozicím bunek (vzorce zustanou nepoškozeny). .A
Presun bunek pomocí myši
[
1, 3 4 5 6 7. 8
~
Leden Unor' ,., Brezen Duben Kvete.o _
Aj
128Q 365u 289Q 5460 1901,
Blok oznacených bunek uchopte za nekterý z jeho okraju levým tlacítkem myši a táhnete požadovaným smerem. Na požadované pozici uvolnete levé tlacítko myši. blok bunek bude presunut.
B Leden Unor . Brezen Duben Kveten
I
c
I
DI
E
1280 3650 2890
r
5460'
j
1901
B 9 10. 11
CŠ:D9!
.
Jestliže pri presouvání bloku bunek v rámci jednoho listu podržíte stisknutou klávesu CTRL, budou bunky prekopírovány.
OPERACE S RÁDKY A SLOUPCI Vkládání prázdných rádku a sloupcu Pokud existuje vytvorená tabulka, do které je treba pridat nový (prázdný) rádek nebo sloupec, je postup následující: 1. Klepnete na záhlaví, resp. písmeno (v prípade sloupcu), nebo na císlo (v prípade rádku) takového sloupce/rádku, pred který potrebujete vložit prázdný sloupec/rádek - oznacte ho do bloku. 2. V hlavní nabídce Excelu zvolte položku Vložit. 3. V otevrené nabídce zvolte rádek/sloupec.V nabídce bude figurovat bud rádek, nebo
sloupec, podle toho, zda byl predtím oznacen rádek, nebo sloupec.
Odstranování rádku a sloupcu Každý sloupec nebo rádek mužete odstranit. Pozor, odstranením se rozumí skutecné odstranení rádku nebo sloupce, a nikoliv pouze hodnot, které sloupec nebo rádek obsahoval (jednalo by se pouze o vymazání). Po odstranení rádku nebo sloupce se do odstraneného rádku nebo sloupce umístí následující bunky v poradí - celá tabulka se o odstranenou cást posune.
IY[oá
~y... I
Rádek
I
..
2IoLI>ec List ~of. !!;onec stránky
f. Eunkce... Název ~~ár ~
Ob(ázek QbjekL 'I. Hypertextový odk;q..
CtMI<
ÚprAVY .-, &Pet:Vymazat cJ Qpakov&: Vymazat
Ctrk-Z Ctrl+Y
Ji, '!Y)IOOUt ~ ~oval:
CtrH-X Ctri+e
e!t~it;"~d\ Viaži!1ihaK
1. Oznacte do bloku rádek nebo sloupec, který je treba odstranit. 2. V hlavní nabídce Excelu klepnete na Úpravy. 3. V otevrené podnabídce zvolte Odstranit.
Odstreni; list eYe
Ctrl+/'
Nat\rodiL.
Ctri+H
Pr~~ na...
Ctri+G
131
