Innehåll
Anta att du och jag behöver visualisera data från följande tabell med bilförsäljningsvärden för olika länder 2021 (verkliga data hämtade härifrån förresten):
Eftersom antalet dataserier (länder) är stort, kommer att försöka klämma in alla i en graf samtidigt antingen leda till ett fruktansvärt "spaghettidiagram" eller att bygga separata diagram för varje serie, vilket är mycket besvärligt.
En elegant lösning på detta problem kan vara att rita ett diagram endast på data från den aktuella raden, dvs raden där den aktiva cellen finns:
Att implementera detta är väldigt enkelt – du behöver bara två formler och ett litet makro på 3 rader.
Steg 1. Aktuellt radnummer
Det första vi behöver är ett namngivet intervall som beräknar radnumret på arket där vår aktiva cell nu finns. Öppning på en flik Formler – Namnhanterare (Formler — Namnansvarig), klicka på knappen Skapa (Skapa) och skriv in följande struktur där:
- Förnamn – något lämpligt namn för vår variabel (i vårt fall är detta TekString)
- Area – hädanefter måste du välja det aktuella arket så att namnen som skapas är lokala
- Mätområde – här använder vi funktionen CELL (cell), som kan utfärda ett gäng olika parametrar för en given cell, inklusive radnumret vi behöver – "line"-argumentet är ansvarigt för detta.
Steg 2. Länk till titeln
För att visa det valda landet i diagrammets titel och förklaring måste vi få en referens till cellen med dess (lands)namn från den första kolumnen. För att göra detta skapar vi en annan lokal (dvs Area = aktuellt blad, inte bok!) ett namngivet intervall med följande formel:
Här väljer INDEX-funktionen från ett givet intervall (kolumn A, där våra undertecknande länder ligger) en cell med det radnummer som vi tidigare bestämt.
Steg 3. Länk till data
Låt oss nu, på liknande sätt, få en länk till ett intervall med all försäljningsdata från den aktuella raden, där den aktiva cellen nu finns. Skapa ett annat namngivet intervall med följande formel:
Här gör det tredje argumentet, som är noll, att INDEX returnerar inte ett enda värde, utan hela raden som ett resultat.
Steg 4. Ersätter länkar i diagrammet
Välj nu tabellhuvudet och den första raden med data (intervall) och bygg ett diagram baserat på dem med hjälp av Infoga – Diagram (Infoga — Diagram). Om du väljer en rad med data i diagrammet kommer funktionen att visas i formelfältet RAD (SERIER) är en speciell funktion som Excel automatiskt använder när man skapar ett diagram för att referera till originaldata och etiketter:
Låt oss noggrant ersätta de första (signatur) och tredje (data) argumenten i den här funktionen med namnen på våra intervall från steg 2 och 3:
Diagrammet börjar visa försäljningsdata från den aktuella raden.
Steg 5. Omräkningsmakro
Den sista touchen återstår. Microsoft Excel räknar om formler endast när data på arket ändras eller när en tangent trycks ned F9, och vi vill att omräkningen ska ske när urvalet ändras, dvs när den aktiva cellen flyttas över arket. För att göra detta måste vi lägga till ett enkelt makro i vår arbetsbok.
Högerklicka på databladsfliken och välj kommandot Källa (Källkod). I fönstret som öppnas anger du koden för makrohanteraren för ändringshändelsen för urval:
Som du lätt kan föreställa dig, är allt det gör att utlösa en omräkning av ark närhelst positionen för den aktiva cellen ändras.
Steg 6. Markera den aktuella linjen
För tydlighetens skull kan du också lägga till en villkorlig formateringsregel för att markera landet som för närvarande visas i diagrammet. För att göra detta, välj tabellen och välj Hem — Villkorlig formatering — Skapa regel — Använd formel för att bestämma celler som ska formateras (Hem — Villkorlig formatering — Ny regel — Använd en formel för att bestämma vilka celler som ska formateras):
Här kontrollerar formeln för varje cell i tabellen att dess radnummer matchar numret som är lagrat i TekRow-variabeln, och om det finns en matchning utlöses fyllningen med den valda färgen.
Det är allt – enkelt och vackert, eller hur?
Anmärkningar
- På stora bord kan all denna skönhet sakta ner – villkorlig formatering är en resurskrävande sak, och omräkning för varje val kan också vara tung.
- För att förhindra att data försvinner i diagrammet när en cell av misstag markeras ovanför eller under tabellen, kan du lägga till en extra kontroll i TekRow-namnet med hjälp av kapslade IF-funktioner i formen:
=OM(CELL(“rad”)<4,OM(CELL("rad")>4,CELL(“rad”)))
- Markera angivna kolumner i ett diagram
- Hur man skapar ett interaktivt diagram i Excel
- Koordinatval