Funkce propojení a transpozice v Excelu se vzájemně vylučují. To znamená, že transponované buňky nebudou fungovat jako odkazy na vašem listu. Jinými slovy, jakékoli změny, které provedete v původních buňkách, se v transponované kopii neprojeví.
Vaše projekty však často vyžadují, abyste buňky/sloupec transponovali i propojili. Existuje tedy způsob, jak využít obě funkce? Samozřejmě existuje a my vám představíme čtyři různé způsoby, jak toho dosáhnout.
Dá se s jistotou říci, že tyto triky jsou součástí středně pokročilé znalosti Excelu, ale pokud budete postupovat podle kroků k T, nedojde k žádnému pokusu a omylu, i když jste úplný nováček.
Problém vkládání
Pro účely tohoto článku předpokládejme, že chcete transponovat sloupce do stejného listu. Takže, co děláš? Vyberte sloupce, stiskněte Ctrl + C (Cmd + C na Macu) a zvolte cíl vložení. Poté klikněte na možnosti Vložit, vyberte Vložit jinak a zaškrtněte políčko před Transpose.
Ale jakmile zaškrtnete políčko, Paste Link se zobrazí šedě. Naštěstí existuje několik vzorců a triků, které vám pomohou tento problém vyřešit. Pojďme se na ně podívat.
TRANSPOSE – Array Formule
Hlavní výhodou tohoto vzorce je, že nemusíte buňky ručně přetahovat. To však přináší určité nevýhody. Například není snadné změnit velikost, což znamená, že pokud se změní rozsah zdrojových buněk, musíte vzorec použít znovu.
Podobné problémy se však týkají jiných maticových vzorců a tento vám pomůže vyřešit problém transponování propojení poměrně rychle.
Krok 1
Zkopírujte buňky a klikněte na levou horní buňku v oblasti, do které chcete buňky vložit. Stisknutím Ctrl + Alt + V otevřete okno Vložit jinak. Můžete to udělat také z panelu nástrojů Excel.
Krok 2
Jakmile otevřete okno, zaškrtněte Formáty v části Vložit, vpravo dole zvolte Transponovat a klikněte na OK. Tato akce pouze transponuje formátování, nikoli hodnoty, a existují dva důvody, proč to musíte udělat. Nejprve budete znát rozsah transponovaných buněk. Za druhé, zachováte původní formát buněk.
Krok 3
Je třeba vybrat celou oblast vkládání a můžete to udělat po vložení formátů. Nyní zadejte =TRANSPOSE („původní rozsah“) a stiskněte Ctrl + Shift + Enter.
Poznámka: Je důležité stisknout Enter společně s Ctrl a Shift. V opačném případě program nerozpozná příkaz správně a automaticky vygeneruje složené závorky.
Link and Transpose – Manual Method
Ano, Excel je o automatizaci a používání funkcí pro snazší manipulaci s buňkami a sloupci. Pokud však máte co do činění s poměrně malým rozsahem buněk, ruční propojení a transpozice je často nejrychlejším řešením. Je pravda, že pokud nejste dostatečně opatrní, existuje prostor pro chyby.
Krok 1
Vyberte buňky a zkopírujte/vložte je pomocí možnosti Vložit jinak. Tentokrát nezaškrtnete políčko před Transpose a ponecháte možnosti pod Vložit jako výchozí.
Krok 2
Klikněte na tlačítko Vložit odkaz vlevo dole a vaše data budou vložena ve formě odkazů.
Krok 3
Tady přichází ta těžší část. Je třeba ručně přetáhnout a poté přetáhnout buňky do nové oblasti. Zároveň je třeba dávat pozor na výměnu řádků a sloupců.
OFFSET vzorec
Jedná se o jeden z nejúčinnějších nástrojů pro vkládání buněk, jejich propojení a transpozici. Pokud však s Excelem začínáte, nemusí to být snadné, proto se pokusíme kroky co nejvíce objasnit.
Krok 1
Musíte si připravit čísla vlevo a nahoře. Pokud jsou například tři řádky, použijete 0-2, a pokud jsou dva sloupce, použijete 0-1. Metoda je celkový počet řádků a sloupců mínus 1.
Krok 2
Dále musíte najít a definovat základní buňku. Tato buňka by při kopírování/vkládání měla zůstat nedotčená, a proto pro buňku používáte speciální symboly. Řekněme, že základní buňka je B2: k označení této buňky budete muset vložit znak dolaru. Ve vzorci by to mělo vypadat takto: =OFFSET($B$2.
Krok 3
Nyní musíte definovat vzdálenost (v řádcích) mezi základní buňkou a cílovou buňkou. Toto číslo se musí zvýšit, když vzorec posunete doprava. Z tohoto důvodu by před sloupcem funkce neměl být žádný znak dolaru. Místo toho je první řádek fixován znakem dolaru.
Pokud je funkce například ve sloupci F, měla by funkce vypadat takto: =OFFSET($B$2, F$1.
Krok 4
Stejně jako řádky, i sloupce se musí po propojení a transpozici zvětšit. Znak dolaru také můžete použít k opravě jednoho sloupce, ale umožnit nárůst řádků. Aby to bylo jasné, je nejlepší odkázat na příklad, který může vypadat takto: =OFFSET($B$2, F$1, $E2).
Jak excelovat v Excelu
Kromě dané metody existují také nástroje třetích stran, které můžete použít k propojení a transpozici. A pokud dané metody nepřinášejí uspokojivé výsledky, může být nejlepší použít jeden takový nástroj.
Použili jste některou z těchto aplikací pro transpozici/propojení k provedení této operace v aplikaci Excel? Byli jste s výsledkem spokojeni? Podělte se o své zkušenosti v sekci komentářů níže.