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

Узнайте, как переворачивать ячейки и переключать строки в столбцы или столбцы в строки

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

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

Эти инструкции применяются к Microsoft Excel 2010, 1013, 2016, 2019 и Excel для Office 365.

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

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

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

1. Нажмите правой кнопкой мыши столбец A и выберите Вставить , чтобы добавить новый столбец слева от столбца продавца, который вы хотите отсортировать.

2. Введите 1 в первой ячейке (строка 2). Введите 2 во вторую ячейку (строка 3).

3. Удерживая нажатой клавишу Cntrl , поместите угол мыши в нижний правый угол ячейки с цифрой 2. Курсор изменится на две горизонтальные линии. Дважды щелкните левой кнопкой мыши, чтобы автоматически заполнить оставшуюся часть столбца до имени последнего продавца. Это автоматически заполнит оставшуюся часть столбца числами от 3 до 8.

4. Выделите всю таблицу.

5. В меню Главная выберите Сортировать и фильтровать на ленте. Выберите Пользовательская сортировка .

6. Установите для Сортировать по значение Столбец А , для Сортировать по значение Значения ячеек и Порядок . От самого большого до самого маленького . Нажмите ОК .

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

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

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

Как поменять ячейки строк в Excel

Что делать, если вы хотите видеть данные о продажах с декабря по январь, а не с января по декабрь?

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

Сначала добавьте новую строку прямо под заголовком и пронумеруйте эти ячейки от 1 (до января) до 12 (до декабря).

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

1. Выделите только столбцы от A до M.

2. В меню Главная выберите Сортировать и фильтровать на ленте. Выберите Пользовательская сортировка .

3. Нажмите Параметры и выберите Сортировать слева направо .

4. Установите для Сортировать по значение Строка 2 , для Сортировать по значение Значения ячеек и Порядок . От самого большого до самого маленького . Нажмите ОК .

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

Теперь щелкните левой кнопкой мыши на строке 2 и удалите пронумерованную строку.

Поменяйте местами только две колонки или строки

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

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

Вот как вы это делаете:

1. Выделите весь ряд с Джули Мур в столбце А.

2. Удерживая нажатой клавишу Shift , переместите курсор мыши к верхнему краю ячейки Джули Мур. Курсор мыши изменится на перекрестие.

3. Удерживая нажатой клавишу Shift , перетащите мышь к верхнему краю ячейки Джона Хадсона, пока линия чуть выше этой строки не будет выделена темной линией.

4. Когда вы отпустите левую кнопку мыши, две строки поменяются местами.

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

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

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

Как поменять местами столбцы и строки

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

Большинство людей предпочитают делать это вручную, даже не подозревая, что в Excel есть встроенная функция «transpose», которая сделает это за вас.

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

1. Выделите весь диапазон ячеек для всей таблицы данных о продажах. Нажмите Cntrl-C , чтобы скопировать весь диапазон.

2. Нажмите на новый лист, который вы создаете. Щелкните правой кнопкой мыши вызов A1 и выберите параметр Транспонировать в разделе Параметры вставки .

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

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

Используйте VBA, чтобы поменять строки или столбцы (Дополнительно)

VBA – это мощная функция в Excel. На самом деле вы можете выполнить каждую из перечисленных выше задач с помощью очень простого кода VBA.

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

Перевернуть столбцы или строки

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

Чтобы создать этот код, выберите меню Разработчик и выберите Просмотреть код .

Если Разработчик не указан в меню, вы можете добавить его. Нажмите Файл , нажмите Параметры и выберите Настроить ленту . В этом окне найдите Разработчик на левой панели и добавьте его справа. Нажмите ОК , и появится пункт меню «Разработчик».

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

 Sub Flip_Rows () 
 Dim vTop как вариант 
 Dim vEnd как вариант 
 Dim iStart как целое число 
 Dim iEnd как целое число 
 Application.ScreenUpdating = False 
 iStart = 1 
 iEnd = Selection.Rows.Count 
 Сделать, пока iStart  vTop = Selection.Rows (iStart) 
 vEnd = Selection.Rows (iEnd) < br /> Selection.Rows (iEnd) = vTop 
 Selection.Rows (iStart) = vEnd 
 iStart = iStart + 1 
 iEnd = iEnd - 1 
 Loop 
 Application.ScreenUpdating = True 
 End Sub 
 

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

 Sub Flip_Columns () 
 Dim vLeft как вариант 
 Dim vRight как вариант 
 Dim iStart как целое число 
 Dim iEnd как целое число 
 Application.ScreenUpdating = False 
 iStart = 1 
 iEnd = Selection.Columns.Count 
 Делать пока iStart  vTop = Selection.Columns (iStart) 
 vEnd = Selection.Columns (iEnd) < br /> Selection.Columns (iEnd) = vRight 
 Selection.Columns (iStart) = vLeft 
 iStart = iStart + 1 
 iEnd = iEnd - 1 
 Loop 
 Application.ScreenUpdating = True 
 End Sub 
 

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

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

Поменяйте местами две колонки или строки

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

 Sub Swap () 
 Для i = 1 в Selection.Areas (1) .Count 
 temp = Selection.Areas (1) (i) 
 Selection.Areas (1) ( i) = Selection.Areas (2) (i) 
 Selection.Areas (2) (i) = temp 
 Next i 
 End Sub 
 

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

Транспонировать весь диапазон

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

Для этого даже есть метод в VBA:

 Set DestRange = Application.WorksheetFunction.Transpose (SelectedRange) 

Однако сделать это в VBA – гораздо больше работы, чем стоит, поскольку, как вы видели ранее в этой статье, транспонирование всего диапазона – это не более, чем копирование и вставка этого диапазона всего несколькими щелчками мыши.

Перелистывание столбцов и строк в Excel

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

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