Indhold
Antag, at du og jeg skal visualisere data fra følgende tabel med bilsalgsværdier for forskellige lande i 2021 (rigtige data taget herfra i øvrigt):
Da antallet af dataserier (lande) er stort, vil forsøg på at proppe dem alle sammen i én graf på én gang enten føre til et forfærdeligt "spaghettidiagram" eller til at bygge separate diagrammer for hver serie, hvilket er meget besværligt.
En elegant løsning på dette problem kan være kun at plotte et diagram på dataene fra den aktuelle række, dvs. den række, hvor den aktive celle er placeret:
Det er meget nemt at implementere dette – du behøver kun to formler og en lille makro på 3 linjer.
Trin 1. Aktuelt linjenummer
Det første, vi skal bruge, er et navngivet område, der beregner rækkenummeret på arket, hvor vores aktive celle nu er placeret. Åbning på en fane Formler – Navneadministrator (Formler — Navneadministrator), klik på knappen Opret (Skab) og indtast følgende struktur der:
- Fornavn – ethvert passende navn til vores variabel (i vores tilfælde er dette TekString)
- Miljø – herefter skal du vælge det aktuelle ark, så de oprettede navne er lokale
- Range – her bruger vi funktionen CELLE (CELLE), som kan udstede en masse forskellige parametre for en given celle, inklusive det linjenummer, vi har brug for - "line"-argumentet er ansvarlig for dette.
Trin 2. Link til titlen
For at vise det valgte land i titlen og forklaringen på diagrammet, skal vi hente en reference til cellen med dens (lands)navn fra den første kolonne. For at gøre dette opretter vi en anden lokal (dvs Miljø = aktuelt ark, ikke bog!) et navngivet område med følgende formel:
Her vælger INDEX-funktionen fra et givent område (kolonne A, hvor vores signaturlande ligger) en celle med det rækkenummer, som vi tidligere har bestemt.
Trin 3. Link til data
Lad os nu på lignende måde få et link til et område med alle salgsdata fra den aktuelle række, hvor den aktive celle nu er placeret. Opret et andet navngivet område med følgende formel:
Her får det tredje argument, som er nul, INDEX til at returnere ikke en enkelt værdi, men hele rækken som et resultat.
Trin 4. Erstatning af links i diagrammet
Vælg nu tabeloverskriften og den første række med data (interval) og opbyg et diagram baseret på dem vha Indsæt – Diagrammer (Indsæt — diagrammer). Hvis du vælger en række med data i diagrammet, vil funktionen blive vist i formellinjen RÆKKE (SERIE) er en speciel funktion, som Excel automatisk bruger, når du opretter et hvilket som helst diagram for at referere til de originale data og etiketter:
Lad os omhyggeligt erstatte det første (signatur) og tredje (data) argument i denne funktion med navnene på vores områder fra trin 2 og 3:
Diagrammet begynder at vise salgsdata fra den aktuelle række.
Trin 5. Genberegningsmakro
Den sidste touch forbliver. Microsoft Excel genberegner kun formler, når dataene på arket ændres, eller når der trykkes på en tast F9, og vi ønsker, at genberegningen skal ske, når markeringen ændres, altså når den aktive celle flyttes hen over arket. For at gøre dette skal vi tilføje en simpel makro til vores projektmappe.
Højreklik på fanen dataark og vælg kommandoen Kilde (Kildekode). I vinduet, der åbnes, skal du indtaste koden for makrobehandleren for valgændringshændelsen:
Som du nemt kan forestille dig, er det eneste, det gør, at udløse en arkgenberegning, når positionen af den aktive celle ændres.
Trin 6. Fremhævelse af den aktuelle linje
For klarhedens skyld kan du også tilføje en betinget formateringsregel for at fremhæve det land, der i øjeblikket vises på diagrammet. For at gøre dette skal du vælge tabellen og vælge Hjem — Betinget formatering — Opret regel — Brug formel til at bestemme celler, der skal formateres (Hjem — Betinget formatering — Ny regel — Brug en formel til at bestemme, hvilke celler der skal formateres):
Her kontrollerer formlen for hver celle i tabellen, at dens rækkenummer matcher tallet, der er gemt i TekRow-variablen, og hvis der er et match, udløses fyldningen med den valgte farve.
Det er det – enkelt og smukt, ikke?
Noter
- På store borde kan al denne skønhed blive langsommere – betinget formatering er en ressourcekrævende ting, og genberegning for hvert valg kan også være tungt.
- For at forhindre data i at forsvinde på diagrammet, når en celle ved et uheld vælges over eller under tabellen, kan du tilføje en ekstra markering til TekRow-navnet ved hjælp af indlejrede IF-funktioner i formen:
=HVIS(CELLE(“række”)<4,HVIS(CELLE(“række“)>4,CELLE(“række”)))
- Fremhævelse af specificerede kolonner i et diagram
- Sådan opretter du et interaktivt diagram i Excel
- Koordinatvalg