Et varmekort i Excel er en visuel repræsentation, der hurtigt viser dig en sammenligning af et datasæt.
For eksempel i nedenstående datasæt kan jeg let opdage, hvilke måneder der var, hvor salget var lavt (markeret med rødt) sammenlignet med andre måneder.
I ovenstående datasæt tildeles farverne baseret på værdien i cellen. Farveskalaen er grøn til gul til rød med høje værdier, der får den grønne farve og lave værdier får den røde farve.
Oprettelse af et varmekort i Excel
Mens du kan oprette et varmekort i Excel ved manuelt at farvekodning af cellerne. Du bliver dog nødt til at gøre det igen, når værdierne ændres.
I stedet for det manuelle arbejde kan du bruge betinget formatering til at markere celler baseret på værdien. På denne måde, i tilfælde af at du ændrer værdierne i cellerne, vil celleens farve/format automatisk opdatere varmekortet baseret på de forud specificerede regler i betinget formatering.
I denne vejledning lærer du, hvordan du:
- Opret hurtigt et varmekort i Excel ved hjælp af betinget formatering.
- Opret et dynamisk varmekort i Excel.
- Opret et varmekort i Excel -pivottabeller.
Lad os komme igang!
Oprettelse af et varmekort i Excel ved hjælp af betinget formatering
Hvis du har et datasæt i Excel, kan du manuelt fremhæve datapunkter og oprette et varmekort.
Det ville dog være et statisk varmekort, da farven ikke ændres, når du ændrer værdien i en celle.
Derfor er betinget formatering den rigtige vej at gå, da det får farven i en celle til at ændre sig, når du ændrer værdien i den.
Antag, at du har et datasæt som vist herunder:
Her er trinene til at oprette et varmekort ved hjælp af disse data:
- Vælg datasættet. I dette eksempel ville det være B2: D13.
- Gå til Hjem -> Betinget formatering -> Farveskalaer. Det viser forskellige farvekombinationer, der kan bruges til at fremhæve dataene. Den mest almindelige farveskala er den første, hvor celler med høje værdier fremhæves med grønt og lavt i rødt. Bemærk, at når du holder musen over disse farveskalaer, kan du se live preview i datasættet.
Dette giver dig et varmekort som vist herunder:
Som standard tildeler Excel den laveste værdi rød og den grønne farve til den højeste værdi, og alle de resterende værdier får en farve baseret på værdien. Så der er en gradient med forskellige nuancer af de tre farver baseret på værdien.
Hvad nu hvis du ikke vil have en gradient og kun vil vise rød, gul og grøn. For eksempel vil du fremhæve alle værdierne mindre end sige 700 med rødt, uanset værdien. Så 500 og 650 får begge den samme røde farve, da den er mindre end 700.
At gøre dette:
- Gå til Hjem -> Betinget formatering -> Farveskalaer -> Flere muligheder.
- I dialogboksen Ny formateringsregel skal du vælge '3-farveskala' i rullemenuen Formatstil.
- Nu kan du angive minimum, midtpunkt og maksimumværdi og tildele farven til den. Da vi vil markere alle cellerne med en værdi under 700 i rødt, skal du ændre typen til Tal og værdi til 700.
- Klik på OK.
Nu får du resultatet som vist herunder. Bemærk, at alle værdierne under 700 får den samme nuance af rød farve.
Bemærk: Selvom betinget formatering er et vidunderligt værktøj, er det desværre flygtigt. Det betyder, at når der er nogen ændringer i regnearket, bliver betinget formatering genberegnet. Selvom virkningen kan være ubetydelig på små datasæt, kan det føre til en langsom Excel -projektmappe, når du arbejder med store datasæt.
Oprettelse af et dynamisk varmekort i Excel
Da betinget formatering er afhængig af værdien i en celle, genberegnes og ændres betinget formatering, så snart du ændrer værdien.
Dette gør det muligt at lave et dynamisk varmekort.
Lad os se på to eksempler på oprettelse af varmekort ved hjælp af interaktive kontroller i Excel.
Eksempel 1: Varmekort ved hjælp af rullepanel
Her er et eksempel, hvor varmekortet ændres, så snart du bruger rullefeltet til at ændre året.
Denne type dynamiske varmekort kan bruges i dashboards, hvor du har pladsbegrænsninger, men stadig vil have brugeren til at få adgang til hele datasættet.
Klik her for at downloade Heat Map -skabelonen
Hvordan opretter man dette dynamiske varmekort?
Her er det komplette datasæt, der bruges til at oprette dette dynamiske varmekort.
Her er trinene:
- I et nyt ark (eller i det samme ark) skal du indtaste månedsnavne (kopier blot det fra de originale data).
- Gå til Developer -> Controls -> Insert -> Scroll Bar. Klik nu hvor som helst i regnearket, og det vil indsætte en rullebjælke. (klik her, hvis du ikke kan finde fanen udvikler).
- Højreklik på rullefeltet, og klik på Format Control.
- Foretag følgende ændringer i dialogboksen Formatkontrol:
- Mindste værdi: 1
- Maksimal værdi 5
- Cell Link: Sheet1! $ J $ 1 (Du kan klikke på ikonet til højre og derefter manuelt vælge den celle, du vil linke til rullefeltet).
- Klik på OK.
- I celle B1 skal du indtaste formlen: = INDEX (Sheet1! $ B $ 1: $ H $ 13, ROW (), Sheet1! $ J $ 1+COLUMNS (Sheet2! $ B $ 1: B1) -1)
- Tilpas størrelsen og placer rullepanelet i bunden af datasættet.
Når du nu ændrer rullefeltet, ændres værdien i Sheet1! $ J $ 1, og da formlerne er knyttet til denne celle, opdateres den for at vise de korrekte værdier.
Da betinget formatering også er flygtig, bliver den også opdateret, så snart værdien ændres.
Se video - dynamisk varmekort i Excel
Eksempel 2: Oprettelse af et dynamisk varmekort i Excel ved hjælp af radioknapper
Her er et andet eksempel, hvor du kan ændre varmekortet ved at foretage et valg af radioknapper:
I dette eksempel kan du fremhæve top/bund 10 værdier baseret på valg af radio/optionsknap.
Klik her for at downloade Heat Map -skabelonen
Oprettelse af et varmekort i Excel -pivottabel
Betinget formatering i pivottabeller fungerer på samme måde som med normale data.
Men der er noget vigtigt, du skal vide.
Lad mig tage et eksempel og vise dig.
Antag, at du har en pivottabel som vist herunder:
Sådan opretter du et varmekort i denne Excel -pivottabel:
- Vælg cellerne (B5: D14).
- Gå til Hjem -> Betinget formatering -> Farveskalaer, og vælg den farveskala, du vil anvende.
Dette ville øjeblikkeligt skabe varmekortet i pivottabellen.
Problemet med denne metode er, at hvis du tilføjer nye data i backend og opdaterer denne pivottabel, vil den betingede formatering ikke blive anvendt på de nye data.
Da jeg f.eks. Tilføjede nye data i bagenden, justerede kildedataene og opdaterede pivottabellen, kan du se, at betinget formatering ikke anvendes på den.
Dette sker, da vi kun anvendte den betingede formatering til celler B5: D14.
Hvis du vil have, at dette varmekort skal være dynamisk, så det opdateres, når nye data tilføjes, er her trin:
- Vælg cellerne (B5: D14).
- Gå til Hjem -> Betinget formatering -> Farveskalaer, og vælg den farveskala, du vil anvende.
- Gå igen til Hjem -> Betinget formatering -> Administrer regler.
- I Manager for betinget formateringsregler skal du klikke på knappen Rediger.
- Vælg den tredje mulighed i dialogboksen Rediger formateringsregel: Alle celler, der viser 'Salgs' -værdier for' Dato 'og' Kunde '.
Nu ville den betingede formatering opdateres, når du ændrer backend -data.
Bemærk: Betinget formatering forsvinder, hvis du ændrer række-/kolonnefelterne. Hvis du f.eks. Fjerner feltet Dato og anvender det igen, vil betinget formatering gå tabt.
