Az Excelben található hőtérkép vizuális megjelenítés, amely gyorsan megmutatja az adathalmaz összehasonlító nézetét.
Például az alábbi adatkészletben könnyen felismerhetem azokat a hónapokat, amikor az eladások alacsonyak voltak (pirossal kiemelve) a többi hónaphoz képest.
A fenti adatkészletben a színek a cella értéke alapján vannak hozzárendelve. A színskála zöldtől sárgától pirosig terjed, a magas értékek a zöld színt és az alacsony értékek a piros színt kapják.
Hőtérkép létrehozása Excelben
Míg hőtérképet hozhat létre az Excelben a cellák kézi színkódolásával. Az értékek változásakor azonban újra kell tennie.
A manuális munka helyett feltételes formázással kiemelheti a cellákat az érték alapján. Így, ha módosítja a cellák értékeit, a cella színe/formátuma automatikusan frissíti a hőtérképet a feltételes formázás előre meghatározott szabályai alapján.
Ebben az oktatóanyagban megtudhatja, hogyan:
- Gyorsan hozhat létre hőtérképet Excelben feltételes formázással.
- Készítsen dinamikus hőtérképet az Excelben.
- Hőtérkép létrehozása az Excel pivot táblákban.
Kezdjük el!
Hőtérkép létrehozása Excelben feltételes formázással
Ha rendelkezik adathalmazzal az Excelben, manuálisan kiemelheti az adatpontokat, és létrehozhat hőtérképet.
Ez azonban statikus hőtérkép lenne, mivel a szín nem változik, ha megváltoztatja a cella értékét.
Ezért a feltételes formázás a helyes út, mivel megváltoztatja a cella színét, ha megváltoztatja a benne lévő értéket.
Tegyük fel, hogy rendelkezik az alább látható adatkészlettel:
Az alábbi lépésekkel hozhat létre hőtérképet ezen adatok felhasználásával:
- Válassza ki az adatkészletet. Ebben a példában B2: D13 lenne.
- Lépjen a Főoldalra -> Feltételes formázás -> Színskálák. Különféle színkombinációkat jelenít meg, amelyekkel kiemelhetők az adatok. A legelterjedtebb színskála az első, ahol a magas értékű cellákat zöld és alacsony piros színnel jelölik. Ne feledje, hogy amikor az egeret az e színskálák fölé viszi, láthatja az élő előnézetet az adatkészletben.
Ekkor kap egy hőtérképet az alábbiak szerint:
Alapértelmezés szerint az Excel a vörös színt rendel a legalacsonyabb értékhez, a zöldet pedig a legmagasabb értékhez, és az összes többi érték az érték alapján kap színt. Tehát van egy színátmenet a három szín különböző árnyalataival az érték alapján.
Mi van akkor, ha nem szeretne színátmenetet, és csak pirosat, sárgát és zöldet szeretne megjeleníteni. Például minden 700 -nál kisebb értéket pirossal szeretne kiemelni, függetlenül az értéktől. Tehát az 500 és a 650 is ugyanazt a piros színt kapja, mivel kevesebb, mint 700.
Ezt csináld meg:
- Lépjen a Főoldalra -> Feltételes formázás -> Színskálák -> További lehetőségek.
- Az Új formázási szabály párbeszédpanelen válassza a „3-színskála” lehetőséget a Formátumstílus legördülő menüből.
- Most megadhatja a minimális, a középső és a maximális értéket, és hozzárendelheti a színt. Mivel minden 700 alatti értékű cellát pirossal szeretnénk kiemelni, módosítsuk a típust Számra és értékre 700 -ra.
- Kattintson az OK gombra.
Most megkapja az alább látható eredményt. Vegye figyelembe, hogy minden 700 alatti érték ugyanazt a piros színárnyalatot kapja.
Egy óvatosság szava: Bár a feltételes formázás csodálatos eszköz, sajnos ingadozó. Ez azt jelenti, hogy amikor a munkalapon bármilyen változás történik, a feltételes formázás újraszámításra kerül. Bár a hatás elhanyagolható a kis adatkészletekre, lassú Excel -munkafüzethez vezethet, ha nagy adathalmazokkal dolgozik.
Dinamikus hőtérkép létrehozása Excelben
Mivel a feltételes formázás a cella értékétől függ, amint megváltoztatja az értéket, a feltételes formázás újraszámítja és módosítja.
Ez lehetővé teszi a dinamikus hőtérkép készítését.
Nézzünk két példát a hőtérképek létrehozására az Excel interaktív vezérlőivel.
1. példa: Hőtérkép görgetősáv használatával
Íme egy példa, amikor a hőtérkép megváltozik, amint a görgetősávot használja az év megváltoztatásához.
Az ilyen típusú dinamikus hőtérképek használhatók olyan műszerfalakon, ahol helyszűke van, de mégis azt szeretné, ha a felhasználó hozzáférne a teljes adatkészlethez.
Kattintson ide a Heat Map sablon letöltéséhez
Hogyan lehet létrehozni ezt a dinamikus hőtérképet?
Itt található a dinamikus hőtérkép létrehozásához használt teljes adathalmaz.
Íme a lépések:
- Egy új lapon (vagy ugyanazon a lapon) adja meg a hónapok nevét (egyszerűen másolja be és illessze be az eredeti adatokból).
- Lépjen a Fejlesztő -> Vezérlők -> Beszúrás -> Görgetősáv menüpontra. Most kattintson bárhová a munkalapon, és beilleszt egy görgetősávot. (kattintson ide, ha nem találja a fejlesztői lapot).
- Kattintson a jobb gombbal a görgetősávra, és kattintson a Formátumvezérlés elemre.
- A Formátumvezérlés párbeszédpanelen hajtsa végre a következő módosításokat:
- Minimális érték: 1
- Maximális érték 5
- Cellahivatkozás: Sheet1! $ J $ 1 (Kattintson a jobb oldali ikonra, majd manuálisan válassza ki a görgetősávhoz kapcsolni kívánt cellát).
- Kattintson az OK gombra.
- A B1 cellába írja be a következő képletet: = INDEX (1. lap! $ B $ 1: $ H $ 13, ROW (), 1. lap! $ J $ 1+OSZLOPOK (2. lap! $ B $ 1: B1) -1)
- Méretezze át, és helyezze a görgetősávot az adathalmaz aljára.
Most, amikor megváltoztatja a görgetősávot, a Sheet1! $ J $ 1 értéke megváltozik, és mivel a képletek ehhez a cellához vannak kapcsolva, a rendszer frissíti a megfelelő értékeket.
Továbbá, mivel a feltételes formázás ingadozó, amint az érték megváltozik, az is frissül.
Videó megtekintése - Dinamikus hőtérkép az Excelben
2. példa: Dinamikus hőtérkép létrehozása Excelben a rádiógombokkal
Íme egy másik példa, ahol megváltoztathatja a hőtérképet egy választógomb kiválasztásával:
Ebben a példában a rádió/opciógomb kiválasztása alapján kiemelheti a felső/alsó 10 értékeket.
Kattintson ide a Heat Map sablon letöltéséhez
Hőtérkép létrehozása Excel pivot táblázatban
A feltételes formázás a kimutatástáblákban ugyanúgy működik, mint bármely normál adat.
De van egy fontos dolog, amit tudnia kell.
Hadd vegyek egy példát, és megmutatom.
Tegyük fel, hogy van egy pivot táblája az alábbiak szerint:
Hőtérkép létrehozása az Excel pivot táblázatában:
- Válassza ki a cellákat (B5: D14).
- Lépjen a Főoldalra -> Feltételes formázás -> Színskálák, és válassza ki az alkalmazni kívánt színskálát.
Ez azonnal létrehozza a hőtérképet a pivot táblázatban.
A probléma ezzel a módszerrel az, hogy ha új adatokat ad hozzá a háttérhez, és frissíti ezt a kimutatástáblát, a feltételes formázás nem lesz alkalmazva az új adatokra.
Például amikor új adatokat adtam hozzá a hátsó részhez, módosítottam a forrásadatokat és frissítettem a kimutatástáblát, láthatja, hogy a feltételes formázás nem vonatkozik rá.
Ez akkor történik, amikor a feltételes formázást csak a B5: D14 cellákra alkalmaztuk.
Ha azt szeretné, hogy ez a hőtérkép dinamikus legyen, és frissüljön, amikor új adatokat ad hozzá, akkor tegye a következőket:
- Válassza ki a cellákat (B5: D14).
- Lépjen a Főoldalra -> Feltételes formázás -> Színskálák, és válassza ki az alkalmazni kívánt színskálát.
- Ismét menjen a Főoldalra -> Feltételes formázás -> Szabályok kezelése.
- A Feltételes formázási szabályok kezelőben kattintson a Szerkesztés gombra.
- A Formázási szabály szerkesztése párbeszédpanelen válassza ki a harmadik lehetőséget: Minden cellát, amely a „Dátum” és az „Ügyfél” „Értékesítés” értékeit mutatja.
Most a feltételes formázás frissül, amikor megváltoztatja a háttér adatokat.
Jegyzet: A feltételes formázás megszűnik, ha megváltoztatja a sor/oszlop mezőket. Ha például eltávolítja a Dátum mezőt, és újra alkalmazza, akkor a feltételes formázás elvész.
