Posuvník v Excelu je skvělým ovládacím prvkem, který vám dovolí vnést interaktivní prvek do vaší prezentace. V dnešním videu si ukážeme, jak vytvořit v Excelu rolovací tabulku. Tedy tabulku, která ukazuje určitý počet řádků a je ovládána posuvníkem, takže v tabulce můžete rolovat. Rolovací tabulka se vám bude hodit například v dynamických přehledech neboli dashboardech, kde se většinou na malém kusu místa snažíme zobrazit co nejvíce informací.
Excelový soubor ke stažení:
Posuvník a rolovací tabulka v Excelu
Pro tvorbu rolovací tabulky použijeme Ovládací prvek, kterému se říká posuvník. Ovládací prvky najdeme na kartě Vývojář. Pokud tuto kartu v Excelu nemáte aktivovanou, tak na horní lištu v excelovém sešitě klikněte pravým tlačítkem myši a vyberte možnost Přizpůsobit pás karet. Zde v rozbalovacím seznamu vyberte Hlavní karty a dole uvidíte možnost Vývojář. Když na kartu Vývojář kliknete tak se zaktivní tlačítko Přidat. Vyberte přidat a Vývojář se vám přidá do nabídky v Excelu. Teď již stačí jen výběr potvrdit.
Na záložce Vývojář najdete Ovládací prvky a můžete zde vybrat ovládací prvek Posuvník. Kurzor myši se vám z šipky změní na kříž, což znamená, že posuvník do Excelu vložíte tak, že ho nakreslíte, jako kdybyste vkládali do Excelu tvary. Začneme tedy tím, že jednoduše podržíme levé tlačítko myši a nakreslíme obdélník. Pustíme tlačítko myši a vložil se posuvník. Tvar posuvníku můžeme kdykoliv upravit, takže výsledný tvar upravíme až podle tvaru výsledné tabulky.
Posuvník a jeho nastavení
Aby posuvník fungoval tak, jak chceme, musíme ho správně nastavit. Klikneme na něj tedy pravým tlačítkem myši a vybereme možnost Formát ovládacího prvku.
Zde nás zajímá zejména záložka Ovládací prvek. Právě zde se nastavuje to, jak bude posuvník fungovat.
Nejnižší hodnota posuvníku je hodnota, od které v rolovací tabulce začínáme. Pro mě osobně je to vždy jednička, jelikož chci, aby tabulka začínala vždy prvním řádkem.
Přírůstková změna je počet, o který se bude posuvník v tabulce posouvat. Pokud chceme, aby se hodnoty v tabulce posouvaly po jednom řádku, tak nastavíme jedničku, pokud po dvou řádcích, tak nastavíme dvojku atd.
Posuvník lze v podstatě ovládat dvěma způsoby. Můžeme ho ovládat pomocí šipek posuvníku, v tomto případě se budeme v rolovací tabulce posouvat o počet řádků, který je uvedený na řádku přírůstková změna. Pokud tedy nastavíme v přírůstkové změně jedničku, tak se klikáním na šipky bude posuvník posouvat vždy o jeden řádek, a to buď nahoru nebo dolů. Druhou možností je, že budete posuvník ovládat klikáním na posuvník kdekoliv po jeho délce a posuvník se pak bude posouvat o počet řádků, které jsou uvedené v řádku změna o stránku.
Změna o stránku je počet, o který se budeme v rolovací tabulce posouvat, pokud budeme ovládat posuvník klikáním mimo šipky. Jedná se tedy o rychlejší posouvání.
Nejvyšší hodnotu v posuvníku nastavujeme tak, že od celkového počtu řádků v tabulce odečteme hodnotu, kterou jsme nastavili v poli změna o stránku, a k této hodnotě ještě přičteme hodnotu přírůstkové změny. V našem případě máme v tabulce 500 řádků, takže od hodnoty 500 odečteme 10 (změna o stránku) a přičteme 1 (přírůstková změna), tedy 500 mínus 9. Do pole nejvyšší hodnota napíšeme tedy číslo 491.
Posuvník a propojení s buňkou
Nejdůležitějším polem v nastavení posuvníku je ovšem propojení s buňkou. V listu Excelu musíme vybrat buňku, kterou propojíme s posuvníkem. V našem případě vybereme například buňku G1. Klikneme do pole Propojení s buňkou a následně klikneme do buňky G1. Potvrdíme.
V poli G1 se objevila hodnota 1. Když klikneme na dolní šipku v posuvníku, tak se hodnota v poli G1 změní na 2, jelikož jsme se s posuvníkem posunuli o jedno pole dolů. Pokud se posuneme znovu, tak se v poli G1 objeví trojka a tak dále. Posuvník se posouvá přesně tak, jak jsme nastavili v poli Přírůstková změna. Když klikneme mimo šipky v posuvníku, tak se číslo v poli G1 změnilo na 13. Přesně tak jak jsme nastavili v poli Změna o stránku. Posuvník byl v poli tři a jelikož jsme klikli mimo šipky, tak se posuvník přesunul o deset polí dolů, tak jak jsme nastavili. Pokud to uděláme znovu, posuvník se posune na 23. Teď když víme, jak posuvník funguje, zkusíme vytvořit jednoduchou tabulku.
Rolovací tabulka pomocí funkce INDEX
Posuvník ovládá rolovací tabulku za pomocí buňky, kterou jsme propojili s buňkou, tedy buňkou G1. Z toho plyne, že rolovací tabulku musíme vytvořit pomocí nějaké funkce.
V prvním případě můžeme tabulku vytvořit pomocí funkce INDEX. Klikneme do první buňky v nové tabulce a napíšeme funkci INDEX. Jako pole označíme první sloupec hodnot, ale to není vše, musíme buňky správně zafixovat. Tím, jak budeme hýbat posuvníkem, tak potřebujeme, aby první buňka v tomto rozpětí byla variabilní. Pokud posuneme posuvník dolů, potřebujeme, aby se tato první buňky posunula dle posuvníku. Nebudeme ji tedy vůbec fixovat. Kde naopak potřebujeme fixovat je druhá buňka. Rozměr tabulky máme daný, takže s tím jak budeme posuvník posouvat, tak nechceme, aby se tato buňka posunula mimo tabulku, takže tuto druhou buňku zafixujeme klávesou F4 pro řádek.
Parametr pole je hotový. Stačí ještě vyplnit parametr řádek. A co to bude? Bude to buňka G1, kde máme buňku, která ovládá posuvník. Tuto buňku zafixujeme klávesou F4 jak pro sloupce, tak řádky. Hotovo. Potvrdíme. Vrátila se hodnota na prvním řádku, jelikož posuvník máme nastavený na začátku. Pokud s ním teď posuneme, tak se hodnota změní na hodnotu, kterou máme ve zdrojové tabulce na druhém řádku. Když máme vyzkoušeno, že to máme dobře, tak buňku stáhneme dolů pro dalších 9 řádků. A jelikož máme buňky správně zafixované, tak zbytek hodnot doplníme prostým protažením.
Rolovací tabulka pomocí funkce POSUN
Druhou možností, jak rolovací tabulku vytvořit je za pomocí funkce POSUN. Zkusíme vytvořit druhou rolovací tabulku.
Klikneme do první buňky v rolovací tabulce a napíšeme funkci POSUN. Jako první parametr funkce POSUN je odkaz, neboli styčný bod, odkud se funkce POSUN bude posouvat. V tomto případě se bude posouvat od záhlaví ve zdrojové tabulce, a jelikož máme v plánu zase tabulku vyplnit přetahováním hodnot, tak buňku zafixujeme pro řádky. Další parametr je počet řádků, právě do tohoto parametru vyplníme buňku H1, kam jsme nastavili ovládání posuvníku. Zafixujeme pro sloupce i řádky. Následuje parametr sloupce, zde vyplníme 0, jelikož chceme zůstat ve stejném sloupci. Výška je počet řádků, které chceme od zvoleného řádku zobrazit, chceme v tabulce vždy jen deset řádků, takže doplníme číslo deset a poslední parametr je nepovinný. Potvrdíme a vyplníme celou tabulku. Zkontrolujeme posuvník, a vše funguje tak, jak má.
2 komentáře
Při tvorbě rolovací tabulky s posuvníkem, s použitím funkce POSUN, jsem musel vzorec vložit pomocí CTRL + Shift + Enter. Jinak v buňce byla #HODNOTA! Ve vzorci se odkázat na buňku A1 zdrojové tabulky bez fixace. Jinak byly ve všech deseti řádcích rolovací tabulky hodnoty z prvního řádku zdrojové tabulky.
Používám Excel 2010.
Máte naprostou pravdu, já to zkoušela na Office 365 a 2019 a fungovalo to, ale už mě upozornil další uživatel 2010, že to musí udělat přesně jako vy. Budu muset moje řešení testovat i na Office 2010 🙂 Děkuji za upozornění, napíšu to do komentáře k videu na Youtube, aby na to byli upozornění i ostatní.