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

0 Акции

Если у вас есть набор данных и вы хотите транспонировать его в Excel (Это означает преобразование строк в столбцы и столбцов в строки.), ручное исполнение совершенно невозможно!

Содержание:

1 Транспонировать данные с помощью специальной вставки
2 Транспонируйте данные, используя специальную вставку, а также найдите и замените
3 Транспонировать данные с помощью функции Excel ТРАНСП
4 Транспонировать данные с помощью Power Query
- 4.1 в Эксель 2016
- 4.2 В Excel 2013/2010
5 О, привет, приятно познакомиться.

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

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

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

Данные имеют регионы в столбцах и кварталы в строках.

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

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

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

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

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

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

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

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

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

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

Выберите набор данных (A1: E5).
Дублируйте его (Control + C или щелкните правой кнопкой мыши и выберите «Дублировать»).
Теперь вы можете вставить транспонированные данные в новое место.В этом примере я хочу скопировать в G1:K5, поэтому щелкните правой кнопкой мыши ячейку G1 и выберите «Специальная вставка».
В диалоговом окне «Специальная вставка» нажмите кнопку «Вставить ссылку».Это даст вам тот же набор данных, но здесь ячейки связаны с исходным набором данных (например, G1 связан с A1, G2 связан с A2 и т. д.).
Выбрав эти недавно скопированные данные, нажмите Control + H (или перейдите в «Главная» -> «Правка» -> «Найти и выбрать» -> «Заменить»).Откроется диалоговое окно «Найти и заменить».
В диалоговом окне «Найти и заменить» используйте следующие команды:
- В Найти что :=
- Замените на: !@# (обратите внимание, что я использовал !@#, так как это уникальная комбинация символов, которая вряд ли будет частью ваших данных. Вы можете использовать любой такой уникальный набор символов).
Щелкните Заменить все.Это заменит равенство в формуле, и у вас будет !@#, за которым следует ссылка на ячейку в каждой ячейке.
Щелкните правой кнопкой мыши и скопируйте набор данных (или используйте Control+C).
Выберите новое место, щелкните правой кнопкой мыши и выберите «Специальная вставка».В этом примере я вставляю его в ячейку G7.Вы можете вставить его куда угодно.
В диалоговом окне «Специальная вставка» выберите «Транспонировать» и нажмите «ОК».
Скопируйте и вставьте эти недавно созданные транспонированные данные в место, где они были созданы.
Теперь снова откройте диалоговое окно «Найти и заменить» и замените !@# на =.

связанный вопрос Исправьте ошибку Windows 5 Spotlight не работает за 10 шагов

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

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

Транспонировать данные с помощью функции Excel ТРАНСП

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

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

Вот шаги для его транспонирования:

Выберите ячейки, в которые вы хотите транспонировать набор данных.Обратите внимание, что вам нужно выбрать точное количество ячеек в качестве необработанных данных.Так, например, если у вас есть 2 строки и 3 столбца, вам нужно выбрать 3 строки и 2 столбца ячеек, в которые вы хотите перенести данные.В этом случае, поскольку есть 5 строк и 5 столбцов, вам нужно выбрать 5 строк и 5 столбцов.
Введите =ТРАНСП(A1:E5) в активной ячейке (должна быть выбрана верхняя левая ячейка и нажмите Control Shift Enter.

Это транспонирует набор данных.

Вот что вам нужно знать о функции ТРАНСП:

Это функция массива, поэтому вам нужно использовать Control-Shift-Enter вместо простого Enter.
Вы не можете удалить часть результата.Вам нужно удалить весь массив значений, возвращаемых функцией ТРАНСП.
Функция транспонирования копирует только значение, а не формат.

связанный вопрос Новости и интересы - функции и настройки в Windows 10

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

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

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

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

Вот шаги для переноса этих данных с помощью Power Query:

в Эксель 2016

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