Транспонирането на данни означава промяна на ориентацията на масив чрез преобразуване на неговите редове в колони или обратно. В този урок ще споделим с вас пет различни метода за превключване на ориентацията на масив и постигане на желания резултат.

Превключете колони към редове със специално поставяне

Транспониране и свързване на данни към източника

• С функция TRANSPOSE
• С функциите INDIRECT, ADDRESS, COLUMN и ROW
• С Paste Special и Find & Replace
• с Power Query

Изтегляне на примери за данни за транспониране (.xlsx)

Видео: Транспониране на данни в Excel

Превключете колони към редове със специално поставяне

Стъпка 1: Копирайте диапазона за транспониране

Изберете диапазона от клетки, където искате да превключвате редове и колони, и след това натиснете Ctrl + C за копиране на диапазона.

Стъпка 2: Изберете опцията Paste Transpose

Щракнете с десния бутон върху първата клетка от целевия диапазон и след това щракнете върху Транспониране икона (Под Опции за поставяне) от менюто с десен бутон.

Резултат

Ето! Диапазонът се транспонира за нула време!

Транспониране и свързване на данни към източника

За да превключите динамично ориентацията на диапазон (превключете колони към редове и обратно) и свържете превключения резултат с оригиналния набор от данни, можете да използвате някой от подходите по-долу:

• Функция TRANSPOSE (Лесен за използване, но резултатите не могат да се редактират)
• Функции INDIRECT, ADDRESS, COLUMN и ROW (Голяма формула, но позволява извършване на промени в резултатите)
• Специално поставяне и намиране и замяна (Сложно, но лесно за разбиране)
• Power Query (Лесно с резултати, форматирани в таблица)

Променете редове в колони с помощта на функцията TRANSPOSE

За да превърнете редове в колони и обратно с ТРАНЗОЗИРАНЕ функция, моля, направете следното.

Стъпка 1: Изберете същия брой празни клетки като оригиналния набор от клетки, но в друга посока

Съвет: Пропуснете тази стъпка, ако използвате Excel 365 или Excel 2021.

Да приемем, че вашата оригинална таблица е в обхват A1:C4, което означава, че таблицата има 4 реда и 3 колони. Така транспонираната таблица ще има 3 реда и 4 колони. Което означава, че трябва да изберете 3 реда и 4 колони с празни клетки.

Стъпка 2: Въведете формулата TRANSPOSE

Въведете формулата по-долу в лентата за формули и след това натиснете Ctrl + Shift + Enter за да получите резултата.

Съвет: Натискане Въведете ако сте потребители на Excel 365 или Excel 2021.

=TRANSPOSE(A1:C4)

Резултат

Редовете се превръщат в колони, а колоните се преобразуват в редове.

Завъртете данните с помощта на функциите INDIRECT, ADDRESS, COLUMN и ROW

Въпреки че горната формула е доста лесна за разбиране и използване, чийто недостатък е, че не можете да редактирате или изтриете клетки в завъртяната таблица. И така, ще представя формула, използваща НЕПРЯК, АДРЕС, КОЛОНА намлява РЕД функции. Да приемем, че вашата оригинална таблица е в обхват A1:C4, за да извършите трансформация от колона към ред и да поддържате завъртяните данни свързани с изходния набор от данни, моля, следвайте стъпките по-долу.

Стъпка 1: Въведете формулата

Въведете формулата по-долу в най-горната лява клетка на целевия диапазон (A6 в нашия случай) и натиснете Въведете:

=INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))

Стъпка 2: Копирайте формулата вдясно и под клетките

Изберете клетката с формула и плъзнете манипулатора й за запълване (малкия зелен квадрат в долния десен ъгъл на клетката) надолу и след това надясно до толкова редове и колони, колкото е необходимо.

Резултат

Колоните и редовете се сменят веднага.

Преобразувайте колони в редове с Paste Special и Find & Replace

Още няколко стъпки с метода Paste Special заедно с функцията Find and Replace ви позволяват да транспонирате данни, докато свързвате изходните клетки с транспонираните.

Стъпка 1: Копирайте диапазона за транспониране

Изберете диапазона от клетки, които искате да транспонирате, и след това натиснете Ctrl + C за копиране на диапазона.

Стъпка 2: Приложете опцията за поставяне на връзка

1. Щракнете с десния бутон върху празна клетка и след това щракнете Специално поставяне.

   

2. Кликнете върху Поставете Link.

   

Ще получите резултата, както е показано по-долу:

Стъпка 3: Намерете и заменете знаците за равенство (=) от резултата за поставяне на връзка

1. Изберете диапазона на резултатите (A6:C9) и натиснете Ctrl + H, и след това заменете = с @EO (или всеки символ(и), който не съществува в избрания диапазон) в Търсене и заместване диалогов прозорец.

   

2. Кликнете върху Замести всичкии след това затворете диалоговия прозорец. Следното показва как ще изглеждат данните.

   

Стъпка 4: Транспонирайте заменения резултат от поставяне на връзка

Изберете диапазона (A6:C9) и натиснете Ctrl + C да го копирате. Щракнете с десния бутон върху празна клетка (тук избрах A11) и изберете Транспониране икона от Опции за поставяне за да поставите транспонирания резултат.

Стъпка 5: Върнете знаците за равенство (=), за да свържете транспонирания резултат с оригиналните данни

1. Запазете данните за транспонирания резултат A11: D13 избрано и след това натиснете Ctrl + H, и заменете @EO с = (обратното на стъпка 3).

   

2. Кликнете върху Замести всичкии след това затворете диалоговия прозорец.

Резултат

Данните се транспонират и свързват с оригиналните клетки.

Транспонирайте и свържете данните с източника Power Query

Power Query е мощен инструмент за автоматизация на данни, който ви позволява лесно да транспонирате данни в Excel. Можете да свършите работата, като следвате стъпките по-долу:

Стъпка 1: Изберете диапазона за транспониране и отворете Power Query Редактор

Изберете диапазона от данни за транспониране. И тогава, на Дата в раздела Вземете и трансформирайте данни група, щракнете върху От таблица/обхват.

Стъпка 2: Преобразувайте колони в редове с Power Query

1. Отидете в Transform раздел. В Използвайте първия ред като заглавки падащо меню, изберете Използвайте заглавки като първи ред.

   

2. Кликнете върху Транспониране.

   

Стъпка 3: Запазете транспонираните данни в лист

От досие кликнете върху Затваряне и зареждане за да затворите прозореца на Power Editor и да създадете нов работен лист за зареждане на транспонираните данни.

Резултат

Транспонираните данни се преобразуват в таблица в новосъздадения работен лист.

Свързани статии

• Как бързо да транспонирате диапазони и да пропуснете празни клетки в Excel?
• Когато поставим диапазон като транспониран, празните клетки също ще бъдат поставени. Понякога обаче просто искаме да транспонираме диапазоните, като игнорираме празните клетки, както е показано на екранната снимка по-долу. Има ли начини за бързо транспониране на диапазони и пропускане на празни клетки в Excel?
• Как да транспонирам всеки 5 или n реда от една колона в множество колони?
• Да предположим, че имате дълги данни в колона A и сега искате да транспонирате всеки 5 реда от колона A в множество колони, като например транспониране на A1:A5 в C6:G6, A6:A10 в C7:G7 и т.н. показана е следната екранна снимка. Как бихте могли да се справите с тази задача, без да копирате и поставяте многократно в Excel?
• Как да транспонирам / конвертирам колони и редове в една колона?
• Когато използвате работен лист на Excel, понякога ще срещнете този проблем: как можете да конвертирате или транспонирате набор от данни в една колона? (Вижте следните екранни снимки:) Сега ви представям три бързи трика за разрешаване на този проблем.
• Как да транспонирам / конвертирам колони и редове в един ред?
• Как да съединя няколко реда и колони в един дълъг ред? Може би изглежда лесно за вас, защото можете да ги копирате един по един и да ги съедините в ред ръчно. Това обаче може да отнеме много време и досадно, ако има стотици редове и колони. Тук ще говоря за някои бързи трикове за разрешаването му.