Diagram etter valgt celle

Anta at du og jeg trenger å visualisere data fra følgende tabell med bilsalgsverdier for forskjellige land i 2021 (virkelige data hentet herfra, forresten):

Siden antallet dataserier (land) er stort, vil det å prøve å stappe alle sammen i én graf på en gang enten føre til et forferdelig "spaghettidiagram" eller til å bygge separate diagrammer for hver serie, noe som er veldig tungvint.

En elegant løsning på dette problemet kan være å plotte et diagram kun på dataene fra gjeldende rad, dvs. raden der den aktive cellen er plassert:

Å implementere dette er veldig enkelt - du trenger bare to formler og en liten makro på 3 linjer.

Trinn 1. Nåværende linjenummer

Det første vi trenger er et navngitt område som beregner radnummeret på arket der vår aktive celle nå er plassert. Åpning på en fane Formler – Navnebehandler (Formler – navneansvarlig), klikk på knappen Opprett (Skape) og skriv inn følgende struktur der:

Trinn 2. Link til tittelen

For å vise det valgte landet i tittelen og forklaringen til diagrammet, må vi hente en referanse til cellen med dets (lands)navn fra den første kolonnen. For å gjøre dette oppretter vi en annen lokal (dvs Område = gjeldende ark, ikke bok!) et navngitt område med følgende formel:

Her velger INDEX-funksjonen fra et gitt område (kolonne A, hvor signeringslandene våre ligger) en celle med radnummeret som vi tidligere har bestemt.

Trinn 3. Koble til data

La oss nå, på lignende måte, få en kobling til et område med alle salgsdata fra gjeldende rad, der den aktive cellen nå er plassert. Opprett et annet navngitt område med følgende formel:

Her fører det tredje argumentet, som er null, til at INDEX returnerer ikke en enkelt verdi, men hele raden som et resultat.

Trinn 4. Bytte ut lenker i diagrammet

Velg nå tabelloverskriften og den første raden med data (område) og bygg et diagram basert på dem ved hjelp av Sett inn – Diagrammer (Sett inn – diagrammer). Hvis du velger en rad med data i diagrammet, vil funksjonen vises i formellinjen RAD (SERIE) er en spesiell funksjon som Excel automatisk bruker når du lager et hvilket som helst diagram for å referere til de originale dataene og etikettene:

La oss nøye erstatte de første (signatur) og tredje (data) argumentene i denne funksjonen med navnene på områdene våre fra trinn 2 og 3:

Diagrammet vil begynne å vise salgsdata fra gjeldende rad.

Trinn 5. Omberegningsmakro

Siste touch gjenstår. Microsoft Excel rekalkulerer formler bare når dataene på arket endres eller når en tast trykkes F9, og vi ønsker at omberegningen skal skje når utvalget endres, dvs. når den aktive cellen flyttes over arket. For å gjøre dette må vi legge til en enkel makro i arbeidsboken vår.

Høyreklikk på dataarkfanen og velg kommandoen kilde (Kildekode). I vinduet som åpnes, skriv inn koden til makrobehandleren for valgendringshendelsen:

Som du lett kan forestille deg, er alt det gjør å utløse en arkberegning når posisjonen til den aktive cellen endres.

Trinn 6. Utheving av gjeldende linje

For klarhetens skyld kan du også legge til en betinget formateringsregel for å markere landet som for øyeblikket vises på diagrammet. For å gjøre dette, velg tabellen og velg Hjem — Betinget formatering — Lag regel — Bruk formel for å bestemme celler som skal formateres (Hjem — Betinget formatering — Ny regel — Bruk en formel for å bestemme hvilke celler som skal formateres):

Her sjekker formelen for hver celle i tabellen at radnummeret samsvarer med tallet som er lagret i TekRow-variabelen, og hvis det er samsvar, utløses fyllingen med den valgte fargen.

Det er det – enkelt og vakkert, ikke sant?

Merknader

• På store bord kan all denne skjønnheten bremses – betinget formatering er en ressurskrevende ting, og omberegning for hvert valg kan også være tungt.
• For å forhindre at data forsvinner på diagrammet når en celle ved et uhell velges over eller under tabellen, kan du legge til en ekstra hake til TekRow-navnet ved å bruke nestede IF-funksjoner av skjemaet:

  =HVIS(CELLE(“rad”)<4,HVIS(CELL(“rad”)>4,CELL(“rad”)))
  

• Utheving av spesifiserte kolonner i et diagram
• Hvordan lage et interaktivt diagram i Excel
• Koordinatvalg