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

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

Переворот в Microsoft Excel

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

Процедура транспонирования

Смена местами столбцов и строчек в Экселе называется транспонированием. Выполнить данную процедуру можно двумя способами: через специальную вставку и с применением функции.

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

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

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

Копирование в Microsoft Excel

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

Переход в специальную вставку в Microsoft Excel.png

  • Открывается окно настройки специальной вставки. Устанавливаем галочку напротив значения «Транспонировать». Жмем на кнопку «OK».
  • Специальная вставка в Microsoft Excel

    Как видим, после этих действий исходная таблица была скопирована в новое место, но уже с перевернутыми ячейками.

    Ячейки перевернуты в Microsoft Excel

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

    Удаление таблицы в Microsoft Excel

    Способ 2: применение функции

    Второй способ переворачивания в Экселе предполагает применения специализированной функции ТРАНСП.

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

    Переход к вставке функции в Microsoft Excel

    Открывается Мастер функций. В списке представленных инструментов ищем наименование «ТРАНСП». После того, как нашли, выделяем и жмем на кнопку «OK».

    Мастер функций в Microsoft Excel

    Открывается окно аргументов. У данной функции только один аргумент – «Массив». Ставим курсор в его поле. Вслед за этим, выделяем всю таблицу, которую хотим транспонировать. После того, как адрес выделенного диапазона записан в поле, жмем на кнопку «OK».

    Мастер функций в Microsoft Excel

    Ставим курсор в конец строки формул. На клавиатуре набираем сочетание клавиш Ctrl+Shift+Enter. Это действие нужно для того, чтобы данные корректно были преобразованы, так как мы имеем дело не с одной ячейкой, а с целым массивом.

    Действия в строке формул в Microsoft Excel.png

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

    Транспонированная таблица в Microsoft Excel.png

  • Форматируем таблицу так, чтобы она имела приемлемый вид.
  • Готовая таблица в Microsoft Excel

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

    Мы выяснили, как поменять местами столбцы и строки в Excel. Существует два основных способа перевернуть таблицу. Каким из них воспользоваться зависит от того, планируете ли вы применение связанных данных или нет. Если таких планов не имеется, то рекомендуется использовать первый вариант решения поставленной задачи, как более простой.

    Мы рады, что смогли помочь Вам в решении проблемы.

    Помимо этой статьи, на сайте еще 11905 инструкций.
    Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

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

    history 26 апреля 2013 г.
      Группы статей

    • Изменение Таблиц
    • Транспонирование

    Иногда требуется «перевернуть» таблицу, т.е. поменять столбцы и строки местами.

    Пусть имеется таблица продаж по отделам и кварталам.

    Необходимо поменять строки и столбцы местами. Транспонировать их можно несколькими способами (см. Файл примера ).

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

    • выделить всю таблицу ( A1:E5 );
    • скопировать таблицу в Буфер обмена ( CTRL+C );
    • выделить ячейку ниже таблицы ( A8 );
    • в меню Вставить ( Главная / Буфер обмена ) выбираем Транспонировать ;
    • нажимаем ОК.

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

    Наиболее простой способ транспонировать таблицу с помощью формул — использовать функцию СМЕЩ()

    Ячейка 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)) )

    Ссылка на основную публикацию