Transpozice v Excelu – v Excelu existuje několik možností, jak můžete otočit sloupce na řádky a řádky na sloupce. V dnešním příspěvku si ukážeme 4 základní způsoby plus jeden bonusový trik. Jedna metoda transpozice je statická a ostatní jsou dynamické, to znamená, že vám i po otočení dat budou reagovat na změny, které provedete ve zdrojové tabulce. Potřebujete spíše tabulku překlopit? Podívejte se na video Jak překlopit tabulku dat v Excelu.
Excelový soubor ke stažení
Transpozice v Excelu
Jako zdrojová data použijeme tabulku, ve které máme ve sloupcích list evropských zemí a jejich smyšlená HDP. Naším úkolem je otočit tuto tabulku, to znamená otočit řádky na sloupce, přesně tak jak vidíme na obrázku č.1. Pokud bychom se snažili buňky zafixovat, kopírovat a jinak přetahovat zafixované buňky, tak zjistíme, že v Excelu neexistuje možnost, jak kopírovat buňky směrem dolů, tak aby se přetahovaly do stran. Pro otočení dat musíme tedy použít jednu z následujících metod.
Kopírovat a vložit
První metoda otočení dat je nejjednodušší, ale jedná se o metodu statickou. Jednoduše si buňky, které chceme otočit označíme (B5:C12) a zkopírujeme, třeba za pomocí klávesové zkratky CTRL+C. Následně klikneme tam, kam chceme data vložit (F4) a klikneme pravým tlačítkem myši a najdeme Možnosti vložení. Z nabídky vybereme možnost Transponovat. Data se tím vloží a zároveň se otočí ze sloupců na řádek. Nevýhodou této metody transpozice je, že je to metoda statická, to znamená, že změníte-li data ve zdrojové tabulce, tak se změny nepropíší do otočené tabulky.
Manuální otočení
Pokud máte ve zdrojové tabulce málo dat a nejedná se o data, která se vám každý den rozrůstají, tak lze řádky a sloupce otočit i manuálně. Jednoduše bychom si pomohli propojením jednotlivých buněk. V našem případě bychom v buňce F15 napsali rovná se a označili buňku s Francií, tedy B15. Následovalo by Německo v buňce G15, které bychom propojili s buňkou B16. A tato bychom propojili všechny buňky ve zdrojové tabulce. Tato metoda je určitě účinná u velmi malého počtu dat. Výhodou oproti první metodě je, že je dynamická, takže se každá změna ve zdrojové tabulce díky propojení okamžitě propíše do otočené tabulky.
Funkce TRANSPOZICE
Dynamickou verzí první metody, kdy jsme data kopírovali a vkládali za pomocí možnosti Vložit jinak a Transponovat je funkce TRNSPOZICE, anglicky funkce TRANSPOSE. Tato funkce nedělá totiž nic jiného než to, že otočí řádky na sloupce a sloupce na řádky. Jediným argumentem této funkce je pole, což jsou data, která chcete otočit.
= TRANSPOZICE/TRANSPOSE (pole)
U funkce TRANSPOZICE si musíte pamatovat tři věci.
1. První pravidlo je, že než funkci TRANSPOZICI použijete, tak si musíte označit přesný počet buněk, jaký je počet buněk, které chcete otáčet. Pokud chceme otáčet tabulku o dvou sloupcích a osmi řádcích (B5:C12), tak nejdříve musíme označit osm sloupců a dva řádky (F4:M5). Když máme buňky označené, tak do první buňky napíšeme rovná se a funkce TRANSPONOVAT. Argument pole jsou buňky, které chceme otočit.
2. Druhé pravidlo, které si musíte pamatovat je to, že funkci TRANSPONOVAT nemůžete potvrdit pouze klávesou ENTER, ale musíte zmáčknout kombinaci kláves CONTROL+SHIFT+ENTER.
Funkce TRANSPOZCE je totiž maticovou funkcí a pro tyto funkce nestačí potvrdit funkci pouze klávesou ENTER. Pokud bychom na toto pravidlo zapomněli a potvrdili funkci pouze zmáčknutím klávesy ENTER, tak by vám Excel vrátil chybu, jelikož by se snažil zobrazit všechny hodnoty v jedné buňce, což není možné. Na to, aby vám maticové funkce fungovaly, je tedy vždy potřeba zmáčknout kombinaci kláves CTRL+SHIFT+ENTER.
Argumentem funkce TRANSPOZICE nemusí být pouze jeden řádek nebo jeden sloupec, ale může to být i skupina řádků a sloupců. Můžeme tedy rovnou označit celou tabulku a otočit ji. Akorát musíme vždy označit přesný počet buněk.
Tak jako všechny maticové funkce, tak i celá funkce TRANSPOZICE žije v jedné buňce, a to v první buňce funkce. Pokud byste se rozhodli smazat některé buňky uprostřed otočené tabulky, tak se vám to nepovede.
3. Třetí pravidlo, které si musíte pamatovat je to, že u maticových funkcí žije funkce pouze v první buňce, v našem případě tedy v buňce F4, do které jsme funkci poprvé vepsali. Nelze tedy změnit pouze část matice. Buď musíme změnit zdrojovou tabulku nebo funkci kompletně smazat z buňky F126 a začít znovu.
Pro uživatele nových Office 365 se práce s maticovými funkcemi výrazně zjednodušila. Pokud používáte Office 365, tak už nemusíte označovat přesný počet buněk předtím, než začnete funkci psát a ani nemusíte mačkat kombinaci CONTROL+SHIFT+ENTER, stačí pouze zmáčknout ENTER.
Power Query
Další dynamickou metodou otočení dat je použití doplňkového nástroje Excelu Power Query. V takovém případě nejdříve uděláme změníme zdrojovou tabulku na oficiální excelovou tabulku. Stačí kamkoliv v tabulce kliknout a zmáčknout klávesovou kombinaci CTRL+A, tím se označí celá tabulka. Potom zmáčkněte klávesovou kombinaci CTRL+T, čímž se z excelové tabulky stane oficiální excelová tabulka. Teď musíme tuto tabulku nahrát do Power Query. Na liště data vybereme Z tabulky nebo oblasti. Tabulka se nahraje po Power Query a zde můžeme jednoduše otočit data přes možnost transponovat.
Když máme data otočená, stačí tabulku nahrát zpátky do Excelu. Otočená tabulka se vloží na samostatný list. Propojení mezi tabulkami je dynamické, takže pokud změníme něco ve zdrojové tabulce, tak se změna propíše i do otočené tabulky. Nicméně stejně jako v kontingenční tabulce, musíme nejdříve otočenou tabulku aktualizovat. Klikneme do tabulky pravým tlačítkem a vybereme Aktualizovat.
Bonusový trik
Obdoba druhé manuální metody je i následující trik. Místo toho, abychom do každé buňky psali rovná se a označovali příslušné buňky, tak použijeme textový trik. Vybereme si libovolné písmeno abecedy, já si vyberu například písmeno T, a napíšu ho do buňky, kde chci mít později Francii. Za písmeno T napíšu označení buňky, ve které sedí Francie. Francie se nachází v buňce B6, výsledek tedy bude TB6. Pod Francií chceme mít velikost francouzského HDP, které sedí v buňce C6, takže napíšeme TC6. Takto to zopakujeme ještě pro Německo, takže v buňce G5 bude napsáno TB7 a pod tím TC7.
Nakonec zkopírujeme buňky doprava. Excel pochopil naši logiku a správně doplnil všechny buňky .
Teď si označíme tyto naše nové buňky a zmáčkneme CTRL+H, čímž se nám otevře okno pro nahrazování. Do pole najít napíšeme naše zvolené písmeno, u mě to bylo písmeno T a do pole nahradit napíšeme =. Zmáčkneme nahradit vše.
Pokud se podíváme zblízka, tak zjistím, že písmeno T bylo nahrazeno znaménkem rovná se, a proto nám buňky přímo odkazují na zdroj. Dokonce je to dynamické, takže se propíšou jakékoliv změny, které uděláte.
Jedna odpověď
Jak otočit celou tabulku naplněnou daty (čísly i textem) z vodorovné polohy do svislé, při zachování formátování buněk (bez transponování) tak, aby se vešla na jednu stránku ve formátu .pdf
Dá se to CTRL+C, otevřít Word a tam CTRL+V. Případně vložit jako obrázek, tento otočit a takto vzniklý dokument uložit
jako .pdf soubor.
Dá se tabulka otočit přímo v Excelu, bez transponování?
Děkuji za odpověď