Tepelná mapa v aplikaci Excel je vizuální znázornění, které vám rychle ukáže srovnávací pohled na datovou sadu.
Například v níže uvedené datové sadě snadno zjistím, které jsou měsíce, kdy byly tržby nízké (zvýrazněny červeně) ve srovnání s jinými měsíci.
Ve výše uvedené datové sadě jsou barvy přiřazeny na základě hodnoty v buňce. Barevná škála je zelená až žlutá až červená, přičemž vysoké hodnoty získávají zelenou barvu a nízké hodnoty červenou barvu.
Vytvoření tepelné mapy v aplikaci Excel
Zatímco v aplikaci Excel můžete vytvořit tepelnou mapu ručním barevným kódováním buněk. Při změně hodnot to však budete muset zopakovat.
Místo ruční práce můžete použít podmíněné formátování pro zvýraznění buněk na základě hodnoty. Tímto způsobem v případě, že změníte hodnoty v buňkách, barva/formát buňky automaticky aktualizuje tepelnou mapu na základě předem zadaných pravidel v podmíněném formátování.
V tomto tutoriálu se naučíte, jak:
- Rychle vytvořte tepelnou mapu v aplikaci Excel pomocí podmíněného formátování.
- Vytvořte dynamickou tepelnou mapu v Excelu.
- Vytvořte tepelnou mapu v kontingenčních tabulkách aplikace Excel.
Začněme!
Vytvoření tepelné mapy v aplikaci Excel pomocí podmíněného formátování
Pokud máte datovou sadu v Excelu, můžete ručně zvýraznit datové body a vytvořit tepelnou mapu.
To by však byla statická tepelná mapa, protože barva se při změně hodnoty v buňce nezmění.
Podmíněné formátování je tedy správnou cestou, protože změní barvu v buňce, když v ní změníte hodnotu.
Předpokládejme, že máte datovou sadu, jak je uvedeno níže:
Zde jsou kroky k vytvoření tepelné mapy pomocí těchto dat:
- Vyberte datovou sadu. V tomto případě by to bylo B2: D13.
- Přejděte na Domů -> Podmíněné formátování -> Barevné stupnice. Ukazuje různé barevné kombinace, které lze použít ke zvýraznění dat. Nejběžnější barevná škála je první, kde jsou buňky s vysokými hodnotami zvýrazněny zeleně a nízko červeně. Všimněte si, že když umístíte kurzor myši na tato barevná měřítka, uvidíte v sadě dat živý náhled.
Tím získáte tepelnou mapu, jak je znázorněno níže:
Ve výchozím nastavení aplikace Excel přiřadí červenou barvu k nejnižší hodnotě a zelenou barvu k nejvyšší hodnotě a všechny zbývající hodnoty získají barvu na základě hodnoty. Existuje tedy přechod s různými odstíny tří barev na základě hodnoty.
Co když nechceme přechod a chceme ukázat pouze červenou, žlutou a zelenou. Například chcete zvýraznit všechny hodnoty méně než řekněme 700 červeně, bez ohledu na hodnotu. Takže 500 a 650 dostanou stejnou červenou barvu, protože je to méně než 700.
Udělat toto:
- Přejděte na Domů -> Podmíněné formátování -> Barevné stupnice -> Další možnosti.
- V dialogovém okně Nové pravidlo formátování vyberte z rozevíracího seznamu Styl formátování „Tříbarevné měřítko“.
- Nyní můžete zadat minimální, střední a maximální hodnotu a přiřadit jí barvu. Protože chceme červeně zvýraznit všechny buňky s hodnotou pod 700, změňte typ na Číslo a hodnotu na 700.
- Klikněte na OK.
Nyní získáte výsledek, jak je uvedeno níže. Všimněte si, že všechny hodnoty pod 700 dostanou stejný odstín červené barvy.
Slovo opatrnosti: Přestože je podmíněné formátování skvělý nástroj, bohužel je nestálé. To znamená, že kdykoli dojde k jakékoli změně v listu, podmíněné formátování se přepočítá. I když dopad může být u malých datových sad zanedbatelný, může při práci s velkými datovými sadami vést k pomalému sešitu aplikace Excel.
Vytvoření dynamické tepelné mapy v aplikaci Excel
Protože podmíněné formátování závisí na hodnotě v buňce, jakmile změníte hodnotu, podmíněné formátování se přepočítá a změní.
Díky tomu je možné vytvořit dynamickou tepelnou mapu.
Podívejme se na dva příklady vytváření tepelných map pomocí interaktivních ovládacích prvků v Excelu.
Příklad 1: Tepelná mapa pomocí posuvníku
Zde je příklad, kdy se tepelná mapa změní, jakmile pomocí posuvníku změníte rok.
Tento typ dynamických tepelných map lze použít na řídicích panelech, kde máte prostorová omezení, ale přesto chcete, aby měl uživatel přístup k celé sadě dat.
Kliknutím sem stáhnete šablonu Heat Map
Jak vytvořit tuto dynamickou tepelnou mapu?
Zde je kompletní sada dat, která se používá k vytvoření této dynamické tepelné mapy.
Zde jsou kroky:
- Do nového listu (nebo do stejného listu) zadejte názvy měsíců (jednoduše je zkopírujte a vložte z původních dat).
- Přejděte na Vývojář -> Ovládací prvky -> Vložit -> Posuvník. Nyní klikněte kamkoli do listu a vloží se posuvník. (klikněte zde, pokud nemůžete najít kartu vývojáře).
- Klikněte pravým tlačítkem na posuvník a klikněte na Ovládání formátu.
- V dialogovém okně Ovládání formátu proveďte následující změny:
- Minimální hodnota: 1
- Maximální hodnota 5
- Odkaz na buňku: List1! $ J $ 1 (Můžete kliknout na ikonu vpravo a poté ručně vybrat buňku, kterou chcete propojit s posuvníkem).
- Klikněte na OK.
- Do buňky B1 zadejte vzorec: = INDEX (List1! $ B $ 1: $ H $ 13, ROW (), Sheet1! $ J $ 1+COLUMNS (Sheet2! $ B $ 1: B1) -1)
- Změňte velikost a umístěte posuvník ve spodní části datové sady.
Když nyní změníte posuvník, změní se hodnota v List1! $ J $ 1, a protože jsou vzorce propojeny s touto buňkou, aktualizuje se a zobrazí správné hodnoty.
Protože podmíněné formátování je nestálé, jakmile se hodnota změní, aktualizuje se také.
Podívejte se na video - dynamická tepelná mapa v Excelu
Příklad 2: Vytvoření dynamické tepelné mapy v aplikaci Excel pomocí přepínačů
Zde je další příklad, kde můžete změnit tepelnou mapu výběrem přepínače:
V tomto příkladu můžete zvýraznit 10 horních/dolních hodnot na základě výběru přepínače/tlačítka volby.
Kliknutím sem stáhnete šablonu Heat Map
Vytvoření tepelné mapy v kontingenční tabulce aplikace Excel
Podmíněné formátování v kontingenčních tabulkách funguje stejně jako u všech normálních dat.
Je tu ale něco důležitého, co musíte vědět.
Ukážu vám příklad a ukážu vám to.
Předpokládejme, že máte kontingenční tabulku, jak je uvedeno níže:
Vytvoření tepelné mapy v této kontingenční tabulce aplikace Excel:
- Vyberte buňky (B5: D14).
- Přejděte na Domů -> Podmíněné formátování -> Barevná měřítka a vyberte barevné měřítko, které chcete použít.
To by okamžitě vytvořilo tepelnou mapu v kontingenční tabulce.
Problém této metody spočívá v tom, že pokud do backendu přidáte nová data a aktualizujete tuto kontingenční tabulku, nebude na nová data použito podmíněné formátování.
Když jsem například přidal nová data do back -endu, upravil zdrojová data a aktualizoval kontingenční tabulku, můžete vidět, že na ni není aplikováno podmíněné formátování.
K tomu dochází, protože jsme podmíněné formátování použili pouze na buňky B5: D14.
Pokud chcete, aby byla tato tepelná mapa dynamická a aktualizovala se po přidání nových dat, postupujte takto:
- Vyberte buňky (B5: D14).
- Přejděte na Domů -> Podmíněné formátování -> Barevné stupnice a vyberte barevné měřítko, které chcete použít.
- Opět přejděte na Domů -> Podmíněné formátování -> Spravovat pravidla.
- Ve Správci pravidel podmíněného formátování klikněte na tlačítko Upravit.
- V dialogovém okně Upravit pravidlo formátování vyberte třetí možnost: Všechny buňky zobrazující hodnoty „Prodej“ pro „Datum“ a „Zákazník“.
Nyní se podmíněné formátování aktualizuje, když změníte data backendu.
Poznámka: Podmíněné formátování zmizí, pokud změníte pole řádků/sloupců. Pokud například odeberete pole Datum a použijete jej znovu, dojde ke ztrátě podmíněného formátování.
