Как горизонтальную таблицу сделать вертикальной в эксель

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

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

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

Вы можете повернуть столбцы и строки, чтобы отобразить кварталы в верхней части листа, а регионы — сбоку.

Ниже рассказывается, как это сделать.

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

Примечание: Обязательно скопируйте данные. Это не получится сделать с помощью команды Вырезать или клавиш CONTROL+X.

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

Повернув данные, можно удалить исходные.

Советы по транспонированию данных

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

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

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

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

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

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

Вот как это сделать:

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

Примечание: Убедитесь, что вы копируете данные для этого, так как с помощью команды Вырезать или CTRL+X не будет работать.

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

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

Советы по транспонированию данных

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

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

Вот как можно транспонировать содержимое ячейки:

1. Скопируйте диапазон ячеек.
2. Выделите пустые ячейки, в которые нужно вставить транспонированные данные.
3. На вкладке Главная щелкните значок Вставить и выберите Вставить Транспонировать.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Три способа как транспонировать таблицу в 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 Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

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

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

Способ 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)))

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

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

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

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