Функция в 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 варианта, это какой вам будет удобней, либо на панеле меню «Вставить» — «Транспонировать», либо то же самое но через контекстное меню при нажатии контекстного меню.

Читать еще:  Как забить формулу в excel

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

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

ТРАНСП — Автоматическое транспонироване данных в Excel

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

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

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

Это можно сделать с помощью формулы ТРАНСП. Синтаксис данной функции очень прост

где массив это диапазон данных, которые необходимо преобразовать в вертикальный или горизонтальный вид (транспонировать).

Для реализации данного способа нам необходимо выбрать диапазон в котором автоматически будут транспонироваться данные. В нашем случае это C8:C13

После этого на клавиатуре сразу набираете формулу

В результате у вас должно получить следующее:

Далее, набрав формулу необходимо нажать одновременно (Ctrl+Shift+Enter) для применения формулы массива. В результате формула с двух сторон будет окружена фигурными скобками.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Как от числа отнять процент в excel формула

Выделяем диапазон ячеек 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. Вернуть транспонированную таблицу из предыдущего примера без использования функции ТРАНСП.

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

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

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

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

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

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

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

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

Иногда требуется изменить направление, в котором располагаются ячейки. Это можно сделать путем копирования и вставки и применения команды «Транспонировать». Но в этом случае образуются повторяющиеся данные. Чтобы такого не происходило, можно вместо этого ввести формулу с функцией ТРАНСП. Например, на следующем изображении показано, как расположить горизонтально ячейки с 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+ВВОД, а не просто клавишу ВВОД.

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

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

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

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

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

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

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

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
Adblock
detector