Как транспонировать таблицу в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Транспонирование таблиц в EXCEL
Необходимо поменять строки и столбцы местами. Транспонировать их можно несколькими способами (см. Файл примера ).
Специальная ставка — транспонировать
Транспонирование функцией СМЕЩ()
Наиболее простой способ транспонировать таблицу с помощью формул — использовать функцию СМЕЩ()
Ячейка J2 — ссылка на верхнюю левую ячейку исходной таблицы;
Ячейка H19 — ссылка на верхнюю левую ячейку транспонированной таблицы (на ту ячейку, в которую Вы вводите первую формулу) См. файл примера .
Функция ТРАНСП()
- выделить нужное количество пустых ячеек вне таблицы ( A7:E11 ). Число выделенных строк должно совпадать с количеством столбцов в исходной таблице, а число выделенных столбцов – с количеством строк;
- в Строке формул ввести =ТРАНСП(A1:E5) – т.е. дать ссылку на исходную таблицу;
- Вместо ENTER нажать CTRL + SHIFT + ENTER .
Транспонирование функцией ДВССЫЛ()
- в ячейку ниже таблицы ( A7 ) ввести формулу =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1))) , где А1 – координата левого верхнего угла таблицы.
- с помощью Маркера заполнения скопировать формулу на нужное количество строк и столбцов.
Эта формула работает только при определенном расположении исходной таблицы на листе: когда левый верхний угол таблицы расположен на «диагонали» листа, т.е. в ячейке, у которой номер строки совпадает с номером столбца ( А1, B 2, C 3 и т.п., В – это второй столбец, С – это третий столбец).
Если левый верхний угол таблицы расположен в другой ячейке, например в J 2 , формула немного усложняется: =ДВССЫЛ( АДРЕС(СТОЛБЕЦ(J2)+СТРОКА($J$2)-СТОЛБЕЦ($J$2); СТРОКА(J2)-СТРОКА($J$2)+СТОЛБЕЦ($J$2)) )
Транспоны данных из строк в столбцы (или наоборот) в Excel для Mac
При наличии листа с данными в столбцах, которые нужно повернуть, чтобы упорядочить строки, можно использовать функцию Транспонирование. Она позволяет повернуть данные из столбцов в строки и наоборот.
Например, если данные выглядят так, как показано ниже, где продажи по регионам указаны в верхней части листа, а продажи по кварталам — в левой части листа.
Вы можете повернуть столбцы и строки, чтобы отобразить кварталы в верхней части листа, а регионы — сбоку.
Ниже рассказывается, как это сделать.
-
Выберите диапазон данных, который нужно изменить, включая подписи строк или столбцов, и нажмите кнопку Копировать на вкладке Главная или нажмите control+C.
Примечание: Обязательно скопируйте данные. Это не получится сделать с помощью команды Вырезать или клавиш CONTROL+X.
Советы по транспонированию данных
- Если данные содержат формулы, Excel автоматически обновляет их в соответствие с новым расположением. Убедитесь, что в этих формулах используются абсолютные ссылки. Если они не используются, перед поворотом данных можно переключаться между относительным и абсолютным ссылками.
- Если данные хранятся в таблице Excel, функция Транспонировать будет недоступна. В этом случае вы можете сначала преобразовать таблицу в диапазон с помощью команды Преобразовать в диапазон на вкладке Таблица или воспользоваться функцией ТРАНСП, чтобы повернуть строки и столбцы.
- Если вам требуется часто поворачивать данные для просмотра под разными углами, стоит создать сводную таблицу. С ее помощью вы сможете быстро свести данные, перетащив поля из области строк в область столбцов (и наоборот) в списке полей сводной таблицы.
Три способа как транспонировать таблицу в Excel
Понятие «транспонировать» почти не встречается в работе пользователей ПК. Но тем, кто работает с массивами, будь то матрицы в высшей математике или таблицы в Excel, приходится сталкиваться с этим явлением.
Транспонированием таблицы в Excel называется замена столбцов строками и наоборот. Иными словами – это поворот в двух плоскостях: горизонтальной и вертикальной. Транспонировать таблицы можно тремя способами.
Способ 1. Специальная вставка
Самый простой и универсальный путь. Рассмотрим сразу на примере. Имеем таблицу с ценой некоего товара за штуку и определенным его количеством. Шапка таблицы расположена горизонтально, а данные расположены вертикально соответственно. Стоимость рассчитана по формуле: цена*количество. Для наглядности примера подсветим шапку таблицы зеленым цветом.
Нам нужно расположить данные таблицы горизонтально относительно вертикального расположения ее шапки.
Чтобы транспонировать таблицу, будем использовать команду СПЕЦИАЛЬНАЯ ВСТАВКА. Действуем по шагам:
- Выделяем всю таблицу и копируем ее (CTRL+C).
- Ставим курсор в любом месте листа Excel и правой кнопкой вызываем меню.
- Кликаем по команде СПЕЦИАЛЬНАЯ ВСТАВКА.
- В появившемся окне ставим галочку возле пункта ТРАНСПОНИРОВАТЬ. Остальное оставляем как есть и жмем ОК.
В результате получили ту же таблицу, но с другим расположением строк и столбцов. Причем, заметим, что зеленым подсвечены ячейки с тем же содержанием. Формула стоимости тоже скопировалась и посчитала произведение цены и количества, но уже с учетом других ячеек. Теперь шапка таблицы расположена вертикально (что хорошо видно благодаря зеленому цвету шапки), а данные соответственно расположились горизонтально.
Аналогично можно транспонировать только значения, без наименований строк и столбцов. Для этого нужно выделить только массив со значениями и проделать те же действия с командой СПЕЦИАЛЬНАЯ ВСТАВКА.
Способ 2. Функция ТРАНСП в Excel
С появлением СПЕЦИАЛЬНОЙ ВСТАВКИ транспонирование таблицы при помощи команды ТРАНСП почти не используется по причине сложности и большего времени на операцию. Но функция ТРАНСП все же присутствует в Excel, поэтому научимся ею пользоваться.
Снова действует по этапам:
- Правильно выделяем диапазон для транспонирования таблицы. В данном примере исходной таблицы имеется 4 столбца и 6 строк. Соответственно мы должны выделить диапазон ячеек в котором будет 6 столбцов и 4 строки. Как показано на рисунке:
- Сразу заполняем активную ячейку так чтобы не снять выделенную область. Вводим следующую формулу:=ТРАНСП(A1:D6)
- Нажимаем CTRL+SHIFT+ENTER. Внимание! Функия ТРАНСП()работает тилько в массиве. Поэтому после ее ввода нужно обязательно нажать комбинацию горячих клавиш CTRL+SHIFT+ENTER для выполнения функции в массиве, а не просто ENTER.
Обратите внимание, что формула не скопировалась. При нажатии на каждую ячейку становится видно, что эта таблица была транспонирована. К тому же все исходное форматирование утеряно. Придется выравнивать и подсвечивать снова. Так же стоит обратить внимание на то что транспонированная таблица привязанная к исходной. Измененные значения исходной таблице автоматически обновляются в транспонированной.
Способ 3. Сводная таблица
Те, кто тесно работает с Excel, знают, что сводная таблица многофункциональна. И одной из ее функций является возможность транспонирования. Правда, оно будет выглядеть немного иначе, чем в предыдущих примерах.
- Создадим сводную таблицу. Для этого выделим исходную таблицу и откроем пункт ВСТАВКА – СВОДНАЯ ТАБЛИЦА.
- Местом, где будет создана сводная таблица, выбираем новый лист.
- В получившемся макете сводных таблиц можем выбрать необходимые пункты и перенести их в нужные поля. Перенесем «продукт» в НАЗВАНИЯ СТОЛБЦОВ, а «цена за шт» в ЗНАЧЕНИЯ.
- Получили сводную таблицу по нужному нам полю. Также команда автоматически подсчитала общий итог.
- Можно убрать галочку у ЦЕНА ЗА ШТ и поставить галочку рядом с ОБЩАЯ СТОИМОСТЬ. И тогда получим сводную таблицу по стоимости товаров, а также опять общий итог.
Это достаточно своеобразный способ частичного транспонирования, позволяющий заменить столбцы на строки (наоборот – не получится) и дополнительно узнать общую сумму по заданному полю.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры