Excel функция трансп

3 способа транспонировать в Excel данные

Добрый день уважаемый читатель!

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

Итак, транспонирование — происходит от глагола «транспонировать», далее от немецкого «transponieren», далее из французского «transposer» «переставлять, перемещать», далее из латынского «trānspōnere» — «перекладывать», «переносить», , математическое обозначение, это преобразование матрицы, в результате чего, столбцы становятся строками, а строки превращаются в столбцы. Говоря простым языком, это все значение в строке (горизонталь) перекинуть в столбец (вертикаль).

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

Возможность транспонировать в Excel реализовано 3 способами:

Рассмотрим детально все варианты транспонировать в Excel и пойдем от сложного к простому, а уже только вы решите какой из способов вам будет удобен в том или ином случае.

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

Для начала создаем таблицу для исходных данных:

Следующим шагом переходим к меню «Вставка» — «Сводная таблица», в диалоговом окне указываем данные для анализа и место где расположится сводная таблица:

Для анализа использована функция транспонировать в Excel, я использовал наглядно столбик «Наименование», которое сделал строкой и шапкой в таблице и столбец «Всего» которое превратилось в строковое значение:

При рассмотрении ближе вы видите что со всего выбора, я использовал 2 пункта, одно из них, это «Наименование» я перетянул в раздел «Колонны» и получил шапку таблицы. А вторым шагом столбец «Всего» перетянул в раздел «Значение» и в параметрах свойства значения указал суммирование. Вуаля. Результат вы видете перед собой.

Вторым вариантом транспонировать в Excel будет использование функции Excel «=ТРАНСП()», которая разрешит сохранить связь с первоначальными данными:

Создаем исходные данные, ну или они уже есть у нас в наличии. К примеру, наши данные расположены с 4-й по 14-ю строки и соответственно, 3 столбца:

В нужном вам месте вводите формулу «=ТРАНСП()» и передаете в неё ссылку на весь объем диапазона, который необходимо транспонировать в Excel:

После того как выделен диапазон для вставки формулы, в формулу введен массив исходных данных, клацнем на кнопочку F2 и сразу же используем комбинацию гарячих клавиш Ctrl+Shift+Enter, таким способом вводится формула на весь массив и как результат получаем перевернутые данные которые сохраняют ссылки на исходные данные.
Подводя итоги, вы видете что у нас получился диапазон, который аналогичен первоначальным данным, только в перевернутом виде и особенно важен тот факт, что данные в новом диапазоне связаны с исходником и если мы изменим исходные данные, то данные автоматически изменятся в диапазоне с транспонированными данными. Таким же способом диапазон можно развернуть и в другую сторону.

Транспонировать в Excel с помощью пункта «Меню» — «Специальная вставка» — «Транспонировать». Для использование этого метода вам необходимо скопировать данные которые мы будем транспонировать в Excel, с помощью кнопки меню «Копировать» или же сочетанием на клавиатуре ctrl+c.

Следующий шаг, это установить указатель на то место где вы хотите вставить транспонированные данные. Тут есть 2 варианта, это какой вам будет удобней, либо на панеле меню «Вставить» — «Транспонировать», либо то же самое но через контекстное меню при нажатии контекстного меню.

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

Скачать пример можно здесь.

Примеры функции ТРАНСП для переворачивания таблиц в Excel

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

Читать еще: Excel скрыть формулу в ячейке

Примеры использования функции ТРАНСП в Excel

Пример 1. Дана прямоугольная матрица, записанная в таблице Excel. Выполнить операцию умножения данной матрицы на число -1 и отобразить транспонированную матрицу.

Выделяем диапазон ячеек G2:L4 и вводим формулу для решения (использовать как формулу массива CTRL+SHIFT+Enter):

Единственный аргумент функции – диапазон ячеек A2:C7, значение каждой из которых будет умножено на число, содержащееся в ячейке E4 (5).

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

Как переворачивать таблицу в Excel вертикально и горизонтально

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

Сначала выделяем диапазон ячеек A9:G21. Ведь для транспонирования приведенной таблицы мы выделяем область шириной в 6 ячеек и высотой в 12 ячеек и используем следующую формулу массива (CTRL+SHIFT+Enter):

A2:M8 – диапазон ячеек исходной таблицы.

Таблицу такого вида можно распечатать на листе А4.

Переворот таблицы в Excel без использования функции ТРАНСП

Пример 3. Вернуть транспонированную таблицу из предыдущего примера без использования функции ТРАНСП.

Выделить исходную таблицу и скопировать все данные (Ctrl+С).
Установить курсор в ячейку, которая будет находиться в левом верхнем углу транспонированной таблицы, вызвать контекстное меню и выбрать пункт «Специальная вставка».
В открывшемся окне установить флажок напротив надписи «Транспонировать» и нажать кнопку «ОК».

В результате будет добавлена таблица:

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

Особенности использования функции ТРАНСП в Excel

Функция имеет следующий синтаксис:

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

Если в качестве аргумента функции были ошибочно переданы данные формата Имя, функция ТРАНСП вернет код ошибки #ИМЯ?. Числовые, текстовые и логические данные, переданные на вход функции ТРАНСП, в результате ее выполнения будут отображены без изменений.
После выполнения функции ТРАНСП в созданной перевернутой таблице некоторые данные могут отображаться некорректно. В частности, данные типа Дата могут отображаться в виде чисел в коде времени Excel. Для корректного отображения необходимо установить требуемый тип данных в соответствующих ячейках.
Перед использованием функции ТРАНСП необходимо выделить область, количество ячеек в которой равно или больше числу ячеек в транспонируемой таблице. Если было выделено больше ячеек, часть из них будут содержать код ошибки #Н/Д. После выполнения операции, описанной в пункте 3, снова выделите все ячейки включая те, которые содержат ошибку #Н/Д, нажмите Ctrl+H. В текстовом поле «Найти» необходимо ввести символы «#*», а поле «Заменить на» оставить пустым (замена на пустое значение) и нажать Enter.
Поскольку функция ТРАНСП является формулой массива, при попытке внесения изменений в любую из ячеек транспонированной таблицы появится диалоговое окно с ошибкой «Нельзя изменить часть массива». Для получения возможности редактировать новую таблицу необходимо:

выделить весь диапазон входящих в ее состав ячеек и скопировать данные в буфер обмена (Ctrl+С или пункт «Копировать» в контекстном меню);
открыть контекстное меню нажатием левой кнопки мыши (или использовать кнопку «Вставить» в меню программы Excel) и выбрать пункт «Специальная вставка» (CTRL+ALT+V);
выбрать требуемый вариант в подменю «Вставить значения». Теперь связь между исходной и транспонированной таблицами отсутствует, любые данные могут быть изменены.

Функция ТРАНСП является одним из трех доступных в Excel способов транспонирования диапазонов данных. Остальные способы будут рассмотрены ниже.
Данная функция рассматривает переданные данные в качестве массива. При транспонировании массивов типа ключ->значение строка с о значением «ключ» становится столбцом с этим же значением.
Функцию можно использовать для транспонирования математических матриц, записанных в виде таблицы в Excel.
Рассматриваемая функция может быть использована только в качестве формулы массива.

ТРАНСП (функция ТРАНСП)

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

Примечание: Если у вас текущая версия Office 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+ВВОД, а не просто клавишу ВВОД.

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

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

Технические подробности

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

Аргументы функции ТРАНСП описаны ниже.

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

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Функция ТРАНСП() — Транспонирование диапазонов ячеек в MS EXCEL

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

Синтаксис функции

ТРАНСП(массив)

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

Транспонирование вертикальных диапазонов ячеек (столбцов)

Пусть дан столбец с пятью заполненными ячейками B2:B6 (в этих ячейках могут быть константы или формулы). Сделаем из него строку. Строка будет той же размерности (длины), что и столбец (см. Файл примера ).

выделим строку длиной 5 ячеек;
в Строке формул введем =ТРАНСП(B2:B6) ;
нажмем CTRL+SHIFT+ENTER.

Транспонирование горизонтальных диапазонов ячеек (строк)

Пусть дана строка с пятью заполненными ячейками B10:F10 (в этих ячейках могут быть константы или формулы). Сделаем из нее столбец. Столбец будет той же размерности (длины), что и строка.

выделим столбец длиной 5 ячеек;
в Строке формул введем =ТРАНСП(B10:F10) ;
Нажмем CTRL+SHIFT+ENTER.

Транспонирование диапазонов ячеек

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

Читать еще: Abs excel функция

СОВЕТ:
Транспонирование можно осуществить и обычными формулами: см. статью Транспонирование таблиц.

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

Также транспонирование диапазонов значений можно осуществить с помощью Специальной вставки ( Главная/ Буфер обмена/ Вставить/ Транспонировать ). Для этого выделите исходный диапазон.

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

30 функций Excel за 30 дней: ТРАНСП (TRANSPOSE)

Вчера в марафоне 30 функций Excel за 30 дней мы считали количество столбцов в диапазоне, используя функцию COLUMNS (ЧИСЛСТОЛБ), а теперь пришло время для чего-то более востребованного.

13-й день марафона мы посвятим исследованию функции TRANSPOSE (ТРАНСП). При помощи этой функции Вы можете поворачивать свои данные, превращая вертикальные области в горизонтальные и наоборот. У Вас возникает такая необходимость? Сможете сделать это, используя специальную вставку? Смогут ли это сделать другие функции?

Итак, обратимся к информации и примерам по функции TRANSPOSE (ТРАНСП). Если у Вас есть дополнительные сведения или примеры, пожалуйста, делитесь ими в комментариях.

Функция 13: TRANSPOSE (ТРАНСП)

Функция TRANSPOSE (ТРАНСП) возвращает горизонтальный диапазон ячеек как вертикальный или наоборот.

Как можно использовать функцию TRANSPOSE (ТРАНСП)?

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

Изменить горизонтальное расположение данных на вертикальное.
Показать лучший общий объём заработной платы за последние годы.

Чтобы изменить ориентацию данных, не создавая ссылок на исходные данные:

Используйте Paste Special (Специальная вставка) >Transpose (Транспонировать).

Синтаксис TRANSPOSE (ТРАНСП)

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

TRANSPOSE(array)
ТРАНСП(массив)

array (массив) – это массив или диапазон ячеек, которые нужно транспонировать.

Ловушки TRANSPOSE (ТРАНСП)

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

Пример 1: Превращаем горизонтальные данные в вертикальные

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

Чтобы транспонировать горизонтальный диапазон 2х4 в вертикальный диапазон 4х2:

Выделите 8 ячеек, куда Вы хотите разместить полученный вертикальный диапазон. В нашем примере это будут ячейки B4:C7.
Введите следующую формулу и превратите её в формулу массива нажатием Ctrl+Shift+Enter.

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

Вместо TRANSPOSE (ТРАНСП), Вы можете использовать другую функцию для преобразования данных, например, INDEX (ИНДЕКС). Она не потребует ввода формулы массива, и Вам не придётся выделять все ячейки конечной области, при создании формулы.

Пример 2: Изменяем ориентацию без ссылок

Если Вы просто хотите изменить ориентацию Ваших данных, не сохраняя ссылку на изначальные данные, Вы можете использовать специальную вставку:

Выберите исходные данные и скопируйте их.
Выберите верхнюю левую ячейку области, куда необходимо поместить результат.
На вкладке Home (Главная) нажмите на выпадающее меню команды Paste (Вставить).
Выберите Transpose (Транспонировать).
Удалите исходные данные (по желанию).

Пример 3: Лучший общий объём заработной платы за прошедшие годы

Функция TRANSPOSE (ТРАНСП) может использоваться в сочетании с другими функциями, например, как в этой сногсшибательной формуле. Она была опубликована Харланом Гроувом в новостном блоке Excel в обсуждении вопроса о подсчёте лучшей общей суммы заработной платы за 5 прошедших лет (подряд!).

=MAX(MMULT(A8:J8, —(ABS(TRANSPOSE(COLUMN(A8:J8))-COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number+1))-(Number-1)/2)
=МАКС(МУМНОЖ(A8:J8; —(ABS(ТРАНСП(СТОЛБЕЦ(A8:J8))-СТОЛБЕЦ(СМЕЩ(A8:J8;0;0;1;ЧИСЛСТОЛБ(A8:J8)-Number+1))-(Number-1)/2)

Как можно понять по фигурным скобкам в строке формул – это формула массива. Ячейка A5 названа Number и в этом примере число 4 введено, как значение для количества лет.

Формула проверяет диапазоны, чтобы увидеть достаточно ли в них последовательных столбцов. Результаты проверки (1 или 0) умножаются на значения ячеек, чтобы получить суммарный объём заработной платы.

Для проверки результата на рисунке ниже в строке под значениями зарплат показаны суммарные значения для каждой стартовой ячейки, при этом максимальное значение выделено жёлтым. Это более долгий путь к тому же результату, что предыдущая формула массива получает в одной ячейке!