Обърнете данните с главата надолу в Excel (обърнете реда на данните в колони/редове)

Понякога може да се наложи да обърнете данните с главата надолу в Excel, тоест да обърнете реда на данните във вертикален набор от данни и да обърнете реда на данните отляво надясно в хоризонтален набор от данни.

Сега, ако смятате, че трябва да има вградена функция в Excel, за да направите това, страхувам се, че ще бъдете разочаровани.

Въпреки че има няколко начина за обръщане на данни в Excel, няма вградена функционалност.Но можете лесно да направите това с прости трикове за сортиране, формули или VBA.

В този урок ще ви покажа как да обръщате данните в редове, колони и таблици с главата надолу в Excel.

Така че нека започваме!

Обърнете данните с помощта на SORT и помощни колони

Един от най-лесните начини за обръщане на реда на данните в Excel е да използвате помощна колона и след това да използвате тази помощна колона, за да сортирате данните.

Обръщане на данните вертикално (обратен ред)

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

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

1. В съседната колона въведете „Помощник“ като заглавие на колоната
2. В колоната за помощ въведете поредица от числа (1, 2, 3 и т.н.).Можете да направите това бързо, като използвате метода, показан тук
3. Изберете целия набор от данни, включително помощните колони
4. Щракнете върху раздела Данни
5. Щракнете върху иконата за сортиране
6. В диалоговия прозорец Сортиране изберете Помощник от падащото меню Сортиране по
7. В падащото меню за поръчка изберете „Голяма към малка“
8. Щракнете върху OK

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

Чувствайте се свободни да пуснете колоната за помощ, когато сте готови.

В този пример ви показвам как да обърнете данните, когато има само една колона, но можете да използвате същата техника, ако имате цялата таблица.Просто се уверете, че сте избрали цялата таблица, след което използвайте колоната за помощ, за да сортирате данните в низходящ ред.

Обърнете данните хоризонтално

Можете също да обърнете данните хоризонтално в Excel по същия начин.

Excel има опция за сортиране на данни хоризонтално с помощта на диалоговия прозорец Сортиране (функция Сортиране отляво надясно).

Да предположим, че имате таблица, както е показано по-долу, и искате да обърнете тези данни хоризонтално.

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

1. В реда по-долу въведете „Помощник“ като заглавие на реда
2. В помощния ред въведете серия от числа (1, 2, 3 и т.н.).
3. Изберете целия набор от данни, включително помощните редове
4. Щракнете върху раздела Данни
5. Щракнете върху иконата за сортиране
6. В диалоговия прозорец Сортиране щракнете върху бутона Опции.
7. В диалоговия прозорец, който се отваря, щракнете върху Сортиране отляво надясно
8. Щракнете върху OK
9. В падащия списък Сортиране по изберете ред 3 (или каквото има вашата помощна колона)
10. В падащото меню за поръчка изберете „Голяма към малка“
11. Щракнете върху OK

Горните стъпки ще обърнат цялата маса хоризонтално.

Когато сте готови, можете да изтриете помощната линия.

Използвайте Excel, за да обърнете формулите с главата надолуфлип данни

Microsoft 365 има някои нови формули, които правят много лесно обръщането на реда на колона или таблица в Excel.

В този раздел ще ви покажа как да направите това с помощта на формулата SORTBY (ако използвате Microsoft 365) или формулата INDEX (ако не използвате Microsoft 365)

Използвайте функцията SORTBY (налична в Microsoft 365)

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

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

Сега използвайте следната формула под най-лявата заглавна клетка:

=SORTBY($A$2:$B$12,ROW(A2:A12),-1)

Горната формула сортира данните и сортира въз основа на резултата от функцията ROW.

В този случай функцията ROW ще върне масив от числа, представляващи номера на редове между посочения диапазон (в този пример серия от числа, като 2, 3, 4 и т.н.).

Тъй като третият параметър на тази формула е -1, той принуждава формулата да сортира данните в низходящ ред.

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

След като сте готови, можете да преобразувате формулата в стойност, за да получите статична таблица.

Използвайте индексна функция

Ако не можете да използвате функцията SORTBY, не се притеснявайте - можете да използвате невероятната функция INDEX.

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

Ето формулата, за да направите това:

=ИНДЕКС($A$2:$A$12,РЕДОВЕ(A2:$A$12))

 Как действа тази формула?

Горната формула използва функцията INDEX, която ще върне стойността от клетката въз основа на числото, посочено във втория параметър.

Истинската магия се случва, когато използвам втория параметър на функцията ROWS.

Тъй като заключих втората част на препратката във функцията ROWS, в първата клетка тя ще върне броя на редовете между A2 и A12, което е 11.

Но докато се спуска надолу по реда, първата препратка ще се промени на A3, след това A4 и така нататък, докато втората препратка ще остане такава, каквато е, защото я заключих и я направих абсолютна.

Докато слизаме надолу, резултатът от функцията ROWS ще намалее с 1, от 11 на 10 до 9 и т.н.

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

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

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

Ето формулата, която ще направи това за вас:

=INDEX($A$2:$B$12,ROWS(A2:$A$12),COLUMNS($A$2:A2))

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

За да направя тази формула динамична, използвах функцията COLUMNS, която непрекъснато променя стойността на колоната от 1 на 2 на 3, докато я копирате вдясно.

След като сте готови, можете да преобразувате формулата в стойност, за да осигурите статични резултати.

Забележка: Когато използвате формула за обръщане на реда на набор от данни в Excel, тя не запазва оригиналното форматиране.Ако имате нужда и от оригиналното форматиране на сортираните данни, можете да го приложите ръчно или да копирате и поставите форматирането от оригиналния набор от данни в новия сортиран набор от данни

Обръщане на данни с помощта на VBA

Ако обръщането на данни в Excel е нещо, което трябва да правите често, можете също да опитате метода VBA.

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

Можете също да запишете кода в лична работна книга с макроси или като добавка на Excel и да можете да го използвате във всяка работна книга на вашата система.

По-долу е кодът на VBA за обръщане на избраните данни вертикално в работния лист.

Sub Flip Verical()

Dim TopRow като вариант
Dim LastRow като вариант
Dim StartNum като цяло число
Dim EndNum като цяло число

Application.ScreenUpdating = False

Начален номер = 1
EndNum = Избор.Редове.Брой

Направете, докато StartNum < EndNum
Горен ред = Избор.Редове(Начален номер)
Последен ред = Избор.Редове(КраенНом)
Selection.Rows(EndNum) = Горен ред
Selection.Rows(StartNum) = Последен ред
Начален номер = Начален номер + 1
КрайНомер = КрайНомер - 1
Контур

Application.ScreenUpdating = Вярно
End Sub

За да използвате този код, първо трябва да изберете набора от данни, който искате да обърнете (с изключение на заглавките), след което да изпълните този код.

Как работи този код?

Горният код първо отчита общия брой редове в набора от данни и го присвоява на променливата EndNum.

След това използва цикъл Do While за сортиране на данните.

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

Цикълът завършва, когато цялото сортиране приключи.

Той също така използва свойството Application.ScreenUpdating и го задава на FALSE, когато кодът се изпълнява, след което го връща обратно на TRUE, когато кодът приключи.

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

Как да използвам кода?

Копирайте и поставете този код във VB редактора, както следва:

1. Отворете файла на Excel, където искате да добавите VBA кода
2. Задръжте ALT и натиснете F-11 (можете също да отидете в раздела за програмисти и да щракнете върху иконата на Visual Basic)
3. В редактора на Visual Basic, който се отваря, ще има Project Explorer от лявата страна на редактора на VBA.Ако не го виждате, щракнете върху раздела Изглед, след което щракнете върху Project Explorer
4. Щракнете с десния бутон върху всеки обект от работната книга, където искате да добавите код
5. Отидете на опцията Вмъкване и щракнете върху Модул.Това ще добави нов модул към работната книга
6. Щракнете двукратно върху иконата на модула в Project Explorer.Това ще отвори прозореца с кода за този модул
7. Копирайте и поставете горния VBA код в прозореца с кода

За да стартирате макрокода на VBA, първо изберете набора от данни за обръщане (с изключение на заглавката).

С избраните данни отидете на редактора на VB и щракнете върху зеления бутон за възпроизвеждане в лентата с инструменти или изберете произволен ред в кода и натиснете F5

И така, това са някои от начините, по които можете да обърнете данни в Excel (т.е. да обърнете реда на набор от данни).

Всички методи, които разгледах в този урок (формули, функции SORT и VBA), могат да се използват за обръщане на данни вертикално и хоризонтално (трябва да коригирате формулите и VBA кода съответно за хоризонтално обръщане на данни).

Надявам се, че сте намерили този урок за полезен.