Как транспонировать данные в Excel: формулы и советы
Если вам нужно транспонировать данные в Excel, то есть повернуть таблицу из горизонтальной ориентации в вертикальную, существует несколько эффективных методов. В этой статье мы рассмотрим как простой способ через вставку, так и мощные формулы, включая ТРАНСП / TRANSPOSE и комбинацию ИНДЕКС / INDEX и СТРОКА / ROW, которые позволят вам динамически менять структуру данных.
Если вы разбираете формулы в Excel с нуля или хотите ускорить расчеты, здесь показано, как использовать функции в экселе на понятных и рабочих примерах.
Для разовой операции скопируйте диапазон и выберите «Специальная вставка → Транспонировать». Полученная копия не связана с исходной областью. Если копируются формулы, проверьте изменение относительных ссылок; для фиксации результатов выберите также вставку значений.
Формулы для динамического транспонирования
В Microsoft 365, Excel 2021 и Excel 2024 с динамическими массивами введите ТРАНСП в верхнюю левую ячейку свободной области и нажмите Enter. В Excel 2016/2019 заранее выделите область результата нужного размера и подтвердите ТРАНСП сочетанием Ctrl+Shift+Enter.
- Для транспонирования диапазона, например,
C2:N2, используйте формулу Русская версия:=ТРАНСП(C2:N2)English version:=TRANSPOSE(C2:N2). - Альтернативный вариант — функция Русская версия:
=СВЕРНСТРОК(C2:N2;1)English version:=WRAPROWS(C2:N2,1), которая размещает по одному значению в каждой строке и тем самым создаёт столбец. СВЕРНСТРОК (WRAPROWS) доступна в Microsoft 365 и Excel 2024, но не в Excel 2021/2019/2016.
В любой из этих версий можно извлекать значения по одному классической комбинацией функций и копировать её вниз на 12 строк.
=ИНДЕКС($C$2:$N$2; СТРОКА(1:1))=INDEX($C$2:$N$2, ROW(1:1))В этой формуле СТРОКА(1:1) возвращает номер строки (1), который увеличивается при копировании формулы вниз. Функция ИНДЕКС / INDEX извлекает соответствующий элемент из исходного горизонтального диапазона.
Бонусный совет: ограничение редактирования части массива
Многоячеечную формулу массива старого типа нельзя изменять по частям. Это может помешать случайному удалению отдельных строк, пересекающих массив, но не заменяет защиту листа: весь массив можно удалить целиком.
- Выделите диапазон в пустой области справа, например,
Z1:Z8. - Введите любое число, например Русская версия:
=2English version:=2. - Нажмите
Ctrl+Shift+Enter, чтобы создать массив.
При попытке удалить отдельную строку, пересекающую часть массива Z1:Z8, Excel сообщает, что нельзя изменить часть массива. Для штатного ограничения удаления строк используйте защиту листа с нужными разрешениями.
Таким образом, транспонирование данных в Excel — это гибкий процесс, который можно выполнить как для разовых задач, так и для создания динамических связей между диапазонами. Выбирайте метод в зависимости от вашей версии Excel и требуемого результата.