Toplotni zemljevid v Excelu je vizualni prikaz, ki vam hitro prikaže primerjalni pogled nabora podatkov.
Na primer, v spodnjem nizu podatkov lahko enostavno opazim, kateri so meseci, ko je bila prodaja nizka (označena z rdečo) v primerjavi z drugimi meseci.
V zgornjem naboru podatkov so barve dodeljene glede na vrednost v celici. Barvna lestvica je od zelene do rumene do rdeče, pri čemer visoke vrednosti dobijo zeleno barvo, nizke pa rdečo barvo.
Ustvarjanje toplotnega zemljevida v Excelu
Medtem ko lahko v Excelu ustvarite toplotni zemljevid z ročnim barvnim kodiranjem celic. Vendar pa ga boste morali spremeniti, ko se vrednosti spremenijo.
Namesto ročnega dela lahko s pogojnim oblikovanjem označite celice glede na vrednost. Na ta način bo v primeru spreminjanja vrednosti v celicah barva/oblika celice samodejno posodobila toplotni zemljevid na podlagi vnaprej določenih pravil pri pogojnem oblikovanju.
V tej vadnici se boste naučili, kako:
- Hitro ustvarite toplotni zemljevid v Excelu s pogojnim oblikovanjem.
- Ustvarite dinamični toplotni zemljevid v Excelu.
- Ustvarite toplotni zemljevid v vrtilnih tabelah Excel.
Začnimo!
Ustvarjanje toplotnega zemljevida v Excelu z uporabo pogojnega oblikovanja
Če imate nabor podatkov v Excelu, lahko ročno označite podatkovne točke in ustvarite toplotni zemljevid.
Vendar bi bil to statični toplotni zemljevid, saj se barva ne spremeni, če spremenite vrednost v celici.
Pogojno oblikovanje je torej prava pot, saj spremeni barvo celice, ko spremenite vrednost v njej.
Recimo, da imate nabor podatkov, kot je prikazano spodaj:
Tukaj so koraki za izdelavo toplotne karte s temi podatki:
- Izberite nabor podatkov. V tem primeru bi bilo to B2: D13.
- Pojdite na dom -> Pogojno oblikovanje -> Barvne lestvice. Prikazuje različne barvne kombinacije, s katerimi lahko označite podatke. Najpogostejša barvna lestvica je prva, kjer so celice z visokimi vrednostmi označene z zeleno in nizko z rdečo. Upoštevajte, da lahko med premikanjem miške nad temi barvnimi lestvicami v naboru podatkov vidite predogled v živo.
Tako boste dobili toplotni zemljevid, kot je prikazano spodaj:
Excel privzeto dodeli rdečo barvo najnižji vrednosti, zeleno pa najvišji vrednosti, vse preostale vrednosti pa dobijo barvo glede na vrednost. Torej obstaja gradient z različnimi odtenki treh barv glede na vrednost.
Kaj pa, če ne želite naklona in želite prikazati samo rdečo, rumeno in zeleno. Na primer, želite označiti vse vrednosti, manjše od 700, rdeče, ne glede na vrednost. Tako dobivata 500 in 650 enako rdečo barvo, saj je manj kot 700.
Storiti to:
- Pojdite na Domov -> Pogojno oblikovanje -> Barvne lestvice -> Več možnosti.
- V pogovornem oknu Novo pravilo oblikovanja v spustnem meniju Oblika sloga izberite »3-barvna lestvica«.
- Zdaj lahko določite najmanjšo, srednjo točko in največjo vrednost ter ji dodelite barvo. Ker želimo rdeče označiti vse celice z vrednostjo pod 700, spremenite vrsto v Številka in vrednost v 700.
- Kliknite V redu.
Zdaj boste dobili rezultat, kot je prikazano spodaj. Upoštevajte, da imajo vse vrednosti pod 700 enak odtenek rdeče barve.
Beseda previdnosti: Pogojno oblikovanje je čudovito orodje, na žalost pa je nestanovitno. To pomeni, da se vsakič, ko pride do kakršne koli spremembe na delovnem listu, pogojno oblikovanje ponovno izračuna. Čeprav je učinek pri majhnih naborih podatkov zanemarljiv, lahko pri delu z velikimi nizi podatkov privede do počasnega Excelovega delovnega zvezka.
Ustvarjanje dinamičnega toplotnega zemljevida v Excelu
Ker je pogojno oblikovanje odvisno od vrednosti v celici, takoj ko spremenite vrednost, se pogojno oblikovanje ponovno izračuna in spremeni.
To omogoča izdelavo dinamičnega toplotnega zemljevida.
Poglejmo dva primera ustvarjanja toplotnih zemljevidov z uporabo interaktivnih kontrol v Excelu.
Primer 1: Toplotni zemljevid z drsnim trakom
Tu je primer, ko se toplotni zemljevid spremeni, takoj ko z drsnim trakom spremenite leto.
To vrsto dinamičnih toplotnih zemljevidov lahko uporabite na nadzornih ploščah, kjer imate prostorske omejitve, vendar še vedno želite, da uporabnik dostopa do celotnega nabora podatkov.
Kliknite tukaj za prenos predloge Heat Map
Kako ustvariti ta dinamični toplotni zemljevid?
Tu je celoten nabor podatkov, ki se uporablja za izdelavo te dinamične toplotne karte.
Tu so koraki:
- V nov list (ali v isti list) vnesite imena mesecev (preprosto kopirajte prilepite iz prvotnih podatkov).
- Pojdite na Razvijalec -> Kontrolniki -> Vstavi -> Drsni trak. Zdaj kliknite kjer koli na delovnem listu in vstavila bo drsnik. (kliknite tukaj, če ne najdete zavihka za razvijalce).
- Z desno miškino tipko kliknite drsni trak in kliknite Format Control.
- V pogovornem oknu Format Control naredite te spremembe:
- Najmanjša vrednost: 1
- Največja vrednost 5
- Cell Link: Sheet1! $ J $ 1 (Lahko kliknete ikono na desni in nato ročno izberete celico, ki jo želite povezati z drsnim trakom).
- Kliknite V redu.
- V celico B1 vnesite formulo: = INDEKS (List1! $ B $ 1: $ H $ 13, ROW (), List1! $ J $ 1+STOLPCI (List2! $ B $ 1: B1) -1)
- Spremenite velikost in postavite drsnik na dno nabora podatkov.
Ko spremenite drsni trak, bi se vrednost v Sheet1! $ J $ 1 spremenila, in ker so formule povezane s to celico, bi se posodobila, da bi prikazala pravilne vrednosti.
Ker je pogojno oblikovanje nestanovitno, se takoj, ko se vrednost spremeni, tudi posodobi.
Oglejte si video - Dinamični toplotni zemljevid v Excelu
Primer 2: Ustvarjanje dinamičnega toplotnega zemljevida v Excelu z radijskimi gumbi
Tu je še en primer, kjer lahko spremenite toplotni zemljevid z izbiro izbirnega gumba:
V tem primeru lahko označite zgornjih/spodnjih 10 vrednosti glede na izbiro radia/gumba za izbiro.
Kliknite tukaj, če želite prenesti predlogo toplotnega zemljevida
Ustvarjanje toplotnega zemljevida v vrtilni tabeli Excel
Pogojno oblikovanje v vrtilnih tabelah deluje na enak način kot pri vseh običajnih podatkih.
Vendar morate vedeti nekaj pomembnega.
Naj vzamem primer in vam pokažem.
Recimo, da imate vrtilno tabelo, kot je prikazano spodaj:
Če želite ustvariti toplotni zemljevid v tej vrtilni tabeli Excel:
- Izberite celice (B5: D14).
- Pojdite na Domov -> Pogojno oblikovanje -> Barvne lestvice in izberite barvno lestvico, ki jo želite uporabiti.
To bi takoj ustvarilo toplotni zemljevid v vrtilni tabeli.
Težava pri tej metodi je, da če v zaledje dodate nove podatke in osvežite to vrtilno tabelo, pogojno oblikovanje ne bo uporabljeno za nove podatke.
Ko sem na primer dodal nove podatke v zadnji del, prilagodil izvorne podatke in osvežil vrtilno tabelo, lahko vidite, da se pogojno oblikovanje zanj ne uporablja.
To se zgodi, ko smo pogojno oblikovanje uporabili samo za celice B5: D14.
Če želite, da je ta toplotni zemljevid tako dinamičen, da se posodobi ob dodajanju novih podatkov, sledite tem korakom:
- Izberite celice (B5: D14).
- Pojdite na Domov -> Pogojno oblikovanje -> Barvne lestvice in izberite barvno lestvico, ki jo želite uporabiti.
- Spet pojdite na Domov -> Pogojno oblikovanje -> Upravljanje pravil.
- V upravitelju pravil pogojnega oblikovanja kliknite gumb Uredi.
- V pogovornem oknu Urejanje pravila oblikovanja izberite tretjo možnost: Vse celice, ki prikazujejo vrednosti »Prodaja« za »Datum« in »Stranka«.
Zdaj se bo pogojno oblikovanje posodobilo, ko spremenite podatke zaledja.
Opomba: Pogojno oblikovanje izgine, če spremenite polja vrstice/stolpca. Če na primer odstranite polje Datum in ga znova uporabite, se pogojno oblikovanje izgubi.
