Транспонировать в Excel (эксель): функция ТРАНСП

Чтобы поменять местами строки и столбцы в Excel, введите =ТРАНСП(A1:D3) там, где должен начинаться результат: первая строка A1:D3 станет первым столбцом результата. Это функция ТРАНСП (по-английски TRANSPOSE). Результат остаётся связанным, поэтому изменение числа в источнике меняет его и в копии. Замените B2 на 50 и посмотрите, как изменится B6. В таблицах формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой: =ТРАНСП(ЕСЛИ(A1:E2="";"";A1:E2)).

ФормулыСбросить

A5fx

=TRANSPOSE(A1:D3)

	A	B	C	D
1	Rep	Q1	Q2	Q3
2	Ann	10	12	9
3	Ben	8	11	14
4
5	Rep	Ann	Ben
6	Q1	10	8
7	Q2	12	11
8	Q3	9	14

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ТРАНСП(A1:D3)

В источнике 3 строки и 4 столбца, поэтому в результате 4 строки и 3 столбца. В Excel 2021 и Microsoft 365 ТРАНСП разливается: вы вводите её в одну ячейку и нажимаете Enter.

Как транспонировать через специальную вставку

Если таблица с переставленными строками и столбцами нужна один раз, специальная вставка быстрее и не оставляет формулы:

1. Выделите диапазон и скопируйте его (Ctrl+C, на Mac Cmd+C).
2. Щёлкните левую верхнюю ячейку места, куда должен попасть результат, за пределами скопированного диапазона.
3. На вкладке «Главная» нажмите стрелку под кнопкой «Вставить» и выберите «Транспонировать» или нажмите Ctrl+Alt+V (на Mac Cmd+Ctrl+V), отметьте «транспонировать» и нажмите «ОК».

В английской версии Excel для Windows путь с клавиатуры после копирования такой: Alt, H, V, T. Вставленная копия сохраняет форматирование, но не связана с источником: когда источник меняется, вставьте заново. Excel подстраивает формулы внутри скопированного диапазона под их новое положение; проверьте те, где есть относительные ссылки, или вставьте значения, если нужны только числа.

Синтаксис ТРАНСП

=TRANSPOSE(array)

Единственный аргумент это диапазон или массив, который нужно развернуть. ТРАНСП есть в Excel давно, но до Excel 2021 это формула массива: сначала выделите диапазон назначения нужной формы (4 строки на 3 столбца для примера выше), введите формулу и нажмите Ctrl+Shift+Enter (на Mac Cmd+Shift+Enter). Тогда Excel показывает её в фигурных скобках, {=ТРАНСП(A1:D3)}. В Excel 2021 и Microsoft 365 достаточно обычного Enter, а значение на пути результата даёт #ПЕРЕНОС! (по-английски #SPILL!).

В Google Таблицах TRANSPOSE тоже есть и разливается так же.

Превратить столбец в строку

Та же функция разворачивает один столбец в строку, например список имён в заголовки столбцов:

ФормулыСбросить

C2fx

=TRANSPOSE(A2:A6)

	A	B	C	D	E	F	G
1	Name
2	Ann		Ann	Ben	Cara	Dan	Eve
3	Ben
4	Cara
5	Dan
6	Eve

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ТРАНСП(A2:A6)

C2 разливает имена от Ann до Eve по C2:G2. Добавьте имя в A7, и оно не попадёт в результат, потому что формула читает A2:A6; расширьте диапазон до A2:A7 или больше, чтобы включить его.

Почему ТРАНСП показывает 0 для пустых ячеек

Формула, которая указывает на пустую ячейку, возвращает 0, и ТРАНСП тоже. Ниже за март продаж нет, поэтому простой вариант показывает 0 там, где источник пуст:

ФормулыСбросить

D4fx

=TRANSPOSE(IF(A1:E2="","",A1:E2))

	A	B	C	D	E
1	Month	Jan	Feb	Mar	Apr
2	Sales	120	95		150
3
4	Month	Sales		Month	Sales
5	Jan	120		Jan	120
6	Feb	95		Feb	95
7	Mar	0		Mar
8	Apr	150		Apr	150

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ТРАНСП(ЕСЛИ(A1:E2="";"";A1:E2))

B7 показывает 0 для Mar. Вторая формула сначала превращает каждую пустую ячейку в пустой текст с помощью ЕСЛИ, поэтому E7 остаётся пустой. Введите число в D2, и обе формулы его покажут.

Транспонировать несколько строк в один столбец

Чтобы сложить всю сетку в один столбец, используйте ПОСТОЛБЦ (по-английски TOCOL, Microsoft 365 и Excel 2024). Она читает диапазон строка за строкой; ПОСТРОК делает то же в одну строку:

ФормулыСбросить

F2fx

=TOCOL(B2:D3)

	A	B	C	D	E	F
1	Rep	Q1	Q2	Q3		All values
2	Ann	10	12	9		10
3	Ben	8	11	14		12
4						9
5						8
6						11
7						14

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ПОСТОЛБЦ(B2:D3)

F2 перечисляет 10, 12, 9 из строки Ann, а затем 8, 11, 14 из строки Ben. Чтобы читать вниз по каждому столбцу, задайте третьему аргументу ИСТИНА: =ПОСТОЛБЦ(B2:D3;;ИСТИНА). =ПОСТОЛБЦ(B2:D3;1) пропускает пустые ячейки.

Практика: месяцы по столбцу, а не по строке

ФормулыСбросить

A5fx

	A	B	C	D	E
1	Month	Jan	Feb	Mar	Apr
2	Sales	120	95	80	150
3
4	Months down

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.

Ваша очередь: В A5 разверните таблицу A1:E2 так, чтобы месяцы шли вниз по столбцу A, а продажи вниз по столбцу B.

ПроверитьПоказать ответ

ТРАНСП или поиск по строке

Часто широкую таблицу транспонируют только для того, чтобы применить к ней ВПР. Это не нужно: ГПР и ПРОСМОТРX ищут прямо по строке. =ПРОСМОТРX("Mar";B1:E1;B2:E2) возвращает продажи за март из широкой таблицы выше, ничего не разворачивая. Транспонируйте, когда должна измениться сама структура: для диаграммы, отчёта или системы, которая ждёт одну запись на строку.