Funkce Transpose v Excelu

Čas od času může mít uživatel Excelu za úkol převést rozsah dat, která mají horizontální strukturu, na vertikální. Tento proces se nazývá transpozice. Toto slovo je pro většinu lidí nové, protože při běžné práci na PC tuto operaci používat nemusíte. Kdo však musí pracovat s velkým množstvím dat, musí vědět, jak na to. Dnes si povíme podrobněji, jak to provést, s jakou funkcí, a také se podrobně podíváme na některé další metody.

Funkce TRANSPOSE – transponuje rozsahy buněk v Excelu

Jednou z nejzajímavějších a nejfunkčnějších metod transpozice tabulek v Excelu je funkce TRANSP. S jeho pomocí můžete změnit horizontální rozsah dat na vertikální nebo provést opačnou operaci. Pojďme se podívat, jak s tím pracovat.

Syntaxe funkce

Syntaxe této funkce je neuvěřitelně jednoduchá: TRANSPOSE(pole). To znamená, že musíme použít pouze jeden argument, což je soubor dat, který je třeba převést na horizontální nebo vertikální pohled podle toho, jaký byl původně.

Transponování vertikálních rozsahů buněk (sloupců)

Předpokládejme, že máme sloupec s rozsahem B2:B6. Mohou obsahovat jak hotové hodnoty, tak vzorce, které vracejí výsledky do těchto buněk. Pro nás to není tak důležité, transpozice je možná v obou případech. Po použití této funkce bude délka řádku stejná jako původní délka sloupce rozsahu.

Posloupnost kroků pro použití tohoto vzorce je následující:

1. Vyberte řádek. V našem případě má délku pět buněk.
2. Poté přesuňte kurzor na řádek vzorců a zadejte tam vzorec =TRANSP(B2:B6).
3. Stiskněte kombinaci kláves Ctrl + Shift + Enter.

Samozřejmě ve vašem případě musíte určit rozsah, který je typický pro váš stůl.

Transponování vodorovných rozsahů buněk (řádků)

V zásadě je mechanismus účinku téměř stejný jako v předchozím odstavci. Předpokládejme, že máme řetězec s počátečními a koncovými souřadnicemi B10:F10. Může také obsahovat jak přímo hodnoty, tak vzorce. Udělejme z něj sloupec, který bude mít stejné rozměry jako původní řádek. Posloupnost akcí je následující:

1. Vyberte tento sloupec pomocí myši. Můžete také použít klávesy na klávesnici Ctrl a šipku dolů po kliknutí na nejvyšší buňku tohoto sloupce.
2. Poté napíšeme vzorec =TRANSP(B10:F10) ve vzorci bar.
3. Zapíšeme jej jako maticový vzorec pomocí kombinace kláves Ctrl + Shift + Enter.

Transponování pomocí speciální pasty

Další možnou možností transpozice je použití funkce Vložit jinak. Toto již není operátor pro použití ve vzorcích, ale je to také jedna z oblíbených metod pro přeměnu sloupců na řádky a naopak.

Tato možnost je na kartě Domů. Chcete-li se k němu dostat, musíte najít skupinu „Schránka“ a tam najít tlačítko „Vložit“. Poté otevřete nabídku, která se nachází pod touto možností, a vyberte položku „Transpose“. Předtím musíte vybrat rozsah, který chcete vybrat. Ve výsledku dostaneme stejný rozsah, jen se zrcadlí opak.

3 způsoby, jak transponovat tabulku v Excelu

Ale ve skutečnosti existuje mnohem více způsobů, jak přeměnit sloupce na řádky a naopak. Pojďme si popsat 3 metody, kterými můžeme transponovat tabulku v Excelu. O dvou z nich jsme hovořili výše, ale uvedeme několik dalších příkladů, abyste získali lepší představu o tom, jak tento postup provést.

Metoda 1: Vložit speciální

Tato metoda je nejjednodušší. Stačí stisknout několik tlačítek a uživatel obdrží transponovanou verzi tabulky. Pro větší názornost uveďme malý příklad. Předpokládejme, že máme tabulku obsahující informace o tom, kolik produktů je aktuálně skladem, a také kolik stojí celkem. Samotný stůl vypadá takto.

Vidíme, že máme záhlaví a sloupec s čísly produktů. V našem příkladu záhlaví obsahuje informace o tom, jaký produkt, kolik stojí, kolik je skladem a jaké jsou celkové náklady na všechny produkty související s touto položkou, které jsou skladem. Náklady získáme podle vzorce, kde se náklady vynásobí množstvím. Aby byl příklad vizuálnější, udělejme záhlaví zeleným.

Naším úkolem je zajistit, aby informace obsažené v tabulce byly umístěny vodorovně. Tedy tak, aby se ze sloupců staly řádky. Posloupnost akcí v našem případě bude následující:

1. Vyberte rozsah dat, který potřebujeme otočit. Poté tato data zkopírujeme.
2. Umístěte kurzor kamkoli na list. Poté klikněte pravým tlačítkem myši a otevřete kontextové menu.
3. Poté klikněte na tlačítko „Vložit jinak“.

Po dokončení těchto kroků musíte kliknout na tlačítko „Transpose“. Raději zaškrtněte políčko vedle této položky. Ostatní nastavení neměníme a poté klikněte na tlačítko „OK“.

Po provedení těchto kroků nám zůstane stejná tabulka, jen jsou její řádky a sloupce jinak uspořádány. Všimněte si také, že buňky obsahující stejné informace jsou zvýrazněny zeleně. Otázka: co se stalo se vzorci, které byly v původním rozsahu? Jejich umístění se změnilo, ale oni sami zůstali. Adresy buněk se jednoduše změnily na ty, které vznikly po transpozici.

Téměř stejné akce je třeba provést pro transpozici hodnot, nikoli vzorců. V tomto případě je také nutné použít nabídku Vložit jinak, ale předtím vyberte rozsah dat obsahující hodnoty. Vidíme, že okno Vložit jinak lze vyvolat dvěma způsoby: prostřednictvím speciální nabídky na pásu karet nebo kontextové nabídky.

Metoda 2. Funkce TRANSP v Excelu

Ve skutečnosti se tato metoda již nepoužívá tak aktivně jako na samém počátku vzniku tohoto tabulkového procesoru. Tato metoda je totiž mnohem složitější než použití Paste Special. Své využití však nachází při automatizaci transpozice tabulek.

Také tato funkce je v Excelu, takže je nutné o ní vědět, i když se již téměř nepoužívá. Dříve jsme zvažovali postup, jak s tím pracovat. Nyní tyto poznatky doplníme dalším příkladem.

1. Nejprve musíme vybrat datový rozsah, který bude použit k transpozici tabulky. Stačí naopak vybrat oblast. Například v tomto příkladu máme 4 sloupce a 6 řádků. Proto je nutné vybrat oblast s opačnými charakteristikami: 6 sloupců a 4 řádky. Obrázek to ukazuje velmi dobře.
2. Poté okamžitě začneme vyplňovat tuto buňku. Je důležité, abyste náhodně neodstranili výběr. Proto musíte vzorec zadat přímo v řádku vzorců.
3. Poté stiskněte kombinaci kláves Ctrl + Shift + Enter. Pamatujte, že se jedná o maticový vzorec, protože pracujeme s velkou sadou dat najednou, která se přenesou do jiné velké sady buněk.

Po zadání dat stiskneme klávesu Enter, po které dostaneme následující výsledek.

Vidíme, že vzorec nebyl přenesen do nové tabulky. Ztratilo se také formátování. Poeto

To vše bude muset být provedeno ručně. Navíc nezapomeňte, že tato tabulka souvisí s tou původní. Jakmile se tedy některá informace změní v původním rozsahu, tyto úpravy se automaticky provedou v transponované tabulce.

Proto se tato metoda dobře hodí v případech, kdy potřebujete zajistit propojení transponované tabulky s tou původní. Pokud použijete speciální vložku, tato možnost již nebude.

souhrnná tabulka

Jedná se o zásadně novou metodu, která umožňuje nejen transponovat tabulku, ale také provádět obrovské množství akcí. Je pravda, že transpoziční mechanismus bude ve srovnání s předchozími metodami poněkud odlišný. Posloupnost akcí je následující:

1. Udělejme kontingenční tabulku. Chcete-li to provést, musíte vybrat tabulku, kterou potřebujeme transponovat. Poté přejděte na položku „Vložit“ a vyhledejte tam „Kontingenční tabulka“. Zobrazí se dialogové okno podobné tomu na tomto snímku obrazovky.
2. Zde můžete znovu přiřadit rozsah, ze kterého se bude provádět, a také provést řadu dalších nastavení. Nás teď zajímá především místo kontingenční tabulky – na novém listu.
3. Poté se automaticky vytvoří rozvržení kontingenční tabulky. Je nutné v něm označit ty položky, které budeme používat, a ty je pak třeba přesunout na správné místo. V našem případě musíme přesunout položku „Produkt“ do „Názvy sloupců“ a „Cena za kus“ do „Hodnoty“.
4. Poté se definitivně vytvoří kontingenční tabulka. Bonusem navíc je automatický výpočet konečné hodnoty.
5. Můžete změnit i další nastavení. Zrušte například zaškrtnutí položky „Cena za kus“ a zaškrtněte položku „Celkové náklady“. V důsledku toho budeme mít tabulku obsahující informace o tom, kolik produkty stojí. Tato metoda transpozice je mnohem funkčnější než ostatní. Pojďme si popsat některé výhody kontingenčních tabulek:

1. Automatizace. Pomocí kontingenčních tabulek můžete automaticky sumarizovat data a také libovolně měnit pozici sloupců a sloupců. Chcete-li to provést, nemusíte provádět žádné další kroky.
2. Interaktivita. Uživatel může měnit strukturu informací tolikrát, kolikrát potřebuje k plnění svých úkolů. Můžete například libovolným způsobem změnit pořadí sloupců a také seskupovat data. To lze provést tolikrát, kolikrát uživatel potřebuje. A to doslova trvá méně než minutu.
3. Snadné formátování dat. Je velmi snadné uspořádat kontingenční stůl tak, jak chce člověk. Chcete-li to provést, stačí provést několik kliknutí myší.
4. Získávání hodnot. Převážné množství vzorců, které se používají k vytváření sestav, se nachází v přímé dostupnosti osoby a lze je snadno integrovat do kontingenční tabulky. Jedná se o data, jako je sčítání, získání aritmetického průměru, určení počtu buněk, násobení, nalezení největších a nejmenších hodnot v určeném vzorku.
5. Schopnost vytvářet souhrnné grafy. Pokud jsou kontingenční tabulky přepočítány, jejich přidružené grafy se automaticky aktualizují. Je možné vytvořit tolik grafů, kolik potřebujete. Všechny lze změnit pro konkrétní úkol a nebudou vzájemně propojeny.
6. Schopnost filtrovat data.
7. Je možné sestavit kontingenční tabulku na základě více než jedné sady zdrojových informací. Proto se jejich funkčnost ještě zvýší.

Je pravda, že při používání kontingenčních tabulek je třeba vzít v úvahu následující omezení:

1. Ne všechny informace lze použít ke generování kontingenčních tabulek. Než mohou být použity pro tento účel, musí být buňky normalizovány. Jednoduše řečeno, udělejte to správně. Povinné požadavky: přítomnost řádku záhlaví, plnost všech řádků, rovnost datových formátů.
2. Data se aktualizují poloautomaticky. Chcete-li získat nové informace v kontingenční tabulce, musíte kliknout na speciální tlačítko.
3. Kontingenční tabulky zabírají hodně místa. To může vést k určitému narušení počítače. Z tohoto důvodu bude také obtížné odeslat soubor e-mailem.

Po vytvoření kontingenční tabulky také uživatel nemá možnost přidávat nové informace.