Funkcie prepojenia a transponovania v Exceli sa navzájom vylučujú. To znamená, že transponované bunky nebudú fungovať ako odkazy na vašom hárku. Inými slovami, akékoľvek zmeny, ktoré vykonáte v pôvodných bunkách, sa neprejavia v transponovanej kópii.
Vaše projekty však často vyžadujú, aby ste bunky/stĺpec transponovali aj prepojili. Existuje teda spôsob, ako využiť obe funkcie? Samozrejme existuje a my vám predstavíme štyri rôzne spôsoby, ako to urobiť.
Dá sa s istotou povedať, že tieto triky sú súčasťou stredne pokročilých znalostí Excelu, ale ak budete postupovať podľa krokov na T, nedôjde k žiadnym pokusom a omylom, aj keď ste úplný nováčik.
Problém vkladania
Na účely tohto článku predpokladajme, že chcete transponovať stĺpce do rovnakého hárka. Tak čo robíš? Vyberte stĺpce, stlačte Ctrl + C (Cmd + C na Macu) a vyberte cieľ prilepenia. Potom kliknite na možnosti Prilepiť, vyberte Prilepiť špeciálne a začiarknite políčko pred Transponovať.
Ale hneď ako začiarknete políčko, Paste Link sa zobrazí sivo. Našťastie existuje niekoľko vzorcov a trikov, ktoré vám pomôžu tento problém vyriešiť. Poďme sa na ne pozrieť.
TRANSPOSE – Array Formula
Hlavnou výhodou tohto vzorca je, že nemusíte bunky ručne presúvať myšou. Má to však určité nevýhody. Napríklad veľkosť sa nedá ľahko zmeniť, čo znamená, že ak sa zmení rozsah zdrojových buniek, musíte vzorec použiť znova.
Podobné problémy sa však týkajú aj iných vzorcov poľa a tento vám pomôže pomerne rýchlo vyriešiť problém transponovania prepojenia.
Krok 1
Skopírujte bunky a kliknite na ľavú hornú bunku v oblasti, do ktorej chcete bunky prilepiť. Stlačením klávesov Ctrl + Alt + V otvorte okno Prilepiť špeciálne. Môžete to urobiť aj z panela nástrojov Excel.
Krok 2
Po otvorení okna začiarknite položku Formáty v časti Prilepiť, vpravo dole vyberte možnosť Transponovať a kliknite na tlačidlo OK. Táto akcia transponuje iba formátovanie, nie hodnoty a existujú dva dôvody, prečo to musíte urobiť. Najprv budete poznať rozsah transponovaných buniek. Po druhé, zachováte pôvodný formát buniek.
Krok 3
Je potrebné vybrať celú oblasť prilepenia a môžete to urobiť po prilepení formátov. Teraz napíšte =TRANSPOSE („Pôvodný rozsah“) a stlačte Ctrl + Shift + Enter.
Poznámka: Je dôležité stlačiť Enter spolu s Ctrl a Shift. V opačnom prípade program nerozpozná príkaz správne a automaticky vygeneruje zložené zátvorky.
Link and Transpose – Manual Method
Áno, Excel je o automatizácii a používaní funkcií na uľahčenie manipulácie s bunkami a stĺpcami. Ak však máte čo do činenia s pomerne malým rozsahom buniek, ručné prepojenie a transpozícia je často najrýchlejším riešením. Je pravda, že ak nie ste dostatočne opatrní, existuje priestor na chyby.
Krok 1
Vyberte bunky a skopírujte/prilepte ich pomocou možnosti Prilepiť špeciálne. Tentoraz nezačiarknete políčko pred Transpose a ponecháte možnosti v časti Prilepiť ako predvolené.
Krok 2
Kliknite na tlačidlo Prilepiť prepojenie vľavo dole a vaše údaje sa prilepia vo forme odkazov.
Krok 3
Tu prichádza tá ťažšia časť. Bunky do novej oblasti musíte manuálne potiahnuť a potom pustiť. Zároveň si treba dať pozor na výmenu riadkov a stĺpcov.
OFFSET vzorec
Toto je jeden z najsilnejších nástrojov na prilepenie buniek, ich prepojenie a transpozíciu. Ak ste v Exceli nováčikom, nemusí to byť jednoduché, preto sa pokúsime urobiť kroky čo najjasnejšie.
Krok 1
Musíte si pripraviť čísla vľavo a hore. Napríklad, ak sú tri riadky, použijete 0-2, a ak sú tam dva stĺpce, použijete 0-1. Metóda je celkový počet riadkov a stĺpcov mínus 1.
Krok 2
Ďalej musíte nájsť a definovať základnú bunku. Táto bunka by mala zostať nedotknutá pri kopírovaní/vkladaní, a preto pre bunku používate špeciálne symboly. Povedzme, že základná bunka je B2: na zvýraznenie tejto bunky budete musieť vložiť znak dolára. Vo vzorci by to malo vyzerať takto: =OFFSET($B$2.
Krok 3
Teraz musíte definovať vzdialenosť (v riadkoch) medzi základnou bunkou a cieľovou bunkou. Toto číslo sa musí zvýšiť, keď vzorec posuniete doprava. Z tohto dôvodu by pred stĺpcom funkcie nemal byť žiadny znak dolára. Namiesto toho sa prvý riadok fixuje znakom dolára.
Napríklad, ak je funkcia v stĺpci F, funkcia by mala vyzerať takto: =OFFSET($B$2, F$1.
Krok 4
Rovnako ako riadky, aj stĺpce sa musia po prepojení a transponovaní zväčšiť. Znak dolára môžete použiť aj na opravu jedného stĺpca, ale umožníte zväčšenie riadkov. Aby to bolo jasné, najlepšie je odkázať na príklad, ktorý môže vyzerať takto: =OFFSET($B$2, F$1, $E2).
Ako Excel v Exceli
Okrem danej metódy existujú aj nástroje tretích strán, ktoré môžete použiť na prepojenie a transpozíciu. A ak dané metódy neprinášajú uspokojivé výsledky, môže byť najlepšie použiť jeden takýto nástroj.
Použili ste jednu z týchto aplikácií na transpozíciu/prepojenie na vykonanie tejto operácie v Exceli? Boli ste spokojní s výsledkom? Podeľte sa o svoje skúsenosti v sekcii komentárov nižšie.