В Excel в обратном порядке

Обновлено: 21.11.2024

Отражение данных в Excel звучит как тривиальная задача, выполняемая одним щелчком мыши, но на удивление такой встроенной опции нет. В ситуациях, когда вам нужно изменить порядок данных в столбце, расположенном в алфавитном порядке или от меньшего к большему, вы, очевидно, можете использовать функцию сортировки Excel. Но как перевернуть столбец с несортированными данными? Или как изменить порядок данных в таблице по горизонтали в строках? Вы получите все ответы через мгновение.

Отразить данные в Excel по вертикали

Проявив немного изобретательности, вы можете разработать несколько различных способов отражения столбца в Excel: с помощью встроенных функций, формул, VBA или специальных инструментов. Подробные шаги по каждому методу приведены ниже.

Как перевернуть столбец в Excel

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

Добавьте вспомогательный столбец рядом со столбцом, который вы хотите перевернуть, и заполните этот столбец последовательностью чисел, начиная с 1. В этом совете показано, как сделать это автоматически.
Сортировать столбец чисел в порядке убывания. Для этого выберите любую ячейку во вспомогательном столбце, перейдите на вкладку Данные в группу Сортировка и фильтр и нажмите кнопку Сортировать от большего к меньшему (ZA).

Как показано на снимке экрана ниже, при этом будут отсортированы не только числа в столбце B, но и исходные элементы в столбце A с обратным порядком строк:

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

Введите 1 в первую ячейку и 2 во вторую ячейку (ячейки B2 и B3 на снимке экрана ниже).
Выделите ячейки, в которые вы только что ввели числа, и дважды щелкните правый нижний угол выделения.

Вот оно! Excel автоматически заполнит столбец серийными номерами до последней ячейки с данными в соседнем столбце.

Как перевернуть таблицу в Excel

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

Иногда (чаще всего, когда вы выбираете весь столбец чисел перед сортировкой) Excel может отображать диалоговое окно Предупреждение о сортировке. В этом случае установите флажок Расширить выбор, а затем нажмите кнопку Сортировать.

Совет. Если вы хотите повернуть данные из строк в столбцы или наоборот, используйте функцию транспонирования Excel или другие способы преобразования строк в столбцы, описанные в разделе Транспонирование в Excel.

Как отразить столбцы в Excel с помощью формулы

Еще один способ перевернуть столбец вверх ногами — использовать следующую общую формулу:

Для нашего примера набора данных формула выглядит следующим образом:

…и безупречно переворачивает столбец A:

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

В основе формулы лежит функция ИНДЕКС(массив, номер_строки, [номер_столбца]), которая возвращает значение элемента в массиве на основе указанных вами номеров строк и/или столбцов. .

В массиве вы передаете весь список, который хотите перевернуть (в этом примере A2:A7).

Номер строки определяется функцией ROWS. В своей простейшей форме ROWS(массив) возвращает количество строк в массиве. В нашей формуле умное использование относительных и абсолютных ссылок делает трюк «перевернуть столбец»:

Для первой ячейки (B2) функция ROWS(A2:$A$7) возвращает 6, поэтому функция ИНДЕКС получает последний элемент в списке (6-й элемент).
Во второй ячейке (B3) относительная ссылка A2 изменяется на A3, следовательно, ROWS(A3:$A$7) возвращает 5, заставляя INDEX извлекать предпоследний элемент.

Другими словами, ROWS создает своего рода убывающий счетчик для INDEX, чтобы он перемещался от последнего элемента к первому элементу.

Теперь, когда у вас есть два столбца данных, вы можете заменить формулы вычисляемыми значениями, а затем удалить дополнительный столбец. Для этого скопируйте ячейки формулы, выберите ячейки, в которые вы хотите вставить значения, и нажмите Shift+F10, затем V , что является самым быстрым способом применения параметра Excel Special Paste Special > Values.

Как перевернуть столбцы в Excel с помощью VBA

Если у вас есть опыт работы с VBA, вы можете использовать следующий макрос, чтобы изменить порядок данных по вертикали в одном или нескольких столбцах:

Как использовать макрос "Перевернуть столбцы"

Откройте окно Microsoft Visual Basic для приложений ( Alt + F11 ).
Нажмите Вставить >Модуль и вставьте приведенный выше код в окно кода.
Запустите макрос ( F5 ).
Откроется диалоговое окно Перевернуть столбцы, в котором вам будет предложено выбрать диапазон для отражения:

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

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

Как отразить данные в Excel с сохранением форматирования и формул

С помощью описанных выше методов вы можете легко изменить порядок данных в столбце или таблице. Но что, если вы хотите поменять местами не только значения, но и форматы ячеек? Кроме того, что, если некоторые данные в вашей таблице основаны на формулах, и вы хотите, чтобы формулы не нарушались при переключении столбцов? В этом случае вы можете использовать функцию Flip, включенную в Ultimate Suite for Excel.

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

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

Выделив любую ячейку в таблице, перейдите на вкладку Данные Ablebits в группу Преобразование и нажмите «Отразить» > «Отразить по вертикали».
В диалоговом окне Отразить по вертикали настройте следующие параметры:
- В поле Выберите диапазон проверьте ссылку на диапазон и убедитесь, что строка заголовка не включена.
- Выберите параметр "Настроить ссылки на ячейки" и установите флажок "Сохранить форматирование".
- При необходимости выберите Создать резервную копию (выбрано по умолчанию).
- Нажмите кнопку "Перевернуть".

Готово! Порядок данных в таблице изменен на обратный, форматирование сохранено, а ссылки на ячейки в формулах изменены соответствующим образом:

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

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

Как перевернуть строки в Excel

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

Примечание. Если ваши исходные данные содержат формулы, они могут быть повреждены во время операции транспонирования. В этом случае вам придется восстанавливать формулы вручную. Или вы можете использовать инструмент Flip, включенный в наш Ultimate Suite, и он автоматически настроит все ссылки для вас.

Обратный порядок данных по горизонтали с помощью VBA

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

Чтобы добавить макрос в книгу Excel, выполните следующие действия. Как только вы запустите макрос, появится следующее диалоговое окно с просьбой выбрать диапазон:

Вы выбираете всю таблицу, включая строку заголовка, и нажимаете ОК. Через мгновение порядок данных в строках будет обратным:

Отразить данные в строках с помощью Ultimate Suite для Excel

По аналогии с переключением столбцов вы можете использовать наш Ultimate Suite для Excel, чтобы изменить порядок данных в строках. Просто выберите диапазон ячеек, которые вы хотите отразить, перейдите на вкладку Данные Ablebits > группу Преобразование и нажмите «Отразить» > «Отразить по горизонтали».

В диалоговом окне Отразить по горизонтали выберите параметры, подходящие для вашего набора данных. В этом примере мы работаем со значениями, поэтому выбираем Вставить только значения и Сохранить форматирование:

Нажмите кнопку Flip, и ваша таблица будет перевернута слева направо в мгновение ока.

Вот как вы переворачиваете данные в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

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

<р>1. Введите значение 1 в ячейку B1 и значение 2 в ячейку B2.

<р>2. Выберите диапазон B1:B2, щелкните правый нижний угол этого диапазона и перетащите его вниз, в ячейку B8.

<р>3. Щелкните любое число в списке в столбце B.

<р>4. Чтобы отсортировать по убыванию, на вкладке "Данные" в группе "Сортировка и фильтр" нажмите ZA.

Результат. Перевернут не только список в столбце B, но и список в столбце A.

Если у вас Excel 365 или Excel 2021, используйте SEQUENCE, SORTBY и ROWS для сортировки списка в обратном порядке. Следующая формула просто потрясающая.

<р>5. Сначала используйте функцию ПОСЛЕДОВАТЕЛЬНОСТЬ, чтобы сгенерировать список чисел. Функция ПОСЛЕДОВАТЕЛЬНОСТЬ ниже имеет 4 аргумента. Строки = 8, Столбцы = 1, Начало = 1, Шаг = 1.

Примечание: функция ПОСЛЕДОВАТЕЛЬНОСТЬ, введенная в ячейку B1, заполняет несколько ячеек. Ух ты! Такое поведение в Excel 365/2021 называется сбросом.

<р>6. Функция SORTBY сортирует диапазон на основе значений в соответствующем диапазоне. Используйте -1 (третий аргумент) для сортировки в порядке убывания.

<р>7. Вложите функцию SEQUENCE внутрь функции SORTBY.

<р>8. Если у вас есть длинный список, скажем, 20 имен, измените значение 8 на 20 в формуле, показанной выше, или, что еще лучше, используйте функцию СТРОКИ.

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

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

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

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

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

Это руководство охватывает:

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

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

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

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

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

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

После этого можно удалить вспомогательный столбец.

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

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

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

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

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

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

Отразить данные с помощью формул

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

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

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

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

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

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

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

В этом случае функция ROW вернет массив чисел, представляющий номера строк между указанным диапазоном (который в этом примере будет рядом чисел, таких как 2, 3, 4 и т. д.).< /p>

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

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

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

Использование функции ИНДЕКС

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

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

Ниже приведена формула для этого:

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

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

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

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

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

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

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

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

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

Ниже приведена формула, которая сделает это за вас:

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

Чтобы сделать эту формулу динамической, я использовал функцию СТОЛБЦЫ, которая продолжала бы изменять значение столбца с 1 на 2 и на 3 при копировании вправо.

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

Примечание. Когда вы используете формулу для изменения порядка набора данных в Excel, исходное форматирование не сохраняется. Если вам также нужно исходное форматирование отсортированных данных, вы можете либо применить его вручную, либо скопировать и вставить форматирование из исходного набора данных в новый отсортированный набор данных

Обратить данные с помощью VBA

Если вам приходится часто переворачивать данные в Excel, вы также можете попробовать метод VBA.

С помощью кода макроса VBA вы можете один раз скопировать и вставить его в книгу в редакторе VBA, а затем повторно использовать его снова и снова в одной и той же книге.

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

Ниже приведен код VBA, который переворачивает выбранные данные на листе по вертикали.

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

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

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

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

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

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

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

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

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

Выполните следующие действия, чтобы скопировать и вставить этот код в редактор VB:

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

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

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

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

Обратный порядок в Excel — это не что иное, как переворачивание данных, при котором нижнее значение идет вверху, а верхнее значение — внизу.

Например, посмотрите на изображение ниже.

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

Как изменить порядок строк данных в Excel на обратный?

В Excel мы можем сортировать несколькими способами; здесь мы покажем вам все возможные способы изменить порядок в Excel.

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

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

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

Шаг 1. Рассмотрим приведенные ниже данные для этого примера.

Шаг 2. Сначала откройте функцию ИНДЕКС.

Шаг 3. Для массива выберите названия городов из A2: A9 и сделайте их абсолютными ссылками, нажав клавишу F4.

Шаг 4. Затем, чтобы вставить Row Num, откройте функцию ROWS внутри функции INDEX.

Шаг 6. Закройте скобку и нажмите клавишу ввода, чтобы получить результат.

Шаг 7. Перетащите формулу, чтобы получить полный результат.

Код:

Скопируйте этот код в свой модуль.

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

Позвольте мне объяснить вам, как работает этот код. Во-первых, я объявил две переменные, «k» и «LR», как тип данных LONG.

«k» — для циклического просмотра ячеек, а «LR» — для поиска последней использованной строки на листе.

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

Затем я применил цикл FOR NEXT для перебора ячеек в обратном порядке.

Итак, когда мы запускаем этот код, мы получаем следующий результат.

Что нужно помнить

В Excel нет встроенной функции или инструмента для изменения порядка.
Комбинация INDEX + ROWS изменит порядок.
Из всех доступных методов сортировка является лучшим и простым способом сортировки.
Чтобы понимать код VBA, необходимо иметь представление о макросах VBA.