Az Excel hőtérképe egy vizuális ábrázolás, amely gyorsan megmutatja az adatkészlet összehasonlító nézetét.
például az alábbi adatkészletben könnyen észrevehetem, hogy melyek azok a hónapok, amikor az eladások alacsonyak voltak (pirossal kiemelve) a többi hónaphoz képest.
a fenti adatkészletben a színek a cellában lévő érték alapján vannak hozzárendelve. A színskála zöldtől sárgától pirosig terjed, a magas értékek zöld színt kapnak, az alacsony értékek pedig piros színt kapnak.
Ez az oktatóanyag a következőket tartalmazza:
Hőtérkép létrehozása Excel-ben
miközben hőtérképet hozhat létre Excel-ben a cellák kézi színkódolásával. Az értékek változásakor azonban újra kell tennie.
a kézi munka helyett feltételes formázást használhat a cellák kiemelésére az érték alapján. Ily módon, ha megváltoztatja 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 megadott szabályai alapján.
ebben az oktatóanyagban megtudhatja, hogyan lehet:
- feltételes formázással gyorsan létrehozni egy hőtérképet az Excelben.
- hozzon létre egy dinamikus hőtérképet az Excel programban.
- hozzon létre egy hőtérképet az Excel Pivot táblákban.
kezdjük!
Hőtérkép létrehozása az Excelben feltételes formázással
ha van adatkészlet 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 cellában lévő értéket.
ezért a feltételes formázás a helyes út, mivel megváltoztatja a cella színét, amikor megváltoztatja az értékét.
tegyük fel, hogy van egy adatkészleted az alábbiak szerint:
itt vannak a lépések egy hőtérkép létrehozásához ezen adatok felhasználásával:
- válassza ki az adatkészletet. Ebben a példában ez lenne B2: D13.
- ugrás a kezdőlapra –> Feltételes formázás –> színskálák. Különböző színkombinációkat mutat, amelyek felhasználhatók az adatok kiemelésére. A leggyakoribb színskála az első, ahol a magas értékű cellákat zöld színnel, az alacsonyat pedig pirossal emelik ki. Vegye figyelembe, hogy amikor az egeret ezen színskálák fölé viszi, láthatja az élő előnézetet az adatkészletben.
Ez egy hőtérképet ad az alábbiak szerint:
alapértelmezés szerint az Excel piros színt rendel a legalacsonyabb értékhez, a zöld színt pedig a legmagasabb értékhez, és az összes fennmaradó érték egy szín az érték alapján. Tehát van egy gradiens a három szín különböző árnyalataival az érték alapján.
most mi van, ha nem akar színátmenetet, és csak piros, sárga és zöld színt szeretne mutatni. Például ki szeretné emelni az összes értéket, amely kevesebb, mint mondjuk 700 piros színnel, függetlenül az értéktől. Tehát az 500-as és a 650-es is ugyanazt a piros színt kapja, mivel kevesebb, mint 700.
ehhez:
- ugrás a kezdőlapra –>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átum Stílus legördülő menüből.
- most megadhatja a minimumot, a középpontot és a maximális értéket, és hozzárendelheti a színt. Mivel az összes 700 alatti értékű cellát piros színnel szeretnénk kiemelni, változtassa meg a típust számra, az értéket pedig 700-ra.
- kattintson az OK gombra.
most megkapja az eredményt az alábbiak szerint. Vegye figyelembe, hogy a 700 alatti összes érték ugyanolyan árnyalatú piros színt kap.
figyelmeztetés: bár a feltételes formázás csodálatos eszköz, sajnos ingatag. Ez azt jelenti, hogy amikor bármilyen változás történik a munkalapon, a feltételes formázás újraszámításra kerül. Bár a hatás elhanyagolható lehet A Kis adatkészletekre, lassú Excel-munkafüzethez vezethet, ha nagy adatkészletekkel dolgozik.
dinamikus Hőtérkép létrehozása az Excelben
mivel a feltételes formázás a cellában lévő értéktől függ, az érték módosítása után a feltételes formázás újraszámítja és módosítja.
Ez lehetővé teszi dinamikus hőtérkép készítését.
nézzünk meg két példát a hőtérképek létrehozására interaktív vezérlők segítségével az Excelben.
1.példa: Hőtérkép görgetősáv használatával
íme egy példa, ahol a hőtérkép megváltozik, amint a görgetősáv segítségével megváltoztatja az évet.
Ez a típusú dinamikus hőtérkép használható olyan irányítópultokban, ahol helykorlátok vannak, de továbbra is szeretné, ha a felhasználó hozzáférne a teljes adatkészlethez.
kattintson ide a hőtérkép 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 adatkészlet.
itt vannak a lépések:
- egy új lapon (vagy ugyanazon a lapon) írja be a hónap nevét (egyszerűen másolja be az eredeti adatokból).
- ugrás a fejlesztőhöz –> vezérlők –> Beszúrás –> görgetősáv. Most kattintson a munkalap bármely pontjára, és beilleszti a görgetősávot. (kattintson ide, ha nem találja a fejlesztő fület).
- kattintson a jobb gombbal a görgetősávra, majd 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
- Cell Link: 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 (Sheet1!$B$1:$H$13, sor (), Sheet1!$J$1 + oszlopok (Sheet2!$ B$1:B1) -1)
- méretezze át a görgetősávot az adatkészlet aljára.
Most, amikor megváltoztatja a görgetősáv, az érték Sheet1!$J$1 megváltozna, és mivel a képletek ehhez a cellához vannak kapcsolva, frissítené a helyes értékeket.
mivel a feltételes formázás ingatag, amint az érték megváltozik, az is frissül.
Videó megtekintése-dinamikus Hőtérkép Excel-ben
2. példa: Dinamikus Hőtérkép létrehozása az Excelben rádiógombok használatával
itt van egy másik példa, ahol a hőtérképet megváltoztathatja egy választógomb kiválasztásával:
ebben a példában kiemelheti a felső/alsó 10 értéket a rádió/option gomb kiválasztása alapján.
kattintson ide a hőtérkép sablon letöltéséhez
Hőtérkép létrehozása az Excel Pivot táblában
Feltételes formázás a Pivot táblákban ugyanúgy működik, mint bármely normál adat esetében.
de van valami fontos, amit tudnod kell.
hadd mutassak egy példát.
tegyük fel, hogy van egy pivot táblája az alábbiak szerint:
hőtérkép létrehozása ebben az Excel Pivot táblában:
- válassza ki a cellákat (B5:D14).
- ugrás a kezdőlapra –> 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étrehozná a hőtérképet a pivot táblában.
ezzel a módszerrel az a probléma, hogy ha új adatokat ad hozzá a háttérbe, és frissíti ezt a Pivot táblát, akkor a feltételes formázás nem kerül alkalmazásra az új adatokra.
például, amikor új adatokat adtam hozzá a hátsó véghez, módosítottam a forrásadatokat és frissítettem a kimutatást, 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, hogy frissüljön az új adatok hozzáadásakor, a következő lépések vannak:
- válassza ki a cellákat (B5:D14).
- ugrás a kezdőlapra –> Feltételes formázás –> színskálák és válassza ki az alkalmazni kívánt színskálát.
- ismét lépjen a kezdőlapra –>Feltételes formázás – > Szabályok kezelése.
- a feltételes formázási szabályok kezelőjé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 olyan cellát, amely a “dátum” és az “ügyfél” értékesítési értékeit mutatja.
most a feltételes formázás frissül, amikor megváltoztatja a háttéradatokat.
Megjegyzés: 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, a feltételes formázás elvész.
a következő Excel oktatóanyagok is tetszhetnek:
- hogyan lehet kiemelni az Excel minden más sorát.
- hozzon létre 100% – os verem oszlopdiagramot feltételes formázással.
- hogyan kell számolni a színes cellákat Excel-ben.
- hogyan lehet kiemelni az üres cellákat az Excel programban.
- Excel Sparklines-teljes útmutató