Обръщане на данни в 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

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

След като приключите, можете да премахнете помощния ред.

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

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, тя би принудила формулата да сортира данните в низходящ ред.

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

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

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

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

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

По -долу е формулата за това:

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

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

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

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

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

Но когато слезе по редовете, първата препратка ще се промени на A3, а след това на A4 и т.н.

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

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

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

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

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

= ИНДЕКС ($ A $ 2: $ B $ 12, ROWS (A2: $ A $ 12), КОЛОНИ ($ A $ 2: A2))

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

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

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

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

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

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

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

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

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

Sub FlipVerically () 'Код от Sumit Bansal от TrumpExcel.com Dim TopRow като вариант Dim LastRow като вариант Dim Dim StartNum Като Integer Dim EndNum Като цяло приложение.CreenUpdating = False StartNum = 1 EndNum = Selection.Rows.Count Do While StartNum <EndNum TopRow = Selection.Rows (StartNum) LastRow = Selection.Rows (EndNum) Selection.Rows (EndNum) = TopRow Selection.Rows (StartNum) = LastRow StartNum = StartNum + 1 EndNum = EndNum - 1 Loop Application.ScreenUpdating = True End Sub

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

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

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

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

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

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

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

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

Как да използвате кода?

Следвайте стъпките по -долу, за да копирате и поставите този код във VB Editor:

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

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

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

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

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

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