Dashboard jak vytvořit v Excel

Úvodem

Podle reakcí na Facebooku to vypadá, že o tvorbu dashboardu je zájem, který předčil má očekávaní (jednoho/dvou lijků), při počtu 10 jsem se rozhodl napsat tento článek, ať na praktickém příkladu ukážu jak na první ukázkový dashboard.

Kapitoly

Z důvodu přehlednosti a pro případné další doplnění je článek rozdělen do následujících kapitol:

• Cíl dashboardu
• Vstupní data
• Výpočty ze vstupních dat
• Tvorba dynamického grafu
• Grafické vylepšení - loga
• Další možná vylepšení
• Ke stažení - jako vždy zdarma

Byl jsem názoru, že o dashboardy (inteligentní přehledové vizualizace) nebude zájem, stačilo jen:

• Vědět co chcete vytvořit - víte sami, případně vám to řekne šéf - Check
• Zpracovat tabulku - například využitím funkcí SUBTOTAL, KDYŽ, SUMIFS, SVYHLEDAT, POSUN, případně využít Rozšířený filtr, propojené seznamy - což už určitě umíte - Check
• Vytvořit graf - ať už základní výsečový, nebo sloupcový, klidně i burzovní, pokud standartní nevyhovují, dávám k dispozici 40. netradičních grafů, například oblíbený vodopádový - což také umíte - Check
• Vylepšit graf na dynamický pokud nevyhovují statické grafy, můžete si vytvořit dynamické, kdy si zobrazíte (zvýrazníte) jen zvolenou datovou řadu - také už umíte - Check
• Vylepšit design například využitím podmíněného formátování, které můžete opět upravit k obrazu svému mraky praktických příkladů - takže také umíte - Check
• Doplnit o logo firmy - vložit SmartArt a případně jiný obrázek - Check
• A je hotovo ;)

Poznámka

Předpokládám alespoň základní znalosti Excel, ale pro jistotu se budu u jednotlivých kapitol odkazovat na podrobnější články, kde je problém podrobněji vyřešen.

Cíl dashboardu

Fiktivní firma - modelový příklad

Máte za úkol prezentovat měsíční příjmy a výdaje, za poslední tři roky (lze použít i na vaše domácí finance). Tak aby prezentována data byla interaktivní. Bude se předvádět novým akcionářům, prezentace dat by měla vypadat dobře tj. mít i logo firmy a odkaz na web atd. Tj. vytvoříte interaktivní dashboard.

Poznámky - tužka a papír

Osobně si kreslím a dělám poznámky nejprve na papír. Jak nákres může vypadat:

V této části nepotřebujete Excel. Sedout si k Excel a začít něco tvořit je mnohdy kontraproduktivní (nadřete se zbytečně). Musíte mít rozmyšleno co je cílem (jak se k němu dostat). Umět si sami zformulovat cíl (pokud nemáte osvíceného šéfa, který vám dá jasný cíl), máte vyhráno. Zbytek už je hračka.

Rozkreslíte si na papír požadavky (perfektní je tužka, papír guma, lze mazat). Protože víte, co máte (zdrojová data) a co chcete zobrazit (dashboard) tak jen vytvoříte střed (list s výpočty).

Z podkladů zjistíte a budete vědět:

• Jaké listy budou vytvořeny
• Která listy se budou přidávat
• Data která se budou předávat
• Jak bude vypadat dashboard

Úkol máte jasný

Jak bude vypadat dashboard Za pomocí ověření dat (nebo výběrového pole) na listu dashboardu vyberete rok a měsíc. V grafu uvidíte daný rok rozdělený na jednotlivé měsíce a vybraný měsíc bude v grafu zvýrazněn. Graf bude sloupcový.

Která listy se budou přidávat - výpočtový list a dashboardový (s grafem)

Data která se budou předávat - bude se předávat rok a měsíc

Tvorba

a teď můžete jít tvořit...

Vstupní data

K dispozici máme základní zdrojovou tabulku. Obsahující následující sloupce (pro začátek je volená záměrně jednoduchá):

• ROK
• MĚSÍC
• PŘÍJEM
• VÝDAJ

ROK	MĚSÍC	PŘÍJEM	VÝDAJ
2013	Leden	150	134
2013	Únor	151	137
...	...	...	...

Výpočty ze vstupních dat

Pro přehlednost umístíte vaše výpočty na nový list (ať jen nepracujeme na tom jednom).

Protože jste si v úvodu dobře rozmysleli, už víte jak budou výpočty ze vstupních dat vypadat. Podle vybraného roku se zobrazí příslušná data příjmů a výdajů. Do dalšího sloupce příjem a výdej jen pro příslušný měsíc.

Předpokládám znalosti

• Relativní a absolutní odkazování
• Funkce KDYŽ

Výpočtová tabulka

Stačí doplnit o pár vzorečků (funkcí) a rozkopírovat.

Pro data dle roků

=KDYŽ($B$4=2013;'Zdrojova data'!C6;KDYŽ($B$4=2014;'Zdrojova data'!C18;'Zdrojova data'!C30))

Pro data dle měsíce

=KDYŽ($B$5=A10;B10;NEDEF())

Poznámka

Lze řešit i přes jiné funkce, ale pro jednoduchost jsem použil druhou nejpoužívanější funkci KDYŽ (první je SUMA).

Tvorba dynamického grafu

Na další list umístíme Dashboard (graf). Samozdřejmě bude graf dynamický (to nám zaručuje tabulka na výpočtovém listě).

Předpokládám znalosti

• Ověření dat
• Tvorba sloupcového grafu
• Druhá vedlejší osa - jak ji přidat

Ověření dat

V prvním kroku do listu dashboardu umístíme vstupní pole (výběr měsíce a výběr roku). Použijeme ověření dat, ale stejně lze využít i vložení pole se seznamem.

Vložené ověření dat provážete s listem výpočty pro dashboard.

Graf

Vložíte sloupcový graf. Při vkládaní sloupcového grafu se sloupce (příjmy a výdaje) pro daný měsíc zobrazí 2x (což je pochopitelné, jedná se o další datovou řadu).

Stačí tyto dvě datové řady přesunout na vedlejší osu. A pro dokonalost skrýt popis této osy (ať nemáte na grafu 2x stajné hodnoty, teda pokud je tam nechcete mít záměrně 2x).

Doplnit názvy, možno upravit barvy, na listu doplnit pár dalších textů.

Hotovo

První použitelný dashboard je téměř hotov.

V dalším kroku jen opatříte dashboard o logo a graficky vylepšíte.

Než si budete hrát s vylepšováním grafiky, doporučuji vyzkoušet, zda se graf dynamicky mění a obsahuje ty správné hodnoty.

Grafické vylepšení - loga

Design je velice subjektivní pohled. Každému se líbí něco jiného. Někdo má rád věci malé, někdo velké/dlouhé. Někdo raději barvu červenou, někdo modrou a jiný zase zelenou. Začněte postupně vylepšovat

Doplnit o texty, co dělat

Doplnit text a šipku.

Skrýt - mřížky, záhlaví, řádek vzorců

Zpřehlednit tím, že se skryje řádek vzorců, záhlaví, mřížky, skryjete pás karet.

Další vylepšení

Další vylepšení dashboardu již nechám na vás. Případně časem doplním.

Další možná vylepšení dashboardu

Určitě vám dashboard funguje, pokud né v další kapitole si jej můžete stáhnout.

Dashboard lze dále vylepšovat (praktické ukázky proberu v dalším článku):

• Výběr klikem na buňku - Místo výběru (rok, měsíc) ze seznamu, můžeme tabulku doplnit o roky a měsíce a vybírat klikem na danou buňku. V tomto případě musíme využít VBA a makra.
• Automatická prezentace - informace se budou automaticky měnit
• doplňovat do grafu rok měsíc - propojeným obrázkem
• Volba barev - využít podmíněné barev
• atd.

Praktické návody již brzo ;)

Ke stažení

Soubor ke stažení Dashboard hotové řešení a jako obvykle je zdarma. Pro Excel 2007 - 2016

Další verze souboru Dashboard hotové řešení - skryté mřížky atd. a jako obvykle je zdarma. Pro Excel 2007 - 2016

Závěrem

Máte nějaké svoje dashboardy? Můžete se zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 10:56

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře