6 способов быстро транспонировать таблицу

В этой статье показано, как превратить столбец в строку в Excel с помощью TRANSPOSE, Специальной вставки, кода VBA или специального инструмента. Другими словами, мы научимся транспонировать таблицу.

В этой статье вы найдете различные способы обмена строками и столбцами (или столбцами и строками), как бы вы это ни называли, они одинаковы Эти решения работают во всех версиях Excel 2016, 2013, 2010, 2007 и более ранних версиях. Возможны сценарии и объясняются наиболее частые ошибки. Вот о чем мы поговорим:

О вкусах не спорят. То же самое и с рабочими привычками. Некоторые пользователи Excel предпочитают организовывать свои данные вертикально в столбцы, в то время как другие выбирают горизонтальные строки. А когда мы вместе работаем за столами, вкусы часто не совпадают …

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

Транспонирование — это математическая операция. Это матричное преобразование, при котором столбцы становятся строками, а строки становятся столбцами. Проще говоря, вы должны поместить все значения в одну строку (по горизонтали) в один столбец (по вертикали).

«>

Способ 1. Использовать функцию ТРАНСП.

В случае, если вам нужно быстро изменить ориентацию стола, вы можете использовать функцию TRANSPOSE).

Назначение функции TRANSPOSE в Excel — превратить строки в столбцы, то есть изменить ориентацию с горизонтальной на вертикальную или наоборот.

Функция ТРАНСП в Excel — синтаксис

Функция транспонирования принимает только один аргумент:

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

Важно! Для работы TRANSPOSE необходимо ввести его как формулу массива, нажав Ctrl + Shift + Enter.

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

1. Подсчитайте количество столбцов и строк в исходной таблице.

В этом примере мы транспонируем небольшую таблицу, показывающую объем экспорта свежих фруктов по регионам:

2. Выберите такое же количество ячеек, но измените их ориентацию.

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

В нашем случае мы выбираем диапазон из 5 столбцов и 4 строк:

Выделив этот пустой интервал, используйте функцию транспонирования:

Сначала введите знак равенства, имя функции и открывающую скобку: = TRANSPOSE(

Затем выберите исходный диапазон мышью или введите его вручную:

Наконец, введите закрывающую скобку, но не нажимайте клавишу Enter! На этом этапе ваша формула должна выглядеть так:

Нажмите Ctrl + Shift + Enter, чтобы правильно заполнить формулу массива. Почему это? Потому что формула должна применяться ко всем данным. И именно для этого нужны формулы массива.

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

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

Преимущества функции ТРАНСП:

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

Слабые стороны функции ТРАНСП:

• Исходное форматирование не сохраняется в преобразованной таблице, как вы можете видеть на скриншоте выше.
• Если в исходной таблице есть пустые ячейки, ячейки в новой таблице будут содержать ноль. Чтобы обойти эту проблему, используйте TRANSPOSE в сочетании с функцией IF, как описано в следующем примере: Как транспонировать без нулей.
• Вы не можете изменить какие-либо данные в преобразованной таблице, потому что она полностью привязана к исходным данным. Если вы попытаетесь изменить какое-либо значение, отобразится ошибка «Не удается изменить часть массива».

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

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

Мы уже упоминали выше, что если одна или несколько ячеек в исходной таблице пусты, после преобразования они будут иметь нулевые значения, как показано на следующем снимке экрана:

То есть, если при переносе строки в столбец программа обнаруживает пустую ячейку, сразу же вставляется ноль. Это особенность Excel: нужно произвести какую-то математическую операцию с пустой ячейкой, и тогда появится ноль. А транспонирование в Excel — это математическая операция с массивом значений.

С одной стороны, это кажется несущественным, так как не меняет математических результатов в итогах. Но вам эти нули могут показаться не очень эстетичными, поскольку они меняют внешний вид данных. Что ж, если они появляются в середине текстовых значений, это совсем плохо.

Если вы хотите оставить в новой таблице пустые ячейки, а не нули, вложите функцию ЕСЛИ в формулу TRANSPOSE, чтобы заранее проверить, пуста ли ячейка. Если пусто, функция ЕСЛИ вернет пустую строку («»), в противном случае она предоставит значение для транспонирования:

Введите формулу, как описано выше (не забудьте нажать Ctrl + Shift + Enter, чтобы правильно заполнить формулу массива), и вы получите следующий результат:

Как видите, мы не транспонируем все ячейки, а только непустые.

Советы по использованию ТРАНСП

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

1. Как редактировать формулу?

Как функция массива TRANSPOSE не позволяет изменять часть возвращаемого массива. Чтобы изменить формулу транспонирования, выберите весь диапазон, к которому принадлежит формула, внесите необходимые изменения и нажмите Ctrl + Shift + Enter, чтобы сохранить обновленную формулу.

2. Как удалить формулу?

Чтобы удалить формулу транспонирования с листа, выберите весь диапазон, на который ссылается формула, и нажмите клавишу «Удалить.

3. Как заменить формулу на значения?

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

Если вы хотите разорвать связь между ними, замените формулы вычисленными значениями. Для этого выделите все ячейки преобразованной таблицы, затем нажмите Ctrl + C, чтобы скопировать их в буфер обмена. Щелкните правой кнопкой мыши и выберите в контекстном меню «Специальная вставка»> «Значения». Или используйте комбинацию клавиш Ctrl + Alt + V для ввода значений вместо формул.

Способ 2. Как транспонировать с помощью формул

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

Для создания ссылок на строки и столбцы нам понадобятся четыре функции из категории «Ссылки и массивы:

• Функция АДРЕС (номер_строки; номер_столбца) — предоставляет адрес ячейки на основе номера строки и столбца на листе, например ADDRESS (2; 3) предоставит, например, ссылку на C2.
• Функция ДВССЫЛ (link_in_type_text) — преобразует текстовую строку, такую ​​как «F3», в реальную ссылку на ячейку F3.
• Функции ROW () и COLUMN () предоставляют номера строк и столбцов для данной ячейки, например = ROW (F1) вернет 1, а формула = COLUMN (A3) вернет 3.

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

Вводим следующую формулу в любую свободную ячейку (скажем, в A8:

в английской версии Excel это будет = КОСВЕННО (АДРЕС (КОЛОНКА (A1), СТРОКА (A1)))

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

это все! Во вновь созданной таблице все столбцы заменены строками.

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

= КОСВЕННО (АДРЕС (КОЛОНКА (B6) — КОЛОНКА ($ B $ 6) + СТРОКА ($ B $ 6); СТРОКА (B6) — СТРОКА ($ B $ 6) + КОЛОНКА ($ B $ 6)))

Где B6 — верхняя левая ячейка исходной таблицы. Также обратите внимание на использование абсолютных и относительных ссылок.

Однако транспонированные данные выглядят очень просто и скучно по сравнению с оригиналом. Все форматирование потеряно.

Но не расстраивайтесь, эту проблему легко исправить. Чтобы восстановить исходное форматирование, выполните следующие действия:

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

Недостатки: вижу только один — теряется форматирование данных. Хотя можно быстро восстановить как описано выше.

Способ 3. Специальная вставка

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

1. Выберите исходные данные. Чтобы быстро выделить всю таблицу, то есть все ячейки, содержащие данные, нажмите Ctrl + Home, затем Ctrl + Shift + End.
2. Скопируйте выделение, щелкнув правой кнопкой мыши и выбрав Копировать в контекстном меню или нажав Ctrl + C.
3. Выберите первую позицию диапазона назначения.
   Убедитесь, что вы выбрали ячейку за пределами исходной таблицы, чтобы области копирования и вставки не перекрывались. Например, если у вас сейчас 4 столбца и 10 строк, преобразованная таблица будет иметь 10 столбцов и 4 строки.
4. Щелкните правой кнопкой мыши целевую ячейку и выберите «Специальная вставка» в контекстном меню, затем выберите «Транспонировать».

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

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

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

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

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

1. Скопируйте строки для преобразования в столбцы (или столбцы для преобразования в строки).
2. Выберите пустую ячейку на том же или другом листе.
3. Откройте диалоговое окно «Специальная вставка», как описано в предыдущем примере, и нажмите «Вставить ссылку» в нижнем левом углу.

1. Выберите новую таблицу и откройте диалоговое окно «Найти и заменить» в Excel (или нажмите Ctrl + H, чтобы перейти непосредственно на вкладку «Заменить).
2. Замените все знаки «=» на «###» или любые другие символы, которые больше нигде не встречаются в ваших реальных данных. Таким простым способом мы преобразуем наши формулы связывания в текст.

Это превратит ваши данные во что-то немного пугающее, как вы можете видеть на скриншоте ниже. Но не паникуйте — еще 2 шага и вы получите желаемый результат.

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

Способ 4. Перенести данные в Excel с помощью макроса VBA

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

Чтобы добавить макрос на лист, следуйте инструкциям в статье Как вставить и запустить код VBA в Excel .

Примечание. Транспонирование с помощью VBA имеет ограничение в 65536 элементов. Если ваш массив превышает этот предел, дополнительные данные будут автоматически удалены.

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

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

Способ 5. Сводная таблица.

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

Для начала нужно создать сводную таблицу (здесь есть подробные инструкции). Выберем исходные данные:

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

Перетаскиваем поля в макет таблицы и получаем результат: таблица повернута набок.

Преобразуйте столбцы в строки с помощью инструмента транспонирования

Если вам нужно выполнять преобразование строки в столбец достаточно часто, вы, вероятно, ищете более простой и быстрый способ. К счастью, у меня есть такой способ в моем Excel, как и у других пользователей дополнений Ultimate Suite

Позвольте мне показать вам, как перенести строки в столбцы в Excel за пару кликов с помощью инструмента транспонирования таблицы:

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

[expert_bq id=»1570″]Если вы хотите создать ссылки на исходные данные, чтобы повернутая таблица автоматически обновлялась при каждом изменении исходной таблицы, выберите соответствующий вариант. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Для анализа использована функция транспонировать в Excel, я использовал наглядно столбик «Наименование», которое сделал строкой и шапкой в таблице и столбец «Всего» которое превратилось в строковое значение:

Как перевернуть таблицу Excel — Мир ПК

• Функция АДРЕС (номер_строки; номер_столбца) — предоставляет адрес ячейки на основе номера строки и столбца на листе, например ADDRESS (2; 3) предоставит, например, ссылку на C2.
• Функция ДВССЫЛ (link_in_type_text) — преобразует текстовую строку, такую ​​как «F3», в реальную ссылку на ячейку F3.
• Функции ROW () и COLUMN () предоставляют номера строк и столбцов для данной ячейки, например = ROW (F1) вернет 1, а формула = COLUMN (A3) вернет 3.

Теперь дату преобразуем в формат «Дата», а продажи в «руб». В итоге получаем диапазон, аналогичный исходным данным, только с перевернутыми значениями и, что важно, связанный с исходными данными. Если мы будем изменять исходные данные, то автоматически они будут меняться на листе с транспонированными данными.

Как быстро перенести данные в Excel (пошаговое руководство)

Если у вас есть набор данных, и вы хотите транспонировать его в Excel (что означает преобразование строк в столбцы и столбцы в строки), делать это вручную — полное НЕТ!

Транспонировать данные с помощью специальной вставки

Специальная вставка может делать много удивительных вещей, и одна из них — переносить данные в Excel.

Предположим, у вас есть набор данных, как показано ниже:

Эти данные содержат регионы в столбце и кварталы в строке.

Теперь по какой-то причине, если вам нужно перенести эти данные, вот как вы можете сделать это с помощью специальной вставки:

Это мгновенно скопировало бы и вставило данные, но таким образом, чтобы они были транспонированы. Ниже представлена ​​демонстрация всего процесса.

Шаги, показанные выше, копируют значение, формулу (если есть), а также формат. Если вы хотите скопировать только значение, выберите «значение» в специальном диалоговом окне вставки.

Обратите внимание, что скопированные данные статичны, и если вы внесете какие-либо изменения в исходный набор данных, эти изменения не будут отражены в транспонированных данных.

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

Транспонировать данные с помощью специальной вставки и поиска и замены

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

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

Предположим, у вас есть набор данных, как показано ниже:

Вот шаги, чтобы перенести данные, но сохранить ссылки нетронутыми:

Это даст вам связанные данные, которые были перенесены. Если вы внесете какие-либо изменения в исходный набор данных, транспонированные данные будут автоматически обновлены.

Примечание. Поскольку в наших исходных данных A1 пусто, вам нужно вручную удалить 0 в G1. 0 появляется, когда мы вставляем ссылки, поскольку ссылка на пустую ячейку все равно будет возвращать 0. Кроме того, вам нужно будет отформатировать новый набор данных (вы можете просто скопировать и вставить формат только из исходного набора данных).

Транспонировать данные с помощью функции TRANSPOSE Excel

Функция Excel TRANSPOSE — как следует из названия — может использоваться для транспонирования данных в Excel.

Предположим, у вас есть набор данных, как показано ниже:

Вот несколько вещей, которые вам нужно знать о функции TRANSPOSE:

• Это функция массива, поэтому вам нужно использовать Control-Shift-Enter, а не только Enter.
• Вы не можете удалить часть результата. Вам нужно удалить весь массив значений, который возвращает функция TRANSPOSE.
• Функция транспонирования копирует только значения, но не форматирование.

Перенести данные с помощью Power Query

Power Query — это мощный инструмент, который позволяет быстро переносить данные в Excel.

Power Query является частью Excel 2016 («Получить и преобразовать» на вкладке «Данные»), но если вы используете Excel 2013 или 2010, вам необходимо установить его как надстройку.

Предположим, у вас есть набор данных, как показано ниже:

Вот шаги, чтобы перенести эти данные с помощью Power Query:

В Excel 2016

Обратите внимание: поскольку верхняя левая ячейка нашего набора данных была пустой, она получает общее имя Column1 (как показано ниже). Вы можете удалить эту ячейку из транспонированных данных.

В Excel 2013/2010

В Excel 2013/10 вам необходимо установить Power Query в качестве надстройки.

Щелкните здесь, чтобы загрузить плагин и получить инструкции по установке.

После установки Power Query перейдите в Power Query -> Данные Excel -> Из таблицы.

Это откроет диалоговое окно создания таблицы. Теперь выполните те же действия, что и для Excel 2016.

[expert_bq id=»1570″]Если вы хотите быстро поворачивать данные, чтобы просмотреть их в разных угловых обозначениях, попробуйте создать сводную таблицу для быстрой группировки данных путем перетаскивания полей из области строки в область столбцы или наоборот в списке полей сводной таблицы. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Затем переходим на новый лист, устанавливаем курсор, куда хотим вставить данные, и заходим в «специальную вставку»: нажимаем кнопку в меню «Специальная вставка» или нажимаем правой кнопкой мыши и выбираем пункт «Специальная вставка».

Транспонировать ячейки excel — Новости из мира ПК

• Это функция массива, поэтому вам нужно использовать Control-Shift-Enter, а не только Enter.
• Вы не можете удалить часть результата. Вам нужно удалить весь массив значений, который возвращает функция TRANSPOSE.
• Функция транспонирования копирует только значения, но не форматирование.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).