Какво прави функцията за транспониране в Excel?
Функцията за транспониране в Excel се използва за завъртане на стойностите или превключване на стойностите от редове в колони и колони в редове, тъй като тази функция приема масив от клетки като аргумент, така че за да я изпълним, трябва да натиснем CSE или Control + Shift + Enter, първо изберете точния размер на диапазона, който трябва да бъде транспониран и след това използвайте формулата за транспониране.
Синтаксис
По-долу е формулата TRANSPOSE в excel.
Аргументи
масив: Задължително. Обхват от клетки, които искате да транспонирате.
Това е функция от масив .
Как да използвам функцията TRANSPOSE в Excel? (с примери)
Пример # 1
Оригиналните данни, които искате да транспонирате, са в A3: B8.
- Стъпка 1: Изберете диапазона, в който искате вашата транспонирана стойност, тук, D6: I7.
- Стъпка 2: Сега въведете формулата TRANSPOSE в избрания регион и натиснете CTRL + SHIFT + ENTER (или COMMAND + SHIFT + ENTER в Mac).
= ПРОЗЕРВАНЕ (A3: B7)
Ще получите транспонирания изход в D6: I7, както е показано по-долу.
В това D3: I4 е транспониран изход за копиране и поставяне, а D6: I7 е изход за транспониране.
Забележете, че функцията Excel TRANSPOSE е
(= ПРОЗЕРВАНЕ (A3: B7))
Показаните по-горе къдрави скоби () показват, че функцията е въведена като формула на масив.
Ако трябва да промените каквато и да е стойност в оригиналните данни, да речем B8 = SUM (B3: B7), транспонираният изход, където сте използвали TRANSOSE, ще се актуализира автоматично, но не и в случая, когато сте използвали копието - опция за поставяне.
Той може да се използва заедно с други функции, за да се получи желаният изход. Нека да видим няколко примера за това.
Пример # 2
Да предположим, че имате списък на учениците и техните оценки, както е показано по-долу. Някои от учениците не дадоха изпити. Следователно колоната за марки се оставя празна за тези ученици.
Искате да транспонирате тези данни и когато клетката е празна, искате да поставите „Отсъстващ“ в тази клетка.
Можете да го направите, като използвате Transpose заедно с функцията IF в Excel. Можете да използвате следната формула TRANSPOSE в Excel за това:
= ПРОЗЕРВАНЕ (АКО (B3: D10 = ””, ”АБСЕНТЕН”, B3: D10))
"АКО (B3: D10 =" "," АБСЕНТЕН "означава, че ако някоя клетка в диапазона B3: D10 е празна, вместо това ще постави" Отсъства ". Тя ще транспонира дадения диапазон.
Да предположим, че в този списък, ако някой студент е набрал по-малко от 70 точки или не се е явил на изпита, се счита за НЕУСТОЙЧИВ, можете да промените формулата ЗА ТРАНСПОЗИРАНЕ като
= ПРОЗЕРВАНЕ (АКО (B3: D10 <70, ”FAIL”, B3: D10)
и ще получите крайния изход, както е показано по-долу.
Пример # 3
Понякога има нужда да се добавят някои знаци към съществуващите заедно с транспонирането. Да предположим, че имате списък с идентификатори в B4: B7, както е показано по-долу.
Искате да транспонирате идентификаторите и да добавите към него префикса “ID”. Можете да направите това, като използвате Формулата в Excel:
= ТРАНСПОЗИРАНЕ („ID“ и B4: B7)
Това ще транспонира данните и ще добави префикса към всяко от съдържанието на клетката.
Можете също така да добавите всяка стойност като суфикс, използвайки горната формула TRANSPOSE. Това може да е полезно, когато използвате функцията за транспониране, за да обедините думите, дадени в различни редове, в една клетка. За да направите това, можете да изпълните следните стъпки:
- Въведете синтаксиса: CONCATENATE (TRANSPOSE (B4: B7 & ”,”). Не натискайте Enter.
- Изберете функцията за транспониране на Excel, както е показано по-долу.
- Натиснете клавиша F9.
- Премахнете () от формулата.
- Натиснете Enter.
Той ще обедини клетките в една клетка.
Пример # 4
Да предположим, че имате библиотечни данни, в които няколкото обекта са подредени на рафт и има различни рафтове, както е показано по-долу.
Може да поискате да извлечете местоположението на който и да е от обектите (дадени в H3: H11), като използвате тези данни (B4: E6).
Можете да направите това, като използвате синтаксиса
= ИНДЕКС ($ B $ 4: $ B $ 6, СЪВПАДАНЕ (1, MMULT (- ($ C $ 4: $ E $ 6 =), ПРОЗОРЕ (КОЛОНА ($ C $ 4: $ E $ 6) 0)), 0))
Нека видим как работи тази формула TRANSPOSE в Excel.
- - ($ C $ 4: $ E $ 6 =)
Когато е дадено в H3, ще бъде - ($ C $ 4: $ E $ 6 = H3).
Това формира масив от 1 и 0, за да посочи наличието или отсъствието на стойността. За биологията в H3 той ще формира масива като: (1,0,0; 0,0,0; 0,0,0)
- ТРАНСПОЗИРАНЕ (КОЛОНА ($ C $ 4: $ E $ 6) 0))
Той създава масив от 3 реда в колона и степен 0 гарантира, че числата се преобразуват в 1. Изходът на тази функция е (1,1,1)
- MMULT (- ($ C $ 4: $ E $ 6 =), ПРОЗОРИ (КОЛОНА ($ C $ 4: $ E $ 6) 0))
MMULT в Excel умножава изхода на A и B.
MMULT ((1,0,0; 0,0,0; 0,0,0), (1,1,1)) дава изхода (1; 0; 0)
- МАТЧ (1, MMULT (), 0)
Той ще съответства на изхода на C с 1. MATCH (1, (1; 0; 0), 0) връща позицията 1.
- ИНДЕКС ($ B $ 4: $ B $ 6, съвпадение (, 0))
Той ще идентифицира стойността на клетката, за която функцията за съвпадение определя позицията. INDEX ($ B $ 4: $ B $ 6, 1) ще върне A1.
По същия начин за „География“ като предмет, той ще бъде:
- - ($ C $ 4: $ E $ 6 = D6) връща (0,0,0; 0,0,0; 0,1,0)
- ТРАНСПОЗИРАНЕ (КОЛОНА ($ C $ 4: $ E $ 6) 0)) пренастройки (1,1,1)
- MMULT ((0,0,0; 0,0,0; 0,1,0), (1,1,1)) връща (0; 0; 1)
- MATCH (1, (0; 0; 1), 0) връща 3
- INDEX ($ B $ 4: $ B $ 6, 3) връща A3.
Неща за запомняне
- Опцията copy-paste създава дубликати.
- Тази функция свързва данните с източника.
- Ако броят на избраните редове и колони не е равен на колоните и редовете на изходните данни, това ще даде грешка #VALUE.
- След като е въведена, всяка отделна клетка, която е част от тази функция, не може да бъде променена.
- Натиснете CTRL + SHIFT + ENTER, за да въведете формулата в Excel.
