Graf podľa vybratej bunky

Predpokladajme, že vy a ja potrebujeme vizualizovať údaje z nasledujúcej tabuľky s hodnotami predaja áut v rôznych krajinách v roku 2021 (mimochodom skutočné údaje prevzaté odtiaľto):

Keďže počet radov údajov (krajín) je veľký, snaha vtesnať ich všetky do jedného grafu naraz povedie buď k hroznému „špagetovému grafu“, alebo k vytvoreniu samostatných grafov pre každý rad, čo je veľmi ťažkopádne.

Elegantným riešením tohto problému môže byť vykreslenie grafu len na dátach z aktuálneho riadku, teda riadku, kde sa nachádza aktívna bunka:

Implementácia je veľmi jednoduchá – potrebujete len dva vzorce a jedno malé makro v 3 riadkoch.

Krok 1. Číslo aktuálneho riadku

Prvá vec, ktorú potrebujeme, je pomenovaný rozsah, ktorý vypočíta číslo riadku na hárku, kde sa teraz nachádza naša aktívna bunka. Otváranie na karte Vzorce – Správca mien (Vzorce — Menný manažér), kliknite na tlačidlo vytvoriť (Vytvoriť) a zadajte tam nasledujúcu štruktúru:

Krok 2. Odkaz na názov

Na zobrazenie vybranej krajiny v názve a legende grafu potrebujeme z prvého stĺpca získať odkaz na bunku s jej názvom (krajiny). Aby sme to dosiahli, vytvoríme ďalšie lokálne (tj Plocha = aktuálny hárok, nie Kniha!) pomenovaný rozsah s nasledujúcim vzorcom:

Tu funkcia INDEX vyberie z daného rozsahu (stĺpec A, kde ležia naše podpisujúce krajiny) bunku s číslom riadku, ktoré sme predtým určili.

Krok 3. Prepojenie s údajmi

Teraz podobným spôsobom získajme odkaz na rozsah so všetkými údajmi o predaji z aktuálneho riadku, kde sa teraz nachádza aktívna bunka. Vytvorte ďalší pomenovaný rozsah pomocou nasledujúceho vzorca:

Tu tretí argument, ktorý je nula, spôsobí, že INDEX nevráti jedinú hodnotu, ale ako výsledok celý riadok.

Krok 4. Nahradenie odkazov v tabuľke

Teraz vyberte hlavičku tabuľky a prvý riadok s údajmi (rozsah) a pomocou nich vytvorte graf Vložiť – grafy (Vložiť — grafy). Ak v grafe vyberiete riadok s údajmi, funkcia sa zobrazí na riadku vzorcov ROW (SÉRIA) je špeciálna funkcia, ktorú Excel automaticky používa pri vytváraní akéhokoľvek grafu na odkazovanie na pôvodné údaje a označenia:

Opatrne nahraďme prvý (podpis) a tretí (údaje) argument v tejto funkcii názvami našich rozsahov z krokov 2 a 3:

V grafe sa začnú zobrazovať údaje o predaji z aktuálneho riadku.

Krok 5. Makro prepočtu

Posledný dotyk zostáva. Program Microsoft Excel prepočítava vzorce iba vtedy, keď sa zmenia údaje na hárku alebo keď sa stlačí kláves F9, a chceme, aby prepočet nastal pri zmene výberu, teda pri presune aktívnej bunky po hárku. Aby sme to dosiahli, musíme do nášho zošita pridať jednoduché makro.

Kliknite pravým tlačidlom myši na kartu údajového listu a vyberte príkaz zdroj (Zdrojový kód). V okne, ktoré sa otvorí, zadajte kód obslužného programu makra pre udalosť zmeny výberu:

Ako si viete ľahko predstaviť, všetko, čo robí, je spustenie prepočtu listu vždy, keď sa zmení pozícia aktívnej bunky.

Krok 6. Zvýraznenie aktuálneho riadku

Kvôli prehľadnosti môžete pridať aj pravidlo podmieneného formátovania na zvýraznenie krajiny, ktorá je aktuálne zobrazená v grafe. Ak to chcete urobiť, vyberte tabuľku a vyberte Domov — Podmienené formátovanie — Vytvoriť pravidlo — Použite vzorec na určenie buniek na formátovanie (Domov — Podmienené formátovanie — Nové pravidlo — Použite vzorec na určenie, ktoré bunky sa majú formátovať):

Tu vzorec skontroluje pre každú bunku v tabuľke, či sa jej číslo riadka zhoduje s číslom uloženým v premennej TekRow, a ak existuje zhoda, spustí sa výplň zvolenou farbou.

To je všetko - jednoduché a krásne, však?

Poznámky

• Na veľkých stoloch sa môže všetka táto krása spomaliť – podmienené formátovanie je náročná záležitosť a prepočítavanie pre každý výber môže byť tiež náročné.
• Ak chcete zabrániť zmiznutiu údajov z grafu, keď je bunka náhodne vybratá nad alebo pod tabuľkou, môžete pridať dodatočnú kontrolu k názvu TekRow pomocou vnorených funkcií IF formulára:

  =IF(CELL(“riadok”)<4,IF(CELL("riadok")>4,BUNKA(“riadok”)))
  

• Zvýraznenie určených stĺpcov v grafe
• Ako vytvoriť interaktívny graf v Exceli
• Výber súradníc