čtvrtek 26. února 2015

Roční kalendář – 4. díl: Kontingenční tabulky

Během posledního měsíce jsme si ukázali, jak vypadá šablona kalendáře, jak ji vytvořit a jak ji naplnit daty. Postup to byl možná trošku krkolomný a tak se teď podíváme na daleko jednodušší postup, který s sebou ale přináší určitá úskalí.

Základní kontingenční tabulka


Začneme tak, že si vytvoříme zdrojovou tabulku, která bude obsahovat sloupce:
  • datum: bude to seznam jednotlivých dat, například data od 1. 1. 2015 do 31. 12. 2016,
  • rok: pomocí funkce =ROK zjistíme rok daného data,
  • měsíc: pomocí funkce =MĚSÍC zjistíme měsíc daného data,
  • den týdne: funkcí =DENTÝDNE získáme pořadové číslo dne týdne (1 pro pondělí, 7 pro neděli),
  • týden: funkce =WEEKNUM pomůže zjistit, do kterého týdne roku dané datum spadá.
Zdrojová tabulka
Vytvořenou tabulku použijeme jako zdroj pro vložení kontingenční tabulky, kterou sestavíme následovně:
  • filtry: rok – můžeme se tak snadno přepnout z roku 2015 na 2016,
  • řádky: měsíc a týden – jednotlivé měsíce budeme ještě členit po týdnech,
  • sloupce: den týdne – abychom dosáhli dnů pondělí až neděle,
  • hodnoty: součet ze sloupce datum – použijeme vlastní formát čísla d (zobrazí se tak pouze číslo dne).
Nezapomeňte se přepnout ve filtrech pouze na konkrétní rok. Pak dostanete velmi jednoduchý roční kalendář.
Základní kontingenční tabulka
Úpravy

V tabulce výše už jsme (na kartě Nástroje kontingenční tabulky: Návrh) odstranili Celkové součty pro řádky i sloupce, jelikož zde nedává smysl sčítat několik dat. Ze stejného důvodu jsme odstranili Souhrny.

Abychom dostali nějakou hezčí/smysluplnější tabulku, můžeme zdrojovou oblast pro tuto kontingenční tabulku rozšířit o název měsíce. K tomu využijeme funkci =ZVOLIT, která pak bude vypadat následovně:

=ZVOLIT([@měsíc];"leden";"únor";"březen";"duben";"květen";"červen";"červenec";"srpen";"září";"říjen";"listopad";"prosinec")
 (první argument odkazuje na sloupec měsíc v tabulce)

Nezapomeňte tento nově vytvořený sloupec přidat do řádků tabulky (objeví se tam tedy sloupec měsíc_název místo původního měsíc). Aby se změny projevily, musíte kontingenční tabulku aktualizovat (např. klávesovou zkratkou Alt + F5).
Aktualizace kontingenční tabulky

V záhlaví tabulky můžete přepsat čísla dnů týdnů na zkratky (po, út, st…), v řádcích pak bílou barvou "schovat" čísla týdnů. Nakonec skryjte přebytečné řádky, přidejte průřez na roky a dostanete profesionální výsledek.
Napojení a analýza dat

"Co když chci v šabloně kalendáře zobrazit nikoli dny, ale počet kusů prodaných pomerančů a jablek pro daný den?"

Zdrojovou tabulku budeme muset opět rozšířit o dva sloupce:
  • ovoce: uvádíme buď pomeranče nebo jablka,
  • prodáno: kolik kusů mrkve nebo jablek se v daný den prodalo.
Na konec tabulky pak přidáme samotná data.

V kontingenční tabulce nahradíme položku "Součet z datum" sloupcem Prodáno, do filtrů přidáme sloupec ovoce a máme na chvíli vystaráno. Brzy však narazíme na problém, když si vyfiltrujeme pouze jablka. Všimnete si, že jablka se v neděli vůbec neprodávají a tak ani kontingenční tabulka neděli nezobrazuje.

Tohle lze, byť poměrně krkolomě ošetřit ve zdrojových datech. Trik spočívá v tom, že do tabulky nebudete zadávat pouze dny, kdy se nějaké ovoce prodalo, ale všechny dny – ty bez prodejů s číslem 0 ve sloupci prodáno.
Po aktualizaci a opětovném "očištění" tabulky se již hodnoty zobrazují v pořádku. Pokud chcete, můžete opět zapnout Celkové součty a Souhrny, zde to už smysl dává. :-)
Stáhněte si zdrojový soubor.
Závěr

Vytváření kalendáře a analýza dat pomocí kontingenčních tabulek má své výhody i nevýhody. Pojďme se na ně tedy podívat.

Výhody
  • velmi snadné vytvoření kalendáře (hotovo do 10 minut)
  • snadná údržba (nejsou tu skoro žádné vzorce, pouze zdrojová a kontingenční tabulka)
  • variabilita v napojení na data (snadné přepínání mezi pomeranči, jablky)
  • snadné porovnávání více období (leden 2014 proti lednu 2015 apod.)
  • profesionální vzhled (celkové součty, použití průřezů)
Nevýhody:
  • složitejší příprava dat pro napojení
  • nelze vytvořit matici 4×3 měsíce
  • nutnost aktualizovat tabulku po změně zdrojových dat (platí ale pro kontingenční tabulky obecně, lze řešit pomocí maker)
Seznam dílů:
  • 1. díl: Šablona (5. 2. 2015)
    • Šablona pro roční kalendář s podmíněným formátováním na důležitá data
  • 2. díl: Jak to to funguje? (12. 2. 2015)
    • Jaké funkce jsou použité?
    • Co vlastně buňky zobrazují?
    • Jaké jsem použil podmíněné formátování?
  • 3. díl: Analýza dat v kalendáři (19. 2. 2015)
    • Jak na kalendář napojit jiná data?
  • 4. díl: Kontingenční tabulky (26. 2. 2015)
    • Jak vytvořit kalendář pomocí kontingenčních tabulek?
    • Jak jej napojit na data?
    • Jaké jsou výhody a nevýhody tohoto řešení?

pondělí 23. února 2015

Náhled na připravované Office 2016

Microsoft včera zveřejnil video, kde představuje nové Office 2016, které by měly vyjít v příštím roce. Z videa toho tolik patrné není, je to jenom stručný náhled, ale je vidět, že se opět trošku změnila nabídka pásu karet. Ta je mnohem jednodušší, na první pohled okleštěná o některé funkce a tlačítka jsou opět o něco větší.

Na další novinky si budeme muset ještě počkat.

Kterou funkci byste v nových Officech uvítali nejvíce? Co vám chybí?

sobota 21. února 2015

Rychlé vyhledávání z Excelu

Potřebujete z Excelu rychle vyhledat obsah buňky na internetu? Aplikace nabízí docela nenápadnou funkci.

Klepněte na buňku, která obsahuje text k vyhledání při držení klávesy Alt. Otevře se vám postranní panel, kde uvidíte náhled výsledků. Klepnutím na vybraný odkaz pak můžete otevřít výchozí internetový prohlížeč s danou stránkou.

Jako výchozí je nastavené vyhledávání přes vyhledávač Bing, na obecné dotazy to ale stačí. V rozbalovacím seznamu dále můžete vybrat vyhledávání slova přes Tezaurus a další.

čtvrtek 19. února 2015

Roční kalendář – 3. díl: Analýza dat v kalendáři

V předchozích dílech jsme se podívali na šablonu pro roční kalendář a na to, jak se dá vytvořit. Nyní si ukážeme, jak na takový kalendář jednoduše napojit libovolná denní data.

Představme si následující situaci:

Fima má v excelovské tabulce seznam dnů a ke každému datu uvedené denní tržby. Jelikož někdy ve firmě pracuje více pokladních, může se v některé dny objevovat i více zápisů. 

Struktura dat pak vypadá následovně.
Teď už potřebujeme naplnit kalendář celkovými denními tržbami. Začneme tak, že si zkopírujeme všechny sloupce kalendáře "bokem" (vedle kalendáře, případně na druhý list). Vznikne nám tak prázdná šablona, kam budeme moct vkládat vzorce a zobrazit tak data tržeb.
Abychom kalendář naplnili daty, využijeme oblíbené funkce listu SUMIFS. Dále pomocí funkce KDYŽ zamezíme, aby se nám zobrazovala nula v místech, které nepředstavuje žádné datum (například levá horní buňka v lednu, jelikož 1. 1. 2015 nebylo pondělí). Výsledný vzorec pak vypadá následovně.

=KDYŽ(I8=0;"";SUMIFS(data!$B$2:$B$637;data!$A$2:$A$637;kalendář!I8))

Na list přidáme vhodné podmíněné formátování a vznikne tak profesionální výsledek. Díky použitému formátování (nulové hodnoty šedě, barevná škála, zvýraznění důležitých hodnot) je interpretace dat velice snadná.

Na první pohled můžeme zaznamenat:
  • které dny vznikly nejvyšší tržby (v únoru, lednu a listopadu),
  • dovolenou (a tudíž nulové tržby) v dubnu,
  • zajímavou situaci v říjnu, kdy firma vykazovala poměrně vysoké hodnoty ve středu, ale prakticky žádné následující čtvrtky
  • a mnoho dalšího.
Stáhněte si tento soubor a vyzkoušejte si práci s kalendářem.

V posledním díle tohoto miniseriálu si ukážeme, jak vytvořit a naplnit kalendář daty ještě jednodušeji. Je to mnohem jednodušší řešení, na které jsem přišel před nedávnem. Má určité nevýhody, ale nabízí i něco navíc. Jakou formou budeme kalendář tvořit, to si zatím nechám pro sebe. Nechte se překvapit. :-)

Seznam dílů:
  • 1. díl: Šablona (5. 2. 2015)
    • Šablona pro roční kalendář s podmíněným formátováním na důležitá data
  • 2. díl: Jak to to funguje? (12. 2. 2015)
    • Jaké funkce jsou použité?
    • Co vlastně buňky zobrazují?
    • Jaké jsem použil podmíněné formátování?
  • 3. díl: Analýza dat v kalendáři (19. 2. 2015)
    • Jak na kalendář napojit jiná data?
  • 4. díl: Kontingenční tabulky (26. 2. 2015)
    • Jak vytvořit kalendář pomocí kontingenčních tabulek?
    • Jak jej napojit na data?
    • Jaké jsou výhody a nevýhody tohoto řešení?

pondělí 16. února 2015

Nesmyslné koláčové grafy

Myslel jsem si, že už jsem o koláčových grafech napsal dost. To co jsem ale nedávno viděl na serveru F1news.cz mě zarazilo natolik, že se o tom nemůžu nezmínit.

Původní graf

Originální graf odkazuje na letošní testování monopostů F1 před novou sezónou. Autor se snažil znázornit, kolik výrobce pneumatik Pirelli dovezl sad různých směsí a kolik jich týmy v průběhu testů ve španělském Jezeru skutečně využili.

Co je špatně?
  • Typ grafu: koláče patří do kuchyně, ne do grafů a reportů
  • Barvy sice odkazují na barevné označní pneumatik, ale jako celej působí nesourodě a nepřehledně.
  • Graf přímo vybízí k nějakému porovnání, zjištění, kolik z jakých směsí bylo využito. To ale na dvojici grafů není zrovna snadné. Na první pohled tak například není zřejmé, že týmy využili skoro všechny sady zimní směsi. Ani není zřejmé, že z "tvrdých sad" bylo využito jen něco přes polovinu.

Nový graf a co se zlepšilo

  • Počet grafů. :-) Nejedná se o dva samostatná grafy, ale o jeden.
  • Díky tomu je porovnávání hodnot mnohem jednodušší. 
  • Popisky hodnot
  • Barevně je graf jednoduchý a díky tomu čitelný.
  • Typ grafu: zobrazené "teploměry" jsou pro zobrazení daného problému nejlepším řešením.
Potřebujete poradit, jak graf nebo tabulku vytvořit? Napište mi. :-)

sobota 14. února 2015

Našeptávač v Excelu

Přišel mi od kamaráda zajímavý dotaz.

Umí Excel našeptávání? Potřeboval bych udělat políčko, které bude našeptávat. Chtěl udělat vlastní kalkulátor cen jízdenek a potřebuju tam mít políčka pro zadání místa odjezdu a příjezdu. Chci, ať mi to trochu našeptává, když začnu psát "Pra" tak at to našeptává někde ze seznamu "Praha, hlavní nádraží", "Praha, Smíchov" atd.
výsledný našeptávač

Jak na to?

Začneme tím, že si vytvoříme zdrojovou tabulku, která bude sloužit jako seznam stanic, ze kterých budeme vybírat. Oblast pojmenujeme jako data. Dále se v Excelu přesuneme na kartu Vývojář.

Pokud tuto kartu nemáte zobrazenou, postupujte následovně.
  • Pravým tlačítkem klepněte kamkoli na pás karet a zvolte možnost "Přizpůsobit pás karet".
  • V pravé části zaškrtněte kartu Vývojář a potvrďte tlačítkem OK.

Na této kartě klepneme na tlačítko Vložit a vybereme ovládací prvek ActiveX "Pole se seznamem". To vložíme kamkoli na list.

Na vložený objekt klepneme pravým tlačítkem a vybereme Vlastnosti. Otevře se nové okno, kde je potřeba definovat dvě vlastnosti.
  • ListFillRange označuje oblast, odkud bude výběrové pole čerpat data. Uvedeme tedy pojmenovanou oblast data.
  • LinkedCell je odkaz na buňku, která pak zobrazí výsledek, resp. právě vybranou položku (tak, abychom s ní mohli dále pracovat, odkazovat se na ni ve vzorcích atd.). Pozor, nelze buňku vybrat, ale je třeba vepsat adresu buňky ručně (např. D12, odjezd apod.).

Nyní už stačí na kartě vývojář odklepnout tlačítko Režim návrhu a můžete začít pracovat s našeptávačem.
LinkedCell můžete schovat za výběrové pole, datovou oblast můžete skrýt. Pomocí vhodného formátování dosáhnete opravdu profesionálních výsledků.

Stáhněte si soubor, kde už jsou našeptávače připravené.

čtvrtek 12. února 2015

Roční kalendář – 2. díl: Jak to funguje?

Před týdnem jsem představil šablonu ročního kalendáře, kterou jsem sám vytvořil. Jak jsem psal minule, vlastní šablonu jsem vytvářel hlavně proto, abych ji měl více pod kontrolou. Ostatním kalendářům jsem většinou pořádně nerozumněl a podrobnější vysvětlení od autorů scházelo.

Abych zabránil podobnému osudu u mého kalendáře, podíváme se nyní, jak celý kalendář funguje.

Než ale začnete číst, důrazně doporučuji, abyste si stáhli zdrojový soubor, na kterém jsem fungování kalendáře demonstroval.

Čeho chceme dosáhnout?

Potřebujeme vytvořit dynamický roční kalendář, kde jednotlivé buňky budou obsahovat datum daného dne (čili např. 1. 1. 2015, 2. 1. 2012, 3. 1. 2015 atd.). Samozřejmě nabízí se i varianta, kde bychom buňky naplnili "pouze" čísly 1, 2, 3 atd., první varianta je však univerzálnější a má více možných využití do budoucna. To si ukážeme ve 3. díle seriálu.

Teoretický postup

Vytvoříme si nejprve jednoduchou tabulku pro jeden měsíc (např. leden). Vzhledem k tomu, že tabulka bude dynamická (bude obsahovat vzorce), můžeme ji pak zkopírovat pro zbylých jedenáct měsíců.

V dalším kroku pak na tabulku aplikujeme vhodné formátování (včetně podmíněného).

Vytvoření tabulky
  • Rok obsahuje dvanáct měsíců, 52 týdnů. 
  • Týdny mají 7 dní. 
  • Každý měsíc, kromě února, se může "rozpadnout" až do šesti týdnů (např. březen 2015).
Základní (nevyplněná) tabulka pro jeden měsíc pak bude vypadat takto. (K zeleně označným buňkám se ještě vrátíme.)
Teď už potřebujeme "jenom" tabulku naplnit daty. Teorie je snadná. Leden 2015 začínal ve čtvrtek, do prvního týdne tak potřebujeme vepsat hodnotu 1. 1. 2015 a následně buňku naformátovat tak, aby nám zůstala zobrazená pouze jednička.

Abychom toho dosáhli, vytvoříme si nejprve pomocnou tabulku, která bude vypadat následovně.

  • Sloupec A: obsahuje data zvoleného roku. To zajistíme tak, že do buňky A2 vepíšeme vzorec =DATUM(G1;1;1) (první den, prvního měsíce, roku z buňky G1 – 2015). Buňka A3 přičítá k buňce A2 jeden den (=A2+1), další buňky následují. Tak vyplníme všechny dny roku.
  • Sloupec B: určuje, co za den v týdnu (pondělí, úterý...) je hodnota ze sloupce A. =DENTÝDNE(A2;2)
  • Sloupec C: určuje, v kolikátém týdnu den je =WEEKNUM(A2;2)
  • Sloupec D: pro první měsíc (leden) vrací hodnotu ze sloupce C, pro zbylé měsíce (od února do prosince) určí týden posledního dne předcházejícího měsíce (v únoru zjistí, že 31. leden byl v pátém týdnu). =KDYŽ(E2=1;C2;WEEKNUM(DATUM(ROK(A2);E2;1)-1;2))
  • Sloupec E: určuje, v kolikátém měsíci den je =MĚSÍC(A2)
Teď ještě v původní tabulce (kterou jsme tvořili jako první) upravíme zelená čísla označující týdny. První buňka nám vrátí číslo týdne prvního dne z vybraného měsíce, další buňky vždy jeden týden přičtou.

Základ máme připravený a můžeme se pustit do naplnění buněk samotné tabulky (červěne ohraničená část). K tomu využijeme funkci SOUČIN.SKALÁRNÍ. Ta nám pro každou buňku umožní najít datum z pomocné tabulky na základě zvolených kritérií. Vzorec vypadá následovně:

=SOUČIN.SKALÁRNÍ(($B$1:$B$367=L$1)*($C$1:$C$367=$H2)*($E$1:$E$367=$H$1);$A$1:$A$367)

Vše asi bude nejlépe patrné z následujícího obrázku (případně souboru, který jste si stáhli).
Co vzorec vlastně dělá? V případě prvního ledna hledá, ve kterém řádku pomocné tabulky je kombinace: prvního měsíce (ledna), čtvrtého dne týdne (čtvrtka) a prvního týdne. Pro tento řádek pak vrátí hodnotu ze sloupce A (výsledné datum).

No a to je celá alchymie. :-) Pokud jste neudělali chybu, dostaneme výsledek pro jeden měsíc. Následně stačí zkopírovat tabulky měsíců pro zbylých jedenáct měsíců, skrýt nežádoucí sloupce a řádky a tabulku vhodně naformátovat.

Formátování tabulky

(Je zobrazené pouze v původním souboru.)

Výsledný kalendář obsahuje několik formátů. Základem je formát čísla buňky (aby se nezobrazoval celý datum, ale pouze číslo dne v daném měsíci). Dále je v hojné míře využité podmíněné formátování

Jelikož výše zmíněná funkce SOUČIN.SKALÁRNÍ vrací hodnotu 0 (nula) v případě, že danou kombinaci nenalezne, je třeba to ošetřit. Je tak použito podmíněné formátování, které formátuje buňky jinak, pokud je buňka rovna nule.

Další podmíněné formátování hlídá (pomocí funkce SVYHLEDAT), zda se datum nenachází mezi důležitými daty, které si uživatel definuje v pravé části.

Závěr

Na první pohled či přečtení z toho máte určitě hokej a jde vám ze všech vzorců a logických posloupností hlava kolem. :-) V dalším díle se však podíváme, jaké další možnosti takto vytvořený kalendář nabízí a zdůvodníme si, proč jsme některé věci dělali tak či jinak...

Seznam dílů:
  • 1. díl: Šablona (5. 2. 2015)
    • Šablona pro roční kalendář s podmíněným formátováním na důležitá data
  • 2. díl: Jak to to funguje? (12. 2. 2015)
    • Jaké funkce jsou použité?
    • Co vlastně buňky zobrazují?
    • Jaké jsem použil podmíněné formátování?
  • 3. díl: Analýza dat v kalendáři (19. 2. 2015)
    • Jak na kalendář napojit jiná data?
  • 4. díl: Kontingenční tabulky (26. 2. 2015)
    • Jak vytvořit kalendář pomocí kontingenčních tabulek?
    • Jak jej napojit na data?
    • Jaké jsou výhody a nevýhody tohoto řešení?

čtvrtek 5. února 2015

Roční kalendář – 1. díl: Šablona

Různě po internetu určitě najdete spoustu českých i cizojazyčných dynamických šablon pro roční kalendář.

Problém je, že sice fungují, ale nikdy jsem pořádně nechápal jak. Proto jsem vytvořil dynamický roční kalendář po svém.
Stáhnout (*.xlsx)

V dalších dílech se podíváme na kalendář podrobněji.

Seznam dílů:
  • 1. díl: Šablona (5. 2. 2015)
    • Šablona pro roční kalendář s podmíněným formátováním na důležitá data
  • 2. díl: Jak to to funguje? (12. 2. 2015)
    • Jaké funkce jsou použité?
    • Co vlastně buňky zobrazují?
    • Jaké jsem použil podmíněné formátování?
  • 3. díl: Analýza dat v kalendáři (19. 2. 2015)
    • Jak na kalendář napojit jiná data?
  • 4. díl: Kontingenční tabulky (26. 2. 2015)
    • Jak vytvořit kalendář pomocí kontingenčních tabulek?
    • Jak jej napojit na data?
    • Jaké jsou výhody a nevýhody tohoto řešení?

neděle 1. února 2015

Ukázka špatné tabulky

Koncem ledna se na webu mobilmania.cz objevil článek o prohřešcích řidičů za jízdy. Článek popisoval české šoféry a jejich chování za volantem. Výsledky jako takové ani tolik nepřekvapují – nikdo z nás není svatoušek a nějaká ta nadávka uletí každému.

Daleko víc alarmující byla ale tabulka, která shrnovala výsledky průzkumu.
Zdroj: mobilmania.cz
Co je tu špatně?

Tabulka na jednom místě zobrazuje výsledky na jednom místě a líný čtenář tak nemusí číst celý článek, aby si udělal dojem o řidičích na českých silnicích. Při bližším pohledu na tabulku zjistíme ale několik problémů, které činí tabulku nepřehlednou a interpretaci dat komplikovanou.
  • Data jsou zobrazena"obráceně". Nebylo by lepší čtenářům říct, jaké procento řidičů za jízdy telefonuje namísto sdělení, kolik z nich nikdy netelefonovalo?
  • Název tabulky (Co jsem jako řidič nikdy neudělal/a) je v záhlaví tabulky, záhlaví samotné (tudíž nadpisy sloupců Činnost a % dotazovaných) jsou mezi samotnými daty.
  • Zarovnání. Hodnoty v druhém sloupci nejsou zarovnané (jednotky pod desítkami v případě řádku nadávek).
  • Data nejsou seřazená.
Jak vytvořit tabulku lépe?

Přestože se na tomto blogu věnuji primárně špatným grafům (a jejich lepším variantám), nemám nic proti tabulkám. Jenže i tabulka lze udělat vkusně. Zde jsou tři jednoduché ukázky.
  • Všechny tabulky využívají podmíněné formátování, které všechny moderní tabulkové editory nabízejí.
  • Tabulky mají správný formát sloupce hodnot.
  • Data již nejsou invertovaná.
  • V případě druhé tabulky jsem hodnoty seřadil pro co nejsnažší čtení. (Ale jak je vidět u zbylých dvou, není to to klíčové.)
Jak data zobrazit pomocí grafu?

Následující případ je jedním z mála, kde použití koláčového grafu nevadí, jinak se koláčům, prosím, velkým obloukem vyhněte. :-) Následující sada výsečových grafů zobrazuje data jednoduše a přehledně.
  • Výsečové grafy zobrazují vždy pouze dvě hodnoty ("ano/ne"). Grafy zobrazují hříšníky rudou barvou.
  • Použití jednoduchých ikon zpříjemní čtení.

Závěr

Samozřejmě chápu, že původní tabulka nebyla tvořená v tabulkovém editoru a sloužila jenom jako doplnění docela zajímavého článku. Nicméně i přesto by měla sloužit jako ukázka toho, jak tabulku nedělat. Rozhodně to není příklad, jakým byste se měli řídit při vytváření tabulek například do bakalářské nebo diplomové práce.

Potřebujete poradit, jak grafy nebo tabulky vytvořit? Napište mi. :-)