Når du oppretter et diagram i Excel og kildedata endres, må du oppdatere diagramets datakilde for å sikre at den gjenspeiler de nye dataene.
Hvis du jobber med diagrammer som oppdateres ofte, er det bedre å lage et dynamisk diagramområde.
Hva er et dynamisk kartområde?
Et dynamisk diagramområde er et dataområde som oppdateres automatisk når du endrer datakilden.
Dette dynamiske området brukes deretter som kildedata i et diagram. Etter hvert som dataene endres, oppdateres det dynamiske området umiddelbart, noe som fører til en oppdatering i diagrammet.
Nedenfor er et eksempel på et diagram som bruker et dynamisk diagramområde.
Vær oppmerksom på at diagrammet oppdateres med de nye datapunktene for mai og juni så snart dataene er lagt inn.
Hvordan lage et dynamisk diagramområde i Excel?
Det er to måter å lage et dynamisk diagramområde i Excel:
- Bruke Excel -tabell
- Bruke formler
I de fleste tilfeller er bruk av Excel -tabell den beste måten å lage dynamiske områder i Excel.
La oss se hvordan hver av disse metodene fungerer.
Klikk her for å laste ned eksempelfilen.
Bruke Excel -tabell
Å bruke Excel -tabell er den beste måten å opprette dynamiske områder på, ettersom det oppdateres automatisk når et nytt datapunkt legges til.
Excel -tabellfunksjonen ble introdusert i Excel 2007 -versjonen av Windows, og hvis du har versjoner før den, kan du ikke bruke den (se neste avsnitt om hvordan du oppretter dynamisk diagramområde ved hjelp av formler).
Profftips: For å konvertere en rekke celler til en Excel -tabell, velg cellene og bruk hurtigtasten - Ctrl + T (hold Ctrl -tasten og trykk T -tasten).I eksemplet nedenfor kan du se at så snart jeg legger til nye data, utvides Excel -tabellen til å inkludere disse dataene som en del av tabellen (merk at grensen og formateringen utvides for å inkludere den i tabellen).
Nå må vi bruke denne Excel -tabellen mens vi lager diagrammene.
Her er de nøyaktige trinnene for å lage et dynamisk linjediagram ved hjelp av Excel -tabellen:
- Velg hele Excel -tabellen.
- Gå til kategorien Sett inn.
- Velg diagrammet «Linje med markører» i diagramgruppen.
Det er det!
Trinnene ovenfor vil sette inn et linjediagram som automatisk oppdateres når du legger til flere data i Excel -tabellen.
Vær oppmerksom på at mens du legger til nye data automatisk oppdaterer diagrammet, vil sletting av data ikke helt fjerne datapunktene. For eksempel, hvis du fjerner to datapunkter, viser diagrammet en tom plass til høyre. For å korrigere dette, dra det blå merket nederst til høyre i Excel -tabellen for å fjerne de slettede datapunktene fra tabellen (som vist nedenfor).
Selv om jeg har tatt eksemplet på et linjediagram, kan du også opprette andre diagramtyper, for eksempel kolonne-/stolpediagrammer ved å bruke denne teknikken.
Bruke Excel -formler
Som jeg nevnte, er bruk av Excel -tabell den beste måten å lage dynamiske diagramområder.
Men hvis du av en eller annen grunn ikke kan bruke Excel -tabellen (muligens hvis du bruker Excel 2003), er det en annen (litt komplisert) måte å lage dynamiske diagramområder med Excel -formler og navngitte områder.
Anta at du har datasettet som vist nedenfor:
For å lage et dynamisk diagramområde fra disse dataene, må vi:
- Lag to dynamiske navngitte områder ved å bruke OFFSET -formelen (ett hver for kolonnen "Verdier" og "Måneder"). Hvis du legger til/sletter et datapunkt, oppdateres disse navngitte områdene automatisk.
- Sett inn et diagram som bruker de navngitte områdene som en datakilde.
La meg forklare hvert trinn i detalj nå.
Trinn 1 - Opprette dynamiske navngitte områder
Nedenfor er trinnene for å lage dynamiske navngitte områder:
- Gå til "Formler" -fanen.
- Klikk på "Name Manager".
- Angi navnet som i dialogboksen Navnebehandler ChartValues og skriv inn følgende formel i Refererer til del: = OFFSET (Formula! $ B $ 2 ,,, COUNTIF (Formula! $ B $ 2: $ B $ 100, ””))
- Klikk OK.
- Klikk på Ny i dialogboksen Navnebehandler.
- Angi navnet som i dialogboksen Navnebehandler ChartMonths og skriv inn følgende formel i Refererer til del: = OFFSET (Formula! $ A $ 2 ,,, COUNTIF (Formula! $ A $ 2: $ A $ 100, ””))
- Klikk Ok.
- Klikk på Lukk.
Trinnene ovenfor har opprettet to navngitte områder i arbeidsboken - ChartValue og ChartMonth (disse refererer til verdiene og månedsområdet i datasettet henholdsvis).
Hvis du går og oppdaterer verdikolonnen ved å legge til et datapunkt til, vil ChartValue -navngitte området nå automatisk oppdateres for å vise tilleggsdatapunktet i det.
Magien gjøres med OFFSET -funksjonen her.
I formelen "ChartValue" med navnet, har vi spesifisert B2 som referansepunkt. OFFSET -formelen starter der og strekker seg til å dekke alle de fylte cellene i kolonnen.
Den samme logikken fungerer også i ChartMonth -navngitte områdeformelen.
Trinn 2 - Lag et diagram ved å bruke disse navngitte områdene
Alt du trenger å gjøre er å sette inn et diagram som vil bruke de navngitte områdene som datakilde.
Her er trinnene for å sette inn et diagram og bruke dynamiske diagramområder:
- Gå til kategorien Sett inn.
- Klikk på "Sett inn linje eller områdediagram" og sett inn "Linje med markører" -diagrammet. Dette vil sette inn diagrammet i regnearket.
- Når diagrammet er valgt, går du til kategorien Design.
- Klikk på Velg data.
- I dialogboksen "Velg datakilde" klikker du på Legg til -knappen i "Legend Entries (Series)".
- I feltet Serieverdi skriver du inn = Formula! ChartValues (merk at du må spesifisere regnearknavnet før det navngitte området for at dette skal fungere).
- Klikk OK.
- Klikk på Rediger -knappen i de 'Horisontale (kategori) aksemerkene'.
- I dialogboksen 'Axis Labels' skriver du = Formula! ChartMonths
- Klikk Ok.
Det er det! Nå bruker diagrammet et dynamisk område og oppdateres når du legger til/sletter datapunkter i diagrammet.
Noen viktige ting å vite når du bruker navngitte områder med diagrammer:
- Det bør ikke være noen tomme celler i diagramdataene. Hvis det er et tomt, navngitt område, vil ikke referere til det riktige datasettet (da det totale antallet ville føre til at det refererte til færre antall celler).
- Du må følge navnekonvensjonen når du bruker arknavnet i diagramkilden. For eksempel, hvis arknavnet er et enkelt ord, for eksempel Formel, kan du bruke = Formula! ChartValue. Men hvis det er mer enn ett ord, for eksempel Formel Chart, må du bruke = 'Formula Chart'! ChartValue.
