Перевернути дані в Excel - Зворотний порядок даних у стовпці/рядку

Іноді вам може знадобитися перевернути дані в Excel, тобто змінити порядок даних догори дном у вертикальному наборі даних та зліва направо у горизонтальному наборі даних.

Тепер, якщо ви думаєте, що для цього в Excel має бути вбудована функція, я боюся, що ви розчаруєтесь.

Хоча є кілька способів перевернути дані в Excel, вбудованої функції немає. Але ви можете легко це зробити, використовуючи простий трюк сортування, формули або VBA.

У цьому посібнику я покажу вам, як перевернути дані у рядках, стовпцях та таблицях у Excel.

Тож почнемо!

Перевернути дані за допомогою СОРТУВАННЯ та стовпця помічника

Одним із найпростіших способів змінити порядок даних у 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.

Припустимо, у вас є набір даних імен, як показано нижче, і ви хочете перевернути ці дані.

Нижче наведена формула для цього:

= INDEX ($ A $ 2: $ A $ 12, ROWS (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 FlipVerically () 'Код від Sumit Bansal від TrumpExcel.com Dim TopRow як варіант Dim Dim LastRow Як варіант Dim Dim StartNum As Integer Dim EndNum As Integer Application.ScreenUpdating = 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:

1. Відкрийте файл Excel, куди потрібно додати код VBA
2. Утримуйте клавішу ALT і натисніть клавішу F-11 (ви також можете перейти на вкладку Розробник і натиснути на значок Visual Basic)
3. У редакторі Visual Basic, який відкриється, у лівій частині редактора VBA міститься Провідник проектів. Якщо ви не бачите його, натисніть на вкладку "Перегляд", а потім - на "Провідник проектів"
4. Клацніть правою кнопкою миші будь-який об’єкт книги, до якого потрібно додати код
5. Перейдіть до опції Вставка, а потім натисніть на Модуль. Це додасть новий модуль до книги
6. Двічі клацніть піктограму модуля в Провіднику проектів. Це відкриє вікно коду для цього модуля
7. Скопіюйте та вставте вищевказаний код VBA у вікно коду

Щоб запустити код макросу VBA, спочатку виберіть набір даних, який потрібно перевернути (за винятком заголовків).

З вибраними даними перейдіть до редактора VB і натисніть зелену кнопку відтворення на панелі інструментів, або виберіть будь -який рядок у коді, а потім натисніть клавішу F5

Отже, це деякі з методів, які можна використовувати для перевертання даних у Excel (тобто змінити порядок набору даних).

Усі методи, які я розглянув у цьому посібнику (формули, функція СОРТУВАННЯ та VBA), можна використовувати для перевертання даних по вертикалі та горизонталі (вам доведеться відповідно налаштувати формулу та код VBA для горизонтального перегортання даних).

Сподіваюся, цей підручник був вам корисним.