Excelentnφ kousky

Jak nßzev napovφdß, tento dφl se bude v∞novat seznam∙m. Pod pojmem seznamy si v tutu chvφli p°edstavte oblast dat tvo°enou sadou zßznam∙ v °ßdcφch pod sebou. Mßte-li nap°φklad svou domßcφ hudebnφ sbφrku, pak zßznamem je konkrΘtnφ album s polo₧kami interpret, nßzev alba, hudebnφ ₧ßnr a rok vydßnφ umφst∞n²mi v jednotliv²ch sloupcφch. O dlouhΘm seznamu m∙₧eme mluvit takΘ jako o databßzi.
NaÜe seznamy budete jist∞ chtφt t°idit, vyhledßvat v nich, zjiÜ¥ovat duplicitnφ zßznamy apod. Dr₧te se prosφm nßsledujφcφch rad:

• Seznamy by zpravidla m∞ly mφt svou hlaviΦku, tj. vymezen prvnφ °ßdek na popisky sloupc∙. Do tohoto °ßdku se pak umφs¥ujφ ovlßdacφ prvky pro filtrovßnφ. Na hlaviΦky je t°eba v₧dy brßt ohled ve funkcφch a nßstrojφch Excelu, stejn∞ jako p°i exportu dat.
• Dφky hlaviΦce nelze pou₧φt Φφsla °ßdk∙ za urΦujφcφ pro po°adφ zßznamu (prvnφ zßznam nele₧φ v prvnφm °ßdku). V p°φpad∞ nutnosti vymezte prvnφ sloupec seznamu pro Φφselnφk.
• V seznamech zapome≥te na slouΦenΘ bu≥ky. Pou₧φvßte-li je v popiscφch sloupc∙, nep°i°azujte je bezprost°edn∞ k prvnφmu zßznamu. Tip: Vyberte oblast bun∞k, kterΘ chcete p°ekr²t textem v prvnφ bu≥ce. Zvolte Formßt / Bu≥ky / karta Zarovnßnφ / Vodorovn∞: na st°ed v²b∞ru. Tento postup uÜet°φ °adu trßpenφ se slouΦen²mi bu≥kami.
• Dbejte na to, aby polo₧ky tΘho₧ sloupce m∞ly stejn² charakter (Φφslo, text, pravdivostnφ hodnota, zßpis datumu, ...). VhodnΘ je nap°φklad i stanovit maximßlnφ dΘlku textovΘ polo₧ky.
• Nenφ-li to nezbytn∞ nutnΘ, na list se seznamem neumφs¥ujte ji₧ nic jinΘho. P°φpadnΘ vzorce zpracovßvajφcφ filtrovanß data umφs¥ujte nad seznam (op∞t vyvstßvß pot°eba pou₧φt prvnφ sloupec seznamu jako Φφselnφk, nebo¥ dochßzφ k posunu poΦßtku seznamu).
• PrßzdnΘ °ßdky mezi zßznamy jsou nep°φpustnΘ!
• Polo₧ky seznamu by m∞ly bφt fixnφ (neobsahovat vzorce). Vyvarujte se takΘ zalamovßnφ °ßdk∙ (Alt+Enter) a extrΘmn∞ dlouh²ch text∙.

Z osmdesßti procent seznamy dotvß°φme tak, ₧e novΘ zßznamy dopl≥ujeme na konec seznamu. Pak Φasto vyvstane pot°eba data abecedn∞ se°adit. K tomu postaΦφ umφstit kurzor do seznamu a zvolit Data / Se°adit. Pozor! Ikona "AZ" (objevuje se na panelu nßstroj∙ Standardnφ) je ikonou Se°adit vzestupn∞. DoporuΦuji ji zam∞nit za ikonu Se°adit (Nßstroje / Vlastnφ / karta P°φkazy, kategorie: Data), kterß mß sice stejn² vzhled, ale odliÜnou funkci (dßvß na v²b∞r kritΘrium °azenφ). Zmφn∞nß ikona Se°adit vzestupn∞ toti₧ bez vyptßvßnφ °adφ zßznamy podle aktivnφ bu≥ky v²b∞ru, a to vzestupn∞. Vznikß tak dojem, ₧e jsme spustili n∞co, nad Φφm nemßme kontrolu.

Poznßmka
Stejn∞ neÜ¥astn∞ (tent²₧ vzhled ikony, jinß funkce) jako ikona °azenφ je zpracovßna i ikona Nov² versus Nov².... nebo t°eba ikony pro tisk. K Φemu Excel mß v databßzi p°es 1000 ikon, kdy₧ pou₧φvß ty samΘ pro rozdφlnΘ funkce? Ach jo...

Tip I.
Nezßle₧φ na tom jak se zßznamy v Excelu manipulujete. Chcete-li si ov∞°it, ₧e pohybem dat jste polo₧ky zßznam∙ neposunuli (co₧ m∙₧e nap°φklad u polo₧ek bankovnφch ·Φt∙ klient∙ zp∙sobit p∞kn² poprask), vyberte pseudonßhodnΘ zßznamy a barevn∞ je zv²razn∞te. Po manipulaci tyto zßznamy zkontrolujte. Barevn∞ zv²razn∞nΘ polo₧ky samoz°ejm∞ nesmφ doznat posunu ve svislΘm sm∞ru. Zßrove≥ musφ sed∞t poΦet zßznam∙ p°ed a po manipulaci.

Tip II.
Neset°φd∞nß data mohou zp∙sobit problΘmy u vyhledßvacφch funkcφ nebo p°i statistickΘm vyhodnocovßnφ. P°esto se n∞kdy setkßte s po₧adavkem uspo°ßdat zßznamy po skupinßch (pohromad∞ TV, ledniΦky, walkmany, ...). Je-li takov² seznam vytvo°en a necht∞n∞ se°azen abecedn∞, m∙₧ete toho p°i pozd∞jÜφ prßci litovat. Proto je vhodnΘ vytvo°it sloupec, kter² narozdφl od doporuΦovanΘho Φφselnφku nebude tvo°en rostoucφ posloupnostφ (1, 2, ..., n-1, n), ale u ka₧dΘho zßznamu bude bu∩ hodnota po°adφ nebo hodnota spoleΦnß pro celou skupinu. Data se pak budou °adit podle tohoto sloupce.
Alternativn∞ lze pou₧φt klφΦ °azenφ (Data / Se°adit / tlaΦφtko Mo₧nosti). Roletka vyu₧φvß seznam∙ z dialogu Nßstroje / Mo₧nosti / karta Seznamy. Posloupnost °azenφ m∙₧e b²t v tomto p°φpad∞ postavena jen na zßklad∞ textov²ch °et∞zc∙.

Automatick² filtr

Automatick² filtr je prvnφ volbou, kterß nßm pom∙₧e t°idit data. Najdeme ji pod menu Data/ Filtr / Automatick² filtr a ne₧ ji pou₧ijeme, musφme umφstit kurzor dovnit° seznamu. Excel vyhledß prvnφ °ßdek oblasti (naÜe p°ipravenß hlaviΦka) a umφstφ do n∞j sadu ovlßdacφch prvk∙ (rozbalovacφch nabφdek). Stejn²m zp∙sobem v menu volbu deaktivujeme.

Klepnutφm na Üipku rozbalovacφho seznamu v jednom ze sloupc∙ u₧ pak definujeme filtrovßnφ dat onoho sloupce. Dodßvßm, ₧e filtrovßnφm data neztrßcφme, pouze ta necht∞nß skr²vßme.

V∞zte, ₧e:

• Volba (vÜe) deaktivuje filtr pro dan² sloupec.
• Volba (prvnφch 10...) umo₧≥uje filtraci prvnφch Φi poslednφch "x" polo₧ek, resp. prvnφch Φi poslednφch "x" procent polo₧ek (viz roletky dialogu). Za "prvnφ" se p°itom berou hodnoty nejv∞tÜφ, tj. na ΦφselnΘ ose ty nejvφce vpravo. Volba kupodivu ignoruje textovΘ polo₧ky, p°esto₧e i znaky lze t°φdit.
• Volbou (vlastnφ...) definujeme v zobrazenΘm dialogu jednu Φi dv∞ vlastnφ podmφnky. V prav²ch okΘnkßch p°itom nemusφme vybφrat pouze ze seznamu, pro textovΘ polo₧ky smφme pou₧φt i vlastnφ "Üablonky" se zßstupn²mi symboly (* ... cokoli, ? ... jeden znak). Nßsledujφcφ ukßzka nap°φklad vyfiltruje vÜechny zßznamy, jejich polo₧ka v danΘm sloupci zaΦφnß pφsmenem "L" a konΦφ "ß" (LijßΦkovß, Lah∙dkovß).

• V²b∞rem nßzvu konkrΘtnφ polo₧ky filtrujeme vÜechny bu≥ky sloupce obsahujφcφ tento nßzev.
• Volby (prßzdnΘ) a (neprßzdnΘ) filtrujφ zßznamy s nevypln∞n²mi polo₧kami (jen tehdy se takΘ zobrazujφ).
• Chcete-li resetovat kritΘria filtrovßnφ pro vÜechny sloupce narßz, zvolte Data / Filtr / Zobrazit vÜe.

Poznßmka
Trojφ kliknutφ "tam" a trojφ kliknutφ "zp∞t" je zvlßÜt∞ pro experimentovßnφ dosti otravnΘ. Do panelu nßstroj∙ Standardnφ m∙₧ete sice lehce p°idat ikonu Automatick² filtr (trycht²° se znakem rovnß se, viz Nßstroje / Vlastnφ / karta P°φkazy, kategorie: Data a obrßzek v²Üe), ale i zde se nejednß o funkci ekvivalentnφ volb∞ z menu. Klepnutφ na tuto ikonu zp∙sobφ aktivaci filtru s kritΘriem postaven²m na obsahu aktußlnφ bu≥ky. Tu "sprßvnou" ikonu ale v nabφdce nenajdete. Jedinou mo₧nostφ je p°eta₧enφ nabφdky Data/ Filtr / Automatick² filtr za dr₧enφ klßvesy Ctrl na viditeln∞jÜφ mφsto. I proto jsem nechal vzniknout specißln∞ pro tento seÜit panelu nßstroj∙ Filtry. Jeho vytvo°enφ po otev°enφ seÜitu (a smazanφ po uzav°enφ) je zajiÜt∞no voln∞ p°φstupn²m k≤dem VBA (viz Alt+F11, objekt ThisWorkbook, procedury Workbook_Open, Workbook_BeforeClose).

Poznßmka
Mßte-li v Excelu XP na panelu nßstroj∙ vyta₧enou ikonu "Odstranit °ßdek" ( viz Nßstroje / Vlastnφ / karta P°φkazy, kategorie: ┌pravy), stane se, ₧e po aplikaci filtru "(prßzdnΘ)" a ·myslu odstranit filtrovanΘ zßznamy bude tato ikona nep°φstupnß. V tom p°φpad∞ u₧ijte pravΘ tlaΦφtko myÜi a volbu Odstranit °ßdky.

Dodatek
Excel transformuje ka₧dΘ sl∙vko PRAVDA a NEPRAVDA, kterΘ mu v listu zadßte, na pravdivostnφ hodnotu, a tak se m∙₧ete setkat s tφm, ₧e p°i pou₧itφ automatickΘho filtru (nap°φklad p°i vytvß°enφ seznamu funkcφ Excelu) najdete tyto sl∙vka Üpatn∞ abecedn∞ za°azenß (nachßzφ se na konci seznamu). V seÜitu najdete ukßzku toho, jak z toho ven.

RozÜφ°en² filtr

RozÜφ°en² filtr, jak nßzev napovφdß, obsahuje vφce mo₧nostφ pro filtrovßnφ dat. KritΘria se p°itom definujφ p°φmo na listu a to tφm zp∙sobem, ₧e pro dan² sloupec vytvo°φme na jinΘm mφst∞ listu (zpravidla nad vlastnφm seznamem s odstupem n∞kolika °ßdk∙) stejnou hlaviΦku, pod nφ₧ zapφÜeme kritΘrium. Nßsledn∞ umφstφme kurzor do seznamu a vyvolßme dialog z menu Data / Filtr / RozÜφ°en² filtr. . Filtrovanß data m∙₧eme bu∩ zpracovat p°φmo v seznamu, nebo v²sledek p°enΘst jinam (volba Kopφrovat jinam). Do Oblasti seznamu je takΘ zahrnuta hlaviΦka seznamu. Vypln∞n² dialog ukazuje nßsledujφcφ obrßzek.

• Jedna podmφnka pro jeden sloupec.
• Vφce podmφnek pro jeden sloupec.
• Kombinace p°edchßzejφcφch.

P°itom platφ nßsledujφcφ:

• Podmφnky zapsanΘ ve stejnΘm °ßdku jsou navzßjem ve vztahu odpovφdajφmu logickΘ spojce "A (AND)".
• Podmφnky zapsanΘ v r∙zn²ch °ßdcφch jsou navzßjem ve vztahu odpovφdajφmu logickΘ spojce "NEBO (OR)".

Z p°edchozφho vypl²vß, ₧e pokud pot°ebujeme dv∞ podmφnky pro jeden sloupec ve vztahu "A (AND)", museli bychom mφt dv∞ hlaviΦky stejnΘho nßzvu, pod nimi₧ budou v jednom °ßdku zapsßny podmφnky filtrovßnφ. A tak to takΘ funguje.

Tento p°φklad by bylo mo₧nΘ °eÜit i automatick²m filtrem. Ten by nßm ovÜem neumo₧nil zadat vφce jak dv∞ podmφnky pro jeden sloupec.

Do t°etice si ukß₧eme kombinovan² p°φklad, kdy mß platit:

A) Hodnota zßznamu ve sloupci "A" je menÜφ ne₧ 1500 nebo
B) zßznam ve sloupci "Typ desky" neobsahuje pφsmeno "V" a souΦasn∞ je hodnota tΘho₧ zßznamu ve sloupci "A" v∞tÜφ ne₧ 1600.

Ukßzky n∞kolika zßpis∙ obecn²ch podmφnek:

Je vid∞t, ₧e i rozÜφ°enΘ filtrovßnφ podporuje zßstupnΘ symboly hv∞zdiΦka a otaznφk. Pro deaktivaci filtru slou₧φ volba Data / Filtr / Zobrazit vÜe.

DopoΦφtßvanß kritΘria

Podmφnku filtrovßnφ tΘto metody tvo°φ vzorec, jeho₧ v²sledkem je hodnota PRAVDA Φi NEPRAVDA. Pou₧itφ nejlΘpe osv∞tlφ p°φklad:

Vzorec viditeln² v °ßdk∙ vzorc∙ nßle₧φ bu≥ce D3 a sv²m charakterem je podobn² maticovΘmu vzorci. Odkazuje se na Φßst seznamu spadajφcφ pod hlaviΦku "Body" (alternativn∞ m∙₧e ukazovat do prvnφ bu≥ky tohoto sloupce). V tuto chvφli nenφ podstatnΘ, ₧e vracφ chybovou hodnotu #Nßzev, filtr ji zpracuje sprßvn∞. V dialogu je pak nutnΘ uva₧ovat i hlaviΦku kritΘria, je₧ nese libovoln² nßzev, ovÜem odliÜn² od vÜech hlaviΦek vlastnφho seznamu. PovÜimn∞te si takΘ komentß°e pole "Oblast seznamu:". Metodu dopoΦφtßvßnφ je mo₧nΘ kombinovat s p°edchozφmi.

Funkce pro filtrovanß data

Funkce "kovanß" na filtrovanß data nese nßzev SUBTOTAL a dokß₧e vrßtit vφcero ·daj∙ o filtrovan²ch datech (souΦet, souΦin, b∞₧nß popisnß statistika). Vφce se o nφ dozvφte v nßpov∞d∞. Hodit se mohou i tzv. databßzovΘ funkce, mezi kterΘ pat°φ DMAX, DMIN, DSUMA, DPO╚ET, DPR┘M╠R, DSOU╚IN a dalÜφ (zahrnuty i funkce statistickΘ), jejich₧ pou₧itφ je naznaΦeno zde:

Souhrny

Ka₧d² sb∞ratel mß Φas od Φasu pot°ebu poΦφtat a Ükatulkovat ty svΘ "kousky". K t∞mto ·Φel∙m slou₧φ nßstroj Excelu, kter² se skr²vß v menu Data / Souhrny. StaΦφ mφt seznam podobnΘho charakteru, jako je nφ₧e zobrazen², kurzor ponechat v oblasti seznamu a aplikovat zmφn∞nΘ Souhrny. Obrßzky dokumentujφ stav "p°ed" a "po".

Excel vytvo°φ stromovou strukturu dat, jejφ₧ zobrazenφ je mo₧nΘ ovlivnit globßln∞ zm∞nou zobrazenφ celΘ ·rovn∞ (zde tlaΦφtka 1, 2, 3), nebo individußln∞ (tlaΦφtka plus a mφnus). Seznam m∙₧ete vrßtit do p∙vodnφho stavu volbou Data / Souhrny / Odstranit vÜe.

Unikßtnφmi (jedineΦn²mi, originßlnφmi) rozum∞jte takovΘ polo₧ky, resp. celΘ zßznamy, kterΘ se vyskytujφ v seznamu jen jednou. Duplicitnφ polo₧ky (zßznamy) jsou takovΘ, kterΘ se vyskytujφ v seznamu vφcekrßt. T∞₧ko ale urΦφme, co je v p°φpad∞ dvou stejn²ch zßpis∙ originßl a co kopie. Dohoda neexistuje, postavit se k problΘmu m∙₧eme dv∞ma zp∙soby:

• Originßlnφ je ten zßpis v listu, kter² se vyskytuje jako prvnφ ve sm∞ru zleva doprava a shora dol∙ ("Z").
• Originßlnφ je ten zßpis, kter² se na listu vyskytl d°φve ("Kdo d°φv p°ijde, ten d°φv mele.").

Ve skuteΦnosti nßm ale nejde ani tak o to rozliÜit, co je originßl a co kopie, n²br₧ chceme eleminovat duplicitnφ hodnoty, resp. separovat unikßtnφ hodnoty.

Jak na duplikßty

Prvnφ postup vyu₧φvß vzorce listu a automatick² filtr.

Vzorec zobrazen² v °ßdku vzorc∙ je zkopφrovßn z bu≥ky B2 ta₧enφm do bun∞k nφ₧e. Tak vizußln∞ vyt°φdφme originßly. Dßle nastoupφ automatick² filtr s volbou (neprßzdnΘ), kter² seskupφ originßlnφ zßznamy. Ty potΘ m∙₧eme kopφrovßnφm p°enΘst jinam.

Druh² postup vychßzφ z rozÜφ°enΘho filtru a jeho podstata tkvφ ve ... ne neprozradφm, pokud jste byli pozornφ, pak u₧ princip vφte, pokud ne, vra¥te se na tΘto strßnce zp∞t a projd∞te znovu vÜechny obrßzky. Tak :-)

Ukßzka je °eÜenφm kombinujφcφm rozÜφ°en² filtr s VBA. K≤d je op∞t voln∞ p°φstupn² a makra pro tlaΦφtka najdete pod objektem List1 (originaly II). Pou₧ita je i pojmenovanß oblast.

DalÜφ obrßzek ukazuje p°ehled vzorc∙ pro prßci s duplicitnφmi polo₧kami a s vyjφmkou poslednφho sloupce se jednß o vzorce listu postavenΘ na funkci COUNTIF.

A jeÜt∞ jedna technika....

DalÜφ p°φklad °eÜφ problematiku od podlahy s pomocφ vlastnφ funkce ORIGINALY. Jednß se o funkci, kterß vracφ matici unikßtnφch hodnot, navφc set°φd∞n²ch. V prvnφm kroku touto funkcφ zjistφme poΦet unikßtnφch hodnot, v kroku druhΘm vybereme poΦet bun∞k odpovφdajφcφ poΦtu unikßt∙ a zadßme maticov² vzorec pro v²pis t∞chto polo₧ek.

A tφm pro dneÜek konΦφme. P°φÜt∞ se budeme v∞novat ovlßdacφm prvk∙m na listu a ukß₧eme si °adu praktick²ch p°φklad∙.