30 funkcí Excelu za 30 dní: TRANSPOSE

Včera na maratonu 30 funkcí Excelu za 30 dní pomocí funkce jsme spočítali počet sloupců v rozsahu SLOUPCE (NUMBERCOLUMN) a nyní je čas na něco žádaného.

13. den maratonu se budeme věnovat studiu funkce PŘEMÍSTIT (TRANSP). Pomocí této funkce můžete svá data otáčet přeměnou vertikálních oblastí na horizontální a naopak. Máte takovou potřebu? Můžete to udělat pomocí speciální vložky? Zvládnou to i jiné funkce?

Pojďme tedy k informacím a příkladům o funkci PŘEMÍSTIT (TRANSP). Pokud máte další informace nebo příklady, podělte se o ně v komentářích.

Funkce 13: TRANSPOSE

funkce PŘEMÍSTIT (TRANSPOSE) vrátí horizontální rozsah buněk jako vertikální rozsah nebo naopak.

Jak lze použít funkci TRANSPOSE?

funkce PŘEMÍSTIT (TRANSP) může měnit orientaci dat a také pracovat ve spojení s dalšími funkcemi:

  • Změňte horizontální rozložení dat na vertikální.
  • Ukažte nejlepší celkové mzdy za poslední roky.

Chcete-li změnit orientaci dat bez vytváření odkazů na původní data:

  • Použijte speciální pasta (Speciální pasta) > Přemístit (Přemístit).

Syntaxe TRANSPOSE (TRANSP)

funkce PŘEMÍSTIT (TRANSPOSE) má následující syntaxi:

TRANSPOSE(array)

ТРАНСП(массив)

  • řada (pole) je pole nebo rozsah buněk, které mají být transponovány.

Pasti TRANSPOSE (TRANSPOSE)

  • funkce PŘEMÍSTIT (TRANSPOSE) je nutné zadat jako maticový vzorec stisknutím Ctrl + Shift + Enter.
  • Rozsah, který bude výsledkem transformace funkcí PŘEMÍSTIT (TRANSPOSE) musí mít stejný počet řádků a sloupců, jako má původní rozsah sloupců a řádků.

Příklad 1: Převedení horizontálních dat na vertikální data

Pokud jsou data v listu aplikace Excel vodorovná, můžete funkci použít PŘEMÍSTIT (TRANSPOSE), abyste je převedli do svislé polohy, ale na jiné místo na listu. Například v konečné tabulce benchmarků by bylo výhodnější vertikální uspořádání. Pomocí funkce PŘEMÍSTIT (TRANSPOSE), můžete odkazovat na původní horizontální data, aniž byste změnili jejich umístění.

Pro transpozici horizontálního rozsahu 2 × 4 do vertikálního rozsahu 4 × 2:

  1. Vyberte 8 buněk, kam chcete umístit výsledný vertikální rozsah. V našem příkladu to budou buňky B4:C7.
  2. Zadejte následující vzorec a kliknutím jej převeďte na maticový vzorec Ctrl + Shift + Enter.

=TRANSPOSE(B1:E2)

=ТРАНСП(B1:E2)

Složené závorky budou automaticky přidány na začátek a konec vzorce, aby indikovaly, že byl zadán maticový vzorec.

30 funkcí Excelu za 30 dní: TRANSPOSE

Místo PŘEMÍSTIT (TRANSPOSE), K transformaci dat můžete použít jinou funkci, např. INDEX (INDEX). Nevyžaduje zadání maticového vzorce a při vytváření vzorce nemusíte vybrat všechny buňky v cílové oblasti.

=INDEX($B$2:$E$2,,ROW()-ROW(C$4)+1)

=ИНДЕКС($B$2:$E$2;;СТРОКА()-СТРОКА(C$4)+1)

30 funkcí Excelu za 30 dní: TRANSPOSE

Příklad 2: Změna orientace bez odkazů

Pokud chcete pouze změnit orientaci svých dat bez zachování odkazu na původní data, můžete použít Vložit jinak:

  1. Vyberte zdrojová data a zkopírujte je.
  2. Vyberte levou horní buňku oblasti, kam chcete umístit výsledek.
  3. Na kartě Upřesnit Domů (Domů) klikněte na rozbalovací nabídku příkazů pasta (Vložit).
  4. vybrat Přemístit (Přemístit).
  5. Smažte původní data (volitelné).

30 funkcí Excelu za 30 dní: TRANSPOSE

Příklad 3: Nejlepší celkový plat v minulých letech

funkce PŘEMÍSTIT (TRANSP) lze použít v kombinaci s dalšími funkcemi, například v tomto úžasném složení. Zveřejnil to Harlan Grove v Excel News Bloc v diskusi o výpočtu nejlepší celkové mzdy za posledních 5 let (v řadě!).

=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 funkcí Excelu za 30 dní: TRANSPOSE

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

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

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

30 funkcí Excelu za 30 dní: TRANSPOSE

Napsat komentář