Что такое power query в Excel

Обновлено: 14.05.2024

Вы имеете дело с данными из множества разных источников и регулярно объединяете их для анализа или составления отчетов? Excel Power Query может быть решением, которое вы ищете! Лучшее в этом инструменте то, что вы можете полностью автоматизировать процедуры загрузки и очистки данных одним нажатием кнопки.

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

Что такое Power Query в Excel?

Power Query — это инструмент бизнес-аналитики в Excel, используемый для выполнения процесса ETL (извлечение, преобразование и загрузка). Этот процесс включает в себя получение данных из источника, их преобразование, а затем помещение в место назначения для анализа. ETL известен как важный шаг в создании хранилища данных, но на самом деле вы выполняете процессы, подобные ETL, даже если вы просто составляете еженедельный или ежемесячный отчет.

Что может Power Query?

Благодаря Power Query каждый может быстро получить ценную информацию с помощью Excel. Было время, когда для процессов BI требовались выделенные группы ИТ-специалистов, но сейчас это не так. Вы можете использовать Power Query как часть решения ETL самообслуживания для выполнения следующих задач:

Power Query позволяет мгновенно подключаться к широкому спектру данных в разных форматах и из разных мест. Независимо от того, находятся ли ваши данные в форматах CSV, XML, JSON или PDF, это не проблема. Ваша организация хранит данные в базе данных SQL Azure, IBM DB2, Oracle или PostgreSQL? Вы можете легко получить к ним доступ. Даже если вы используете такие платформы, как Salesforce и MS Dynamics 365, просто подключитесь без проблем!

После подключения к источнику данных вам может потребоваться изменить данные несколькими способами. Преобразование данных — это область, в которой сияет Power Query. Этот инструмент позволяет выполнять ряд операций, от простых задач по преобразованию данных до самых сложных задач по реструктуризации данных, всего за несколько кликов.

Примеры задач преобразования данных:

Очистка данных: удаление дубликатов, изменение типов и форматирования данных, фильтрация строк, разделение столбцов и объединение/разведение столбцов.
Интеграция данных: объединение или разделение исходных таблиц, добавление ключей поиска и агрегирование данных.
Обогащение данных. Расширьте исходные данные, создав вычисляемые столбцы.

Упомянутые выше методы лишь поверхностно освещают все, что может сделать Power Query для преобразования ваших данных. Лучшее в этом инструменте то, что вы можете автоматизировать эти задачи преобразования данных, используя интерфейс без кода — без макросов или кодов VBA.

После того как ваши данные очищены и готовы к анализу, Power Query Excel предлагает варианты загрузки данных в одно или оба следующих места назначения:

Рабочий лист. По умолчанию Power Query размещает выходные данные непосредственно на новом листе внутри вашего файла Excel. При желании вы можете поместить данные из каждого источника на отдельный лист, а затем делать с ними все, что хотите, как если бы это были «обычные» данные Excel.
Модель данных. Ваши данные сжимаются и сохраняются в памяти. С этой опцией вы можете работать с миллионами, десятками миллионов и даже сотнями миллионов строк данных, превысив ограничение в 1 048 576 строк на листе Excel!

Модель данных обычно используется в качестве основы для вывода сводной таблицы в Excel. Поэтому ее также называют моделью данных Power Pivot. В этой статье мы не будем подробно рассматривать модель данных и Power Pivot, так как это обширная тема.

Зачем использовать Excel Power Query?

Power Query не только позволяет получать и преобразовывать данные, но и записывает все выполненные действия.

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

Все это делается в инструменте, с которым вы уже знакомы: Excel.

Power Query в разных версиях Excel

Power Query для Excel изначально был выпущен как надстройка для загрузки и установки для Excel 2010 и 2013. После добавления Power Query в Excel на ленте Excel появится новая вкладка с названием Power Query.

Этот инструмент был полностью интегрирован в Excel в версии 2016. Доступ к нему можно получить в разделе «Получить и преобразовать» на вкладке «Данные». Поэтому, если вы используете последние версии Excel, Power Query уже интегрирован в Excel.

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

В этом руководстве мы используем Office 365, однако вы можете выполнить действия, описанные в этой статье, с более ранними версиями продукта. Точка входа в Power Query может быть разной, но это не должно вызывать особых сложностей.

Excel Power Query: скачать примеры файлов

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

После этого поместите файлы CSV в папку, например, в «D:/Power Query/Sample files».

Как использовать Power Query для ПОЛУЧЕНИЯ и ЗАГРУЗКИ данных в Excel

Начнем с краткого обзора списка источников данных Power Query. После этого мы перенесем некоторые данные в Excel и более подробно рассмотрим интерфейс Power Query.

Список источников данных Power Query

Перейдите на вкладку "Данные" и найдите Power Query в разделе "Получить и преобразовать данные". Нажмите кнопку «Получить данные» — вы увидите раскрывающееся меню для выбора источника данных:

Обратите внимание, что диапазон доступных источников данных Power Query будет зависеть от версии Excel, которую вы используете.

Параметры источника данных

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

Что делать, если вашего источника данных нет среди доступных в настоящее время?

Одним из решений является использование универсального коннектора данных, такого как OData Feed, OLE DB и ODBC. Другое решение — использовать инструмент интеграции, который поможет вам легко подключаться и получать данные из внешних приложений в Excel. Примером этого является использование Coupler.io, которое представляет собой решение для импорта данных из различных приложений, таких как Airtable, Shopify, Jira, QuickBooks, Pipedrive, Hubspot и других!

Ознакомьтесь с полным списком интеграций Excel от Coupler.io.

Простой пример: получение данных из CSV-файлов в Excel с помощью Power Query

В следующем примере мы будем импортировать данные из двух загруженных CSV-файлов один за другим и загружать их в новые рабочие листы.

Во-первых, мы покажем вам, как получить и загрузить файл Sales.csv непосредственно на новый лист. После этого мы покажем вам, как импортировать файл Products.csv и открыть его в редакторе Power Query перед загрузкой. Вот шаги:

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

Откроется редактор Power Query, как показано на следующем снимке экрана:

Как видите, нажав кнопку «Преобразовать данные», вы перейдете к другому отдельному интерфейсу под названием «Редактор Power Query». Этот редактор позволяет преобразовывать данные перед их загрузкой на новый лист.

Подробнее о редакторе Power Query мы расскажем в следующем разделе. Пока давайте не будем делать здесь никаких преобразований данных. Мы продолжим загружать данные о товарах на новый лист из этого редактора.

Примечание. Если вы выберете второй вариант, у вас будет больше возможностей для загрузки данных — подробнее об этом в разделе Загрузить в... < em>раздел.

В конечном результате вы увидите новый рабочий лист, содержащий таблицу «Продукты». Если вы заметили, на правой панели есть два запроса: «Продажи» и «Продукты».

Вы узнали, как получить и загрузить два CSV-файла непосредственно в Excel с помощью Power Query. Кстати, нечто подобное можно сделать и с помощью Coupler.io, так как он также включает интеграцию CSV в Excel. Вы даже можете настроить автоматическое обновление данных по расписанию, например ежечасно, еженедельно или ежемесячно.

Параметры загрузки в… в Excel Power Query

Как объяснялось ранее, Power Query предоставляет два варианта загрузки данных: на лист и/или в модель данных. Если вы хотите загрузить данные на рабочий лист, существует несколько вариантов выбора параметра «Загрузить в…»:

Как показано в приведенном выше диалоговом окне, вы можете:

Загрузить в именованную таблицу Excel (по умолчанию)
Загрузить в сводную таблицу на основе исходных данных.
Загрузить в сводную диаграмму на основе исходных данных.
Только создайте подключение к данным, но пока не загружайте их.

Обратите внимание, что помимо этого у вас есть выбор: создать таблицу данных, сводную таблицу или сводную диаграмму на существующем или новом листе.

Если вы также хотите добавить данные в модель данных, установите флажок Добавить эти данные в модель данных.

Редактор Excel Power Query

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

Вот шесть основных элементов редактора Power Query:

Лента. Он имеет 5 основных вкладок: "Файл", "Главная", "Преобразование", "Добавить столбец" и "Просмотр".
Список запросов. Эта панель содержит все запросы, которые были добавлены в текущую книгу. Вы можете перейти к любому запросу из этой области, чтобы начать его редактирование.
Предварительный просмотр данных. В этой области вы можете увидеть образец данных для выбранного запроса.
Панель формул. В этой области отображается М-код текущего шага преобразования. Power Query записывает каждый из ваших шагов преобразования в M-код, который вы видите в этой строке формул. В большинстве случаев вам вообще не нужно использовать язык M напрямую.
Свойства. Здесь вы можете просматривать и редактировать свойства вашего запроса. Например, вы можете переименовать свой запрос, добавить к нему описание и включить быструю загрузку данных.
Прикладные шаги. В этой области содержится список шагов, используемых для преобразования данных.

Как использовать Power Query для ПРЕОБРАЗОВАНИЯ данных в Excel

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

Excel Power Query: удаление дубликатов

Внешний источник данных может быть не таким безупречным, как вы ожидаете. Наличие дубликатов — одна из самых неприятных характеристик данных низкого качества.

Если вы внимательно посмотрите на таблицу "Продукты", вы заметите два продукта с номером продукта DS803.

Чтобы удалить вышеуказанные дубликаты, выполните следующие действия:

Excel Power Query: создание параметров для путей к папкам

В редакторе Power Query давайте откроем запрос «Товары» и щелкнем первую строку «Источник» в ПРИМЕНЯЕМЫХ ШАГАХ. Вы увидите жестко заданный путь к файлу, как показано ниже:

Если вы проверите запрос Sales, вы заметите, что он также использует фиксированное значение для пути к файлу, т. е. D:\Power Query\Sample files\Sales.csv

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

Давайте выполним следующие шаги, чтобы заменить жестко заданный путь к папке параметром:

Теперь вы изменили путь к файлу обоих запросов, чтобы использовать параметр FolderPath. Обратите внимание, что код в строке формул чувствителен к регистру. Также обратите внимание, что вам не нужно заключать параметры в двойные кавычки.

Excel Power Query: добавление условного столбца с оператором IF

Предположим, вы хотите создать новый столбец, т. е. "Категория" в запросе "Товары", в котором указано, к какой категории относится каждый продукт. Первые 2 цифры номера продукта определяют категорию продукта на основе следующих правил:

Номер продукта начинается с «DS» → «Маргаритка».
Номер продукта начинается с «OC» → Orchid.
Номер продукта начинается с «RS» → «Роза».
Номер продукта начинается с «SF» → «Подсолнух».

Вот как можно добавить столбец "Категория":

Excel Power Query: детализация для создания параметров из ячейки

Предположим, вы хотите иметь возможность фильтровать товары по категориям из ячейки, как показано на изображении ниже:

Чтобы передать значение из ячейки B3 в запрос и использовать его для фильтрации продуктов, выполните следующие действия:

Ваш окончательный рабочий лист будет выглядеть так, как показано ниже. Проверьте, изменив значение раскрывающегося списка на «Роза», затем нажмите кнопку «Обновить все».

Excel Power Query: объединение таблиц

Запросы на слияние позволяют объединять таблицы на основе ключевого столбца. Это похоже на использование ВПР Excel. Например, здесь мы собираемся объединить запросы «Продажи» и «Продукты» в один. Мы извлечем столбцы из таблицы «Продукты» (таблица поиска) и перетащим их в таблицу «Продажи».

Вот шаги:

Примечание. Как видите, у вас есть два варианта слияния. Вы можете перезаписать текущий запрос о продажах дополнительными столбцами или объединить два запроса в один новый, чтобы сохранить текущую таблицу о продажах без изменений.

Вот пример после того, как мы переместили столбцы ProductNumber, Category и Price перед столбцами Quantity и Discount.

Excel Power Query: использование формул

Предположим, что в запросе ProductSales_Merge мы хотим добавить новый столбец OrderTotal, который рассчитывается на основе других столбцов по следующей формуле:

Для этого выполните следующие действия:

Excel Power Query: использование функций

В этом последнем примере мы покажем вам, как использовать функции в Power Query. Мы добавим новый столбец «Квартал», который представляет номер квартала с даты заказа.

Запустите редактор Power Query и выберите запрос ProductSales_Merge.
На вкладке "Добавить столбец" нажмите "Пользовательский столбец".

В появившемся диалоговом окне «Пользовательский столбец» введите следующие данные, затем нажмите «ОК».

Новое название столбца: Квартал
Формула пользовательского столбца:
= "Q"&Number.ToText(Date.QuarterOfYear([OrderDate]))

Функция Date.QuarterOfYear() возвращает номер квартала (1–4) из даты, а функция Number.ToText() преобразует число в текстовый формат.

Что дальше?

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

Power Query — это механизм преобразования и подготовки данных. Power Query поставляется с графическим интерфейсом для получения данных из источников и редактором Power Query для применения преобразований. Поскольку механизм доступен во многих продуктах и службах, место, где будут храниться данные, зависит от того, где использовался Power Query. С помощью Power Query вы можете выполнять извлечение, преобразование и загрузку данных (ETL).

Схема с обозначенными символами источниками данных справа, которые проходят через запрос Power для преобразования, а затем переходят к различным местам назначения, таким как Azure Data Lake Storage, Dataverse, Microsoft Excel или Power BI.

Как Power Query помогает в сборе данных

Бизнес-пользователи тратят до 80 % своего времени на подготовку данных, что задерживает работу по анализу и принятию решений. Этой ситуации способствуют несколько проблем, и Power Query помогает решить многие из них.

< /tbody>

Существующая проблема	Как помогает Power Query?
Поиск и подключение к данным слишком сложно	Power Query обеспечивает подключение к широкому спектру источников данных, включая данные всех размеров и форм.
Опыт работы с данными соединения слишком фрагментированы	Постоянство опыта и паритет возможностей запросов по всем источникам данных.
Данные часто необходимо изменять перед использованием	Высоко интерактивный и интуитивно понятный интерфейс для быстрого и итеративного создания запросов к любому источнику данных любого размера.
Любое формирование является одноразовым и не повторяемым	При использовании Power Query для доступа и преобразования данных вы определяете повторяемый процесс (запрос), который можно легко обновить в будущем для получения актуальных данных. Если вам нужно изменить процесс или запрос для учета базовых данных или изменений схемы, вы можете использовать тот же интерактивный и интуитивно понятный интерфейс, который вы использовали при первоначальном определении запроса.
Объем (размеры данных), скорость (скорость изменения) и разнообразие (широта источников данных и форм данных)	Power Query предлагает возможность работать с подмножеством весь набор данных, чтобы определить необходимые преобразования данных, что позволяет легко фильтровать и преобразовывать данные до управляемого размера. Запросы Power Query можно обновлять вручную или с помощью возможностей запланированного обновления в определенных продуктах (например, Power BI) или даже программно (с помощью объектной модели Excel). Поскольку Power Query обеспечивает подключение к сотням источников данных и более 350 различных типов преобразования данных для каждого из этих источников, вы можете работать с данными из любого источника и в любой форме.

Возможности Power Query

Пользовательский интерфейс Power Query обеспечивается через пользовательский интерфейс редактора Power Query. Цель этого интерфейса — помочь вам применить необходимые преобразования, просто взаимодействуя с удобным набором лент, меню, кнопок и других интерактивных компонентов.

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

Когда вы создаете новый шаг преобразования, взаимодействуя с компонентами интерфейса Power Query, Power Query автоматически создает M-код, необходимый для выполнения преобразования, поэтому вам не нужно писать какой-либо код.

В настоящее время доступны два варианта Power Query:

Power Query Online. Используется в таких интеграциях, как потоки данных Power BI, потоки данных Microsoft Power Platform, потоки данных обработки фабрики данных Azure и многих других, которые обеспечивают работу через веб-страницу в Интернете.
Power Query для настольных ПК — используется в таких интеграциях, как Power Query для Excel и Power BI Desktop.

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

Преобразования

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

Все эти преобразования становятся возможными благодаря выбору параметра преобразования в меню и последующему применению параметров, необходимых для этого преобразования. На следующем рисунке показаны некоторые преобразования, доступные в редакторе Power Query.

Изображение из курса CFI по основам Power Query

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

Power Query – это удобный инструмент бизнес-аналитики, который не требует от пользователя изучения какого-либо определенного кода. Впервые доступно в MS Excel Определение Excel Определение Excel: программа от Microsoft, которая использует электронные таблицы для организации чисел и данных с помощью формул и функций. Анализ Excel широко распространен во всем мире и используется предприятиями любого размера для проведения финансового анализа. в 2010 г. эта функция является бесплатной надстройкой в Excel 2010 и 2013. С 2016 г. она полностью интегрирована в Excel.

Обзор

Power Query – это инструмент в Microsoft Excel, упрощающий процесс импорта данных из различных исходных файлов и их сортировки на листе Excel в наиболее удобном и удобном для использования формате.
Power Query – это удобный инструмент бизнес-аналитики, не требующий от пользователя изучения какого-либо кода.
Самым большим преимуществом инструмента Power Query является более высокая эффективность. использование Excel с Power Query дает пользователю более быстрые результаты за очень короткое время.

Иллюстрация

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

Идентификатор сотрудника, идентификатор продукта, дата, комиссионная ставка, количество проданных единиц, цена за единицу

A10001, 10200301, 30 января 2020 г., 20,0%, 65, 100

A10002, 10200304, 30 января 2020 г., 20,0%, 77, 200

A10003, 10200301, 30 января 2020 г., 20,0%, 50, 100

A10002, 10200301, 30 января 2020 г., 20,0%, 44, 100

A10003, 10200304, 30 января 2020 г., 20,0%, 60, 200

A10001, 10200304, 30 января 2020 г., 20,0%, 35, 200

После копирования приведенных выше данных в Excel с использованием параметра "Текст в столбец" данные будут выглядеть следующим образом:

Чтобы рассчитать сумму, подлежащую выплате каждому продавцу:

Найдите имя сотрудника, связанное с каждым идентификатором, с помощью ВПР ВПР Изучите ВПР простым способом со снимками экрана, примерами, подробным описанием того, как формула работает в Excel. Функция =ВПР(искомое значение, диапазон таблицы, номер столбца). Например, «ищите эту часть информации в следующей области и дайте мне соответствующие данные в другом столбце». .
Свяжите каждый идентификатор продукта с названием продукта.
Используйте математические формулы для расчета общей суммы продаж и комиссионных к уплате.

После добавления и удаления некоторых столбцов окончательные данные должны выглядеть следующим образом:

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

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

Чтобы узнать больше, ознакомьтесь с курсом CFI по основам Power Query!

Другое использование Power Query

Очевидно, что самым большим преимуществом инструмента Power Query является его более высокая эффективность. Excel сам по себе является полезным и эффективным инструментом для форматирования и анализа данных; использование Excel с Power Query позволяет быстрее получать результаты.

Следующие функции этого инструмента делают его еще более полезным для обычных приложений:

1. Подключение к различным источникам данных

Power Query запрограммирован на экспорт данных из нескольких источников, включая, помимо прочего, текстовые файлы, книги Excel и файлы CSV.

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

2. Объединение таблиц

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

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

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

3. Объединение таблиц

Параметр «Объединить» в Power Query заменяет функцию ВПР в Excel. Последняя функция действительно полезна для поиска соответствующих значений, но ее использование в большом наборе данных, охватывающем несколько тысяч строк, становится несколько неудобным.

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

ИЗВЛЕЧЕНИЕ ФАЙЛА

Данные можно экспортировать из настольных версий QuickBooks в Excel в виде файла CSV или файла Excel. Онлайн-версия QuickBooks экспортирует данные только в виде файла Excel. В любом случае рекомендуется сначала настроить как можно больше данных в собственном приложении, прежде чем экспортировать их в Excel. В QuickBooks это делается в два этапа. Первый шаг — выбрать столбцы, которые вы хотите включить в отчет. Второй шаг включает в себя использование Advanced. в диалоговом окне "Отправить отчет в Excel", чтобы настроить формат файла.

Я предпочитаю использовать файл CSV, так как он предлагает два важных преимущества по сравнению с файлом Excel. Во-первых, файл загружается за секунды. Файл Excel, содержащий много строк и формул, будет загружаться очень долго.Второе преимущество заключается в том, что в файле CSV все суммы записываются как значения, поэтому преобразование формул не требуется.

Начните процесс настройки отчета, выбрав столбцы для включения в отчет. Столбцы выбираются нажатием кнопки «Настроить отчет» на панели кнопок отчета QuickBooks. Откроется диалоговое окно, как показано на снимке экрана «Изменить отчет: сведения о сверке».

Изменить отчет: сведения о сверке

В отчете главной бухгалтерской книги я рекомендую снять флажки на вкладке "Отображение" (левое поле), "Очистка", "Разделить" и "Баланс" и установить флажок "Счет" и "Сумма". Я предлагаю проверить учетную запись, чтобы QuickBooks записывала название учетной записи в отдельный столбец и сумму, чтобы создать один столбец со всеми транзакциями.

При работе с файлом Excel я также предлагаю снять все параметры QuickBooks в диалоговом окне «Дополнительные параметры Excel», как показано на снимке экрана «Дополнительные параметры Excel». Я особенно рекомендую снять флажок «Пробел между столбцами», чтобы их не пришлось удалять позже в рамках процесса очистки данных. Мы будем работать с файлом Excel, который не был отформатирован рекомендуемым образом. Это позволяет нам взглянуть на некоторые функции Power Query.

Дополнительные параметры Excel

ПРЕОБРАЗОВАНИЕ ФАЙЛА

Преобразование файла начинается с открытия пустой книги. В Excel 2016 доступ к Power Query можно получить, щелкнув вкладку «Данные», щелкнув стрелку раскрывающегося списка рядом с «Новый запрос» в группе «Получить и преобразовать», а затем выбрав «Из файла». Если вы загружаете файл Excel, выберите верхний вариант «Из книги». Если вы загружаете CSV-файл, выберите второй вариант «Из CSV». Когда вы открываете файл Excel в Power Query, вы видите диалоговое окно Power Query Navigator, как показано на снимке экрана «Диалоговое окно Navigator». В навигаторе перечислены все рабочие листы в файле рабочей книги. Вам понадобятся загруженные данные из главной книги QuickBooks, поэтому выберите третий элемент, отчет QuickBooks GL, и нажмите «Изменить». Если вы открываете файл CSV или файл Excel только с одним рабочим листом, экран навигатора не появится. Power Query откроет экран предварительного просмотра, как показано на снимке экрана «Диалоговое окно навигатора с экраном предварительного просмотра». Щелкните поле «Изменить» в нижней части экрана, чтобы загрузить файл в редактор запросов Power Query.

Читайте также:

Что такое power query в Excel