Здравствуйте! Нужен совет/консультация, как правильно работать с PowerQuery в Excel для создания сводного бюджета компании.
Сводный бюджет состоит из файлов с бюджетами проектов (около 20 шт.), и файлом с зарплатой. Файлы с бюджетами проектов однотипные (внутри у них одинаковая структура). Файл с зарплатой кардинально отличается по своей структуре от файлов проектов бюджетов. Все файлы с бюджетами проектов и зарплатой находятся в папке, на которую ссылается PowerQuery.
Механизм, как сейчас у меня все работает такой: 1. Каждый файл бюджета проекта внутри себя имеет обработку PowerQuery, таким образом чтобы на отдельный лист выгружалась вся необходимая информация для сводного бюджета (только нужные столбцы и с одинокой структурой). 2. Файл зарплаты тоже внутри себя имеет обработку PowerQuery, с помощью которой выгружает все в таблицу с нужной структурой (нужным названием столбцов, в одинаковом порядке). 3. Файл сводного бюджета через запрос PowerQuery ссылается на папку с бюджетами проектов/зарплатой -> находит в каждом файле нужный лист с нужной таблицей -> складывает эту информацию в одну сплошную простыню -> выгружает в таблицу, на основании которой строиться сводная таблица со сводным бюджетом. Минус этого всего механизма:
После работы с каждым файлом мне нужно внутри него обновлять запрос (пункт 1 и 2 для файла зарплаты), чтобы на отдельный лист выгружалась вся обновленная информация для сводного бюджета. То есть даже одно малейшее изменение вынуждает переда закрытием файла с бюджетом проекта обновлять запрос в файле, чтобы при обновлении в файл со сводным бюджетом подтянулась актуальная информация.
Подскажите, может я это делаю неправильно, и есть какой-то более удобный вариант? Например, напрямую обращаться к каждому файлу и иметь в файле сводного бюджета около 20 запросов.
Файлов немного, но в процессе бюджетирования, когда я получаю файлы с бюджетами проектов от разных сотрудников, и нужно каждый открыть и обновить запросы занимает время, которое не хотелось бы на это тратить.
Спасибо, буду благодарен за любой подсказ и консультацию!
Vorozhbeev, если структура файлов бюджета однотипная, нет смысла хранить в каждом запрос. Тем более, если вдруг вам надо что-то в нем менять - будете менять 20 раз. Я бы предложил так: 1) из вашего запроса обработки бюджета делаете функцию 2) обращаетесь к папке, отбираете файлы бюджета. 3) все их обрабатываете функцией из п.1 4) потом объединяете результаты. 5) обрабатываете другой функцией (да или просто другим запросом) файл зарплаты 6) сливаете все в одну таблицу, или как вам там надо. В результате имеете один файл с запросами PQ, и не храните запросы PQ в исходных файлах
Vorozhbeev написал: Файлов немного, но в процессе бюджетирования, когда я получаю файлы с бюджетами проектов от разных сотрудников, и нужно каждый открыть и обновить запросы занимает время, которое не хотелось бы на это тратить
Для меня это тоже актуально. Приходится объединять информацию файлы с разной структурой. Пока эту проблему решаю путем, что в каждом файле формирую умную таблицу при помощи PQ, одну таблицу (DB_OUT) в каждом файле одинаковой структуры. Таким образом привожу разную структуру к одной таблице, результирующий файл берет данные с этих таблиц. Но большой минус такой схемы, что при при обновлении исходного фала, приходиться его открывать и обновлять таблицу (DB_OUT), при большом количестве исходных файлов это уже напрягает (особенно если они лежат на OneDrive) . Я тоже буду благодарен за наводку на более "правильную" схему работы по консолидации бюджетов.
Заранее прошу прощения, если в чем то нарушил правила форума.
Для каждой структуры пишите функцию обработки содержимого файла, далее обращаетесь к папке и для каждого файла определяетесь по известным вам признакам (заголовки/название файла/расширение файла/что-то еще) какой именно функцией его приводить к общему знаменателю. И обновляетесь из общего файла. Например, у меня в одной папке лежат как xlsx так и txt источники, запрос смотрит на расширение, и исходя из этого использует разные алгоритмы для трансформации содержимого перед объединением. Ничего более конкретного без примеров показать не могу.