Съдържание
Вчера на маратона 30 Excel функции за 30 дни преброихме броя на колоните в диапазона с помощта на функцията КОЛОНИ (NUMBERCOLUMN), а сега е време за нещо по-търсено.
На 13-ия ден от маратона ще се посветим на изучаването на функцията ТРАНЗОЗИРАНЕ (TRANSP). С тази функция можете да завъртате вашите данни, като превръщате вертикалните области в хоризонтални и обратно. Имате ли такава нужда? Можете ли да направите това с помощта на специална вложка? Могат ли други функции да го направят?
И така, нека се обърнем към информация и примери за функцията ТРАНЗОЗИРАНЕ (TRANSP). Ако имате допълнителна информация или примери, моля, споделете ги в коментарите.
Функция 13: ТРАНСПОНИРАНЕ
функция ТРАНЗОЗИРАНЕ (TRANSPOSE) връща хоризонтален диапазон от клетки като вертикален диапазон или обратното.
Как може да се използва функцията TRANSPOSE?
функция ТРАНЗОЗИРАНЕ (TRANSP) може да промени ориентацията на данните, както и да работи заедно с други функции:
- Променете хоризонталното оформление на данните на вертикално.
- Покажете най-добрите общи заплати през последните години.
За да промените ориентацията на данните, без да създавате връзки към оригиналните данни:
- употреба паста Специална (Специална паста) > Транспониране (Транспониране).
Синтаксис TRANSPOSE (TRANSP)
функция ТРАНЗОЗИРАНЕ (TRANSPOSE) има следния синтаксис:
TRANSPOSE(array)
ТРАНСП(массив)
- масив (масив) е масивът или диапазонът от клетки, които трябва да бъдат транспонирани.
Капани TRANSPOSE (TRANSPOSE)
- функция ТРАНЗОЗИРАНЕ (TRANSPOSE) трябва да се въведе като формула за масив чрез натискане Ctrl + Shift + Enter.
- Диапазонът, който ще бъде резултат от трансформацията от функцията ТРАНЗОЗИРАНЕ (TRANSPOSE) трябва да има същия брой редове и колони, както първоначалният диапазон има съответно колони и редове.
Пример 1: Превръщане на хоризонтални данни във вертикални
Ако данните са хоризонтални в лист на Excel, можете да приложите функцията ТРАНЗОЗИРАНЕ (TRANSPOSE), за да ги преобразувате във вертикална позиция, но на друго място на листа. Например, във финалната таблица с бенчмаркове, вертикалното подреждане би било по-удобно. Използване на функцията ТРАНЗОЗИРАНЕ (TRANSPOSE), можете да препращате към оригиналните хоризонтални данни, без да променяте местоположението им.
За транспониране на хоризонталния диапазон 2 × 4 във вертикалния диапазон 4 × 2:
- Изберете 8 клетки, където искате да поставите получения вертикален диапазон. В нашия пример това ще бъдат клетки B4:C7.
- Въведете следната формула и я превърнете във формула за масив, като щракнете Ctrl + Shift + Enter.
=TRANSPOSE(B1:E2)
=ТРАНСП(B1:E2)
В началото и в края на формулата автоматично ще се добавят фигурни скоби, за да се покаже, че е въведена формула за масив.
Вместо ТРАНЗОЗИРАНЕ (TRANSPOSE), можете да използвате друга функция за трансформиране на данните, например INDEX (ИНДЕКС). Не изисква да въвеждате формула за масив и не е необходимо да избирате всички клетки в целевата област, когато създавате формулата.
=INDEX($B$2:$E$2,,ROW()-ROW(C$4)+1)
=ИНДЕКС($B$2:$E$2;;СТРОКА()-СТРОКА(C$4)+1)
Пример 2: Промяна на ориентацията без връзки
Ако просто искате да промените ориентацията на вашите данни, без да запазвате препратка към оригиналните данни, можете да използвате специално поставяне:
- Изберете изходните данни и ги копирайте.
- Изберете горната лява клетка на областта, където искате да поставите резултата.
- В раздела Разширени Начало (Начало) щракнете върху падащото меню с команди паста (Вмъкване).
- Изберете Транспониране (Транспониране).
- Изтрийте оригиналните данни (по избор).
Пример 3: Най-добра обща заплата през последните години
функция ТРАНЗОЗИРАНЕ (TRANSP) може да се използва в комбинация с други функции, като например в тази зашеметяваща формула. Беше публикувано от Harlan Grove в Excel News Bloc в дискусия за изчисляване на най-добрата обща заплата за последните 5 години (подред!).
=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 наречена Телефон за връзка: и в този пример е въведено число 4, като значение за количеството години.
Формулата проверява диапазоните, за да видите достатъчно дали в тях последователни столбци. Резултатите от проверките (1 или 0) се умножават на стойностите на ячеек, за да получите сумарния объём заработной плати.
За проверка на резултатите на рисунките по-долу в реда под стойностите на заплатите са показани суммарни значения за всяка стартова ячейка, при това максималното значение е избрано жёлтым. Това е по-дълъг път към този резултат, който предишната формула на масива получава в една ячейка!
