Video
Formulace problému
Máme tabulku, se kterou musíme neustále pracovat (třídit, filtrovat, něco v ní počítat) a jejíž obsah se periodicky mění (přidávat, mazat, upravovat). Tedy alespoň pro příklad – zde je to takto:
Velikost – od několika desítek do několika set tisíc řádků – není důležitá. Úkolem je zjednodušit a usnadnit vám život všemi možnými způsoby tím, že tyto buňky proměníte v „chytrou“ tabulku.
Řešení
Vyberte libovolnou buňku v tabulce a na kartě Domů (Domov) rozbalte seznam Formátovat jako tabulku (Formátovat jako tabulku):
V rozevíracím seznamu stylů vyberte libovolnou možnost výplně podle našeho vkusu a barvy a v potvrzovacím okně pro vybraný rozsah klikněte na OK a dostaneme následující výstup:
Výsledkem je, že po takové transformaci rozsahu na „chytrý“ Tabulka (s velkým písmenem!) máme tyto radosti (až na pěkný design):
- Vytvořeno Tabulka dostane jméno Tabulka 1,2,3 atd., které lze na kartě změnit na adekvátnější Stavitel (Design). Tento název lze použít v libovolných vzorcích, rozevíracích seznamech a funkcích, jako je zdroj dat pro kontingenční tabulku nebo vyhledávací pole pro funkci SVYHLEDAT.
- Vytvořeno jednou Tabulka automaticky se přizpůsobí velikosti při přidávání nebo mazání dat do něj. Pokud k takovým přidáte Tabulka nové řádky – protáhne se níže, pokud přidáte nové sloupce – rozšíří se do šířky. V pravém dolním rohu Tabulky můžete vidět automaticky se pohybující hraniční značku a v případě potřeby upravit její polohu pomocí myši:
- V klobouku Tabulky automaticky Automatický filtr se zapne (lze vynutit deaktivaci na kartě Data (Datum)).
- Při přidávání nových řádků k nim automaticky všechny vzorce jsou zkopírovány.
- Při vytváření nového sloupce se vzorcem – bude automaticky zkopírován do celého sloupce – není třeba přetahovat vzorec s černým křížkem automatického doplňování.
- Při rolování Tabulky dolů záhlaví sloupců (A, B, C…) se změní na názvy polí, tj. již nemůžete opravit záhlaví rozsahu jako dříve (v Excelu 2010 je také automatický filtr):
- Povolením zaškrtávacího políčka Zobrazit celkový řádek (řádek celkem) Karta Stavitel (Design) na konci dostaneme řádek automatického součtu Tabulky s možností vybrat funkci (součet, průměr, počet atd.) pro každý sloupec:
- K datům v Tabulka lze řešit pomocí názvů jeho jednotlivých prvků. Chcete-li například sečíst všechna čísla ve sloupci DPH, můžete použít vzorec =SUM(Tabulka1[DPH]) místo toho = SUM (F2: F200) a nemyslet na velikost tabulky, počet řádků a správnost rozsahů výběru. Je také možné použít následující příkazy (za předpokladu, že tabulka má standardní název Tabulka 1):
- =Tabulka1[#Vše] – odkaz na celou tabulku včetně záhlaví sloupců, dat a řádku součtu
- =Tabulka1[#Data] – odkaz pouze na data (bez záhlaví)
- =Tabulka1[#Headers] – odkaz pouze na první řádek tabulky s nadpisy sloupců
- =Tabulka1[#Totals] – odkaz na celkový řádek (pokud je zahrnut)
- =Tabulka1[#Tento řádek] — odkaz na aktuální řádek, například vzorec =Tabulka1[[#Tento řádek];[DPH]] bude odkazovat na hodnotu DPH z aktuálního řádku tabulky.
(V anglické verzi budou tyto operátory znít jako #All, #Data, #Headers, #Totals a #This row).
PS
V Excelu 2003 bylo něco vzdáleně podobné takovým „chytrým“ tabulkám – jmenovalo se to Seznam a vytvářelo se přes menu Data – Seznam – Vytvořit seznam (Data — Seznam — Vytvořit seznam). Jenže ani polovina současné funkcionality tam vůbec nebyla. Starší verze Excelu to také neměly.