V Excelu existují 3 základní způsoby, jak můžete překlopit tabulku hodnot. Řeč není o transpozici, kdy převádíme řádky na sloupce. Ani o řazení, kdy řadíme data dle velikosti čísla nebo pořadí písmen. Řeč je o technice, kdy tabulku prostě potřebujete jen převrátit, takže hodnoty, které jsou dole potřebujete nahoře a obráceně.
Excelový soubor ke stažení
Jak překlopit tabulku v Excelu
Úkolem je překlopit zdrojovou tabulku tak, aby se tabulka otočila, to znamená, že hodnota na posledním ádk se objeví v prvním řádku a hodnota v prvním řádku bude v nov tabulce v posledním řádku. Mimo to se pořadí hodnot ale v tabulce nezmění. Ukážeme si tři způsoby, jak překlopit data v Excelu a začneme tím nejjednodušším, kterým je překlopení tabulky pomocí indexového sloupce.
Indexový sloupec
První způsob, jak můžete velmi jednoduše převrátit data je, že si vedle tabulky vytvoříte pomocný sloupec. V tomto pomocném sloupci vytvoříme sérii čísel od jedničky až po poslední řádek v tabulce. Nakonec na celou tabulku, včetně pomocného sloupce, uplatníme automatický filtr. Nejrychlejší způsob, jak na tabulku uplatníte automatický filtr je, že kliknete do tabulky a pomocí klávesové zkratky CTRL+SHIFT a L zapnete automatický filtr.
Teď čísla v pomocném sloupci pouze seřadíme od největšího po nejnižší. Tím se data v celé tabulce jednoduše převrátí. Pokud byste data potřebovali zase vrátit zpátky do původní podoby, tak stačí tento pomocný sloupec opět seřadit od nejnižšího čísla po nejvyšší. Takto otočená data můžeme jednoduše vzít a zkopírovat tam, kam potřebujeme.
Výhodou tohoto přístupu je jeho jednoduchost. Nevýhodou je samozřejmě to, že se jedná o statickou metodu otočení, takže pokud ve zdrojové tabulce něco změníme, tak se změna neprojeví v naší nové tabulce. Pokud bychom ve zdrojové tabulce provedli změny, museli bychom celý proces opakovat.
Funkce INDEX
Druhý způsob otočení dat se vám bude hodit, pokud z nějakého důvodu nemůžete nebo nechcete vytvářet pomocný sloupec vedle zdrojové tabulky a zároveň, pokud chcete, aby vaše otočená tabulka byla stále propojená se zdrojovou tabulkou. Tabulku v tomto případě otočíme pomocí funkce INDEX. Podíváme se nejprve na parametry funkce INDEX, abychom věděli, čeho chceme ve funkci docílit.
= INDEX (pole; řádek; [sloupec])
První parametr pole je jasné, to budou naše zdrojová data. Pro trik s otočením dat je klíčový druhý parametr funkce, tedy parametr řádky.
Funkce ŘÁDKY a trik s dynamickým rozpětím
Aby se data správně otočila, tak potřebujeme, aby se v parametru řádky funkce INDEX na prvním řádku objevilo číslo 11, jelikož v tabulce je celkem 11 řádků. Na dalším řádku ale potřebujeme, aby se objevilo číslo 10, na dalším zase číslo 9 a tak dále. Potřebujeme tedy vytvořit dynamické rozpětí.
Toho docílíme pomocí triku s dvěma funkcemi ŘÁDKY. Trik si nejprve ukážeme vedle tabulky a pak to spojíme s funkcí INDEX. Do buňky I17 napíšeme funkci ŘÁDKY. A jako pole označíme první sloupec zdrojové tabulky (A17:A27). Funkci potvrdíme a vrátila se hodnota 11, a to proto, že funkce ŘÁDKY spočítala počet řádků v tabulce a vrátila toto číslo. Jelikož budeme chtít v tabulce funkcí vyplnit celou tabulku, tak se k funkci vrátíme a zafixujeme buňky klávesou F4 pro řádky (A$17:A$27). Funkci protáhneme dolů a na každém řádku se objevilo číslo 11. Vrátíme se do buňky I17 a od první funkce ŘÁDKY odečteme druhou funkci ŘÁDKY. Do této druhé funkce ŘÁDKY napíšeme koordinaci buňky, ve které právě jsme, tedy I17, dvojtečka I17 (I17:I17). To ovšem není vše, buňky musíme ještě zafixovat. První buňku zafixujeme jak pro řádky, tak sloupce ($I$17). Druhou buňku zafixujeme pouze pro sloupec ($I17). Potvrdíme a stáhneme funkci dolů. Z čísel 11 se stala číselná řada od 10 až po 0. Proč?
První funkce ŘÁDKY na každém řádku vrátila hodnotu 11, což je celkový počet řádků v tabulce. Druhá funkce ŘÁDKY v první buňce vrátila číslo 1, jelikož I17:I17 je jeden řádek. Tím, že jsme ovšem první buňku zafixovali jak pro řádky, tak sloupce, tak se při posunutí funkce o jeden řádek dolů stalo z I17:I17 rozpětí I17:I18, což jsou dva řádky. Na druhém řádku se tedy od čísla 11 odečetla dvojka, tedy devět. Nicméně v tabulce máme 11 řádků a potřebujeme, aby i zde bylo číslo 11. Takže k celé této funkci přičteme jedničku.
Teď když máme dynamické rozpětí správně, můžeme ho použít ve funkci INDEX.
Do první buňky v nové tabulce (E17) napíšeme funkci INDEX, kde jako pole označíme první sloupec v tabulce (A17:A27). Jelikož budeme chtít funkcí INDEX vyplnit celou tabulku, tak buňky zafixujeme klávesou F4 pouze pro řádky (A$17:A$27). Následuje středník a přepneme se do parametru řádky. A zde napíšeme první funkci ŘÁDKY, označíme první sloupec v tabulce, zafixujeme pro řádky (A$17:A$27). Následuje znaménko mínus a druhá funkce ŘÁDKY, kde napíšeme koordinace buňky, ve které se právě nacházíme, což je buňka E17, takže E17:E17, přičemž první buňku zafixujeme jak pro sloupce, tak řádky a druhou buňku pouze pro sloupec ($E$17:$E17). Nakonec přičteme jedničku a potvrdíme. Celou funkci protáhneme doprava a dolů. A tabulka je otočená. Výhodou je, že jsou tabulky stále propojené, takže pokud ve zdrojové tabulce provedeme změny, tak se tyto změny okamžitě projeví i v otočené tabulce
Funkce SORTBY
Předplatitelé služby Office 365 mohou k otočení dat využít funkci SORTBY. Stačí do tabulky do první buňky (E31) napsat funkci SORTBY a jako pole označit celou tabulku (A31:C41), následuje druhý parametr podle pole, zde napíšeme funkci ŘÁDEK, kde opět označíme celou tabulku (A31:C41). Poslední parametr, který potřebujeme je styl řazení. Zde stačí vybrat -1 jako sestupné řazení. Potvrdíme a tabulka se otočila. Samozřejmě je plně dynamická, takže jakákoliv změna ve zdrojové tabulce se okamžitě projeví v nové, otočené tabulce.
