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

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

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

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

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

• выделить всю таблицу ( 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)) )