Jak Vytvořit Tepelnou Mapu v aplikaci Excel – Krok Za krokem Průvodce

Heat Map v aplikaci Excel je vizuální reprezentace, která rychle zobrazuje srovnávací pohled dataset.

například v datovém souboru níže mohu snadno zjistit, které jsou měsíce, kdy byly prodeje nízké (zvýrazněny červeně) ve srovnání s ostatními měsíci.

ve výše uvedeném datasetu jsou barvy přiřazeny na základě hodnoty v buňce. Barevná stupnice je zelená až žlutá až červená s vysokými hodnotami získání zelené barvy a nízké hodnoty získání červené barvy.

vytvoření tepelné mapy v aplikaci Excel

Zatímco můžete vytvořit tepelnou mapu v aplikaci Excel ručně barevným kódováním buněk. Při změně hodnot však budete muset znovu provést.

namí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ňku bude automaticky aktualizovat tepelné mapy na základě předem stanovených pravidel v podmíněném formátování.

V tomto tutoriálu, se dozvíte, jak:

• Rychle vytvořit tepelnou mapu v Excelu pomocí podmíněného formátování.
• Vytvořte dynamickou tepelnou mapu v aplikaci Excel.
• Vytvořte tepelnou mapu v kontingenčních tabulkách aplikace Excel.

začněme!

vytvoření tepelné mapy v Excelu 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 by se při změně hodnoty v buňce nezměnila.

podmíněné formátování je tedy správný způsob, jak jít, protože změní barvu v buňce, když změníte hodnotu v ní.

Předpokládejme, že máte soubor údajů, jak je uvedeno níže:

Zde jsou kroky k vytvoření heat mapy pomocí těchto údajů:

• Vyberte datovou sadu. V tomto příkladu by to bylo B2: D13.
• Jít na Home –> Podmíněné Formátování –> Barevné Stupnice. Zobrazuje různé barevné kombinace, které lze použít k zvýraznění dat. Nejběžnější barevná stupnice 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 tyto barevné stupnice, můžete vidět živý náhled v datové sadě.
  

To vám dá mapu tepla, jak je uvedeno níže:

ve výchozím nastavení, Excel přiřadí červená barva na nejnižší hodnotu a zelená barva na nejvyšší hodnotu, a všechny zbývající hodnoty dostat barvu na základě hodnoty. Existuje tedy gradient s různými odstíny tří barev na základě hodnoty.

nyní, co když nechcete gradient a chcete zobrazit pouze červenou, žlutou a zelenou. Chcete například zvýraznit všechny hodnoty menší než 700 červeně, bez ohledu na hodnotu. Takže 500 a 650 mají stejnou červenou barvu, protože je to méně než 700.

:

• Jít na Home –> Podmíněné Formátování –> Barva Váhy –> Další Možnosti.
• v dialogovém okně Nové pravidlo formátování vyberte z rozbalovací nabídky Styl formátu „3-barevná stupnice“.
• Nyní můžete zadat minimální, střední a maximální hodnota a přiřadit barvu. Protože chceme zvýraznit všechny buňky hodnotou pod 700 červeně, 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 mají stejný odstín červené barvy.

upozornění: zatímco podmíněné formátování je skvělý nástroj, bohužel je nestálý. To znamená, že kdykoli dojde ke změně listu, podmíněné formátování se přepočítá. Zatímco dopad může být zanedbatelný na malé datové sady, může to vést k pomalému sešitu aplikace Excel při práci s velkými datovými sadami.

Vytvoření Dynamické Tepelné Mapy v aplikaci Excel

Od té doby, podmíněné formátování je závislá na hodnotě v buňce, jakmile změníte hodnotu, podmíněné formátování přepočítá a změní.

To umožňuje 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 aplikaci Excel.

Příklad 1: mapa tepla pomocí posuvníku

zde je příklad, kdy se mapa tepla změní, jakmile použijete posuvník ke změně roku.

Tento typ dynamické tepelné mapy může být použit v řídicí panely, kde máte prostor omezení, ale přesto chcete, aby uživatel přístup k celé datové sadě.

Klikněte zde pro stažení šablony tepelné mapy

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:

• v novém listu (nebo ve stejném listu) zadejte názvy měsíců (jednoduše zkopírujte jej z původních dat).
• přejít na Developer – > Controls – > Insert – > Scroll Bar. Nyní klikněte kdekoli v listu a vloží posuvník. (klikněte zde, pokud nemůžete najít kartu Vývojář).
• klepněte pravým tlačítkem myši na posuvník a klikněte na ovládání formátu.
• Formát Ovládacího prvku dialogové okno pole, 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.
• v buňce B1 zadejte vzorec: = INDEX (List1!$B$1:$H$13,řádek (), List1!$J $ 1+sloupce (Sheet2!$B$1:B1) -1)
• změňte velikost a umístěte posuvník do dolní části datové sady.

Nyní, když změníte posuvník, hodnota v Listu1!$J$1 by se změnilo a protože vzorce jsou spojeny s touto buňkou, aktualizovalo by se, aby zobrazilo správné hodnoty.

Také, protože podmíněné formátování je nestabilní, jakmile se změní hodnota, to bude aktualizován, stejně.

sledovat Video-dynamická tepelná mapa v aplikaci Excel

příklad 2: Vytvoření Dynamické Tepelné Mapy v aplikaci Excel pomocí Radio Tlačítek

Zde je další příklad, kde můžete změnit tepelné mapy tím, že přepínač výběru:

V tomto příkladu, můžete zvýraznit horní/dolní 10 hodnoty založené na rádio/možnost tlačítko výběr.

kliknutím sem stáhnete šablonu tepelné mapy

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 běžných dat.

ale je tu něco důležitého, co potřebujete vědět.

dovolte mi vzít příklad a ukázat vám.

Předpokládejme, že máte kontingenční tabulky, jak je uvedeno níže:

K vytvoření tepelné mapy v této aplikaci Excel Kontingenční Tabulky:

• Vyberte buňky (B5:D14).
• přejděte na domovskou stránku – > Podmíněné formátování – > barevné stupnice a vyberte barevnou stupnici, kterou chcete použít.

tím se okamžitě vytvoří mapa tepla v kontingenční tabulce.

problém s tato metoda je, že pokud chcete přidat nová data v backend a obnovte tuto Kontingenční Tabulky, podmíněné formátování by neměl být aplikován na nová data.

například, když jsem přidal nová data do zadního konce, upravil zdrojová data a obnovil kontingenční tabulku, můžete vidět, že se na ni nepoužije podmíněné formátování.

Tohle se stane, když začneme aplikovat podmíněné formátování buněk B5:D14.

Pokud chcete, aby tento heat mapy, které mají být dynamické taková, že to aktualizuje, když nová data přidána, zde jsou kroky:

• Vyberte buňky (B5:D14).
• přejděte na domovskou stránku – > Podmíněné formátování – > barevné stupnice a vyberte barevnou stupnici, kterou chcete použít.
• jít na Home –> 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 Upravit Pravidlo Formátování dialogové okno, vyberte třetí možnost: Všechny buňky zobrazující „Prodeje“ hodnoty „Datum“ a „Zákazníkem“.

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 řádek/sloupec. Pokud například odstraníte pole datum a znovu jej použijete, dojde ke ztrátě podmíněného formátování.

mohlo by se vám také líbit následující výukové programy aplikace Excel:

• jak zvýraznit každý další řádek v aplikaci Excel.
• Vytvořte 100% sloupcový graf zásobníku pomocí podmíněného formátování.
• jak počítat barevné buňky v aplikaci Excel.
• jak zvýraznit prázdné buňky v aplikaci Excel.
• Excel Sparklines-Kompletní průvodce