Посмотреть видео - Самый быстрый способ развернуть данные в Excel
Сводные таблицы отлично подходят, когда вы хотите проанализировать огромный объем данных за секунды. Он также позволяет быстро создавать различные представления данных простым перетаскиванием.
А для создания сводной таблицы вам необходимо иметь данные в определенном готовом формате сводной таблицы.
Во многих случаях вы, скорее всего, получите данные в форматах, не поддерживающих сводную таблицу.
Это часто случается, когда кто-то вручную собирает данные и создает формат, более удобочитаемый людьми (не сводные таблицы).
Что-то вроде того, что показано ниже:
Вышеуказанный формат данных - это то, что вы ожидаете получить в результате анализа сводной таблицы.
А что, если вы хотите проанализировать те же данные и посмотреть, каковы были общие продажи по каждому региону или по месяцам.
Хотя это легко сделать с помощью сводных таблиц, к сожалению, вы не можете передать указанные выше данные в сводную таблицу.
Поэтому вам нужно развернуть данные и сделать их удобными для сводных таблиц.
Хотя есть несколько способов сделать это с помощью формулы Excel или VBA, Power Query (получение и преобразование в Excel 2016) - лучший инструмент для отмены сводки данных.
Отмена сводки данных с помощью Power Query
Вот шаги, чтобы отключить данные с помощью Power Query:
(Если ваши данные уже находятся в таблице Excel, начните с шага 6 и далее)
- Выберите любую ячейку в наборе данных.
- Перейдите на вкладку "Вставка".
- Щелкните значок таблицы.
- В диалоговом окне «Создать таблицу» убедитесь, что диапазон правильный. При необходимости вы можете изменить диапазон.
- Щелкните ОК. Это преобразует ваши табличные данные в таблицу Excel.
- Выделив любую ячейку в таблице Excel, щелкните вкладку «Данные».
- В группе «Получить и преобразовать данные» щелкните значок «Из таблицы / диапазона».
- В открывшемся диалоговом окне «Создание таблицы» (если оно открывается) нажмите «ОК». Откроется редактор запросов, использующий данные таблицы Excel.
- В редакторе запросов щелкните правой кнопкой мыши столбец «Регион».
- Нажмите на опцию «Unpivot Other Columns». Это мгновенно отключит ваши данные.
- Измените название столбца «Атрибут» на более значимое, например «Месяцы».
- Когда у вас есть несведенные данные, рекомендуется убедиться, что все типы данных верны. В этом примере щелкните по одной ячейке для каждого столбца и посмотрите тип данных на вкладке «Преобразование». При необходимости вы также можете изменить тип данных.
- (Необязательно) Измените название своего запроса на «Продажи».
- Перейдите на вкладку «Главная» (в редакторе запросов).
- Нажмите "Закрыть и загрузить".
Приведенные выше шаги позволят развернуть ваш набор данных с помощью Power Query и вернуть его в Excel в виде таблицы на новом листе.
Теперь вы можете использовать эти данные для создания различных представлений с помощью сводной таблицы. Например, вы можете проверить общую стоимость продажи по месяцам или регионам.
Обновление запроса при добавлении новых данных
Все работает нормально.
Но что происходит, когда к нашему исходному набору данных добавляются новые данные.
Допустим, вы получаете данные за июль в том же формате, что и тот, с которого мы начали.
Нужно ли мне повторять все шаги еще раз, чтобы включить эти данные в мой несведенный набор данных?
Ответ - нет.
И это то, что так здорово в Power Query. Вы можете продолжать добавлять новые данные (или изменять существующие), и Power Query обновит их мгновенно, как только вы обновите их.
Позвольте мне показать вам, как это сделать.
Предположим, что ниже приведен новый набор данных, который я получаю (в котором есть дополнительные данные за июль):
Вот шаги, чтобы обновить уже созданный запрос и отключить эти данные:
- Добавьте эти новые данные к исходным данным, которые вы использовали для создания запроса.
- Поскольку вы добавляете данные в соседний столбец таблицы Excel, таблица Excel будет расширяться, чтобы включить в нее эти данные. Если этого не произошло, сделайте это вручную, перетащив маленький перевернутый значок «L» в правом нижнем углу таблицы Excel.
- Перейдите на вкладку «Данные» и нажмите «Запросы и подключения». Откроется панель со всеми существующими запросами.
- Щелкните правой кнопкой мыши запрос на продажу на панели запросов.
- Щелкните Обновить.
Вот и все! Ваши новые данные мгновенно отключаются и добавляются к существующим.
Вы могли бы заметить, что количество строк, отображаемых в запросе, обновляется, чтобы показать вам новые числа. В этом примере до обновления было 24, а после обновления - 28.
Это также означает, что если вы создали какие-либо сводные таблицы с использованием данных, полученных из Power Query, эти сводные таблицы также будут обновлены, чтобы показать вам обновленные результаты.
