Dynamiske diagrammer, der bruger Excels OFFSET-funktion og områdenavne, ændrer størrelse, når der tilføjes eller slettes data. I modsætning til at bruge tabeller til dynamiske diagrammer er denne metode bagudkompatibel med tidlige versioner af Excel.
Brug Excels OFFSET- og COUNTA-funktioner til at oprette dynamiske intervalnavne, der automatisk justeres, når der tilføjes eller slettes data.
Klik for at downloade eksempelfilen med vejledning om dynamisk størrelsesændring af diagrammer ved hjælp af OFFSET og intervalnavne. Eksemplet indeholder både horisontale og vertikale data.
Metoden OFFSET og område-navn kræver mere arbejde end de to klik, der bruges til en tabel, men den giver dig kontrol over alt vedrørende diagramdataene, som min bog “Balanced Scorecards and Operational Dashboards with Microsoft Excel” viser. Dette websted, CriticalToSuccess.com, har eksempler på andre måder at bruge OFFSET på, f.eks. oprettelse af et rulningsdiagram, styring af diagrammer med drop-down-menuer og meget mere. Søg på CriticalToSuccess.com ved at bruge udtrykket “OFFSET” eller “dynamisk område” for at se eksempler.
Skabelse af et statisk diagram
Begynd dit dynamiske diagram ved først at oprette et statisk diagram på følgende måde,
1. Klik inden for området B7:D12, så Excel ved, hvor diagramdataene er placeret.
2. Vælg et simpelt diagram som f.eks. et kolonne-, søjle-, søjle- eller linjediagram på fanen Indsæt i gruppen Diagrammer.
Flyt dette diagram, så du har plads til at tilføje data under dataområdet.
De forskellige versioner af Excel har forskellige metoder til oprettelse og redigering af formler for intervalnavne. De følgende instruktioner gælder for Excel 2007, 2010 og 2013.
1. Klik på Definer navn under fanen Formler i gruppen Defineret navn i gruppen Definer navn. Feltet Definer navn vises.
Opret intervalnavne i feltet Definer navn.
2. Indtast rngVDate i tekstfeltet Navn. Navngivningskonventionerne er,
rng Range Name
V Lodret orientering af data i B7:B16
Date Typen af data i intervallet
3. Skriv en kommentar i tekstboksen Kommentar, der kan hjælpe dig eller andre med at vide, hvad dette intervalnavn er til.
4. Skriv OFFSET-formlen i feltet Refererer til, som beregner intervallet af Datadata.
=OFFSET(wrksht!$B$7,0,0,0,COUNTA(wrksht!$B$7:$B$16),1)
5. Klik på Ok.
Med denne OFFSET-formel kan du indtaste yderligere datoer hvor som helst i intervallet B7:B16, så længe de nye datoer ligger nederst på den gamle liste over datoer uden tomme celler mellem datoerne.
Gentag nu den samme proces for at oprette intervalnavne for Salgs- og Enhedsdata.
De navne og formler, du skal tilføje, er,
Dataområde-navn refererer til
Salg rngVSales =OFFSET(wrksht!$C$7,0,0,0,COUNTA(wrksht!$C$7:$C$16),1)
Enheder rngVUnits =OFFSET(wrksht!$D$7,0,0,0,COUNTA(wrksht!$D$7:$D$16),1)
Klik her for at lære, hvordan OFFSET beregner størrelsen af navnet på det dynamiske område. Denne artikel giver dig også tips om kontrol af dynamiske område-navne.
Klik her for at lære den fejl, du aldrig vil begå med OFFSET. Gør du denne fejl?
Klik for at downloade eksempelfilen med tutorial om dynamisk størrelsesændring af diagrammer ved hjælp af OFFSET og intervalnavne. Eksemplet indeholder både horisontale og vertikale data.
Nu kan du gøre dine diagrammer dynamiske ved at indsætte de intervalnavne, du har oprettet, i de formler, som diagrammerne bruger til at henvise til data. Du skal ikke være bekymret. Dette er meget ligetil. Faktisk giver det at lære at gøre dette dig mystiske evner og magt til at bøje ethvert diagram efter din vilje. (Det giver dig også mulighed for at rette diagrammer, der bliver ødelagt.)
Overfor har du oprettet tre dynamiske intervalnavne, der henviser til data, som skal bruges af diagrammet,
rngVDate
rngVSales
rngVUnits
Der følgende erstatter de cellehenvisninger, der bruges af diagrammet, med de dynamiske intervalnavne, du har oprettet,
1. Vælg diagrammet ved at klikke på dets kant.
2. Klik på værktøjet Vælg data under fanen Diagramværktøjer Design i gruppen Data i gruppen Data for at få vist dialogboksen Vælg datakilde.
Dialogboksen Vælg datakilde giver dig magisk kontrol over de data, som et diagram bruger.
3. Klik på Rediger under Etiketter for horisontal (kategori) akse for at få vist boksen Akseetiketter. Det er svært at se formlen i denne dialogboks, så klik på selector-ikonet til højre for cellehenvisningen. Dette viser en bredere boks med akseetiketter, hvor du kan henvise til formlen. PAS PÅ, før du redigerer!
Feltet Axis Labels viser referencen for den vandrette akse.
4. PAS PÅ! Før du forsøger at redigere formlen, skal du klikke inde i formlen og trykke på Rediger-tasten, F2, så du kan redigere formlen. Tryk på End-tasten for at gå til slutningen af formlen. Udskift cellecellehenvisningerne efter !-tegnet med intervallets navn som vist på dette billede.
Din formel skal nu se således ud,
=’Dynamisk ændre størrelsen på diagrammer til data.xlsx’!rngVDate
Feltet Axis Labels ser således ud, efter at du har trykket på F2 og redigeret formlen for at inkludere navnet på datointervallet.
TIP: Hvis du begår en fejl, når du redigerer formlen, kan du trykke på Ctrl+Z (Fortryd) for at gå tilbage.
5. Klik på Ok for at vende tilbage til dialogboksen Vælg datakilde, så du kan tilføje salgs- og enhedsnavnene.
Nu skal du erstatte salgs- og enhedsakseformlerne med intervalnavne ved hjælp af næsten samme fremgangsmåde.
1. Klik på Salg og knappen Rediger i dialogboksen Vælg datakilde for at få vist dialogboksen Rediger serie.
Erstat referencen til cellen Salgsdata med dens intervalnavn.
2. Klik på selector-ikonet til højre for formlen Serieværdi, der henviser til salgsserien.
3. Klik inde i formlen, tryk på F2 for at redigere, og skift cellehenvisningerne for Salgsdata til intervallets navn, rngVSales.
Diagrammet bruger nu det dynamiske intervalnavn til at henvise til Salgsdata.