Использование функционала Power Query для ежемесячной сверки данных по остаткам ТМЦ между АСБНУ и АСКУ

Сверка данных «1С: АСБНУ» и «1С: АСКУ»
4/1
РЕШЕНИЕ
Редактор Power Query – надстройка в Excel, с функционалом по извлечению, преобразованию и загрузке данных.
ПРЕВОСХОДИМ ОЖИДАНИЯ КЛИЕНТОВ
ЗАДАЧА
Осуществить сверку данных из двух выгрузок, за период - календарный месяц, из АСБНУ (Оборотно-сальдовая ведомость по счету 10.08) и АСКУ (Анализ себестоимости товаров) в целом по УФПС.
ОБОСНОВАНИЕ АКТУАЛЬНОСТИ ЗАДАЧИ
В целях контроля за приведением в соответствие данных информационных систем бухгалтерского учета «1С: АСБНУ» и коммерческого учета «1С: АСКУ», в части данных по остаткам ТМЦ, необходимо проводить периодические ежемесячные сверки указанных остатков на основании выгрузок отчетов из АСБНУ и АСКУ в разрезе товаров, материалов и ЗПО.

Последовательность действий.
1. Создаем новый лист Excel. На закладке «Данные» выбираем действие «Получить данные» - «из файла» - «из книги».

2. Выбираем и импортируем в Power Query (PQ) файл с выгрузкой данных из АСБНУ

3. Загружаем выбранный файл в PQ, для этого выделяем файл, кликаем на кнопку «Загрузить» и выбираем «Загрузить в …»

4. При загрузке выбираем способ представления данных в книге – «Только создать подключение»

5. По аналогии выбираем, импортируем и загружаем в PQ файл с выгрузкой данных из АСКУ

6. Выбираем команду «Объединить запросы» - «Объединить»

7. Заполняем окно «Слияние», для этого выбираем ранее загруженные файлы и выделяем столбцы по которым будем объединять массивы. В нашем случае, объединять данные будем по названию ОПС

8. Получили сводную таблицу в виде, где крайний правый столбец (выделен зеленым цветом) является агрегированными присоединенными данными из второй таблицы (с данными из АСКУ)

9. Выберем нужные нам значения. В нашем случае это столбец Column21 (Конечный остаток. Себестоимость).

Получили необходимый столбец с данными по себестоимости на конец периода.

10. Форматируем данные, а именно столбцы содержащие числовые значения, переведем в формат десятичных чисел

11. Кликаем на кнопку «Закрыть и загрузить» и выбираем «Закрыть и загрузить в…»

12. Выбираем способ предоставления данных в книге «Таблица», и перемещения данных «Новый лист»

Получаем выгрузку сводных данных в формате «Умной таблицы»

13. Удаляем все не нужные столбцы и производим все необходимые вычисления (в нашем случае рассчитываем сумму расхождений и процент). Сверка закончена. Сохраняем полученный файл

Выводы:
С помощью надстройки Power Query была осуществлена сверка данных из двух выгрузок, за период - календарный месяц, из АСБНУ (Оборотно-сальдовая ведомость по счету 10.08) и АСКУ (Анализ себестоимости товаров) в целом по УФПС.
Кроме того, были проведены необходимые вычисления с использованием функционала «Умной таблицы».
Для этих целей, не понадобилось дополнительная обработка массивов данных выгрузок, перед операцией их объединения.
Полученный итоговый файл со сверкой и расчетами, может быть использован для проведения регулярных ежемесячных сверок аналогичных данных.
Подобная возможность предусмотрена функционалом Power Query. А именно, при изменении данных в исходных файлах (выгрузки из АСБНУ и АСКУ), PQ будет автоматически (посредством функции «Обновить») пересчитывать их в данной таблице.
Получив новую выгрузку из АСБНУ и/или АСКУ, ее можно вставить на тот же лист исходного файла (при условии, что структура данных не изменится), сохранить файл, обновить данные в PQ, в результате чего, программа обратится к новому диапазону и выдаст новую таблицу по новой выгрузке.