En värmekarta i Excel är en visuell representation som snabbt visar en jämförande vy av en datamängd.
Till exempel i datamängden nedan kan jag enkelt upptäcka vilka månader då försäljningen var låg (markerad med rött) jämfört med andra månader.
I datauppsättningen ovan tilldelas färgerna baserat på värdet i cellen. Färgskalan är grön till gul till röd med höga värden som får den gröna färgen och låga värden får den röda färgen.
Skapa en värmekarta i Excel
Medan du kan skapa en värmekarta i Excel genom att färgkoda cellerna manuellt. Du måste dock göra om det när värdena ändras.
I stället för det manuella arbetet kan du använda villkorlig formatering för att markera celler baserat på värdet. På det här sättet, om du ändrar värdena i cellerna, uppdateras cellens färg/format automatiskt värmekartan baserat på de fördefinierade reglerna i villkorlig formatering.
I den här självstudien lär du dig att:
- Skapa snabbt en värmekarta i Excel med villkorlig formatering.
- Skapa en dynamisk värmekarta i Excel.
- Skapa en värmekarta i Excel -pivottabeller.
Låt oss börja!
Skapa en värmekarta i Excel med villkorlig formatering
Om du har en datamängd i Excel kan du manuellt markera datapunkter och skapa en värmekarta.
Det skulle dock vara en statisk värmekarta eftersom färgen inte ändras när du ändrar värdet i en cell.
Därför är villkorlig formatering rätt väg att gå eftersom det gör att färgen i en cell ändras när du ändrar värdet i den.
Anta att du har en dataset som visas nedan:
Här är stegen för att skapa en värmekarta med hjälp av dessa data:
- Välj datauppsättningen. I det här exemplet skulle det vara B2: D13.
- Gå till Hem -> Villkorlig formatering -> Färgskalor. Den visar olika färgkombinationer som kan användas för att markera data. Den vanligaste färgskalan är den första där celler med höga värden markeras med grönt och lågt i rött. Observera att när du håller musen över dessa färgskalor kan du se förhandsgranskningen live i datamängden.
Detta ger dig en värmekarta enligt nedan:
Som standard tilldelar Excel det lägsta värdet och den gröna färgen till det högsta värdet, och alla återstående värden får en färg baserat på värdet. Så det finns en lutning med olika nyanser av de tre färgerna baserat på värdet.
Tänk om du inte vill ha en lutning och bara vill visa rött, gult och grönt. Till exempel vill du markera alla värden mindre än säga 700 i rött, oavsett värde. Så 500 och 650 får båda samma röda färg eftersom det är mindre än 700.
Att göra detta:
- Gå till Hem -> Villkorlig formatering -> Färgskalor -> Fler alternativ.
- I dialogrutan Ny formateringsregel väljer du "3-färgskala" från rullgardinsmenyn Formatstil.
- Nu kan du ange lägsta, mittpunkt och maximivärde och tilldela färgen till den. Eftersom vi vill markera alla celler med ett värde under 700 i rött, ändra typen till Antal och värde till 700.
- Klicka på OK.
Nu får du resultatet som visas nedan. Observera att alla värden under 700 får samma röda nyans.
Ett ord av försiktighet: Även om villkorlig formatering är ett underbart verktyg, är det tyvärr flyktigt. Det betyder att när det blir någon ändring i kalkylbladet så beräknas villkorlig formatering om. Även om effekten kan vara försumbar på små datamängder kan det leda till en långsam Excel -arbetsbok när du arbetar med stora datamängder.
Skapa en dynamisk värmekarta i Excel
Eftersom villkorlig formatering är beroende av värdet i en cell beräknas och ändras omedelbart när du ändrar värdet.
Detta gör det möjligt att göra en dynamisk värmekarta.
Låt oss titta på två exempel på att skapa värmekartor med hjälp av interaktiva kontroller i Excel.
Exempel 1: Värmekarta med rullningsfältet
Här är ett exempel där värmekartan ändras så snart du använder rullningslisten för att ändra år.
Denna typ av dynamiska värmekartor kan användas på instrumentpaneler där du har begränsade utrymmen men ändå vill att användaren ska få tillgång till hela datamängden.
Klicka här för att ladda ner Heat Map -mallen
Hur skapar man denna dynamiska värmekarta?
Här är den kompletta datamängden som används för att skapa denna dynamiska värmekarta.
Här är stegen:
- I ett nytt blad (eller i samma blad) anger du månadsnamnen (kopiera och klistra in det från originaldata).
- Gå till Utvecklare -> Kontroller -> Infoga -> Rullningsfält. Klicka nu var som helst i kalkylbladet så infogas en rullningslist. (klicka här om du inte hittar utvecklarfliken).
- Högerklicka på rullningslisten och klicka på Format Control.
- Gör följande ändringar i dialogrutan Formatkontroll:
- Minsta värde: 1
- Maxvärde 5
- Cell Link: Sheet1! $ J $ 1 (Du kan klicka på ikonen till höger och sedan välja den cell du vill länka till rullningslisten manuellt).
- Klicka på OK.
- I cell B1 anger du formeln: = INDEX (Sheet1! $ B $ 1: $ H $ 13, ROW (), Sheet1! $ J $ 1+COLUMNS (Sheet2! $ B $ 1: B1) -1)
- Ändra storlek och placera rullningslisten längst ned i datamängden.
När du nu ändrar rullningsfältet skulle värdet i Sheet1! $ J $ 1 förändras, och eftersom formlerna är länkade till den här cellen uppdateras det för att visa de korrekta värdena.
Eftersom villkorlig formatering är flyktig, så uppdateras den också så snart värdet ändras.
Titta på video - Dynamisk värmekarta i Excel
Exempel 2: Skapa en dynamisk värmekarta i Excel med radioknappar
Här är ett annat exempel där du kan ändra värmekartan genom att välja en alternativknapp:
I det här exemplet kan du markera översta/nedre 10 värden baserat på valet av radio/alternativknapp.
Klicka här för att ladda ner Heat Map -mallen
Skapa en värmekarta i Excel -pivottabell
Villkorlig formatering i pivottabeller fungerar på samma sätt som med normal data.
Men det är något viktigt du behöver veta.
Låt mig ta ett exempel och visa dig.
Antag att du har ett pivottabell enligt nedan:
Så här skapar du en värmekarta i denna Excel -pivottabell:
- Markera cellerna (B5: D14).
- Gå till Hem -> Villkorlig formatering -> Färgskalor och välj den färgskala som du vill använda.
Detta skulle omedelbart skapa värmekartan i pivottabellen.
Problemet med den här metoden är att om du lägger till ny data i backend och uppdaterar denna pivottabell, kommer den villkorade formateringen inte att tillämpas på de nya data.
När jag till exempel lade till ny data i backend, justerade källdata och uppdaterade pivottabellen kan du se att villkorlig formatering inte tillämpas på den.
Detta händer när vi tillämpade den villkorade formateringen endast på cellerna B5: D14.
Om du vill att denna värmekarta ska vara dynamisk så att den uppdateras när ny data läggs till, här är stegen:
- Markera cellerna (B5: D14).
- Gå till Hem -> Villkorlig formatering -> Färgskalor och välj den färgskala som du vill använda.
- Återigen går du till Hem -> Villkorlig formatering -> Hantera regler.
- I den villkorliga formateringsregelhanteraren klickar du på knappen Redigera.
- Välj det tredje alternativet i dialogrutan Redigera formateringsregel: Alla celler som visar "Försäljnings" -värden för "Datum" och "Kund".
Nu uppdateras den villkorade formateringen när du ändrar backend -data.
Notera: Villkorsstyrd formatering försvinner om du ändrar rad-/kolumnfälten. Om du till exempel tar bort datumfältet och använder det igen, kommer villkorlig formatering att gå förlorad.
