Obsah
Včera na maratóne 30 funkcií Excelu za 30 dní pomocou funkcie sme spočítali počet stĺpcov v rozsahu STĹPCE (NUMBERCOLUMN) a teraz je čas na niečo žiadanejšie.
13. deň maratónu sa budeme venovať štúdiu funkcie TRANSPOZÍCIA (TRANSP). Pomocou tejto funkcie môžete otáčať dáta tak, že vertikálne oblasti zmeníte na horizontálne a naopak. Máte takúto potrebu? Môžete to urobiť pomocou špeciálnej vložky? Zvládnu to aj iné funkcie?
Poďme teda k informáciám a príkladom o funkcii TRANSPOZÍCIA (TRANSP). Ak máte ďalšie informácie alebo príklady, podeľte sa o ne v komentároch.
Funkcia 13: TRANSPOSE
Funkcie TRANSPOZÍCIA (TRANSPOSE) vráti horizontálny rozsah buniek ako vertikálny rozsah alebo naopak.
Ako sa dá použiť funkcia TRANSPOSE?
Funkcie TRANSPOZÍCIA (TRANSP) môže zmeniť orientáciu údajov, ako aj pracovať v spojení s ďalšími funkciami:
- Zmeňte horizontálne rozloženie údajov na vertikálne.
- Ukážte najlepšie celkové mzdy za posledné roky.
Ak chcete zmeniť orientáciu údajov bez vytvárania odkazov na pôvodné údaje:
- Použitie pasta Špeciálna (špeciálna pasta) > transponovať (Transponovať).
Syntax TRANSPOSE (TRANSP)
Funkcie TRANSPOZÍCIA (TRANSPOSE) má nasledujúcu syntax:
TRANSPOSE(array)
ТРАНСП(массив)
- rad (pole) je pole alebo rozsah buniek, ktoré sa majú transponovať.
Pasce TRANSPOSE (TRANSPOSE)
- Funkcie TRANSPOZÍCIA (TRANSPOSE) je potrebné zadať ako vzorec poľa stlačením Ctrl + Shift + Enter.
- Rozsah, ktorý bude výsledkom transformácie funkciou TRANSPOZÍCIA (TRANSPOSE) musí mať rovnaký počet riadkov a stĺpcov, ako má pôvodný rozsah stĺpcov a riadkov.
Príklad 1: Premena horizontálnych údajov na vertikálne údaje
Ak sú údaje v hárku programu Excel vodorovné, môžete použiť funkciu TRANSPOZÍCIA (TRANSPOSE), aby ste ich previedli do zvislej polohy, ale na iné miesto na hárku. Napríklad v konečnej tabuľke benchmarkov by bolo pohodlnejšie vertikálne usporiadanie. Pomocou funkcie TRANSPOZÍCIA (TRANSPOSE), môžete odkazovať na pôvodné horizontálne údaje bez zmeny ich umiestnenia.
Na transponovanie horizontálneho rozsahu 2 × 4 do vertikálneho rozsahu 4 × 2:
- Vyberte 8 buniek, kam chcete umiestniť výsledný vertikálny rozsah. V našom príklade to budú bunky B4:C7.
- Zadajte nasledujúci vzorec a kliknutím ho premeňte na vzorec poľa Ctrl + Shift + Enter.
=TRANSPOSE(B1:E2)
=ТРАНСП(B1:E2)
Zložené zátvorky sa automaticky pridajú na začiatok a koniec vzorca, čo znamená, že bol zadaný vzorec poľa.
Namiesto TRANSPOZÍCIA (TRANSPOSE), Na transformáciu údajov môžete použiť inú funkciu, napr. INDEX (INDEX). Nevyžaduje si zadanie vzorca poľa a pri vytváraní vzorca nemusíte vybrať všetky bunky v cieľovej oblasti.
=INDEX($B$2:$E$2,,ROW()-ROW(C$4)+1)
=ИНДЕКС($B$2:$E$2;;СТРОКА()-СТРОКА(C$4)+1)
Príklad 2: Zmena orientácie bez odkazov
Ak chcete iba zmeniť orientáciu údajov bez toho, aby ste si ponechali odkaz na pôvodné údaje, môžete použiť Prilepiť špeciálne:
- Vyberte zdrojové údaje a skopírujte ich.
- Vyberte ľavú hornú bunku oblasti, do ktorej chcete umiestniť výsledok.
- Na karte Rozšírené Domov (Domov) kliknite na rozbaľovaciu ponuku príkazov cestoviny (Vložiť).
- vybrať transponovať (Transponovať).
- Vymažte pôvodné údaje (voliteľné).
Príklad 3: Najlepší celkový plat za posledné roky
Funkcie TRANSPOZÍCIA (TRANSP) možno použiť v kombinácii s inými funkciami, ako napríklad v tomto úžasnom zložení. Zverejnil to Harlan Grove v Excel News Bloc v diskusii o výpočte najlepšej celkovej mzdy za posledných 5 rokov (v rade!).
=MAX(MMULT(A8:J8, --(ABS(TRANSPOSE(COLUMN(A8:J8))-COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number+1))-(Number-1)/2)
=МАКС(МУМНОЖ(A8:J8; --(ABS(ТРАНСП(СТОЛБЕЦ(A8:J8))-СТОЛБЕЦ(СМЕЩ(A8:J8;0;0;1;ЧИСЛСТОЛБ(A8:J8)-Number+1))-(Number-1)/2)
Как можно понять по фигурным скобкам в строке формул – это формула массива. Ячейка A5 названа číslo и в этом примере число 4 введено, как значение для количества лет.
Формула проверяет диапазоны, чтобы увидеть достаточно ли в них последовательных Результаты проверки (1 или 0) умножаются на значения ячеек, чтобы получитьйБорну
Для проверки результата на рисунке ниже в строке под значениями зарплат показаны суммарные значения для каждой стартовой ячейки, при этом максимальное значение выделено жёлтым. Это более долгий путь к тому же результату, что предыдущая формчула масоинева