Всем добрый день,
Давно не писал статьи ввиду загрузки делами ) Хотя я обычно пишу про Apple, решил поделиться с читателями тем, чем пользуюсь сам много лет - а именно активным использованием инструмента под названием Power Query, уже давно внедренного в Excel. Но, к сожалению, почему-то о формулах в Excel знают и пользуются многие, а то что формулы Power Query по возможностям превосходят стандартные формулы Excel на много порядков, до сих пор не так много кто знает и тем более этим инструментом активно пользуется.
Данная статья рассчитана на тех, кто уже неплохо работает с формулами Excel, либо ориентируется в основных понятиях программирования и хочет максимально быстро освоить Power Query. Возможно, смешанный и довольно быстрый стиль изложения будет не всем понятен.
Что это такое и зачем нужно?
Power Query (далее - PQ) относится к инструментам класса ETL - то есть, Extract, Transform, Load (Извлечь - Преобразовать - Загрузить). Фактически, это такой комбайн, работающий с любыми данными. Вообще, совершенно любыми.
Можно сказать что это некий аналог формул, работающий с чем угодно и как угодно. В отличие от стандартных формул, работа которых ограничена только значениями ячеек Excel и, в лучшем случае, с диапазонами ячеек.
Например, если вы бухгалтер, вы прямо из Excel можете незаметно для пользователя загрузить данные с какого-нибудь сайта (например, курс валют с сайта ЦБ РФ), выделить курс доллара на сегодняшний день и пересчитать данные в таблице по поставкам в рубли или доллары с учетом этого курса.
Если вы учитель в школе, вы можете использовать Power Query для уменьшения времени на подготовку бумажных отчетов, которые многие учителя до сих пор делают вручную. Казалось бы, у нас внедрены цифровые технологии в школе, но судя по разговорам с несколькими людьми, работающими учителями, цифровизация-то есть, да почему-то не совсем та, которая позволила бы уменьшить количество бумажной работы. Так как, несмотря на цифровизацию, отчетов учителям приходится делать вручную все равно очень много.
Если вы разработчик или аналитик и вам нужно сгенерировать сложный XML или JSON из выборки с какой-нибудь базы, ну или что-то подобное - вам точно сюда.
То есть вы можете выполнять любые операции с любыми наборами данных, преобразовывая так, и получая из то, что вам требуется. Возможности практически не ограничены - работа с таблицами самого Excel, работа с внешними файлами, базами данных, сайтами и так далее.
Это действительно невероятные возможности. Excel уже давно не табличка с формулами, как о ней думают многие.
С другой стороны, Power Query - это совсем не скрипты VBA, которые закрывают совсем другую часть функционала Excel, фактически автоматизируя его. Power Query - это работа с данными (автоматизировать сам Excel тут невозможно), VBA - это автоматизация с доступом к объектом модели Excel (работать с данными тут крайне неудобно и очень медленно). Можно и нужно объединять эти два разных инструмента для достижения необходимого результата.
Зачем эта статья?
Про Power Query в интернете полно материала, но, с моей точки зрения, там PQ показан слишком сложно и запутанно для пользователя, особенно того, кто ранее активно пользовался формулами в Excel. В этой статье я хотел бы показать что, несмотря на кажущуюся довольно высокую сложность, сам Power Query ничем не сложнее формул, ведь он, по сути, является такой же формулой, ну разве что с более сложным синтаксисом. Точнее, Power Query, как и формулы Excel являются разновидностями функциональных языков программирования
Сам Power Query использует специальный язык, который называется M. Да, вот так, одной латинской буквой. На самом деле обычные формулы в EXCEL ведь тоже пишутся на каком-то своем языке формул, у которого даже нет названия.
В Power Query есть пользовательский интерфейс, позволяющий любому человеку без какого-либо знания языка M в довольно удобной форме провести обработку данных пошагово. Вот так это выглядит на примере получения курса доллара на момент выполнения запроса с сайта ЦБ РФ:
"Под капотом" такой редактор все равно создает формулу на языке М, которую потом можно отредактировать вручную. Но мы вообще не будем им пользоваться так как этот графический редактор уводит от сути и, как тесная тельняшка, лишь ограничивает нас. Предлагаю читателю сразу пользоваться сразу всей мощью языка M. Не нужно этого бояться, он совсем не сложный :-)
Ведь давайте будем честны - сложные формулы в Excel, а без них никак в более-менее приличных расчетах в Excel, совсем не просты для понимания человеком. Особенно с учетом того что в формулах нельзя использовать форматирование (например, переносы строк и т.д.) и нельзя писать комментарии, как и делать многое другое.
Ну вот, например, просто ли понять что делают эти формулы Excel, приведенные на сайте Reddit? Там, если что есть подсказки )
Итак, основной целью данной статьи обозначу знакомство читателя, уже умеющего хорошо работать и с классическими формулами Excel, с "новым" языком M, на основании которого можно и нужно делать вещи, просто немыслимые ранее с классическими формулами.
Power Query vs Формулы Excel
Cледует отметить, что у каждого инструмента инструмента (и Power Query не исключение) есть свои плюсы и минусы. Вряд ли микроскоп хорош для забивания гвоздей, и вряд ли молоток хорош для исследования бактерий. Так и здесь:
- Формулы прекрасно подходят для вычислений, где речь идет всего лишь о значениях одной или нескольких (десятков или даже сотен) ячеек. Power Query работает скорее с таблицами, где отдельная ячейка, условно, как деталь на конвейере. Особенно если эти таблицы состоят из тысяч или более ячеек или имеют сложную структуру данных. У Power Query есть колоссальное преимущество, обеспечивающее быструю работу с такими данными - часть запроса выполняется на внешней стороне. Например, если вы подключаетесь к базе данных, получаете из нее данные, а потом фильтруете их в Power Query, фильтр в реальности будет выполнен на стороне СУБД, что будет очень быстро. И так далее.
- Если вам нужны вычисления, жестко завязанные на порядок ячеек (а не на ключ!), например, когда пятая строка в таблице зависит от третьей потому что это именно третья строка, а четвертая от девятой - формулы почти наверняка будут удобнее. Хотя это можно сделать и в Power Query без проблем, но формулы будут удобнее и, возможно, в некоторых случаях даже быстрее.
- Если вы любите украшать ячейки разными цветами, выделять текст и прочее, как многие любят, Power Query не для вас. Power Query - это скорее про работу с данными, приведенными к первой нормальной форме.
То есть:
- Каждая колонка имеет уникальное имя и одинаковый тип данных во всех ячейках этой колонки.
- Вся информация содержится только в значениях ячеек. Это значит что никаких выделений цветом и т.д., ведь фактически выделение цветом это по сути тоже какая-то информация (например, чтобы показать важность и т.д.). Например, чтобы привести "цветную" таблицу к первой нормальной форме, добавьте и заполните соответствующую колонку, например "важность" вместо выделения цветом.
- Одна ячейка таблицы содержит только одну информационную сущность (например, ФИО и т.д.), соответствующую своей колонке.
- Порядок строк и колонок в таблице не имеет никакого значения.
Если коротко, приведу картинку из Википедии:
Язык M - это такие же формулы
Итак, если вы рискнули не мучаться с графическим редактором Power Query (несмотря на то, что он закрывает большинство задач обычных пользователей) и попробовать "с нуля" изучить язык M, предлагаю сделать следующее.
Просто создайте какую-нибудь табличку такого вида, выделите ее, затем идите в меню "Данные" (этот раздел в основном посвящен Power Query) -> "Из таблицы / диапазона". Вот так:
Excel создаст запрос Power Query и покажет графический редактор. Нажимая любую кнопку в этом интерфейсе или делая какое-то действие, вы фактически добавляете новый шаг к преобразованию данных (шаги видны справа):
А теперь идем в меню "Главная" -> "Расширенный редактор":
Появится такое вот окно редактора языка М. Главное, не пугаться той писанине что там отображается, особенно если вы открыли Расширенный редактор, перед этим создав множество шагов в графическом интерфейсе Power Query. Дело в том что Microsoft какого-то черта сделал не просто поддержку Unicode в именах, а еще и поддержку пробелов, что делает синтаксис M с такими именами, с моей точки зрения, сильно запутывающим.
Синтаксис языка M может показаться запутывающим, но главное - он вообще ничем не отличается от формул так как является чистым функциональным языком.
Вот в формулах вы писали: ="Hello, world!" и в ячейке с такой формулой появлялся данный текст. Я здесь предлагаю сделать то же самое - стереть весь этот текст и написать вместо него просто "Hello, world!" или что вам душе угодно, но обязательно в двойных кавычках. Вот так:
Затем нажмите "готово" - данная строка появится как результат выполнения вашего запроса:
Да, если вы нажмете "сохранить и выгрузить", Excel создаст новую таблицу, данные из которой берутся из созданного вами запроса. В отличие от формул, запросы ввиду их обычно тяжеловесности пересчитывают не при любом изменении данных, а когда вы сами это попросите (меню "Данные" -> "Обновить все").
Вас не смутило, что в запросе вы создали строку "Hello, world!" и в графическом редакторе Power Query верно показал что это строка, но в Excel это все выгрузилось как таблица, содержащая одну строку, а не сама строка?
Это одно из ограничений самого Excel - результат выполнения запроса в случае выгрузки его на лист _всегда_ будет преобразован в таблицу. Сами листы Excel не поддерживают и близко все многообразие типов данных, которые поддерживает Power Query (PQ) - в PQ совершенно нормальной является ситуация, когда ячейка одной таблицы содержит в себе другую полноценную таблицу, ну или ячейка таблицы является функцией :-) Ну и так далее..
Но при выгрузке на лист книги, сам Excel будет неспособен такое отобразить, он заменит содержимое таких ячеек заглушкой [Table] и т.д. Учтите пожалуйста это, когда будете создавать свои Power Query.
А теперь вернитесь в расширенный редактор и замените текст, например, на:
"Число равно " & Text.From(123) & "!"
Вы увидите что результатом стала строка "Число равно 123!". Ничем не напоминает классические формулы Excel?)
Наверное, первым вашим вопросом будет, а как в Power Query использовать значение в конкретной ячейки на листе? ) Это можно сделать, но не так как в классических формулах, в PQ это немного сложнее. Но главное - следует понимать что обращение к конкретной ячейки из PQ это даже не из пушки по воробьям, а примерно как водородной бомбой по воробьям выстрелить. Незачем так делать, ибо такой подход в корне противоречит принципам Power Query. Ведь в Power Query вы работаете не с отдельными ячейками в книге, а с отдельными таблицами. Вот если ваша ячейка является частью именованной таблицы, или именованного диапазона, все становится гораздо проще )
Итак, подводя итоги - текст, который вы вводите в окне "Расширенный редактор", фактически является формулой, которая должна вернуть в качестве результата какое-то значение. В прошлом примере вы задали жестко зафиксированный текст, во втором - текст, куда подставляется значение числа.
Но главное здесь понимать что итогом выполнения запроса всегда является какое-то возвращаемое значение. Фактически, все как в классических формулах. Используя вместо определенных значений исходные данные, вы можете получить результат нужной вам обработки этих данных.
Важные особенности языка M
- Регистр имеет значение! То есть "а" и "A" в имени - разные буквы. Например, если в примере выше вы пишите Text.from(123) вместо Text.From(123), Power Query будет ругаться на неизвестное имя.
- Язык M имеет строгую типизацию. Это значит что если язык ожидает программную сущность какого-то определенного типа (например, в качестве аргумента у функции), то она должна быть только такого типа, а не другого. Про типы M будет чуть далее. Пример: если вместо "Число равно " & Text.From(123) & "!" вы напишите "Число равно " & 123 & "!", то возникнет ошибка так как оператор конкатенации (объединения) & слева и справа ожидает (в данном случае) увидеть текст, а там число 123. Чтобы был полный порядок, это число надо сначала перевести в текст, используя, например, функцию Text.From(Число).
- Забудьте с М про классическое императивное программирование, которому (по крайней мере в мое время) учили в школе и институте! M - это чистый функциональный язык. Конечно, и близко не такой крутой и навороченный как древний LISP и его функциональные потомки, но тем не менее. При открытии в расширенном редакторе запросов Power Query, состоящих из множества шагов, вы можете подумать что речь идет о присвоении кучи переменных:
Это и близко не так, в M нет никаких переменных, даже такого понятия нет. Как и нет операции присвоения в привычном для императивных языков виде, но это для уж совсем гуру функциональных языков ))) Представьте, что у вас есть классическое для программирования "дерево", состоящее из узлов и связей между ними.
Вычисление дерева начинается с его корня и продолжается по тем веткам, которые на момент вычисления актуальны. То же самое происходит и в формулах. Это тоже мы разберем чуть позже более подробно в следующих статьях.
Выводы
- Современный Excel имеет мощнейший инструмент - Power Query, позволяющий создавать формулы на специальном языке М, делающие такие вещи в части обработки данных, о которых ранее можно было только мечтать. Excel, начиная с версии 2016 имеет встроенный PQ. Даже в более раннем Excel, начиная с версии 2010, PQ можно скачать с сайта Microsoft и установить как расширение.
- К сожалению, версия Excel для Mac по состоянию на 2025 год не поддерживает PQ. Это для меня самый большой минус версии Excel для Mac. Для тех, кто работает на Mac, особенно с чипами М, вопрос решается просто - установкой Parallels Desktop. Это хост виртуальный машины, но в отличие от машин под Windows, Parallels имеет режим Coherence, в котором приложения под Windows для пользователя выглядят и работают как нативные под Mac. Что вы и видите на моих скриншотах.
- PQ поддерживает графический редактор формул, которого хватит большинству пользователей, материалов по нему на русском в сети много, а вот непосредственно по формулам на языке M куда меньше. Я далее буду рассматривать только вариант с написанием формул самостоятельно. Для пользователей, впервые осваивающими формулы на языке M, удобно будет сначала создать шаги в графическом редакторе, затем скорректировать или редактировать код M в расширенном редакторе.
- Формулы на M обновляются не так как классические, а по команде пользователя (Данные -> Обновить или Обновить Все). Можно их обновить автоматически при открытии книги (есть такая настройка).
- Формулы на языке M критичны к заглавным и прописным буквам, а так же крайне строго относятся к типам данных. Основные ошибки в M у вас будут как раз из-за несовпадения типов.
- Готов ответить на вопросы по PQ в комментариях.
Спасибо за внимание!