Переверните данные вверх дном в Excel (обратный порядок данных в столбцах/строках)

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

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

Хотя в Excel есть несколько способов перевернуть данные, встроенных функций нет.Но вы можете легко сделать это с помощью простых приемов сортировки, формул или VBA.

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

Итак, приступим!

Отразить данные с помощью SORT и вспомогательных столбцов

Один из самых простых способов изменить порядок данных в Excel на обратный — использовать вспомогательный столбец, а затем использовать этот вспомогательный столбец для сортировки данных.

Отразить данные по вертикали (обратный порядок)

Предположим, у вас есть столбец, как показано ниже.Имя набора данных, и вы хотите перевернуть эти данные:

Вот шаги, чтобы перевернуть данные по вертикали:

1. В соседнем столбце введите «Помощник» в качестве заголовка столбца.
2. В справочной колонке введите ряд цифр (1, 2, 3 и т. д.).Вы можете сделать это быстро, используя метод, показанный здесь
3. Выберите весь набор данных, включая вспомогательные столбцы.
4. Перейдите на вкладку Данные
5. Щелкните значок сортировки
6. В диалоговом окне «Сортировка» выберите «Помощник» в раскрывающемся меню «Сортировать по».
7. В раскрывающемся меню порядка выберите «От большого к маленькому».
8. Нажмите ОК

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

Не стесняйтесь отказаться от столбца справки, когда вы закончите.

В этом примере я покажу вам, как перевернуть данные, когда есть только один столбец, но вы можете использовать тот же метод, если у вас есть вся таблица.Просто обязательно выберите всю таблицу, а затем используйте вспомогательный столбец для сортировки данных в порядке убывания.

Отразить данные по горизонтали

Таким же образом вы можете отразить данные по горизонтали в Excel.

В Excel есть возможность сортировать данные по горизонтали с помощью диалогового окна «Сортировка» (функция сортировки слева направо).

Предположим, у вас есть таблица, показанная ниже, и вы хотите перевернуть эти данные по горизонтали.

Вот шаги, чтобы сделать это:

1. В строке ниже введите «Помощник» в качестве заголовка строки.
2. Во вспомогательной строке введите ряд чисел (1, 2, 3 и т. д.).
3. Выберите весь набор данных, включая вспомогательные строки
4. Перейдите на вкладку Данные
5. Щелкните значок сортировки
6. В диалоговом окне «Сортировка» нажмите кнопку «Параметры».
7. В открывшемся диалоговом окне нажмите Сортировать слева направо
8. Нажмите ОК
9. В раскрывающемся списке «Сортировать по» выберите строку 3 (или любую другую, в которой есть вспомогательный столбец).
10. В раскрывающемся меню порядка выберите «От большого к маленькому».
11. Нажмите ОК

Вышеуказанные шаги перевернут всю таблицу по горизонтали.

Когда закончите, вы можете удалить вспомогательную строку.

Используйте Excel, чтобы перевернуть формулы вперевернуть данные

В Microsoft 365 есть несколько новых формул, которые упрощают изменение порядка столбца или таблицы в Excel.

В этом разделе я покажу вам, как это сделать с помощью формулы SORTBY (если вы используете Microsoft 365) или формулы INDEX (если вы не используете Microsoft 365).

Используйте функцию SORTBY (доступна в Microsoft 365)

Предположим, у вас есть таблица, подобная следующей, и вы хотите перевернуть данные в этой таблице:

Для этого сначала скопируйте заголовки и разместите их там, где хотите перевернуть таблицу.

Теперь используйте следующую формулу под самой левой ячейкой заголовка:

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

Приведенная выше формула сортирует данные и сортирует на основе результата функции ROW.

В этом случае функция СТРОКА вернет массив чисел, представляющих номера строк в указанном диапазоне (в данном примере ряд чисел, например 2, 3, 4 и т. д.).

Поскольку третий параметр этой формулы равен -1, он заставляет формулу сортировать данные в порядке убывания.

Запись с наибольшим номером строки будет вверху, а запись с наименьшим номером правила будет внизу, что в основном приводит к обратному порядку данных.

После этого вы можете преобразовать формулу в значение, чтобы получить статическую таблицу.

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

Если вы не можете использовать функцию SORTBY, не беспокойтесь — вы можете использовать замечательную функцию INDEX.

Предположим, у вас есть набор данных с именами, как показано ниже, и вы хотите перевернуть эти данные.

Вот формула для этого:

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

 Как работает эта формула?

В приведенной выше формуле используется функция ИНДЕКС, которая возвращает значение из ячейки на основе числа, указанного во втором параметре.

Настоящая магия происходит, когда я использую второй параметр функции ROWS.

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

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

По мере того, как мы спускаемся вниз, результат функции СТРОКИ будет уменьшаться на 1, с 11 до 10, до 9 и так далее.

Поскольку функция ИНДЕКС возвращает значение, основанное на числе во втором аргументе, это приводит к тому, что мы получаем данные в обратном порядке.

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

Предположим, у вас есть набор данных, который выглядит так, и вы хотите изменить порядок всей таблицы:

Вот формула, которая сделает это за вас:

=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 ()

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

Приложение.ScreenUpdating = False

Стартовое число = 1
EndNum = Выбор.Строки.Количество

Делать, пока StartNum < EndNum
TopRow = Выбор.Ряды(StartNum)
LastRow = Выбор.Ряды(EndNum)
Selection.Rows(EndNum) = Верхняя строка
Selection.Rows(StartNum) = Последняястрока
Стартовый номер = Стартовый номер + 1
EndNum = EndNum - 1
Петля

Application.ScreenUpdating = True
End Sub

Чтобы использовать этот код, вам сначала нужно выбрать набор данных, который вы хотите реверсировать (исключая заголовки), а затем запустить этот код.

Как работает этот код?

Приведенный выше код сначала подсчитывает общее количество строк в наборе данных и присваивает его переменной EndNum.

Затем он использует цикл Do While для сортировки данных.

Он сортирует эти данные, беря первую и последнюю строку и меняя их местами.Затем он переходит ко второй строке предпоследней строки, а затем меняет их местами.Затем он перемещается на третью строку с третьей на последнюю строку и так далее.

Цикл заканчивается, когда вся сортировка завершена.

Он также использует свойство Application.ScreenUpdating и задает для него значение FALSE во время выполнения кода, а затем возвращает его в значение TRUE по завершении выполнения кода.

Это гарантирует, что вы не видите изменений, происходящих на экране в режиме реального времени, а также ускоряет процесс.

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

Скопируйте и вставьте этот код в редактор VB следующим образом:

1. Откройте файл Excel, в который вы хотите добавить код VBA.
2. Удерживайте ALT и нажмите F-11 (вы также можете перейти на вкладку разработчика и щелкнуть значок Visual Basic)
3. В открывшемся редакторе Visual Basic слева от редактора VBA будет Project Explorer.Если вы его не видите, щелкните вкладку «Вид», затем щелкните «Проводник проекта».
4. Щелкните правой кнопкой мыши любой объект рабочей книги, куда вы хотите добавить код.
5. Перейдите к опции «Вставить» и нажмите «Модуль».Это добавит новый модуль в книгу
6. Дважды щелкните значок модуля в Project Explorer.Это откроет окно кода для этого модуля.
7. Скопируйте и вставьте приведенный выше код VBA в окно кода.

Чтобы запустить код макроса VBA, сначала выберите набор данных для отражения (исключая заголовок).

Выбрав данные, перейдите в редактор VB и нажмите зеленую кнопку воспроизведения на панели инструментов или выберите любую строку в коде и нажмите F5.

Итак, вот некоторые из способов, которыми вы можете инвертировать данные в Excel (т. е. изменить порядок набора данных на обратный).

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

Я надеюсь, что вы нашли этот урок полезным.