Excel: Транспонирование
Довольно часто при составлении таблиц встречается такая проблема.
Мы первоначально расставляем отдельные параметры по вертикали, а другие по горизонтали, но постепенно добавляя элементы таблицы, понимаем, что для наглядности представления информации в таблице необходимо поменять местами строки и столбцы.
Делать это вручную, то же самое, что начинать создание таблицы с нуля.
В Excel реализована возможность автоматически поменять столбцы и строки местами, этот процесс называется транспонированием .
- Скопируйте всю таблицу вместе с заголовками , используя кнопку Копировать .
Чтобы скопировать выделенные данные, можно также нажать клавиши CTRL+C.
Примечание. Для перестановки данных можно использовать только команду Копировать. Команда Вырезать для этого случая не подходит.
- Выделите на листе первую ячейку будущей таблицы.
Примечание. Исходная таблица и транспонированная таблица не должны перекрываться. Убедитесь, что выделенная ячейка находится вне области, из которой скопированы данные.
- Далее можно действовать несколькими способами.
На вкладке Главная выберите параметр Вставки — Транспонировать , нажав на значок
Выделив ячейку, кликните правой кнопкой мыши. Откроется меню, в котором необходимо выбрать опцию Транспонировать в Параметрах вставки.
Можно нажать клавиши CTRL+V для вставки данных, а затем нажав на смарт-тег выбрать опцию Транспонировать.
В окне Специальная вставка отметьте опцию транспонировать и нажмите кнопку ОК.
- В результате все заголовки будут расположены по горизонтали.
После того как данные будут успешно транспонированы, останется только удалить первоначальную таблицу.
Три способа как транспонировать таблицу в 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
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Как горизонтальную таблицу сделать вертикальной в эксель
Добрый день! Очень много советов о том как перевести вертикальную таблицу в горизонтальную, в мне нужно наоборот — горизонтальную таблицу в вертикальную.
Есть таблица:
Номер оборудования; Наименование; ФИО ответсвенного за отгрузку; С какого склада было отгружено; Какой организации;
В месяц получается 2-3 тысячи строк.
А мне нужно эту таблицу побить на вертикальные листы
Номер оборудования;
Наименование;
ФИО ответсвенного за отгрузку;
С какого склада было отгружено;
Какой организации;
Вот и все. Не могу справиться уже второй день. Пробовал ДВССЫЛ но не получилось..
Помогите пожалуйста.
Пользователь
Сообщений: 11251 Регистрация: 01.01.1970
07.10.2014 09:17:49
Навряд ли дождетесь желающих за вас создать файл с вашей структурой
Лень двигатель прогресса, доказано.
Пользователь
Сообщений: 5 Регистрация: 07.10.2014
07.10.2014 09:26:04
Так дело не в структуре, дело в алгоритме. Я думаю, что не только мне пригодиться. Мне только формула нужна которая после пятого столбца первой строчки перейдет на первый столбец второй и т.д.
Пользователь
Сообщений: 11251 Регистрация: 01.01.1970
07.10.2014 09:41:05
| Цитата |
|---|
| SergeySV пишет: Так дело не в структуре, дело в алгоритме. |
Алгоритм специальная вставка транспонирование
или
=трансп()
Лень двигатель прогресса, доказано.
Пользователь
Сообщений: 5 Регистрация: 07.10.2014
07.10.2014 10:07:51
Транспонирование не подойдет, мне не чего транспонировать с вертикальной таблицы, мне нужно что бы из горизонтальной таблицы данные с интервалом в пять столбцов вставлялись в вертикальную. В вертикальной они повторяются, а в горизонтальной названия не повторяются но данные повторяются вниз
(это горизонтальная таблица)
Номер оборудования; Наименование; ФИО ответсвенного за отгрузку; С какого склада было отгружено; Какой организации;
(здесь данные; здесь данные; здесь данные; здесь данные; здесь данные;здесь данные; здесь данные; здесь данные)
(здесь данные; здесь данные; здесь данные; здесь данные; здесь данные;здесь данные; здесь данные; здесь данные)
(Тут вертикальная таблица)
Номер оборудования; (здесь данные должны быть)
Наименование; (здесь данные должны быть)
ФИО ответсвенного за отгрузку; (здесь данные должны быть)
С какого склада было отгружено; (здесь данные должны быть)
Какой организации; (здесь данные должны быть)
Номер оборудования; (здесь данные должны быть)
Наименование; (здесь данные должны быть)
ФИО ответсвенного за отгрузку; (здесь данные должны быть)
С какого склада было отгружено; (здесь данные должны быть)
Какой организации; (здесь данные должны быть)
Номер оборудования; (здесь данные должны быть)
Наименование; (здесь данные должны быть)
ФИО ответсвенного за отгрузку; (здесь данные должны быть)
С какого склада было отгружено; (здесь данные должны быть)
Какой организации; (здесь данные должны быть)
Как конвертировать горизонтальную таблицу в вертикальную google sheets?
Если религиозные соображения позволяют завести во второй таблице ещё один столбец, то можно поступить следующим образом. Организуем новую таблицу с тремя столбцами: номер строк|столбец1 | столбец2.
Первый столбец просто заранее сделанная нумерация (в примере она начинается в ячейки B19), в «столбец1» пишем формулу:
=INDEX($B$4:$E$7;ROUNDUP(B19/2);IF(ISEVEN(B19);3;1))
и протягиваем вниз
в «столбец2» пишем формулу:
=INDEX($B$4:$E$7;ROUNDUP(B19/2);IF(ISEVEN(B19);4;2))
и протягиваем вниз.
Для реальной работы надо заменить мои «$B$4:$E$7» на тот диапазон, который занимает Ваша первая табличка. Он, понятно, может быть на другом листе и даже вообще в другой гугл-таблице. А «B19» надо заменить на номер ячейки с которой у Вас начинается нумерация строк второй таблицы.
Ответ написан более трёх лет назад
Комментировать
Нравится Комментировать
Ваш ответ на вопрос
Войдите, чтобы написать ответ
- Google Sheets
Скрип для записи последних действий в документе?
- 1 подписчик
- вчера
- 73 просмотра
- Google Sheets
- +1 ещё
Как настроить триггер на изменение в гугл таблицах?
- 1 подписчик
- 28 апр.
- 48 просмотров