Как транспонировать таблицу в excel

Функция ТРАНСП

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

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

Шаг 1. Выделите пустые ячейки

Сначала выделите пустые ячейки. Их число должно совпадать с числом исходных ячеек, но располагаться они должны в другом направлении. Например, имеется 8 ячеек, расположенных по вертикали:

Нам нужно выделить 8 ячеек по горизонтали:

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

Шаг 2. Введите =ТРАНСП(

Не снимая выделение с пустых ячеек, введите =ТРАНСП(

Лист Excel будет выглядеть так:

Обратите внимание на то, что восемь ячеек по-прежнему выделены даже несмотря на то, что мы начали вводить формулу.

Шаг 3. Введите исходный диапазон ячеек

Теперь введите диапазон ячеек, которые нужно транспонировать. В этом примере мы хотим транспонировать ячейки из A1 в B4. Поэтому формула для этого примера будет: =TRANSPOSE(A1:B4) — но пока не нажимайте клавишу ВВОД! Просто прекратите вводить текст и перейдите к следующему шагу.

Лист Excel будет выглядеть так:

Шаг 4. Нажмите клавиши CTRL+SHIFT+ВВОД

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

Советы

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

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

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

Синтаксис

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

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

Превращение строк в столбцы и обратно

Хотим, упрощенно говоря, повернуть таблицу на бок, т.е. то, что располагалось в строке — пустить по столбцу и наоборот:

Способ 1. Специальная вставка

Выделяем и копируем исходную таблицу (правой кнопкой мыши — Копировать). Затем щелкаем правой по пустой ячейке, куда хотим поместить повернутую таблицу и выбираем из контекстного меню команду Специальная вставка (Paste Special) . В открывшемся диалоговом окне ставим галочку Транспонировать (Transpose) и жмем ОК. Минусы : не всегда корректно копируются ячейки с формулами, нет связи между таблицами (изменение данных в первой таблице не повлияет на вторую). Плюсы : в транспонированной таблице сохраняется исходное форматирование ячеек.

Способ 2. Функция ТРАНСП

Выделяем нужное количество пустых ячеек (т.е. если, например, исходная таблица была из 3 строк и 5 столбцов, то выделить обязательно нужно диапазон из 5 строк и 3 столбцов) и вводим в первую ячейку функцию ТРАНСП (TRANSPOSE) из категории Ссылки и массивы (Lookup and Reference): После ввода функции необходимо нажать не Enter, а Ctrl+Shift+Enter, чтобы ввести ее сразу во все выделенные ячейки как формулу массива . Если раньше не сталкивались с формулами массивов, то советую почитать тут — это весьма экзотический, но очень мощный инструмент в Excel. Плюсы : между таблицами сохраняется связь, т.е. изменения в первой таблице тут же отражаются во второй. Минусы : не сохраняется форматирование, пустые ячейки из первой таблицы отображаются в виде нулей во второй, нельзя редактировать отдельные ячейки во второй таблице, поскольку формулу массива можно менять только целиком.

Способ 3. Формируем адрес сами

• Функция АДРЕС(номер_строки; номер_столбца) — выдает адрес ячейки по номеру строки и столбца на листе, т.е. АДРЕС(2;3) выдаст, например, ссылку на ячейку C2.
• Функция ДВССЫЛ(ссылка_в_виде_текста) — преобразует текстовую строку, например, «F3» в настоящую ссылку на ячейку F3.
• Функции СТРОКА(ячейка) и СТОЛБЕЦ(ячейка) — выдают номер строки и столбца для заданной ячейки, например =СТРОКА(F1) выдаст 1, а =СТОЛБЕЦ(А3) выдаст 3.

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

=ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1)))

в английской версии Excel это будет =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

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

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

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

Минусы : форматирование не сохраняется, но его можно легко воспроизвести Специальной вставкой (вставить только Формат с флажком Транспонировать

Три способа как транспонировать таблицу в Excel

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

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

Способ 1. Специальная вставка

Самый простой и универсальный путь. Рассмотрим сразу на примере. Имеем таблицу с ценой некоего товара за штуку и определенным его количеством. Шапка таблицы расположена горизонтально, а данные расположены вертикально соответственно. Стоимость рассчитана по формуле: цена*количество. Для наглядности примера подсветим шапку таблицы зеленым цветом.

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

Чтобы транспонировать таблицу, будем использовать команду СПЕЦИАЛЬНАЯ ВСТАВКА. Действуем по шагам:

1. Выделяем всю таблицу и копируем ее (CTRL+C).
2. Ставим курсор в любом месте листа Excel и правой кнопкой вызываем меню.
3. Кликаем по команде СПЕЦИАЛЬНАЯ ВСТАВКА.
4. В появившемся окне ставим галочку возле пункта ТРАНСПОНИРОВАТЬ. Остальное оставляем как есть и жмем ОК.

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

Аналогично можно транспонировать только значения, без наименований строк и столбцов. Для этого нужно выделить только массив со значениями и проделать те же действия с командой СПЕЦИАЛЬНАЯ ВСТАВКА.

Способ 2. Функция ТРАНСП в Excel

С появлением СПЕЦИАЛЬНОЙ ВСТАВКИ транспонирование таблицы при помощи команды ТРАНСП почти не используется по причине сложности и большего времени на операцию. Но функция ТРАНСП все же присутствует в Excel, поэтому научимся ею пользоваться.

Снова действует по этапам:

1. Правильно выделяем диапазон для транспонирования таблицы. В данном примере исходной таблицы имеется 4 столбца и 6 строк. Соответственно мы должны выделить диапазон ячеек в котором будет 6 столбцов и 4 строки. Как показано на рисунке:
2. Сразу заполняем активную ячейку так чтобы не снять выделенную область. Вводим следующую формулу:=ТРАНСП(A1:D6)
3. Нажимаем CTRL+SHIFT+ENTER. Внимание! Функия ТРАНСП()работает тилько в массиве. Поэтому после ее ввода нужно обязательно нажать комбинацию горячих клавиш CTRL+SHIFT+ENTER для выполнения функции в массиве, а не просто ENTER.

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

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

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

1. Создадим сводную таблицу. Для этого выделим исходную таблицу и откроем пункт ВСТАВКА – СВОДНАЯ ТАБЛИЦА.
2. Местом, где будет создана сводная таблица, выбираем новый лист.
3. В получившемся макете сводных таблиц можем выбрать необходимые пункты и перенести их в нужные поля. Перенесем «продукт» в НАЗВАНИЯ СТОЛБЦОВ, а «цена за шт» в ЗНАЧЕНИЯ.
4. Получили сводную таблицу по нужному нам полю. Также команда автоматически подсчитала общий итог.
5. Можно убрать галочку у ЦЕНА ЗА ШТ и поставить галочку рядом с ОБЩАЯ СТОИМОСТЬ. И тогда получим сводную таблицу по стоимости товаров, а также опять общий итог.

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

• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Excel: Транспонирование

Довольно часто при составлении таблиц встречается такая проблема.

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

Делать это вручную, то же самое, что начинать создание таблицы с нуля.

В Excel реализована возможность автоматически поменять столбцы и строки местами, этот процесс называется транспонированием .

1. Скопируйте всю таблицу вместе с заголовками , используя кнопку Копировать .

Чтобы скопировать выделенные данные, можно также нажать клавиши CTRL+C.

Примечание. Для перестановки данных можно использовать только команду Копировать. Команда Вырезать для этого случая не подходит.

1. Выделите на листе первую ячейку будущей таблицы.

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

1. Далее можно действовать несколькими способами.

На вкладке Главная выберите параметр Вставки — Транспонировать , нажав на значок

Выделив ячейку, кликните правой кнопкой мыши. Откроется меню, в котором необходимо выбрать опцию Транспонировать в Параметрах вставки.

Можно нажать клавиши CTRL+V для вставки данных, а затем нажав на смарт-тег выбрать опцию Транспонировать.

В окне Специальная вставка отметьте опцию транспонировать и нажмите кнопку ОК.

1. В результате все заголовки будут расположены по горизонтали.

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