Иногда вам может потребоваться перевернуть данные в Excel, то есть изменить порядок данных вверх ногами в вертикальном наборе данных и слева направо в горизонтальном наборе данных.
Если вы думаете, что для этого в Excel должна быть встроенная функция, боюсь, вы будете разочарованы.
Хотя есть несколько способов перевернуть данные в Excel, встроенной функции нет. Но вы можете легко сделать это, используя простой трюк сортировки, формулы или VBA.
В этом руководстве я покажу вам, как переворачивать данные в строках, столбцах и таблицах в Excel.
Итак, приступим!
Отразить данные с помощью SORT и вспомогательного столбца
Один из самых простых способов изменить порядок данных в Excel — использовать вспомогательный столбец, а затем использовать этот вспомогательный столбец для сортировки данных.
Переверните данные по вертикали (обратный порядок вверх ногами)
Предположим, у вас есть набор данных с именами в столбце, как показано ниже, и вы хотите перевернуть эти данные:
Ниже приведены шаги, чтобы перевернуть данные по вертикали:
- В соседнем столбце введите «Помощник» в качестве заголовка столбца.
- В столбце помощника введите ряд чисел (1, 2, 3 и т. Д.). Вы можете использовать показанные здесь методы, чтобы сделать это быстро.
- Выберите весь набор данных, включая вспомогательный столбец
- Перейдите на вкладку «Данные«.
- Щелкните значок сортировки
- В диалоговом окне «Сортировка» выберите «Помощник» в раскрывающемся списке «Сортировать по».
- В раскрывающемся списке «Порядок» выберите «От наибольшего к наименьшему».
- Нажмите ОК.
Вышеупомянутые шаги будут отсортировать данные на основе значений вспомогательных столбцов, что также приведет к изменению порядка имен в данных на обратный.
После этого можете удалить вспомогательный столбец.
В этом примере я показал вам, как переворачивать данные, когда у вас всего один столбец, но вы также можете использовать тот же метод, если у вас есть вся таблица. Просто убедитесь, что вы выбрали всю таблицу, а затем использовали вспомогательный столбец для сортировки данных в порядке убывания.
Отразить данные по горизонтали
Вы также можете использовать ту же методику, чтобы перевернуть данные в Excel по горизонтали.
В Excel есть возможность сортировать данные по горизонтали с помощью диалогового окна «Сортировка» (функция «Сортировать слева направо»).
Предположим, у вас есть таблица, показанная ниже, и вы хотите перевернуть эти данные по горизонтали.
Ниже приведены шаги для этого:
- В строке ниже введите «Помощник» в качестве заголовка строки.
- Во вспомогательной строке введите ряд чисел (1, 2, 3 и т. Д.).
- Выберите весь набор данных, включая вспомогательную строку
- Перейдите на вкладку «Данные«.
- Щелкните значок сортировки
- В диалоговом окне «Сортировка» нажмите кнопку «Параметры«.
- В открывшемся диалоговом окне нажмите «Сортировать слева направо».
- Нажмите ОК.
- В раскрывающемся списке «Сортировать по» выберите строку 3 (или любую другую строку, в которой есть вспомогательный столбец).
- В раскрывающемся списке «Порядок» выберите «От наибольшего к наименьшему».
- Нажмите ОК.
Вышеупомянутые шаги перевернут всю таблицу по горизонтали.
После этого вы можете удалить вспомогательную строку.
Отразить данные с помощью формул
В Microsoft 365 появилось несколько новых формул, которые позволяют очень легко изменить порядок столбца или таблицы в Excel.
В этом разделе я покажу вам, как это сделать, используя формулу СОРТБИ (если вы используете Microsoft 365) или формулу ИНДЕКС (если вы не используете Microsoft 365).
Использование функции СОРТБИ (доступно в Microsoft 365)
Предположим, у вас есть таблица, показанная ниже, и вы хотите перевернуть данные в этой таблице:
Для этого сначала скопируйте заголовки и поместите их там, где вы хотите, чтобы перевернутая таблица.
Теперь используйте следующую формулу под ячейкой в крайнем левом заголовке:= СОРТИРОВАТЬ ($ A $ 2: $ B $ 12; СТРОКА (A2: A12); - 1)
Приведенная выше формула сортирует данные и использует результат функции СТРОКА в качестве основы для их сортировки.
Функция ROW в этом случае вернет массив чисел, который представляет номера строк между указанным диапазоном (который в этом примере будет серией чисел, таких как 2, 3, 4 и т. Д.).
А поскольку третий аргумент этой формулы равен -1, это заставит формулу сортировать данные в порядке убывания.
Запись с наивысшим номером строки будет наверху, а запись с наименьшим номером правила — внизу, что существенно меняет порядок данных.
После этого вы можете преобразовать формулу в значения, чтобы получить статическую таблицу.
Использование функции ИНДЕКС
Если у вас нет доступа к функции СОРТИРОВКА, не беспокойтесь — вы можете использовать удивительную функцию ИНДЕКС.
Предположим, у вас есть набор данных с именами, как показано ниже, и вы хотите перевернуть эти данные.
Ниже приведена формула для этого:= ИНДЕКС ($ A $ 2: $ A $ 12, СТРОКИ (A2: $ A $ 12))
Как работает эта формула?
В приведенной выше формуле используется функция ИНДЕКС, которая возвращает значение из ячейки на основе числа, указанного во втором аргументе.
Настоящая магия происходит во втором аргументе, где я использовал функцию ROWS.
Поскольку я заблокировал вторую часть ссылки в функции ROWS, в первой ячейке она вернет количество строк между A2 и A12, которое будет равно 11.
Но когда он идет вниз по строкам, первая ссылка изменится на A3, а затем на A4 и так далее, в то время как вторая ссылка останется как есть, потому что я заблокировал ее и сделал ее абсолютной.
По мере того, как мы спускаемся по строкам, результат функции ROWS будет уменьшаться на 1, с 11 до 10, до 9 и так далее.
А поскольку функция ИНДЕКС возвращает нам значение на основе числа во втором аргументе, это в конечном итоге даст нам данные в обратном порядке.
Вы можете использовать ту же формулу, даже если у вас есть несколько столбцов в наборе данных. однако вам нужно будет указать второй аргумент, который будет указывать номер столбца, из которого должны быть извлечены данные.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите изменить порядок всей таблицы в обратном порядке:
Ниже приведена формула, которая сделает это за вас:= ИНДЕКС ($ A $ 2: $ B $ 12, СТРОКИ (A2: $ A $ 12), КОЛОННЫ ($ A $ 2: A2))
Это аналогичная формула, в которой я также добавил третий аргумент, определяющий номер столбца, из которого должно быть извлечено значение.
Чтобы сделать эту формулу динамической, я использовал функцию COLUMNS, которая продолжала бы изменять значение столбца с 1 на 2 до 3, когда вы копируете его вправо.
После этого вы можете преобразовать формулы в значения, чтобы получить статический результат.
Примечание. При использовании формулы для изменения порядка набора данных в Excel исходное форматирование не сохраняется. Если вам также нужно исходное форматирование для отсортированных данных, вы можете применить его вручную или скопировать и вставить форматирование из исходного набора данных в новый отсортированный набор данных.
Перевернуть данные с помощью VBA
Если перелистывание данных в Excel — это то, что вам нужно делать довольно часто, вы также можете попробовать метод VBA.
С помощью кода макроса VBA вы можете один раз скопировать и вставить его в книгу в редакторе VBA, а затем повторно использовать его снова и снова в той же книге.
Вы также можете сохранить код в личной книге макросов или как надстройку Excel и иметь возможность использовать его в любой книге в вашей системе.
Ниже приведен код VBA, который перевернет выбранные данные на листе по вертикали.Sub FlipVerically () 'Code by Sumit Bansal с TrumpExcel.com Dim TopRow As Variant Dim LastRow As Variant 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 приложение цикла. ScreenUpdating = True End Sub
Чтобы использовать этот код, вам сначала нужно выбрать набор данных, который вы хотите отменить (исключая заголовки), а затем запустить этот код.
Как работает этот код?
Приведенный выше код сначала подсчитывает общее количество строк в наборе данных и присваивает его переменной EndNum.
Затем он использует цикл Do While, в котором происходит сортировка данных.
Он сортирует эти данные, беря первую и последнюю строки и меняя их местами. Затем он переходит во вторую строку во второй последней строке и меняет их местами. Затем он переходит в третий ряд в третьем последнем ряду и так далее.
Цикл заканчивается, когда вся сортировка завершена.
Он также использует свойство Application.ScreenUpdating и устанавливает для него значение FALSE во время выполнения кода, а затем возвращает его в значение TRUE, когда код завершает работу.
Это гарантирует, что вы не увидите изменений, происходящих в реальном времени на экране, а также ускоряет процесс.
Как пользоваться кодом?
Выполните следующие действия, чтобы скопировать и вставить этот код в редактор VB:
- Откройте файл Excel, в который вы хотите добавить код VBA.
- Удерживая клавишу ALT, нажмите клавишу F11 (вы также можете перейти на вкладку Разработчик и щелкнуть значок Visual Basic)
- В открывшемся редакторе Visual Basic в левой части редактора VBA будет Project Explorer. Если вы его не видите, перейдите на вкладку «Просмотр», а затем нажмите «Обозреватель проекта».
- Щелкните правой кнопкой мыши любой из объектов книги, в которую вы хотите добавить код.
- Перейдите к опции «Вставить» и нажмите «Модуль». Это добавит новый модуль в книгу
- Дважды щелкните значок модуля в проводнике проекта. Это откроет окно кода для этого модуля
- Скопируйте и вставьте приведенный выше код VBA в окно кода.
Чтобы запустить код макроса VBA, сначала выберите набор данных, который вы хотите перевернуть (за исключением заголовков).
Выбрав данные, перейдите в редактор VB и нажмите зеленую кнопку воспроизведения на панели инструментов или выберите любую строку в коде и затем нажмите клавишу F5.
Итак, это некоторые из методов, которые вы можете использовать для переворота данных в Excel (т. Е. Обратного порядка набора данных).
Все методы, которые я рассмотрел в этом руководстве (формулы, функция СОРТИРОВКИ и VBA), можно использовать для переворота данных по вертикали и горизонтали (вам нужно будет соответствующим образом настроить формулу и код VBA для горизонтального переворота данных).
Надеюсь, вы нашли этот урок полезным.
