Inhalter
Ugeholl datt Dir an ech Daten aus der folgender Tabelle mat Autosverkaafswäerter vu verschiddene Länner am Joer 2021 musse visualiséieren (echt Daten vun hei geholl, iwwregens):
Well d'Zuel vun den Dateserien (Länner) grouss ass, versicht se all op eemol an eng Grafik ze kräischen entweder zu enger schrecklecher "Spaghetti-Chart" oder zu enger separater Charts fir all Serie ze bauen, wat ganz ëmständlech ass.
Eng elegant Léisung fir dëse Problem kann et sinn, en Diagramm nëmmen op d'Donnéeën vun der aktueller Zeil ze plotten, also d'Zeil wou déi aktiv Zell läit:
Dëst ëmzesetzen ass ganz einfach - Dir braucht nëmmen zwou Formelen an e klenge Makro an 3 Zeilen.
Schrëtt 1. Aktuell Linn Zuel
Déi éischt Saach, déi mir brauchen, ass e benannte Beräich, deen d'Zeilnummer op der Blat berechent, wou eis aktiv Zell elo läit. Ouverture op engem Tab Formulen - Numm Manager (Formelen - Numm Manager), klickt op de Knäppchen schafen (Erstellen) a gitt déi folgend Struktur do:
- éischte Numm - all gëeegent Numm fir eis Variabel (an eisem Fall ass dëst TekString)
- Beräich - Heino musst Dir déi aktuell Blat auswielen, sou datt déi erstallt Nimm lokal sinn
- Rei - hei benotze mir d'Funktioun ZELL (ZELL), déi eng Rëtsch vu verschiddene Parameteren fir eng bestëmmten Zell erausginn kann, dorënner d'Zeilnummer déi mir brauchen - d'Argument "Linn" ass verantwortlech fir dëst.
Schrëtt 2. Link op den Titel
Fir dat ausgewielt Land am Titel an der Legend vum Diagramm ze weisen, musse mir eng Referenz op d'Zelle mat sengem (Land) Numm aus der éischter Kolonn kréien. Fir dëst ze maachen, kreéiere mir eng aner lokal (dh Beräich = aktuell Blat, net Buch!) e benannte Beräich mat der folgender Formel:
Hei wählt d'INDEX-Funktioun aus engem bestëmmte Beräich (Kolonn A, wou eis Ënnerschrëftlänner leien) eng Zelle mat der Zeilnummer, déi mir virdru festgestallt hunn.
Schrëtt 3. Link op Donnéeën
Elo, op eng ähnlech Manéier, loosst eis e Link op eng Rei mat all de Verkafsdaten aus der aktueller Zeil kréien, wou déi aktiv Zell elo läit. Erstellt eng aner benannt Gamme mat der folgender Formel:
Hei ass dat drëtt Argument, dat null ass, verursaacht datt den INDEX net een eenzege Wäert zréckkënnt, mee déi ganz Rei als Resultat.
Schrëtt 4. Ersetzen Linken am Diagramm
Wielt elo den Dësch Header an déi éischt Zeil mat Daten (Gamme) a baut en Diagramm baséiert op se benotzt Insert - Charts (Insert - Charts). Wann Dir eng Zeil mat Daten am Diagramm wielt, da gëtt d'Funktioun an der Formelbar ugewisen rudderen (SERIE) ass eng speziell Funktioun déi Excel automatesch benotzt wann Dir all Diagramm erstellt fir op déi ursprénglech Donnéeën an Etiketten ze referenzéieren:
Loosst eis virsiichteg déi éischt (Ënnerschrëft) an drëtt (Daten) Argumenter an dëser Funktioun ersetzen mat den Nimm vun eise Beräicher vu Schrëtt 2 an 3:
D'Diagramm fänkt Verkafsdaten vun der aktueller Zeil un.
Schrëtt 5. Recalculation Macro
De finalen Touch bleift. Microsoft Excel recalculéiert Formelen nëmmen wann d'Donnéeën op der Blat ännert oder wann e Schlëssel gedréckt gëtt F9, a mir wëllen datt d'Neiberechnung geschitt wann d'Auswiel ännert, also wann déi aktiv Zell iwwer d'Blat geréckelt gëtt. Fir dëst ze maachen, musse mir en einfachen Makro op eis Aarbechtsbuch addéieren.
Riets-klickt op d'Dateblatt Reiter a wielt de Kommando Source (Source Code). An der Fënster déi opmaacht, gitt de Code vum Makro-Handler fir de Selektiounsännerungsevenement:
Wéi Dir Iech ganz einfach kënnt virstellen, alles wat et mécht ass eng Blat-Recalculatioun ausléisen wann ëmmer d'Positioun vun der aktiver Zell ännert.
Schrëtt 6. Ervirhiewen déi aktuell Linn
Fir Kloerheet kënnt Dir och eng bedingt Formatéierungsregel addéieren fir d'Land ze markéieren dat am Moment op der Grafik ugewise gëtt. Fir dëst ze maachen, wielt den Dësch a wielt Home - Conditional Formatting - Erstellt Regel - Benotzt Formel fir Zellen ze formatéieren (Home - Conditional Formatting - Nei Regel - Benotzt eng Formel fir ze bestëmmen wéi eng Zellen ze formatéieren):
Hei kontrolléiert d'Formel fir all Zell an der Tabell datt seng Zeilnummer mat der Nummer an der TekRow Variabel entsprécht, a wann et e Match gëtt, da gëtt d'Fëllung mat der gewielter Faarf ausgeléist.
Dat ass et - einfach a schéin, richteg?
Notes
- Op groussen Dëscher kann all dës Schéinheet verlangsamen - bedingt Formatéierung ass eng Ressourceintensiv Saach, a Recalculation fir all Auswiel kann och schwéier sinn.
- Fir ze verhënneren datt Daten am Diagramm verschwannen wann eng Zell zoufälleg uewen oder ënner der Tabell ausgewielt gëtt, kënnt Dir en zousätzleche Scheck zum TekRow Numm addéieren mat nestéierten IF Funktiounen vun der Form:
=IF(CELL(“row”)<4,IF(CELL(“row”)>4,CELL(“row”)))
- Highlight spezifizéierte Kolonnen an engem Diagramm
- Wéi erstellen ech en interaktiven Diagramm an Excel
- Koordinate Auswiel