Podívejte se na video - Jak transponovat data v Excelu
Pokud máte datovou sadu a chcete ji transponovat v Excelu (což znamená převádět řádky na sloupce a sloupce na řádky), dělat to ručně je úplné NE!
Transponujte data pomocí Vložit jinak
Vložit jinak dokáže spoustu úžasných věcí a jednou takovou věcí je transpozice dat v Excelu.
Předpokládejme, že máte datovou sadu, jak je uvedeno níže:
Tato data obsahují oblasti ve sloupci a čtvrtiny za sebou.
Pokud z nějakého důvodu potřebujete tato data transponovat, postupujte takto:
- Vyberte sadu dat (v tomto případě A1: E5).
- Zkopírujte datovou sadu (Ctrl + C) nebo klikněte pravým tlačítkem a vyberte kopírovat.
- Nyní můžete transponovaná data vložit na nové místo. V tomto příkladu chci kopírovat v G1: K5, takže klikněte pravým tlačítkem na buňku G1 a vyberte Vložit jinak.
- V dialogovém okně Vložit speciální zaškrtněte možnost transpozice vpravo dole.
- Klikněte na OK.
To by okamžitě zkopírovalo a vložilo data, ale takovým způsobem, že byly transponovány. Níže je ukázka ukazující celý proces.
Výše uvedené kroky zkopírují hodnotu, vzorec (pokud existuje) a také formát. Pokud chcete pouze zkopírovat hodnotu, vyberte v dialogovém okně Vložit speciální hodnotu „hodnota“.
Zkopírovaná data jsou statická a pokud provedete jakékoli změny v původní sadě dat, tyto změny se neprojeví v transponovaných datech.
Pokud chcete, aby byly tyto transponované buňky propojeny s původními buňkami, můžete zkombinovat sílu funkce Najít a nahradit s Vložit jinak.
Transponujte data pomocí Vložit jinak a Najít a nahradit
Samotné použití Paste Special vám poskytne statická data. To znamená, že pokud se změní vaše původní data a chcete, aby byla aktualizována i transponovaná data, pak je k transpozici budete muset použít znovu Vložit jinak.
Zde je skvělý trik, který můžete použít k transpozici dat a jejich propojení s původními buňkami.
Předpokládejme, že máte datovou sadu, jak je uvedeno níže:
Zde jsou kroky k transpozici dat, ale odkazy zůstanou nedotčené:
- Vyberte sadu dat (A1: E5).
- Zkopírujte jej (Ctrl + C nebo klikněte pravým tlačítkem a vyberte kopírovat).
- Nyní můžete transponovaná data vložit na nové místo. V tomto příkladu chci kopírovat v G1: K5, takže klikněte pravým tlačítkem na buňku G1 a vyberte Vložit jinak.
- V dialogovém okně Vložit jinak klikněte na tlačítko Vložit odkaz. Získáte stejnou sadu dat, ale zde jsou buňky propojeny s původní sadou dat (například G1 je spojeno s A1 a G2 je spojeno s A2 atd.).
- Když jsou vybrána tato nová zkopírovaná data, stiskněte Ctrl + H (nebo přejděte na Domů -> Úpravy -> Najít a vybrat -> Nahradit). Otevře se dialogové okno Najít a nahradit.
- V dialogovém okně Najít a nahradit použijte následující:
- V Najít co: =
- V Nahradit:!@# (Všimněte si, že používám!@#, Protože je to jedinečná kombinace znaků, která pravděpodobně nebude součástí vašich dat. Můžete použít jakoukoli takovou jedinečnou sadu znaků).
- Klikněte na Nahradit vše. Tím nahradíte rovnici ze vzorce a budete mít!@# Následovaný odkazem na buňku v každé buňce.
- Klikněte pravým tlačítkem a zkopírujte datovou sadu (nebo použijte Ctrl + C).
- Vyberte nové umístění, klikněte pravým tlačítkem a vyberte Vložit jinak. V tomto případě jej vkládám do buňky G7. Můžete jej vložit kamkoli chcete.
- V dialogovém okně Vložit jinak vyberte Transponovat a klikněte na OK.
- Zkopírujte a vložte tato nově vytvořená transponovaná data do té, ze které jsou vytvořena.
- Nyní znovu otevřete dialogové okno Najít a nahradit a nahraďte!@# Znakem =.
Tím získáte propojená data, která byla transponována. Pokud provedete nějaké změny v původní sadě dat, transponovaná data se automaticky aktualizují.
Poznámka: Protože naše původní data mají A1 jako prázdné, budete muset ručně odstranit 0 v G1. Když vložíme odkazy, zobrazí se 0, protože odkaz na prázdnou buňku by stále vrátil hodnotu 0. Také byste museli naformátovat novou datovou sadu (formát můžete jednoduše zkopírovat a vložit pouze z původní datové sady).
Transponujte data pomocí funkce Excel TRANSPOSE
Funkci Excel TRANSPOSE - jak název napovídá - lze použít k transpozici dat v aplikaci Excel.
Předpokládejme, že máte datovou sadu, jak je uvedeno níže:
Zde jsou kroky k transpozici:
- Vyberte buňky, do kterých chcete transponovat datovou sadu. Všimněte si, že musíte vybrat přesný počet buněk jako původní data. Pokud tedy máte například 2 řádky a 3 sloupce, musíte vybrat 3 řádky a 2 sloupce buněk, kam chcete transponovaná data. V tomto případě, protože existuje 5 řádků a 5 sloupců, musíte vybrat 5 řádků a 5 sloupců.
- Do aktivní buňky (což by měla být horní levá buňka výběru) zadejte = TRANSPOSE (A1: E5) a stiskněte Ctrl Shift Enter.
To by transponovalo datovou sadu.
Zde je několik věcí, které byste měli vědět o funkci TRANSPOSE:
- Je to funkce pole, takže musíte použít Control-Shift-Enter a ne jen Enter.
- Část výsledku nemůžete smazat. Musíte odstranit celé pole hodnot, které funkce TRANSPOSE vrací.
- Funkce Transpose kopíruje pouze hodnoty, nikoli formátování.
Transponujte data pomocí Power Query
Power Query je výkonný nástroj, který vám umožňuje rychle transponovat data v aplikaci Excel.
Power Query je součástí Excelu 2016 (Získat a transformovat na kartě Data), ale pokud používáte Excel 2013 nebo 2010, musíte jej nainstalovat jako doplněk.
Předpokládejme, že máte sadu dat, jak je uvedeno níže:
Zde jsou kroky k transpozici těchto dat pomocí Power Query:
V aplikaci Excel 2016
- Vyberte data a přejděte na Data -> Získat a transformovat -> Z tabulky.
- V dialogovém okně Vytvořit tabulku zkontrolujte, zda je rozsah správný, a klikněte na OK. Tím se otevře dialogové okno Editor dotazů.
- V dialogovém okně Editor dotazů vyberte kartu „Transformovat“.
- Na kartě Transformace přejděte na Tabulka -> Použít první řádek jako záhlaví -> Použít záhlaví jako první řádek. Tím je zajištěno, že první řádek (který obsahuje záhlaví: Q1, Q2, Q3 a Q4) bude také považován za data a bude transponován.
- Klikněte na Transponovat. To by okamžitě transponovalo data.
- Klikněte na Použít první řádek jako záhlaví. Tím by se nyní první řádek transponovaných dat stal záhlavím.
- Přejděte na Soubor -> Zavřít a načíst. Tím se zavře okno Power Editoru a vytvoří se nový list, který obsahuje transponovaná data.
Všimněte si toho, že protože horní levá buňka naší sady dat byla prázdná, získá obecný název Sloupec1 (jak je uvedeno níže). Tuto buňku můžete z transponovaných dat odstranit.
V aplikaci Excel 2013/2010
V aplikaci Excel 2013/10 je třeba nainstalovat Power Query jako doplněk.
Kliknutím sem stáhnete plugin a získáte pokyny k instalaci.
Jakmile si nainstalujete Power Query, přejděte na Power Query -> Data aplikace Excel -> Z tabulky.
Otevře se dialogové okno pro vytvoření tabulky. Nyní postupujte stejně jako v aplikaci Excel 2016.
