Tepelná mapa v programe Excel je vizuálna reprezentácia, ktorá vám rýchlo ukáže komparatívne zobrazenie množiny údajov.
Napríklad v nižšie uvedenom súbore údajov môžem ľahko zistiť, ktoré sú mesiace, kedy boli tržby nízke (zvýraznené červenou farbou) v porovnaní s inými mesiacmi.
Vo vyššie uvedenom súbore údajov sú farby priradené na základe hodnoty v bunke. Farebná škála je zelená až žltá až červená s vysokými hodnotami zelenej farby a nízkymi hodnotami s červenou farbou.
Vytvorenie tepelnej mapy v programe Excel
Tepelnú mapu v programe Excel však môžete vytvoriť ručným farebným kódovaním buniek. Pri zmene hodnôt to však budete musieť zopakovať.
Namiesto manuálnej práce môžete na zvýraznenie buniek na základe hodnoty použiť podmienené formátovanie. Týmto spôsobom, v prípade, že zmeníte hodnoty v bunkách, farba/formát bunky automaticky aktualizuje tepelnú mapu na základe vopred zadaných pravidiel v podmienenom formátovaní.
V tomto návode sa naučíte, ako:
- Rýchlo vytvorte tepelnú mapu v programe Excel pomocou podmieneného formátovania.
- V programe Excel vytvorte dynamickú tepelnú mapu.
- V kontingenčných tabuľkách programu Excel vytvorte tepelnú mapu.
Začnime!
Vytvorenie tepelnej mapy v programe Excel pomocou podmieneného formátovania
Ak máte množinu údajov v programe Excel, môžete dátové body zvýrazniť ručne a vytvoriť tepelnú mapu.
To by však bola statická tepelná mapa, pretože farba sa nezmení, keď zmeníte hodnotu v bunke.
Podmienené formátovanie je preto správnou cestou, pretože zmení farbu v bunke, keď v nej zmeníte hodnotu.
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie:
Tu je postup, ako vytvoriť tepelnú mapu pomocou týchto údajov:
- Vyberte množinu údajov. V tomto prípade by to bolo B2: D13.
- Prejdite na domovskú stránku -> Podmienené formátovanie -> Farebné škály. Zobrazuje rôzne farebné kombinácie, ktoré je možné použiť na zvýraznenie údajov. Najbežnejšia farebná škála je prvá, kde sú bunky s vysokými hodnotami zvýraznené zelenou farbou a nízke červenou. Všimnite si toho, že keď umiestnite kurzor myši na tieto farebné škály, v súprave údajov sa vám zobrazí živý náhľad.
Získate tak tepelnú mapu, ako je uvedené nižšie:
Excel predvolene priraďuje červenú farbu k najnižšej hodnote a zelenú k najvyššej hodnote a všetky zostávajúce hodnoty získajú farbu podľa hodnoty. Existuje teda gradient s rôznymi odtieňmi troch farieb na základe hodnoty.
Teraz, čo keď nechceme prechod a chceme zobrazovať iba červenú, žltú a zelenú farbu. Napríklad chcete zvýrazniť všetky hodnoty menej ako povedzme 700 červenou farbou, bez ohľadu na hodnotu. Takže 500 a 650 dostanú rovnakú červenú farbu, pretože je to menej ako 700.
Urobiť toto:
- Prejdite na domovskú stránku -> Podmienené formátovanie -> Farebné škály -> Ďalšie možnosti.
- V dialógovom okne Nové pravidlo formátovania vyberte v rozbaľovacom zozname Štýl formátu položku „Trojfarebná škála“.
- Teraz môžete určiť minimálnu, strednú a maximálnu hodnotu a priradiť k nej farbu. Pretože chceme všetky bunky s hodnotou pod 700 zvýrazniť červenou farbou, zmeňte typ na Číslo a hodnotu na 700.
- Kliknite na tlačidlo OK.
Teraz získate výsledok, ako je uvedené nižšie. Všimnite si toho, že všetky hodnoty pod 700 získajú rovnaký odtieň červenej farby.
Slovo opatrnosti: Aj keď je podmienené formátovanie úžasný nástroj, bohužiaľ je prchavé. To znamená, že vždy, keď dôjde k akejkoľvek zmene v hárku, podmienečné formátovanie sa prepočíta. Aj keď môže byť vplyv na malé množiny údajov zanedbateľný, pri práci s veľkými množinami údajov môže viesť k pomalému zošitu programu Excel.
Vytvorenie dynamickej tepelnej mapy v programe Excel
Pretože podmienené formátovanie závisí od hodnoty v bunke, akonáhle zmeníte hodnotu, podmienené formátovanie sa prepočíta a zmení.
Vďaka tomu je možné vytvoriť dynamickú tepelnú mapu.
Pozrime sa na dva príklady vytvárania tepelných máp pomocou interaktívnych ovládacích prvkov v programe Excel.
Príklad 1: Tepelná mapa pomocou posúvača
Tu je príklad, kde sa tepelná mapa zmení hneď, ako pomocou posuvníka zmeníte rok.
Tento typ dynamických tepelných máp je možné použiť na dashboardoch, kde máte priestorové obmedzenia, ale napriek tomu chcete, aby mal používateľ prístup k celej množine údajov.
Kliknutím sem stiahnete šablónu tepelnej mapy
Ako vytvoriť túto dynamickú tepelnú mapu?
Tu je kompletný súbor údajov, ktorý sa používa na vytvorenie tejto dynamickej tepelnej mapy.
Tu sú kroky:
- Do nového hárka (alebo do toho istého hárka) zadajte názvy mesiacov (stačí skopírovať a prilepiť z pôvodných údajov).
- Prejdite na položku Vývojár -> Ovládacie prvky -> Vložiť -> Posuvník. Teraz kliknite kdekoľvek v pracovnom hárku a vloží sa posúvač. (kliknite sem, ak nemôžete nájsť kartu vývojára).
- Kliknite pravým tlačidlom myši na posuvník a kliknite na položku Ovládanie formátu.
- V dialógovom okne Ovládanie formátu vykonajte nasledujúce zmeny:
- Minimálna hodnota: 1
- Maximálna hodnota 5
- Odkaz na bunku: List1! $ J $ 1 (Môžete kliknúť na ikonu vpravo a potom ručne vybrať bunku, ktorú chcete prepojiť s posúvačom).
- Kliknite na tlačidlo OK.
- Do bunky B1 zadajte vzorec: = INDEX (List1! $ B $ 1: $ H $ 13, ROW (), Sheet1! $ J $ 1+COLUMNS (Sheet2! $ B $ 1: B1) -1)
- Zmeňte veľkosť a posuňte posúvač v spodnej časti množiny údajov.
Teraz, keď zmeníte posúvač, hodnota v Hárku1! $ J $ 1 sa zmení a keďže sú vzorce prepojené s touto bunkou, aktualizuje sa a zobrazuje správne hodnoty.
Pretože podmienené formátovanie je volatilné, hneď ako sa zmení hodnota, aktualizuje sa tiež.
Pozrite si video - Dynamická tepelná mapa v programe Excel
Príklad 2: Vytvorenie dynamickej tepelnej mapy v programe Excel pomocou prepínačov
Tu je ďalší príklad, kde môžete zmeniť tepelnú mapu výberom prepínača:
V tomto prípade môžete zvýrazniť 10 horných/dolných hodnôt na základe výberu prepínača/tlačidla voľby.
Kliknutím sem stiahnete šablónu tepelnej mapy
Vytvorenie tepelnej mapy v kontingenčnej tabuľke programu Excel
Podmienené formátovanie v kontingenčných tabuľkách funguje rovnako ako pri bežných údajoch.
Je tu však niečo dôležité, čo musíte vedieť.
Uvediem príklad a ukážem vám to.
Predpokladajme, že máte kontingenčnú tabuľku, ako je uvedené nižšie:
Ak chcete vytvoriť tepelnú mapu v tejto kontingenčnej tabuľke programu Excel:
- Vyberte bunky (B5: D14).
- Prejdite na položku Domov -> Podmienené formátovanie -> Farebné škály a vyberte farebnú škálu, ktorú chcete použiť.
V kontingenčnej tabuľke by sa tak okamžite vytvorila tepelná mapa.
Problém s touto metódou je, že ak do backendu pridáte nové údaje a aktualizujete túto kontingenčnú tabuľku, podmienené formátovanie sa na nové údaje nepoužije.
Keď som napríklad pridal nové údaje do zadného konca, upravil zdrojové údaje a aktualizoval kontingenčnú tabuľku, môžete vidieť, že sa na neho nevzťahuje podmienené formátovanie.
Stáva sa to, pretože sme podmienené formátovanie použili iba na bunky B5: D14.
Ak chcete, aby bola táto tepelná mapa dynamická a aktualizovala sa po pridaní nových údajov, postupujte takto:
- Vyberte bunky (B5: D14).
- Prejdite na položku Domov -> Podmienené formátovanie -> Farebné škály a vyberte farebnú škálu, ktorú chcete použiť.
- Znova prejdite na položku Domov -> Podmienené formátovanie -> Spravovať pravidlá.
- V Správcovi pravidiel podmieneného formátovania kliknite na tlačidlo Upraviť.
- V dialógovom okne Upraviť pravidlo formátovania vyberte tretiu možnosť: Všetky bunky s hodnotami „Predaj“ pre „Dátum“ a „Zákazník“.
Teraz sa podmienené formátovanie aktualizuje, keď zmeníte údaje backendu.
Poznámka: Podmienené formátovanie zmizne, ak zmeníte polia riadkov/stĺpcov. Ak napríklad odstránite pole Dátum a použijete ho znova, podmienené formátovanie sa stratí.
