Примеры функции ТРАНСП для переворачивания таблиц в 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)

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

Результат:

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

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

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

• выделить строку длиной 5 ячеек;
• в строке формул введите =TRANSP(B2:B6) ;
• нажмите CTRL+SHIFT+ВВОД .

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

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

• выделить столбец длиной 5 ячеек;
• в строке формул введите =TRANSP(B10:F10) ;
• Нажмите CTRL+SHIFT+ВВОД .

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

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

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

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

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

=ПРОЗРАЧ(массив)

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

Примечания 1:

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

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

Примечания 2:

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

Преимущества функции ТРАНСП:

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

Слабые стороны функции ТРАНСП:

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

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

Как транспонировать таблицу без замены нулей на пробелы.

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

То есть, если при переносе строки в столбец программа обнаруживает пустую ячейку, на ее место сразу вставляется ноль. Это функция Excel: надо произвести какую-то математическую операцию с пустой ячейкой, и там сразу отображается ноль. А транспонирование в Excel — это математическая операция с массивом значений.

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

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

=ТРАНСП(ЕСЛИ(A1:D5=»»;»»;A1:D5))

Введите формулу, как описано выше (не забудьте нажать Ctrl+Shift+Enter, чтобы завершить формулу массива), и вы получите такой результат:

Как видите, мы транспонируем не все ячейки, а только непустые.

Читайте также: Почему Эксель не считает формулу: 5 решений проблемы

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

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

1. Как редактировать формулу?

Как функция массива, TRANSPOSE не позволяет изменять какую-либо часть возвращаемого массива. Чтобы изменить формулу транспонирования, выберите весь диапазон, к которому применяется формула, внесите необходимые изменения и нажмите Ctrl + Shift + Enter, чтобы сохранить обновленную формулу.

2. Как удалить формулу?

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

3. Как заменить формулу на значения?

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

Если вы хотите разорвать связь между ними, замените формулы вычисляемыми значениями. Для этого выделите все ячейки в преобразованной таблице, затем нажмите Ctrl+C, чтобы скопировать их в буфер обмена. Щелкните правой кнопкой мыши и выберите «Специальная вставка» > «Значения» в контекстном меню. Или используйте Ctrl+Alt+V, чтобы вставить значения вместо формул.

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

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

Алгоритм действий:

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

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

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

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

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

Для этого выполните следующие действия:

1. Выберите исходные данные. Чтобы быстро выделить всю таблицу, то есть все ячейки с данными в ней, нажмите Ctrl+Home, а затем Ctrl+Shift+End.
2. Скопируйте выделение, щелкнув правой кнопкой мыши и выбрав «Копировать» в контекстном меню, или нажав Ctrl + C.
3. Выберите первую позицию в целевой области.
   Обязательно выберите ячейку за пределами исходной таблицы, чтобы области копирования и вставки не перекрывались. Например, если в настоящее время у вас есть 4 столбца и 10 строк, преобразованная таблица будет иметь 10 столбцов и 4 строки.
4. Щелкните правой кнопкой мыши целевую ячейку и выберите «Специальная вставка» в контекстном меню, а затем выберите «Транспонировать».

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

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

Однако у этого подхода есть два недостатка, которые не позволяют назвать его идеальным решением для реверсирования данных в Excel:

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

Как транспонировать таблицу и связать ее с исходными данными

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

1. Скопируйте строки, которые нужно преобразовать в столбцы (или столбцы, которые нужно преобразовать в строки).
2. Выберите пустую ячейку на том же или другом листе.
3. откройте диалоговое окно «Специальная вставка», как описано в предыдущем примере, и щелкните ссылку «Вставить» в левом нижнем углу.

1. Результат будет примерно таким:
2. Выберите новую таблицу и откройте диалоговое окно Excel «Найти и заменить» (или нажмите Ctrl + H, чтобы перейти непосредственно на вкладку «Заменить).
3. Замените все символы «=» на «###» или другие символы, которых нет в реальных данных. Таким простым способом мы преобразуем наши справочные формулы в текст.

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

1. Скопируйте диапазон значений «###» в буфер обмена, а затем используйте Специальная вставка > Транспонировать, чтобы преобразовать столбцы в строки.
2. Наконец, снова откройте диалоговое окно «Найти и заменить», чтобы отменить изменение, т е вернуться, чтобы заменить все «###» на «=» и восстановить ссылки на исходные данные.

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

Перенести данные в Excel с помощью макроса VBA

Для автоматизации преобразования строк в столбцы в Excel можно использовать следующий макрос:

Чтобы добавить макрос на лист, выполните действия, описанные в статье Как вставить и запустить код VBA в Excel .

Примечание. Транспонирование с помощью VBA имеет ограничение в 65536 элементов. Если ваш массив превышает этот предел, лишние данные будут автоматически удалены.

Как использовать макрос для преобразования строки в столбец

После вставки макроса в книгу выполните следующие действия, чтобы повернуть таблицу:

1. откройте целевой лист, нажмите Alt + F8, выберите макрос TransposeColumnsRows и нажмите «Выполнить».
2. Выберите область, которую хотите преобразовать, и нажмите «ОК :
3. Выберите положение в левом верхнем углу целевой области и нажмите OK:

Наслаждайтесь результатом 🙂

Сводная таблица.

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

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

Сделайте это на новом листе.

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

Перетаскиваем поля в макете стола, и получаем результат — стол перевернут на бок.

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

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

Позвольте мне показать вам, как преобразовать строки в столбцы в Excel всего за пару кликов с помощью инструмента транспонирования таблицы:

1. Выберите одну ячейку в таблице, перейдите на вкладку AblebitsTools > Transform и нажмите кнопку Transpose.
2. В большинстве случаев настройки по умолчанию работают нормально, поэтому просто нажмите «Транспонировать», ничего не меняя.

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

Прозрачный! Таблица транспонирована:

Если вы заинтересованы в том, чтобы попробовать этот и более 60 других профессиональных инструментов для Excel, я приглашаю вас загрузить пробную версию Ultimate Suite.