Посмотреть видео - Как перенести данные в 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 (как показано ниже). Вы можете удалить эту ячейку из транспонированных данных.
В Excel 2013/2010
В Excel 2013/10 вам необходимо установить Power Query в качестве надстройки.
Щелкните здесь, чтобы загрузить плагин и получить инструкции по установке.
После установки Power Query перейдите в Power Query -> Данные Excel -> Из таблицы.
Это откроет диалоговое окно создания таблицы. Теперь выполните те же действия, что и для Excel 2016.