Et varmekart i Excel er en visuell fremstilling som raskt viser deg en komparativ visning av et datasett.
For eksempel, i datasettet nedenfor, kan jeg enkelt finne ut hvilke måneder det var hvor salget var lavt (markert med rødt) sammenlignet med andre måneder.
I datasettet ovenfor tildeles fargene basert på verdien i cellen. Fargeskalaen er grønn til gul til rød med høye verdier som får den grønne fargen og lave verdier får den røde fargen.
Opprette et varmekart i Excel
Mens du kan lage et varmekart i Excel ved å fargekode cellene manuelt. Du må imidlertid gjøre det på nytt når verdiene endres.
I stedet for det manuelle arbeidet kan du bruke betinget formatering til å markere celler basert på verdien. På denne måten, i tilfelle du endrer verdiene i cellene, vil fargen/formatet på cellen automatisk oppdatere varmekartet basert på de forhåndsspesifiserte reglene i betinget formatering.
I denne opplæringen lærer du hvordan du:
- Lag raskt et varmekart i Excel ved hjelp av betinget formatering.
- Lag et dynamisk varmekart i Excel.
- Lag et varmekart i Excel -pivottabeller.
La oss komme i gang!
Opprette et varmekart i Excel ved hjelp av betinget formatering
Hvis du har et datasett i Excel, kan du markere datapunkter manuelt og lage et varmekart.
Det vil imidlertid være et statisk varmekart, ettersom fargen ikke ville endres når du endrer verdien i en celle.
Derfor er betinget formatering den riktige veien å gå, ettersom fargen i en celle endres når du endrer verdien i den.
Anta at du har et datasett som vist nedenfor:
Her er trinnene for å lage et varmekart ved hjelp av disse dataene:
- Velg datasettet. I dette eksemplet vil det være B2: D13.
- Gå til Hjem -> Betinget formatering -> Fargeskalaer. Den viser forskjellige fargekombinasjoner som kan brukes til å markere dataene. Den vanligste fargeskalaen er den første der celler med høye verdier er markert med grønt og lavt i rødt. Vær oppmerksom på at når du holder musen over disse fargeskalaene, kan du se forhåndsvisningen i datasettet.
Dette vil gi deg et varmekart som vist nedenfor:
Som standard tildeler Excel den laveste verdien rød og den grønne til den høyeste verdien, og alle de gjenværende verdiene får en farge basert på verdien. Så det er en gradient med forskjellige nyanser av de tre fargene basert på verdien.
Hva om du ikke vil ha en gradient og bare vil vise rødt, gult og grønt. For eksempel vil du markere alle verdiene mindre enn si 700 i rødt, uavhengig av verdien. Så 500 og 650 får begge den samme røde fargen siden den er mindre enn 700.
Å gjøre dette:
- Gå til Hjem -> Betinget formatering -> Fargeskalaer -> Flere alternativer.
- I dialogboksen Ny formateringsregel velger du "3-fargeskala" fra Format Style-rullegardinmenyen.
- Nå kan du angi minimum, midtpunkt og maksimal verdi og tildele fargen til den. Siden vi ønsker å markere alle cellene med en verdi under 700 i rødt, endrer du typen til tall og verdi til 700.
- Klikk OK.
Nå får du resultatet som vist nedenfor. Vær oppmerksom på at alle verdiene under 700 får samme røde farge.
Et forsiktig ord: Selv om betinget formatering er et fantastisk verktøy, er det dessverre flyktig. Dette betyr at når det er noen endringer i regnearket, blir betinget formatering beregnet på nytt. Selv om virkningen kan være ubetydelig på små datasett, kan det føre til en treg Excel -arbeidsbok når du arbeider med store datasett.
Opprette et dynamisk varmekart i Excel
Siden betinget formatering er avhengig av verdien i en celle, så beregnes og endres betinget formatering så snart du endrer verdien.
Dette gjør det mulig å lage et dynamisk varmekart.
La oss se på to eksempler på å lage varmekart ved hjelp av interaktive kontroller i Excel.
Eksempel 1: Varmekart ved hjelp av rullefeltet
Her er et eksempel der varmekartet endres så snart du bruker rullefeltet til å endre året.
Denne typen dynamiske varmekart kan brukes i dashbord der du har plassbegrensninger, men fortsatt vil at brukeren skal få tilgang til hele datasettet.
Klikk her for å laste ned Heat Map -malen
Hvordan lage dette dynamiske varmekartet?
Her er det komplette datasettet som brukes til å lage dette dynamiske varmekartet.
Her er trinnene:
- Skriv inn månedsnavnene i et nytt ark (eller i det samme arket) (bare kopier og lim det inn fra de originale dataene).
- Gå til Utvikler -> Kontroller -> Sett inn -> Rullelinje. Klikk nå hvor som helst i regnearket, og det vil sette inn en rullefelt. (klikk her hvis du ikke finner utviklerfanen).
- Høyreklikk på rullefeltet og klikk på Format Control.
- Gjør følgende endringer i dialogboksen Formatkontroll:
- Minste verdi: 1
- Maksimal verdi 5
- Cell Link: Sheet1! $ J $ 1 (Du kan klikke på ikonet til høyre og deretter velge cellen du vil koble til rullefeltet manuelt).
- Klikk OK.
- I celle B1 skriver du inn formelen: = INDEX (Sheet1! $ B $ 1: $ H $ 13, ROW (), Sheet1! $ J $ 1+COLUMNS (Sheet2! $ B $ 1: B1) -1)
- Endre størrelse og plasser rullefeltet nederst i datasettet.
Når du endrer rullefeltet, vil verdien i Sheet1! $ J $ 1 endres, og siden formlene er knyttet til denne cellen, vil den oppdatere seg for å vise de riktige verdiene.
Siden betinget formatering er ustabil, blir den også oppdatert så snart verdien endres.
Se video - Dynamisk varmekart i Excel
Eksempel 2: Opprette et dynamisk varmekart i Excel ved hjelp av radioknapper
Her er et annet eksempel hvor du kan endre varmekartet ved å velge et radioknappvalg:
I dette eksemplet kan du markere topp/bunn 10 verdier basert på valg av radio/alternativknapp.
Klikk her for å laste ned Heat Map -malen
Opprette et varmekart i Excel -pivottabell
Betinget formatering i pivottabeller fungerer på samme måte som med vanlige data.
Men det er noe viktig du må vite.
La meg ta et eksempel og vise deg.
Anta at du har et pivottabell som vist nedenfor:
Slik oppretter du et varmekart i denne Excel -pivottabellen:
- Velg cellene (B5: D14).
- Gå til Hjem -> Betinget formatering -> Fargeskalaer og velg fargeskalaen du vil bruke.
Dette ville umiddelbart lage varmekartet i pivottabellen.
Problemet med denne metoden er at hvis du legger til nye data i backend og oppdaterer denne pivottabellen, vil ikke den betingede formateringen bli brukt på de nye dataene.
Når jeg for eksempel la til nye data i bakenden, justerte kildedataene og oppdaterte pivottabellen, kan du se at betinget formatering ikke brukes på den.
Dette skjer når vi bare brukte den betingede formateringen på cellene B5: D14.
Hvis du vil at dette varmekartet skal være dynamisk slik at det oppdateres når nye data legges til, følger du trinnene:
- Velg cellene (B5: D14).
- Gå til Hjem -> Betinget formatering -> Fargeskalaer og velg fargeskalaen du vil bruke.
- Gå igjen til Hjem -> Betinget formatering -> Administrer regler.
- Klikk på Rediger -knappen i Manager for betinget formateringsregler.
- Velg det tredje alternativet i dialogboksen Rediger formateringsregel: Alle celler som viser "Salgs" -verdier for "Dato" og "Kunde".
Nå vil den betingede formateringen oppdateres når du endrer backend -dataene.
Merk: Betinget formatering forsvinner hvis du endrer rad/kolonne -feltene. For eksempel, hvis du fjerner Dato -feltet og bruker det igjen, vil betinget formatering gå tapt.
