Naučte se transponovat data v Excelu a rychle změnit orientaci sloupců a řádků na listu.
Transponování dat v Excelu znamená přepínání nebo rotaci dat z horizontálního pole do vertikálního pole nebo z vertikálního pole do horizontálního pole. Jednoduše řečeno, funkce TRANSPOSE mění řádky na sloupce a sloupce na řádky.
V Excelu můžete data transponovat dvěma různými způsoby, pomocí funkce TRANSPOSE a metody Vložit jinak. V tomto článku vám ukážeme, jak v Excelu přepnout hodnotu řádku na hodnotu sloupce a hodnotu sloupce na hodnotu řádku.
Použitím Funkce TRANSPOSE
Funkce TRANSPOSE umožňuje přepnout orientaci daného rozsahu buněk (pole) z vertikální na horizontální nebo naopak. Protože se jedná o maticový vzorec, funkce musí být zadána v rozsahu, který má stejný počet řádků a sloupců jako zdroj.
Syntaxe funkce Transpose je:
=Transpose (pole)V následující vzorové tabulce transponujeme tabulku, která obsahuje objem vývozu zvířat podle krajů.
Nejprve musíte vědět, kolik řádků a sloupců obsahuje vaše původní tabulka. Poté vyberte přesný počet buněk jako původní rozsah buněk, ale v opačném směru.
Například máme 5 řádků a 3 sloupce, takže pro transponovaná data musíme vybrat 3 řádky a 5 sloupců buněk. V našem případě máme 5 řádků a 4 sloupce, takže je třeba vybrat 4 řádky a 5 sloupců.
Vyberte rozsah prázdných buněk a zadejte vzorec: =TRANSPOSE(A1:D5), ale ještě nestiskněte klávesu ‚Enter‘! V tomto okamžiku bude váš Excel vypadat takto:
Nyní stiskněte „Ctrl + Shift + Enter“. Jakmile je kombinace kláves stisknuta, data budou transponována.
Pamatujte, že maticový vzorec musí být vždy dokončen stisknutím kláves CTRL + SHIFT + ENTER. Když stisknete kombinaci kláves, umístí se kolem vzorce na řádku vzorců sada složených závorek, takže výsledek bude považován za pole dat, nikoli za hodnotu jedné buňky.
Funkce Excel Transpose pouze zkopíruje data, nikoli formátování původních dat. Jak vidíte, formátování původního záhlaví se nezkopíruje. Také Transpose je dynamická funkce, když je vaše hodnota v původních datech upravena, projeví se to v transponovaných datech, ale ne naopak.
Jak transponovat data v Excelu bez nul
Pokud transponujete tabulku nebo rozsah buněk s jednou nebo více prázdnými buňkami, tyto buňky budou mít při transpozici nulové hodnoty, jak je znázorněno na obrázku níže:
Chcete-li zkopírovat prázdné buňky během transpozice, použijte funkci IF uvnitř vzorce TRANSPOSE a zkontrolujte, zda je buňka prázdná nebo ne. Pokud je buňka prázdná/prázdná, vrátí do transponované tabulky prázdný řetězec (“”).
Pokud má tabulka prázdné buňky, zadejte vzorec: =TRANSPOSE(IF(A1:D5="",","A1:D5)) ve vybraných buňkách.
Poté stiskněte kombinaci kláves „Ctrl + Shift + Enter“. Nyní se prázdné místo v buňce D5 zkopíruje do transponované buňky E11.
Jak provést transpozici v Excelu pomocí Speciální metoda pasty
Dalším způsobem, jak můžete transponovat data v Excelu, je použití metody Vložit jinak. Ale transponování dat pomocí metody Paste Special přichází s nevýhodou a profíkem. Tato funkce umožňuje transponovat data s původním formátováním obsahu, ale transponovaná data budou statická.
Na rozdíl od funkce TRANSPOSE, pokud změníte hodnotu v původních datech, neprojeví se to v transponovaném obsahu při použití metody Vložit jinak.
Nejprve vyberte rozsah, který chcete transponovat, a poté klikněte pravým tlačítkem myši a vyberte „Kopírovat“ nebo stiskněte „CTRL + C“ pro zkopírování tabulky.
Dále vyberte rozsah buněk, který přesně odpovídá velikosti původní tabulky, stejně jako u předchozí metody.
Poté klikněte pravým tlačítkem myši na vybrané buňky a klikněte na ‚Vložit jinak‘.
V dialogovém okně Vložit jinak zaškrtněte políčko „Transpose“ a klikněte na „OK“.
Nyní bude tabulka transponována (zkopírována) do vybraných buněk.
V této metodě se nemusíte obávat, že se černé buňky stanou nulami v transponované tabulce, protože prázdné buňky budou také zkopírovány do vybraných buněk.