Как транспонировать данные в Excel: формулы и советы

Если вам нужно транспонировать данные в Excel, то есть повернуть таблицу из горизонтальной ориентации в вертикальную, существует несколько эффективных методов. В этой статье мы рассмотрим как простой способ через вставку, так и мощные формулы, включая ТРАНСП / TRANSPOSE и комбинацию ИНДЕКС / INDEX и СТРОКА / ROW, которые позволят вам динамически менять структуру данных.

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

Копирование горизонтальной таблицы в C1:N2. Щелкните правой кнопкой мыши в пустой ячейке. Четвертая иконка в параметрах вставки называется Транспонировать. Выберите ее, чтобы вставить повернутую копию исходной таблицы.

Самый быстрый способ — использовать специальную вставку. Однако этот метод создает статическую копию данных. Для динамического транспонирования, которое обновляется при изменении исходных значений, потребуются формулы.

Формулы для динамического транспонирования

В современных версиях Excel с поддержкой динамических массивов задача решается одной функцией.

Для транспонирования диапазона, например, C2:N2, используйте формулу Русская версия: =ТРАНСП(C2:N2)English version: =TRANSPOSE(C2:N2).
Альтернативный вариант — функция Русская версия: =СВЕРНСТОЛБЦ(C2:N2;1)English version: =WRAPCOLS(C2:N2,1), которая даст аналогичный результат, упаковав данные в один столбец.

12 столбцов с заголовками месяцев и значениями в C1:N2. Цель — отобразить их вертикально, начиная с ячейки B4. Одна формула: =TRANSPOSE(C2:N2). Другая формула: =WRAPROWS(C2:N2,1). — 12 столбцов с заголовками месяцев и значениями в C1:N2. Цель — отобразить их вертикально, начиная с ячейки B4. Одна формула: =ТРАНСП(C2:N2). Другая формула: =СВЕРНСТРОК (WRAPROWS)(C2:N2,1).

Если у вас нет Microsoft 365, можно использовать классическую комбинацию функций.

Русская версия:

=ИНДЕКС($C$2:$N$2; СТРОКА(1:1))

English version:

=INDEX($C$2:$N$2, ROW(1:1))

В этой формуле СТРОКА(1:1) возвращает номер строки (1), который увеличивается при копировании формулы вниз. Функция ИНДЕКС / INDEX извлекает соответствующий элемент из исходного горизонтального диапазона.

Альтернативное решение, не требующее Ctrl+Shift+Enter. Формула для января в B4: =INDEX($C$2:$N$2,ROW(1:1)). ROW(1:1) — это сложный способ записать число 1. — Альтернативное решение, не требующее Ctrl+Shift+Enter. Формула для января в B4: =ИНДЕКС($C$2:$Ч / N$2,СТРОКА(1:1)). СТРОКА(1:1) — это сложный способ записать число 1.

Бонусный совет: защита строк с помощью старого массива

Массивы старого типа (вводимые комбинацией Ctrl+Shift+Enter) можно использовать для нестандартной защиты области листа от удаления строк.

Выделите диапазон в пустой области справа, например, Z1:Z9.
Введите любое число, например Русская версия: =2English version: =2.
Нажмите Ctrl+Shift+Enter, чтобы создать массив.

Инструкция на листе просит не удалять строки. Кто-то попытается удалить строку. Справа выделите Z1:Z8. Введите =2 и нажмите Ctrl+Shift+Enter. Это создаст массив в этих строках.

Теперь при попытке удалить строку в защищенном диапазоне Excel выдаст сообщение об ошибке, ссылаясь на массив.

Когда кто-то пытается удалить строку в области строк с 1 по 8, появится сообщение: "Вы не можете изменить часть массива".

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

Как транспонировать данные в Excel: формулы и советы

Формулы для динамического транспонирования

Бонусный совет: защита строк с помощью старого массива

📖 Похожие материалы