Grafiek op geselecteerde cel

Stel dat u en ik gegevens uit de volgende tabel moeten visualiseren met autoverkoopwaarden per verschillende landen in 2021 (echte gegevens hier trouwens vandaan):

Aangezien het aantal gegevensreeksen (landen) groot is, zal proberen om ze allemaal in één grafiek tegelijk te proppen ofwel leiden tot een vreselijke "spaghettigrafiek" of tot het maken van afzonderlijke grafieken voor elke reeks, wat erg omslachtig is.

Een elegante oplossing voor dit probleem kan zijn om een ​​grafiek alleen te plotten op de gegevens van de huidige rij, dat wil zeggen de rij waar de actieve cel zich bevindt:

Het implementeren hiervan is heel eenvoudig - je hebt slechts twee formules en een kleine macro in 3 regels nodig.

Stap 1. Huidig ​​regelnummer

Het eerste dat we nodig hebben, is een benoemd bereik dat het rijnummer berekent op het blad waar onze actieve cel zich nu bevindt. Openen op een tabblad Formules – Naammanager (Formules — Naammanager), klik op de knop creëren (Creëren) en voer daar de volgende structuur in:

Stap 2. Link naar de titel

Om het geselecteerde land in de titel en legenda van de grafiek weer te geven, moeten we een verwijzing naar de cel met zijn (land)naam uit de eerste kolom halen. Om dit te doen, maken we een andere lokale (bijv De Omgeving = huidig ​​blad, niet Boek!) een benoemd bereik met de volgende formule:

Hier selecteert de INDEX-functie uit een bepaald bereik (kolom A, waar onze ondertekenende landen liggen) een cel met het rijnummer dat we eerder hebben bepaald.

Stap 3. Link naar gegevens

Laten we nu op een vergelijkbare manier een link krijgen naar een bereik met alle verkoopgegevens van de huidige rij, waar de actieve cel zich nu bevindt. Maak een ander benoemd bereik met de volgende formule:

Hier zorgt het derde argument, dat nul is, ervoor dat INDEX niet een enkele waarde retourneert, maar de hele rij als resultaat.

Stap 4. Vervangen van links in de grafiek

Selecteer nu de tabelkop en de eerste rij met gegevens (bereik) en bouw een diagram op basis daarvan met behulp van Invoegen – Grafieken (Invoegen — Grafieken). Als u een rij met gegevens in de grafiek selecteert, wordt de functie weergegeven in de formulebalk RIJ (SERIE) is een speciale functie die Excel automatisch gebruikt bij het maken van een grafiek om te verwijzen naar de originele gegevens en labels:

Laten we voorzichtig de eerste (handtekening) en derde (data) argumenten in deze functie vervangen door de namen van onze reeksen uit stap 2 en 3:

De grafiek begint met het weergeven van verkoopgegevens van de huidige rij.

Stap 5. Herberekeningsmacro

De laatste hand blijft. Microsoft Excel herberekent formules alleen wanneer de gegevens op het blad veranderen of wanneer een toets wordt ingedrukt F9, en we willen dat de herberekening plaatsvindt wanneer de selectie verandert, dat wil zeggen wanneer de actieve cel over het werkblad wordt verplaatst. Om dit te doen, moeten we een eenvoudige macro aan onze werkmap toevoegen.

Klik met de rechtermuisknop op het tabblad Gegevensblad en selecteer de opdracht bron (Broncode). Voer in het geopende venster de code van de macro-handler in voor de selectiewijzigingsgebeurtenis:

Zoals u zich gemakkelijk kunt voorstellen, activeert het alleen een herberekening van het blad wanneer de positie van de actieve cel verandert.

Stap 6. De huidige regel markeren

Voor de duidelijkheid kunt u ook een voorwaardelijke opmaakregel toevoegen om het land te markeren dat momenteel in het diagram wordt weergegeven. Selecteer hiervoor de tabel en selecteer Home — Voorwaardelijke opmaak — Regel maken — Formule gebruiken om cellen te bepalen die moeten worden opgemaakt (Home — Voorwaardelijke opmaak — Nieuwe regel — Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt):

Hier controleert de formule voor elke cel in de tabel of het rijnummer overeenkomt met het nummer dat is opgeslagen in de TekRow-variabele, en als er een overeenkomst is, wordt de vulling met de geselecteerde kleur geactiveerd.

Dat is het - eenvoudig en mooi, toch?

Opmerkingen

• Op grote tafels kan al dit moois vertragen - voorwaardelijke opmaak is een arbeidsintensief iets, en herberekening voor elke selectie kan ook zwaar zijn.
• Om te voorkomen dat gegevens in het diagram verdwijnen wanneer per ongeluk een cel boven of onder de tabel wordt geselecteerd, kunt u een extra controle toevoegen aan de TekRow-naam met behulp van geneste IF-functies van het formulier:

  =ALS(CEL(“rij”)<4,ALS(CEL("rij")>4,CEL(“rij”)))
  

• Gespecificeerde kolommen in een diagram markeren
• Hoe maak je een interactieve grafiek in Excel
• Coördinaten selectie