Транспонирование таблицы в excel

Транспонирование таблицы в excel

Автор: Дмитрий
Опубликована: 28.11.2011

Популярность= 101 ( +93 / -8 )

Простой способ отзеркалировать по диагонали таблицу в Excel 2003 и Excel 2007.

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

Алгоритм действий транспонирования таблицы в Excel 2003 и Excel 2007 ничем не отличается. Пусть имеется некоторая таблица с количеством товара в разных точках компании по месяцам.

    Выделяем таблицу полностью вместе с подписями строк и столбцов. На выделенном диапазоне нажмите правой клавишей мыши и в появившемся меню выберите пункт «Копировать».

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

Откроется окно дополнительных параметров вставки данных из буфера обмена. В самом низу отметьте галочкой параметр «Транспонировать» и нажмите кнопку «ОК».

В результате Вы получите транспонированную таблицу в Microsoft Excel.

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

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

Транспонирование таблиц в MS EXCEL

​Смотрите также​ за шт" в​Те, кто тесно работает​

​ ТРАНСП()работает тилько в​ по причине сложности​ тоже скопировалась и​

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

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

  • ​ по наименованию, затем​​ по горизонтали -​​ столбец).​
  • ​– координата левого​ (​​ выбираем Транспонировать;​​Иногда требуется «перевернуть» таблицу,​
  • ​ ЗНАЧЕНИЯ.​ с Excel, знают,​​ массиве. Поэтому после​​ и большего времени​
  • ​ посчитала произведение цены​ использовать команду СПЕЦИАЛЬНАЯ​ Иными словами –​
  • ​ строка (зато $E6 изменяется​

Транспонирование функцией СМЕЩ()

​ чисел от 1​с помощью формулы​ по размеру (чтобы​ список размеров. Транспонирование​

​Если левый верхний угол​

​ верхнего угла таблицы.​​A7:E11​​нажимаем ОК.​ т.е. поменять столбцы​Получили сводную таблицу по​

​ что сводная таблица​​ ее ввода нужно​​ на операцию. Но​ и количества, но​ ВСТАВКА. Действуем по​ это поворот в​ на $E7, затем​ до 10.​

​ массива: ​ размеры в транспонированной​

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

  • ​ произведем формулами.​ таблицы расположен в​с помощью Маркера заполнения​​). Число выделенных строк​​Наиболее простой способ транспонировать​ и строки местами.​ нужному нам полю.​ многофункциональна. И одной​ обязательно нажать комбинацию​ функция ТРАНСП все​ уже с учетом​
  • ​ шагам:​ двух плоскостях: горизонтальной​ на $E8 и т.д.)​Теперь самое интересное: запишем​
  • ​=ЕСЛИОШИБКА(ИНДЕКС(Наименования;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$5:D5;Наименования);0));"")​​ таблице шли по​​Пусть имеется исходная таблица​​ другой ячейке, например​​ скопировать формулу на​

Транспонирование функцией ДВССЫЛ()

  • ​ должно совпадать с​ таблицу с помощью​​Пусть имеется таблица продаж​​ Также команда автоматически​ из ее функций​​ горячих клавиш CTRL+SHIFT+ENTER​​ же присутствует в​ других ячеек. Теперь​
  • ​Выделяем всю таблицу и​ и вертикальной. Транспонировать​Дополним исходную таблицу одним​ в ячейке​
Читайте также:  Найти ранг квадратичной формы

​Формулу введите в ячейку​ порядку).​ наименований обуви с​ в​ нужное количество строк​ количеством столбцов в​ формул — использовать​ по отделам и​ подсчитала общий итог.​ является возможность транспонирования.​ для выполнения функции​​ Excel, поэтому научимся​​ шапка таблицы расположена​​ копируем ее (CTRL+C).​​ таблицы можно тремя​ наименованием обуви Кроссовки дет.​​F6​​D6​Примечание​

​ размерами.​J2​ и столбцов.​ исходной таблице, а​​ функцию СМЕЩ()​​ кварталам.​
​Можно убрать галочку у​
​ Правда, оно будет​
​ в массиве, а​
​ ею пользоваться.​

Транспонирование столбца в MS EXCEL в таблице с повторами

​ вертикально (что хорошо​Ставим курсор в любом​ способами.​ с 3-мя размерами​формулу для транспонирования​. Не забудьте после ввода​: О сортировке таблицы​Требуется для каждого наименования​, формула немного усложняется:​Эта формула работает только​ число выделенных столбцов​=СМЕЩ($J$2;СТОЛБЕЦ()-СТОЛБЕЦ($H$19);СТРОКА()-СТРОКА($H$19))​Необходимо поменять строки и​ ЦЕНА ЗА ШТ​ выглядеть немного иначе,​ не просто ENTER.​Снова действует по этапам:​

​ видно благодаря зеленому​ месте листа Excel​Самый простой и универсальный​

​ (12, 13, 14).​ столбца​ формулы массива нажать​

​ средствами EXCEL читайте​ обуви вывести горизонтально​=ДВССЫЛ(​ при определенном расположении​ – с количеством​Ячейка ​ столбцы местами. Транспонировать​

​ и поставить галочку​ чем в предыдущих​Обратите внимание, что формула​Правильно выделяем диапазон для​ цвету шапки), а​ и правой кнопкой​ путь. Рассмотрим сразу​Различные наименования обуви отделены​В​CTRL+SHIFT+ENTER. ​ статью Автофильтр.​ список размеров.​АДРЕС(СТОЛБЕЦ(J2)+СТРОКА($J$2)-СТОЛБЕЦ($J$2);​ исходной таблицы на​

​ строк;​​J2 ​ их можно несколькими​ рядом с ОБЩАЯ​

Решение

​ примерах.​ не скопировалась. При​ транспонирования таблицы. В​ данные соответственно расположились​ вызываем меню.​ на примере. Имеем​ красными горизонтальными линиями​

​в горизонтальный массив.​Протяните формулу вниз с​С помощью идей из​Сделаем такое преобразование таблицы​СТРОКА(J2)-СТРОКА($J$2)+СТОЛБЕЦ($J$2))​ листе: когда левый​

​в Строке формул ввести​

​- ссылка на верхнюю​ способами (см. Файл​​ СТОИМОСТЬ. И тогда​​Создадим сводную таблицу. Для​ нажатии на каждую​

​ данном примере исходной​

​ горизонтально.​​Кликаем по команде СПЕЦИАЛЬНАЯ​​ таблицу с ценой​ с помощью Условного​​=ЕСЛИ($E6>=F$4;СМЕЩ($B$6;ПОИСКПОЗ($D6;Наименования;0)+F$4-2;;1);"")​​ помощью Маркера заполнения​ статьи Отбор уникальных​ с помощью формул,​)​

​ верхний угол таблицы​​ =ТРАНСП(A1:E5) – т.е.​​ левую ячейку исходной​ примера).​

​ получим сводную таблицу​​ этого выделим исходную​ ячейку становится видно,​ таблицы имеется 4​Аналогично можно транспонировать только​ ВСТАВКА.​

​ некоего товара за​ форматирования.​Формулу протяните на 9​ на 2 ячейки​ значений сформируем в​

​ что позволит в​Пусть имеется таблица наименований​​ расположен на «диагонали»​​ дать ссылку на​ таблицы;​​выделить всю таблицу (​​ по стоимости товаров,​

​ таблицу и откроем​

​ что эта таблица​ столбца и 6​ значения, без наименований​В появившемся окне ставим​ штуку и определенным​Транспонированная таблица обновится автоматически.​ ячеек вправо и​ ниже.​ новой транспорнированной таблице​ дальнейшем при вводе​ обуви. Каждое наименование​ листа, т.е. в​ исходную таблицу;​Ячейка ​A1:E5​ а также опять​ пункт ВСТАВКА –​ была транспонирована. К​

Тестируем

​ строк. Соответственно мы​ строк и столбцов.​ галочку возле пункта​ его количеством. Шапка​

Читайте также:  Apple watch с сим картой в россии

​Понятие «транспонировать» почти не​ на 2 строки​В столбце​ перечень уникальных наименований​

​ новых наименований и​

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

​ обуви (столбец №1) повторяется​ ячейке, у которой​Вместо​H19 ​);​ общий итог.​ СВОДНАЯ ТАБЛИЦА.​ тому же все​ должны выделить диапазон​ Для этого нужно​

​ ТРАНСПОНИРОВАТЬ. Остальное оставляем​ таблицы расположена горизонтально,​ встречается в работе​ ниже. Обратите внимание на​Е​ (у нас будет​ размеров иметь актуальную​ столько раз, сколько​ номер строки совпадает​

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

​ENTER​- ссылка на верхнюю левую​скопировать таблицу в Буфер​Скачать примеры транспонирования таблиц​Местом, где будет создана​ исходное форматирование утеряно.​ ячеек в котором​ выделить только массив​ как есть и​ а данные расположены​ пользователей ПК. Но​ использование смешанных ссылок в формуле.​подсчитаем количество размеров​ их 2).​

​ транспонированную таблицу.​ у него имеется​ с номером столбца​нажать​

​ ячейку транспонированной таблицы​ обмена (​ в Excel.​ сводная таблица, выбираем​

  1. ​ Придется выравнивать и​ будет 6 столбцов​
  2. ​ со значениями и​ жмем ОК.​ вертикально соответственно. Стоимость​ тем, кто работает​
  3. ​ При копировании формулы​ каждого наименования: =СЧЁТЕСЛИ(Наименования;D6)​
  4. ​Для этого создадим Динамический​Для упрощения формул наложим​ различных размеров (столбец​ (​CTRLSHIFTENTER​

​ (на ту ячейку,​CTRL+C​Это достаточно своеобразный способ​ новый лист.​ подсвечивать снова. Так​ и 4 строки.​ проделать те же​В результате получили ту​ рассчитана по формуле:​ с массивами, будь​ вправо ссылка $E6​Примечание​ диапазон Наименования, чтобы​ ограничения на исходную​ №2). Иногда требуется​А1, B2, C3​.​ в которую Вы​);​

​ частичного транспонирования, позволяющий​В получившемся макете сводных​ же стоит обратить​ Как показано на​ действия с командой​ же таблицу, но​ цена*количество. Для наглядности​ то матрицы в​ не изменяется, т.к.​

​: Не забудьте, что Наименования​

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

​ при добавлении новых​ таблицу: количество размеров​ «перевернуть» не всю​и т.п.,​в ячейку ниже таблицы​ вводите первую формулу) См. файл​выделить ячейку ниже таблицы​ заменить столбцы на​ таблиц можем выбрать​ внимание на то​ рисунке:​

  1. ​ с другим расположением​ примера подсветим шапку​ высшей математике или​ зафиксирован столбец (зато​ в вышеприведенной формуле​ данных в исходную​ одного наименования не​ таблицу, а только​В​ (​ примера.​ (​
  2. ​ строки (наоборот –​ необходимые пункты и​ что транспонированная таблица​Сразу заполняем активную ячейку​
  3. ​​ строк и столбцов.​ таблицы зеленым цветом.​ таблицы в Excel,​ F$4 изменяется на​ — это Имя​ таблицу, транспонированная таблица​ может быть больше​ один столбец №2.​

​– это второй​A7​Также см. статью Функция СМЕЩ()​A8​ не получится) и​ перенести их в​ привязанная к исходной.​ так чтобы не​С появлением СПЕЦИАЛЬНОЙ ВСТАВКИ​ Причем, заметим, что​Нам нужно расположить данные​ приходится сталкиваться с​ G$4, затем на​ созданного ранее Вами​ автоматически обновлялась.​ 10 (определяет количество​ Т.е. теперь у​

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

​ столбец,​) ввести формулу =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1))),​ в MS EXCEL​);​ дополнительно узнать общую​ нужные поля. Перенесем​ Измененные значения исходной​ снять выделенную область.​ транспонирование таблицы при​ зеленым подсвечены ячейки​

  1. ​ таблицы горизонтально относительно​ этим явлением.​ H$4 и т.д.),​ Динамического диапазона.​Формула для Динамического диапазона: =СМЕЩ(Лист1!$A$6;;;СЧЁТЗ(Лист1!$A$6:$A$19)).​
  2. ​ столбцов с размерами​ нас будет столбец​С​
  3. ​ где​выделить нужное количество пустых​в меню Вставить (Главная​ сумму по заданному​ "продукт" в НАЗВАНИЯ​ таблице автоматически обновляются​ Вводим следующую формулу:=ТРАНСП(A1:D6)​ помощи команды ТРАНСП​ с тем же​
  4. ​ вертикального расположения ее​Транспонированием таблицы в Excel​ а при копировании​В строке 4 (см.​
  5. ​Затем создадим перечень уникальных наименований​ в транспонированной таблице);​ наименований обуви (уже​– это третий​А1​ ячеек вне таблицы​ / Буфер обмена)​ полю.​ СТОЛБЦОВ, а "цена​
Читайте также:  Как написать звезду на клавиатуре

​ в транспонированной.​Нажимаем CTRL+SHIFT+ENTER. Внимание! Функия​

​ почти не используется​ содержанием. Формула стоимости​ шапки.​ называется замена столбцов​ вниз наоборот ссылка F$4 не​ файл примера) создайте​ в столбце​ таблица отсортирована сначала​

В этом уроке расскажу что такое транспонирование в Excel. Благодаря этой функции можно поменять строки местами. Это может понадобиться тогда, когда вы создаете таблицу и по мере ее заполнения добавляете параметры по горизонтали и вертикали. Со временем вы понимаете, что для большей наглядности было бы лучше, если бы мы поменяли строки и столбцы.

Если это делать вручную, то по сути нужно создавать таблицу заново, что не совсем удобно. Поэтому на помощь приходит специальная опция под названием Транспонирование.

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

Первым делом нужно скопировать таблицу, с которой будем работать. Выделяем и копируем таблицу. Обратите внимание, что таблица обязательно должна копироваться (например, через Ctrl+C), а не вырезаться.

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

В открывшемся окне ставим галочку на пункте Транспонировать и нажимаем кнопку Ок.

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

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

Не забудьте поделиться ссылкой на статью ⇒

В этом уроке расскажу как удалить страницу из PDF файла. Этим способом можно удалить любую страницу из файла и поможет нам в этом браузер Google Chrome.

    08.12.2016 Просмотров: 8339 Google Chrome, PDF

Сегодня расскажу как искать совпадение в столбцах Excel. Разберем все тонкости на примерах.

    24.11.2015 Просмотров: 82619 Excel Видеоурок

В этом уроке расскажу как закрепить строку или столбец в Excel. Закрепленные области будут всегда видны на экране при вертикальной или горизонтальной прокрутке.

    25.11.2015 Просмотров: 6055 Excel Видеоурок

В сегодняшнем уроке я расскажу как вставить формулу в Word. На самом деле существует несколько способов это сделать и по возможности я постараюсь рассмотреть их все.

    04.02.2016 Просмотров: 18721 Word

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

Ссылка на основную публикацию
Тест эксель на собеседовании
Если вы хоть раз пытались устроиться на работу или же работаете на должности, в круг обязанностей которой входит принятие людей...
Тарифы мтс смарт 400 руб
С того момента как тариф «Смарт» стал доступен для активации, он претерпел множество изменений. Они касаются размера абонентской платы, количества...
Тарифы ростелекома на домашний интернет
Полный список актуальных тарифов Ростелеком для города Москва. Подключай тарифы Rostelecom в Москве бесплатно и пользуйся качественными услугами интернета и...
Тестирование cd и dvd дисков
В этой статье я опишу программу тест Nero CD DVD Speed, которая разработана компанией "Nero Softwsre AG". С помощью программы...
Adblock detector