Транспонировать таблицу в Excel

Обновлено: 21.11.2024

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

Например, если ваши данные выглядят так: регионы продаж указаны вверху, а кварталы — слева:

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

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

Примечание. Для этого обязательно скопируйте данные. Использование команды «Вырезать» или CONTROL+X не работает.

Выделите первую ячейку, в которую вы хотите вставить данные, и на вкладке "Главная" нажмите стрелку рядом с кнопкой "Вставить", а затем нажмите "Транспонировать".

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

После успешного чередования данных исходные данные можно удалить.

Советы по переносу данных

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

Если ваши данные находятся в таблице Excel, функция транспонирования будет недоступна. Вы можете сначала преобразовать таблицу в диапазон, нажав кнопку Преобразовать в диапазон на вкладке Таблица, или вы можете использовать функцию ТРАНСП, чтобы повернуть строки и столбцы.

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

Иногда вам нужно переключать или вращать ячейки. Вы можете сделать это, скопировав, вставив и используя опцию Transpose. Но при этом создаются дублированные данные. Если вы этого не хотите, вы можете вместо этого ввести формулу, используя функцию ТРАНСП. Например, на следующем рисунке формула =ТРАНСП(A1:B4) берет ячейки с A1 по B4 и упорядочивает их по горизонтали.

Примечание. Если у вас установлена текущая версия Microsoft 365, вы можете ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введите формулу в верхнюю левую ячейку выходного диапазона, а затем нажмите Ctrl+Shift+Enter для подтверждения. Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массивов см. в разделе Рекомендации и примеры формул массивов.

Шаг 1. Выберите пустые ячейки

Сначала выберите несколько пустых ячеек. Но обязательно выберите то же количество ячеек, что и исходный набор ячеек, но в другом направлении. Например, здесь 8 ячеек, расположенных вертикально:

Итак, нам нужно выделить восемь горизонтальных ячеек, вот так:

Здесь окажутся новые транспонированные ячейки.

Шаг 2. Введите =ТРАНСП(

Не снимая выделения с пустых ячеек, введите: =ТРАНСПОЗА(

Excel будет выглядеть примерно так:

Обратите внимание, что восемь ячеек по-прежнему выделены, несмотря на то, что мы начали вводить формулу.

Шаг 3. Введите диапазон исходных ячеек.

Теперь введите диапазон ячеек, которые вы хотите транспонировать. В этом примере мы хотим перенести ячейки из A1 в B4.Таким образом, формула для этого примера будет следующей: =ТРАНСП(A1:B4) -- но пока не нажимайте клавишу ВВОД! Просто перестаньте печатать и перейдите к следующему шагу.

Excel будет выглядеть примерно так:

Шаг 4. Наконец, нажмите CTRL+SHIFT+ENTER

Теперь нажмите CTRL+SHIFT+ENTER. Почему? Поскольку функция ТРАНСП используется только в формулах массива, именно так вы завершаете формулу массива. Короче говоря, формула массива — это формула, которая применяется более чем к одной ячейке. Поскольку на шаге 1 вы выбрали более одной ячейки (вы это сделали, не так ли?), формула будет применена более чем к одной ячейке. Вот результат после нажатия CTRL+SHIFT+ENTER:

Вам не нужно вводить диапазон вручную. После ввода =ТРАНСП( вы можете использовать мышь, чтобы выбрать диапазон. Просто щелкните и перетащите от начала диапазона к концу. Но помните: нажмите CTRL+SHIFT+ENTER, когда закончите, а не ENTER сам по себе.

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

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

Технические детали

Функция ТРАНСП возвращает диапазон ячеек по вертикали как диапазон по горизонтали или наоборот. Функцию ТРАНСП нужно вводить как формулу массива в диапазоне, который имеет такое же количество строк и столбцов, соответственно, как исходный диапазон имеет столбцы и строки. Используйте ТРАНСП, чтобы сместить вертикальную и горизонтальную ориентацию массива или диапазона на листе.

Синтаксис

Синтаксис функции ТРАНСП имеет следующий аргумент:

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

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

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

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

Примечание. Если ваши данные находятся в таблице Excel, функция транспонирования будет недоступна. Вы можете сначала преобразовать таблицу в диапазон или использовать функцию ТРАНСП, чтобы повернуть строки и столбцы.

Вот как это сделать:

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

Примечание. Для этого обязательно скопируйте данные, так как использование команды «Вырезать» или Ctrl+X не сработает.

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

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

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

Советы по переносу данных

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

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

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

Вот как можно транспонировать содержимое ячейки:

Скопируйте диапазон ячеек.

Выберите пустые ячейки, в которые вы хотите вставить транспонированные данные.

На вкладке "Главная" нажмите значок "Вставить" и выберите "Вставить транспонирование".

Нужна дополнительная помощь?

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

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

Способ 1: специальная вставка

Это самый простой и универсальный способ. Рассмотрим сразу на примере. У нас есть таблица с ценой определенного продукта за штуку и с определенным количеством продуктов. Шапка таблицы расположена горизонтально, а данные расположены соответственно вертикально. Стоимость рассчитывается по формуле: цена * количество. Для наглядности заголовок таблицы выделен зеленым цветом.

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

Чтобы транспонировать таблицу, мы будем использовать команду SPECIAL INSERT. Мы выполняем следующие шаги:

Выделяем всю таблицу и копируем ее (CTRL+C).
Мы помещаем курсор в любое место листа Excel и щелкаем правой кнопкой мыши меню.
Нажимаем на команду "Специальная вставка" (CTRL+ALT+V).
В появившемся окне ставим галочку возле ТРАНСП. Остальное мы оставили как есть и нажимаем ОК.

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

Способ 2: функция ТРАНСП в Excel

С появлением SPECIAL INSERT транспонирование таблицы с помощью команды TRANSPOSE почти не используется из-за сложности и большего времени выполнения операции. Но функция ТРАНСП по-прежнему присутствует в Excel, так что мы научимся ею пользоваться.

Мы снова работаем на этапах:

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

Метод 3: сводная таблица

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

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

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

Рано или поздно вам понадобится и Excel!

В этом посте я покажу вам 3 простых способа сделать это в Excel.

Видеоруководство

Что означает транспонирование данных?

Транспонировать буквально означает заставить две или более вещи поменяться местами друг с другом.

При работе с данными это означает, что вы хотите преобразовать строки в столбцы и преобразовать столбцы в строки, как в приведенном выше примере.

По сути, это означает отражение данных по диагональной оси, идущей из верхнего левого угла в нижний правый угол.

Почему вы можете захотеть перенести свои данные

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

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

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

Транспонирование данных с помощью специальной команды «Вставить»

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

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

Скопируйте данные, которые вы хотите транспонировать.

Выберите данные, которые хотите транспонировать.
Перейдите на вкладку "Главная".
Нажмите команду "Копировать".

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

Вставьте данные с помощью команды транспонирования.

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

Это приведет к вставке транспонированных данных на лист.

Вы также можете выполнить эту команду транспонирования из меню "Специальная вставка". После копирования данных вы можете использовать сочетание клавиш Ctrl + Alt + V, чтобы открыть меню «Специальная вставка».

Отметьте параметр Transpose, затем нажмите кнопку OK, чтобы вставить транспонированные данные.

Транспонирование данных с помощью функции ТРАНСП

На самом деле в Excel есть функция для переноса данных, но до появления динамических массивов в Microsoft 365 эта функция была не такой уж интересной.

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

Синтаксис

Диапазон. Это диапазон данных, которые вы хотите транспонировать.

Функция ТРАНСП очень проста. Он принимает один аргумент, который представляет собой диапазон данных, которые вы хотите транспонировать.

Функция TRANSPOSE как динамический массив

Если вы используете функцию ТРАНСП в Microsoft 365, ее очень легко использовать, и результаты будут автоматически перенесены в виде массива значений.

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

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

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

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

Функция TRANSPOSE с Ctrl Shift Enter

Если у вас нет динамических массивов, то можно использовать функцию ТРАНСП для получения диапазона транспонированных данных, но это сложнее и менее гибко для изменения.

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

Как правило, если ваши данные состоят из N строк и M столбцов, перед вводом формулы необходимо выбрать диапазон из M строк и N столбцов.

Вместо обычного нажатия Enter для ввода формулы нажмите Ctrl + Shift + Enter.

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

Они не гибкие, и если исходные данные вырастут, вам нужно будет удалить формулу массива и ввести ее заново с новым увеличенным диапазоном.

Транспонирование данных с помощью Power Query

Это метод выбора, если вам нужно повторно получать данные из какого-либо источника и транспонировать их.

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

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

Это откроет мощный редактор запросов.

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

Перейдите на вкладку "Преобразование".
Нажмите на нижнюю часть команды "Использовать первую строку как заголовки", чтобы открыть дополнительные параметры.
Выберите команду «Использовать заголовки в качестве первой строки».

В редакторе запросов Power перейдите на вкладку Transform и выберите команду Transpose.

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

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

Теперь данные полностью перенесены, и вы можете перейти на вкладку "Главная", а затем "Закрыть" и "Загрузить данные" на лист Excel по вашему выбору.

Преимущество этой опции в том, что вы можете легко добавлять/удалять строки и столбцы в/из исходной таблицы. Когда вы нажмете кнопку «Обновить» на вкладке «Данные», эти изменения будут отражены в транспонированном выводе запроса мощности.

Выводы

Ну вот, 3 простых способа транспонировать данные в Microsoft Excel!

С тех пор как появились динамические массивы, я предпочитаю использовать функцию ТРАНСП. Это быстро, просто и автоматически обновляется при изменении исходных данных.

Есть ли у вас любимый метод или вы знаете метод, которого нет в списке? Дайте мне знать в комментариях!

Об авторе

Джон МакДугалл

Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.

Читайте также: