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

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

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

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

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

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

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

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

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

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

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

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

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

  • выделить нужное количество пустых ячеек вне таблицы (A7:E11). Число выделенных строк должно совпадать с количеством столбцов в исходной таблице, а число выделенных столбцов – с количеством строк;
  • в Строке формул ввести =ТРАНСП(A1:E5) – т.е. дать ссылку на исходную таблицу;
  • Вместо ENTER нажать CTRL+SHIFT+ENTER.

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

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

Эта формула работает только при определенном расположении исходной таблицы на листе: когда левый верхний угол таблицы расположен на «диагонали» листа, т.е. в ячейке, у которой номер строки совпадает с номером столбца (А1, B2, C3 и т.п., В – это второй столбец, С – это третий столбец).

Если левый верхний угол таблицы расположен в другой ячейке, например в J2, формула немного усложняется:
=ДВССЫЛ(
АДРЕС(СТОЛБЕЦ(J2)+СТРОКА($J$2)-СТОЛБЕЦ($J$2);
СТРОКА(J2)-СТРОКА($J$2)+СТОЛБЕЦ($J$2))
)

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4.3 (3 оценок)