STŘEDNÍ ODBORNÁ ŠKOL A A STŘEDNÍ ODBORNÉ UČILIŠTĚ NERATOVICE Školní 664, 277 11 Neratovice, tel.: 315 682 314, IČO: 683 834 95, IZO: 110 450 639 Ředitelství školy: Spojovací 632, 277 11 Neratovice tel.: 315 663 115, fax 315 684145, e-mail: mhrejsova@ sosa sou. cz, www.sosa souneratovice.cz
Registrační číslo projektu: CZ.1.07/1.5.00/34.0185 Název projektu: Moderní škola 21. století Zařazení materiálu: Šablona: III/2 Stupeň a typ vzdělávání: střední odborné Vzdělávací oblast: vzdělávání v inf. a kom.
Vzdělávací obor: žáci SOU všechny obory
technologiích Vyučovací předmět: inf. a kom. technologie
Tematický okruh: Využití ICT pro praktické aplikace
Sada: 1
Číslo DUM: 10
Ověření materiálu ve výuce: Datum ověření:
Ročník:
Ověřující učitel:
6.12.2012
druhý
Ing. Helena Holcová
STŘEDNÍ ODBORNÁ ŠKOL A A STŘEDNÍ ODBORNÉ UČILIŠTĚ NERATOVICE Školní 664, 277 11 Neratovice, tel.: 315 682 314, IČO: 683 834 95, IZO: 110 450 639 Ředitelství školy: Spojovací 632, 277 11 Neratovice tel.: 315 663 115, fax 315 684145, e-mail: mhrejsova@ sosa sou. cz, www.sosa souneratovice.cz
Název listu:
MS Excel – jednoduché databáze
Jméno autora:
Jan Krejza
Anotace:
Dovednosti rozšiřující možnosti využití tabulkového procesoru Microsoft Excel. Tvorba a třídění databází.
Klíčová slova:
MS Excel, třídění dat, řazení dat, databáze
Klíčové kompetence: Pracuje se základním programovým vybavením počítače a dokáže používat jeho složitější funkce. Přesahy a vazby: Aplikační software Microsoft Office Excel Organizace (čas, velikost skupiny, 40 min, třída žáků včetně žáků se specifickými vzdělávacími prostorová organizace): potřebami, PC s OS Windows Cílová skupina:
Žák, věková skupina 15 – 19 let
Použitá literatura, zdroje: Autorem materiálu a všech jeho částí je Jan Krejza.
Velikost:
900kB
Obsah Úvod ........................................................................................ 4 Problém k řešení ........................................................................ 4 Získání dat ................................................................................ 4 Výběr oblasti dat ........................................................................ 5 Řazení dat ................................................................................ 5 Automatický filtr ........................................................................ 6 Úlohy ....................................................................................... 6 Řešení ...................................................................................... 7 Úloha 1 .................................................................................... 7 Úloha 2 .................................................................................... 8
Úvod I když v úvodu upustíme od jakékoliv širší filozofické úvahy, můžeme přesto konstatovat, že v dnešní době se často můžeme setkat s potřebou zaznamenání dat do databáze. A to jak v pracovním prostředí, tak v prostředí domácím nebo volnočasových aktivit pro utřídění jakékoliv skupiny dat. K tomuto účelu můžeme využít tabulkový procesor, který určité možnosti nabízí. V našem výukovém materiálu využijeme tabulkový procesor Microsoft Excel 2007.
Problém k řešení V našem ukázkovém příkladu se přeneseme do domácího prostředí a trávení volného času. Pro náš příklad zvolíme pasivní zábavu, konkrétně sledování filmů. Zatím nejrozšířenější uchování filmového záznamu (rok 2012) je pomocí CD nebo DVD nosičů. Ty lze vložit do DVD přehrávače nebo počítače a na 90 a více minut se nechat unášet na vlnách pohyblivých obrázků, umělých emocí nebo se nechat vystresovat dobře zpracovaným hororem. Aby však horor nenastal při hledání snímku, na který má divák zrovna chuť, bude výhodné si rozsáhlou filmovou databázi uspo řádat a vytvořit přehled filmů, s kterým bude možné pracovat, vyhledávat v něm, atd. Pro tento úkol si ukážeme několik funkcí, které v tomto směru nabízí program Microsoft Excel 2007.
Získání dat Soubor určitých dat lze vytvořit postupně ručně nebo v některých případech můžeme pro Excel získat takovou databázi exportem dat z jiného programu, který takovou funkci nabízí. (např. účetní program vygeneruje soubor obsahující údaje o vydaných fakturách tak, aby jej bylo možné otevřít v programu MS Excel a dále s nimi pracovat) Vytvoříme ručně jednoduchou databázi – soubor dat.
Tento soubor dat může být samozřejmě mnohem rozsáhlejší. Pro účely ukázky nám postačí tento vzorek.
V menu přepneme na záložku Data a věnujeme pozornost ikonkám na obrázku. Před každou operací s daty je nutné vybrat data s kterými budeme pracovat.
Výběr oblasti dat V případě příliš rozsáhlé oblasti dat, jakou mohou databáze představovat, budeme potřebovat nějaký šikovný způsob, jak vybrat souvislou oblast dat co nejsnadněji. Postup si ukážeme na našem vzorku (viz obrázek výše). Jako první klikneme na buňku, v které je napsáno název filmu. Pak podržíme klávesy SHIFT + CTRL a klikneme na šipku doprava. Proběhne výběr souvislé oblasti dat až k hodnotám zcela vpravo (v našem případě žánr). Při stále stisknutých klávesách SHIFT + CTRL nyní stiskneme šipku dolů a vybere se celá souvislá oblast dat. Pokud oblast dat není souvislá (např. u některého filmu nebude zapsán žánr) klikáme šipkou dolů, dokud se nevybere námi požadovaná oblast. Pokud vybraná oblast z nějakého důvodu o něco přesáhne oblast dat, pustíme klávesu CTRL a pouze při stisknuté klávese SHIFT doladíme pomocí šipek požadovanou oblast dat. Pokud je oblast skutečně nepřerušovaná, je možné, že data nebude nutné označovat, neboť následující funkce je inteligentně rozliší samy.
Řazení dat Máme-li data označená, můžeme data seřadit dle prvního sloupce abecedně od A do Z nebo opačně. K tomu slouží tlačítka AZ a ZA na panelu nástrojů. Pokud si přejeme seřadit data dle hodnot v jiném sloupci, klikneme myší na velké tlačítko Seřadit, které nabízí mnohem více možností řazení.
V otevřeném ovládacím prvku můžeme nastavit několik úrovní řazení dat. Na obrázku se nejprve data seřadí dle roku a potom dle názvu filmu. To znamená
filmy natočené ve stejném roce budou seřazeny dle abecedy. V pravém horním rohu můžeme pomocí zaškrtávacího políčka vybrat, zda vybraná oblast dat obsahuje záhlaví (buňky název filmu – rok – žánr) nebo ne. Podle toho pak program první řádek zařadí do databáze nebo si jej nebude všímat.
Automatický filtr Ještě více funkcí nabízí tlačítko Filtr. Po stisknutí tlačítka se v záhlaví dat (buňky název filmu – rok – žánr) objeví malá šipka. Po jejím rozbalení dostáváme spoustu dalších možností jak data třídit, vyhledávat, apod. Jednodušší vyhledávání a třídění provedeme přednastavenými volbami, ty složitější potom můžeme nadefinovat po kliknutí na volbu Filtry textu. Těmito nástroji lze s databázemi pracovat poměrně efektivně a jen na tvořivosti uživatele, v jakém směru tyto funkce aplikuje.
Úlohy 1) V uvedeném příkladu: a. Seřaďte data dle žánru b. Vytřiďte filmy, které jsou natočeny v rozmezí let 2000 – 2010 včetně c. Vytřiďte pohádky, které jsou natočeny před rokem 1996 2) Vytvořte oblast dat z následující tabulky: Jméno Iva Vébrová Jan Koloděj Prokop Buben Antonín Panák Ivo Nejezrohlík Cyril Flinta Jindřich Veselý Marek Žito Ferenc Minulista Michal Výveg Dan Kovář Josefie Autcajdrová Van de Hong Radek Páčil Igor Hangár František Habaděj Jana Smutná David Rád Uršula Westminstrová Metoděj Skočdopole Pavel Ratmír
narozen 1.4.1991 1.8.1990 2.6.1990 3.12.1991 9.3.1990 11.11.1990 12.3.1991 13.10.1992 14.4.1991 14.7.1992 16.5.1990 17.3.1992 19.7.1990 21.3.1990 23.5.1992 23.7.1991 24.6.1992 24.8.1990 27.9.1992 29.4.1992 30.1.1990
zaplatil a a
a a a a a a a
a a
Ralf Gunter Petr Vobořil
a. Použijte automatický filtr
30.11.1992 31.3.1992
a
b. Vytřiďte všechny osoby, u kterých není poznačeno, že mají zaplaceno c. Vytřiďte všechny osoby, které se narodili v roce 1991 a mají zaplaceno d. Vytřiďte všechny osoby, jejichž jméno začíná písmenem J a obsahuje písmeno N e. Vytřiďte všechny osoby, které se narodili v jarních měsících bez ohledu na rok
Řešení Úloha 1 a. Seřaďte data dle žánru Klikneme na rozbalovací tlačítko u pole žánr a zvolíme první možnost Seřadit od A do Z. Všechna data se seřadí dle zvolené podmínky.
b. Vytřiďte filmy, které jsou natočeny v rozmezí let 2000 – 2010 včetně
U políčka Název filmu klikneme na rozbalovací tlačítko, zvolíme Filtry čísel – Mezi. V otevřeném dialogovém okně zvolíme požadované rozmezí. c. Vytřiďte pohádky, před rokem 1996
které
jsou
natočeny
Zvolíme dva filtry. U pole Žánr rozbalíme nabídku. Kliknutím na (Vybrat vše) zrušíme označení u všech žánrů a zaškrtneme pouze
Pohádka. Ve sloupci rok zvolíme Filtry čísel – Menší než a nastavíme požadovanou hodnotu.
Úloha 2 Tabulku označíme a překopírujeme do Excelu. Tím se vyhneme vytváření oblasti dat pomocí přepisování nebo snahou o konvertování, apod. a. Použijte automatický filtr – tlačítko Filtr v menu Data b. Vytřiďte všechny osoby, u kterých není poznačeno, že mají zaplaceno Rozbalte nabídku u pole Zaplatil a zvolte Prázdné c. Vytřiďte všechny osoby, které se narodili v roce 1991 a mají zaplaceno Použijeme dva filtry. Filtr u pole Narozen nastavíme na rok 1991, filtr u pole Zaplatil nastavíme na „a“. d. Vytřiďte všechny osoby, jejichž jméno začíná písmenem J a obsahuje písmeno N Použijeme filtr u pole Jméno. V rozbalené nabídce zvolíme Filtry textu – Obsahuje. V otevřeném dialogovém okně napíšeme vedle pole Obsahuje znaky J*n. Znak * zastupuje libovolný počet znaků. Znak * napíšeme nejsnadněji pomocí kombinace kláves Pravý ALT + - (znak – a _ při stisknuté klávese Shift, hned vedle vlevo od pravé klávesy Shift). e. Vytřiďte všechny bez ohledu na rok
osoby,
které
se narodily
v jarních
měsících
Toto je bonusový úkol. Autorovi se ve stanoveném čase nepodařilo najít uspokojivý, jednoduchý způsob, jak docílit splnění tohoto úkolu i při použití různých technik obcházení problému.
