Научете как да транспонирате данни в Excel и бързо да преместите ориентацията на колоните и редовете на работен лист.
Транспонирането на данни в Excel означава превключване или завъртане на данни от хоризонтален масив към вертикален масив или от вертикален масив към хоризонтален масив. С прости думи, функцията TRANSPOSE променя редовете в колони и колоните в редове.
В Excel можете да транспонирате данни по два различни начина, като използвате функцията TRANSPOSE и метода Paste Special. В тази статия ще ви покажем как да превключите стойността на реда в стойността на колоната и стойността на колоната в стойността на реда в Excel.
Използвайки TRANSPOSE функция
Функцията TRANSPOSE ви позволява да превключите ориентацията на даден диапазон от клетки (масив) от вертикална към хоризонтална или обратно. Тъй като това е формула за масив, функцията трябва да бъде въведена в диапазон, който има същия брой редове и колони като източника.
Синтаксисът на функцията Transpose е:
= Транспониране (масив)В следващата примерна електронна таблица ще транспонираме таблицата, която съдържа обема на износа на животни по окръзи.
Първо, трябва да знаете колко реда и колони съдържа вашата оригинална таблица. След това изберете точния брой клетки като оригинален диапазон от клетки, но в другата посока.
Например, имаме 5 реда и 3 колони, така че трябва да изберем 3 реда и 5 колони клетки за транспонираните данни. В нашия случай имаме 5 реда и 4 колони, така че трябва да изберете 4 реда и 5 колони.
Изберете диапазон от празни клетки и въведете формулата: =ТРАНСПОЗИРАНЕ(A1:D5), но все още не натискайте клавиша „Enter“! В този момент вашият Excel ще изглежда подобно на това:
Сега натиснете „Ctrl + Shift + Enter“. След като се натисне клавишната комбинация, данните ще бъдат транспонирани.
Не забравяйте, че формулата на масива винаги трябва да бъде завършена чрез натискане на „CTRL + SHIFT + ENTER“. Когато натиснете клавишната комбинация, тя ще постави набор от къдрави скоби около формулата в лентата с формули, така че резултатът да се третира като масив от данни, а не като стойност на една клетка.
Функцията за транспониране на Excel копира само данните, а не форматирането на оригиналните данни. Както можете да видите, форматирането на оригиналната заглавка не се копира. Освен това транспонирането е динамична функция, когато вашата стойност в оригиналните данни е променена, тя ще бъде отразена в транспонираните данни, но не и обратното.
Как да транспонирате данни в Excel без нули
Ако транспонирате таблица или диапазон от клетки с една или повече празни клетки, тези клетки ще имат нулеви стойности при транспониране, както е показано на снимката по-долу:
За да копирате празните клетки по време на транспониране, използвайте функцията IF във вашата формула TRANSPOSE, за да проверите дали клетката е празна или не. Ако клетката е празна/празна, тя връща празен низ (“”) към транспонираната таблица.
Ако таблицата ви има празни клетки, въведете формулата: =ТРАНСПОЗИРАНЕ(АКО(A1:D5="","",A1:D5)) в избраните клетки.
След това натиснете клавишната комбинация „Ctrl + Shift + Enter“. Сега празното пространство в клетка D5 се копира в транспонираната клетка E11.
Как да транспонирате в Excel с Специален метод за поставяне
Друг начин, по който можете да транспонирате данни в Excel, е като използвате метода Paste Special. Но транспонирането на данни чрез метода Paste Special идва с минуси и плюсове. Тази функция ви позволява да транспонирате данни с оригиналното форматиране на съдържанието, но транспонираните данни ще бъдат статични.
За разлика от функцията TRANSPOSE, ако промените стойност в оригиналните данни, тя няма да се отрази в транспонираното съдържание, когато използвате метода Paste Special.
Първо изберете диапазона, който искате да транспонирате, след което щракнете с десния бутон и изберете „Копиране“ или натиснете „CTRL + C“, за да копирате таблицата.
След това изберете диапазона от клетки, който съответства на точния размер на оригиналната таблица, както направихте за предишния метод.
След това щракнете с десния бутон върху избраните клетки и щракнете върху „Специално поставяне“.
В диалоговия прозорец Специално поставяне поставете отметка в квадратчето „Транспониране“ и щракнете върху „OK“.
Сега таблицата ще бъде транспонирана (копирана) в избрани клетки.
При този метод не е нужно да се притеснявате, че черните клетки ще станат нули в транспонираната таблица, тъй като празните клетки също ще бъдат копирани в избраните клетки.