Как транспонировать матрицу в Excel

Обновлено: 21.11.2024

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

Описание

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

Функция ТРАНСП — это встроенная функция Excel, относящаяся к категории Функция поиска/справки. Его можно использовать как функцию рабочего листа (WS) в Excel. В качестве функции рабочего листа функцию ТРАНСП можно ввести как часть формулы в ячейку рабочего листа.

Синтаксис

Синтаксис функции ТРАНСП в Microsoft Excel:

Параметры или аргументы

Возврат

Функция ТРАНСП возвращает транспонированный диапазон ячеек.

  • Значение диапазона в функции ТРАНСП необходимо вводить в виде массива. Чтобы ввести массив, введите значение и нажмите Ctrl + Shift + Enter. Формула будет помещена в квадратные скобки <>, указывая, что это массив.

Применимо к

  • Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Тип функции

Пример (как функция рабочего листа)

Давайте рассмотрим несколько примеров функции ТРАНСП в Excel и узнаем, как использовать функцию ТРАНСП в качестве функции рабочего листа в Microsoft Excel:

На основе приведенной выше таблицы Excel:

Мы разместили значения в ячейках A1, A2 и A3 и хотим просмотреть эти значения в ячейках C1, D1 и E1 (транспонированные). Для этого вы выделяете ячейки C1, D1 и E1, а затем вводите следующую формулу:

Затем нажмите Ctrl + Shift + Enter, чтобы создать формулу массива. Вы заметите, что вокруг формулы появятся квадратные скобки <>, а значения в ячейках A1, A2 и A3 теперь должны появиться в ячейках C1, D1 и E1.

Часто задаваемые вопросы

Вопрос: не проще ли в Microsoft Excel использовать параметр транспонирования "Специальная вставка", чтобы сделать то же самое?

Ответ: Да, если вы ТОЛЬКО хотите выполнить однократную вставку значений, вы можете сделать следующее:

Выделите ячейки, которые хотите скопировать. В этом примере мы выделили ячейки A1: A3. Затем щелкните правой кнопкой мыши и выберите «Копировать» во всплывающем меню.

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

Затем установите флажок ТРАНСПОЗИРОВАТЬ и нажмите кнопку ОК.

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

Функция ТРАНСПОЗИЦИЯ в Excel "меняет" ориентацию заданного диапазона или массива: ТРАНСПОЗИРУЕТ вертикальный диапазон на горизонтальный и горизонтальный на вертикальный.

  • массив — массив или диапазон ячеек для транспонирования.

Функция ТРАНСП преобразует диапазон ячеек по вертикали в диапазон ячеек по горизонтали или диапазон ячеек по горизонтали в диапазон ячеек по вертикали. Другими словами, ТРАНСП "меняет" ориентацию заданного диапазона или массива:

  1. При указании диапазона по вертикали функция ТРАНСП преобразует его в диапазон по горизонтали.
  2. При задании диапазона по горизонтали функция ТРАНСП преобразует его в диапазон по вертикали.

При транспонировании массива первая строка становится первым столбцом нового массива, вторая строка становится вторым столбцом нового массива, третья строка становится третьим столбцом нового массива и так далее.

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

Примеры

При задании вертикального массива функция ТРАНСП возвращает горизонтальный массив:

Чтобы преобразовать вертикальный диапазон A1:A5 в горизонтальный массив:

Чтобы преобразовать горизонтальный диапазон A1:E1 в вертикальный массив:

Примечание. TRANSPOSE не переносит форматирование. В показанном примере целевые диапазоны были отформатированы на отдельном шаге.

ТРАНСПОНИРОВАТЬ с другими функциями

ТРАНСПОЗИРОВКА может использоваться для "перехвата" и переноса вывода другой функции. Приведенная ниже формула изменяет результат XLOOKUP из горизонтальной конфигурации в вертикальную:

Excel 365

В Excel 365, который поддерживает формулы динамического массива, специальный синтаксис не требуется, просто работает ТРАНСП, и результаты автоматически переносятся в целевые ячейки. Однако в других версиях Excel ТРАНСП необходимо вводить как формулу массива с несколькими ячейками с помощью клавиш CTRL + SHIFT + ВВОД:

  1. Сначала выберите целевой диапазон, в котором должно быть столько же строк, сколько столбцов в исходном диапазоне, и столько же столбцов, сколько строк в исходном диапазоне.
  2. Войдите в функцию ТРАНСП и выберите исходный диапазон в качестве аргумента массив.
  3. Подтвердите формулу как формулу массива, нажав Ctrl + Shift + Enter.

Специальная вставка

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

В этих примерах функции ТРАНСПОЗИРОВКА в Excel показано, как можно инвертировать данные, изменяя вертикальные диапазоны на горизонтальные или наоборот. Есть видео, письменные инструкции и образец файла для бесплатной загрузки.

Видео: функция ТРАНСП

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

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

Использование слова ТРАНСП

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

  • изменить горизонтальные данные на вертикальные в другом месте на листе
  • комбинировать ТРАНСПОЗИРОВКУ с другими функциями Excel, чтобы показать общую зарплату человека за пять лучших лет подряд

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

ТРАНСПОЗИРОВАТЬ Синтаксис

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

  • ТРАНСП(массив)
    • массив – это массив или диапазон ячеек, которые нужно транспонировать.

    ТРАНСПОНИРОВАТЬ Ловушки

    Функция ТРАНСП имеет несколько ловушек, если только вы не используете более новые версии Excel с функциями динамического массива:

    • ТРАНСП необходимо вводить как формулу массива, нажав Ctrl+Shift+Enter.
    • Целевой диапазон TRANSPOSE должен иметь такое же количество строк и столбцов, соответственно, сколько столбцов и строк в массиве.

    Формулы динамического массива

    Для первого примера, показанного ниже, есть 2 набора инструкций

    1. Для версий Excel с формулами динамического массива
    2. Для версий Excel без формул динамического массива

    Чтобы проверить, есть ли в вашей версии Excel формулы динамического массива:

    • Выберите пустую ячейку и введите: =SO
    • Если во всплывающей подсказке отображается SORT и SORTBY, у вас есть новые функции.

    Пример 1. Изменение ориентации

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

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

    1) С формулами динамического массива

    Чтобы транспонировать горизонтальный диапазон из 2 строк на 4 столбца в вертикальный диапазон из 4 строк на 2 столбца, выполните следующие действия:

    1. Выберите верхнюю левую ячейку в диапазоне, где вы хотите отобразить данные по вертикали (в данном примере это ячейка B4).
    2. Введите эту формулу:
      • =ТРАНСПОНИРОВАТЬ(B1:E2)
    3. Нажмите Enter, чтобы завершить формулу.
    4. Формула будет автоматически перенесена в соседние ячейки (B4:C7) для переноса данных. Вокруг диапазона есть голубая рамка.

    ПРИМЕЧАНИЕ. Чтобы отредактировать формулу, выберите ячейку, в которой она была первоначально введена — B4. В других ячейках формула будет отображаться в строке формул, но изменить ее можно только в верхней левой ячейке.

    2) Без формул динамического массива

    Чтобы транспонировать горизонтальный диапазон из 2 строк на 4 столбца в вертикальный диапазон из 4 строк на 2 столбца, выполните следующие действия:

    1. Выберите 8 ячеек, в которых вы хотите отображать данные по вертикали. В данном примере это ячейки B4:C7.
      • ВАЖНО: при вводе формулы ТРАНСП необходимо выбрать весь целевой диапазон.
    2. Введите эту формулу:
      • =ТРАНСПОНИРОВАТЬ(B1:E2)
    3. Нажмите Ctrl+Shift+Enter, чтобы массив ввел формулу.
    4. Фигурные скобки автоматически добавляются в начале и в конце формулы, чтобы показать, что это введенный массив.

    ПРИМЕЧАНИЕ. Чтобы отредактировать формулу, выделите все ячейки, в которые она была первоначально введена — B4:C7.

    .

    Пример 2: Альтернатива ТРАНСПОЗИЦИИ

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

    1. Выберите исходные данные и скопируйте их.
    2. Выберите верхнюю левую ячейку диапазона назначения.
    3. На вкладке "Главная" ленты нажмите стрелку раскрывающегося списка "Вставить".
    4. Нажмите "Транспонировать".
    5. (необязательно) Удалить исходные данные.

    Пример 3: Общая заработная плата

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

    Оно было опубликовано Харланом Гроувом в группах новостей Excel в ходе обсуждения расчета общей заработной платы за лучшие 5 лет подряд.

    =MAX(MMULT(A8:J8,–(ABS(ТРАНСП(ТРАНСП(COLUMN(A8:J8))
    -COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)) -Число+1))
    -(Число-1)/2)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Вы также можете выполнить эту команду транспонирования из меню "Специальная вставка". После копирования данных вы можете использовать сочетание клавиш 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 и выполнить преобразование.

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

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

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

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

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

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

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

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

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

    Выводы

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

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

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

    Об авторе

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

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

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