en värmekarta i Excel är en visuell representation som snabbt visar en jämförande bild av en dataset.
till exempel i datauppsättningen nedan kan jag enkelt upptäcka vilka månader då försäljningen var låg (markerad i rött) jämfört med andra månader.
i ovanstående dataset 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 som får den röda färgen.
denna handledning täcker:
skapa en värmekarta i Excel
medan du kan skapa en värmekarta i Excel genom att manuellt färgkoda cellerna. Du måste dock göra om det när värdena ändras.
istället för det manuella arbetet kan du använda villkorlig formatering för att markera celler baserat på värdet. På så sätt, om du ändrar värdena i cellerna, uppdaterar cellens färg/format automatiskt värmekartan baserat på de förutbestämda reglerna i villkorlig formatering.
i den här handledningen lär du dig att:
- snabbt skapa 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 komma igång!
skapa en värmekarta i Excel med Villkorsstyrd formatering
Om du har en datauppsättning i Excel kan du manuellt markera datapunkter och skapa en värmekarta.
det skulle dock vara en statisk värmekarta eftersom färgen inte skulle ä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 i grönt och lågt i rött. Observera att när du håller muspekaren över dessa färgskalor kan du se förhandsgranskningen i datamängden.
detta ger dig en värmekarta som visas nedan:
som standard tilldelar Excel röd färg till 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 ett värde färg baserat på värdet. Så det finns en lutning med olika nyanser av de tre färgerna baserat på värdet.
nu, vad händer om inte vill ha en lutning och bara vill visa rött, gult och grönt. Du vill till exempel markera alla värden som är mindre än 700 i rött, oavsett värdet. Så 500 och 650 får båda samma röda färg eftersom det är mindre än 700.
För 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 Format.
- nu kan du ange minsta, mittpunkten och det maximala värdet och tilldela färgen till den. Eftersom vi vill markera alla celler med ett värde under 700 i rött, ändra typen till nummer 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 nyans av röd färg.
ett varningens ord: medan villkorlig formatering är ett underbart verktyg, är det tyvärr flyktigt. Detta innebär att när det finns någon förändring i kalkylbladet, villkorlig formatering blir omräknat. Ä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, så snart du ändrar värdet, beräknas villkorlig formatering om och ändras.
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 interaktiva kontroller i Excel.
exempel 1: värmekarta med rullningslist
Här är ett exempel där värmekartan ändras så snart du använder rullningslisten för att ändra året.
denna typ av dynamiska värmekartor kan användas i instrumentpaneler där du har utrymmesbegränsningar men ändå vill att användaren ska komma åt hela datamängden.
Klicka här för att ladda ner Heat Map-mallen
hur skapar du den här dynamiska värmekartan?
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 ark (eller i samma ark) anger du månadsnamnen (kopiera bara klistra in det från originaldata).
- gå till utvecklare – > kontroller –> infoga –> rullningslist. Klicka nu var som helst i kalkylbladet, och det kommer att infoga en rullningslist. (klicka här om du inte hittar fliken Utvecklare).
- högerklicka på rullningsfältet och klicka på Formatkontroll.
- i dialogrutan Formatkontroll gör du följande ändringar:
- minimivärde: 1
- maxvärde 5
- celllänk: Ark1!$J$1 (Du kan klicka på ikonen till höger och sedan manuellt välja den cell du vill länka till rullningsfältet).
- Klicka på OK.
- i cell B1 anger du formeln: = INDEX (Ark1!$B$1:$H$13, rad (), Ark1!$ J$1 + kolumner (Ark2!$ B$1:B1) -1)
- ändra storlek och placera rullningslisten längst ner i datamängden.
nu när du ändrar rullningslisten, värdet i Ark1!$J$1 skulle förändras, och eftersom formlerna är kopplade till den här cellen skulle den uppdateras för att visa rätt värden.
eftersom villkorlig formatering är flyktig, så snart värdet ändras, uppdateras det också.
titta på Video-dynamisk värmekarta i Excel
exempel 2: Skapa en dynamisk värmekarta i Excel med hjälp av radioknappar
Här är ett annat exempel där du kan ändra värmekartan genom att göra ett val av radioknapp:
i det här exemplet kan du markera topp/botten 10 värden baserat på valet av radio/Alternativ.
Klicka här för att ladda ner Heat Map-mallen
skapa en Heat Map i Excel pivottabell
Villkorlig formatering i pivottabeller fungerar på samma sätt som med alla normala data.
men det finns något viktigt du behöver veta.
Låt mig ta ett exempel och visa dig.
anta att du har en pivottabell som visas nedan:
för att skapa en värmekarta i denna Excel-pivottabell:
- välj cellerna (B5:D14).
- gå till Home – > 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 nya data i backend och uppdaterar den här pivottabellen, skulle den villkorliga formateringen inte tillämpas på de nya data.
till exempel, när jag lade till nya data i baksidan, 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 villkorlig formatering endast på celler B5:D14.
Om du vill att denna värmekarta ska vara dynamisk så att den uppdateras när nya data läggs till, här är steg:
- välj cellerna (B5:D14).
- gå till Home – > Villkorlig formatering –> färgskalor och välj den färgskala som du vill använda.
- gå igen till hem –>Villkorlig formatering –> Hantera regler.
- i den villkorliga Formateringsreglerhanteraren klickar du på knappen Redigera.
- i dialogrutan Redigera formateringsregel väljer du det tredje alternativet: Alla celler som visar ”försäljningsvärden” för ”datum” och ”kund”.
nu skulle den villkorliga formateringen uppdateras när du ändrar backend-data.
Obs! Villkorsstyrd formatering försvinner om du ändrar rad – / kolumnfälten. Om du till exempel tar bort datumfältet och tillämpar det igen går villkorlig formatering förlorad.
Du kanske också gillar följande Excel-handledning:
- hur man markerar varannan rad i Excel.
- skapa 100% Stapelkolumndiagram med villkorlig formatering.
- hur man räknar färgade celler i Excel.
- så här markerar du tomma celler i Excel.
- Excel Sparklines-en komplett Guide