Manažerská informatika 3MA381
Domů  |  3MA112  |  3MA191  |  3MA381  |  3MA382  |  3MA481   
Osnova cvičení Úvodní informace Harmonogram prezentací Hodnocení Podklady ke cvičením Teorie Domácí cvičeníExtra aktivity Odkazy

Seznamy a filtrování dat

V tabulkovém procesoru často pracujeme s tabulkou, která vypadá jako jednoduchá databáze. Pokud je tabulka dobře strukturovaná a splňuje jednoduchá pravidla, pak s ní můžeme provádět řadu operací. Takové tabulce budeme říkat seznam.

Seznam

Co to je seznam

Seznam je jednoduchá tabulka, která obsahuje v prvním řádku záhlaví (názvy polí) a v dalších řádcích záznamy (věty).

Záznam je jedna položka seznamu, jeden řádek, který obsahuje jednotlivá pole záznamu.

Pole je položka záznamu, buňka, která má daný datový typ stejný s ostatními poli jednotlivých záznamů v daném sloupci. Polem může být text, číslo, datum, čas, logická hodnota, vzorec nebo funkce.

Pravidla pro vytváření seznamů

Pokud chceme, aby seznamu dobře rozuměl nejen uživatel, ale i Excel, je potřeba dodržet některá jednoduchá pravidle. Dodržením těchto pravidel nejen zefektivníme práci s rozsáhlými seznamy, ale současně se i vyhneme řadě možných problémů, které mohou u velkých tabulek nastat.

V seznamu nesmíme mít žádný prázdný řádek ani sloupec. Tím by došlo k rozdělení seznamu na části – dílčí seznamy (dvě tabulky).

Co naopak není potřeba:

Řazení polí

Záznamy můžeme v seznamu řadit podle libovolného pole nebo i podle několika polí najednou. K seřazení záznamů podle jednoho pole stačí označit libovolnou buňku v daném sloupci a zvolit příkaz Seřadit  vzestupně Řazení polí nebo  Seřadit sestupně Řazení polí. Pro řazení záznamů podle více polí můžeme zvolit z karty Data - Seřadit a filtrovat - Seřadit a nadefinovat podmínek více (před zvolením příkazu je potřeba aby byla označena libovolná buňka seznamu). V možnostech řazení je i zajímavá možnost řazení podle řádku nikoli sloupce.

Seřadit  podle

Pokud v seznamu označíme více než jednu buňku, pak se nás Excel zeptá, jestli chceme řazení provést jen v rámci označených buněk nebo jestli chceme tuto oblast rozšířit na celý seznam.

Seřadit  oblast

Jak řadí Excel

Pokud máme v polích údaje stejného typu, je řazení jednoduché. Například při vzestupném řazení:

Při řazení pole, kde je více typů údajů je řazení na první pohled složitější. Při vzestupném řazení se buňky řadí podle následujícího pořadí:

Jako první se ve skutečnosti řadí některé řídící a netisknutelné znaky, ale s těmi se v tabulkovém procesoru setkáme zřídka.

Data se řadí podle pořadí dne. Nejdříve tedy ty co jsou blíže k datu 1. 1. 1900. Při řazení čísel a dat najednou má datum hodnotu čísla dle pořadí od 1. 1. 1900 resp. 1904.

Formát data, času, čísla apod. nemá vliv na jejich řazení stejně jako barva písma, vzorek či ohraničení apod.

Formulář

Pro prohlížení záznamů v seznamu můžeme použít Formulář. Příkaz formulář není v Excelu 2013 standardně k dispozici, na pás karet si ho můžeme přidat přes Soubor - Možnosti - Přizpůsobit pás karet... Údaje můžeme pomocí zobrazení formuláře nejen prohlížet, ale i editovat a přidávat nové řádky (záznamy). Měnit lze ta pole, která jsou v záznamu skutečně zapsaná. Na obrázku níže je vidět, že pole Věk je v seznamu dopočítáváno, proto jej nejde editovat.

Seznamy - formulář

Automatický filtr

Pokud pracujeme s tabulkou, která splňuje podmínky seznamu / databáze, pak se většinou jedná o rozsáhlé množství záznamů, ve kterých není snadné se snadno orientovat. Často jde o stovky až desetitisíce záznamů. Pro lepší orientaci, analýzu a zpracování informací je vhodné pracovat pouze se záznamy, které nás v danou chvíli zajímají. Excel nám pro tento účel mj. nabízí filtrování dat a to pomocí automatického nebo rozšířeného filtru.

Automatický filtr spustíme tak, že se postavíme kurzorem kamkoliv do seznamu (pokud seznam splňuje základní pravidla uvedená výše, není potřeba jej  celý označovat, Excel to pozná sám) a vybereme z nabídky příkaz Data – Seřadit a filtrovat – filtr. Excel automaticky rozpozná první řádek seznamu jako názvy polí a přiřadí každému názvu pole tlačítko se šipkou a seznamem nabízených řazení a filtrů.

Seznamy

Máme tedy následující možnosti:

Filtry v jednotlivých polích seznamu lze libovolně kombinovat. Pokud je aplikován nějaký filtr a alespoň jeden záznam ze seznamu vyhovuje nastavenému filtrování (řádek se záznamem je skrytý), pak je šipka u filtrovaného pole doplněna o ikonu filtru. Pokud použijeme filtrování v několika polích a pak chceme najednou zobrazit všechny záznamy nemusíme u všech názvů polí volit Vymazat filtr , ale postačí zvolit z nabídky Data – Seřadit a filtrovat – Vymazat. Při použití automatického filtru zůstává seznam celou dobu na původním místě a žádné záznamy se ze seznamu nemažou. Při zobrazení všech záznamů dostáváme tedy zpět původní seznam v nezměněné podobě (s výjimkou vzestupného / sestupného řazení seznamu podle pole).

Rozšířený filtr

Rozšířený filtr spustíme tak, že se postavíme kurzorem do seznamu a vybereme z nabídky příkaz Data – Seřadit a filtrovat – Upřesnit. Při použití rozšířeného filtru máme více možností filtrování než u automatického filtru. Můžeme použít více kritérií podle kterých chceme filtrovat, můžeme filtrovat přímo v seznamu nebo výsledek filtrace zaznamenat na jiné místo.

Při použití rozšířeného filtru definujeme oblasti:

Rozšířený filtr

Po prvním spuštění rozšířeného filtru se v sešitu vytvoří názvy Kritéria a Extrakce.

 

Příklady použití rozšířeného filtru

  1. Pokud chceme vybrat všechny záznamy, kde jména začínají na Jan (tedy i např. jméno Jana) a současně starší než 30 let zadáme kritérium:
    Jméno Věk
    Jan >30
  2. Výběr všech záznamů, které mají jméno začínající na Jan a současně příjmení začínající na P.
    Jméno Příjmení
    Jan P
  3. Výběr všech záznamů, které mají jméno začínající na Jan a nebo příjmení začínající na P.
    Jméno Příjmení
    Jan  
      P
  4. Takto nastavené kritérium bychom mohli také číst jako: (1. řádek) vyber všechny záznamy, které mají v poli jméno údaj začínající na Jan a k tomu libovolné příjmení. K tomu přidej záznamy (2. řádek), které mají v poli jméno libovolný údaj a přitom pole příjmení začíná na písmeno p.
  5. Výběr všech záznamů, kde věk je větší než 30 a současně menší nebo roven 40.
    Věk Věk
    >30 <=40
  6. Výběr všech záznamů, kde položce bydliště odpovídá Praha nebo Brno
    Bydliště
    Praha
    Brno
  7. Výběr všech záznamů, které mají v poli jméno právě jen Jan.
    Jméno
    =Jan
    Aby se ve výsledku filtrování vypsaly všechny položky pole jméno, které jsou právě Jan a nikoliv Jana, pak nestačí do kritéria zapsat =Jan nebo ="Jan" ale ="=Jan".

Při vytváření kritérií můžeme použít tyto znaky:

Znak Význam
< Je menší
> Je větší
<= Je menší nebo rovno
>= Je větší nebo rovno
<> Není rovno (je různé od)
* Libovolný sled znaků
? Libovolný znak

Příklady použití

Znak Význam
*ová Příjmení končící na "ová"
*auto* Záznamy, které obsahují v textu "auto"
???1? Předposlední číslo PSČ je jednička
<>????? Pole nemá pět znaků
="=jan" Pole je právě Jan nebo jan
>W Záznamy začínající na X, Y, Z nebo Ž

Výpis unikátních záznamů

Když potřebujeme z nějakého seznamu, který obsahuje duplicitní nebo multiplicitní záznamy vyfiltrovat seznam unikátních záznamů, pak můžeme dané pole použít jako kritérium filtrování (kritérium necháme prázdné) a v dialogovém okně Rozšířený filtr zaškrtneme Bez duplicitních záznamů.

Použití vzorce jako kritéria

Velice účinným způsobem lze filtrovat data při použití kritéria tvořeného vzorcem nebo funkcí.

Filtr a vzorec

V tomto případě nezadáváme do záhlaví kritéria název některého z polí, ale libovolný text - například Únoroví. Jako kritérium pak zapisujeme vzorec, který vrací hodnotu PRAVDA nebo NEPRAVDA. Pokud chceme například vybrat záznamy, kde datum narození je v únoru, pak do kritéria zapíšeme vzorec

=MĚSÍC(E3)=2

Adresa E3 je odkaz na první záznam v poli Datum_narození. Protože měsíc v datu 26.2.1970 odpovídá únoru, vrací vzorec hodnotu PRAVDA. Pro další záznamy nemusíme vzorec upravovat. Filtr v Excelu projde řádek po řádku a vyhodnotí jej sám. Tam, kde bude odpovídat hodnotě PRAVDA zapíše záznam do oblasti extrakce.

© 2007–2013 Miroslav Lorenc

Převzetí, publikování, kopírování, či jiné užití než pro osobní potřebu je možné pouze s písemným svolením autora.