OHGHQ ~QRU E H]HQ GXEHQ NY WHQ þhuyhq. SROROHWt

Vážení zákazníci, dovolujeme si Vás upozornit, že na tuto ukázku knihy se vztahují autorská práva, tzv. copyright. To znamená, že ukázka má sloužit výhradnì pro osobní potøebu potenciálního kupujícího (aby ètenáø vidìl, jakým zpùsobem je titul zpracován a mohl se také podle tohoto, jako jednoho z parametrù, rozhodnout, zda titul koupí èi ne). Z toho vyplývá, že není dovoleno tuto ukázku jakýmkoliv zpùsobem dále šíøit, veøejnì èi neveøejnì napø. umisováním na datová média, na jiné internetové stránky (ani prostøednictvím odkazù) apod. redakce nakladatelství BEN – technická literatura

[email protected]

Sluèování dat DATA ð Slouèit Tento pøíkaz používáme v pøípadì, že chceme zachytit jen podstatné údaje z rozsáhlých dat, které jsou zpracovány na více listech èi ve více souborech. Typickým pøíkladem jsou uzávìrky jednotlivých mìsícù. Každý mìsíc je zachycen na jednom listu. Listy jsou souèástí sešitu (souboru) PRODEJ.XLS. Soubor zachycuje prodej automobilù rùzných znaèek ve tøech prodejnách firmy. Naším úkolem je shrnout výsledky jednotlivých mìsícù do ètvrtletních a pololetního pøehledu. Samozøejmì bychom mohli takto zpracovat i roèní pøehled, ale to již nechám na uživateli, aby vyzkoušel svoji, právì získanou dovednost. Urèitì to pùjde. Schéma zachycující propojení jednotlivých listù souboru PRODEJ.XLS: OHGHQ

~QRU

E H]HQ

GXEHQ

þWYUWOHWt

NY WHQ

þHUYHQ

þWYUWOHWt

SROROHWt

Pøi vytváøení Slouèení dat musíme postupovat podle urèitých zásad: • Na jednom listì by mìla být jen jedna sluèovací tabulka. Touto tabulkou rozumíme výslednou tabulku, ve které jsou zpracovány údaje z více zdrojových tabulek, z více oblastí, ze kterých data sluèujeme. Sluèovací tabulky které jsou výsledkem sluèování jsou na listech 1. ètvrtletí, 2. ètvrtletí a 1. polotetí. • Zdrojová oblast jsou jednotlivé tabulky, které sluèujeme, ze kterých èerpáme. V našem pøípadì výsledky leden - èerven. Doporuèuji pojmenovat si listy názvy mìsícù. Získáte naprostý pøehled o rozmístìní dat a také okamžitì identifikujete listy, ve kterých vytvoøíte slouèení. • Na listì se sluèovací tabulkou by nemìly být další údaje, zejména ne zdrojové! Na následujícím obrázku jsou zachyceny tabulky, které chceme slouèit - List 1 (leden) až List 6 (èerven). Tyto nazýváme zdrojové. List 7 (1. ètvrtletí) pak zachycuje tabulku Pavel Kras: EXCEL pro pokroèilé - BEN technická literatura

7

?

Práce s daty

vzniklou slouèením ledna až bøezna, List 8 (2. ètvrtletí) slouèením dubna až èervna a List 9 (1. pololetí) zachycuje výsledky za první pololetí, to znamená, že vznikne slouèením tabulek za leden až èerven. Takto vzniklé tabulky nazýváme sluèovací. Pozor, na obrázku jsou zachyceny tabulky pro pøehlednost na jednom listu papíru, ale nezapomeòte - sluèovací tabulka se vytváøí vždy na novém, prázdném listì! Tabulky pro sluèování:

OHGHQ

/LVWS HMPHQXMWHQDOHGHQ

$

  SURGHMQD  SURGHMQD  SURGHMQD 

% )DYRULW   

& %0:   

' )LDW   

  SURGHMQD  SURGHMQD  SURGHMQD 

% )DYRULW   

& %0:   

  SURGHMQD  SURGHMQD  SURGHMQD 

% )DYRULW   

& %0:   

    

)DYRULW   

%

%0:   

)LDW

&   

$

)DYRULW   

%

%0:   

)LDW

  

( )RUG   

) $XGL   

' )LDW   

( $XGL

'

  

)RUG   

(

$XGL   

&   

'

)RUG   

(

$XGL   

  

& %0:   

)LDW

'   

$

  SURGHMQD  SURGHMQD  SURGHMQD 

% )DYRULW   

$

  SURGHMQD  SURGHMQD  SURGHMQD 

% )DYRULW   

  

)LDW

'   

    

)DYRULW

$   

%

%0:   

' )RUG

&

'

  

  

( $XGL

)

  

  

( )LDW   

) $XGL

  

þWYUWOHWt )LDW

Qi]Y\ iGN )

  

) $XGL

þHUYHQ

& %0:

/LVWS HMPHQXMWHQDþWYUWOHWt

)

( )RUG

NY WHQ

& %0:

/LVWS HMPHQXMWHQDþHUYHQ

)

SROROHWt

/LVWS HMPHQXMWHQDSROROHWt

    

  SURGHMQD  SURGHMQD  SURGHMQD 

% )DYRULW

/LVWS HMPHQXMWHQDNY WHQ

þWYUWOHWt

/LVWS HMPHQXMWHQDþWYUWOHWt

$

  

$

E H]HQ

/LVWS HMPHQXMWHQDE H]HQ

$

' )LDW

GXEHQ

/LVWS HMPHQXMWHQDGXEHQ

)

~QRU

/LVWS HMPHQXMWHQD~QRU

$

( $XGL

  

)RUG   

(

$XGL   

)

Qi]Y\VORXSF

]GURMRYpWDEXON\ VOXþRYDFtWDEXON\ R]QDþHQtPWpWREX N\P\ãtMHY\PH]HQDFtORYi REODVWMDNMHSRSViQRYS HGFKi]HMtFtPWH[WX

U každé tabulky jsou znázornìny adresy bunìk. Pøi jejich zpracovávání pøed sluèováním, si povšimnìte, že poèítaè je zapisuje ve tvaru absolutních adres, to je $A$2. Podobnì pøi zápisu listu, ze kterého je zrovna èerpáno, to znamená na který je provádìn odkaz, se používá zápis ve tvaru název listu a pak ihned následuje „!“. Práci ale máme ulehèenu tím, že pøi správné manipulaci myší vše za nás vykoná poèítaè.

8

Pavel Kras: EXCEL pro pokroèilé - BEN technická literatura

Práce s daty

?

Dialogové okno pro sluèování:

9QDãHP S tSDG PXVtPH ]DãNUWQRXW

+RUQt iGHN Postup práce: • Zkontrolujete, zda máte správnì vytvoøeny zdrojové oblasti a zda jsou listy, ve kterých se nacházejí správnì pojmenovány. • Postavte se do nového èistého listu, ve kterém chcete vytvoøit cílovou oblast, oznaète buòku, která bude tvoøit její levý horní roh. Od této buòky doprava dolù se vytvoøí, po dokonèení akce sluèovací tabulka. • Použijte pøíkazy DATA ð Slouèit, které najdete v Menu. Otevøe se dialogové okno Slouèit. V tomto oknì máte nabídku veškerých možností, které mùžete pøi sluèování využít. • Nejprve zvolte funkci, která pøi slouèení má být použita. Cvaknìte levým tlaèítkem myši na šipku v rámeèku Funkce a rozvine se Vám nabídka. Nejèastìji budete zøejmì používat souèet. Mezi dalšími deseti funkcemi jsou Prùmìr, Poèet hodnot, Maximum, Minimum. • Do ODKAZ zapište všechny potøebné oblasti a to nejlépe pomocí myši – cvaknìte do rámeèku Odkaz a když v nìm zaène pulzovat kurzor, mùžete s myší oznaèovat zdrojové oblasti. Otevøete si požadovaný list, zaènete lednem a odtáhnìte požadovanou oblast. Protože provádíme souhrn vyznaèením požadované oblasti v otevøeném potøebném listì, musí se objevit zápis ve tvaru leden!$B$1:$E$4 – vše bez mezer a v absolutních adresách. • Tlaèítkem PØIDAT pøenesete Vᚠodkaz do VŠECHNY ODKAZY. Po pøidání mùžete do odkazu zapsat pomocí výbìru myší další oblast. Bude to v listu únor, oblast únor!$B$1:$F$4. Opìt pøeneseme odkaz do rámeèku Všechny odkazy pomocí tlaèítka Pøidat. Tímto zpùsobem je možné vytvoøit až 255 odkazù. • Další sešity, které obsahující zdrojové oblasti, nemohou být aktivní. Nesmí být bìhem sluèování otevøeny. Pavel Kras: EXCEL pro pokroèilé - BEN technická literatura

9

?

Práce s daty

• Buòky obsahující text se chovají jako prázdné. To se netýká pouze bunìk s názvy, podle kterých sluèujete. • Je dobré mít buòky v jednotlivých listech na stejných adresách, výbìr je pak rychlý, prakticky automatický. Nemusíte znovu vyznaèovat zdrojovou oblast. Program vždy respektuje Vaši poslední volbu. • Pøíkazem NALISTOVAT mùžete použít i zdrojové oblasti ze zavøených sešitù. • Když jste skonèili výbìr oblastí, které chcete slouèit, musíte provést výbìr, podle èeho dojde ke slouèení. Ve Vašem pøípadì budete sluèovat podle znaèek automobilù. Znamená to, že jsou rozhodující názvy znaèek v øádcích 1. Proto, když provádíte výbìr mezi HORNÍ ØÁDEK ó LEVÝ SLOUPEC, použijte horní øádek. Tøídìní a slouèení pak probìhne podle názvù znaèek automobilù. • PROPOJENÍ NA ZDROJOVÁ DATA – umožní trvalé pøenášení zmìn ve zdrojových souborech do cílové oblasti. Jestliže zmìníte èísla v nìkteré zdrojové oblasti, okamžitì se zmìny pøenesou do cílové oblasti. Musíte ovšem provést úpravu rámeèku zaškrtnutím u pokynu Vytvoøit propojení na zdrojová data. Tato úprava se provede cvaknutím myší do rámeèku. Stejným zpùsobem se zaškrtnutí odstraní. Je dobøe se rozhodnout, zda je pro Vás výhodnìjší aktivace tohoto pøíkazu, nebo jeho nevyužití. Z pøíkladu, který je probírán, je patrné, že se neslouèí položky, které se nejmenují stejnì. Nemusíte proto úzkostlivì dbát na dodržení poøadí, ani na poèet položek. Poèítaè si cílovou oblast srovná sám. Musíte mít dostateèný prostor pro cílovou oblast. Jiné slouèení, napøíklad výpoèet prùmìru, si zkuste na nový list. Postavíte-li se znova do cílové tabulky a dáte pøíkaz DATA ð Slouèit, vše co bylo v tomto dialogovém oknì zapsáno, se opìt objeví. Pak pouze staèí klepnout OK a tabulka se aktualizuje, nebo mùžete provést opravy, napøíklad odstranìním nìkterých zdrojových oblastí. To je velice jednoduché. Název oblasti, které se chcete zbavit, v rámeèku Všechny odkazy myší namodøete a po stisknutí tlaèítka Odstranit již v seznamu oblast nebude. Jestliže zvolím PROPOJENÍ NA ZDROJOVÁ DATA nemusím aktualizovat, ale musím rozvážit zda je to pro mì takový zpùsob práce výhodný. Možné pøíèiny problémù: • jste na zdrojovém listì a chcete na nìm vytvoøit cílovou oblast, • nezaškrtli jste HORNÍ ØÁDEK ó LEVÝ SLOUPEC, nevybrali jste si ani jedno z tìchto políèek, • máte špatnì popsané názvy dat, která sluèujete.

10

Pavel Kras: EXCEL pro pokroèilé - BEN technická literatura

?

Práce s daty

Seznam Základní seznam pøipravený k dalšímu zpracování: 1i]Y\ SURP QQêFK XPtVW QêFK

YSUYQtP iGNXY\WYi HMtKODYLþNX

3 HKOHGSURGHMHYURFH P VtF

SRþHWNV

FHQD

OHGHQ



URN

6 HOHN W

ILUPD





FHQDFHON SURGHMFH 

. DSU

OHGHQ



=76







. DSU

~QRU



=76







~QRU



$ OH[







9 RGLþ N D $ GDP

~QRU



6 HOHN W







E H] HQ



$ OH[







9 RGLþ N D

E H] HQ



$ OH[







9 RGLþ N D

E H] HQ



6 HOHN W







$ GDP

OHGHQ



=76







OHGHQ



=76







. DSU

~QRU



$ OH[







9 RGLþ N D

~QRU



6 HOHN W







$ GDP

~QRU



$ OH[







9 RGLþ N D

~QRU



$ OH[







$ GDP

$ GDP

. DSU

E H] HQ



6 HOHN W







9 RGLþ N D

E H] HQ



=76







$ GDP

E H] HQ



=76







$ GDP

3RORåND )LHOGV

9 WD 5HFRUG

• Hlavièka musí být provedena odlišným typem písma. • V seznamu nesmí být prázdný øádek nebo sloupec. • Pøi práci se seznamem musíte stát v nìkteré z jeho bunìk. • K oddìlení bunìk èi øádkù nepoužívejte prázdných øádek nebo èárkovaných èar. Jestliže chcete dosáhnout grafické úpravy, jistì Vás nezklame automatický formát. Lépe je ale formátovat až na závìr práce. Seznam je jedním ze zpùsobù ukládání dat. Je to série øádkù s daty stejného typu. Je zamìnitelný s databázovými soubory, jak je mùžete znát napøíklad z programù vytvoøePavel Kras: EXCEL pro pokroèilé - BEN technická literatura

11

?

Práce s daty

ných ve FoxPro. V Excelu si mùžeme tyto databázové soubory otevøít a pracovat s nimi. Po ukonèení práce je mùžeme opìt vrátit zpìt i se zmìnami, které jsme provedli. Seznamy také pøebírají veškeré výhody, které databáze pøinášejí. To je rychlé zpracování velkého množství dat, rychlé vyhledávání, tøídìní a zpracování podle urèitých kritérií, vèetnì vytváøení nejrùznìjších druhù zpráv. Abychom mohli s daty v tabulce pracovat jako se seznamem, musíme dodržet následující zásady. Na první pohled jich je hodnì, ale jsou logické a urèitì Vám uspoøádání dat do seznamu potíže dìlat nebude: Velikost a umístìní seznamu: • V jedné tabulce mìjte vždy jen jeden seznam. • Ponechte vždy alespoò jeden øádek èi sloupec volný mezi seznamem a ostatními daty v listì. • Neukládejte dùležitá data vlevo nebo vpravo od seznamu. Pøi práci se seznamem mùžete o nì pøijít. • Maximální velikost seznamu je shodná s rozsahem tabulky – 16384 øádkù a 256 sloupcù. Názvy sloupcù: • Pro názvy sloupcù (promìnných), používejte vždy první øádek v tabulce. Nemùžete využívat napøíklad levý okraj tabulky. • Vytvoøená „hlavièka“ musí být zobrazena odlišnì než ostatní data v seznamu. Nejlepší a nejjednodušší je použít tuèné písmo. Tím si poèítaè hlavièku identifikuje. • Nesmíte mít v seznamu prázdné øádky (prázdné buòky ano). Pozor – ani hlavièka nesmí být oddìlena prázdným øádkem. • Názvy sloupcù, stejnì jako ostatní buòky, mohou obsahovat jen 255 znakù. Mùžete použít zalomení textu. Øádky a sloupce: • V jednom sloupci mohou být data jen stejného typu. Nemùžete mít do poloviny sloupce napøíklad text a pak pokraèovat èísly. • Nevkládejte mezery na zaèátek bunìk - to znemožní správné øazení a vyhledávání – pozor, je to dost zákeøná chyba. I nevìdomky vložená

12

Pavel Kras: EXCEL pro pokroèilé - BEN technická literatura

?

Práce s daty

mezera, která se na obrazovce vlastnì neprojeví, je nìkde v pamìti zakódována jako èíslo 32 v binárním kódu. • V jednom sloupci používejte vždy stejný formát bunìk. Vyjma odlišení hlavièky – viz výše. • Nezáleží na použití velkých a malých písmen – pouze pøi aktivizaci pøíkazu Rozlišovat velká a malá písmena bude Excel velká a malá písmena respektovat. Doporuèuji radìji tento bìžnì pøíkaz nevyužívat. • Pøi øazení a vyhledávání jsou rozhodující pro práci Excelu výsledky, ne vzorce. Seznam pracuje vždy s celým øádkem (vìtou). Je tøeba tento požadavek respektovat za všech okolností! Jestliže pøesouváte, mažete, filtruje èi jinak zpracováváte data, musíte si uvìdomit, že øádek tvoøí nedílný celek. V následující ukázce jsou ponechány okraje bunìk. Tím si mùžete ovìøit ukládání rùzných typù dat v buòce. Také je jasné, že celý øádek tvoøí logický celek. Ke Karasovi patøí konkrétní jméno, v našem pøípadì Josef. Také jeho narození, èi další údaje nemùžu libovolnì zamìòovat. Jinak by se již nejednalo o naši urèitou osobu. Ukázka další tabulky provedené jako seznam: MPpQR

S tMPHQt

QDUR]HQ

-RVHI -LQG LFK -DURPtU

.DUDV 3WiþQtN +XEiOHN

 3UDKD  .ODWRY\  .UXSND

E\GOLãW

WHOHIRQ

  

Práce se seznamem jako s databází DATA ð Formuláø Tímto pøíkazem vyvoláme na obrazovce dialogové okno Seznam formuláøe dat. Pøesnì vystihuje povahu seznamu a jeho pøíbuznost s databází. Názvy položek (sloupcù) dostanou rámeèky, ve kterých se objevují data, která k sobì patøí a jsou tedy ve spoleèném øádku. Pracujeme vždy s celým øádkem. Data která nezadáváme, ale která vznikají výpoètem, jsou nepøístupná. Nemùžeme je ovlivòovat z klávesnice. Tento zpùsob práce je dobrý zejména pro seznámení se s principy databázového zpracování a pro orientaci v datech na obrazovce. Pavel Kras: EXCEL pro pokroèilé - BEN technická literatura

13

?

Práce s daty

Dialogové okno Seznam, umožòující procházení databází po vìtách, øádcích:

ýtVOR]i]QDPX

7ODþtWND



=iKODYt VORXSF SRORåHN

Ä1HS tVWXSQê³~GDM

iGHNY WD ]HVH]QDPX UR]ORåHQiSRMHGQRWOLYêFK ~GDMtFK

Postavíme se do zpracovávaného seznamu a použijeme pøíkaz DATA ð Formuláø. Formuláø se objeví na obrazovce a zobrazuje seznam ve tvaru øádkù (vìt). Jeho obsluha je opìt intuitivní. Mezi jednotlivými vìtami se pohybujeme tlaèítky DALŠÍ a PØEDCHOZÍ. Mùžeme øádky pomocí tlaèítek Nový a Odstranit i vkládat a odstraòovat. Vložení nového øádku nám umožní aktivace tlaèítka Nový. Odstranìní právì aktivního øádku provedeme stiskem tlaèítka Odstranit. Zajímavý je pøíkaz KRITÉRIA. Jestliže dosadím napøíklad prodejce KAPR a zvolím tlaèítko DALŠÍ, budu se pohybovat jen v tìch vìtách, které obsahují prodejce Kapra. Nastavím-li cenu > 7900, pak se bude provádìt výbìr podle zadaného kritéria. V buòkách, které nejsou chránìny, èi vypoèítávány, mùžu provádìt zásahy. Obsahuje-li buòka napøíklad vzorec, formuláø mì do této buòky nepustí. Provedené zmìny je možné potlaèit tlaèítkem OBNOVIT. Kritéria mùžeme stanovit u více položek souèasnì. Mají mezi sebou pak vztah logického souèinu - pro vyhledávanou vìtu platí jak podmínka první, tak i podmínka druhá, pøípadnì se uplatòují i další. Mezi daty ve vìtì (v jednotlivých rámeècích) se mùžeme pohybovat pomocí myši, ale rychlejší je obsluha z klávesnice – tabulátorem. Jestliže vymažu øádek (vìtu), posune se celý zbytek seznamu, všechny vìty, automaticky nahoru.

14

Pavel Kras: EXCEL pro pokroèilé - BEN technická literatura