Есть одна вещь, которая волнует всех – как сэкономить больше времени и работать с умом.
Что ж, я уже давал много приемов и трюков Excel, которые значительно облегчают работу. Но сегодня речь пойдет о POWER QUERY. Да-да, вы все правильно поняли.
POWER QUERY – это то, что может изменить вашу жизнь. Сегодня в этой статье я поделюсь с вами некоторыми удивительными приемами Power Query, которые могут начать использовать прямо сейчас.
Эти советы не только помогут вам сэкономить время, но и вдохновят использовать POWER QUERY для управления данными . По крайней мере, я на это надеюсь.
Прежде чем мы перейдем к приемам, нужно прояснить пару моментов.
Почему я должен использовать Power Query?
Делюсь с вами некоторыми серьезными причинами, чтобы вы в дальнейшем изучали Power Query.
1. Самый простой способ преобразовать ваши данные
Одной из основных причин использования Power Query – легкость преобразования данных. Обычно вы используете формулы и сводные таблицы, но с Power Query все основные задачи формирования данных могут быть выполнены в кратчайшие сроки.
2. Power Query в реальном времени
Это вторая важная причина, по которой Power Query выполняется в реальном времени, как разовая настройка.
Напишите запрос один раз, и вы можете обновлять его каждый раз, когда происходит изменение данных, также вы можете определить время автоматического обновления (Совет № 26).
3. Нужно просто несколько кликов
Как я уже сказал, обычно вы используете формулы и сводные таблицы для преобразования данных, но с POWER QUERY вы можете сделать многое, просто щелкнув мышью.
Нет необходимости писать формулы или коды.
Как установить Power Query – Шаги
Прежде чем вы начнете использовать эти приемы, в вашем Excel должен быть установлен Power Query.
И если вы один из тех пользователей Excel, у которых нет надстройки с Power Query, используйте эти шаги для ее установки.
Для Excel 2016 или Office 365:
Если вы используете версию Excel 365 или Excel 2016, она уже находится на вкладке «Данные» – «Скачать и преобразовать».
Для версий 2013 и 2010:
Прежде всего, загрузите надстройку отсюда (официальный сайт Microsoft). Как только вы загрузите файл, откройте его и следуйте инструкциям. После этого автоматически откроется вкладка «Power Query» на ленте Excel.
Если вкладка «POWER QUERY» не появляется, вам не о чем беспокоиться.
Вы можете добавить ее, используя опцию Надстройки. COM.
- Перейдите на вкладку «Файл» ➜ «Параметры» ➜ «Надстройки».
- В опциях «Надстройки» выберите «Надстройки COM» и нажмите.
- После этого отметьте галочкой «Microsoft Power Query for Excel».
- В конце нажмите ОК.
Все! Теперь у вас есть новая вкладка на ленте с названием «Power Query».
Откройте Power Query и загрузите в его данные
У вас есть разные способы добавить данные в редактор Power Query. Что ж, если у вас есть данные на рабочем листе, вы можете вставить их оттуда.
- Перейдите на вкладку «Данные» ➜ «Скачать и преобразовать» ➜ Из таблицы.
- Нажмите OK, чтобы преобразовать эту таблицу в таблицу Excel.
- И сразу после этого вы получите эту таблицу в редакторе Power Query, как показано ниже.
Лучшие 25 приемов Power Query для экономии времени в повседневной работе
Теперь пришло время изучить все эти советы по Power Query. Так что давайте начнем.
1. Заменить значения
У нас есть список с некоторыми значениями, и нам нужно заменить определенное значение или некоторые значения, чем-то другие.
С помощью Power Query мы можем создать запрос и заменить эти важные значения очень быстро. В приведенном ниже списке я хочу заменить имя «Алена» на «Алёна».
Давайте сделаем:
- Прежде всего, загрузите список в редактор Power Query.
- После этого выхода на вкладку «Преобразование» и нажмите «Замена значений».
- Теперь в поле «Значение для поиска» введите «Алена», а в поле «Заменить на» введите «Алёна» и после этого нажмите ОК.
- После того, как вы нажмете OK, все значения будут заменены новыми. Теперь можно нажать «Закрыть и загрузить», чтобы загрузить данные в таблицу.
А вот и лучшая часть: Вы только что создали запрос в режиме реального времени. При повторном обновлении запроса он заменит все вновь введенные значения.
2. Сортировка – по возрастанию и по убыванию
Как и при обычной сортировке, вы можете сортировать данные, используя Power Query. Я использую тот же список имен, который мы использовали выше. Вот, что нужно сделать.
- Прежде всего, загрузите данные в редактор
- В редакторе Power Query у вас есть две кнопки сортировки (по возрастанию и по убыванию).
- Нажмите на любую из этих кнопок, чтобы отсортировать.
- В конце нажмите« Закрыть и загрузить », чтобы загрузить данные в таблицу.
Вы, наверное, удивлены:« Зачем мне использовать Power Query, если я могу использовать обычную сортировку на листе? »
Как я уже писал, Power Query – работает в реальном времени. Вы можете создать запрос автообновления (Совет № 26), который будет обновляться через определенное время и автоматически сортировать ваши данные.
3. Удалить столбцы
Очень часто бывает, что вы получаете откуда-то данные, и вам нужно удалить некоторые столбцы из них. Дело в том, что вы должны удалить эти столбцы каждый раз, когда добавляете новые данные.
Но с Power Query вы можете создать запрос.
Вот шаги:
- Прежде всего, откройте данные в редакторе
- После этого выбора столбца или нескольких столбцов
- Теперь щелкните правой кнопкой мыши и выберите «Удалить».
- В конце нажмите «Закрыть и загрузить» , чтобы загрузить данные.
Совет. Также имеется опция «Удалить другие столбцы», в которой можно все невыбранные столбцы.
4. Разделить столбец
Точно так же как опция как «Текст по столбцам» есть в запросе: «Разделить столбец». Сейчас я расскажу, как это работает.
В приведенном ниже списке у вас есть имя и фамилия с дефисом между ними.
Теперь вам нужно разделить их на две колонки. Используйте эти шаги:
- Прежде всего, откройте список в редакторе
- После этого выбора столбца и перехода на вкладку «Преобразование» ➜ «Разделить столбец» ➜ «по разделителю».
- Выберите «Пользовательский» из выпадающего списка и введите «-» в него.
- Теперь у вас есть три варианта, как разбить столбец.
- Самый левый разделитель
- Самый правый разделитель
- По каждому вхождению разделителя
«У нас есть только один разделитель в ячейке, все три будут работать одинаково, но если у вас более одного» разделителя, вы можете выбрать нужный.
- В конце нажмите OK и нажмите «Закрыть и загрузить», чтобы загрузить данные.
5. Переименовать столбец
Вы можете просто переименовать столбец, щелкнув правой кнопкой мыши, а затем нажмите «Переименовать».
Совет: допустим, у вас есть запрос на переименование столбца, а кто-то другой переименовал его по ошибке. Вы можете восстановить это имя одним щелчком мыши.
6. Дубликат столбца
В Power Query есть простой способ создания дубликат столбца.
Все, что вам нужно сделать, это щелкнуть правой кнопкой мыши столбец, для которого вам нужен дубликат, а затем нажать «Создать дубликат столбца».
7. Объединить столбец
Обычно для объединения столбцов и ячеек мы используем формулы в Excel, но с Power Query это можно сделать намного проще.
Помните, мы разделили список сотрудников (Совет № 4). Теперь давайте объединим его, используя пробел.
Следуй этим шагам:
- Как только вы добавите данные в редактор, выберите оба столбца.
- После этого щелкните по ним правой кнопкой мыши и выберите «Объединить столбцы».
- Теперь в окне слияния столбцов выберите разделитель из выпадающего списка (здесь мы используем пробел) и добавьте имя для нового объединенного столбца.
- Нажмите OK и загрузите данные в таблицу.
Совет: вы также можете использовать собственный разделитель для объединения двух столбцов.
8. Транспонировать столбец или строку
В Power Query транспонирование – это проще простого. Да, всего один клик.
- Как только вы загрузите данные в редактор, вам просто нужно выбрать столбец (столбцы) или строку (и).
- Перейдите на вкладку «Преобразование» ➜ Таблица ➜ «Транспонировать».
И все.
9. Заменить/удалить ошибки
Это крутая вещь. Обычно для замены или удаления ошибок в Excel вы можете использовать опцию поиска и замены кода VBA. Но в Power Query все намного проще.
Посмотрите на столбец ниже, где у вас есть некоторые ошибки, вы можете заменить их.
Когда вы щелкнете правой кнопкой мыши по столбцу, у вас будет два варианта, как с ними справиться.
- Заменить ошибки
- Удалить ошибки
10. Изменить тип данных
Посмотрите, это обычное дело: У вас есть данные в столбце, но они не в нужном формате. Поэтому каждый раз нужно менять его формат.
В приведенном выше у нас есть столбец дат, но мы видим просто числа. Чтобы преобразовать их в дату, вы можете использовать Power Query. Это просто.
- Прежде всего, откройте данные в редакторе
- После этого выбора столбец и выхода на вкладку Преание.
- Теперь из типа данных выбора «Дата».
Вот, что получилось.
Совет: в большинстве случаев Power Query автоматически определяет тип данных, но если это не так, вы можете изменить его, как в приведенном выше примере.
11. Добавить столбец из примеров
Вот в чем дело: в Power Query есть возможность добавить образец столбца, который на самом деле не является образцом с текущим столбцом. Позволь мне привести пример:
В приведенном выше примере мы преобразовали число в дату, и теперь вам нужно указать название дня недели для этих дат.
Вместо использования формулы или другого параметра в Power Query мы можем использовать параметр «Добавить столбец из примеров».
Вот как это сделать:
- После того, как вы отправите свои данные в редактор, выберите столбец.
- Затем щелкните по нему правой кнопкой мыши и выберите «Добавить столбец из примеров».
- Здесь вы получите пустой столбец. Нажмите на первую ячейку столбца, чтобы получить список значений, которые вы можете вставить.
- Выберите «Название дня недели с Дата» и нажмите «ОК».
Все! Столбец заполнен.
12. Отменить таблицу
Я написал полное пошаговое руководство, чтобы отменить вывод данных в виде таблицы с помощью Power Query:
- Прежде всего, выбрать данные кросс- таблицы и перейти на вкладку «Данные».
- На внутренней стороне интерфейса к Скачать & Преобразовать → Из таблицы.
- Когда вы щелкнете по нему, он преобразует ваши данные кросс-таблицы в таблицу Excel (если это уже не так ).
- Данные мгновенно загрузятся в редактор Power Query.
- Отсюда нам нужно выбрать все столбцы, которые мы хотим отключить.
- Для этого выберите столбец Янв, нажмите и удерживайте расположение Shift и выберите столбец Дек.
- После этого щелкните правой кнопкой мыши и выберите «Отменить свертывание столбцов».
- Теперь все значения 12-ти ст олбцов разделены на два столбца. В одном – месяц, в другом – сумма.
- Последнее, что вам нужно сделать, это переименовать столбцы. Для этого щелкните правой кнопкой мыши по столбцу и переименуйте их.
- В конце нажмите «Закрыть и загрузить».
Теперь ваши данные кросс-таблицы преобразуются в простых данных, и вы можете использовать их для создания сводных таблиц и всего для дальнейшего анализа и составления отчетов о продажах.
13. Изменить регистр
Как в функциях, которые вы используете в Excel, в Power Query есть пакет опций для изменения регистра текста.
- нижний регистр
- ВЕРХНИЙ РЕГИСТР
- Каждое Слово С Прописной
Вы можете сделать это, щелкнув правой кнопкой мыши по столбцу и выбрать любой из трех указанных вариантов. Или перейдите на вкладку «Преобразование» ➜ «Столбец Текст» ➜ «Формат».
14. Усечь и Очистить
очистить данные или удалить ненужные пробелы, вы можете использовать опции Усечь и Очистить в Power Query.
Шаги просты:
- щелкните правой кнопкой мыши по столбцу или выберите все столбцы, если у вас несколько столбцов.
- Переключите опции преобразования и выберите любые из опций:
- Усечь : для удаления лишних пробелов из ячеек.
- Очистить : для удаления непечатаемых символов из ячеек.
15 . Добавить префикс/суффикс
Итак, у вас есть список значений, и в этот список вы хотите добавить префикс/суффикс в каждую ячейку. В Excel вы можете использовать функцию СЦЕПИТЬ, но в Power Query есть более простой способ.
- Прежде всего, выберите столбец, который нужно добавить префикс/суффикс.
- Затем перейдите на вкладку «Преобразование» ➜ Столбец Текст ➜ Формат ➜ Добавить префикс/Добавить суффикс.
- Как только вы нажмете одну из опций откроется диалоговое окно для ввода текста.
- После ввода текста нажмите ОК.
Опция работает одинаково для чисел, текста и дат.
16. Извлечь значения
Если вы разбираетесь в формулах, то выитесь со мной, что для извлечения текста или числа из ячеек необходимо комбинировать несколько функций. Power Query решает много подобных задач. У вас есть семь способов извлечения значений из ячеек. И да, одним щелчком мыши.
Просто посмотрите на варианты, которые у вас есть.
Эти опции закрывают все основные задачи, которые вы привыкли решать с помощью формул.
17. Только дата или время
Часто бывает, что у вас есть дата и время в одной ячейке, но вам нужна только одна из них.
Для этого в Power Query вам нужен всего лишь один клик, вот шаги.
- Выберите столбец, где у вас есть дата и время вместе.
- Если вам нужна Дата: щелкните правой кнопкой мыши ➜ Преобразование ➜ Только дата;
- Если хотите Время: щелкните правой кнопкой мыши ➜ Преобразование ➜ Только время.
18. Объедините дату и время
Теперь у вас есть отдельно дата и время. Значит, пора узнать, как их объединить.
Это довольно просто.
- Прежде всего, загрузите ваши данные в редактор.
- После выбора обоих столбцов (Дата и время) и продолжить на вкладке преобразования.
- Теперь из группы Столбец «Дата и время» выход к «Дата» и нажмите «Объединить дату и время».
Теперь у вас есть новый столбец с объединенными данными.
19. Округление чисел
Есть функции для округления чисел в Excel, но также есть и Power Query.
Вот варианты:
- Округление с уменьшением.
- Округление: Вы можете выбрать, до какого знака после запятой округлить.
Шаги:
- Прежде всего, откройте ваши данные в редакторе.
- Выберите столбец правой кнопкой мыши ➜ Преобразование ➜ Округление и выберите любой из трех вариантов.
Примечание . Когда вы выбираете опцию «Округление», вам нужно количество десятичных знаков для округления.
20. Расчеты
В Power Query есть варианты, которые вы можете использовать для выполнения расчетов. Посмотрите на приведенный ниже список.
- Стандартный
- Статистика
- Научный
- Тригонометрические
- Округление
- Информация
Вы можете найти все эти опции на вкладке Преобразование.
Для выполнения любого из этих расчетов вам нужно выбрать столбец и выбрать опцию.
21. Группировка
Как и сводные таблицы, Power Query – отличный вариант для группировки. Вы можете найти эту опцию на вкладке Преобразование.
Допустим, у вас большой набор данных и вы хотите создать сводную таблицу. Вот что вам нужно сделать:
- На вкладке «Преобразование» нажмите «Группировать по», откроется диалоговое окно.
- Теперь в этом диалоговом окне отображается выбор столбца, который вы хотите сгруппировать.
После этого дополнительного имени, выбора операции и столбец, в в котором у вас есть значения.
- В конце нажмите ОК.
Примечание. В параметрах «Группировать по» также есть несколько расширенных опций, которые можно использовать для создания многоуровневой групповой таблицы.
22. Удалить знак отрицания
Есть много методов удаления отрицательного знака, и один из них – это Power Query.
Щелкните правой кнопкой мыши по столбцу и в направлении Преобразование, а нажмите на Абсолютное значение.
23. Добавить настраиваемый столбец
Хотя в Power Query есть много опций, вы также можете создать пользовательский столбец, используя формулу расчета. Выполните следующие шаги, чтобы его создать:
- Прежде всего, добавьте на вкладку Добавление столбца ➜ Настраиваемый столбец.
- Здесь у вас появится диалоговое окно для создания формулы для использования в столбце (сейчас я хочу умножить количество на цену). Ведите формулу в поле формулы.
Совет. Когда вы вводите формулу, в диалоговом окне появляется сообщение, если в формуле есть какая-то ошибка.
24. Автообновление запроса
Из всех советов и приемов, которые янул здесь, этот наиболее важный. Когда вы можете создать запрос, вы можете установить таймер.
Вот шаги:
- На вкладке «Данные» нажмите «Существующие подключения»
- Теперь щелкните правой кнопкой мыши по нужному запросу, далее Изменить свойства подключения.
- Через минуты в открывшемся диалоговом окне.
- В конце нажмите ОК.
25. Создайте сводную таблицу из нескольких рабочих книг.
Иногда мы получаем или собираем данные из разных книг. И в этом случае создание сводной таблицы потребует усилий для объединения этих рабочих книг в одну.
. Выполните эти три простых шага, чтобы создать сводную таблицу из разных рабочих книг.
У меня есть четыре книги с данными о продажах для разных отделений.
Убедитесь, что все эти файлы в одной папке.
Шаг 1 – Объедините файлы с помощью Power Query
Прежде всего, нам нужно объединить все файлы в одну таблицу с Power Query.
- Перейдите на вкладку «Данные» ➜ «Скачать & Преобразовать» ➜ «Создать запрос» ➜ «Из файла» ➜ «Из папки».
- Теперь в окне выбора папки нажмите «Обзор» и выберите папку, в которой находятся все файлы.
- ОК.
- Вы увидите окно «Объединить файлы».
- В этом эксклюзивном листе с вашими данными во всех книгах. Важно: убедитесь, что во всех книгах указано одинаковое имя листа!
- После того, как вы нажмете OK, Power Query отправит все данные из рабочих книг в редакторе.
Шаг 2 – Подготовка данных для сводной таблицы
Теперь нам нужно внести небольшие изменения в наши данные, чтобы подготовить их к сводной таблице. Если вы посмотрите на данные, у нас появился дополнительный столбец с именем исходного файла.
- Щелкните правой кнопкой мыши по этому столбцу и выберите «Разделить столбец» ➜ «По разделителю».
- В окне разделителя выбора« Пользовательский », добавить«. » в качестве разделителя и выберите «Самый левый разделитель».
- Нажмите ОК.
- этого удалите второй столбец.
- Теперь переименуйте первый столбец.
- Теперь данные готовы. Нажмите на закрыть и загрузить.
Шаг 3 – Вставьте сводную таблицу
На данный момент у нас есть новая рабочая таблица в рабочей книге с объединенными данными из всех четырех файлов. Теперь пришло время создано из них сводную таблицу.
- Выберите таблицу и перейдите на вкладку «Вставка» и нажмите кнопку «Сводная таблица».
- Откроется окно создания сводной таблицы. Нажмите OK, и вы получите новую сводную таблицу в своей книге.
Поздравляю! Вы успешно создали новую сводную таблицу из разных файлов.
Заключение
Вы можете не пользоваться POWER QUERY. Но… Многие приведенные выше советы вдохновляют вас использовать его все больше и больше.
Многие… Многие из приведенных выше советов вдохновляют вас использовать его все больше и больше. /p>
Не поделиться своими мнениями со мной в разделе комментариев. И, пожалуйста, не забудьте поделиться этой информацией со своими друзьями, я уверен, что они оценены.
Спасибо за большой объем полезной информации. Хотел бы дополнить п.10 «Изменить тип данных». Описанный вами метод преобразования значений в столбцы указаны как число (пиктограмма «1,2,3»). В моем случае исходные данные в формате «дата» PowerQ почему-то воспринял как «текст»! Т.е. все даты стали пятизначным числом но в формате текста. В этом случае пришлось вначале текст преобразовать в число и только потом число преобразовать в дату. Если пытаться текст сразу преобразовать в дату получим Ошибка.
Спасибо за информацию.
Подскажите, в аналогичном примере задания 25, почему-то 2 файла объединяет, а с
Одинаковая структура, но вот почему-то никак не получается объединить больше двух.
Не сталкивались ли с такой проблемой?
Файл cvs воспринимаеться как текстовый, поэтому с таблицами он не стыкуется. Необходимо подтягивать отдельно.
Дмитрий! Огромное Вам спасибо! Все нужные решения в одной статье, отличная памятка для новичков. Гениально!
Хороший и полезный материал.
Знакомство с Power Query на примерах транспонирования Таблицы Excel
Power Query – это инструмент MS Excel, предназначенный для импорта из самых различных источников и обработки данных. Впервые появился в 2013 году и был доступен в виде специальной надстройки, которую можно скачать с официального сайта Microsoft и установить на Excel 2010-2013. После установки и подключения на ленте Excel появится соответствующая вкладка.
В Excel 2016 Power Query уже встроен в ядро программы. Команды управления запросами находятся во вкладке Данные , в группе Скачать и преобразовать (в английском варианте Get & Transform ).
Далее будем использовать привычное название Power Query.
На самом деле в Excel и раньше можно было импортировать данные. Для этого в той же вкладке Данные была и есть целая группа команд Получение внешних данных .
Однако их возможности и удобство использования сильно ограничены.
После появления Power Query в среде пользователей Excel произошло потрясение, сравнимое появление сводных таблиц. Это не шаг, а прыжок вперед, соответствующий любой аналитик (и обычный пользователь Excel), имеющий дело с большими и обновляемыми данными из разных источников, может ускорить свою работу в десятки раз. Да, в десятки, если не в сотни. Ведь как раньше делался, скажем, отчет? Импортируются данные (из разных источников), очищаются, связываются вместе с помощью формул ВПР, затем делаются необходимые расчеты, все агрегируются с помощью сводных таблиц в краткий отчет. Периодически эти действия нужно повторять, т.к. традиционными методами (без VBA) очень трудно автоматизировать все шаги. Сегодня этому кошмару пришел конец. В Power Query достаточно один раз все настроить и выполнить все операции импорта, обработки и выгрузки данных повторяться нажатием одной кнопкой обновления.
Power Query работает на специальном языке программирования под названием M , с помощью которого записываются последовательные шаги обработки данных. Однако есть и пользовательский редактор с кнопками, поэтому быть программистом не обязательно. Здесь уместна аналогия с записью обычных макросов. Включили запись, произвели действия, закончили запись. В любой момент запустили выбранный макрос.
Вкратце алгоритм работы Power Query таков:
1. импорт данных из выбранных источников данных
2. обработка полученных данных
3. выгрузка
Список источников довольно разнообразный: от текстовых файлов до внешних баз данных и интернета. Можно легко присоединиться к данным внутри самого MS Excel.
На этапе обработки также производят операции по очистке, связыванию, группировке, математическому преобразованию и т.д. Специфика работы именно с такими, плохо организованными и неочищенными данными, объясняет набор инструментов Power Query. Частично они повторяют то, что есть в Excel, но есть и новые, которые значительно расширяют привычный функционал Эксель. Важнейшей особенности работы в Power Query – это то, что все шаги записываются. Это дает возможность нажатием одной кнопки повторить все операции. Объединяя возможность подключения к данным внутри Excel и новые методы их обработки, мы получаем дополнительные инструменты, которые делают работу в Excel удобнее и быстрее.
На последнем этапе запроса обработанные данные выгружаются в указанном месте либо создается только соединение (часто запросы – это только промежуточный этап обработки данных). Но об этом в другом раз.
В качестве наглядного примера рассмотрим следующую задачу. Имеются данные, которые нужно транспонировать, то есть строки сделать столбцами, а столбцы строками.
В целом это не проблема, т.к. в Excel существует минимум два способа транспонирования.
Первый и самый быстрый способ – используйте Специальной вставкой , поставив галочку напротив транспонировать .
Отличный вариант, но одноразовый. В смысле, нет никакой связи между результатом и результатом. Поэтому при любом изменении данных все нужно повторить снова. Это минус.
Второй способ транспонирования – используйте функции ТРАНСП . Это формула массива, поэтому для ее вставки нужно вначале указать точный диапазон и использовать с помощью комбинации Ctrl + Shift + Enter .
Теперь при изменении данных в появнике автоматически обновится и транспонированный диапазон. Но здесь также есть серьезные недостатки. Во-первых, для вставки формулы ТРАНСП нужно заранее подсчитать, сколько строк и столбцов занимает диапазон, что, мягко говоря, не всегда удобно. Во-вторых, при изменении размера механизма работает, т.к.
Итого получается, что мы не можем сделать динамическое транспонирование в изменяющемся диапазоне. Так да не так. С появлением Power Query задача решается быстро, без шума и пыли.
Транспонирование таблицы результатов Power Query
Первым делом нужно сделать запрос на источник данных. Нас интересуют данные из этой же книги Excel. Power Query не видит обычных ячеек, а только именованные диапазоны и Таблицы Excel. Как правило, используйте Таблицы Excel. Для преобразования обычного диапазона в таблицу рекомендую горячую комбинацию клавиш Ctrl + T .
Теперь активируем любую ячейку Таблицы с данными и нажимаем кнопку Данные – Скачать и преобразовать – Из таблицы .
Открывается окно редактирования Power Query.
Выглядит, как другая программа, но это только отдельное окно внутри Excel. Интерфейс состоит из пяти частей:
1. Инструменты редактирования – лента, на которой находятся команды Power Query.
2. Строка формул – здесь записывается код языка М для выделенного в данный момент шага обработки.
3. Запросы – скрываемая панель для навигации между запросами текущих книг.
4. Панель результата – место, где отображается результат обработки данных этапа выделенного шага.
5. Параметры запроса – панель с вызовом запроса (можно проверить) и перечнем созданных шагов, которые также можно редактировать.
Выделив любой из шагов, мы увидим состояние данных на соответствующем этап.
Название запроса лучше всего изменить на более говорящее. Довольно часто в использовать сразу несколько запросов, поэтому в них нужно ориентироваться. Назовем «Транспонирование».
Из предыдущего рисунка видно , что мы еще ничего не сделали, а два шага уже записаны. Как так? Все просто. Первый шаг – это обращение к новому столбцу, а также автоматическое определение типа данных у каждого столбца. Поэтому все в порядке.
Вернемся к условию задачи. Нужно транспонировать вот эту таблицу.
Обратим внимание, что заголовки перенеслись из заголовков Таблицы Excel, которую мы использовали в качестве источника. Однако транспонирование происходит без заголовков. Поэтому, чтобы избежать потерь названий столбцов, «опустим» их названия в первую строку таблицы Преобразование – Таблица – Использовать первую строку в качестве заголовков – Использовать заголовки как первую строку .
Таблица с данными получит такой вид.
Теперь можно транспонировать. Используем команду Преобразование – Транспонировать .
Таблица мгновенно изменяется.
Сделаем первую строку назад заголовками. Можно через Преобразование – Таблица – использовать первую строку в качестве заголовка через кнопку в верхнем левом углу от таблицы.
Получим конечный результат обработки.
Задача решена. Все шаги преобразования данных записаны и видны справа.
Осталось измененные данные вернуть в Excel с помощью команды Главная – Закрыть – Закрыть и загрузить .
Если ее нажать, то результат загрузится на новый эксель и будет представлять из себя Таблицу Excel с названием, как у запроса. Но давайте пока зайдем в раскрывающийся список, чтобы посмотреть опции выгрузки. В раскрывающемся списке выберем Закрыть и загрузить в… Откроется следующее окно.
Если выбрать Только создать соединение, выгрузки не произойдет. Такой вариант применяют, если требуется дальнейшая обработка или использование этого запроса. Для выгрузки в Excel можно выбрать Новый лист либо указать конкретный диапазон. Если установить галочку Добавить эти сведения в модель данных , то результат запроса даже без выгрузки в Excel можно использовать в модели данных или Power Pivot. Этот вариант позволяет обрабатывать миллионы (миллионы!) Строк, т.к. на обработке данных в памяти требуется гораздо меньше ресурсов. Оставляем все по умолчанию и жмем Загрузить . В процессе выгрузки таблица имеет серенький цвет, а когда выгрузка завершена, становится зелененькой.
Вот и все, дело сделано, мы получили транспонированную таблицу исходных данных.
Самое интересное происходит далее. Если добавить новые данные, то для повторения всех действий достаточно обновить запрос через правую кнопку в панели запросов (см. чуть ниже), либо во вкладке Данные – Подключения – Обновить все .
Добавим в исходную таблицу данные о продажах во втором квартале.
А теперь обновим запрос.
Это просто праздник какой-то! (с).
Обратим внимание, что справа в окне Excel появляется панель для управления существующими запросами.
Их может быть много, но у нас только один. Сразу под названием видно, сколько загружено строк. Здесь же указываются ошибки, если они есть. Это важно для контроля. Если подвести курсор мыши к названию, то откроется окно с кратким описанием запроса и командой управления снизу.
Можно вновь войти в редактирование запроса, удалить его и т.д. Эти же и некоторые другие команды появились в контекстном меню после кликания по названию запроса правой кнопкой мыши.
Перечислим наиболее часто используемым среди них.
Изменить – команда открытия окна редактирования. Эквивалентно двойному двойию левой кнопки мыши по самому запросу.
Обновить – обновление выбранного запроса (если нужно обновить только один запрос, а не все).
Загрузить в… – изменение места загрузки (в таблицу, модель или создание только соединения)
Дублировать – сделать выбранную панель запроса.
Другие команды не менее важны, но их рассмотрим другой раз.
Запросы книги можно закрыть или снова отобразить с помощью команды Данные – Скачать и преобразовать – Показать запросы .
Итак, мы узнали, что такое Power Query. На примере транспонирования данных увидели, он облегчает и ускоряет работу в Excel.
Серия видеоуроков о Power Query.