Gymnázium a Střední odborná škola, Rokycany, Mládežníků 1115 Číslo projektu: Číslo šablony: Název materiálu: Ročník: Identifikace materiálu: Jméno autora: Předmět: Tématický celek: Anotace: Datum:
CZ.1.07/1.5.00/34.0410 11 Práce se seznamy 2. ročník WOH_52_11_práce se seznamy Ing. Bc. Martina Wohlrathová Informatika Seznamy v Excelu Učební text obsahuje postupy pojmenování tabulky, řazení a filtrování včetně úkolů na procvičení a ukázky tabulek kritérií pro rozšířený filtr. 8.3.2014
Seznamy Seznamy v Excelu nazýváme rozlehlé tabulky, kterým se jinak říká databáze. Tyto tabulky lze do Excelu importovat z databázových aplikací, např. MS Access (na kartě Data–Načíst externí data…) Databáze je organizovaná sada údajů ve formě tabulky, v níž řádky nazýváme záznamy a sloupce pole. Excel disponuje mnoha nástroji pro práci s databázemi, z nichž nyní vybíráme: Definované názvy Řazení Filtry Připomeňte si úpravy typu vložit sloupec/řádek, skrýt a zobrazit sloupec/řádek, přizpůsobit šířku sloupce apod.
Pojmenování oblasti buněk na listu pomocí pole názvů
1) vyznačíme oblast 2) klikneme do pole názvů a vepíšeme název 3) ukončíme klávesou Enter Název musí začínat písmenem, další znaky pomou být číslice a podtržítko Další možností, jak vytvořit název, je na kartě Vzorce–Správce názvů, v jehož dialogovém okně lze mj. názvy upravit nebo odstranit. Ve skupině tlačítek Definované názvy se nabízí možnost Vytvořit z výběru.
Definované názvy slouží ke zjednodušení práce: – lze je použít ve vzorcích namísto odkazů, zejména při práci se seznamy (v argumentech databázových funkcí) – poslouží k rychlému přechodu (výběru) na pojmenovanou oblast Ukázka použití ve vzorci: Modrou buňku s pětkou mám pojmenovanou pětka, do vedlejší buňky vložím vzorec: =pětka 5 5
Řazení Tabulku můžeme seřadit dle zvoleného sloupce vzestupně, sestupně nebo dle vlastního seznamu, můžeme přidat další úrovně řazení, řadit podle hodnoty, barvy apod. Stačí umístit kurzor do libovolné buňky v tabulce (Excel si najde hranice tabulky sám) a zvolit Data–Seřadit Je dobré zkontrolovat, zda odpovídá toto zaškrtnutí. Tento údaj ovlivní, zda se první řádek zahrne do řazení nebo ne. Ostatní nastavení jsou zřejmá.
Poznámka:
Pokud před řazením označíme oblast (např. sloupec), seřadí se pouze označené buňky (v tabulce to znamená prohození údajů v záznamech – nepřijatelné!). Je-li označený sloupec součástí tabulky, Excel zobrazí upozornění s dotazem, co opravdu chceme řadit.
Cvičení:
pořadí 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
– Seřaďte tabulku níže dle příjmení a dále podle jména (jako v telefonním seznamu). – Vytvořte si vlastní seznam řazení dle vzdělání a seřaďte dle tohoto seznamu a dále podle pořadí.
příjmení jméno Horáček David Mladý Josef Korn Petr Jará Radka Novotná Ilona Sabová Markéta Rolník Martin Daněk Jiří Štrunc Josef Šebková Jana Šebová Bohumila KolomazníkDan Novotný Petr Moudrá Jiřina Klapka Josef Klapková Stanislava Řezníček David Fialová Hana Trhlíková Zlata Boudová Helena Skopec Petr Šťastný Petr Švec Josef Havlíček Karel Karpíšek Martin Svobodová Dana Veselý Jiří Šedivý Karel Bílá Jana Červený Martin Novák Daniel Svobodová Iveta Vintíř Josef Plachý Jiří Franěk Jaroslav Svatuška Marek Slaninka Josef Masný Martin Svojtka Albert Maňas Petr Pilous Luboš Hrobařík Martin Kovář Jiří Nekovář Petr Hrbatý Josef Sivá Anna Mladý Jiří Stará Iris LedajaksováDagmar Pilný Josef
datum narození 11. září 1972 3. srpen 1978 6. červenec 1967 23. květen 1956 27. únor 1979 19. březen 1971 24. červen 1965 18. květen 1982 31. leden 1979 14. červenec 1969 7. duben 1972 15. listopad 1978 13. únor 1985 25. květen 1976 21. červen 1968 4. říjen 1967 29. červen 1988 27. prosinec 1986 16. červen 1986 12. prosinec 1967 11. září 1976 17. srpen 1972 30. květen 1983 8. červen 1969 23. duben 1985 11. březen 1983 13. listopad 1980 15. prosinec 1979 20. říjen 1965 8. srpen 1967 15. leden 1979 22. únor 1978 14. září 1965 7. září 1976 5. srpen 1980 30. leden 1981 16. duben 1974 27. říjen 1982 5. prosinec 1985 6. červen 1984 6. červenec 1979 3. červenec 1989 4. květen 1976 27. duben 1973 14. srpen 1979 3. květen 1969 14. březen 1990 11. prosinec 1983 6. listopad 1977 25. únor 1980
město Brno Plzeň Plzeň Praha Plzeň Brno Praha Rokycany Praha Plzeň Rokycany Brno Praha Brno Rokycany Plzeň Brno Ostrava Praha Rokycany Plzeň Ostrava Rokycany Ostrava Brno Ostrava Praha Ostrava Rokycany Plzeň Ostrava Brno Praha Teplice Ostrava Plzeň Brno Brno Rokycany Teplice Praha Ostrava Teplice Praha Plzeň Rokycany Ostrava Plzeň Rokycany Plzeň
pohlaví m m m ž ž ž m m m ž ž m m ž m ž m ž ž ž m m m m m ž m m ž m m ž m m m m m m m m m m m m m ž m ž ž m
počet dětí 1 2 1 0 0 3 3 1 1 1 2 2 1 5 4 2 2 1 0 0 0 1 0 1 0 2 2 1 0 0 1 1 1 2 2 1 2 3 3 0 0 2 2 1 1 2 2 3 3 1
zaměstnání účetní obchodník asistent učitel prodavačka asistent řidič obchodník asistent účetní prodavačka ředitel návrhář prodavačka obchodník účetní asistent asistent účetní ředitel řidič návrhář asistent učitel obchodník účetní návrhář pokrývač účetní ředitel obchodník učitel ředitel učitel účetní řidič pokrývač asistent pokrývač učitel obchodník účetní ředitel asistent učitel ředitel řidič účetní prodavačka ředitel
mzda 21500 34000 35000 22000 25000 36000 38000 29000 48000 21000 14000 89000 67000 21000 43000 23000 45000 34000 19500 35000 16500 56000 29500 25000 32000 30000 49000 18000 28000 46000 29000 24000 185000 29000 25000 21000 29000 34000 21500 27000 27000 23000 67000 41000 26000 54000 15000 23500 18000 79000
věk 41 35 46 57 35 43 48 32 35 44 42 35 29 37 45 46 26 27 28 46 37 41 31 44 29 31 33 34 48 46 35 36 48 37 33 33 40 31 28 30 34 25 38 41 34 44 24 30 36 34
výška 187 190 195 163 167 170 192 198 195 175 172 187 198 178 195 175 186 168 161 178 180 197 187 189 186 174 194 180 175 176 179 168 180 198 186 179 180 189 175 176 179 180 182 187 179 178 197 169 163 186
váha 98 87 89 65 54 64 87 97 105 62 59 78 96 71 84 69 81 75 59 62 79 96 89 108 96 67 89 84 65 83 90 64 85 87 91 90 109 78 87 80 97 92 75 99 86 73 89 59 56 79
vzdělání středoškolské vysokoškolské středoškolské vysokoškolské vysokoškolské středoškolské vysokoškolské středoškolské vysokoškolské středoškolské středoškolské vysokoškolské vysokoškolské základní středoškolské středoškolské středoškolské vysokoškolské středoškolské vysokoškolské základní vysokoškolské středoškolské vysokoškolské středoškolské středoškolské středoškolské základní středoškolské vysokoškolské středoškolské vysokoškolské vysokoškolské vysokoškolské středoškolské středoškolské vysokoškolské středoškolské základní vysokoškolské vysokoškolské středoškolské středoškolské vysokoškolské vysokoškolské vysokoškolské základní středoškolské středoškolské vysokoškolské
Automatický filtr Zapnutím automatického filtru Data–Filtr (po umístění kurzoru do tabulky) se v záhlaví sloupců zobrazí šipky a je možné data filtrovat, tzn. zobrazit pouze ty záznamy, které vyhovují nastaveným kritériím. Kliknutím na šipku ve zvoleném sloupci zaškrtneme buď konkrétní hodnotu(y), nebo zadáme složitější podmínku otevřením nabídky Filtry… a zadáním podmínek v okně Vlastní automatický filtr. Filtrováním se skryjí řádky, v jejichž buňkách nevyhovovaly hodnoty podmínkám. Původní tabulku nejrychleji získáme vypnutím filtru na kartě Data. Vyfiltrovaná data můžeme řadit.
Cvičení: 1) Vyfiltrujte záznamy s ženami, které mají děti. (11 záznamů) 2) … prodavačky z Plzně nebo Rokycan. (3) 3) … lidi z Prahy nebo Brna, kteří pobírají mzdu větší než 30 000 Kč a jejich příjmení je v druhé polovině abecedy, tj. začíná písmenem O, P, atd. (9) 4) … lidi narozené po roce 1980, kteří jsou menší než 180 cm. (7) 5) … lidi s červeně podbarveným datumem narození. (12) pořadí 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
příjmení jméno Horáček David Mladý Josef Korn Petr Jará Radka Novotná Ilona Sabová Markéta Rolník Martin Daněk Jiří Štrunc Josef Šebková Jana Šebová Bohumila KolomazníkDan Novotný Petr Moudrá Jiřina Klapka Josef Klapková Stanislava Řezníček David Fialová Hana Trhlíková Zlata Boudová Helena Skopec Petr Šťastný Petr Švec Josef Havlíček Karel Karpíšek Martin Svobodová Dana Veselý Jiří Šedivý Karel Bílá Jana Červený Martin Novák Daniel Svobodová Iveta Vintíř Josef Plachý Jiří Franěk Jaroslav Svatuška Marek Slaninka Josef Masný Martin Svojtka Albert Maňas Petr Pilous Luboš Hrobařík Martin Kovář Jiří Nekovář Petr Hrbatý Josef Sivá Anna Mladý Jiří Stará Iris LedajaksováDagmar Pilný Josef
datum narození 11. září 1972 3. srpen 1978 6. červenec 1967 23. květen 1956 27. únor 1979 19. březen 1971 24. červen 1965 18. květen 1982 31. leden 1979 14. červenec 1969 7. duben 1972 15. listopad 1978 13. únor 1985 25. květen 1976 21. červen 1968 4. říjen 1967 29. červen 1988 27. prosinec 1986 16. červen 1986 12. prosinec 1967 11. září 1976 17. srpen 1972 30. květen 1983 8. červen 1969 23. duben 1985 11. březen 1983 13. listopad 1980 15. prosinec 1979 20. říjen 1965 8. srpen 1967 15. leden 1979 22. únor 1978 14. září 1965 7. září 1976 5. srpen 1980 30. leden 1981 16. duben 1974 27. říjen 1982 5. prosinec 1985 6. červen 1984 6. červenec 1979 3. červenec 1989 4. květen 1976 27. duben 1973 14. srpen 1979 3. květen 1969 14. březen 1990 11. prosinec 1983 6. listopad 1977 25. únor 1980
město Brno Plzeň Plzeň Praha Plzeň Brno Praha Rokycany Praha Plzeň Rokycany Brno Praha Brno Rokycany Plzeň Brno Ostrava Praha Rokycany Plzeň Ostrava Rokycany Ostrava Brno Ostrava Praha Ostrava Rokycany Plzeň Ostrava Brno Praha Teplice Ostrava Plzeň Brno Brno Rokycany Teplice Praha Ostrava Teplice Praha Plzeň Rokycany Ostrava Plzeň Rokycany Plzeň
pohlaví počet dětí m 1 m 2 m 1 ž 0 ž 0 ž 3 m 3 m 1 m 1 ž 1 ž 2 m 2 m 1 ž 5 m 4 ž 2 m 2 ž 1 ž 0 ž 0 m 0 m 1 m 0 m 1 m 0 ž 2 m 2 m 1 ž 0 m 0 m 1 ž 1 m 1 m 2 m 2 m 1 m 2 m 3 m 3 m 0 m 0 m 2 m 2 m 1 m 1 ž 2 m 2 ž 3 ž 3 m 1
zaměstnání účetní obchodník asistent učitel prodavačka asistent řidič obchodník asistent účetní prodavačka ředitel návrhář prodavačka obchodník účetní asistent asistent účetní ředitel řidič návrhář asistent učitel obchodník účetní návrhář pokrývač účetní ředitel obchodník učitel ředitel učitel účetní řidič pokrývač asistent pokrývač učitel obchodník účetní ředitel asistent učitel ředitel řidič účetní prodavačka ředitel
mzda 21500 34000 35000 22000 25000 36000 38000 29000 48000 21000 14000 89000 67000 21000 43000 23000 45000 34000 19500 35000 16500 56000 29500 25000 32000 30000 49000 18000 28000 46000 29000 24000 185000 29000 25000 21000 29000 34000 21500 27000 27000 23000 67000 41000 26000 54000 15000 23500 18000 79000
věk 41 35 46 57 35 43 48 32 35 44 42 35 29 37 45 46 26 27 28 46 37 41 31 44 29 31 33 34 48 46 35 36 48 37 33 33 40 31 28 30 34 25 38 41 34 44 24 30 36 34
výška 187 190 195 163 167 170 192 198 195 175 172 187 198 178 195 175 186 168 161 178 180 197 187 189 186 174 194 180 175 176 179 168 180 198 186 179 180 189 175 176 179 180 182 187 179 178 197 169 163 186
váha 98 87 89 65 54 64 87 97 105 62 59 78 96 71 84 69 81 75 59 62 79 96 89 108 96 67 89 84 65 83 90 64 85 87 91 90 109 78 87 80 97 92 75 99 86 73 89 59 56 79
vzdělání středoškolské vysokoškolské středoškolské vysokoškolské vysokoškolské středoškolské vysokoškolské středoškolské vysokoškolské středoškolské středoškolské vysokoškolské vysokoškolské základní středoškolské středoškolské středoškolské vysokoškolské středoškolské vysokoškolské základní vysokoškolské středoškolské vysokoškolské středoškolské středoškolské středoškolské základní středoškolské vysokoškolské středoškolské vysokoškolské vysokoškolské vysokoškolské středoškolské středoškolské vysokoškolské středoškolské základní vysokoškolské vysokoškolské středoškolské středoškolské vysokoškolské vysokoškolské vysokoškolské základní středoškolské středoškolské vysokoškolské
Rozšířený filtr Používá se v případě, že kritéria pro filtrování jsou příliš složitá. Naučíme se ho používat na příkladech, které lze zvládnout i automatickým filtrem. Zadáme Data–Upřesnit (vedle automatického filtru) V okně rozšířeného filtru zvolíme, zda chceme vyfiltrovaná data kopírovat jinam na tento list. Vyplníme odkaz na tabulku (není třeba, máme-li kurzor v tabulce) nebo název oblasti tabulky. Oblast kritérií je samostatná tabulka, kterou s potřebnými podmínkami připravíme předem. Kopírovat do: zadáme odkaz na levou horní buňku oblasti, kam se mají kopírovat vyfiltrovaná data
Jak vytvořit tabulku kritérií? V prvním řádku zadáme názvy polí (záhlaví sloupců), ve kterých stanovujeme podmínky. Doporučuje se tyto názvy z tabulky kopírovat. V dalších řádcích zapisujeme pro jednotlivá pole podmínky a dodržujeme při tom tato pravidla: s podmínky, které mají být splněny zároveň (spojeny pomocí AND), zapisujeme na stejný řádek tabulky kritérií s podmínky spojené pomocí OR (platí jedna nebo druhá) uvádíme do různých řádků s nevynecháváme prázdné řádky, neboť mají také význam podmínky (podmínky, kterou splňují všechny záznamy) Na listu Tabulky kritérií si prohlédněte ukázky.
Cvičení: Zkopírujte do oblasti začínající buňkou B90 (nebo libovolně) vyfiltrované záznamy, které splňují požadované podmínky: a) vysokoškolsky vzdělaní pražané s jedním nebo dvěma dětmi b) všichni, jejichž křestní jméno začíná na J a je jim nejméně 30 let c) muži o hmotnosti mezi 70 kg až 85 kg se středoškolským vzděláním d) vymyslete další složitější kritéria pořadí 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
příjmení Horáček Mladý Korn Jará Novotná Sabová Rolník Daněk Štrunc Šebková Šebová Kolomazník Novotný Moudrá Klapka Klapková Řezníček Fialová Trhlíková Boudová Skopec Šťastný Švec Havlíček Karpíšek Svobodová Veselý Šedivý Bílá Červený Novák Svobodová Vintíř Plachý Franěk Svatuška Slaninka Masný Svojtka Maňas Pilous Hrobařík Kovář Nekovář Hrbatý Sivá Mladý Stará Ledajaksová Pilný
jméno David Josef Petr Radka Ilona Markéta Martin Jiří Josef Jana Bohumila Dan Petr Jiřina Josef Stanislava David Hana Zlata Helena Petr Petr Josef Karel Martin Dana Jiří Karel Jana Martin Daniel Iveta Josef Jiří Jaroslav Marek Josef Martin Albert Petr Luboš Martin Jiří Petr Josef Anna Jiří Iris Dagmar Josef
datum narození 11. září 1972 3. srpen 1978 6. červenec 1967 23. květen 1956 27. únor 1979 19. březen 1971 24. červen 1965 18. květen 1982 31. leden 1979 14. červenec 1969 7. duben 1972 15. listopad 1978 13. únor 1985 25. květen 1976 21. červen 1968 4. říjen 1967 29. červen 1988 27. prosinec 1986 16. červen 1986 12. prosinec 1967 11. září 1976 17. srpen 1972 30. květen 1983 8. červen 1969 23. duben 1985 11. březen 1983 13. listopad 1980 15. prosinec 1979 20. říjen 1965 8. srpen 1967 15. leden 1979 22. únor 1978 14. září 1965 7. září 1976 5. srpen 1980 30. leden 1981 16. duben 1974 27. říjen 1982 5. prosinec 1985 6. červen 1984 6. červenec 1979 3. červenec 1989 4. květen 1976 27. duben 1973 14. srpen 1979 3. květen 1969 14. březen 1990 11. prosinec 1983 6. listopad 1977 25. únor 1980
město Brno Plzeň Plzeň Praha Plzeň Brno Praha Rokycany Praha Plzeň Rokycany Brno Praha Brno Rokycany Plzeň Brno Ostrava Praha Rokycany Plzeň Ostrava Rokycany Ostrava Brno Ostrava Praha Ostrava Rokycany Plzeň Ostrava Brno Praha Teplice Ostrava Plzeň Brno Brno Rokycany Teplice Praha Ostrava Teplice Praha Plzeň Rokycany Ostrava Plzeň Rokycany Plzeň
pohlaví m m m ž ž ž m m m ž ž m m ž m ž m ž ž ž m m m m m ž m m ž m m ž m m m m m m m m m m m m m ž m ž ž m
počet dětí 1 2 1 0 0 3 3 1 1 1 2 2 1 5 4 2 2 1 0 0 0 1 0 1 0 2 2 1 0 0 1 1 1 2 2 1 2 3 3 0 0 2 2 1 1 2 2 3 3 1
zaměstnání účetní obchodník asistent učitel prodavačka asistent řidič obchodník asistent účetní prodavačka ředitel návrhář prodavačka obchodník účetní asistent asistent účetní ředitel řidič návrhář asistent učitel obchodník účetní návrhář pokrývač účetní ředitel obchodník učitel ředitel učitel účetní řidič pokrývač asistent pokrývač učitel obchodník účetní ředitel asistent učitel ředitel řidič účetní prodavačka ředitel
mzda 21500 34000 35000 22000 25000 36000 38000 29000 48000 21000 14000 89000 67000 21000 43000 23000 45000 34000 19500 35000 16500 56000 29500 25000 32000 30000 49000 18000 28000 46000 29000 24000 185000 29000 25000 21000 29000 34000 21500 27000 27000 23000 67000 41000 26000 54000 15000 23500 18000 79000
věk 41 35 46 57 35 43 48 32 35 44 42 35 29 37 45 46 26 27 28 46 37 41 31 44 29 31 33 34 48 46 35 36 48 37 33 33 40 31 28 30 34 25 38 41 34 44 24 30 36 34
výška 187 190 195 163 167 170 192 198 195 175 172 187 198 178 195 175 186 168 161 178 180 197 187 189 186 174 194 180 175 176 179 168 180 198 186 179 180 189 175 176 179 180 182 187 179 178 197 169 163 186
váha 98 87 89 65 54 64 87 97 105 62 59 78 96 71 84 69 81 75 59 62 79 96 89 108 96 67 89 84 65 83 90 64 85 87 91 90 109 78 87 80 97 92 75 99 86 73 89 59 56 79
vzdělání středoškolské vysokoškolské středoškolské vysokoškolské vysokoškolské středoškolské vysokoškolské středoškolské vysokoškolské středoškolské středoškolské vysokoškolské vysokoškolské základní středoškolské středoškolské středoškolské vysokoškolské středoškolské vysokoškolské základní vysokoškolské středoškolské vysokoškolské středoškolské středoškolské středoškolské základní středoškolské vysokoškolské středoškolské vysokoškolské vysokoškolské vysokoškolské středoškolské středoškolské vysokoškolské středoškolské základní vysokoškolské vysokoškolské středoškolské středoškolské vysokoškolské vysokoškolské vysokoškolské základní středoškolské středoškolské vysokoškolské
Ukázka tabulek kritérií
jméno Josef
Všechny záznamy, u kterých je v poli jméno údaj Josef, jinými slovy všichni
... lidé z Teplic se základním nebo město Teplice Teplice
vzdělání základní středoškolské
věk >30
...starší než 30 let město
Kdyby zde nebylo zapsáno Teplice (např. prázdná buňka), šlo by o středoškolsky
...všichni s bydlištěm
Praha Brno příjmení M datum narození <1.1.1970
...s příjmením od M
...narozeni před rokem 1970
počet dětí 0 ... všichni s počtem dětí 0.
zaměstnání ředitel asistent asistent ředitel
... všichni ředitelé nebo asistenti, kteří mají jedno nebo dvě děti. (Čteme podmínky po řádcích: ředitelé s jedním dítětem, nebo asistenti se dvěma, nebo asistenti s jedním,
počet dětí 1 2 1 2
pořadí Všechny záznamy. Lze použít libovolné pole (název
pohlaví m m
mzda >50000
výška >190
... všichni muži s platem vyšším než 50 000 Kč nebo s výškou těla větší než 190 cm.
