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

Съдържание

Транспониране на данни с помощта на Специално поставяне
Транспонирайте данни с помощта на Специално поставяне и Намиране и замяна
Транспониране на данни с помощта на Excel TRANSPOSE функция
Транспониране на данни с помощта на Power Query

Гледайте видео - Как да транспонирате данни в Excel

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

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

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

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

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

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

Изберете набора от данни (в този случай A1: E5).
Копирайте набора от данни (Control + C) или щракнете с десния бутон и изберете копиране.
Сега можете да поставите транспонираните данни на ново място. В този пример искам да копирам в G1: K5, така че щракнете с десния бутон върху клетка G1 и изберете специално поставяне.
В специалния диалогов прозорец за поставяне проверете опцията за транспониране в долния десен ъгъл.
Щракнете върху OK.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Power Query е част от Excel 2016 (Get & Transform в раздела Data), но ако използвате Excel 2013 или 2010, тогава трябва да го инсталирате като добавка.

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

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

В Excel 2016

Изберете данните и отидете на Data -> Get & Transform -> From Table.
В диалоговия прозорец Създаване на таблица се уверете, че диапазонът е правилен и щракнете върху OK. Това ще отвори диалоговия прозорец Редактор на заявки.
В диалоговия прозорец Редактор на заявки изберете раздела „Трансформиране“.
В раздела Трансформиране отидете на Таблица -> Използвайте първия ред като заглавки -> Използвайте заглавките като първи ред. Това гарантира, че първият ред (който съдържа заглавки: Q1, Q2, Q3 и Q4) също се третира като данни и транспонира.
Кликнете върху Транспониране. Това незабавно ще транспонира данните.
Кликнете върху Използване на първия ред като заглавки. Това сега ще направи първия ред от транспонираните данни като заглавки.
Отидете на File -> Close and Load. Това ще затвори прозореца на Power Editor и ще създаде нов лист, който съдържа транспонираните данни.

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