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

history

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


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

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

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

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

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



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

= СМЕЩ($J$2;СТОЛБЕЦ()-СТОЛБЕЦ($H$19);СТРОКА()-СТРОКА($H$19))

Ячейка J2 - ссылка на верхнюю левую ячейку исходной таблицы;

Ячейка H19 - ссылка на верхнюю левую ячейку транспонированной таблицы (на ту ячейку, в которую Вы вводите первую формулу) См. файл примера .

Также см. статью Функция СМЕЩ() в MS EXCEL

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

  • выделить нужное количество пустых ячеек вне таблицы ( A 7: E 11 ). Число выделенных строк должно совпадать с количеством столбцов в исходной таблице, а число выделенных столбцов – с количеством строк;
  • в Строке формул ввести =ТРАНСП(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)) )


Комментарии

Только для авторизованных пользователей

(только для авторизованных пользователей)

© Copyright 2013 - 2024 Excel2.ru. All Rights Reserved