načítání...
nákupní košík
Košík

je prázdný
a
b

Kniha: Microsoft Excel pro techniky a inženýry - Pavel Simr; Jiří Barilla

Microsoft Excel pro techniky a inženýry
-15%
sleva

Kniha: Microsoft Excel pro techniky a inženýry
Autor: ;

Znalost Excelu a jeho pokročilých funkcí je vyžadována téměř na všech technických a ekonomických pozicích. Chcete-li i vy nezaspat dobu a naučit se s Excelem 2007 pracovat na úrovni ... (celý popis)
337
Kniha teď bohužel není dostupná.


»hlídat dostupnost


hodnoceni - 70.3%hodnoceni - 70.3%hodnoceni - 70.3%hodnoceni - 70.3%hodnoceni - 70.3% 100%   celkové hodnocení
1 hodnocení + 0 recenzí

Specifikace
Nakladatelství: Computer press
Médium / forma: Tištěná kniha
Rok vydání: 2008-12-18
Počet stran: 368
Rozměr: 167 x 225 mm
Úprava: 366 stran : ilustrace
Vydání: Vyd. 1.
Vazba: brožovaná lepená
ISBN: 9788025124215
EAN: 9788025124215
Ukázka: » zobrazit ukázku
Popis

Znalost Excelu a jeho pokročilých funkcí je vyžadována téměř na všech technických a ekonomických pozicích. Chcete-li i vy nezaspat dobu a naučit se s Excelem 2007 pracovat na úrovni technického profesionála, popřípadě se připravit na výběrová řízení, je pro vás v této knize připraveno obrovské množství rad, tipů a zpracovaných hotových řešení přímo z technické praxe. Kniha je určena zejména technikům, kteří chtějí Excel využívat nejen pro tvorbu databázových seznamů, ale také pomocí Excelu provádět technické výpočty a statistické zpracování dat. Obsahuje kapitoly, které mohou využít i čtenáři, kteří nejsou technicky zaměřeni (například manažeři). Autoři se v knize mimo jiné věnují následujícím tématům: - Funkce a analytické nástroje, které jsou dále využívány při řešení úloh - Tvorba technických grafů a jejich formátování - Výpočty povrchů, objemů a hmotnosti technických těles - Evidence technických dat, zakázek a dokumentace - Statistické zpracování naměřených dat - Využití matematických modelů v technické praxi - Matematické modelování výrobních procesů a přepravy zboží - Optimalizace výroby, přemísťování objektů a dalších oblastí Na adrese http://knihy.cpress.cz/K1172 navíc naleznete ukázky řešených příkladů v knize. O autorech: Jiří Barilla pracuje jako vysokoškolský učitel na Katedře informatiky Přírodovědecké fakulty Univerzity J. E. Purkyně v Ústí nad Labem a jako konzultant pro firmu blue.point Solutions, s. r. o. Je autorem a spoluautorem knihy, skript a řady odborných článků zabývajících se podobnou problematikou a Microsoft Excelem. Pavel Simr se zaměřuje na administraci počítačových sítí, bezdrátové technologie, moderní aplikační programy a operační systémy. Je manažerem střediska ECDL Přírodovědecké fakulty UJEP, akreditovaným testerem ECDL a držitelem certifikátů CCNA.

Předmětná hesla
Související tituly dle názvu:
Microsoft Excel pro techniky a inženýry Microsoft Excel pro techniky a inženýry
Barilla Jiří, Simr Pavel
Cena: 199 Kč
Microsoft Excel 2013 Podrobná uživatelská příručka Microsoft Excel 2013 Podrobná uživatelská příručka
Barilla Jiří, Simr Pavel, Sýkorová Květuše
Cena: 249 Kč
Microsoft Excel 2016 Podrobná uživatelská příručka Microsoft Excel 2016 Podrobná uživatelská příručka
Barilla Jiří, Simr Pavel, Sýkorová Květuše
Cena: 269 Kč
Microsoft Excel 2016 Podrobná uživatelská příručka Microsoft Excel 2016 Podrobná uživatelská příručka
Sýkorová Květuše, Simr Pavel, Barilla Jiří
Cena: 424 Kč
Microsoft Excel 2010 Microsoft Excel 2010
Barilla Jiří, Simr Pavel, Sýkorová Květuše
Cena: 249 Kč
Recenze a komentáře k titulu
Zatím žádné recenze.


Ukázka / obsah
Přepis ukázky

4

Evidence

technických dat

V té to ka pi to le:

Evidence majetku

Evidence zakázek

Evidence technické dokumentace


136 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat Povinnost evidovat různé druhy dat má každý podnikatelský subjekt. Dříve se prováděla evidence v různých knihách nebo na evidenčních kartách. Dnes už se převážně k evidenci využívají počítače. V současné době řeší firmy evidenci dat dvěma základními způsoby: „ Hotovými programy od softwarových firem. „ Vlastními seznamy vytvořenými v Excelu. Hotové programy si firmy zpravidla kupují nebo nechávají vyhotovit na zakázku. Jejich výhodou je to, že firma dostane hotový program, k jehož užívání budou příslušní zaměstnanci zaškoleni. Nevýhodou je: „ poměrně vysoká pořizovací cena, „ závislost na tvůrci programu při potřebě nějaké změny v systému. Vlastní seznamy vytvořené v Excelu se používají v malých a středních firmách. Jejich výhodou je, že: „ nestojí firmu žádné peníze, „ tvůrcem seznamu je pracovník firmy, který zná dobře problematiku, „ seznam si můžou pracovníci firmy upravovat dle potřeby. Častou situací je, že databázové soubory mají na výstupu soubory v Excelu a dokážou i tyto soubory zpracovat. To má velkou výhodu v tom, že svá data můžeme zařadit do centrální databázové evidence, anebo si určitou skupinu dat z centrální databázové evidence analyzovat v Excelu. Pro efektivní využití databázových funkcí v Excelu je nezbytné vytvářet tabulky ve formě seznamu, ve kterých: „ v prvním řádku seznamu budou názvy polí (sloupců), „ v dalších řádcích budou jednotlivé záznamy (položky seznamu). Než začneme vytvářet tabulku v Excelu, musíme si dobře rozmyslet, jaké informace chceme v naší evidenci sledovat. Je to individuální záležitost. Někomu stačí několik základních informací a někdo chce mít informací co nejvíce. Je potřeba si uvědomit, že velké množství polí (sloupců tabulky) způsobuje to, že: „ tabulka je méně přehledná, „ potřebujeme více času na její aktualizaci (zadávání dat). Excel má tu výhodu, že: „ Když v průběhu práce se seznamem zjistíme, že některá pole (sloupce) jsou pro nás zby

tečná, můžeme je jednoduše z tabulky odstranit. „ Když v průběhu práce se seznamem zjistíme, že některá pole nám v seznamu chybí,

můžeme je jednoduše do tabulky přidat. V této kapitole se naučíme vytvářet jednoduché seznamy dat, které pro svoji práci potřebujeme. Seznamy se naučíme vytvářet na jednoduchých modelových praktických příkladech, které budou obsahovat pouze nezbytné množství polí a položek (záznamů). Byly vybrány tři základní oblasti, které se v praxi často vyskytují:


137

1. evidence majetku, 2. evidence zakázek, 3. evidence technické dokumentace. U těchto seznamů budeme chtít: „ vyhledávat potřebné údaje, „ seřazovat záznamy podle určitého pole (sloupce), „ vybírat záznamy na základě určitého kritéria, „ vytvářet souhrny, „ provádět různé výpočty. K těmto základním požadavkům můžeme přidat celou řadu dalších. Evidence majetku Evidence majetku je jednou z nejčastěji používaných evidencí, protože každá firma vlastní nějaký majetek. Metodický postup vytvoření evidence formou tabulky (seznamu) v Excelu si ukážeme na jednoduchém příkladě. Tento příklad poslouží jako návod pro vytváření evidencí a získávání potřebných informací z této evidence. Ukážeme si na něm využití některých funkcí pro práci s databázovými seznamy. Vzorový příklad vytvoření evidence majetku Máme vytvořit tabulku v Excelu (viz obrázek 4.1), pomocí které budeme evidovat majetek firmy. Rozhodli jsme se, že chceme sledovat tato pole seznamu: „ Inventární číslo, „ Název majetku, „ Typ (V – pro výrobu, D – doprava, K – kancelářská technika, X – ostatní), „ Provoz, „ Středisko, „ Pořizovací cena (v Kč), „ Zůstatková cena (v Kč), „ Datum zařazení.

Tip: Před zadáváním číselných údajů do tabulky je výhodné použít funkci pro ověřování vstupních dat,

která nám zabrání zadávat data ve špatném formátu a mimo požadovaný rozsah. Ověření vstupních číselných dat provedeme tak, že: 1. Označíme oblast buněk F3:G23. 2. Zvolíme kartu Data.

Evidence majetku


138 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat 3. Ve skupině Datové nástroje klepneme na

položku Ověření dat. 4. V dialogu Ověření dat volíme Nastavení

(viz obrázek 4.2). 5. V rozbalovací nabídce Povolit volíme Dese

tinné číslo. 6. V rozbalovací nabídce Rozsah volíme je

větší než nebo rovno. 7. Do okna Minimum zapíšeme nulu. 8. Nastavení potvrdíme stiskem tlačítka

OK. Ověření vstupních datových dat provedeme tak, že: 1. Označíme oblast buněk H3:H23. 2. Zvolíme kartu Data. 3. Ve skupině Datové nástroje klepneme na položku Ověření dat. 4. V dialogu Ověření dat volíme Nastavení (viz obrázek 4.3). 5. V rozbalovací nabídce Povolit volíme Datum. 6. V rozbalovací nabídce Rozsah volíme je větší než.

Obrázek 4.1 Tabulka evidence majetku

Obrázek 4.2 Dialog Ověření dat


139

7. Do okna Počáteční datum zapíšeme

1.1.1997.

8. Nastavení potvrdíme stisknutím tlačítka

OK.

Poznámky:

„ Oblast buněk A3:A23 musíme zformáto

vat jako text, jinak bychom nemohli zapsat

číslo, které začíná nulou.

„ U databázových seznamů předpokládá

me, že nám nezáleží na pořadí, v jakém

zadáváme jednotlivé záznamy, a že data

bázový seznam bude seřazen podle urči

tého pole (sloupce).

„ Pokud nám záleží na pořadí, v jakém zadáváme záznamy, musíme přidat další pole (např. pořa

dové číslo), do kterého budeme zapisovat pořadové číslo záznamu. To nám umožní kdykoli seřa

dit záznamy podle pořadí, v jakém byly zadávány. Vyhledávání údajů v databázovém seznamu V databázovém seznamu můžeme vyhledat libovolný záznam tak, že zadáme vzorový údaj z některého jeho pole. Například budeme chtít vyhledat záznam o monitoru z provozu D. Nejjednodušší způsob vyhledávání je pomocí příkazu Najít tak, že:

Obrázek 4.3 Dialog Ověření dat

Obrázek 4.4 Dialog Najít a nahradit

Evidence majetku


140 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat 1. Označíme buňku A3. 2. Zvolíme kartu Domů. 3. Ve skupině Úpravy klepneme na položku Najít a vybrat. 4. V rozbalovací nabídce zvolíme: Najít. 5. V dialogu Najít a nahradit (viz obrázek 4.4) zvolíme Najít. 6. Do okna Najít zapíšeme text MONITOR. 7. V rozbalovací nabídce Hledat volíme Po sloupcích. 8. Klepneme na tlačítko Najít další. Na obrázku 4.4 vidíme, že jsme sice našli záznam s názvem monitor, ale tento monitor nepatří do provozu D. Pro vyhledání dalšího záznamu s tímto názvem klepneme znovu na tlačítko Najít další.

Tip: Pro rychlejší vyhledávání v databázovém seznamu je výhodné označit první buňku ve sloupci,

podle kterého vyhledáváme (A3 ve sloupci Název majetku) a v dialogu Najít a nahradit v rozbalovací

nabídce Hledat zvolit Po sloupcích. Seřazení údajů v databázovém seznamu Databázový seznam zpravidla chceme mít seřazený podle určitého pole tak, abychom se v něm mohli dobře orientovat. Ukážeme si seřazení seznamu v tabulce na obrázku 4.1 podle pole (sloupce) Název majetku. Budeme chtít mít seznam seřazený vzestupně podle abecedy (viz obrázek 4.6). Seznam seřadíme tak, že: 1. Označíme oblast buněk, kterou chceme seřadit: A2:H23. 2. Zvolíme kartu Data. 3. Ve skupině Seřadit a filtrovat klepneme na položku Seřadit. 4. V dialogu Seřadit (viz obrázek 4.5):

„ Označíme políčko Data obsahují záhlaví.

„ V rozbalovací nabídce Seřadit podle volíme Název majetku.

Obrázek 4.5 Dialog Seřadit


141

„ V rozbalovací nabídce Řazení volíme Hodnoty.

„ V rozbalovací nabídce Pořadí volíme A až Z. Po potvrzení zadaných údajů dostaneme seřazený seznam na obrázku 4.6.

Obrázek 4.6 Seřazená tabulka evidence majetku

Důležité: Nesmíme zapomenout označit políčko Data obsahují záhlaví, protože jinak se bude seznam

setřiďovat i se záhlavím.

Vytváření souhrnů Důležitou informací může být pro nás podíl jednotlivých provozů a středisek na celkové ceně majetku. Tuto informaci můžeme snadno získat pomocí souhrnů. Před vytvořením souhrnů musíme databázový seznam seřadit podle polí, pro která se mají souhrny vypočítat. Pro vytvoření souhrnů použijeme tabulku na obrázku 4.1, kterou nejdříve seřadíme podle polí Provoz a Středisko tak, že: 1. Označíme oblast buněk, kterou chceme seřadit: A2:H23. 2. Zvolíme kartu Data. 3. Ve skupině Seřadit a filtrovat klepneme na položku Seřadit. 4. V dialogu Seřadit (viz obrázek 4.7):

„ Označíme políčko: Data obsahují záhlaví.

„ V rozbalovací nabídce Seřadit podle volíme Provoz.

Evidence majetku


142 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat

„ V rozbalovací nabídce Řazení volíme Hodnoty.

„ V rozbalovací nabídce Pořadí volíme A až Z.

„ Klepneme na tlačítko Přidat úroveň.

„ V rozbalovací nabídce Seřadit podle volíme Středisko.

Obrázek 4.7 Dialog Seřadit

Po potvrzení zadaných údajů dostaneme seřazený seznam na obrázku 4.8.

Obrázek 4.8 Seřazena tabulka evidence majetku

Nejdříve vytvoříme jednodušší souhrn podle pole Provoz tak, že: 1. Označíme kteroukoli buňku (např. A2) v seřazené tabulce na obrázku 4.8. 2. Zvolíme kartu Data. 3. Ve skupině Osnova klepneme na položku Souhrn. 4. V dialogu Souhrny (viz obrázek 4.9):

„ V rozbalovací nabídce U každé změny ve sloupci volíme Provoz.

„ V rozbalovací nabídce Použít funkci volíme Součet.


143

„ V okně Přidat souhrn do sloupce označíme políčka

Pořizovací cena a Zůstatková cena.

„ V dolní části dialogu označíme políčka Nahradit aktu

ální souhrny a Celkový souhrn pod daty.

Po potvrzení zadaných údajů dostaneme seznam se souhrny na obrázku 4.10. Interpretace výsledků Z tabulky na obrázku 4.10 vidíme, že: „ V řádku 5 (Celkem z A) je uveden celkový součet z pořizovací ceny za provoz A (556 744 Kč)

a ze zůstatkové ceny (0 Kč). „ V řádku 11 (Celkem z B) je uveden celkový součet za provoz B z pořizovací ceny

(538 843 Kč) a ze zůstatkové ceny (141 983,93 Kč). „ V řádku 20 (Celkem z C) je uveden celkový součet za provoz C z pořizovací ceny

(572 212 Kč) a ze zůstatkové ceny (81 739,07 Kč).

Obrázek 4.9 Dialog Souhrny

Obrázek 4.10 Tabulka evidence majetku se souhrny

Evidence majetku


144 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat „ V řádku 27 (Celkem z D) je uveden celkový součet za

provoz D z pořizovací ceny (1 646 063 Kč) a ze zůstatko

vé ceny (942 146,66 Kč). „ V řádku 28 (Celkový součet) je uveden celkový součet

za všechny provozy z pořizovací ceny (3 313 862 Kč) a ze

zůstatkové ceny (1 165 869,66 Kč). Nyní do tabulky na obrázku 4.10 přidáme ještě jeden souhrn podle pole Středisko tak, že: 1. Dříve popsaným postupem si zobrazíme dialog Souhr

ny. 2. V rozbalovací nabídce U každé změny ve sloupci volíme

Středisko. 3. V rozbalovací nabídce Použít funkci volíme Součet. 4. V okně Přidat souhrn do sloupce označíme políčka Pořizovací cena a Zůstatková cena. 5. V dolní části dialogu zrušíme označení políčka Nahradit aktuální souhrny (viz obrázek

4.11). Po potvrzení zadaných údajů dostaneme seznam se souhrny na obrázku 4.12.

Obrázek 4.12 Tabulka evidence majetku se souhrny

V tabulce na obrázku 4.12 máme i součty za jednotlivá střediska (viz např. Celkem z A02).

Tip: V rozbalovací nabídce Použít funkci můžeme volit kromě funkce Součet i další funkce: Počet, Prů

měr, Maximum, Minimum, Součin, Počet čísel, Směrodatná odchylka, Odhad rozptylu a Rozptyl.

Poznámka: Souhrn, který byl vytvořen (viz obrázek 4.12), můžeme zrušit a můžeme uvést tabulku

do původního stavu na obrázku 4.8 tak, že v dialogu Souhrny (viz obrázek 4.11) klepneme na polož

ku Odebrat vše.

Obrázek 4.11 Dialog Souhrny


145

Vytváření skupin Souhrny se tvoří na základě seřazených polí v databázovém seznamu, kdežto skupiny si můžeme vytvářet libovolně. Skupina je ruční účelové seskupení několika řádků k dalšímu, většinou prázdnému řádku. Nejdříve si oddělíme jednotlivé skupiny záznamů v databázovém souboru na obrázku 4.1 tak, že mezi ně vložíme prázdné řádky nadepsané jako Skupina 1, Skupina 2 atd. (viz obrázek 4.13). Jednotlivé skupiny budeme vytvářet tak, že: 1. Označíme řádky, které chceme seskupit: A3:A6. 2. Zvolíme kartu Data. 3. Ve skupině Osnova klepneme na položku Seskupit. 4. V dialogu Seskupit označíme Řádky (viz obrázek 4.14). 5. Stejným způsobem budeme vytvářet další skupiny.

Poznámka:

„ Vlevo vedle řádku Skupina 1 je znaménko minus (-), které označuje rozbalenou skupinu.

„ Vlevo vedle řádků Skupina 2 a Skupina 3 je znaménko plus (+), které označuje nerozbalenou

skupinu.

Obrázek 4.13 Tabulka evidence majetku s vloženými řádky

Evidence majetku


146 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat Vytváření součtů ve skupinách V tabulce na obrázku 4.13 jsme si vytvořili vlastní seskupení řádků pomocí skupin. Mezi jednotlivé skupiny jsme vložili prázdný řádek, který jsme nadepsali Skupina 1, Skupina 2 a Skupina 3. Chceme vytvořit součty ve skupinách podle polí Pořizovací cena a Zůstatková cena a celkový součet. 1. Pro výpočet součtu z pořizovací ceny (sloupec Pořizovací cena) za Skupinu 1 vložíme do

buňky F7 funkci:

=SUMA(F3:F6). (4.1) 2. Funkci zkopírujeme do buňky G7, ve které pak bude funkce pro výpočet součtu ze zůstat

kové ceny (sloupec Zůstatková cena) za Skupinu 1:

=SUMA(G3:G6). (4.2) 3. Pro výpočet součtu z pořizovací ceny za Skupinu 2 vložíme do buňky F14 funkci:

=SUMA(F8:F13). (4.3) 4. Funkci zkopírujeme do buňky G14, ve které pak bude funkce pro výpočet součtu ze zůstat

kové ceny za Skupinu 2:

=SUMA(G8:G13). (4.4) 5. Pro výpočet součtu z pořizovací ceny za Skupinu 3 vložíme do buňky F26 funkci:

=SUMA(F15:F25). (4.5) 6. Funkci zkopírujeme do buňky G26, ve které pak bude funkce pro výpočet součtu ze zůstat

kové ceny za Skupinu 3:

=SUMA(G15:G25). (4.6)

Obrázek 4.14 Tabulka evidence majetku a dialog Seskupit


147

7. Pro výpočet celkového součtu z pořizovací ceny vložíme do buňky F27 funkci:

=SUMA(F26;F14;F7). (4.7)

8. Funkci zkopírujeme do buňky G27, ve které pak bude funkce pro výpočet celkového souč

tu ze zůstatkové ceny:

=SUMA(G26;G14;G7). (4.8)

Po zadání všech vzorců dostaneme tabulku ve tvaru na obrázku 4.15.

Obrázek 4.15 Tabulka evidence majetku

Tip: Funkci pro celkový součet z pořizovací ceny (=SUMA(F26;F14;F7)) je výhodné zadat tak, že:

1. Zvolíme kartu Vzorce.

2. Ve skupině Knihovna funkcí klepneme na položku Automatické shrnutí.

Pokud nám stačí pouze souhrnné informace za jednotlivé skupiny, potom klepnutím na zna

ménko minus (-) u jednotlivých skupin dostaneme tabulku ve tvaru na obrázku 4.16).

Obrázek 4.16 Tabulka evidence majetku

Evidence majetku


148 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat

Poznámka: Seskupení, které bylo vytvořeno (viz obrázek 4.15), můžeme zrušit a uvést tabulku do

původního stavu na obrázku 4.13 tak, že:

1. Označíme seskupenou oblast buněk A3:A26.

2. Zvolíme kartu Data.

3. Ve skupině Osnova klepneme na položku Oddělit.

4. V dialogu Oddělit volíme Řádky. Vytvoření kontingenční tabulky Pro získávání souhrnných informací z databázového seznamu a analýzu dat je velice vhodným nástrojem kontingenční tabulka. Pomocí kontingenční tabulky získáme velice jednoduše součty za jednotlivé provozy a střediska. Součty za jednotlivé provozy a střediska z pořizovací a zůstatkové ceny v tabulce na obrázku 4.1 dostaneme tak, že: 1. Zvolíme kartu Vložení. 2. Ve skupině Tabulky klepneme na položku

Kontingenční tabulka.

3. V dialogu Vytvořit kontingenční tabulku

(viz obrázek 4.17):

„ Označíme políčko Vybrat

tabulku či oblast.

„ V okně Tabulka/oblast

vytyčením zadáme oblast

buněk A2:H23.

„ Označíme políčko Nový

list. Po potvrzení zadaných údajů se nám zobrazí nový list s nástroji na vytvoření kontingenční tabulky (viz obrázek 4.18). V dialogu Seznam polí kontingenční tabulky označíme v okně Zvolte pole, které chcete přidat do sestavy tato pole: 1. Provoz, 2. Středisko, 3. Pořizovací cena, 4. Zůstatková cena.

Obrázek 4.17 Dialog Vytvořit kontingenční tabulku

Obrázek 4.18 Nástroje pro vytvoření kontingenční tabulky


149

Po označení vybraných polí dostaneme kontingenční tabulku na obrázku 4.19.

Obrázek 4.19 Kontingenční tabulka a dialog Seznam polí kontingenční tabulky

Důležité: Při označování polí v dialogu Seznam polí kontingenční tabulky musíme dodržovat pořadí,

v jakém chceme mít seřazena pole v kontingenční tabulce. V případě, že chceme pořadí polí měnit,

můžeme to provést tak, že přesuneme pole na jinou pozici myší. Pokud chceme mít číselné hodnoty ve sloupcích Součet z Pořizovací cena a Součet z Zůstatková cena ve formátu měny Kč, potom v dialogu Seznam polí kontingenční tabulky: 1. V okně ∑ Hodnoty otevřeme rozbalovací nabíd

ku u pole Součet z Pořizovací cena a vybereme

Nastavení polí hodnot.

2. V dialogu Nastavení polí hodnot klepneme na

tlačítko Formát čísla (viz obrázek (4.20).

3. V dialogu Formát buněk volíme Měna a nasta

víme počet desetinných míst.

Stejným způsobem budeme postupovat i u zůstatkové ceny. Po zformátování čísel dostaneme kontingenční tabulku na obrázku 4.21.

Poznámka: Pokud chceme upravit kontingenční tabulku na obrázku 4.21, potom stačí umístit kurzor

do této tabulky a využít možností Nástroje kontingenční tabulky.

Obrázek 4.20 Dialog Nastavení polí hodnot

Evidence majetku


150 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat

Důležité: Při změně údajů v data

bázovém seznamu, ze kterého je

vytvořena kontingenční tabulka,

nedochází automaticky k aktuali

zaci dat v kontingenční tabulce.

Aktualizaci provedeme tak, že:

1. Umístíme kurzor do kontin

genční tabulky.

2. Zvolíme kartu Možnosti.

3. Ve skupině Data stiskneme

tlačítko Aktualizovat.

Vytvoření kontingenčního grafu Pomocí kontingenčního grafu si můžeme přehledně znázornit některé důležité informace z databázového seznamu. Zajímají nás náklady na pořízení majetku za jednotlivá střediska. Kontingenční graf z tabulky na obrázku 4.1 vytvoříme tak, že: 1. Zvolíme kartu Vložení. 2. Ve skupině Tabulky rozbalíme nabídku u pole Kontingenční tabulka a volíme Kontin

genční graf.

Obrázek 4.21 Kontingenční tabulka

Obrázek 4.22 Nástroje pro vytvoření kontingenčního grafu


151

3. V dialogu Vytvořit kontingenční tabulku s kontingenčním grafem:

„ Označíme políčko Vybrat tabulku či oblast.

„ V okně Tabulka/oblast vytyčením zadáme oblast buněk A2:H23.

„ Označíme políčko Nový list (viz obrázek 4.17).

Po potvrzení zadaných údajů se nám zobrazí nový list s nástroji na vytvoření kontingenčního grafu (viz obrázek 4.22). V dialogu Seznam polí kontingenční tabulky označíme stejným způsobem jako při vytváření kontingenční tabulky v okně Zvolte pole, které chcete přidat do sestavy: 1. Středisko, 2. Pořizovací cena. Po označení vybraných polí a vložení nadpisů grafu a os dostaneme kontingenční graf na obrázku 4.23.

Obrázek 4.23 Kontingenční graf

Poznámky:

„ Pokud chceme upravit kontingenční graf na obrázku 4.23, potom stačí klepnout na graf a využít

možností Nástroje grafu.

„ Graf můžeme formátovat tak, že klepneme na graf a zvolíme kartu Rozložení nebo Návrh.

Důležité: Při změně údajů v databázovém seznamu, ze kterého je vytvořen kontingenční graf, nedo

chází automaticky k aktualizaci dat v kontingenčním grafu. Aktualizaci provedeme tak, že:

1. Označíme kontingenční graf.

2. Zvolíme kartu Analýza.

3. Ve skupině Data stiskneme tlačítko Aktualizovat.

Evidence majetku


152 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat

Výběr záznamů pomocí automatického fi ltru

Pro výběr záznamů z databázového seznamu, které mají pro určité pole (sloupec) stejnou

hodnotu nebo splňují zadanou podmínku (jsou větší, menší, rovny apod. než určitá hodno

ta), můžeme použít automatický filtr.

V tabulce na obrázku 4.1 si budeme chtít zobrazit pouze majetek, který byl pořízen pro výro

bu. To provedeme tak, že:

1. Označíme kteroukoli buňku (např. A2) v tabulce na obrázku 4.1.

2. Zvolíme kartu Data.

3. Ve skupině Seřadit a filtrovat klepneme na položku Filtr a v tabulce se u názvu polí (sloup

ců) objeví značky pro rozbalovací nabídku (viz obrázek 4.24).

4. Klepneme na značku pro rozbalovací nabídku u pole Ty p.

5. Zrušíme označení políčka (Vybrat vše).

6. Označíme políčko V.

Obrázek 4.24 Tabulka s nastaveným automatickým filtrem

Po potvrzení zadaných údajů dostaneme tabulku na obrázku 4.25.

Pokud si chceme zobrazit majetek, který byl pořízen pro výrobu u provozu C, potom stej

ným způsobem, jako jsme vybrali typ výrobku V, vybereme i provoz C a dostaneme tabulku

na obrázku 4.26.

Dalším, pro nás zajímavým výběrem, jsou všechny záznamy z tabulky na obrázku 4.1, které

splňují podmínku, že jsou z provozu C a jejich pořizovací cena je větší než 40 000 Kč.

Výběr provozu provedeme dříve popsaným postupem a podmínku, aby pořizovací cena byla

větší než 40 000 Kč, zadáme tak, že:


153

1. Klepneme na značku pro rozbalovací nabídku u pole Pořizovací cena.

2. V rozbalovací nabídce Filtry čísel vybereme Větší než (viz obrázek 4.27).

Obrázek 4.27 Tabulka s výběrem podmínky

Obrázek 4.25 Tabulka s vybranými záznamy

Obrázek 4.26 Tabulka s vybranými záznamy

Evidence majetku


154 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat Po vybrání podmínky Větší než se zobrazí dialog Vlastní automatický filtr (viz obrázek 4.28), ve kterém: „ V rozbalovací nabídce Pořizovací cena vybere

me je větší než. „ Do vedlejšího okna zapíšeme číslo 40 000. Po potvrzení zadaných údajů dostaneme tabulku na obrázku 4.29. Vytvoření součtu vybraných záznamů pomocí funkce SUBTOTAL U záznamů vybraných na obrázku 4.29 (automatický filtr) nemůžeme vytvářet součty pomocí funkce SUMA, protože tato funkce filtry ignoruje. Pro výpočty výsledků pouze z vybraných záznamů použijeme funkci SUBTOTAL. U vybraných záznamů v tabulce na obrázku 4.29 (majetek provozu C s pořizovací cenou větší než 40 000 Kč) máme zjistit celkovou pořizovací a zůstatkovou cenu. 1. Pro výpočet součtu z pořizovací ceny (sloupec Pořizovací cena) vložíme do buňky F24 funkci:

=SUBTOTAL(9;F3:F23). (4.9) 2. Funkci zkopírujeme do buňky G24, ve které pak bude funkce pro výpočet součtu ze zůstat

kové ceny (sloupec Zůstatková cena):

=SUBTOTAL(9;G3:G23). (4.10) Po zadání vzorců dostaneme tabulku ve tvaru na obrázku 4.30.

Obrázek 4.28 Dialog Vlastní automatický filtr

Obrázek 4.29 Tabulka s vybranými záznamy

Obrázek 4.30 Tabulka se součty vybraných záznamů


155

Tip: Funkci pro celkový součet z pořizovací ceny u vybraných záznamů (=SUBTOTAL(9;F3:F23)) je výhodné zadat tak, že: 1. Zvolíme kartu Vzorce. 2. Ve skupině Knihovna funkcí klepneme na položku Automatické shrnutí. Poznámka: Seznam funkcí, které odpovídají jednotlivým kódům, najdeme v nápovědě k funkci SUBTOTAL, kterou si zobrazíme tak, že: 1. Zvolíme kartu Vzorce. 2. Ve skupině Knihovna funkcí klepneme

na položku Vložit funkci. 3. V dialogu Vložit funkci vybereme v roz

balovací nabídce Vybrat kategorii: Mate

matické. 4. V okně Vybrat funkci označíme funkci

SUBTOTAL (viz obrázek 4.31). 5. V levém dolním rohu klepneme na text:

Nápověda k této funkci.

V nápovědě (viz obrázek 4.32) se dovíme

základní informace o funkci SUBTOTAL

a významu jednotlivých kódů.

Obrázek 4.31 Dialog Vložit funkci

Obrázek 4.32 Nápověda k funkci SUBTOTAL

Evidence majetku


156 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat Vytvoření součtu pomocí funkce SUMIF Funkce SUMIF nám umožňuje vypočítat součet čísel z nějaké položky (sloupce) v databázovém seznamu za podmínky, že údaje v nějaké jiné položce splňují určitou podmínku. Máme vypočítat součet z pořizovací ceny u výrobků, které byly zakoupeny po roce 1999 (po 31.12.1999). Použijeme tabulku na obrázku 4.1. Mohli bychom to dokázat pomocí automatického filtru a funkce SUBTOTAL, avšak funkce SUMIF bude pro tento úkol vhodnější. Funkci SUMIF vložíme do buňky F24 ve tvaru: =SUMIF(H3:H23;“>31.12.1999“;F3:F23). (4.11)

Poznámka: Význam argumentů funkce SUMIF je tento:

„ H3:H23 určuje vyhodnocovanou oblast buněk pro zadaná kritéria.

„ ">31.12.1999" určuje podmínku, že hodnoty v oblasti H3:H23 musí být větší než

31.12.1999.

„ F3:F23 je oblast buněk, které se mají sčítat. Tuto funkci můžeme zapsat přímo do buňky nebo zadat pomocí dialogu Argumenty funkce (viz obrázek 4.33) tak, že: 1. Zvolíme kartu Vzorce. 2. Ve skupině Knihovna funkcí klepneme na položku Vložit funkci. 3. V dialogu Vložit funkci vybereme v rozbalovací nabídce Vybrat kategorii: Matematic

ké.

4. V okně Vybrat funkci označíme funkci SUMIF. 5. V dialogu Argumenty funkce:

„ V okně Oblast vytyčením zadá

me oblast buněk H3:H23.

„ V okně Kritéria zadáme pod

mínku „>31.12.1999“.

„ V okně Součet vytyčením

zadáme oblast buněk F3:F23.

Poznámka: V dialogu Argumenty

funkce můžeme v okně Kritéria

zadat pouze výraz >31.12.1999.

Uvozovky si Excel doplní sám. Po potvrzení údajů dostaneme tabulku ve tvaru na obrázku 4.34.

Obrázek 4.33 Dialog Argumenty funkce


157

Obrázek 4.34 Tabulka se součtem vybraných záznamů

Zaokrouhlování číselných hodnot Při formátování buněk s číselnými údaji na určitý počet desetinných míst dojde pouze k zobrazení požadovaného formátu čísla v buňce. Při provádění výpočtů používáme stále původní číslo. Pokud chceme změnit číslo pro výpočet tak, že ho zaokrouhlíme na požadovaný počet desetinných míst, musíme použít funkci ZAOKROUHLIT. U pole (sloupce) Zůstatková cena v tabulce na obrázku 4.1 vidíme, že některé ceny jsou vyjádřeny na dvě desetinná místa (na haléře). Potřebujeme tyto údaje zaokrouhlit na jedno desetinné místo. Zaokrouhlení provedeme tak, že: 1. Mezi sloupce Zůstatková cena a Datum zařazení vložíme nový sloupec. 2. Pro zaokrouhlení čísla v buňce G3 vložíme do buňky H3 funkci:

=ZAOKROUHLIT(G3;1). (4.12) 3. Funkci zkopírujeme do oblasti buněk H4:H23, ve které pak budou funkce pro zaokrouh

lení čísel v oblasti buněk G4:G23:

=ZAOKROUHLIT(G4;1),

=ZAOKROUHLIT(G5;1), (4.13)

..........................................

=ZAOKROUHLIT(G23;1).

Evidence majetku


158 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat

Poznámka: Ve funkci =ZAOKROUHLIT(G3;1):

„ G3 je adresa buňky, ve které je číslo, které se má zaokrouhlit.

„ Číslo 1 znamená, že chceme zaokrouhlit na jedno desetinné místo.

„ Pokud je místo čísla 1 jiné číslo větší než nula, je číslo zaokrouhleno na zadaný počet desetin

ných míst.

„ Pokud je místo čísla 1 číslo rovno nule, je číslo zaokrouhleno na nejbližší celé číslo.

„ Pokud je místo čísla 1 číslo menší než nula, je číslo zaokrouhleno vlevo od desetinné čárky.

Tuto funkci můžeme zapsat přímo do buňky nebo ji můžeme zadat pomocí dialogu Argumenty funkce (viz obrázek 4.35) tak, že: 1. Zvolíme kartu Vzorce. 2. Ve skupině Knihovna funkcí klep

neme na položku Vložit funkci. 3. V dialogu Vložit funkci vybere

me v rozbalovací nabídce Vybrat

kategorii: Matematické. 4. V okně Vybrat funkci označíme

funkci ZAOKROUHLIT. 5. V dialogu Argumenty funkce:

„ V okně Číslo vytyčením zadáme adresu buňky G3.

„ V okně Číslice zapíšeme číslo 1. Po potvrzení údajů dostaneme tabulku ve tvaru na obrázku 4.36.

Obrázek 4.35 Dialog Argumenty funkce

Obrázek 4.36 Tabulka se zaokrouhlenými čísly


159

Pokud si chceme zkontrolovat tabulku na obrázku 4.36 i se zadanými funkcemi (viz obrázek

4.37), pak budeme postupovat následovně:

1. Zvolíme kartu Vzorce.

2. Ve skupině Závislosti vzorců klepneme na položku Zobrazit vzorce.

Obrázek 4.37 Tabulka výsledků se zobrazenými vzorci

Označení číselných hodnot

pomocí podmíněného formátování

Podmíněné formátování buněk v určité oblasti můžeme využít k označení takových buněk,

které splňují určitá (námi zadaná) kritéria.

V databázovém seznamu na obrázku 4.1 chceme být upozorněni na všechny záznamy, u kte

rých je nulová hodnota zůstatkové ceny. Budeme postupovat tak, že:

1. Označíme oblast buněk G3:G23.

2. V kartě Domů klepneme ve skupině Styly na položku Podmíněné formátování.

3. Z nabídky zvolíme Zvýraznit pravidla buněk.

4. V další nabídce zvolíme Je rovno.

5. V dialogu Je rovno do levého okna zapíšeme nulu a v pravém okně necháme text: „Světle

červená výplň s tmavě červeným textem“ (viz obrázek 4.38).

Evidence majetku


160 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat

Obrázek 4.38 Dialog Je rovno

Po potvrzení údajů dostaneme tabulku ve tvaru na obrázku 4.39.

Obrázek 4.39 Tabulka výsledků s označenými buňkami

V tabulce na obrázku 4.39 máme označeny buňky, kde je nulová hodnota zůstatkové

ceny.

Zjištění počtu záznamů pomocí funkce COUNTIF

Funkce COUNTIF nám umožní zjistit počet buněk v určité oblasti, které splňují určité krité

rium (např. je v nich zapsán znak D).

Chceme zjistit, kolik strojů a zařízení z databázového seznamu na obrázku 4.1 je určeno pro

výrobu (ve sloupci Ty p je písmeno V).

Pro zjištění počtu záznamů, které mají ve sloupci Ty p znak V, vložíme do buňky C24 funk

ci:

=COUNTIF(C3:C23;“V“). (4.14)


161

Poznámka: Význam argumentů funkce COUNTIF je tento:

„ C3:C23 určuje oblast buněk, ve které chceme zjistit počet buněk, které splňují zadanou podmín

ku.

„ "V" určuje podmínku, že v oblasti buněk C3:C23 musí být zapsán znak V. Tuto funkci můžeme zapsat přímo do buňky nebo zadat pomocí dialogu Argumenty funkce (viz obrázek 4.40) tak, že: 1. Zvolíme kartu Vzorce. 2. Ve skupině Knihovna funkcí klepneme na položku Vložit funkci. 3. V dialogu Vložit funkci vybereme v rozbalovací nabídce Vybrat kategorii: Statistické. 4. V okně Vybrat funkci označíme

funkci COUNTIF.

5. V dialogu Argumenty funkce:

„ V okně Oblast vytyčením

zadáme oblast buněk C3:C23.

„ V okně Kritérium zadáme

podmínku „V“.

Poznámka: V dialogu Argumenty funkce můžeme v okně Kritérium zadat pouze znak V. Uvozovky

si Excel doplní sám. Po potvrzení údajů dostaneme tabulku ve tvaru na obrázku 4.41.

Obrázek 4.40 Dialog Argumenty funkce

Obrázek 4.41 Tabulka výsledků

Evidence majetku


162 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat V buňce C24 (viz tabulka 4.41) je zapsán počet záznamů, ve kterých jsou stroje nebo zařízení pro výrobu (ve sloupci Ty p je znak V). Vyhledávání údajů pomocí funkce SVYHLEDAT Funkce SVYHLEDAT nám umožňuje vyhledat záznam v databázovém seznamu na základě prohledávání prvního sloupce. Můžeme si zobrazit zvolenou položku tak, že zadáme číslo sloupce. Chceme zjistit název položky, která má inventární číslo 04128. Pro použití funkce SVYHLEDAT je výhodné seřadit tabulku na obrázku 4.1 podle prvního sloupce. Tabulku seřadíme tak, že: 1. Označíme oblast buněk, kterou chceme seřadit: A2:H23. 2. Zvolíme kartu Data. 3. Ve skupině Seřadit a filtrovat klepneme na položku Seřadit. 4. V dialogu Seřadit (viz obrázek 4.5):

„ Označíme políčko Data obsahují záhlaví.

„ V rozbalovací nabídce Seřadit podle volíme Inventární číslo.

„ V rozbalovací nabídce Řazení volíme Hodnoty.

„ V rozbalovací nabídce Pořadí volíme A až Z. Po potvrzení zadaných údajů dostaneme seřazený seznam na obrázku 4.42.

Obrázek 4.42 Seřazená tabulka výsledků


163

Poznámka: Seřazení záznamu podle prvního sloupce je výhodné, protože nemusíme pro vyhledávací

pole (sloupec) zadat přesnou hodnotu. Není-li přesná shoda nalezena, bude vrácena nejvyšší hodnota,

která je menší než hledaná hodnota. To je výhodné v případě, že si přesně nepamatujeme inventární

číslo. U nesetříděného souboru můžeme vyhledávat pouze přesně zadané hodnoty, Pro zjištění názvu položky s inventárním číslem 04128: 1. Zapíšeme do buňky A25 formou textu inventární číslo 04128 (buňka musí být zformáto

vaná na text).

2. Do buňky B25 vložíme funkci:

=SVYHLEDAT(A25;A3:H23;2). (4.15)

Poznámka: Význam argumentů funkce SVYHLEDAT je tento:

„ A25 určuje buňku, kde je hodnota, která se má vyhledat v prvním sloupci tabulky.

„ A3:H23 určuje oblast buněk (tabulku), ve které jsou odpovídající záznamy.

„ 2 určuje číslo sloupce, ve kterém je položka, kterou chceme zobrazit. Tuto funkci můžeme zapsat přímo do buňky nebo ji můžeme zadat pomocí dialogu Argumenty funkce (viz obrázek 4.43) tak, že: 1. Zvolíme kartu Vzorce. 2. Ve skupině Knihovna funkcí klepneme na položku Vložit funkci. 3. V dialogu Vložit funkci vybereme v rozbalovací nabídce Vybrat kategorii: Vyhledávací. 4. V okně Vybrat funkci označíme funkci SVYHLEDAT. 5. V dialogu Argumenty funkce:

„ V okně Hledat vytyčením zadáme adresu buňky A25.

„ V okně Tabulka vytyčením zadáme oblast buněk A3:H23.

„ V okně Sloupec zapíšeme číslo 2.

„ Okno Ty p necháme prázdné.

Obrázek 4.43 Dialog Argumenty funkce

Evidence majetku


164 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat

Poznámka: Bližší informace o funkci SVYHLEDAT se dozvíme v nápovědě k této funkci (viz obrázek

4.44), kterou si zobrazíme dříve popsaným postupem. Na tomto obrázku vidíme význam paramet

ru Typ.

Obrázek 4.44 Nápověda k funkci SVYHLEDAT

Po potvrzení údajů dostaneme tabulku ve tvaru na obrázku 4.45.

Obrázek 4.45 Tabulka výsledků s nalezenou položkou

Nyní si ukážeme způsob, jak si pomocí funkce SVYHLEDAT můžeme zobrazit všechny (popř. vybrané) položky nalezeného záznamu. To provedeme tak, že: 1. Do buněk B27, C27, D27, E27, F27, G27 a H27 zapíšeme čísla 2, 3, 4, 5, 6, 7 a 8, která

představují čísla sloupců v pořadí zleva. Těmto číslům odpovídají tato pole (sloupce) ta

bulky na obrázku 4.42:

„ 2 odpovídá položce Název majetku.

„ 3 odpovídá položce Ty p.

„ 4 odpovídá položce Provoz.

„ 5 odpovídá položce Středisko.

„ 6 odpovídá položce Pořizovací cena.


165

„ 7 odpovídá položce Zůstatková cena.

„ 8 odpovídá položce Datum zařazení.

2. Do buňky B28 vložíme funkci, která zobrazí položku ve sloupci Název majetku (sloupec

2) v řádku s inventárním číslem 04201:

=SVYHLEDAT($A28;$A$3:$H$23;B$27). (4.16)

3. Funkci zkopírujeme do oblasti buněk C28:H28, ve které pak budou funkce pro zobrazení

dalších položek (ve stejném řádku):

„ Položku ve sloupci Ty p (sloupec 3) zobrazí funkce:

=SVYHLEDAT($A28;$A$3:$H$23;C$27). (4.17)

„ Položku ve sloupci Provoz (sloupec 4) zobrazí funkce:

=SVYHLEDAT($A28;$A$3:$H$23;D$27). (4.18)

„ Položku ve sloupci Středisko (sloupec 5) zobrazí funkce:

=SVYHLEDAT($A28;$A$3:$H$23;E$27). (4.19)

„ Položku ve sloupci Pořizovací cena (sloupec 6) zobrazí funkce:

=SVYHLEDAT($A28;$A$3:$H$23;F$27). (4.20)

„ Položku ve sloupci Zůstatková cena (sloupec 7) zobrazí funkce:

=SVYHLEDAT($A28;$A$3:$H$23;G$27). (4.21)

„ Položku ve sloupci Datum zařazení (sloupec 8) zobrazí funkce:

=SVYHLEDAT($A28;$A$3:$H$23;H$27). (4.22)

Poznámka: Význam argumentů funkce SVYHLEDAT v buňce B28 je tento:

„ $A28 určuje buňku, kde je hodnota, která se má vyhledat v prvním sloupci tabulky. Sloupec je

adresován absolutně ($A) a řádek relativně (28). Je to z toho důvodu, aby se při kopírování funk

ce do oblasti buněk C28:H28 adresa sloupce, ve kterém je inventární číslo, neměnila.

„ $A$3:$H$23 určuje oblast buněk (tabulku), ve které jsou odpovídající záznamy. Obě adresy jsou

absolutní, protože se při kopírování funkce nesmí měnit.

„ B$27 určuje číslo sloupce, ve kterém je položka, kterou chceme zobrazit (v buňce B27 je zapsa

né číslo 2). Při kopírování funkce potřebujeme, aby se měnila adresa sloupce a neměnila se ad

resa řádku (tím se zobrazí další položky v řádku, který odpovídá inventárnímu číslu v buňce A28).

Sloupec je adresován relativně (B) a řádek absolutně ($27).

Po zadání všech funkcí dostaneme tabulku ve tvaru na obrázku 4.46.

Do buňky B28 jsme zadali funkci SVYHLEDAT tak, že pokud zadáme ve sloupci A do buněk

A29, A30 (popř. dalších) inventární čísla, můžeme kopírováním funkce zobrazit položky, kte

ré odpovídají těmto inventárním číslům.

Chceme si zobrazit odpovídající položky pro další inventární čísla 03768, 04142 a 04035. To

provedeme tak, že:

1. Inventární čísla 03768, 04142 a 04035 zapíšeme do buněk A29, A30 a A31.

Evidence majetku


166 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat

2. Oblast buněk B28:H28, ve které jsou zadány funkce 4.16 - 4.22, zkopírujeme do oblasti

B29:H31.

Tabulku výsledků pak dostaneme ve tvaru na obrázku 4.47.

Obrázek 4.47 Tabulka výsledků s nalezenými záznamy

Pro názornost si zobrazíme vzorce, které jsme zapsali do jednotlivých buněk (viz obrázek 4.48).

Obrázek 4.48 Tabulka výsledků s nalezenými zobrazenými vzorci

Obrázek 4.46 Tabulka výsledků s nalezeným záznamem


167

Poznámka: Pokud chceme zobrazit jiné záznamy z tabulky na obrázku 4.42, stačí zadat do oblasti

buněk A28:A31 jiné hodnoty inventárních čísel. Vyhledávání údajů pomocí funkce VVYHLEDAT Funkce VVYHLEDAT nám umožňuje vyhledat záznam v databázovém seznamu na základě prohledávání prvního řádku. Můžeme si zobrazit zvolenou položku tak, že zadáme číslo řádku. V tabulce na obrázku 4.1 chceme zjistit název položky ve sloupci Název majetku, která odpovídá desátému řádku. To provedeme tak, že: 1. Zapíšeme do buňky B25 text: Název majetku. 2. Do buňky B26 vložíme funkci:

=VVYHLEDAT(B25;A2:H23;10;0). (4.23)

Poznámka: Význam argumentů funkce VVYHLEDAT je tento:

„ B25 určuje buňku, kde je vzor textu, který se má vyhledat v prvním řádku tabulky.

„ A2:H23 určuje oblast buněk (tabulku), ve které jsou odpovídající záznamy.

„ 10 určuje číslo řádku, ve kterém je položka, kterou chceme zobrazit.

„ 0 určuje logickou hodnotu NEPRAVDA, která zajišťuje, že se bude hledat přesná shoda se zada

ným vzorem (v buňce B25) v nesetříděném řádku.

Důležité: Číslo řádků není totožné s číslem v záhlaví řádků. Jako první řádek se bere řádek, který je

prohledáván (řádek s názvy sloupců). Tuto funkci můžeme zapsat přímo do buňky nebo zadat pomocí dialogu Argumenty funkce (viz obrázek 4.49) tak, že: 1. Zvolíme kartu Vzorce. 2. Ve skupině Knihovna funkcí klepneme na položku Vložit funkci. 3. V dialogu Vložit funkci vybereme v rozbalovací nabídce Vybrat kategorii: Vyhledávací. 4. V okně Vybrat funkci označíme funkci VVYHLEDAT. 5. V dialogu Argumenty funkce:

„ V okně Hledat vytyčením zadáme adresu buňky B25.

Obrázek 4.49 Dialog Argumenty funkce

Evidence majetku


168 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat

„ V okně Tabulka vytyčením zadáme oblast buněk A2:H23.

„ V okně Řádek zapíšeme číslo 10.

„ V okně Ty p zapíšeme číslo 0.

Poznámka: Bližší informace o funkci VVYHLEDAT se dozvíme v nápovědě k této funkci (viz obrázek

4.50), kterou si zobrazíme dříve popsaným postupem. Na tomto obrázku vidíme význam paramet

ru Typ.

Obrázek 4.50 Nápověda k funkci VVYHLEDAT

Po potvrzení údajů dostaneme tabulku ve tvaru na obrázku 4.51.

Obrázek 4.51 Tabulka výsledků s nalezenou položkou


169

Nyní si ukážeme způsob, jak si pomocí funkce VVYHLEDAT můžeme zobrazit všechny (popř. vybrané) položky nalezeného záznamu. To provedeme tak, že: 1. Do oblasti buněk A28:H28 zapíšeme názvy polí (sloupců), které budeme chtít vyhledat:

„ Do buňky A28 zapíšeme text Inventární číslo.

„ Do buňky B28 zapíšeme text Název majetku.

„ Do buňky C28 zapíšeme text Ty p.

„ Do buňky D28 zapíšeme text Provoz.

„ Do buňky E28 zapíšeme text Středisko.

„ Do buňky F28 zapíšeme text Pořizovací cena.

„ Do buňky G28 zapíšeme text Zůstatková cena.

„ Do buňky H28 zapíšeme text Datum zařazení. 2. Do buňky A29 vložíme funkci, která zobrazí položku ve sloupci Název majetku z desáté

ho řádku tabulky: =VVYHLEDAT(A$28;$A$2:$H$23;$I29;0). (4.24) 3. Funkci zkopírujeme do oblasti buněk B29:H29, ve které pak budou funkce pro zobrazení

dalších položek (ve stejném řádku):

„ Položku ve sloupci Název majetku zobrazí funkce: =VVYHLEDAT(B$28;$A$2:$H$23;$I29;0). (4.25)

„ Položku ve sloupci Ty p zobrazí funkce: =VVYHLEDAT(C$28;$A$2:$H$23;$I29;0). (4.26)

„ Položku ve sloupci Provoz zobrazí funkce: =VVYHLEDAT(D$28;$A$2:$H$23;$I29;0). (4.27)

„ Položku ve sloupci Středisko zobrazí funkce: =VVYHLEDAT(E$28;$A$2:$H$23;$I29;0). (4.28)

„ Položku ve sloupci Pořizovací cena zobrazí funkce: =VVYHLEDAT(F$28;$A$2:$H$23;$I29;0). (4.29)

„ Položku ve sloupci Zůstatková cena zobrazí funkce: =VVYHLEDAT(G$28;$A$2:$H$23;$I29;0). (4.30)

„ Položku ve sloupci Datum zařazení zobrazí funkce: =VVYHLEDAT(H$28;$A$2:$H$23;$I29;0). (4.31)

Poznámka: Význam argumentů funkce VVYHLEDAT v buňce A29 je tento:

„ A$28 určuje buňku, kde je hodnota, která se má vyhledat v prvním řádku tabulky. Sloupec je

adresován relativně (A) a řádek absolutně ($28). Je to z důvodu toho, aby se při kopírování funk

ce do oblasti buněk B29:H29 adresa řádku, ve kterém je název pole (sloupce), neměnila.

„ $A$2:$H$23 určuje oblast buněk (tabulku), ve které jsou odpovídající záznamy. Obě adresy jsou

absolutní, protože se při kopírování funkce nesmí měnit.

Evidence majetku


170 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat

„ $I29 určuje číslo řádku, ve kterém je položka, kterou chceme zobrazit (v buňce I29 je zapsané

číslo 10). Při kopírování funkce potřebujeme, aby se neměnila adresa sloupce a měnila se adre

sa řádku (tím se zobrazí další položky v řádku, který odpovídá číslu řádku zadaném v buňce I29).

Sloupec je adresován absolutně ($I) a řádek relativně (29). Po zadání všech funkcí dostaneme tabulku ve tvaru na obrázku 4.52.

Obrázek 4.52 Tabulka výsledků s nalezeným záznamem

Do buňky A29 jsme zadali funkci VVYHLEDAT tak, že pokud zadáme ve sloupci I do buněk I30, I31 a I32 (popř. dalších) čísla řádků, můžeme kopírováním funkce zobrazit položky, které odpovídají těmto číslům řádků. Chceme si zobrazit odpovídající položky pro další čísla řádků (počítáno od řádku s názvy sloupců) 12, 15 a 20. To provedeme tak, že:

Obrázek 4.53 Tabulka výsledků s nalezenými záznamy


171

1. Do buněk I30, I31 a I32 zapíšeme čísla řádků 12, 15 a 20. 2. Oblast buněk A29:H29, ve které jsou zadány funkce 4.24 - 4.31, zkopírujeme do oblasti

A30:H32. Tabulku výsledků pak dostaneme ve tvaru na obrázku 4.53. Pro názornost si zobrazíme vzorce, které jsme zapsali do jednotlivých buněk (viz obrázek 4.54).

Obrázek 4.54 Tabulka výsledků s nalezenými zobrazenými vzorci

Poznámka: Pokud chceme zobrazit jiné záznamy z tabulky na obrázku 4.1, stačí zadat do oblasti

buněk I29:I32 jiné hodnoty čísel řádků.

Aktualizace kontingenční tabulky a grafu pomocí maker Pro pokročilejší uživatele Excelu si ukážeme aktualizaci kontingenční tabulky a kontingenčního grafu pomocí vlastního makra. Je to výhodné zejména v případě, že v jednom sešitě máme více pracovních listů a provedli jsme změny v tabulce, nad kterou je vytvořena kontingenční tabulka a kontingenční graf. Budeme předpokládat, že jsme provedli změny v tabulce na obrázku 4.1 a chceme provést aktualizaci kontingenční tabulky na obrázku 4.21 a kontingenčního grafu na obrázku 4.23. To provedeme tak, že: 1. Zvolíme kartu Zobrazení. 2. Ve skupině Makra rozbalíme nabídku u pole Makra a zvolíme Záznam makra. 3. V dialogu Záznam makra (viz obrázek 4.55):

„ V okně Název makra zapíšeme zvolený název Aktualizace.

„ V okně Klávesová zkratka CTRL+ zapíšeme zvolenou zkratku a.

„ V rozbalovací nabídce Uložit makro do volíme Tento sešit.

„ V okně Popis zapíšeme text: Aktualizace kontingenční tabulky a kontingenčního gra

fu.

Evidence majetku


172 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat Po potvrzení zadaných údajů se do makra zaznamenají všechny akce, které provedeme do doby, než zadáme příkaz Zastavit záznam. Pro aktualizaci kontingenční tabulky a kontingenčního grafu budeme provádět tyto akce: 1. Umístíme kurzor do kontingenční tabulky (např.

do buňky A4 v tabulce na obrázku 4.21). 2. Zvolíme kartu Možnosti. 3. Ve skupině Data stiskneme tlačítko Aktualizo

vat. 4. Označíme kontingenční graf. 5. Zvolíme kartu Analýza. 6. Ve skupině Data stiskneme tlačítko Aktualizovat. 7. Klepnutím na některou buňku vedle kontingenčního grafu zrušíme označení. 8. Zvolíme kartu Zobrazení. 9. Ve skupině Makra rozbalíme nabídku u pole Makra a zvolíme Zastavit záznam. Pokud provedeme v tabulce na obrázku 4.1 (zdrojová tabulka pro kontingenční tabulku a kontingenční graf) nějaké změny, pak můžeme provést aktualizaci kontingenční tabulky a kontingenčního grafu jednoduše tak, že stiskneme Ctrl+a. V případě, že bychom zapomněli klávesovou zkratku, pak budeme postupovat takto: 1. Zvolíme kartu Zobrazení. 2. Ve skupině Makra rozbalíme nabídku

u pole Makra a zvolíme Zobrazit makra. 3. V dialogu Makro (viz obrázek 4.56) v okně Název

makra zvolíme Aktualizace a klepneme na tlačít

ko Spustit.

Poznámka: Informace o makru (zejména klávesovou

zkratku pro jeho spuštění) se dozvíme klepnutím na

tlačítko Možnosti v dialogovém okně Možnosti makra

(viz obrázek 4.57).

Evidence zakázek Další často používanou evidencí je evidence zakázek, která by měla zajistit informace o získaných zakázkách tak, aby si firma mohla hlídat jejich včasné plnění. Metodický postup vytvoření evidence formou tabulky (seznamu) v Excelu si ukážeme na jednoduchém příkladě, do kterého zahrneme pouze některé důležité položky.

Obrázek 4.55 Dialog Záznam makra

Obrázek 4.56 Dialog Makro

Obrázek 4.57 Dialog Možnosti makra


173

Vzorový příklad vytvoření evidence zakázek Máme vytvořit tabulku v Excelu (viz obrázek 4.58), pomocí které budeme evidovat zakázky malé softwarové firmy. Rozhodli jsme se, že chceme sledovat tato pole seznamu: „ Číslo zakázky, „ Název zakázky, „ Typ (P – tvorba programů, I – tvorba internetových stránek, Š – školení), „ Firma, „ Cena, „ Termín zadání, „ Termín splnění, „ Splněno dne.

Obrázek 4.58 Tabulka evidence zakázek

Tip: Před zadáním číselných údajů do tabulky je výhodné použít funkci pro ověřování vstupních

dat, která nám zabrání zadávat data ve špatném formátu a mimo požadovaný rozsah. Ověření

vstupních dat provedeme stejným postupem jako u vzorového příkladu vytvoření evidence majet

ku s tím rozdílem, že pro zadávání číselných hodnot označíme oblast E3:E22 a pro zadávání dato

vých hodnot:

„ Označíme oblast buněk F3:H22.

„ V dialogu Ověření dat (viz obrázek 4.3) v okně Počáteční datum zapíšeme 1.1.2006.

Evidence zakázek


174 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat Vyhledávání údajů v databázovém seznamu V databázovém seznamu můžeme vyhledat libovolný záznam tak, že zadáme vzorový údaj z některého jeho pole (sloupce). Nejjednodušší způsob vyhledávání je pomocí příkazu najít, se kterým jsme se seznámili ve vzorovém příkladě vytvoření evidence majetku. Seřazení údajů v databázovém seznamu Stejně jako u vzorového příkladu vytvoření evidence majetku je i u evidence zakázek výhodné si seřadit seznam podle určitého pole (sloupce). Z praktického hlediska je pro nás výhodné si seřadit databázový seznam na obrázku 4.58 podle pole (sloupce) Termín splnění, abychom na první pohled viděli, v jakém pořadí musíme jednotlivé zakázky splnit. Seznam seřadíme tak, že: 1. Označíme oblast buněk, kterou chceme seřadit: A2:H22. 2. Zvolíme kartu Data. 3. Ve skupině Seřadit a filtrovat klepneme na položku Seřadit. 4. V dialogu Seřadit (viz obrázek 4.5):

„ Označíme políčko Data obsahují záhlaví.

„ V rozbalovací nabídce Seřadit podle zvolíme Termín splnění.

„ V rozbalovací nabídce Řazení zvolíme Hodnoty.

„ V rozbalovací nabídce Pořadí zvolíme Od nejstaršího k novějšímu. Po potvrzení zadaných údajů dostaneme seřazený seznam na obrázku 4.59.

Obrázek 4.59 Seřazená tabulka evidence zakázek


175

Důležité: Nesmíme zapomenout označit políčko: Data obsahují záhlaví, protože jinak se bude seznam

setřiďovat i se záhlavím.

Vytváření souhrnů Důležitou informací pro nás může být podíl jednotlivých typů prací a firem na celkové ceně zakázek. Tuto informaci můžeme snadno získat pomocí souhrnů. Před vytvořením souhrnů musíme databázový seznam seřadit podle polí, pro která se mají souhrny vypočítat. Pro vytvoření souhrnů použijeme tabulku na obrázku 4.58, kterou nejdříve seřadíme podle polí Ty p a Firma tak, že: 1. Označíme oblast buněk, kterou chceme seřadit: A2:H22. 2. Zvolíme kartu Data. 3. Ve skupině Seřadit a filtrovat klepneme na položku Seřadit. 4. V dialogu Seřadit (viz obrázek 4.7):

„ Označíme políčko: Data obsahují záhlaví.

„ V rozbalovací nabídce Seřadit podle volíme Ty p.

„ V rozbalovací nabídce Řazení volíme Hodnoty.

„ V rozbalovací nabídce Pořadí volíme A až Z.

„ Klepneme na tlačítko Přidat úroveň.

„ V rozbalovací nabídce Seřadit podle volíme Firma. Po potvrzení zadaných údajů dostaneme seřazený seznam na obrázku 4.60.

Obrázek 4.60 Seřazená tabulka evidence zakázek

Evidence zakázek


176 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat Nejdříve vytvoříme jednodušší souhrn podle pole Ty p tak, že: 1. Označíme kteroukoli buňku (např. A2) v seřazené tabulce na obrázku 4.60. 2. Zvolíme kartu Data. 3. Ve skupině Osnova klepneme na položku Souhrn. 4. V dialogu Souhrny (viz obrázek 4.9):

„ V rozbalovací nabídce U každé změny ve sloupci zvolíme Ty p.

„ V rozbalovací nabídce Použít funkci zvolíme Součet.

„ V okně Přidat souhrn do sloupce označíme políčko Cena.

„ V dolní části dialogu označíme políčka Nahradit aktuální souhrny a Celkový souhrn

pod daty.

Po potvrzení zadaných údajů dostaneme seznam se souhrny na obrázku 4.61.

Obrázek 4.61 Tabulka evidence zakázek se souhrny

Interpretace a analýza výsledků Z tabulky na obrázku 4.61 vidíme, že: „ V řádku Celkem z I je uveden celkový součet ceny ze zakázek za internetové stránky

(108 000 Kč). „ V řádku Celkem z P je uveden celkový součet ceny ze zakázek za zhotovení programů

(170 000 Kč).


177

„ V řádku Celkem z Š je uveden celkový součet ceny ze zakázek za školení pro firmy

(50 000 Kč). „ V řádku Celkový součet je uveden celkový součet ceny ze všech získaných zakázek

(328 000 Kč). Na základě získaných výsledků vidíme, že největší podíl na celkové ceně ze získaných zakázek tvoří zhotovení programů a nejmenší školení pro firmy. Nyní do tabulky na obrázku 4.61 přidáme ještě jeden souhrn podle pole Firma tak, že: 1. Dříve popsaným postupem si zobrazíme dialog Souhrny. 2. V rozbalovací nabídce U každé změny ve sloupci zvolíme Firma. 3. V rozbalovací nabídce Použít funkci zvolíme Součet. 4. V okně Přidat souhrn do sloupce označíme políčko Cena. 5. V dolní části dialogu zrušíme označení políčka Nahradit aktuální souhrny (viz obrázek

4.11). 6. Po potvrzení zadaných údajů dostaneme seznam se souhrny na obrázku 4.62.

Obrázek 4.62 Tabulka evidence zakázek se souhrny

V tabulce na obrázku 4.62 máme i součty za jednotlivé firmy, které jsou nadepsány Celkem.

Tip: V rozbalovací nabídce Použít funkci můžeme volit kromě funkce Součet i další funkce: Počet, Prů

měr, Maximum, Minimum, Součin, Počet čísel, Směrodatná odchylka, Odhad rozptylu a Rozptyl.

Evidence zakázek


178 Microsoft Excel pro techniky a inženýry

Kapitola 4 – Evidence technických dat

Poznámka: Souhrn, který byl vytvořen (viz obrázek 4.62), můžeme zrušit a můžeme uvést tabulku do

původního stavu na obrázku 4.60 tak, že v dialogu souhrny (viz obrázek 4.11) klepneme na polož

ku Odebrat vše.

Další důležitou informací, kterou můžeme získat pomocí souhrnů, jsou součty cen za jednotlivé roky podle termínu splnění zakázek a podle typu zakázky. Pro rozčlenění databázového seznamu podle roku, kdy měla být zakázka splněna: 1. Přidáme do tabulky na obrázku 4.59 další sloupec s názvem Rok splnění. 2. Pro převedení termínu splnění 15.5.2006 na rok splnění 2006 vložíme do buňky I3 funk

ci: =ROK(G3). (4.32) 3. Funkci zkopírujeme do oblasti buněk I4:I22, ve které pak budou funkce pro převedení

dalších termínů splnění na roky splnění:

=ROK(G4),

=ROK(G5), (4.34)

.....................

=ROK(G22). Tuto funkci můžeme zapsat přímo do buňky nebo ji můžeme zadat pomocí dialogu Argumenty funkce (viz obrázek 4.63) tak, že: 1. Zvolíme kartu Vzorce. 2. Ve skupině Knihovna funkcí klepneme na položku Vložit funkci. 3. V dialogu Vložit funkci vybe

reme v rozbalovací nabíd

ce Vybrat kategorii: Datum

a čas. 4. V okně Vybrat funkci ozna

číme funkci ROK. 5. V dialogu Argumenty funk

ce v okně Pořadové vytyče

ním zadáme adresu buňky G3. Po potvrzení údajů dostaneme tabulku ve tvaru na obrázku 4.64. Pokud si chceme zkontrolovat tabulku na obrázku 4.64 i se zadan



       
Knihkupectví Knihy.ABZ.cz - online prodej | ABZ Knihy, a.s.
ABZ knihy, a.s.
 
 
 

Knihy.ABZ.cz - knihkupectví online -  © 2004-2019 - ABZ ABZ knihy, a.s. TOPlist