Lämpökartta Excelissä on visuaalinen esitys, joka näyttää nopeasti tietojoukon vertailukuvan.
Esimerkiksi alla olevasta tietojoukosta voin helposti havaita kuukaudet, jolloin myynti oli alhainen (korostettu punaisella) verrattuna muihin kuukausiin.
Yllä olevassa tietojoukossa värit määritetään solun arvon perusteella. Väriasteikko on vihreästä keltaisesta punaiseen, ja suuret arvot saavat vihreän värin ja pienet arvot punaisen värin.
Lämpökartan luominen Excelissä
Vaikka voit luoda lämpökartan Excelissä manuaalisesti värikoodaamalla solut. Sinun on kuitenkin tehtävä se uudelleen, kun arvot muuttuvat.
Manuaalisen työn sijasta voit käyttää ehdollista muotoilua korostamaan solut arvon perusteella. Jos muutat solujen arvoja, solun väri/muoto päivittää automaattisesti lämpökartan ennalta määritettyjen sääntöjen perusteella ehdollisessa muotoilussa.
Tässä opetusohjelmassa opit:
- Luo nopeasti lämpökartta Excelissä ehdollisen muotoilun avulla.
- Luo dynaaminen lämpökartta Excelissä.
- Luo lämpökartta Excelin pivot -taulukoissa.
Aloitetaan!
Lämpökartan luominen Excelissä käyttämällä ehdollista muotoilua
Jos sinulla on Excelissä tietojoukko, voit korostaa datapisteet manuaalisesti ja luoda lämpökartan.
Se olisi kuitenkin staattinen lämpökartta, koska väri ei muutu, kun muutat solun arvoa.
Ehdollinen muotoilu on siis oikea tapa edetä, koska se muuttaa solun värin, kun muutat sen arvoa.
Oletetaan, että sinulla on alla näkyvä tietojoukko:
Tässä on vaiheet lämpökartan luomiseksi näiden tietojen perusteella:
- Valitse tietojoukko. Tässä esimerkissä se olisi B2: D13.
- Siirry etusivulle -> Ehdollinen muotoilu -> Väriasteikot. Se näyttää erilaisia väriyhdistelmiä, joita voidaan käyttää tietojen korostamiseen. Yleisin väriasteikko on ensimmäinen, jossa suuret arvot sisältävät solut on korostettu vihreällä ja matala punaisella. Huomaa, että kun viet hiiren näiden väriasteikkojen päälle, näet reaaliaikaisen esikatselun tietojoukossa.
Tämä antaa sinulle lämpökartan alla olevan kuvan mukaisesti:
Oletuksena Excel määrittää punaisen värin alimmalle arvolle ja vihreän värin suurimmalle arvolle, ja kaikki muut arvot saavat värin arvon perusteella. Joten on olemassa gradientti, jossa on kolme sävyä eri väreillä arvon perusteella.
Entä jos et halua kaltevuutta ja haluat näyttää vain punaisen, keltaisen ja vihreän. Haluat esimerkiksi korostaa kaikki alle 700: n arvot punaisella arvosta riippumatta. Joten sekä 500 että 650 saavat saman punaisen värin, koska se on alle 700.
Tehdä tämä:
- Siirry etusivulle -> Ehdollinen muotoilu -> Väriasteikot -> Lisää vaihtoehtoja.
- Valitse Uusi muotoilusääntö -valintaikkunassa avattavasta Muotoilutyyli-valikosta "3-väriasteikko".
- Nyt voit määrittää minimi-, keskipiste- ja enimmäisarvon ja määrittää sille värin. Koska haluamme korostaa punaisella kaikki solut, joiden arvo on alle 700, vaihda tyypiksi Luku ja arvo 700.
- Napsauta OK.
Nyt saat tuloksen, kuten alla on esitetty. Huomaa, että kaikki arvot alle 700 saavat saman punaisen sävyn.
Varoituksen sana: Ehdollinen muotoilu on loistava työkalu, mutta valitettavasti se on epävakaa. Tämä tarkoittaa, että aina kun laskentataulukossa tapahtuu muutoksia, ehdollinen muotoilu lasketaan uudelleen. Vaikka vaikutus voi olla vähäinen pienille tietojoukoille, se voi johtaa hitaaseen Excel -työkirjaan, kun käsitellään suuria tietojoukkoja.
Dynaamisen lämpökartan luominen Excelissä
Koska ehdollinen muotoilu riippuu solun arvosta, heti kun muutat arvoa, ehdollinen muotoilu laskee uudelleen ja muuttuu.
Tämä mahdollistaa dynaamisen lämpökartan tekemisen.
Katsotaanpa kahta esimerkkiä lämpökarttojen luomisesta interaktiivisilla ohjaimilla Excelissä.
Esimerkki 1: Lämpökartta vierityspalkin avulla
Tässä on esimerkki, jossa lämpökartta muuttuu heti, kun muutat vuosia vierityspalkin avulla.
Tämäntyyppisiä dynaamisia lämpökarttoja voidaan käyttää koontinäytöissä, joissa on tilaa rajoitetusti, mutta haluat silti käyttäjän pääsyn koko tietojoukkoon.
Lataa Heat Map -malli napsauttamalla tätä
Kuinka luoda tämä dynaaminen lämpökartta?
Tässä on koko tietojoukko, jota käytetään tämän dynaamisen lämpökartan luomiseen.
Tässä on vaiheet:
- Kirjoita kuukausien nimet uudelle arkille (tai samaan taulukkoon) (kopioi ja liitä se alkuperäisistä tiedoista).
- Siirry kohtaan Kehittäjä -> Ohjaimet -> Lisää -> Vierityspalkki. Napsauta nyt mitä tahansa laskentataulukon kohtaa, ja se lisää vierityspalkin. (napsauta tätä, jos et löydä kehittäjä -välilehteä).
- Napsauta vierityspalkkia hiiren kakkospainikkeella ja napsauta Muoto-ohjaus.
- Tee Muoto -ohjaus -valintaikkunassa seuraavat muutokset:
- Vähimmäisarvo: 1
- Suurin arvo 5
- Solulinkki: Sheet1! $ J $ 1 (Voit napsauttaa oikealla olevaa kuvaketta ja valita manuaalisesti solun, jonka haluat linkittää vierityspalkkiin).
- Napsauta OK.
- Kirjoita soluun B1 kaava: = INDEKSI (Sheet1! $ B $ 1: $ H $ 13, ROW (), Sheet1! $ J $ 1+COLUMNS (Sheet2! $ B $ 1: B1) -1)
- Muuta kokoa ja aseta vierityspalkki tietojoukon alareunaan.
Nyt kun muutat vierityspalkkia, Sheet1! $ J $ 1: n arvo muuttuu, ja koska kaavat on linkitetty tähän soluun, se päivittyy näyttämään oikeat arvot.
Lisäksi koska ehdollinen muotoilu on epävakaa, se päivittyy heti, kun arvo muuttuu.
Katso video - Dynaaminen lämpökartta Excelissä
Esimerkki 2: Dynaamisen lämpökartan luominen Excelissä radiopainikkeilla
Tässä on toinen esimerkki, jossa voit muuttaa lämpökarttaa valitsemalla valintanapin:
Tässä esimerkissä voit korostaa ylin/alin 10 arvoa radio/vaihtoehto -painikkeen valinnan perusteella.
Lataa Heat Map -malli napsauttamalla tätä
Lämpökartan luominen Excel -pivot -taulukossa
Pivot -taulukoiden ehdollinen muotoilu toimii samalla tavalla kuin normaalit tiedot.
Mutta sinun on tiedettävä jotain tärkeää.
Otan esimerkin ja näytän sinulle.
Oletetaan, että sinulla on pivot -taulukko alla olevan kuvan mukaisesti:
Lämpökartan luominen tässä Excel -pivot -taulukossa:
- Valitse solut (B5: D14).
- Siirry etusivulle -> Ehdollinen muotoilu -> Väriasteikot ja valitse väriasteikko, jota haluat käyttää.
Tämä luo välittömästi lämpökartan pivot -taulukkoon.
Tämän menetelmän ongelma on se, että jos lisäät uusia tietoja taustajärjestelmään ja päivität tämän pivot -taulukon, ehdollista muotoilua ei käytetä uusiin tietoihin.
Esimerkiksi kun lisäsin uusia tietoja takapäähän, säädin lähdetietoja ja päivitin pivot -taulukon, näet, että ehdollista muotoilua ei sovelleta siihen.
Näin tapahtuu, kun ehdollista muotoilua sovellettiin vain soluihin B5: D14.
Jos haluat tämän lämpökartan olevan dynaaminen ja päivittyvän, kun uusia tietoja lisätään, toimi seuraavasti:
- Valitse solut (B5: D14).
- Siirry etusivulle -> Ehdollinen muotoilu -> Väriasteikot ja valitse väriasteikko, jota haluat käyttää.
- Siirry jälleen etusivulle -> Ehdollinen muotoilu -> Hallitse sääntöjä.
- Napsauta Ehdollisen muotoilun sääntöjenhallinnassa Muokkaa -painiketta.
- Valitse Muokkaa muotoilusääntöä -valintaikkunassa kolmas vaihtoehto: Kaikki solut, joissa näkyy Päivämäärä- ja Asiakas -arvon Myynti -arvot.
Nyt ehdollinen muotoilu päivittyy, kun muutat taustaohjelman tietoja.
merkintä: Ehdollinen muotoilu katoaa, jos muutat rivi-/sarakekenttiä. Jos esimerkiksi poistat Päivämäärä -kentän ja otat sen uudelleen käyttöön, ehdollinen muotoilu menetetään.
