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

Содержание

Транспонировать данные с помощью специальной вставки
Транспонировать данные с помощью специальной вставки и поиска и замены
Транспонировать данные с помощью функции TRANSPOSE Excel
Перенести данные с помощью Power Query

Посмотреть видео - Как перенести данные в Excel

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

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

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

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

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

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

Выберите набор данных (в данном случае A1: E5).
Скопируйте набор данных (Control + C) или щелкните правой кнопкой мыши и выберите копию.
Теперь вы можете вставить транспонированные данные в новое место. В этом примере я хочу скопировать в G1: K5, поэтому щелкните правой кнопкой мыши ячейку G1 и выберите «Специальная вставка».
В специальном диалоговом окне вставки отметьте опцию транспонирования в правом нижнем углу.
Щелкните ОК.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вот шаги, чтобы переставить его:

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

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

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

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

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

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

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

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

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

В Excel 2016

Выберите данные и перейдите в «Данные» -> «Получить и преобразовать» -> «Из таблицы».
В диалоговом окне «Создание таблицы» убедитесь, что диапазон указан правильно, и нажмите «ОК». Это откроет диалоговое окно редактора запросов.
В диалоговом окне редактора запросов выберите вкладку «Преобразование».
На вкладке Transform перейдите в Table -> Use First Row as Headers -> Use Headers as First Row. Это гарантирует, что первая строка (содержащая заголовки: Q1, Q2, Q3 и Q4) также обрабатывается как данные и транспонируется.
Щелкните транспонировать. Это мгновенно переместит данные.
Щелкните Использовать первую строку в качестве заголовков. Теперь это сделает первую строку транспонированных данных заголовками.
Перейдите в Файл -> Закрыть и загрузить. Это закроет окно Power Editor и создаст новый лист, содержащий транспонированные данные.

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