Как отразить данные в столбцах и строках Excel (по вертикали и по горизонтали)

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

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

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

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

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

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

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

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

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

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

Совет: Как быстро заполнить столбец порядковыми номерами

Самый быстрый способ заполнить столбец последовательностью чисел — использовать функцию автозаполнения Excel:

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

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

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

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

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

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

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

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

ИНДЕКС(диапазонСТРОКИ(диапазон))

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

=ИНДЕКС($A$2:$A$7,СТРОКИ(A2:$A$7))

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

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

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

В массив вы загружаете весь список, который хотите перевернуть (в этом примере 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 > Values.

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

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

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

Dim Rng As Range Dim WorkRng As Range Dim Arr As Variant Dim i As Integer, j As Integer, k As Integer On Error Resume Next xTitleId = “Отразить столбцы по вертикали” Set WorkRng = Application.Selection Set WorkRng = Application.InputBox(“Range “, xTitleId, WorkRng.Address, Type:=8) Arr = WorkRng.Formula Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For j = 1 To UBound(Arr, 2) k = UBound(Arr, 1) For i = 1 To UBound(Arr, 1) / 2 xTemp = Arr(i, j) Arr(i, j) = Arr(k, j) Arr(k, j) = xTemp k = k – 1 Next Next WorkRng.Formula = Arr Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Используйте функцию «Специальная вставка» > «Транспонировать», чтобы преобразовать столбцы в строки. В результате ваша таблица претерпит такое преобразование:
   
2. Добавьте вспомогательный столбец с номерами, как в самом первом примере, а затем выполните сортировку по вспомогательному столбцу. Ваш промежуточный результат будет выглядеть примерно так:
   
3. Использовать Специальная вставка > Транспонировать еще раз, чтобы повернуть таблицу назад:
   

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

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

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

Sub FlipDataHorizontally() Dim Rng As Range Dim WorkRng As Range Dim Arr As Variant Dim i As Integer, j As Integer, k As Integer On Error Resume Next xTitleId = “Flip Data Horizontally” Set WorkRng = Application.Selection Set WorkRng = Application. InputBox(“Range”, xTitleId, WorkRng.Address, Type:=8) Arr = WorkRng.Formula Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For i = 1 To UBound(Arr, 1) k = UBound(Arr, 2 ) Для j = 1 To UBound(Arr, 2) / 2 xTemp = Arr(i, j) Arr(i, j) = Arr(i, k) Arr(i, k) = xTemp k = k – 1 Next Next WorkRng.Formula = Arr Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub

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

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

Переворачивание данных в строках с помощью Ultimate Suite for Excel

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

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

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

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

Вас также могут заинтересовать