5 způsobů, jak transponovat data v Excelu (výukový program krok za krokem)
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.
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!
(AD) Pomocí Kutools snadno transponujte rozměry stolu
Převod křížové tabulky (dvourozměrná tabulka) na plochý seznam (jednorozměrný seznam) nebo naopak v Excelu vždy zabere spoustu času a úsilí. Nicméně, s nainstalovaným Kutools pro Excel, jeho Transponujte rozměry tabulky nástroj vám pomůže provést konverzi rychle a snadno.
Kutools pro Excel - Obsahuje více než 300 užitečných funkcí, které vám usnadní práci. Získejte 30denní plnohodnotnou bezplatnou zkušební verzi!
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é)
- Power Query (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)
- Měli byste se změnit A1: C4 na váš skutečný zdrojový rozsah, který chcete transponovat.
- Pokud jsou v původním rozsahu nějaké prázdné buňky, PŘEMÍSTIT funkce převede prázdné buňky na 0s (nuly). Abyste se zbavili nulových výsledků a ponechali prázdné buňky při transpozici, musíte využít výhod IF funkce:
-
=TRANSPOSE(IF(A1:C4="","",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)))
- Měli byste se změnit A1 do levé horní buňky vašeho skutečného zdrojového rozsahu, který budete transponovat, a ponechte znaky dolaru tak, jak jsou. Znak dolaru ($) před písmenem sloupce a číslem řádku označuje absolutní odkaz, který ponechá písmeno sloupce a číslo řádku nezměněné, když vzorec přesunete nebo zkopírujete do jiných buněk.
- Pokud jsou v původním rozsahu nějaké prázdné buňky, vzorec převede prázdné buňky na 0s (nuly). Abyste se zbavili nulových výsledků a ponechali prázdné buňky při transpozici, musíte využít výhod IF funkce:
-
=IF(INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))=0,"",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
-
Klepněte pravým tlačítkem myši na prázdnou buňku a potom klepněte Vložit jinak.
-
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
-
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.
-
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
-
Uchovejte transponovaná výsledná data A11: D13 a poté stiskněte Ctrl + Ha vyměnit @EO s = (Opak kroku 3).
-
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 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 soubor Power Query editor
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.
- Pokud vybraný rozsah dat není v tabulce, a Vytvořit tabulku objeví se dialogové okno; klikněte OK vytvořit pro něj tabulku.
- Pokud používáte Excel 2013 nebo 2010 a nemůžete najít Z tabulky/rozsahu na Data kartu, budete si ji muset stáhnout a nainstalovat z Microsoft Power Query pro stránku Excel. Po instalaci přejděte na Power Query klepněte na kartu Z tabulky v Excel Data skupina.
Krok 2: Převeďte sloupce na řádky pomocí Power Query
-
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.
-
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.
- Další záhlaví sloupců jsou generována v prvním řádku, jak je uvedeno výše. Chcete-li povýšit první řádek pod záhlaví na záhlaví sloupců, vyberte buňku v datech a klikněte na Dotaz > Změnit. Poté vyberte Změnit > Použijte první řádek jako záhlaví. Nakonec vyberte Domů > Zavřít a načíst.
- Pokud jsou v původní datové sadě provedeny nějaké změny, můžete aktualizovat transponovaná data výše těmito změnami kliknutím na Obnovit ikona vedle stolu v Dotazy a připojení podokno, nebo kliknutím na Obnovit Tlačítko na Dotaz Karta.
(AD) Transformujte rozsah pomocí Kutools několika kliknutími
Projekt Rozsah transformace nástroj v Kutools pro Excel vám může pomoci snadno převést (převést) svislý sloupec na několik sloupců nebo naopak, nebo převést řádek na více řádků nebo naopak.
Kutools pro Excel - Obsahuje více než 300 užitečných funkcí, které vám usnadní práci. Získejte 30denní plnohodnotnou bezplatnou zkušební verzi!
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.
Nejlepší nástroje pro produktivitu v kanceláři
Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času. Kliknutím sem získáte funkci, kterou nejvíce potřebujete...
Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci
- Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
- Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
- Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
Obsah
- Video: Transponujte data v Excelu
- 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
- Související články
- Nejlepší kancelářské nástroje produktivity
- Komentáře