Funkcia transponovania v Exceli

Z času na čas môže mať používateľ Excelu za úlohu zmeniť rozsah údajov, ktoré majú horizontálnu štruktúru, na vertikálnu. Tento proces sa nazýva transpozícia. Toto slovo je pre väčšinu ľudí nové, pretože pri bežnej práci s PC túto operáciu používať nemusíte. Kto však musí pracovať s veľkým množstvom dát, musí vedieť, ako na to. Dnes budeme hovoriť podrobnejšie o tom, ako to vykonať, s akou funkciou, a tiež sa podrobne pozrieť na niektoré ďalšie metódy.

Funkcia TRANSPOSE – transponuje rozsahy buniek v Exceli

Jednou z najzaujímavejších a najfunkčnejších metód transpozície tabuliek v Exceli je funkcia TRANSP. S jeho pomocou môžete zmeniť horizontálny rozsah údajov na vertikálny alebo vykonať opačnú operáciu. Pozrime sa, ako sa s tým dá pracovať.

Syntax funkcie

Syntax tejto funkcie je neuveriteľne jednoduchá: TRANSPOSE (pole). To znamená, že musíme použiť iba jeden argument, ktorým je súbor údajov, ktorý je potrebné previesť na horizontálne alebo vertikálne zobrazenie v závislosti od toho, čo bolo pôvodne.

Transponovanie zvislých rozsahov buniek (stĺpcov)

Predpokladajme, že máme stĺpec s rozsahom B2:B6. Môžu obsahovať hotové hodnoty aj vzorce, ktoré vracajú výsledky do týchto buniek. Pre nás to nie je až také dôležité, transpozícia je možná v oboch prípadoch. Po použití tejto funkcie bude dĺžka riadka rovnaká ako pôvodná dĺžka stĺpca rozsahu.

Postupnosť krokov na použitie tohto vzorca je nasledovná:

1. Vyberte riadok. V našom prípade má dĺžku päť buniek.
2. Potom presuňte kurzor na riadok vzorcov a zadajte tam vzorec =TRANSP(B2:B6).
3. Stlačte kombináciu klávesov Ctrl + Shift + Enter.

Prirodzene, vo vašom prípade musíte špecifikovať rozsah, ktorý je typický pre váš stôl.

Transponovanie vodorovných rozsahov buniek (riadkov)

V zásade je mechanizmus účinku takmer rovnaký ako v predchádzajúcom odseku. Predpokladajme, že máme reťazec so začiatočnými a koncovými súradnicami B10:F10. Môže tiež obsahovať priamo hodnoty aj vzorce. Urobme si z neho stĺpec, ktorý bude mať rovnaké rozmery ako pôvodný riadok. Postupnosť akcií je nasledovná:

1. Vyberte tento stĺpec pomocou myši. Môžete tiež použiť klávesy na klávesnici Ctrl a šípku nadol po kliknutí na najvrchnejšiu bunku tohto stĺpca.
2. Potom napíšeme vzorec =TRANSP(B10:F10) v riadku vzorcov.
3. Zapíšeme ho ako maticový vzorec pomocou kombinácie kláves Ctrl + Shift + Enter.

Transponovanie pomocou špeciálnej pasty

Ďalšou možnou možnosťou transpozície je použitie funkcie Prilepiť špeciálne. Toto už nie je operátor na použitie vo vzorcoch, ale je to tiež jeden z populárnych spôsobov premeny stĺpcov na riadky a naopak.

Táto možnosť je na karte Domov. Ak chcete k nemu pristupovať, musíte nájsť skupinu „Schránka“ a tam nájsť tlačidlo „Prilepiť“. Potom otvorte ponuku, ktorá sa nachádza pod touto možnosťou, a vyberte položku „Transpose“. Predtým musíte vybrať rozsah, ktorý chcete vybrať. Vo výsledku dostaneme rovnaký rozsah, len sa zrkadlí opak.

3 spôsoby, ako transponovať tabuľku v Exceli

Ale v skutočnosti existuje oveľa viac spôsobov, ako zmeniť stĺpce na riadky a naopak. Poďme si popísať 3 metódy, ktorými môžeme transponovať tabuľku v Exceli. O dvoch z nich sme hovorili vyššie, ale uvedieme niekoľko ďalších príkladov, aby ste získali lepšiu predstavu o tom, ako tento postup vykonať.

Metóda 1: Paste Special

Táto metóda je najjednoduchšia. Stačí stlačiť niekoľko tlačidiel a používateľ dostane transponovanú verziu tabuľky. Pre lepšiu názornosť uvedieme malý príklad. Predpokladajme, že máme tabuľku obsahujúcu informácie o tom, koľko produktov je momentálne na sklade, ako aj ich celkovú cenu. Samotný stôl vyzerá takto.

Vidíme, že máme hlavičku a stĺpec s číslami produktov. V našom príklade hlavička obsahuje informácie o tom, aký produkt, koľko stojí, koľko je na sklade a aké sú celkové náklady na všetky produkty súvisiace s touto položkou, ktoré sú na sklade. Náklady dostaneme podľa vzorca, kde sa náklady vynásobia množstvom. Aby bol príklad vizuálnejší, urobme hlavičku zelenou.

Našou úlohou je zabezpečiť, aby informácie obsiahnuté v tabuľke boli umiestnené horizontálne. To znamená, že zo stĺpcov sa stanú riadky. Postupnosť akcií v našom prípade bude nasledovná:

1. Vyberte rozsah údajov, ktoré potrebujeme otočiť. Potom tieto údaje skopírujeme.
2. Umiestnite kurzor kdekoľvek na hárku. Potom kliknite na pravé tlačidlo myši a otvorte kontextové menu.
3. Potom kliknite na tlačidlo „Prilepiť špeciálne“.

Po dokončení týchto krokov musíte kliknúť na tlačidlo „Transpose“. Radšej začiarknite políčko vedľa tejto položky. Ostatné nastavenia nemeníme a potom kliknite na tlačidlo „OK“.

Po vykonaní týchto krokov nám zostane rovnaká tabuľka, len sú jej riadky a stĺpce inak usporiadané. Všimnite si tiež, že bunky obsahujúce rovnaké informácie sú zvýraznené zelenou farbou. Otázka: čo sa stalo so vzorcami, ktoré boli v pôvodnom rozsahu? Ich umiestnenie sa zmenilo, ale oni sami zostali. Adresy buniek sa jednoducho zmenili na tie, ktoré vznikli po transpozícii.

Takmer rovnaké akcie je potrebné vykonať na transponovanie hodnôt, nie vzorcov. V tomto prípade musíte tiež použiť ponuku Prilepiť špeciálne, ale predtým vyberte rozsah údajov obsahujúcich hodnoty. Vidíme, že okno Prilepiť špeciálne možno vyvolať dvoma spôsobmi: prostredníctvom špeciálneho menu na páse s nástrojmi alebo kontextového menu.

Metóda 2. Funkcia TRANSP v Exceli

V skutočnosti sa táto metóda už nepoužíva tak aktívne ako na samom začiatku vzniku tohto tabuľkového procesora. Táto metóda je totiž oveľa zložitejšia ako použitie špeciálnej pasty. Svoje využitie však nachádza pri automatizácii transpozície tabuliek.

Aj táto funkcia je v Exceli, takže je nevyhnutné o nej vedieť, aj keď sa už takmer nepoužíva. Dávnejšie sme zvažovali postup, ako s tým pracovať. Teraz tieto poznatky doplníme ďalším príkladom.

1. Najprv musíme vybrať rozsah údajov, ktorý sa použije na transpozíciu tabuľky. Stačí si naopak vybrať oblasť. Napríklad v tomto príklade máme 4 stĺpce a 6 riadkov. Preto je potrebné vybrať oblasť s opačnými charakteristikami: 6 stĺpcov a 4 riadky. Obrázok to ukazuje veľmi dobre.
2. Potom túto bunku okamžite začneme vyplňovať. Je dôležité, aby ste náhodne neodstránili výber. Preto musíte vzorec zadať priamo v riadku vzorcov.
3. Potom stlačte kombináciu klávesov Ctrl + Shift + Enter. Pamätajte, že ide o maticový vzorec, pretože pracujeme s veľkou množinou údajov naraz, ktoré sa prenesú do inej veľkej množiny buniek.

Po zadaní údajov stlačíme kláves Enter, po ktorom dostaneme nasledujúci výsledok.

Vidíme, že vzorec nebol prenesený do novej tabuľky. Stratilo sa aj formátovanie. Poeto

To všetko bude musieť byť vykonané ručne. Okrem toho nezabudnite, že táto tabuľka súvisí s tou pôvodnou. Preto akonáhle dôjde k zmene niektorých informácií v pôvodnom rozsahu, tieto úpravy sa automaticky vykonajú v transponovanej tabuľke.

Preto je táto metóda vhodná v prípadoch, keď potrebujete zabezpečiť prepojenie transponovanej tabuľky s pôvodnou. Ak použijete špeciálnu vložku, táto možnosť už nebude.

súhrnná tabuľka

Ide o zásadne novú metódu, ktorá umožňuje nielen transponovať tabuľku, ale aj vykonávať veľké množstvo akcií. Je pravda, že mechanizmus transpozície bude trochu odlišný v porovnaní s predchádzajúcimi metódami. Postupnosť akcií je nasledovná:

1. Urobme si kontingenčnú tabuľku. Ak to chcete urobiť, musíte vybrať tabuľku, ktorú musíme transponovať. Potom prejdite na položku „Vložiť“ a vyhľadajte tam „Kontingenčná tabuľka“. Zobrazí sa dialógové okno podobné tomu na tejto snímke obrazovky.
2. Tu môžete priradiť rozsah, z ktorého sa bude vykonávať, ako aj vykonať množstvo ďalších nastavení. Nás teraz zaujíma predovšetkým miesto kontingenčnej tabuľky – na novom hárku.
3. Potom sa automaticky vytvorí rozloženie kontingenčnej tabuľky. Je potrebné v ňom označiť tie položky, ktoré budeme používať, a potom ich treba presunúť na správne miesto. V našom prípade musíme presunúť položku „Produkt“ do „Názvy stĺpcov“ a „Cena za kus“ do „Hodnoty“.
4. Potom sa konečne vytvorí kontingenčná tabuľka. Bonusom navyše je automatický výpočet výslednej hodnoty.
5. Môžete zmeniť aj ďalšie nastavenia. Napríklad zrušte začiarknutie položky „Cena za kus“ a začiarknite položku „Celkové náklady“. V dôsledku toho budeme mať tabuľku obsahujúcu informácie o tom, koľko produkty stoja. Táto metóda transpozície je oveľa funkčnejšia ako ostatné. Poďme si popísať niektoré z výhod kontingenčných tabuliek:

1. automatizácia. Pomocou kontingenčných tabuliek môžete automaticky sumarizovať údaje, ako aj ľubovoľne meniť polohu stĺpcov a stĺpcov. Ak to chcete urobiť, nemusíte vykonávať žiadne ďalšie kroky.
2. Interaktivita. Používateľ môže meniť štruktúru informácií toľkokrát, koľkokrát potrebuje na plnenie svojich úloh. Môžete napríklad ľubovoľným spôsobom meniť poradie stĺpcov, ako aj zoskupovať údaje. Toto je možné vykonať toľkokrát, koľkokrát používateľ potrebuje. A to doslova trvá menej ako minútu.
3. Jednoduché formátovanie údajov. Je veľmi jednoduché usporiadať kontingenčný stôl tak, ako chce človek. Ak to chcete urobiť, stačí urobiť niekoľko kliknutí myšou.
4. Získavanie hodnôt. Obrovské množstvo vzorcov, ktoré sa používajú na vytváranie správ, sa nachádza v priamej dostupnosti osoby a je ľahké ich integrovať do kontingenčnej tabuľky. Sú to údaje ako súčet, získanie aritmetického priemeru, určenie počtu buniek, násobenie, nájdenie najväčších a najmenších hodnôt v špecifikovanej vzorke.
5. Schopnosť vytvárať súhrnné grafy. Ak sa kontingenčné tabuľky prepočítajú, ich priradené grafy sa automaticky aktualizujú. Je možné vytvoriť toľko grafov, koľko potrebujete. Všetky je možné zmeniť pre konkrétnu úlohu a nebudú navzájom prepojené.
6. Schopnosť filtrovať údaje.
7. Je možné vytvoriť kontingenčnú tabuľku založenú na viac ako jednej množine zdrojových informácií. Preto bude ich funkčnosť ešte väčšia.

Je pravda, že pri používaní kontingenčných tabuliek je potrebné vziať do úvahy nasledujúce obmedzenia:

1. Nie všetky informácie možno použiť na generovanie kontingenčných tabuliek. Predtým, ako sa môžu použiť na tento účel, musia byť bunky normalizované. Jednoducho povedané, urobte to správne. Povinné požiadavky: prítomnosť riadku hlavičky, plnosť všetkých riadkov, rovnosť formátov údajov.
2. Údaje sa aktualizujú poloautomaticky. Ak chcete získať nové informácie v kontingenčnej tabuľke, musíte kliknúť na špeciálne tlačidlo.
3. Kontingenčné stoly zaberajú veľa miesta. To môže viesť k určitému narušeniu činnosti počítača. Z tohto dôvodu bude tiež ťažké odoslať súbor e-mailom.

Po vytvorení kontingenčnej tabuľky tiež používateľ nemá možnosť pridávať nové informácie.