Excel является одним из наиболее распространенных инструментов для обработки данных, которыми пользуются экономисты. Однако многим пользователям неудобно выполнять рутинные операции по объединению большого объема информации, быстрому поиску данных в Excel. При сложном наборе данных Power Query становится более эффективным вариантом. Рассмотрим доступные приемы использования Power Query (при работе в среде Power BI), а также некоторые способы обработки данных в Excel, которые облегчают работу экономиста.
ХИТРОСТИ ПРИ РАБОТЕ В POWER QUERY
Power Query — это модуль преобразования данных и подсистемы их подготовки. Power Query поставляется с графическим интерфейсом для получения данных из источников и редактором Power Query для применения преобразований. Так как подсистема доступна во многих продуктах и службах, место хранения данных зависит от того, где использовался Power Query.
Преимущества Power Query
Power Query имеет значительные преимущества перед Excel:
1. Power Query прост в применении.
Этот инструмент поддерживает единый пользовательский интерфейс который не зависит от того, из какого источника импортируют данные (Excel, SQL, Power BI, Excel, Azure Data Lake Storage, Bloomberg и др.) или в каком они формате (текстовом, cvx-файле, pdf-формате).
2. Изменения можно вносить на каждом шаге.
Каждая последовательность преобразований данных сохраняется в виде запроса, который может загружаться в отчет или использоваться другими запросами.
В любой момент можно выбрать один из шагов на панели «Примененные шаги», внести в них изменения или вставить новый шаг между двумя уже существующими или в самом конце. В Power Query нет кнопки отмены. Вы отменяете действие, удаляя примененный шаг. Для этого используют черный крестик рядом с названием шага.
3. Данные обрабатываются с высокой скоростью.
Power Query обрабатывает сложные источники быстрее, чем формулы Excel и макросы VBA.
4. Можно дублировать группы команд (запросов), применяемых к таблице.
Вы можете создавать повторно используемые запросы Power Query, а затем применять их к различным наборам данных или совместно использовать в разных командах, обеспечивая согласованность процессов подготовки данных и поиска. Для этого нужно кликнуть правой кнопкой по меню «Запросы» и выбрать «Дублировать».
Недостатки Power Query
Хотя Power Query является мощным инструментом для анализа данных, у него есть и недостатки:
1. Проблемы с совместимостью.
Power Query может быть несовместим со старыми версиями Excel или другими программами для работы с электронными таблицами. Чаще Power Query используют в среде Power BI, т. к. у поздних версий Excel также есть определенные ограничения.
2. Трудное обучение использованию.
Пользователям бывает сложно обучиться использовать Power Query, особенно тем, которые не знакомы с манипуляциями с данными или концепциями программирования. Больший функционал использования программы требует некоторого понимания языка M и пользовательского интерфейса Power Query.
3. Отсутствие контроля исходных данных.
Power Query автоматизированно выполняет различные задачи по обработке данных, но иногда может делать неверные предположения. При работе с программой необходимо отслеживать тип данных, ошибки и нулевые значения.
4. Зависимость от внешних подключений.
Power Query может устанавливать подключения к различным внешним источникам данных, таким как базы данных или веб-службы. Однако, если исходные данные изменяются или соединение прерывается, это может привести к ошибкам или неработающим ссылкам в запросе. Если вы меняете данные в исходном файле, то результаты в Power Query тоже поменяются.
5. Ограниченные возможности совместного использования.
Запросы Power Query встроены в файл Excel, и их сложно совместно использовать нескольким пользователям. Это ограничивает возможности командной работы при работе с Power Query.
Загрузка данных в Power Query
В программе можно сразу выбрать столбцы, с которыми вы хотите работать, чтобы не перегружать рабочую область лишней информацией. Однако при каждом использовании программы Power Query пытается присвоить всем записям значения с десятичными знаками, что иногда вызывает ошибки.
Обратите внимание
Часто программа показывает ошибку при использовании точки в качестве разделителя разрядов, поэтому до обработки данных в Power Query нужно заменить этот разделитель на запятую в среде Excel.
Алгоритм предварительной обработки данных для исключения ошибок выглядит так:
Полезные преобразования данных в Power Query
С помощью Power Query можно проще и эффективнее, чем в Excel, выполнять следующие операции:
1. Перемещать столбцы. Это делают с помощью нажатой левой кнопки мыши.
2. Разделять данные в столбцах.
В Excel данные в столбцах разделяют с помощью функций ЛЕВСИМВ, ПРАВСИМВ.
Пример выделения нескольких символов из кода места возникновения затрат (МВЗ) приведен на рис. 1.
Для столбца D поиск первых четырех символов производится по формуле =ЛЕВСИМВ(A2;4). Для столбца E поиск следующих трех символов выполняется так: =ЛЕВСИМВ(ПРАВСИМВ(A2;6);3).
В Power Query осуществлять такие преобразования можно гораздо быстрее (рис. 2):
1) Преобразование → Разделить столбец → По количеству символов → Однократно, как можно левее (4 символа);
2) Преобразование → Разделить столбец → По количеству символов → Однократно, как можно левее (3 символа).
Разделять данные с помощью Power Query значительно эффективнее в ситуациях, когда есть много символов, которые нужно исключить в Excel, но добавление функции будет нагромождением. В Power Query можно разделить столбец аналогично тому, как это делается в Excel, но преобразования мы будем вести в исходной таблице, поэтому не приходится удалять или копировать нужные данные. В Excel же для обработки данных в исходной таблице ее приходится дополнять различными вложенными функциями (рис. 3). Так, для того чтобы убрать имена сотрудников и формулы, необходимо использовать также функцию ДЛСТР, и формула будет сложной: =(ЛЕВСИМВ(ЛЕВСИМВ(ПРАВСИМВ(A9;ДЛСТР(A9)-1);ПОИСК("_";ПРАВСИМВ(A9;ДЛСТР(A9)-1);2));
ДЛСТР(ЛЕВСИМВ(ПРАВСИМВ(A9;ДЛСТР(A9)-1);ПОИСК("_";ПРАВСИМВ(A9;ДЛСТР(A9)-1);2)))-1)).
3. Искать информацию.
В Excel это делают с помощью функции ВПР.
По умолчанию функция ВПР настроена на возврат приблизительного совпадения. Однако для большинства поисковых запросов требуется точное совпадение.
Для физических лиц
Для юридических лиц
Подписка по каталогам