Забыли пароль?


Хитрости »
Основные понятия (24)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (17)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (65)
Разное (42)
Баги и глюки Excel (4)

Вычисления в PowerQuery

Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе?


Скачать файл с исходными данными, используемый в видеоуроке:

  Вычисления в PowerQuery.xlsx (131,7 KiB, 738 скачиваний)


PowerQuery весьма мощный инструмент обработки данных внутри Excel, но если присмотреться – не видно даже намека на возможность использовать формулы. Нет хоть какого-то списка или значка по подобию Excel. Все потому, что PowerQuery не использует вычисления как таковые, если сравнивать с формулами в самом Excel. Но здесь есть другая возможность – добавлять целые вычисляемые столбцы. Для добавления такого столбца необходимо внутри редактора PowerQuery перейти на вкладку Добавить столбец -Пользовательский столбец. В первом окне(Имя нового столбца) задаем имя столбца, а во втором(Пользовательская формула столбца) – непосредственно формулу:
Добавить вычисляемый столбец

PowerQuery создаст новый столбец с заданным именем, в котором в каждой строке будут значения, вычисленные на основании заданной формулы. При этом все вычисления создаются исключительно линейно "внутри каждой строки" - сослаться на весь столбец или предыдущую строку возможности напрямую нет(как это делается в Excel).
Все доступные для использования в формуле столбцы перечислены в правом окне(Доступные столбцы). Для добавления в формулу какого-либо из столбцов можно просто щелкнуть по нему дважды левой кнопкой мыши или выделить нужный столбец и нажать кнопку Вставить.
На рисунке выше приведен пример простого объединения трех столбцов: Фамилия, Имя и Отчество при помощи формулы:
=[Фамилия]&" "&[Имя]&" "&[Отчество]
Она очень похожа на формулу Excel внутри «умной» таблицы - так же используются квадратные скобки с именами столбцов и нет никаких адресов ячеек. В результате этой формулы будет создан новый столбец ФИО, в котором будут последовательно объединены столбцы Фамилия, Имя и Отчество с пробелом в качестве разделителя.
Но это самый простой пример. На самом деле PowerQuery имеет большое количество встроенных функций, которые постоянно используются при любом нашем действии внутри редактора. Просто нам это не показывается напрямую. Но все эти функции можно посмотреть.
Во-первых они доступны в открытом доступе на сайте Microsoft: PowerQuery - функция языка M.
Во-вторых есть способ посмотреть список функций прямо из PowerQuery:
Вкладка Главная –Создать источник -Другие источники -Пустой запрос. В новом окне в строке формул записываем: =#shared и жмем Enter. Будет создан новый запрос, в котором будет выведет список всех доступных функций:
Посмотреть все функции PowerQuery

Теперь можно левой кнопкой мыши щелкнуть любую функцию из списка и появится окно с аргументами функции и её описанием.
По сути это практически тот же диспетчер функций в Excel, только разделитель аргументов здесь всегда запятая и самое главное – функции PowerQuery чувствительны к регистру. Например, если функцию Date.MonthName записать с маленькой буквы - date.MonthName, то получим синтаксическую ошибку. Поэтому очень важно копировать все формулы точь-в-точь как они записаны в справочнике.
Так же как и в случае с функциями Excel, функции PowerQuery делятся на категории(Text – текстовые, Date – дата, DateTime – дата и время и т.д.). И в отличии от функций в Excel для функций PowerQuery обязательно указывать из какой категории функция используется:
=Категория.ИмяФункции(аргументы)
Разберем пару коротких примеров формул с описанием того, что они делают:
Text.Upper([ФИО]) – преобразует все буквы указанного столбца в верхний регистр
Text.Lower([ФИО]) – преобразует все буквы указанного столбца в нижний регистр
Text.Reverse([Имя]) – записывает все буквы указанного столбца в обратном порядке
Text.Contains([Имя],"а") – определяет, встречается ли в тексте столбца Имя хоть одна буква «а».
Помимо простой вставки функции можно использовать и целые синтаксические конструкции. Правда для этого потребуется чуть больше знаний и очень желателен опыт работы с какими-либо языками программирования, т.к. именно таковым и является используемый в PowerQuery язык M. Возьмем простой пример: необходимо определить, встречается ли в тексте столбца Имя хоть одна буква «а» и если встречается – вернуть в вычисляемый столбец ФИО, а если нет – оставить пустым. В случае с Excel это выглядело бы так(в столбце A – имя, в столбце B – ФИО):
=ЕСЛИ(ЕОШ(НАЙТИ("а";A2));"";B2)
Довольно сложно выглядит, но более-менее понятно. В PowerQuery для выполнения той же задачи придется использовать всего одну функцию, но вдобавок к ней целую условную конструкцию:

if Text.Contains([Имя],"а") then [ФИО] else ""

что дословно так и читается: если(if) выражение(Text.Contains([Имя],"а")) выполняется, то(then) значение_если_истина([ФИО]), в противном случае(else) – значение если ложь("").
И здесь опять надо помнить про регистр: if, then, else – должны быть записаны в нижнем регистре, иначе PowerQuery просто их не распознает.

Но помимо работы с текстом в PowerQuery часто будет необходимо вычислять и другие показатели. Например, некоторые отклонения факта от плана. На примере простой таблицы и формул в ней:
Пример таблицы расчетов в Excel
В столбце J(Отклонение всего) записана формула:
=G4-C4
Её достаточно легко воспроизвести в PowerQuery:
[#"Факт, руб"]-[#"План, руб"]
А вот в столбце К(Ценовой фактор) формула сложнее:
=(H4-D4)*I4*СУММ($F$4:$F$15)
Помимо «линейных» вычислений внутри строки здесь применяется функция СУММ. И аналогом этой формулы в PowerQuery в данном случае будет функция работы со списками List.Sum. Но применять её необходимо по своим правилам. Если просто записать её, применив к имени столбца:
List.Sum([#"Факт, шт"])
То обязательно получим ошибку, т.к. обращение [#"Факт, шт"] подразумевает под собой извлечение данных только из одной ячейки строки и представляет собой одно единственное значение. А функция List.Sum требует указания целого столбца. Чтобы указать целый столбец исходной таблицы необходимо скопировать название первого шага(из расширенного редактора или подсмотрев в шагах запроса) и добавить его перед именем столбца, чтобы получилось что-то такое:
List.Sum(Источник[#"Факт, шт"])
Формула суммирования всего столбца в PowerQuery
В итоге получится такая формула:
=([#"Факт, Средняя цена за шт"]-[#"План, Средняя цена за шт"])*[#"Факт, Доля продаж"]*List.Sum(Источник[#"Факт, шт"])

Конечно, функций в PowerQuery и конкретно в языке M довольно много и хочется рассказать про все 😊 Но какие-то из них слишком хитрые и требуют особого внимания и отдельной статьи на каждую функцию. Целью же данной статьи было дать некую отправную точку для изучения этой возможности PowerQuery. Надеюсь, после прочтения статьи и просмотра видеоурока понимания работы с языком М будет чуть больше и вы сможете самостоятельно создавать свои вычисления и подбирать для них нужные функции.

Скачать файл с исходными данными, используемый в видеоуроке:

  Вычисления в PowerQuery.xlsx (131,7 KiB, 738 скачиваний)

Так же см.:
Объединение запросов при помощи PowerQuery
Собрать данные из файлов защищенных паролем PowerQuery
Собрать данные с таблиц с изменяющимися столбцами в PowerQuery


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки
Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2020 Excel для всех   Войти