Artis Vizbelis

Cтарший консультант в сфере услуг по налоговой отчётности и бухгалтерскому учёту, PwC Латвия

Исполняя бухгалтерские или любые другие обязанности, когда обработка данных занимает много времени или требуется регулярно осуществлять повторяющиеся или схожие действия и процессы, важно иметь удобные и простые в использовании инструменты для выполнения задач. Планируя курсы Цифровой академии PwC, мы хотели бы рассказать об одном из инструментов, который существенно облегчит выполнение ваших повседневных задач. Power Query – инструмент для подготовки и трансформации данных, доступный в программе Microsoft Power BI, а начиная с версии 2016 года – также в Microsoft Excel и других программах. Этот инструмент позволяет пользователям извлекать данные как из различных внешних источников, так и из файлов, сохраненных на компьютере. С его помощью можно группировать, трансформировать данные и выполнять другие действия с ними. В данной статье мы приведем некоторые практические примеры, улучшающие представление о применимости и преимуществах такой функции.

Для чего нужно использовать Power Query

С помощью этого инструмента удобно обрабатывать и трансформировать данные, а также использовать сгруппированные данные для анализа. Оригинальные данные в источнике не изменяются. Потребуется всего один раз сформировать этапы обработки конкретного источника данных, и они будут сохранены. В следующий раз, выполняя работу, например, с данными следующего месяца, останется лишь нажать Refresh – и все ранее выполненные действия будут реплицированы, чтобы пользователю не пришлось повторно обрабатывать данные.

Инструмент имеет удобный интерфейс, который наглядно показывает, какие этапы обработки данных уже выполнены с их источником. Чтобы выполнить этапы трансформации данных, пользователю не требуются навыки программирования, поскольку действия по преобразованию данных можно выбрать из панели инструментов.

Несмотря на то что в Excel существует ограничение приблизительно на 1 миллион строк, Power Query позволяет использовать источники данных, содержащие 2 и больше миллионов строк. Однако это не означает, что можно наглядно отразить в таблице все строки в подробностях без предварительной обработки данных, но можно сгруппировать данные с помощью Power Query и отразить конечный результат в таблицах Excel.

Как найти Power Query в программе Excel

В панели инструментов Excel инструмент Power Query находится в разделе Get & Transform Data группы Data. Выбрав Get Data, можно выбрать Launch Power Query Editor. Если в том же разделе выбрать загрузку данных, например, с помощью From Text/CSV, предлагается меню Transform Data, которое тоже откроет пользователю Power Query.

Практические примеры применения

1. Обработка, группировка данных и отражение результата в таблице

В примере используются данные (20 000 строк), из которых первые четыре строки удаляются, а дата форматируется как Text, поэтому ее сначала нужно преобразовать, чтобы группировать по датам, и за каждой строкой данных следует одна пустая строка:

Чтобы создать заготовку для такой обработки данных с помощью Power Query, в новом файле Excel выбираем этот файл в качестве источника данных, а затем выполняем необходимые действия для достижения желаемого результата:

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

• Removed Top Rows – удалены первые строки из источника данных;
• Changed Type – формат текста изменен на дату;
• Filtered Rows – отобраны только заполненные строки;
• Extracted Year – из поля даты отражен только год;
• Grouped Rows – данные сгруппированы по году/сегменту/среднему количеству/прибыли.

В конечном итоге полученные данные отражаются в таблице Excel уже в сгруппированном виде:

В следующий раз при получении этих данных единственное, что потребуется сделать пользователю, – нажать Refresh, и все установленные в Power Query действия будут реплицированы автоматически.

2. Курсы валют в таблице Excel из внешнего источника данных

Второй пример использования Power Query – подключиться к внешнему источнику и загрузить из него в файл Excel, например, курсы валют за определенный период. Функциональность Power Query также позволяет создать заготовку, где указываем временной период, за который нужно обновить данные. В этом примере, изменив дату в ячейках В1 и В2 и обновив данные в таблице с помощью Refresh, мы обновляем курсы валют за выбранный период:

3. Извлечение данных из нескольких файлов с одинаковой разметкой

Посредством данного инструмента можно автоматически объединить файлы с одинаковой разметкой из одной папки и загрузить их в одну таблицу, предварительно в случае необходимости выполнив обработку конкретных данных. В этом случае остается лишь выбрать папку, где находятся все файлы, и Power Query автоматически создаст действия для объединения данных в одну таблицу и поле с названием файла, из которого помещены данные:

Выводы

В данной статье отражена лишь небольшая часть доступной функциональности. Следует добавить, что таким образом можно создавать заготовки для различных отчетов или для сравнения данных между системами, а также для выполнения других задач. Данный инструмент может заменить ряд типовых действий, осуществляемых на повседневной основе, – составление формул, копирование данных и др.

Если вас заинтересовала возможность применения данного или подобных ему инструментов в вашей конкретной ситуации, предлагаем присылать свои примеры и мы уже в ближайшее время пригласим всех желающих принять участие в PwC’s Digital Academy, где предоставим решения присланных ситуаций.