✅ Ako rýchlo transponovať údaje v programe Excel (podrobný sprievodca)

Obsah

Transponujte údaje pomocou funkcie Prilepiť špeciálne
Transponujte údaje pomocou príkazu Vložiť špeciálne a Hľadať a nahradiť
Transponujte údaje pomocou funkcie Excel TRANSPOSE
Transponujte údaje pomocou Power Query

Pozrite si video - Ako transponovať údaje v programe Excel

Ak máte množinu údajov a chcete ju transponovať v programe Excel (čo znamená prevádzať riadky na stĺpce a stĺpce na riadky), ručné vykonanie tohto postupu je úplné NIE!

Transponujte údaje pomocou funkcie Prilepiť špeciálne

Vložiť špeciálne dokáže veľa úžasných vecí a jednou z nich je transpozícia údajov v programe Excel.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie:

Tieto údaje majú regióny v stĺpci a štvrtiny za sebou.

Ak z nejakého dôvodu potrebujete transponovať tieto údaje, postupujte takto: Vložiť špeciálne:

Vyberte množinu údajov (v tomto prípade A1: E5).
Skopírujte množinu údajov (Ctrl + C) alebo kliknite pravým tlačidlom myši a vyberte možnosť Kopírovať.
Teraz môžete transponované údaje prilepiť na nové miesto. V tomto prípade chcem skopírovať do G1: K5, takže kliknite pravým tlačidlom myši na bunku G1 a vyberte príkaz prilepiť špeciálne.
V dialógovom okne prilepiť špeciálne začiarknite možnosť transpozície vpravo dole.
Kliknite na tlačidlo OK.

Údaje by sa okamžite skopírovali a prilepili, ale takým spôsobom, že boli transponované. Nasleduje ukážka celého postupu.

Vyššie uvedené kroky skopírujú hodnotu, vzorec (ak existuje), ako aj formát. Ak chcete iba skopírovať hodnotu, v dialógovom okne prilepiť špeciálne vyberte „hodnota“.

Skopírované údaje sú statické a ak vykonáte zmeny v pôvodnej množine údajov, tieto zmeny sa v transponovaných údajoch neprejavia.

Ak chcete, aby boli tieto transponované bunky prepojené s pôvodnými bunkami, môžete kombinovať silu funkcie Nájsť a nahradiť s Vložiť inak.

Transponujte údaje pomocou príkazu Vložiť špeciálne a Hľadať a nahradiť

Samotné použitie Paste Special vám poskytne statické údaje. To znamená, že ak sa zmenia vaše pôvodné údaje a chcete, aby sa aktualizovali aj transponované údaje, potom na ich transpozíciu musíte znova použiť príkaz Prilepiť špeciálne.

Tu je skvelý trik, ktorý môžete použiť na transpozíciu údajov a ich prepojenie s pôvodnými bunkami.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie:

Nasledujú kroky na transpozíciu údajov, pričom odkazy zostanú nedotknuté:

Vyberte množinu údajov (A1: E5).
Skopírujte ho (Ctrl + C alebo kliknite pravým tlačidlom myši a vyberte možnosť Kopírovať).
Teraz môžete transponované údaje prilepiť na nové miesto. V tomto prípade chcem skopírovať do G1: K5, takže kliknite pravým tlačidlom myši na bunku G1 a vyberte príkaz prilepiť špeciálne.
V dialógovom okne Vložiť špeciálne kliknite na tlačidlo Vložiť prepojenie. Získate tým rovnaký súbor údajov, ale tu sú bunky prepojené s pôvodným súborom údajov (napríklad G1 je prepojené s A1 a G2 je prepojené s A2 atď.).
Keď sú vybraté tieto nové skopírované údaje, stlačte kombináciu klávesov Control + H (alebo prejdite na položku Domov -> Úpravy -> Nájsť a vybrať -> Nahradiť). Otvorí sa dialógové okno Hľadať a nahradiť.
V dialógovom okne Hľadať a nahradiť použite nasledujúci postup:
- V Nájdite čo: =
- Nahradiť:!@# (Všimnite si, že používam!@#, Pretože ide o jedinečnú kombináciu znakov, ktorá pravdepodobne nebude súčasťou vašich údajov. Môžete použiť akúkoľvek takú jedinečnú sadu znakov).
Kliknite na položku Nahradiť všetko. Tým sa nahradí rovnica zo vzorca a budete mať!@#, Za ktorými bude v každej bunke odkaz na bunku.
Kliknite pravým tlačidlom myši a skopírujte množinu údajov (alebo použite kombináciu klávesov Control + C).
Vyberte nové umiestnenie, kliknite pravým tlačidlom myši a vyberte možnosť Prilepiť špeciálne. V tomto prípade ho vkladám do bunky G7. Môžete ho prilepiť kdekoľvek chcete.
V dialógovom okne Prilepiť špeciálne vyberte položku Transponovať a kliknite na tlačidlo OK.
Skopírujte a prilepte tieto novovytvorené transponované údaje na údaje, z ktorých sú vytvorené.
Teraz znova otvorte dialógové okno Hľadať a nahradiť a nahraďte!@# Znakom =.

Získate tak prepojené údaje, ktoré boli transponované. Ak v pôvodnom súbore údajov vykonáte akékoľvek zmeny, transponované údaje sa automaticky aktualizujú.

Poznámka: Pretože naše pôvodné údaje majú A1 ako prázdny, 0 v G1 budete musieť manuálne odstrániť. Keď vložíme odkazy, zobrazí sa 0, pretože odkaz na prázdnu bunku by stále vrátil hodnotu 0. Tiež by ste museli naformátovať novú množinu údajov (formát môžete jednoducho skopírovať a prilepiť iba z pôvodnej množiny údajov).

Transponujte údaje pomocou funkcie Excel TRANSPOSE

Na transpozíciu údajov v Exceli je možné použiť funkciu Excel TRANSPOSE - ako naznačuje názov.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie:

Tu sú kroky na transpozíciu:

Vyberte bunky, do ktorých chcete transponovať množinu údajov. Upozorňujeme, že ako pôvodné údaje musíte vybrať presný počet buniek. Ak máte napríklad 2 riadky a 3 stĺpce, musíte vybrať 3 riadky a 2 stĺpce buniek, do ktorých chcete transponovať údaje. V tomto prípade, pretože existuje 5 riadkov a 5 stĺpcov, musíte vybrať 5 riadkov a 5 stĺpcov.
Do aktívnej bunky (ktorá by mala byť ľavou hornou bunkou výberu) zadajte = TRANSPOSE (A1: E5) a stlačte Ctrl Shift Enter.

Tým by sa transponoval súbor údajov.

Tu je niekoľko vecí, ktoré potrebujete vedieť o funkcii TRANSPOSE:

Je to funkcia poľa, takže musíte používať Ctrl-Shift-Enter a nie iba Enter.
Časť výsledku nemôžete vymazať. Musíte vymazať celé pole hodnôt, ktoré funkcia TRANSPOSE vracia.
Funkcia Transpose kopíruje iba hodnoty, nie formátovanie.

Transponujte údaje pomocou Power Query

Power Query je účinný nástroj, ktorý vám umožní rýchlo transponovať údaje v programe Excel.

Power Query je súčasťou Excelu 2016 (Získať a transformovať na karte Údaje), ale ak používate Excel 2013 alebo 2010, musíte si ho nainštalovať ako doplnok.

Predpokladajme, že máte súbor údajov, ako je uvedené nižšie:

Tu sú kroky na transpozíciu týchto údajov pomocou Power Query:

V programe Excel 2016

Vyberte údaje a prejdite na položku Údaje -> Získať a transformovať -> Z tabuľky.
V dialógovom okne Vytvoriť tabuľku skontrolujte, či je rozsah správny, a kliknite na tlačidlo OK. Tým sa otvorí dialógové okno Editor dotazov.
V dialógovom okne Editor dotazov vyberte kartu „Transformovať“.
Na karte Transformácia prejdite na Tabuľku -> Použiť prvý riadok ako hlavičky -> Použiť hlavičky ako prvý riadok. To zaisťuje, že prvý riadok (ktorý obsahuje hlavičky: Q1, Q2, Q3 a Q4) bude tiež považovaný za údaje a transponovaný.
Kliknite na Transponovať. To by okamžite transponovalo údaje.
Kliknite na položku Použiť prvý riadok ako hlavičky. Tým by sa prvý riadok transponovaných údajov stal hlavičkami.
Prejdite na položku Súbor -> Zatvoriť a načítať. Tým sa zatvorí okno Power Editor a vytvorí sa nový hárok, ktorý obsahuje transponované údaje.

Všimnite si toho, pretože pretože ľavá horná bunka našej množiny údajov bola prázdna, dostane všeobecný názov Stĺpec1 (ako je uvedené nižšie). Túto bunku môžete odstrániť z transponovaných údajov.