Transcript Podmíněné formátování

2
Ing. Jan Popelka, Ph.D.
odborný asistent
katedra informatiky a geoinformatiky
Univerzita Jana Evangelisty Purkyně v Ústí nad
Labem
email: [email protected]
WWW: http://most.ujep.cz/~popelka
MS Excel – 2. přednáška
 Výpočty
 Adresování buněk
 Pojmenování buněk
 Vzorce
 Transponování
 Funkce, Vnořené funkce
 Podmíněné formátování
Výpočty
Vzorce jsou matematické výrazy.
Vždy začínají znakem ’=’
Operátory:
 Aritmetické
+ (sčítání), - (odčítání), * (násobení),
/ (dělení) a ^ (umocnění)
Pozn. Pomocí mocniny lze zapsat i odmocninu!
15
21  211/15 v MS Excel =21^(1/15)
Výpočty
Operátory:
 Logické, relační
= (rovná se), <> (nerovná se), > (větší než),
< (menší než), >= (větší nebo rovno),
<= (menší nebo rovno)
Např. = 10 < 25 vrací hodnotu PRAVDA
= 10 = 25 vrací hodnotu NEPRAVDA
Vzorce
Operátory:
 Textové
& (spojení, zřetězení textových hodnot)
Např. ="Excel "&2000+7&" CZ"
vrací hodnotu „Excel 2007 CZ“
Vhodné pro připojování jednotek k výpočtům.
=15+12+1&" km"
vrací hodnotu 28 km.
Výpočty
Priorita operátorů: pořadí, ve kterém se operátory
vyhodnocují.
%
2. ^
3. *, /
4. +, 5. &
1.
Výpočty
Priorita operátorů
Příklad:
= 2*5%
= 5^2
= 5^2%
= (5^2)%
vrátí výsledek 0,1 neboli 2·0,05
vrátí výsledek 25 neboli 52
vrátí výsledek 1,03271242 tedy 50,02
(% má přednost před umocněním, takže
nejdříve je vyhodnocena část 2% jako 0,02 a
až pak umocnění)
vrátí výsledek 0,25 tedy 52 %
(uzávorkování změní pořadí priorit, nejdříve se
vypočte obsah závorek a pak teprve to, co je
vně)
Výpočty
Příklad:
= 3+4-6*3^2+2/2
vrátí výsledek -46
1. krok = 3+4-6*9+2/2
2. krok = 3+4-54+1
3. krok = -46
vypočte se mocnina
= ((3+(4-6*3))^2+2)/2
vrátí výsledek 61,5
1. krok
2. krok
3. krok
4. krok
5. krok
vypočte se násobení ve vnitřní závorce
= ((3+(4-18))^2+2)/2
= ((3+(-14))^2+2)/2
= ((-11)^2+2)/2
= (121+2)/2
= 123/2 = 61,5
vypočte se násobení a dělení
vypočte se sčítání a odčítání
vypočte se rozdíl ve vnitřní závorce
vypočte se součet ve druhé vnitřní závorce
vypočte se mocnina
vypočte se součet se vnější závorce
Výpočty
Příklad:
= 5*10000 Kč
MS Excel upozorní na chybu a odstraní
jednotku
= 5*"10 000 Kč"
vrátí výsledek 50000 (sice bez jednotky, ale
provede početním operaci)
= 5*10000 &" Kč"
vrátí výsledek 50000 Kč (provede početním
operaci a připojí text v uvozovkách)
= 5*10000
vrátí výsledek 50 000 Kč (pokud buňku se
vzorcem naformátujeme jako měnu
Domů - Číslo (Měna)
Adresování buněk
Na aktivním listu je buňka popsána souřadnicemi:
=A1
Na jiném listu stejného souboru je popsána názvem
listu a souřadnicemi:
=List1!A1
V jiném souboru je buňka adresována cestou k
souboru, názvem souboru v hranaté závorce, názvem
listu a souřadnicemi buňky:
='C:\Dokumenty\EXCEL\[cenik.xls]List6'!$A$1
Adresování buněk
Zápis vzorců
1. Celý vzorec lze zadat ručně (do buňky se zapíší
adresy buněk, čísla i operátory).
2. Zapisujeme pouze operátory (popř. čísla, konstanty)
a na buňky klikáme levým tlačítkem myši. Tento
postup automaticky vloží souřadnice buňky a není
nutné je vyhledávat.
Adresování buněk
Příklad:
Do buňky C1 chci vypočítat součet sousedních
buněk (A1+B1).
Mohu ručně zapsat vzorec ’= A1+B1’.
Nebo mohu použít myš.
1.
Zadám ’=’ (protože každý vzorec musí
začínat rovnítkem).
2.
Kliknu levým tlačítkem myši na buňku A1.
3.
Zadám ’+’.
4.
Kliknu levým tlačítkem myši na buňku B1.
5.
Ukončím zadávání vzorce klávesou Enter.
Adresování buněk
K dohledání vzorců lze použít grafické zobrazení MS
Excel.
Po dvojkliku levým tlačítkem myši na buňku se vzorcem
(alt. lze použít klávesu F2) se barevně zobrazí
buňky, na které se vzorec odkazuje.
Adresování buněk
Jedna buňka je popsána sloupcem a řádkem:
A1
Souvislá oblast buněk (sloupec, řádek, matice) je
popsána souřadnicemi levé horní a pravé dolní buňky
oddělenými ’:’ . Používá se u funkcí =SUMA(A1:C5)
A1:A10
sloupec
A1:D1
řádek
A1:C5
matice
Adresování buněk
Nesouvislá oblast buněk je popsána souřadnicemi levé
horní a pravé dolní buňky dílčích souvislých oblastí,
které jsou sjednoceny pomocí znaku ’;’ . Používá se u
funkcí =SUMA(A1:A6;C1:E1;C3:C6;E4:E6).
Adresování buněk
Celé sloupce jsou popsány písmeny sloupců
oddělenými ’:’ .
=SUMA(E:H)
sloupce E, F, G, H
Celé řádky jsou popsány čísly řádků oddělenými ’:’ .
=SUMA(1:10)
prvních deset řádků
Adresování buněk
Nesourodé pole řádků a sloupců je popsáno jako dílčí
řádky a sloupce sjednoceno ’;’ .
=SUMA(B:B;8:9)
sloupec B a řádky 8 a 9
Adresování buněk
Průnik oblastí je popsán pomocí mezery ’ ’ .
=SUMA(B:B 8:9)
průnik sloupce B a řádků 8 a 9
(buňky B8 a B9)
Kopírování vzorců
Než přepisovat jeden vzorec stále dokola, je vhodnější jej kopírovat.
Vzorec lze kopírovat do přes schránku (Ctrl + C), a pak vkládat
(Ctrl + V) .
Nebo lze kopírovat tažením myší.
Tažením myší s podržením levého
tlačítka za pravým dolní roh buňky
nebo dvojklikem levým tlačítkem
myši na pravý dolní roh buňky
(vyznačeno silným čtvercem) se
vzorec zkopíruje na požadované
řádky.
Kopírování vzorců
Příklad:
Zapíšeme jednoduchý vzorec pro součet
hodnot dvou buněk
= A1+B1
Pro další řádky jej nebudeme znovu
zapisovat, ale pouze jej zkopírujeme.
Tažením za pravý dolní roh buňky nebo
dvojklikem levým tlačítkem myši na
pravý dolní roh buňky se vzorec
zkopíruje na požadované řádky.
Adresování buněk
Absolutní adresa
Nemá-li se adresa buňky s kopírováním měnit.
Je stále na stejné pozici. Definuje se znakem
’$’.
Absolutně pevná pozice $A$4
Pevný pouze sloupec
$A4
Pevný pouze řádek
A$4
Adresování buněk
Relativní adresa
Má-li se adresa buňky s kopírováním měnit.
Její pozice je stále stejná vzhledem k cílové buňce.
S kopírováním se její pozice mění ve směru kopírování.
Adresování buněk
Relativní adresa
kopírování
vzorce
s relativními
adresami
kopírování
vzorce
s absolutními
adresami
Adresování buněk
kopírovat dolů
kopírovat doprava
Adresování buněk
kopírovat dolů
kopírovat doprava
Adresování buněk
Změna relativní a absolutní adresace buněk se provádí
klávesou F4.
Pořadí změn je následující:
A1 → $A$1 → A$1 → $A1 → A1
Pojmenování buněk
Pokud jsou hodnoty v tabulce popsány nadpisy řádků a
sloupců, lze je využít pro jejich pojmenování.
Při výpočtech pak není potřeba zapisovat adresy buněk,
ale pouze jejich názvy.
Název může mít nejvýše 255 znaků, nesmí začínat číslicí
a nemůže obsahovat mezery.
Pojmenování buněk
Oblast hodnot musí mít definované popisky. Vybereme oblast hodnot a
zvolíme:
Vzorce – Definované názvy – Vytvořit z výběru
Z nabídky zvolíme odkud bude
odpovídající název vzat.
V tomto případě z popisků sloupců
a řádků.
Pojmenování
buněk
Chceme-li definovat nový název, který se nikde v sešitu nevyskytuje,
použijeme:
Vzorce – Definované názvy – Definovat název
Nový název.
Lze upravit nebo zcela nově
definovat i rozsah vybrané
oblasti.
Pojmenování
buněk
Správce názvů slouží k zadávání, úpravě nebo odstraňování vytvořených
názvů: Vzorce – Definované názvy – Správce názvů
Vytvoření nového názvu
Úprava vytvořeného
názvu
Odstranění vytvořeného
názvu
Pojmenování buněk
Na buňky se pak odkazuje jejich názvy, nikoliv adresou.
Hodnota z měsíce Leden a z místa Východ je pak definována jako průnik
dvou oblastí (tedy odděleno mezerou).
Součet za Leden a Únor z míst Východ a Západ je definován vzorcem.
Celá tabulka byla nazvána Celkem.
Pojmenování buněk
K dohledání závislosti buněk lze použít volbu
Vzorce – Závislosti vzorců – Předchůdci
Zobrazí šipky ukazující směr závislosti.
Odstranění šipek: Vzorce – Závislosti vzorců – Odebrat šipky
Funkce
Aby nemusely být složité výpočty zapisovány složitými
vzorci, jsou v Excelu předdefinované funkce.
Funkcí je obrovské množství (přes 800).
Vybrané budou uvedeny v dalším průběhu kurzu.
Detailněji se jim věnuje i kurz Statistika v zimním
semestru II. ročníku.
Funkce - Vkládání funkce
Stejně jako vzorec musí i funkce začínat rovnítkem ’=’
1. Ruční zápis - zadáme
přímo název funkce
(pokud jej známe)
a její argumenty
(pokud je známe).
Příklad: =SUMA(B1:B6) funkce pro součet
Funkce - Vkládání funkce
Zvolíme ze seznamu funkcí Vzorce – Vložit funkci
Nebo vedle stavového řádku
Jsou uspořádány do
použijeme tlačítko fx.
13 kategorií.
2.
Funkce - Vkládání funkce
Příklad: Pomocí volby Vzorce – Vložit funkci vypočteme součet hodnot ve
sloupci B. Tato funkce má název SUMA a patří mezi Matematické
funkce.
Funkce - Vkládání funkce
Příklad: Každá funkce má své dialogové okno, kam se zadávají
argumenty funkce.
Dialogové okno funkce SUMA.
Název funkce
Stručný popis funkce
Vstup pro argumenty funkce
Detailní popis co zadat jako
argument funkce.
Výsledek funkce
Vypočítat funkci - do buňky se zapíše výsledek 75
Funkce - Vkládání funkce
Příklad: Vypočteme odmocninu ze součtu sloupce B. Funkce se nazývá
ODMOCNINA a patří mezi Matematické funkce.
Pokud nechcete hledat do
jaké skupiny funkce
patří, jsou všechny v
nabídce ’Vše’.
Funkce - Vkládání funkce
Příklad: Dialogové okno funkce ODMOCNINA.
Pokud není zcela jasné,
jaké hodnoty se zadávají
jako argumenty funkce
(a to se stává často) je k
dispozici nápověda k
funkci.
Nápověda obsahuje
většinou i příklad
použití funkce.
Bohužel je v některých příkladech nápověda stejně nesrozumitelná.
Často je to dáno nepřesným překladem do češtiny.
Funkce
V jedné buňce nemusí být jen jedna funkce.
Funkce lze vzájemně kombinovat podle potřeby.
Příklad:
= SUMA (25;26;27) + ODMOCNINA (28)
 25  26  27  28  83,2915
Funkce – Vnořené funkce
Vnoření funkce jsou funkce vložené jako argument jiné
funkce.
Příklad:
= ODMOCNINA(SUMA(25;26;27))
Vypočte sumu hodnot (25+26+27), a pak tento součet odmocní.
Výsledek je 8,83.
= SUMA(ODMOCNINA(25);ODMOCNINA(26);ODMOCNINA(27))
Vypočte odmocniny jednotlivých hodnot, a pak tyto odmocniny sečte.
Výsledek je 15,29.
Funkce – Maticové funkce
Výstupem některých funkcí je matice. Kompletní
výsledek získáme označením buňky s výsledkem spolu
s polem o rozměru výsledné matice a kombinací kláves
F2, Ctrl+Shift+Enter.
Příklad: funkce SOUČIN.MATIC
Funkce vypíše hodnotu 21. Po označení
pole 3x3 a stisknutí F2, Ctrl+Shift+Enter
se vypíše výsledná matice.
Podmíněné formátování
Vedle klasického formátování buněk
(viz 1. přednáška) lze využít i nástroje, který formátuje
buňky jen za určitých podmínek.
Uživatel sám stanovuje podmínky a určuje výsledný
formát.
Formátování zlepšuje čitelnost tabulek, umožňuje
zvýraznit data, kterým je třeba věnovat zvýšenou
pozornost.
Podmíněné formátování
Domů – Podmíněné formátování – Správa pravidel
Podmínek lze zadat více tlačítkem ’Nové pravidlo…’ .
Podmínky jsou vyhodnocovány podle zadaného pořadí.
Podmíněné formátování
Domů – Podmíněné formátování – Správa pravidel
Zvýraznění buněk s
vybranými hodnotami
se provede pomocí
pravidla ‚Formátovat
pouze buňky
obsahující‘.
Velmi nízké hodnoty
(menší než -1000)
budou zvýrazněny
žlutou výplní s tučným
a červeným písmem.
Formát se nastavuje
tlačítkem ‚Formát‘.
Podmíněné formátování
Domů – Podmíněné formátování – Správa pravidel
Zvýraznění buněk s
vybranými hodnotami
se provede pomocí
pravidla ‚Formátovat
pouze buňky
obsahující‘.
Záporné hodnoty
(menší než 0) budou
tučným a červeným
písmem.
Podmíněné formátování
Domů – Podmíněné formátování – Správa pravidel
Podmínky jsou vyhodnocovány podle zadaného pořadí. Pořadí podmínek
je důležité a lze jej měnit. Jakmile je buňka formátována jednou
podmínkou, nebude již formátována podmínkou další.
Ve správci lze pravidla i upravovat a odstraňovat.
Podmíněné formátování
Domů – Podmíněné formátování
původní tabulka
formátovaná tabulka
Formátování je omezené: nelze měnit font písma, velikost písma ani
formát čísla.
Lze však nastavit barvu písma a buňky, ohraničení buňky, podtržení
písma, řez písma.
Podmíněné formátování
Domů – Podmíněné formátování – Správa pravidel
Podmínky jsou vyhodnocovány podle zadaného pořadí. Pořadí podmínek
lze měnit.
Ve správci lze pravidla i upravovat a odstraňovat.
Podmíněné formátování
Formát – Podmíněné formátování
Odlišné formátování
nadprůměrných a
podprůměrných hodnot
pomocí dvou pravidel
‚Formátovat pouze
hodnoty nad nebo pod
průměrem‘.
Podmíněné formátování
Formát – Podmíněné formátování
původní tabulka
formátovaná tabulka
formátovaná tabulka
Průměr hodnot je -32. Zelené jsou nadprůměrné hodnoty, béžové
podprůměrné.
Výhodou je, že při zadání jiné hodnoty (namísto -2000 je zadáno 800) se
automaticky přepočte průměr a podmínka se vyhodnotí znovu.
Podmíněné formátování
Formát – Podmíněné formátování
Lze využívat i barevné
škály, datové řady nebo
ikony. Vše vyznačuje
hodnoty v definovaných
intervalech.
Pravidlo: ‚Formátovat
všechny buňky na
základě hodnot‘.
Podmíněné formátování
Formát – Podmíněné formátování
Pravidlo: ‚Formátovat
všechny buňky na
základě hodnot‘.
Aplikace ikon (3 barevné
šipky) pro uživatelem
nastavené rozmezí
hodnot.
více jak 0
-200 až 0
do -200
zelená
žlutá
červená
Podmíněné formátování
Formát – Podmíněné formátování
Pravidlo: ‚Formátovat
všechny buňky na
základě hodnot‘.
Aplikace dvoubarevné
škály (červená je
minimum a bílá
maximum).