Как конвертировать строки в колонки в 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. Все! Таблицу получилось успешно транспонировать, и по-прежнему можно ее редактировать. Конечно, ее внешний вид оставляет желать лучшего, но исправить ее не составит труда. Чтобы восстановить правильное форматирование необходимо таблицу, которую мы транспонировали (то есть, исходную) скопировать, затем выделить только что созданную таблицу. Далее делается правый клик по выделенному диапазону, а потом нажать на «Форматирование» в параметрах вставки.

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

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

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

Как работает сочетание формул ДВССЫЛ и АДРЕСС?

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

Функция ДВССЫЛ в нашей формуле используется, чтобы создать непрямую ссылку на ячейку. Допустим, если необходимо в ячейке А8 указать такое же значение, как и в B1, то можно написать формулу

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Sub TransposeColumnsRows()

    Dim SourceRange As Range

    Dim DestRange As Range

    Set SourceRange = Application.InputBox(Prompt:=»Please select the range to transpose», Title:=»Transpose Rows to Columns», Type:=8)

    Set DestRange = Application.InputBox(Prompt:=»Select the upper left cell of the destination range», Title:=»Transpose Rows to Columns», Type:=8)

    SourceRange.Copy

    DestRange.Select

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

    Application.CutCopyMode = False

End Sub

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