Unlimited Powerpoint templates, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Business
  2. Microsoft Excel
Business

Как переделать столбцы в строки с Power Query в Excel

by
Difficulty:AdvancedLength:MediumLanguages:

Russian (Pусский) translation by Ellen Nelson (you can also view the original English article)

Проблема не в том, что у нас нехватка данных, проблема в том, что у нас их слишком много; что просто уже сложно найти суть в этих данных. Вам нужен способ переорганизации и очистки ваших данных, чтобы сделать их полезными. Microsoft Excel это очень удобный инструмент, но вам всё равно нужно почистить данные, прежде чем их можно будет анализировать и просматривать.

Вот где Power Query вступает в игру. Power Query, также называемый «Подключение и преобразование данных», находится в Excel и автоматизирует процесс очистки данных.

Одним из самых популярных шагов очистки данных является получение данных из столбцов и преобразование их в строки.  Поднимите ваши знания Excel на следующий уровень, научившись «отменять свёртывание» данные с помощью Microsoft Power Query. В этом уроке вы узнаете, как использовать Power Query для преобразования столбцов в строки в Excel.

Зачем преобразовывать столбцы в строки?

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

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

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

Converting columns to Rows in Microsoft Excel
Преобразование столбцов в строки в Microsoft Excel.

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

Вкратце: преобразование данных из столбцов в строки облегчает работу. Power Query делает это запросто, в пару кликов. Давайте узнаем, как:

Как быстро преобразовать столбцы в строки в Excel (смотри и учись)

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

Давайте также рассмотрим написанную версию этих инструкций, чтобы узнать больше о Power Query и «отмене свёртывания» данных в Excel.

Знакомство с Microsoft Power Query для Excel

Power Query, формально называемый «Подключение и преобразование данных» в Excel 2016, это инструмент с двумя основными функциями:

  1. Вы можете использовать его для получения данных, например, указав на другие файлы Excel, базы данных и даже веб-страницы для переноса данных в таблицу.
  2. Он может помочь вам преобразовать данные, что означает перестроить их на лету, добавить больше столбцов или объединение с другими наборами данных.
Power Query Window in Microsoft Excel
Когда вы отправляете данные для работы в Power Query, он открывается в новом окне поверх Excel, где вы можете применить преобразование данных.

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

Установка Power Query

Power Query встроен в Excel 2016, но вы можете установить его для более ранних версий Excel в качестве надстройки. К сожалению, Power Query доступен только для Excel в Windows.

Чтобы установить Power Query для Excel в Windows (требуется только для Excel 2013 и Excel 2010, поскольку эта функция встроена в Excel 2016) перейдите на веб-сайт Microsoft, чтобы загрузить и установить надстройку Power Query.

Отправка данных в Power Query

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

Чтобы отменить свёртывание данные в Excel, вам сначала необходимо конвертировать данные Excel в таблицу, если они ещë не в этом формате. Для работы с данными, вы можете передать в Power Query таблицу данных.

Если вы хотите следовать со мной в этом уроке, я сделал пример данных в этом файле Excel, который доступен бесплатно.

Преобразование в таблицу

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

Convert Data to Table format in Excel
Чтобы преобразовать ваши данные в таблицу, выберите кнопку Форматировать как таблицу на ленте Excel.

Передача в Power Query

Как только ваши данные окажутся в табличном формате, перейдите на вкладку Данные на ленте Excel и нажмите кнопку Из таблицы, чтобы отправить данные таблицы в Power Query и преобразовать их.

Data from Excel Table
Нажмите Данные > Из таблицы, чтобы отправить данные из стандартного представления Excel в Power Query.

Это отправляет данные в новое окно, которое открывается поверх Microsoft Excel. Это совершенно новое окно с множеством функций.

Отмена свертывания данных

На этом этапе вы должны увидеть что-то похожее на снимок экрана ниже. Это окно, в котором находятся инструменты, необходимые для отмены (или сброса данных).

Чтобы отменить свёртывание данных в Excel, выделите все столбцы, которые вы хотите сбросить. В принципе, Power Query преобразует каждый из этих столбцов в самостоятельные строки. Для данного здесь примера, я выделил каждый из столбцов с типом проекта:

Unpivot Columns in Microsoft Excel
Выделите все столбцы, которые вы хотите развернуть в строки, затем щелкните Отменить свёртывание столбцов, чуть повыше ваших данных.

Как только вы нажмете на Отменить свёртывание столбцов, Excel преобразует ваши данные из столбцов в строки. Каждая строка является самостоятельной записью, готовой перейти в сводную таблицу или подойдёт для таблицы данных.

Microsoft Excel spreadsheet unpivoted data
Перевернутые данные появляются в строках вместо столбцов.

Эта особенность кажется волшебной. Теперь давайте вернем данные в Microsoft Excel, чтобы начать с ними работать.

Закрыть и загрузить

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

Unpivoted data with close and load
После того, как вы закроете и загрузите свой запрос, Excel поместит перевёрнутые данные на новый лист и, с правой стороны, покажет запрос, который мы только что построили.

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

Обновление запроса

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

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

Added new client data into Excel spreadsheet
В этом примере, я добавил нового клиента в исходный набор данных, в выделенной строке.

Теперь вернемся на вкладку, где загружаются наши не сформированные данные. Когда мы нажимаем внутри таблицы, мы видим меню Параметры запросов, расположенные рядом с вашими данными. Нажмите кнопку Обновить (в правой части меню), чтобы обновить запрос.

Это произведёт возврат к исходной таблице и повторит шаги запроса. Поскольку мы добавили новые строки в нашу исходную таблицу данных, Power Query добавит эти новые строки в запрос и запустит по ним преобразование.

Updated data in Excel spreadsheet
Нажмите кнопку Обновить, рядом с названием запроса на боковой панели, чтобы добавить новые строки в ваш преобразованный набор данных.

Это одна из моих любимых функций Power Query. Мы добавили данные в исходный исходный файл, но в один клик обновили и преобразовали новые данные.

Повторите и продолжайте дальнейшее обучение по использованию Microsoft Excel

Из этого урока вы узнали, как легко преобразовать столбцы в строки в Microsoft Excel. Power Query — это гибкий и надежный инструмент для получения и изменения данных на лету. Изучите другие приёмы Microsoft Excel из этих уроков на Envato Tuts+:

Как вы используете Power Query или другие инструменты для отмены свёртывания ваших данных? Если хотите поделиться советом, обязательно сделайте это ниже в комментариях.

Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.