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

Переключение столбцов на строки с помощью специальной вставки

Транспонировать и связать данные с источником

• С функцией ТРАНСП
• С функциями INDIRECT, ADDRESS, COLUMN и ROW
• Со специальной вставкой и поиском и заменой
• Доступно Power Query

Скачать примеры транспонированных данных (.xlsx)

Видео: транспонирование данных в Excel

Переключение столбцов на строки с помощью специальной вставки

Шаг 1: Скопируйте диапазон для транспонирования

Выберите диапазон ячеек, в которых вы хотите поменять местами строки и столбцы, а затем нажмите Ctrl + C чтобы скопировать диапазон.

Шаг 2. Выберите параметр «Вставить транспонирование».

Щелкните правой кнопкой мыши первую ячейку целевого диапазона, а затем щелкните значок транспонировать значок (В соответствии с Параметры вставки) из контекстного меню.

Результат

Вуаля! Ассортимент транспонируется в мгновение ока!

Транспонировать и связать данные с источником

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

• Функция ТРАНСПОРТ (Прост в использовании, но результаты не редактируются)
• Функции НЕПРЯМОЙ, АДРЕС, КОЛОННА и СТРОКА (Большая формула, но позволяет вносить изменения в результаты)
• Вставить специальный и найти и заменить (сложно, но понятно)
• Power Query (Легко с форматированием результатов в виде таблицы)

Изменить строки на столбцы с помощью функции ТРАНСП

Чтобы превратить строки в столбцы и наоборот с помощью ТРАНСПОНИРОВАНИЕ функции, пожалуйста, сделайте следующее.

Шаг 1: выберите то же количество пустых ячеек, что и исходный набор ячеек, но в другом направлении.

Наконечник: Пропустите этот шаг, если вы используете Excel 365 или Excel 2021.

Допустим, ваша исходная таблица находится в диапазоне A1: C4, что означает, что в таблице 4 строки и 3 столбца. Итак, транспонированная таблица будет иметь 3 строки и 4 столбца. Это означает, что вы должны выбрать 3 строки и 4 столбца пустых ячеек.

Шаг 2: введите формулу ТРАНСП

Введите приведенную ниже формулу в строке формул, а затем нажмите Shift + Ctrl + Enter чтобы получить результат.

Наконечник: Нажмите Enter если вы являетесь пользователем Excel 365 или Excel 2021.

=TRANSPOSE(A1:C4)

Результат

Строки превращаются в столбцы, а столбцы — в строки.

Поворот данных с использованием функций INDIRECT, ADDRESS, COLUMN и ROW

Хотя приведенная выше формула довольно проста для понимания и использования, недостаток ее в том, что вы не могли редактировать или удалять какие-либо ячейки в повернутой таблице. Итак, я введу формулу, используя КОСВЕННЫЕ, АДРЕС, КОЛОНКА и РЯД функции. Допустим, ваша исходная таблица находится в диапазоне A1: C4, чтобы выполнить преобразование столбца в строку и сохранить связь повернутых данных с исходным набором данных, выполните следующие действия.

Шаг 1: Введите формулу

Введите приведенную ниже формулу в самую верхнюю левую ячейку диапазона назначения (A6 в нашем случае) и нажмите Enter:

=INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))

Шаг 2. Скопируйте формулу в правую и нижнюю ячейки

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

Результат

Столбцы и строки переключаются сразу.

Преобразование столбцов в строки с помощью специальной вставки и поиска и замены

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

Шаг 1: Скопируйте диапазон для транспонирования

Выберите диапазон ячеек, которые вы хотите транспонировать, а затем нажмите Ctrl + C чтобы скопировать диапазон.

Шаг 2. Примените параметр «Вставить ссылку».

1. Щелкните правой кнопкой мыши пустую ячейку и выберите Специальная вставка.

   

2. Нажмите на вставить ссылку.

   

Вы получите результат, как показано ниже:

Шаг 3. Найдите и замените знаки равенства (=) в результате вставки ссылки.

1. Выберите диапазон результатов (A6: C9) и нажмите Ctrl + H, а затем заменить = @ЭО (или любые символы, которые не существуют в выбранном диапазоне) в Найти и заменить Диалог.

   

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

   

Шаг 4. Транспонируйте замененный результат «Вставить ссылку».

Выберите диапазон (A6: C9) и нажмите Ctrl + C скопировать его. Щелкните правой кнопкой мыши пустую ячейку (здесь я выбрал A11) и выберите транспонировать значок от Параметры вставки чтобы вставить транспонированный результат.

Шаг 5: Верните знаки равенства (=), чтобы связать транспонированный результат с исходными данными.

1. Сохранить транспонированные данные результата A11: D13 выбран, а затем нажмите Ctrl + Hи заменить @ЭО = (противоположно шаг 3).

   

2. Нажмите на Заменить все, а затем закройте диалоговое окно.

Результат

Данные транспонируются и связываются с исходными ячейками.

Транспонируйте и свяжите данные с источником с помощью Power Query

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

Шаг 1: Выберите диапазон для транспонирования и откройте Power Query редактор

Выберите диапазон данных для транспонирования. А потом, на Данные в Получить и преобразовать данные группу, нажмите Из таблицы/диапазона.

Шаг 2. Преобразуйте столбцы в строки с помощью Power Query

1. Перейдите в Transform Вкладка. в Использовать первую строку в качестве заголовков в раскрывающемся меню выберите Используйте заголовки в качестве первой строки.

   

2. Нажмите на транспонировать.

   

Шаг 3: Сохраните транспонированные данные на лист

На Файл вкладку нажмите Закрыть и загрузить чтобы закрыть окно Power Editor и создать новый рабочий лист для загрузки транспонированных данных.

Результат

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

Статьи по теме

• Как быстро транспонировать диапазоны и пропускать пустые ячейки в Excel?
• Когда мы вставляем диапазон как транспонированный, пустые ячейки также будут вставлены. Однако иногда мы просто хотим транспонировать диапазоны, игнорируя пустые ячейки, как показано на снимке экрана ниже. Есть ли способы быстро транспонировать диапазоны и пропустить пробелы в Excel?
• Как перенести каждые 5 или n строк из одного столбца в несколько столбцов?
• Предположим, у вас есть длинные данные в столбце A, и теперь вы хотите транспонировать каждые 5 строк из столбца A в несколько столбцов, например транспонировать A1: A5 в C6: G6, A6: A10 в C7: G7 и т. Д. показан следующий снимок экрана. Как вы могли бы справиться с этой задачей без многократного копирования и вставки в Excel?
• Как транспонировать / преобразовать столбцы и строки в один столбец?
• При использовании листа Excel иногда возникает проблема: как преобразовать или транспонировать диапазон данных в один столбец? (См. Следующие скриншоты :) Теперь я предлагаю вам три быстрых приема для решения этой проблемы.
• Как транспонировать / преобразовать столбцы и строки в одну строку?
• Как объединить несколько строк и столбцов в одну длинную строку? Возможно, вам это кажется легким, потому что вы можете копировать их по одному и соединять в ряд вручную. Однако это может занять много времени и утомительно, если есть сотни строк и столбцов. Здесь я расскажу о некоторых быстрых трюках, чтобы решить эту проблему.