När du skapar ett diagram i Excel och källdata ändras måste du uppdatera diagrammets datakälla för att se till att den återspeglar de nya uppgifterna.
Om du arbetar med diagram som uppdateras ofta är det bättre att skapa ett dynamiskt diagramområde.
Denna handledning omfattar:
Vad är ett dynamiskt diagramområde?
Ett dynamiskt diagramområde är ett dataområde som uppdateras automatiskt när du ändrar datakällan.
Detta dynamiska område används sedan som källdata i ett diagram. När data ändras uppdateras det dynamiska området omedelbart, vilket leder till en uppdatering i diagrammet.
Nedan följer ett exempel på ett diagram som använder ett dynamiskt diagramområde.
Notera att diagrammet uppdateras med de nya datapunkterna för maj och juni så snart som data matas in.
Hur skapar man ett dynamiskt diagramområde i Excel?
Det finns två sätt att skapa ett dynamiskt diagramområde i Excel:
- Användning av Excel-tabell
- Användning av formler
I de flesta fall är användning av Excel-tabell det bästa sättet att skapa dynamiska områden i Excel.
Låt oss se hur var och en av dessa metoder fungerar.
Klicka här för att ladda ner exempelfilen.
Använda Excel-tabell
Använda Excel-tabell är det bästa sättet att skapa dynamiska intervall eftersom den uppdateras automatiskt när en ny datapunkt läggs till.
Funktionen Excel-tabell introducerades i Excel 2007-versionen av Windows och om du har versioner före den kan du inte använda den (se nästa avsnitt om att skapa dynamiska diagramintervall med hjälp av formler).
I exemplet nedan kan du se att så snart jag lägger till nya data expanderar Excel-tabellen för att inkludera dessa data som en del av tabellen (observera att gränsen och formateringen expanderar för att inkludera dem i tabellen).
Nu måste vi använda denna Excel-tabell när vi skapar diagrammen.
Här är de exakta stegen för att skapa ett dynamiskt linjediagram med hjälp av Excel-tabellen:
- Markera hela Excel-tabellen.
- Gå till fliken Infoga.
- I gruppen Diagram väljer du ”Linje med markeringar”-diagrammet.
Det var det!
Ovanstående steg skulle infoga ett linjediagram som automatiskt uppdateras när du lägger till mer data till Excel-tabellen.
När du lägger till nya data uppdateras diagrammet automatiskt, men om du raderar data tar du inte bort datapunkterna helt och hållet. Om du till exempel tar bort 2 datapunkter kommer diagrammet att visa lite tomt utrymme till höger. För att korrigera detta drar du i den blå markeringen längst ner till höger i Excel-tabellen för att ta bort de borttagna datapunkterna från tabellen (som visas nedan).
Men även om jag har tagit exemplet med ett linjediagram kan du även skapa andra diagramtyper, t.ex. kolumn- och stapeldiagram, med hjälp av den här tekniken.
Användning av Excel-formler
Som jag nämnde är användning av Excel-tabellen det bästa sättet att skapa dynamiska diagramintervall.
Men om du av någon anledning inte kan använda Excel-tabellen (eventuellt om du använder Excel 2003) finns det ett annat (något komplicerat) sätt att skapa dynamiska diagramområden med hjälp av Excel-formler och namngivna områden.
Antag att du har data som visas nedan:
För att skapa ett dynamiskt diagramområde från dessa data måste vi:
- Skapa två dynamiska namngivna intervall med hjälp av OFFSET-formeln (ett vardera för kolumnerna ”Värden” och ”Månader”). Om du lägger till eller tar bort en datapunkt uppdateras dessa namngivna intervall automatiskt.
- Insätt ett diagram som använder de namngivna intervallerna som datakälla.
Låt mig förklara varje steg i detalj nu.
Steg 1 – Skapa dynamiska namngivna intervall
Nedan följer stegen för att skapa dynamiska namngivna intervall:
- Gå till fliken ”Formler”.
- Klicka på ’Name Manager’.
- I dialogrutan Name Manager anger du namnet ChartValues och anger följande formel i Refers to part: =OFFSET(Formula!$B$2,,,COUNTIF(Formula!$B$2:$B$100,”<>”))
- Klicka på OK.
- Klicka på New i dialogrutan Name Manager.
- I dialogrutan Name Manager anger du namnet ChartMonths och anger följande formel i Refers to part: =OFFSET(Formula!$A$2,,,COUNTIF(Formula!$A$2:$A$100,”<>”))
- Klicka på Ok.
- Klicka på Stäng.
Ovanstående steg har skapat två namngivna intervall i arbetsboken – ChartValue och ChartMonth (dessa hänvisar till värdena respektive månadsintervallet i datamängden).
Om du går och uppdaterar värdekolumnen genom att lägga till ytterligare en datapunkt, skulle det namngivna intervallet ChartValue nu automatiskt uppdateras för att visa den ytterligare datapunkten i det.
Det magiska görs av OFFSET-funktionen här.
I formeln för det namngivna intervallet ”ChartValue” har vi angett B2 som referenspunkt. OFFSET-formeln börjar där och sträcker sig till att täcka alla fyllda celler i kolumnen.
Samma logik fungerar också i formeln för det namngivna intervallet ChartMonth.
Steg 2 – Skapa ett diagram med hjälp av dessa namngivna intervall
Nu behöver du bara infoga ett diagram som kommer att använda de namngivna intervallerna som datakälla.
Här är stegen för att infoga ett diagram och använda dynamiska diagramområden:
- Gå till fliken Infoga.
- Klicka på ”Infoga linje- eller områdesdiagram” och infoga diagrammet ”Linje med markörer”. Detta kommer att infoga diagrammet i arbetsbladet.
- Med diagrammet markerat går du till fliken Design.
- Klicka på Välj data.
- I dialogrutan ”Välj datakälla” klickar du på knappen Lägg till i ”Legend Entries (Series)”.
- I fältet Series value (Värde för serie) skriver du =Formula!ChartValues (observera att du måste ange arbetsbladets namn före det namngivna intervallet för att detta ska fungera).
- Klicka på OK.
- Klicka på knappen Redigera i ”Etiketter för horisontella axlar (kategori)”.
- I dialogrutan ”Axis Labels” skriver du =Formula!ChartMonths
- Klicka på Ok.
Det var allt! Nu använder diagrammet ett dynamiskt intervall och uppdateras när du lägger till/tar bort datapunkter i diagrammet.
Några viktiga saker att känna till när du använder namngivna intervall med diagram:
- Det får inte finnas några tomma celler i diagramdata. Om det finns ett tomt fält skulle det namngivna intervallet inte hänvisa till rätt dataset (eftersom det totala antalet celler skulle leda till att det hänvisar till färre celler).
- Du måste följa namnkonventionerna när du använder arknamnet i diagramkällan. Om arknamnet till exempel är ett enda ord, till exempel Formula, kan du använda =Formula!ChartValue. Men om det finns mer än ett ord, till exempel Formula Chart, måste du använda =’Formula Chart’!ChartValue.
Du kanske också gillar följande Excel-handledningar:
- Hur man skapar ett termometerdiagram i Excel.
- Hur man gör en Bell Curve i Excel.
- Skapa ett stegdiagram i Excel.
- Skapa ett paretodiagram i Excel.
- Hur man gör ett histogram i Excel
- Hur man lägger till felstaplar i Excel (Horisontell/Vertikal/Anpassad)