Функция ТРАНСП() — Транспонирование диапазонов ячеек в EXCEL
Функция ТРАНСП() , в анлийском варианте TRANSPOSE(), преобразует вертикальный диапазон ячеек в горизонтальный и наоборот. Научимся транспонировать (поворачивать) столбцы, строки и диапазоны значений.
Синтаксис функции
ТРАНСП ( массив )
Массив — массив (диапазон ячеек) на рабочем листе, который нужно транспонировать. Транспонирование массива заключается в том, что первая строка массива становится первым столбцом нового массива, вторая — вторым столбцом и т. д.
Транспонирование вертикальных диапазонов ячеек (столбцов)
Пусть дан столбец с пятью заполненными ячейками B2:B6 (в этих ячейках могут быть константы или формулы). Сделаем из него строку. Строка будет той же размерности (длины), что и столбец (см. Файл примера ).
- выделим строку длиной 5 ячеек;
- в Строке формул введем =ТРАНСП(B2:B6) ;
- нажмем CTRL+SHIFT+ENTER .
Транспонирование горизонтальных диапазонов ячеек (строк)
Пусть дана строка с пятью заполненными ячейками B 10: F 10 (в этих ячейках могут быть константы или формулы). Сделаем из нее столбец. Столбец будет той же размерности (длины), что и строка.
- выделим столбец длиной 5 ячеек;
- в Строке формул введем =ТРАНСП(B10:F10) ;
- Нажмем CTRL+SHIFT+ENTER .
Транспонирование диапазонов ячеек
Транспонирование диапазонов ячеек аналогично транспонированию одномерных диапазонов, единственное нужно помнить о размерности: число строк нового транспонированного диапазона должно совпадать с числом столбцов исходного диапазона, а число столбцов должно совпадать с числом строк исходного диапазона.
СОВЕТ: Транспонирование можно осуществить и обычными формулами: см. статью Транспонирование таблиц .
Транспонирование с помощью Специальной вставки
Также транспонирование диапазонов значений можно осуществить с помощью Специальной вставки ( Главная/ Буфер обмена/ Вставить/ Транспонировать ). Для этого выделите исходный диапазон.
Выделите левую верхнюю ячейку диапазона, в который нужно поместить транспонированный диапазон. Выберите пункт меню Главная/ Буфер обмена/ Вставить/ Транспонировать . В результате получим транспонированный диапазон.
ТРАНСП (функция ТРАНСП)
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Иногда требуется изменить направление, в котором располагаются ячейки. Это можно сделать путем копирования и вставки и применения команды «Транспонировать». Но в этом случае образуются повторяющиеся данные. Чтобы такого не происходило, можно вместо этого ввести формулу с функцией ТРАНСП. Например, на следующем изображении показано, как расположить горизонтально ячейки с A1 по B4 с помощью формулы =ТРАНСП(A1:B4).
Примечание: Если у вас есть текущая версия Microsoft 365, вы можете ввести формулу в левую верхнюю ячейку диапазона вывода, а затем нажать ввод, чтобы подтвердить формулу как формулу динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Шаг 1. Выделите пустые ячейки
Сначала выделите пустые ячейки. Их число должно совпадать с числом исходных ячеек, но располагаться они должны в другом направлении. Например, имеется 8 ячеек, расположенных по вертикали:
Нам нужно выделить 8 ячеек по горизонтали:
Так будут располагаться новые ячейки после транспонирования.
Шаг 2. Введите =ТРАНСП(
Не снимая выделение с пустых ячеек, введите =ТРАНСП(
Лист Excel будет выглядеть так:
Обратите внимание на то, что восемь ячеек по-прежнему выделены даже несмотря на то, что мы начали вводить формулу.
Шаг 3. Введите исходный диапазон ячеек
Теперь введите диапазон ячеек, которые нужно транспоннять. В этом примере мы хотим транспоннять ячейки с A1 по B4. Поэтому формула для этого примера будет такой: =ТРАНСП(A1:B4) — но не нажимайте ввод! Просто остановите ввод и перейдите к следующему шагу.
Лист Excel будет выглядеть так:
Шаг 4. Нажмите клавиши CTRL+SHIFT+ВВОД
Теперь нажмите клавиши CTRL+SHIFT+ВВОД. Зачем это нужно? Дело в том, что функция ТРАНСП используется только в формулах массивов, которые завершаются именно так. Если говорить кратко, формула массива — это формула, которая применяется сразу к нескольким ячейкам. Так как в шаге 1 вы выделили более одной ячейки, формула будет применена к нескольким ячейкам. Результат после нажатия клавиш CTRL+SHIFT+ВВОД будет выглядеть так:
Советы
- Вводить диапазон вручную не обязательно. Введя =ТРАНСП(, вы можете выделить диапазон с помощью мыши. Простой щелкните первую ячейку диапазона и перетащите указатель к последней. Но не забывайте: по завершении нужно нажать клавиши CTRL+SHIFT+ВВОД, а не просто клавишу ВВОД.
- Нужно также перенести форматирование текста и ячеек? Вы можете копировать ячейки, вставить их и применить команду «Транспонировать». Но помните, что при этом образуются повторяющиеся данные. При изменении исходных ячеек их копии не обновляются.
- Вы можете узнать больше о формулах массивов. Создайте формулу массива или ознакомьтесь с подробными рекомендациями и примерами.
Технические подробности
Функция ТРАНСП возвращает вертикальный диапазон ячеек в виде горизонтального и наоборот. Функцию ТРАНСП необходимо вводить как формула массива в диапазон, содержащий столько же строк и столбцов, что и аргумент диапазон. Функция ТРАНСП используется для изменения ориентации массива или диапазона на листе с вертикальной на горизонтальную и наоборот.
Синтаксис
Аргументы функции ТРАНСП описаны ниже.
- Массив. Обязательный аргумент. Массив (диапазон ячеек) на листе, который нужно транспонировать. Транспонирование массива заключается в том, что первая строка массива становится первым столбцом нового массива, вторая — вторым столбцом и т. д. Если вы не знаете, как ввести формулу массива, см. статью «Создание формулы массива».
ТРАНСП — Автоматическое транспонироване данных в Excel
Мы с вами уже рассматривали вопрос как в Excel преобразовать строку в столбец, то есть транспонировать данные. На практике данная необходимость встречается очень часто.
Бывают ситуации, когда вам необходимо транспонировать данные одинакового диапазона, то есть одинакового количества значений. Например, вам часто приходится преобразовывать данные по продажам по месяцам в виде горизонтальной таблицы в вертикальную как на рисунке.
Конечно, вы можете каждый раз использовать способ рассмотренный нами раньше, но в некоторых случаях можно упростить задачу и сделать так, чтобы данные транспонировались в вертикальную таблицу автоматически.
Это можно сделать с помощью формулы ТРАНСП. Синтаксис данной функции очень прост
где массив это диапазон данных, которые необходимо преобразовать в вертикальный или горизонтальный вид (транспонировать).
Для реализации данного способа нам необходимо выбрать диапазон в котором автоматически будут транспонироваться данные. В нашем случае это C8:C13
После этого на клавиатуре сразу набираете формулу
В результате у вас должно получить следующее:
Далее, набрав формулу необходимо нажать одновременно (Ctrl+Shift+Enter) для применения формулы массива. В результате формула с двух сторон будет окружена фигурными скобками.
Важно понимать, что если вы просто укажите фигурные скобки, то функция ТРАНСП работать не будет. Кроме того массивы (диапазоны) должны быть одинаковой величины.
Теперь изменяя данные в горизонтальной таблицы, они автоматически будут изменяться в вертикальной таблицы.
Спасибо за внимание.
SirExcel.ru – Безграничные возможности Excel
покупка
Excel Функция ТРАНСПОРТ поворачивает ориентацию диапазона или массива. Например, он может повернуть таблицу, которая расположена горизонтально по строкам, в вертикально по столбцам или наоборот.
Синтаксис
=TRANSPOSE ( array )
Аргумент
массив (обязательный): Массив или диапазон ячеек, которые вы хотите транспонировать.
Возвращаемое значение
Функция TRANSPOSE вернет массив в новой ориентации на основе определенного диапазона ячеек.
Примечания к функциям:
1. Функцию TRANSPOSE необходимо ввести как формулу массива, нажав Ctrl + Shift + Enter ключи одновременно.
2. Результат транспонирования: после транспонирования первая строка исходного массива становится первым столбцом нового массива, вторая строка исходного массива становится вторым столбцом нового массива, третья строка становится третьим столбцом и скоро.
Примеры
Пример 1. Измените ориентацию заданного диапазона
Предположим, вы хотите перевернуть ориентацию диапазона B4: I5, как показано на скриншоте ниже, вы можете попробовать следующее, чтобы добиться этого.
1. Выберите диапазон для нового массива, здесь я выбираю K3: L10, копирую в него формулу ниже:
=TRANSPOSE(B4:I5)
Внимание: Вы должны выбрать то же количество ячеек, что и исходный набор ячеек, но в другом направлении.
2. Затем нажмите Ctrl + Shift + Enter ключи для возврата нового массива.
Пример 2: транспонировать диапазон без нуля в Excel
Обычно, если указанный массив содержит пустые ячейки, эти пустые ячейки будут отображаться как 0 в новом массиве после использования функции транспонирования. Смотрите скриншот:
Если вы хотите транспонировать таблицу без нулей, вам может помочь метод из этого раздела.
1. Выберите диапазон для нового массива, здесь я выбираю K3: L10, копирую в него формулу ниже:
=TRANSPOSE(IF(B4:I5=»»,»»,B4:I5))
2. Нажмите Ctrl + Shift + Enter одновременно, чтобы получить новый массив без нулей, как показано на скриншоте ниже.
Внимание: Если вы хотите поместить «пустое» в пустые ячейки нового массива, а не оставлять его пустым, заключите этот текст во вторые двойные кавычки в формуле:
=TRANSPOSE(IF(B4:I5=»»,»blank»,B4:I5))
А затем нажмите Ctrl + Shift + Enter ключи.