Как бързо да транспонирате данни в Excel (ръководство стъпка по стъпка)

0 Акции

Ако имате набор от данни и искате да го транспонирате в Excel (Това означава да конвертирате редове в колони и колони в редове), ръчното изпълнение е напълно невъзможно!

Съдържание

1 Транспониране на данни с помощта на специално поставяне
2 Транспонирайте данни с помощта на специално поставяне и намерете и заменете
3 Транспониране на данни с помощта на функцията TRANSPOSE на Excel
4 Транспониране на данни с Power Query
- 4.1 в Excel 2016
- 4.2 В Excel 2013/2010
5 О, здравей, радвам се да се запознаем.

Транспониране на данни с помощта на специално поставяне

Paste Special може да направи много невероятни неща, едно от които е да транспонира данни в Excel.

Да предположим, че имате набор от данни, който изглежда така:

Данните имат региони в колони и четвъртинки в редове.

Сега по някаква причина, ако трябва да транспонирате тези данни, можете да използвате специална паста по следния начин:

Това ще копира и постави данните незабавно, но по начин, който е транспониран.По-долу е демонстрация, показваща целия процес.

Показаните по-горе стъпки копират стойностите, формулите (ако има такива) и форматирането.Ако просто искате да копирате стойността, изберете "Стойност" в специалния диалогов прозорец за поставяне.

Имайте предвид, че копираните данни са статични и ако направите някакви промени в оригиналния набор от данни, тези промени няма да бъдат отразени в транспонираните данни.

Ако искате да свържете тези транспонирани клетки с оригиналните клетки, можете да комбинирате силата на Find and Replace с Paste Special.

Транспонирайте данни с помощта на специално поставяне и намерете и заменете

Paste Special само ви дава статични данни.Това означава, че ако оригиналните ви данни се променят и данните, които искате да транспонирате, също се актуализират, тогава трябва да ги транспонирате отново, като използвате специална паста.

Това е страхотен трик, който можете да използвате, за да транспонирате данните и все пак да ги свържете с оригиналните клетки.

Да предположим, че имате набор от данни, който изглежда така:

Ето стъпките за транспониране на данните, но запазване на връзката непокътната:

Изберете набора от данни (A1:E5).
Дублирайте го (Control + C или щракнете с десния бутон и изберете Дублиране).
Сега можете да поставите транспонираните данни на новото място.В този пример искам да копирам в G1:K5, така че щракнете с десния бутон върху клетката G1 и изберете Специално поставяне.
В диалоговия прозорец Специално поставяне щракнете върху бутона Поставяне на връзка.Това ще ви даде същия набор от данни, но тук клетките са свързани с оригиналния набор от данни (например G1 е свързан с A1, G2 е свързан с A2 и т.н.).
С избрани тези новокопирани данни, натиснете Control + H (или отидете на Начало -> Редактиране -> Намери и изберете -> Замяна).Това ще отвори диалоговия прозорец Търсене и замяна.
В диалоговия прозорец Find and Replace използвайте следните команди:
- В Намери какво:=
- Заменете с: !@# (обърнете внимание, че използвах !@#, тъй като това е уникална комбинация от знаци и е малко вероятно да бъде част от вашите данни. Можете да използвате всеки такъв уникален набор от знаци).
Щракнете върху Замяна на всички.Това ще замени равните във формулата и ще имате !@#, последвано от препратка към клетка във всяка клетка.
Щракнете с десния бутон и копирайте набора от данни (или използвайте Control+C).
Изберете ново местоположение, щракнете с десния бутон и изберете Специално поставяне.В този пример го поставям в клетка G7.Можете да го поставите където пожелаете.
В диалоговия прозорец Специално поставяне изберете Транспониране и щракнете върху OK.
Копирайте и поставете тези новосъздадени транспонирани данни на мястото, където са създадени.
Сега отворете отново диалоговия прозорец Търсене и замяна и заменете !@# с =.

Свързани въпроси Поправете грешката в Windows 5 Spotlight не работи в 10 стъпки

Това ще ви даде транспонираните свързани данни.Транспонираните данни ще се актуализират автоматично, ако направите промени в оригиналния набор от данни.

Забележка: Тъй като A1 е празен в нашите оригинални данни, трябва ръчно да премахнете 1 в G0.0 се появява, когато поставим връзката, защото връзката към празна клетка все още връща 0.Също така, трябва да форматирате новия набор от данни (можете просто да копирате и поставите форматирането от оригиналния набор от данни).

Транспониране на данни с помощта на функцията TRANSPOSE на Excel

Функцията TRANSPOSE на Excel - както подсказва името - може да се използва за транспониране на данни в Excel.

Да предположим, че имате набор от данни, който изглежда така:

Ето стъпките за транспонирането му:

Изберете клетките, в които искате да транспонирате набора от данни.Имайте предвид, че трябва да изберете точния брой клетки като необработени данни.Така че, например, ако имате 2 реда и 3 колони, трябва да изберете 3 реда и 2 колони клетки, където искате да транспонирате данните.В този случай, тъй като има 5 реда и 5 колони, трябва да изберете 5 реда и 5 колони.
Въведете =TRANSPOSE(A1:E5) в активната клетка (трябва да бъде избрана горната лява клетка и натиснете Control Shift Enter.

Това ще транспонира набора от данни.

Ето какво трябва да знаете за функцията TRANSPOSE:

Това е функция от масив, така че трябва да използвате Control-Shift-Enter вместо просто Enter.
Не можете да изтриете част от резултата.Трябва да изтриете целия масив от стойности, върнати от функцията TRANSPOSE.
Функцията за транспониране копира само стойността, а не формата.

Свързани въпроси Новини и интереси - функции и настройки в Windows 10

Транспониране на данни с Power Query

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

Power Query ДаExcel 2016(„Вземете и трансформирайте“ в раздела „Данни“), но ако използватеExcel 2013 или 2010, трябва да го инсталирате като добавка.

Да предположим, че имате набор от данни, който изглежда така:

Ето стъпките за транспониране на тези данни с помощта на Power Query:

в Excel 2016

Имайте предвид, че тъй като горната лява клетка на нашия набор от данни е празна, тя получава общо име Column1 (показано по-долу).Можете да премахнете тази клетка от транспонираните данни.