Transponování dat znamená změnu orientace pole převedením jeho řádků na sloupce nebo naopak. V tomto tutoriálu se s vámi podělíme o pět různých metod pro přepínání orientace pole a dosažení požadovaného výsledku.

Přepněte sloupce na řádky pomocí Vložit jinak

Transponujte a propojte data se zdrojem

• S funkcí TRANSPOSE
• S funkcemi NEPŘÍMÝ, ADRESA, SLOUPEK a ŘÁDEK
• Pomocí speciální pasty a funkce Najít a nahradit
• S Power Query

Stáhnout příklady transponovaných dat (.xlsx)

Video: Transponujte data v Excelu

Přepněte sloupce na řádky pomocí Vložit jinak

Krok 1: Zkopírujte rozsah, který má být transponován

Vyberte rozsah buněk, kde chcete přepínat řádky a sloupce, a stiskněte Ctrl + C zkopírovat rozsah.

Krok 2: Vyberte možnost Paste Transpose

Klepněte pravým tlačítkem myši na první buňku cílového rozsahu a potom klepněte na Přemístit ikona (V rámci Možnosti vložení) z nabídky pravého tlačítka myši.

Výsledek

Voila! Rozsah je transponován během okamžiku!

Transponujte a propojte data se zdrojem

Chcete-li dynamicky přepnout orientaci rozsahu (přepnout sloupce na řádky a naopak) a připojit přepínaný výsledek k původní datové sadě, můžete použít některý z níže uvedených přístupů:

• Funkce TRANSPOSE (Snadné použití, ale výsledky nelze upravovat)
• Funkce NEPŘÍMÉ, ADRESA, SLOUPCE a ŘÁDEK (Velký vzorec, ale umožňuje provádět změny ve výsledcích)
• Vložit speciální a najít a nahradit (Složité, ale snadno pochopitelné)
• Dotaz na napájení (Snadné s výsledky formátovanými v tabulce)

Změňte řádky na sloupce pomocí funkce TRANSPOSE

Pro otočení řádků na sloupce a naopak pomocí PŘEMÍSTIT funkce, postupujte následovně.

Krok 1: Vyberte stejný počet prázdných buněk jako původní sada buněk, ale v jiném směru

Tip: Pokud používáte Excel 365 nebo Excel 2021, tento krok přeskočte.

Řekněme, že váš původní stůl je v dosahu A1: C4, což znamená, že tabulka má 4 řádky a 3 sloupce. Transponovaná tabulka tedy bude mít 3 řádky a 4 sloupce. To znamená, že byste měli vybrat 3 řádky a 4 sloupce prázdných buněk.

Krok 2: Zadejte vzorec TRANSPOSE

Do řádku vzorců zadejte níže uvedený vzorec a stiskněte Ctrl + Shift + Enter získat výsledek.

Tip: Stisknout vstoupit pokud jste uživateli Excelu 365 nebo Excelu 2021.

=TRANSPOSE(A1:C4)

Výsledek

Řádky se převedou na sloupce a sloupce se převedou na řádky.

Otočte data pomocí funkcí NEPŘÍMÝ, ADRESA, SLOUPEK a ŘÁDEK

Ačkoli je výše uvedený vzorec poměrně snadno pochopitelný a použitelný, jeho nevýhodou je, že v otočené tabulce nelze upravovat ani mazat žádné buňky. Zavedu tedy vzorec pomocí NEPŘÍMÝ, ADRESA, SLOUPEK a ŘÁDEK funkcí. Řekněme, že váš původní stůl je v dosahu A1: C4, chcete-li provést transformaci sloupců na řádky a zachovat otočená data připojená ke zdrojové datové sadě, postupujte podle následujících kroků.

Krok 1: Zadejte vzorec

Zadejte níže uvedený vzorec do levé horní buňky cílového rozsahu (A6 v našem případě) a stiskněte vstoupit:

=INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))

Krok 2: Zkopírujte vzorec do buněk vpravo a pod

Vyberte buňku vzorce a přetáhněte její úchyt (malý zelený čtverec v pravém dolním rohu buňky) dolů a poté doprava na tolik řádků a sloupců, kolik potřebujete.

Výsledek

Sloupce a řádky se okamžitě vymění.

Převeďte sloupce na řádky pomocí Vložit jinak a Najít a nahradit

Několik dalších kroků s metodou Vložit jinak spolu s funkcí Najít a nahradit vám umožní transponovat data a zároveň propojit zdrojové buňky s transponovanými.

Krok 1: Zkopírujte rozsah, který má být transponován

Vyberte rozsah buněk, které chcete transponovat, a stiskněte Ctrl + C zkopírovat rozsah.

Krok 2: Použijte možnost Vložit odkaz

1. Klepněte pravým tlačítkem myši na prázdnou buňku a potom klepněte Vložit jinak.

   

2. Klikněte na Vložit odkaz.

   

Dostanete výsledek, jak je uvedeno níže:

Krok 3: Najděte a nahraďte rovnítko (=) z výsledku Vložit odkaz

1. Vyberte rozsah výsledků (A6: C9) a stiskněte Ctrl + Ha poté vyměňte = s @EO (nebo jakýkoli znak(y), který ve vybraném rozsahu neexistuje) v Najít a nahradit dialog.

   

2. Klikněte na Nahradit všea poté dialogové okno zavřete. Jak budou data vypadat, ukazuje následující.

   

Krok 4: Transponujte nahrazený výsledek Vložit odkaz

Vyberte rozsah (A6: C9) a stiskněte Ctrl + C zkopírovat to. Klikněte pravým tlačítkem myši na prázdnou buňku (zde jsem vybral A11) a vyberte Přemístit ikona od Možnosti vložení pro vložení transponovaného výsledku.

Krok 5: Získejte zpět rovnítko (=), abyste propojili transponovaný výsledek s původními daty

1. Uchovejte transponovaná výsledná data A11: D13 a poté stiskněte Ctrl + Ha vyměnit @EO s = (Opak kroku 3).

   

2. Klikněte na Nahradit všea poté dialogové okno zavřete.

Výsledek

Data jsou transponována a spojena s původními buňkami.

Transponujte a propojte data se zdrojem pomocí Power Query

Power Query je výkonný nástroj pro automatizaci dat, který umožňuje snadnou transpozici dat v Excelu. Práci můžete provést podle následujících kroků:

Krok 1: Vyberte rozsah, který chcete transponovat, a otevřete Editor Power Query

Vyberte rozsah dat, která mají být transponována. A pak, na Data v záložce Získejte a transformujte data skupiny, klepněte na tlačítko Z tabulky/rozsahu.

Krok 2: Převeďte sloupce na řádky pomocí Power Query

1. Přejít na Změnit záložka. V Použijte první řádek jako záhlaví rozbalovací nabídku, vyberte Použijte záhlaví jako první řádek.

   

2. Klikněte na Přemístit.

   

Krok 3: Uložte transponovaná data na list

Na Soubor klepněte na kartu Zavřít a načíst zavřete okno Power Editor a vytvořte nový list pro načtení transponovaných dat.

Výsledek

Transponovaná data se převedou na tabulku v nově vytvořeném listu.

Související články

• Jak rychle transponovat rozsahy a přeskočit prázdné buňky v aplikaci Excel?
• Když vložíme rozsah jako transponovaný, vloží se i prázdné buňky. Někdy však chceme pouze transponovat rozsahy ignorováním prázdných buněk, jak je znázorněno na obrázku níže. Existují nějaké způsoby, jak rychle transponovat rozsahy a přeskočit mezery v aplikaci Excel?
• Jak transponovat každých 5 nebo n řádků z jednoho sloupce do více sloupců?
• Předpokládejme, že ve sloupci A máte dlouhá data, a nyní chcete transponovat každých 5 řádků ze sloupce A do více sloupců, například transponovat A1: A5 do C6: G6, A6: A10 až C7: G7 atd. Jako je zobrazen následující snímek obrazovky. Jak byste mohli zvládnout tento úkol bez opakovaného kopírování a vkládání v aplikaci Excel?
• Jak transponovat / převést sloupce a řádky do jednoho sloupce?
• Při použití listu aplikace Excel se někdy setkáte s tímto problémem: jak byste mohli převést nebo transponovat rozsah dat do jednoho sloupce? (Viz následující screenshoty :) Nyní představuji tři rychlé triky, jak tento problém vyřešit.
• Jak transponovat / převést sloupce a řádky do jednoho řádku?
• Jak spojit více řádků a sloupců do jednoho dlouhého řádku? Možná se vám to zdá snadné, protože je můžete zkopírovat jeden po druhém a ručně je spojit do řady. To však může být časově náročné a únavné, pokud existují stovky řádků a sloupců. Zde budu mluvit o několika rychlých tricích, jak to vyřešit.