Graf podle vybrané buňky

Předpokládejme, že vy a já potřebujeme vizualizovat data z následující tabulky s hodnotami prodeje aut v různých zemích v roce 2021 (mimochodem skutečná data převzatá odtud):

Vzhledem k tomu, že počet datových řad (zemí) je velký, pokusit se je nacpat všechny do jednoho grafu najednou povede buď k hroznému „špagetovému grafu“, nebo k vytvoření samostatných grafů pro každou řadu, což je velmi těžkopádné.

Elegantním řešením tohoto problému může být vykreslení grafu pouze na datech z aktuálního řádku, tedy řádku, kde se nachází aktivní buňka:

Implementace je velmi snadná – potřebujete pouze dva vzorce a jedno malé makro na 3 řádcích.

Krok 1. Číslo aktuálního řádku

První věc, kterou potřebujeme, je pojmenovaný rozsah, který vypočítá číslo řádku na listu, kde se nyní nachází naše aktivní buňka. Otevření na kartě Vzorce – Správce názvů (Vzorce — Správce jmen), klikněte na tlačítko Vytvořit (Vytvořit) a zadejte tam následující strukturu:

Krok 2. Odkaz na titulek

Pro zobrazení vybrané země v názvu a legendě grafu potřebujeme z prvního sloupce získat odkaz na buňku s jejím (země) názvem. K tomu vytvoříme další místní (tj Oblast = aktuální list, ne Kniha!) pojmenovaný rozsah s následujícím vzorcem:

Zde funkce INDEX vybere z daného rozsahu (sloupec A, kde leží naše podepisující země) buňku s číslem řádku, které jsme dříve určili.

Krok 3. Propojení s daty

Nyní podobným způsobem získáme odkaz na rozsah se všemi údaji o prodeji z aktuálního řádku, kde se nyní nachází aktivní buňka. Vytvořte další pojmenovaný rozsah pomocí následujícího vzorce:

Zde třetí argument, který je nula, způsobí, že INDEX nevrátí jedinou hodnotu, ale jako výsledek celý řádek.

Krok 4. Nahrazení odkazů v grafu

Nyní vyberte záhlaví tabulky a první řádek s daty (rozsah) a pomocí nich vytvořte graf Vložit – Grafy (Vložit — Grafy). Pokud v grafu vyberete řádek s daty, funkce se zobrazí v řádku vzorců ŘÁDEK (SÉRIE) je speciální funkce, kterou Excel automaticky používá při vytváření jakéhokoli grafu k odkazování na původní data a štítky:

Pečlivě nahraďme první (podpis) a třetí (data) argument v této funkci názvy našich rozsahů z kroků 2 a 3:

V grafu se začnou zobrazovat údaje o prodeji z aktuálního řádku.

Krok 5. Makro přepočtu

Zbývá poslední dotek. Microsoft Excel přepočítá vzorce pouze tehdy, když se změní data na listu nebo když je stisknuta klávesa F9, a chceme, aby k přepočtu došlo při změně výběru, tj. při přesunutí aktivní buňky po listu. K tomu potřebujeme přidat do sešitu jednoduché makro.

Klepněte pravým tlačítkem myši na kartu datového listu a vyberte příkaz Zdroj (Zdrojový kód). V okně, které se otevře, zadejte kód obslužné rutiny maker pro událost změny výběru:

Jak si můžete snadno představit, vše, co dělá, je spouštět přepočet listu, kdykoli se změní pozice aktivní buňky.

Krok 6. Zvýraznění aktuálního řádku

Pro přehlednost můžete také přidat pravidlo podmíněného formátování pro zvýraznění země, která je aktuálně zobrazena v grafu. Chcete-li to provést, vyberte tabulku a vyberte Domů — Podmíněné formátování — Vytvořit pravidlo — Použijte vzorec k určení buněk k formátování (Domů — Podmíněné formátování — Nové pravidlo — Pomocí vzorce určete, které buňky se mají formátovat):

Zde vzorec pro každou buňku v tabulce zkontroluje, zda se její číslo řádku shoduje s číslem uloženým v proměnné TekRow, a pokud existuje shoda, spustí se výplň vybranou barvou.

To je ono – jednoduché a krásné, že?

Poznámky

• Na velkých stolech se veškerá tato krása může zpomalit – podmíněné formátování je náročná záležitost a přepočítávání pro každý výběr může být také těžké.
• Chcete-li zabránit zmizení dat v grafu, když je náhodně vybrána buňka nad nebo pod tabulkou, můžete přidat další kontrolu názvu TekRow pomocí vnořených funkcí IF formuláře:

  =IF(CELL(“řádek”)<4,IF(CELL("řádek")>4,CELL(“řádek”)))
  

• Zvýraznění určených sloupců v grafu
• Jak vytvořit interaktivní graf v Excelu
• Výběr souřadnic