30 funkcií Excelu za 30 dní: TRANSPOSE

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:

  1. Vyberte 8 buniek, kam chcete umiestniť výsledný vertikálny rozsah. V našom príklade to budú bunky B4:C7.
  2. 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.

30 funkcií Excelu za 30 dní: TRANSPOSE

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)

30 funkcií Excelu za 30 dní: TRANSPOSE

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:

  1. Vyberte zdrojové údaje a skopírujte ich.
  2. Vyberte ľavú hornú bunku oblasti, do ktorej chcete umiestniť výsledok.
  3. Na karte Rozšírené Domov (Domov) kliknite na rozbaľovaciu ponuku príkazov cestoviny (Vložiť).
  4. vybrať transponovať (Transponovať).
  5. Vymažte pôvodné údaje (voliteľné).

30 funkcií Excelu za 30 dní: TRANSPOSE

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)

30 funkcií Excelu za 30 dní: TRANSPOSE

Как можно понять по фигурным скобкам в строке формул – это формула массива. Ячейка A5 названа číslo и в этом примере число 4 введено, как значение для количества лет.

Формула проверяет диапазоны, чтобы увидеть достаточно ли в них последовательных Результаты проверки (1 или 0) умножаются на значения ячеек, чтобы получитьйБорну

Для проверки результата на рисунке ниже в строке под значениями зарплат показаны суммарные значения для каждой стартовой ячейки, при этом максимальное значение выделено жёлтым. Это более долгий путь к тому же результату, что предыдущая формчула масоинева

30 funkcií Excelu za 30 dní: TRANSPOSE

Nechaj odpoveď