Как преобразовать строки в столбцы в Excel

В этом руководстве объясняется, что делать, чтобы преобразовать строки в столбцы в Excel. При использовании Excel часто приходится менять местами строки и столбцы в таблице. Например, бывает, что человек сделал огромную таблицу, а потом понял, что читать ее гораздо легче, если перевернуть. 

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

Использование функции «Специальная вставка»

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

Чтобы поменять местами строки и столбцы, сделайте следующее:

1. Выберите исходную таблицу. Если вы хотите увидеть всю таблицу сразу (если она очень большая), вам нужно нажать комбинацию клавиш Ctrl+Home, а после этого — Ctrl+Shift+End.
2. Затем ячейки копируются. Это можно сделать либо через контекстное меню, либо нажав комбинацию клавиш Ctrl+C. К последнему варианту рекомендуется сразу приучать себя, ведь если выучить горячие клавиши, то многие задачи можно будет выполнять буквально за одну секунду.
3. Выберите первую ячейку в целевом диапазоне. При этом он должен находиться за пределами таблицы. Также необходимо подобрать ячейку таким образом, чтобы таблица после транспонирования не пересекалась с другими данными. Например, если исходная таблица имеет 4 столбца и 10 строк, то после выполнения всех этих операций она раздвинется на 10 ячеек вниз и на 4 ячейки в стороны. Следовательно, в этом диапазоне (считая от целевой ячейки) ничего быть не должно.
4. На целевой ячейке необходимо нажать правой кнопкой мыши и выбрать «Специальная вставка», а затем поставить галочку возле надписи «Транспонировать».

важно: если в исходной таблице есть формулы, важно обеспечить использование абсолютных ссылок в каждой ячейке. Это необходимо сделать для того, чтобы все ссылки автоматически обновлялись.

Огромное преимущество опции «Специальная вставка» заключается в возможности транспонировать таблицу всего за несколько секунд. И при этом полностью сохранить форматирование, что также позволяет сэкономить время.

Несмотря на эти явные плюсы, есть и ряд серьезных недостатков, которые мешают этому методу называться универсальным:

1. Плохо использовать его для транспонирования полноценных таблиц, не сведенных к банальному диапазону значений. В этом случае функция «Транспонирование» будет отключена. Чтобы обойти эту проблему, необходимо преобразовать таблицу в диапазон.
2. Этот метод хорошо подходит для однократного транспонирования, поскольку он не связывает новую таблицу с исходными данными. Простыми словами, при изменении одной из таблиц информация во второй не будет автоматически обновляться. Поэтому транспозицию придется повторить. 

Как поменять местами строки и столбцы и связать их с основной таблицей?

Итак, что же можно сделать, чтобы опция «Специальная вставка» могла связать таблицу с основными данными и данными, полученными в результате транспонирования? Ведь всем хотелось бы, чтобы данные обновлялись автоматически.

1. Скопировать таблицу, для которой требуется транспонирование.
2. Выделите ячейку без данных в пустой области таблицы.
3. Запустите меню «Специальная вставка», аналогично предыдущему примеру. После этого вам нужно нажать на кнопку «Вставить ссылку», которую можно найти слева внизу.
4. Результат будет следующим.
5. Выбрать новую таблицу и запустить окно «Найти и заменить», нажав клавиши Ctrl + H.
6. Замените все знаки формулы ввода (=) на ххх (или любые другие символы, которых нет в исходной таблице).
7. В результате получится нечто ужасное, но это обязательное промежуточное условие для достижения желаемого результата. Тогда все будет выглядеть красиво.
8. Скопируйте полученную таблицу, а затем используйте специальную вставку, чтобы транспонировать ее.

После этого вам просто нужно заново открыть диалог «Найти и заменить» и с его помощью изменить «ххх» в ячейках на «=», чтобы все ячейки были связаны с исходной информацией.Конечно, это сложнее и дольше, но этот способ позволяет обойти отсутствие ссылки на исходную таблицу. Но у этого подхода есть и недостаток. Выражается в необходимости самостоятельно возобновить форматирование.

Применение формул

Есть две функции, позволяющие гибко изменять строки и столбцы: ТРАНСП и ДВССЫЛ. Здесь также есть возможность сохранить связь с начальной таблицей, но механика работы несколько иная.

функция ТРАНСП

Фактически, эта формула напрямую транспонирует электронную таблицу. Синтаксис следующий:

=ТРАНСП(массив)

Теперь попробуем применить его к таблице, содержащей информацию о населении отдельных штатов.

1. Подсчитайте количество столбцов и строк в таблице и найдите на листе пустое место, имеющее такие же размеры.
2. Запустите режим редактирования, нажав клавишу F2.
3. Напишите функцию ТРАНСПОРТ с диапазоном данных в скобках. При использовании этой функции важно всегда использовать абсолютные ссылки.
4. Нажмите комбинацию клавиш Ctrl+Shift+Enter. Важно нажимать именно комбинацию клавиш, иначе формула откажется работать.

Всё, теперь результат выглядит вот так!

Преимущество этой формулы такое же, как и у предыдущего метода (с использованием функции «найти и заменить»), заключается в том, что при изменении базовой информации транспонированная таблица автоматически обновляется.

Но недостатки у нее тоже имеются:

1. Форматирование, как и в случае с методом «Найти и заменить», не сохранится.
2. Исходная таблица должна содержать какие-то данные, иначе в некоторых ячейках будут нули.
3. Излишняя зависимость от источника данных. То есть, этот метод имеет противоположный недостаток – невозможность изменения транспонированной таблицы. Если обвиняемые это сделать, программа скажет, что невозможно изменить часть массива.

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

Использование КОСВНОЙ формулы

Механика этого метода очень похожа на использование формулы ТРАНСПОРТ, но при этом его использование решает проблему невозможности сохранения транспонированной таблицы без потерь связи с основной информацией.

Но одна формула КОСВЕННЫЕ недостаточно: вам все равно нужно использовать функцию АДРЕС. Большой таблицы в этом примере не будет, чтобы не перегружать вас большим количеством ненужной информации.

Итак, допустим, у вас есть такая таблица, состоящая из 4 столбцов и 5 строк.

Необходимо предпринять следующие действия:

1. Введите эту формулу: =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1),СТРОКА(A1))) в левую верхнюю ячейку новой таблицы (в нашем примере это А7) и нажмите клавишу ввода. Если информация начинается не с первой строки или первого столбца, то придется использовать более сложную формулу: =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1)-СТОЛБЕЦ($A$1)+СТРОКА($A$1);СТРОКА(A1)-СТРОКА($A$1)+СТОЛБЕЦ($A$1))). В этой формуле А1 означает верхнюю ячейку таблицы, на основе которой будет сформирована транспонированная ячейка.
2. Распространите формулу на всю область, где будет размещена новая версия таблицы. Для этого перетащите маркер в правом нижнем углу первой ячейки на противоположный конец будущей таблицы.
3. Все! Таблица оказалась успешно транспонирована, и ее еще можно редактировать. Конечно, ее внешний вид оставляет желать лучшего, но исправить это несложно. Чтобы восстановить правильное форматирование, необходимо скопировать таблицу, которую мы транспонировали (то есть исходную), затем выделить вновь созданную таблицу. Затем щелкните правой кнопкой мыши выбранный диапазон и выберите «Форматирование» в параметрах вставки.

Итак, используя функцию КОСВЕННЫЕ позволяет редактировать абсолютно любое значение в итоговой таблице, при этом данные всегда будут обновляться, как только будут внесены какие-либо изменения в исходную таблицу.

Конечно, этот способ сложнее, но если отработать его на практике, то все не так страшно.

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

Как работает комбинация формул INDIRECT и ADDRESS?

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

Функция КОСВЕННЫЕ в нашей формуле используется для создания косвенной ссылки на ячейку. Например, если вам нужно указать в ячейке А8 то же значение, что и в ячейке В1, то можно написать формулу

=ДВССЫЛ («B1»)

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

За пределами функции АДРЕС также используется в формуле КОЛОНКА и ЛИНИЯ. Первый возвращает адрес ячейки на основе уже известных номеров строк и столбцов. Здесь важно соблюдать правильную последовательность. Сначала указывается строка, а затем только столбец. Например, функция АДРЕС(4;2) вернет адрес $B$2.

Следующая используемая выше функция – это КОЛОНКА. Здесь необходимо, чтобы формула получила номер столбца из конкретной ссылки. Например, если вы используете параметр B2 в скобках этой функции, то она вернет число 2, так как второй столбец — это столбец B.

Очевидно, что функция ROW работает точно так же, она просто возвращает номер строки. 

И теперь мы будем использовать не абстрактные примеры, а вполне конкретную формулу, которая использовалась выше:

=ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1),СТРОКА(A1)))

Здесь вы можете увидеть это сначала в функции АДРЕС указывается столбец, и только потом строка. И именно здесь скрыт секрет работоспособности этой формулы. Мы помним, что эта функция работает зеркально, и первым аргументом в ней является номер строки. И получается, что когда мы пишем туда адрес столбца, он превращается в номер строки и наоборот.

То есть, если подытожить:

1. Номера столбцов и строк мы получаем с помощью соответствующих функций.
2. Используя функцию АДРЕС строки становятся столбцами и наоборот.
3. Функция КОСВЕННЫЕ помогает отобразить зеркальные данные в ячейке.

Вот как все просто оказывается!

Использование макроса для транспонирования

Макрос – это небольшая программа. Его можно использовать для автоматизации процесса. Важно учитывать, что макрос имеет некоторые ограничения. Максимальный метод Transpose позволяет работать с 65536 элементами. Превышение этого лимита приведет к потере данных.

Во всем остальном, это эффективный метод автоматизации, который позволит значительно облегчить жизнь.

Например, можно написать такой код, который будет менять местами строки и колонки.

Sub TransposeColumnsRows()

    Dim SourceRange как диапазон

    Dim DestRange как диапазон

    Установить SourceRange = Application.InputBox(Подсказка:=»Пожалуйста, выберите диапазон для транспонирования», Title:=»Транспонировать строки в столбцы», Тип:=8)

    Set DestRange = Application.InputBox(Prompt:=»Выберите верхнюю левую ячейку целевого диапазона», Title:=»Transpose Rows to Columns», Type:=8)

    SourceRange.Copy

    DestRange.Выбрать

    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

    Application.CutCopyMode = False

End Sub

Но если знаний в программировании особо нет, ничего страшного. Можно воспользоваться описанными выше способами. А потом учиться новому в рамках освоения старых.