Я в этой статье не стал описывать скринами и текстом - решил, что посмотреть вживую весь процесс будет куда более эффективно, чем читать кучу текста со скринами, большая часть которых будет куда менее информативна ввиду специфики темы. Однако, помимо самого видеоурока неплохо было бы и пощупать все это и попробовать самостоятельно сделать. Поэтому я прикладываю к статье готовую модель план-фактного анализа и все необходимые файлы, которые рассматриваются в видеоуроке:
Готовая модель План-фактного анализа (1,0 МиБ, 3 690 скачиваний)
после скачивания, необходимо будет извлечь из архива данные в отдельную папку(например:
Чтобы каждый раз не менять путь к источникам данных - можно сделать путь автоизменяющимся: Относительный путь к данным PowerQuery
А для тех, кому лень вообще разбираться с источниками данных -полностью готовая для работы модель данных(качай и используй):
Для пользователей Excel 2010-2013:
Перейти на вкладку Power Query -группа Настройки(Options) -Параметры источника данных(Data Source Settings) для пользователей 2016 и выше:
Перейти на вкладку Данные(Data) -Создать запрос(New Query) -Параметры источника данных(Data Source Settings)
В появившемся окне выделить из списка строку с источником и нажать Изменить источник(Change source) (обращаю внимание, что один источник в самой книге с моделью, поэтому его изменить нельзя) :
появится еще одно окно, в котором надо лишь изменить указанный там путь к файлу/папке на тот, в который поместили файлы из архива.
Нажимаем Ок.
Повторить для каждого источника:
1. Подразделения
2. Статьи
3. План доходов и расходов 2015 год.xlsx
4. папка Факт
Также см.:
Относительный путь к данным PowerQuery
Как из оборотно-сальдовой ведомости сделать сводную таблицу при помощи Power Query
Дмитрий, скачал Вашу модель, изменил ссылки на источники, но вылезла ошибка. Модель была создана в версии Excel более новой, чем 2010? Если да, то наверное в этом дело, потому как я все изменения делал именно в 2010 версии.
Ярослав, да я создавал в 365, поэтому в более ранних версиях может ругаться. В том числе на вычисляемые поля, для работы которых нужна Power Pivot.
Еще такой нюанс, шел пошагово по Вашему уроку. Когда попытался загрузить первую таблицу "Дата", выбрал "Загрузить в" и "Только создать соединение", а пункта "Добавить эти сведения в модель данных" нет.... Получается, что в итоге я не смогу построить сводную таблицу...
Попробуйте так: при создании сводной таблицы выбираете "использовать внешний источник данных". В появившемся окне выбираете любой из запросов.
Так получилось, но только один из запросов. А как туда добавить остальные, чтобы был план-факт?
Вам надо установить PowerPivot, чтобы у Excel появилась возможность взаимодействия нескольких запросов в одной сводной.
Ярослав, пройдите по ссылке . здесь наглядно показано как решить ваш вопрос
Здравствуйте! Дмитрий, установил PowerPivot. Но дело в том что вкладка не появляется на ленте. Зашел через надстройки, поставил галочку напротив PowerPivot, нажал ОК - ничего не произошло. Зашел опять в надстройки, но вижу что галочка опять не активна. Может знаете, почему так происходит?
Здравствуйте, Дмитрий? Как в Power Query работать с dbf файлами?
Алексей, вообще так же, как и с другими типами файлов. Создать запрос -Из других источников. Выбираете либо ODBC или OLEDB. Здесь может зависеть от структуры файлов, поэтому советую попробовать оба варианта.
Дмитрий, столкнулась с аналогичной проблемой - в Excel 2010 при загрузке первой таблицы "Дата", выбрать "Загрузить в" и "Только создать соединение", пункта "Добавить эти сведения в модель данных" нет.... Power Pivot установила. Безрезультатно, как исправить, чтобы можно было построить сводную таблицу из нескольких запросов? При выборе "Использовать внешний источник данных" на любой из запросов - выпадает сообщение "запрос данных не может быть использован для построения сводной таблицы"
Дмитрий, подскажите пожалуйста такой момент. Стоит Office 365, при выгрузке итоговой сводной план-факт в облако OneDirve для веб-представления и совместной работы - сводная перестает работать. Пишет что "Эта книга содержит подключения к внешним данным, либо в ней используются неподдерживаемые функции бизнес-аналитики."
Добрый день, Дмитрий!
Большое спасибо, за Ваши труды!
Почему-то в Вашем примере в сводной таблице при выбранном подразделении "Бухгалтерия" в факте отображаются статьи не относящиеся к бухгалтерии (например: "Заработная плата производственного персонала")? Что нужно сделать для нивелирования такого нюанса?
Спасибо, разобрался. Причина в фактических исходных данных.