Как из оборотно-сальдовой ведомости сделать сводную таблицу при помощи Power Query
Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе? Power Query – специальная надстройка для для Excel 2010 и выше. Начиная с версии Excel 2016 эта надстройка встроена в Excel и все команды расположены на вкладке Данные -группа Скачать и преобразовать. Для 2010 и 2013 после подключения надстройки появится новая вкладка - PowerQuery. В качестве знакомства с некоторым функционалом и работой с Power Query возьмем печально известный многим отчет –...
Вычисления в PowerQuery
Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе? Скачать файл с исходными данными, используемый в видеоуроке: Вычисления в PowerQuery.xlsx (131,7 KiB, 1 770 скачиваний) PowerQuery весьма мощный инструмент обработки данных внутри Excel, но если присмотреться – не видно даже намека на возможность использовать формулы. Нет хоть какого-то списка или значка по подобию Excel. Все потому, что PowerQuery не использует вычисления как таковые, если...
Объединение запросов при помощи PowerQuery
Одной из наиболее часто решаемых задач в Excel является сравнение таблиц между собой. Как правило это делается либо для выявления расхождений/совпадений между двумя таблицами, либо для получения данных из одной таблицы в другую на основании какого-то критерия. Чаще всего для этого используется функция ВПР(VLOOKUP). Но и PowerQuery здесь тоже может помочь, особенно если речь идет о больших объемах данных. Называется этот инструмент – Слияние. При этом Слияние в PowerQuery имеет несколько реализаций, которые мы и рассмотрим. Если еще не работали с...
Собрать данные из файлов защищенных паролем PowerQuery
PowerQuery очень мощный инструмент по работе с файлами Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе? Наиболее часто она применяется для сбора данных с листов и файлов. Я для примера возьму модель из своей статьи: Собрать и просуммировать данные из разных файлов при помощи PowerQuery. Там из папки отбираются все файлы Excel и объединяются в единую таблицу(плюс суммируются по критерию, но это сейчас неважно). Все работает...
Собрать и просуммировать данные из разных файлов при помощи PowerQuery
На примере файлов бюджетов покажу как можно собирать данные со всех этих файлов в одну итоговую таблицу и просуммировать все присланные данные по статьям из каждой таблицы. Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе? Ниже можно скачать файлы, которые применялись в статье. В архиве два файла бюджета(в папке Бюджет) и готовая модель с запросом(файл "Сводный"). В файле с запросом так же применен прием получения пути...
Замена значений по списку в PowerQuery
Проблему замены значений по списку в Excel я уже затрагивал в статье Массовая замена слов. Там я использовал макросы, т.к. других вариантов по сути не было. Но с появлением PowerQuery многие задачи стало возможно решить и без применения VBA(макросов). В том числе и замену значений по списку соответствия. Правда, чуть менее универсально получается и в применении может быть даже не проще. Но это в любом случае выход для тех, у кого выполнение макросов запрещено политикой компании или когда даже само слово "макрос" уже вызывает легкую рвоту :) Например,...
Как определить последний рабочий день месяца(Excel, VBA, PowerQuery)
Проблема получения последнего дня месяца довольно распространена и очень просто решается. Начиная с Excel 2007 можно без всяких доп.манипуляций использовать функцию КОНМЕСЯЦА(EOMONTH): =КОНМЕСЯЦА(ТДАТА();0) =EOMONTH(NOW(),0) ТДАТА(NOW) - возвращает текущую дату. Вместо неё можно указать любую дату и последняя дата месяца будет возвращена для указанной даты. Для версий 2003 и ранее для использования КОНМЕСЯЦА(EOMONTH) необходимо для начала подключить надстройку Пакет анализа или же использовать чуть менее понятную...
Собрать данные с таблиц с изменяющимися столбцами в PowerQuery
Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе? Данная статья рассчитана на тех пользователей, которые уже умеют получать данные из файлов и таблиц при помощи PowerQuery. Подробная инструкция по получению данных здесь не приводится. Если получение данных для вас пока еще тоже темный лес, то лучше начать с просмотра статьи, в которой я подробно показывал и рассказывал как можно объединять информацию из нескольких файлов...
Оставить цифры или текст при помощи PowerQuery
На днях один хороший друг поделился задачкой: есть таблица, в которой данные записаны "по уровням" и надо эти уровни извлечь Т.е. из текста вида "1.1.1 Выручка от реализации собственных товаров и услуг (Экспорт)" надо получить только цифры уровня - "1.1.1" И что самое главное - сделать это надо не функциями Excel и макросами(все было бы слишком просто, подобное решение я уже выкладывал на сайте - Как оставить в ячейке только цифры или только текст?) - исключительно средствами языка M в PowerQuery. Это я к тому, что формулы DAX тоже нельзя применять. С виду...
Относительный путь к данным PowerQuery
В большинстве случаев создание запросов PowerQuery создается на основании файлов Excel, отличных от того файла, в котором сам запрос. Как пример - одна из статей на этом сайте: План-фактный анализ в Excel при помощи Power Query. Там идет обращение к некоторым вспомогательным файлам с данными и запрос ко всем файлам определенной папки. Т.е. мы можем знать только папку относительно файла с самим запросом и имена некоторых вспомогательных таблиц. И здесь как раз возникает нюанс: если впоследствии переместить или переслать файл с запросом (или даже полностью...
