Obsah
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:
- Vyberte 8 buněk, kam chcete umístit výsledný vertikální rozsah. V našem příkladu to budou buňky B4:C7.
- 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.
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)
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:
- Vyberte zdrojová data a zkopírujte je.
- Vyberte levou horní buňku oblasti, kam chcete umístit výsledek.
- Na kartě Upřesnit Domů (Domů) klikněte na rozbalovací nabídku příkazů pasta (Vložit).
- vybrat Přemístit (Přemístit).
- Smažte původní data (volitelné).
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)
Как можно понять по фигурным скобкам в строке формул – это формула массива. Ячейка A5 названа Číslo и в этом примере число 4 введено, как значение для количества лет.
Формула проверяет диапазоны, чтобы увидеть достаточно ли в них последовательных Результаты проверки (1 или 0) умножаются на значения ячеек, чтобы получитъапорну
Для проверки результата на рисунке ниже в строке под значениями зарплат показаны суммарные значения для каждой стартовой ячейки, при этом максимальное значение выделено жёлтым. Это более долгий путь к тому же результату, что предыдущая формчула масосинева