5 способов транспонирования данных в Excel (Пошаговое руководство)

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

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

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

• Использование функции ТРАНСП
• Использование функций ДВССЫЛ, АДРЕС, СТОЛБЕЦ и СТРОКА
• Использование Специальной вставки и Найти и заменить
• Использование функции Power Query

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

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

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

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

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

Шаг 2: Выберите опцию Транспонировать при вставке

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

Результат

Вуаля! Диапазон транспонирован за считанные секунды!

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

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

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

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

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

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

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

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

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

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

Совет: Нажмите "Enter", если вы пользователь Excel 365 или Excel 2021.

=TRANSPOSE(A1:C4)

Результат

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

Поворот данных с использованием функций ДВССЫЛ, АДРЕС, СТОЛБЕЦ и СТРОКА

Хотя вышеуказанная формула довольно проста для понимания и использования, ее недостатком является то, что вы не сможете редактировать или удалять какие-либо ячейки в повернутой таблице. Поэтому я представлю формулу, использующую функции "ДВССЫЛ", "АДРЕС", "СТОЛБЕЦ" и "СТРОКА". Допустим, ваша исходная таблица находится в диапазоне 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", а затем замените "=" на "@EO" (или любые символы, которых нет в выбранном диапазоне) в диалоговом окне Найти и заменить.

   

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

   

Шаг 4: Транспонируйте замененный результат Вставить связь

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

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

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

   

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

Результат

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

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

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

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

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

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

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

   

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

   

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

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

Результат

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

Связанные статьи

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