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 Query» и автоматически создать новый лист с транспонированными данными.

Результат

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

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

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