Dynamiska diagram som använder Excels OFFSET-funktion och intervallnamn ändrar storlek när data läggs till eller tas bort. Till skillnad från att använda tabeller för dynamiska diagram är den här metoden bakåtkompatibel med tidiga versioner av Excel.
Använd Excels OFFSET- och COUNTA-funktioner för att skapa dynamiska intervallnamn som automatiskt justeras när data läggs till eller tas bort.
Klicka för att ladda ner exempelfilen för handledning om dynamisk storleksändring av diagram med OFFSET och intervallnamn. Exemplet innehåller både horisontella och vertikala data.
Metoden OFFSET och intervallnamn kräver mer arbete än de två klick som används för en tabell, men den ger dig kontroll över allt som rör diagramdata, vilket min bok ”Balanced Scorecards and Operational Dashboards with Microsoft Excel” visar. På den här webbplatsen, CriticalToSuccess.com, finns exempel på andra sätt att använda OFFSET, t.ex. genom att skapa ett rulldiagram, styra diagram med rullgardinsmenyer med mera. Sök på CriticalToSuccess.com med termen ”OFFSET” eller ”dynamiskt intervall” för att se exempel.
Skapa ett statiskt diagram
Begynna ditt dynamiska diagram genom att först skapa ett statiskt diagram enligt följande,
1. Klicka i intervallet B7:D12 så att Excel vet var diagramdata finns.
2. På fliken Infoga, i gruppen Diagram, väljer du ett enkelt diagram som ett kolumn-, stapel- eller linjediagram.
Förflytta diagrammet så att du har plats att lägga till data under datavärdet.
Skapa dynamiska intervallnamn med OFFSET
Olika Excel-versioner har olika metoder för att skapa och redigera formler för intervallnamn. Följande instruktioner gäller Excel 2007, 2010 och 2013.
1. Klicka på Definiera namn på fliken Formler i gruppen Definierat namn. Rutan Definiera namn visas.
Skapa intervallnamn i rutan Definiera namn.
2. Ange rngVDate i textrutan Namn. Namnkonventionerna är,
rng Range Name
V Vertikal orientering av data i B7:B16
Date Typ av data i intervallet
3. Skriv en kommentar i textrutan kommentar som hjälper dig eller andra att veta vad det här intervallnamnet är till för.
4. I rutan Refererar till skriver du formeln OFFSET som beräknar intervallet med datumdata.
=OFFSET(wrksht!$B$7,0,0,0,COUNTA(wrksht!$B$7:$B$16),1)
5. Klicka på Ok.
Med denna OFFSET-formel kan du ange ytterligare datum var som helst i intervallet B7:B16 så länge de nya datumen ligger längst ner i den gamla listan med datum utan tomma celler mellan datumen.
Nu kan du upprepa samma process för att skapa intervallnamn för data om försäljning och enheter.
Namnen och formlerna du kommer att lägga till är,
Dataområdesnamn hänvisar till
Sales rngVSales =OFFSET(wrksht!$C$7,0,0,COUNTA(wrksht!$C$7:$C$16),1)
Enheter rngVUnits =OFFSET(wrksht!$D$7,0,0,COUNTA(wrksht!$D$7:$D$16),1)
Klicka här för att lära dig hur OFFSET beräknar storleken på det dynamiska intervallnamnet. I den här artikeln får du också tips om hur du kontrollerar dynamiska intervallnamn.
Klicka här för att lära dig det misstag du aldrig vill göra med OFFSET. Gör du det här misstaget?
Klicka för att ladda ner en exempelfil med handledning om dynamisk storleksändring av diagram med hjälp av OFFSET och intervallnamn. Exemplet innehåller både horisontella och vertikala data.
Infoga ett intervallnamn i diagrammets datumformel
Nu kan du göra dina diagram dynamiska genom att infoga de intervallnamn som du har skapat i de formler som diagrammen använder för att referera data. Oroa dig inte. Det här är mycket enkelt. Faktum är att om du lär dig hur man gör detta får du mystiska förmågor och kraften att böja vilket diagram som helst efter din vilja. (Det gör det också möjligt för dig att fixa diagram som blir röriga.)
Ovan skapade du tre dynamiska intervallnamn som refererar till data som ska användas av diagrammet,
rngVDate
rngVSales
rngVUnits
Följande ersätter cellreferenserna som diagrammet använder med de dynamiska intervallnamn som du skapat,
1. Markera diagrammet genom att klicka på dess kant.
2. På fliken Diagramverktyg Design, i gruppen Data, klickar du på verktyget Välj data för att visa dialogrutan Välj datakälla.
Dialogrutan Välj datakälla ger dig magisk kontroll över de data som diagrammet använder.
3. Under Etiketter för horisontell axel (kategori) klickar du på Redigera för att visa rutan Etiketter för axlar. Det är svårt att se formeln i den här dialogrutan, så klicka på valikonen till höger om cellreferensen. Då visas en bredare ruta Axis Labels där du kan referera till formeln. Var försiktig innan du redigerar!
Rutan Axis Labels visar referensen för den horisontella axeln.
4. Var försiktig! Innan du försöker redigera formeln klickar du i formeln och trycker på Redigera-tangenten, F2, så att du kan redigera formeln. Tryck på End-tangenten för att gå till slutet av formeln. Ersätt cellreferenserna efter !-tecknet med intervallnamnet som visas i den här bilden.
Din formel ska nu se ut så här,
=’Dynamiskt ändra storlek på diagram till data.xlsx’!rngVDate
Rutan för axeletiketter ser ut så här efter att du har tryckt på F2 och redigerat formeln för att inkludera namnet på datumintervallet.
TIP: Om du gör ett misstag när du redigerar formeln kan du trycka på Ctrl+Z (Ångra) för att gå tillbaka.
5. Klicka på Ok för att återgå till dialogrutan Välj datakälla så att du kan lägga till namnen på försäljning och enheter.
Insättning av ett intervallnamn i formeln för försäljning och enheter i diagrammet
Nu kan du byta ut formlerna för axeln för försäljning och enheter mot intervallnamn genom att använda nästan samma process.
1. I dialogrutan Välj datakälla klickar du på Försäljning och på knappen Redigera för att visa dialogrutan Redigera serie.
Ersätt cellhänvisningen Salesdata med dess intervallnamn.
2. Klicka på ikonen för val av väljare till höger om formeln Serievärde som hänvisar till försäljningsserien.
3. Klicka i formeln, tryck på F2 för att redigera och ändra cellreferenserna för försäljningsdata till intervallnamnet rngVSales.
Diagrammet använder nu det dynamiska intervallnamnet för att referera till försäljningsdata.