Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (14)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (38)
Баги и глюки Excel (2)

План-фактный анализ в Excel при помощи Power Query

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

  Готовая модель План-фактного анализа (1,0 MiB, 1 106 скачиваний)


после скачивания, необходимо будет извлечь из архива данные в отдельную папку(например: C:\PowerQuery\), открыть файл "План-Факт PQ.xlsx" и изменить путь до источников данных(чтобы модель обновлялась без проблем.

Чтобы каждый раз не менять путь к источникам данных - можно сделать путь автоизменяющимся: Относительный путь к данным PowerQuery
А для тех, кому лень вообще разбираться с источниками данных - полностью готовая для работы модель данных(качай и используй):

  Готовая модель План-фактного анализа - относительный путь (491,0 KiB, 945 скачиваний)

Как изменить источник данных

  • Для пользователей Excel 2010-2013:
    Перейти на вкладку Power Query -группа Настройки(Options) -Параметры источника данных(Data Source Settings)
  • для пользователей 2016 и выше:
    Перейти на вкладку Данные(Data) -Создать запрос(New Query) -Параметры источника данных(Data Source Settings)
    Параметры источника данных запроса Power Query
    В появившемся окне выделить из списка строку с источником и нажать Изменить источник(Change source)(обращаю внимание, что один источник в самой книге с моделью, поэтому его изменить нельзя):
    Изменить источник данных Power Query
    появится еще одно окно, в котором надо лишь изменить указанный там путь к файлу/папке на тот, в который поместили файлы из архива.
    Нажимаем Ок.
    Повторить для каждого источника:
    1. Подразделения
    2. Статьи
    3. План доходов и расходов 2015 год.xlsx
    4. папка Факт

 

Также см.:
Относительный путь к данным PowerQuery
Как из оборотно-сальдовой ведомости сделать сводную таблицу при помощи Power Query


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Печать Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки
Обсуждение: 14 комментариев
  1. Ярослав:

    Дмитрий, скачал Вашу модель, изменил ссылки на источники, но вылезла ошибка. Модель была создана в версии Excel более новой, чем 2010? Если да, то наверное в этом дело, потому как я все изменения делал именно в 2010 версии.

    • Ярослав, да я создавал в 365, поэтому в более ранних версиях может ругаться. В том числе на вычисляемые поля, для работы которых нужна Power Pivot.

  2. Ярослав:

    Еще такой нюанс, шел пошагово по Вашему уроку. Когда попытался загрузить первую таблицу "Дата", выбрал "Загрузить в" и "Только создать соединение", а пункта "Добавить эти сведения в модель данных" нет.... Получается, что в итоге я не смогу построить сводную таблицу...

    • Попробуйте так: при создании сводной таблицы выбираете "использовать внешний источник данных". В появившемся окне выбираете любой из запросов.

  3. Ярослав:

    Так получилось, но только один из запросов. А как туда добавить остальные, чтобы был план-факт?

  4. Ярослав:

    Здравствуйте! Дмитрий, установил PowerPivot. Но дело в том что вкладка не появляется на ленте. Зашел через надстройки, поставил галочку напротив PowerPivot, нажал ОК - ничего не произошло. Зашел опять в надстройки, но вижу что галочка опять не активна. Может знаете, почему так происходит?

  5. Алексей:

    Здравствуйте, Дмитрий? Как в Power Query работать с dbf файлами?

    • Алексей, вообще так же, как и с другими типами файлов. Создать запрос -Из других источников. Выбираете либо ODBC или OLEDB. Здесь может зависеть от структуры файлов, поэтому советую попробовать оба варианта.

  6. Дмитрий, столкнулась с аналогичной проблемой - в Excel 2010 при загрузке первой таблицы "Дата", выбрать "Загрузить в" и "Только создать соединение", пункта "Добавить эти сведения в модель данных" нет.... Power Pivot установила. Безрезультатно, как исправить, чтобы можно было построить сводную таблицу из нескольких запросов? При выборе "Использовать внешний источник данных" на любой из запросов - выпадает сообщение "запрос данных не может быть использован для построения сводной таблицы"

  7. Павел:

    Дмитрий, подскажите пожалуйста такой момент. Стоит Office 365, при выгрузке итоговой сводной план-факт в облако OneDirve для веб-представления и совместной работы - сводная перестает работать. Пишет что "Эта книга содержит подключения к внешним данным, либо в ней используются неподдерживаемые функции бизнес-аналитики."

  8. Виталий:

    Добрый день, Дмитрий!
    Большое спасибо, за Ваши труды!
    Почему-то в Вашем примере в сводной таблице при выбранном подразделении "Бухгалтерия" в факте отображаются статьи не относящиеся к бухгалтерии (например: "Заработная плата производственного персонала")? Что нужно сделать для нивелирования такого нюанса?

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2018 Excel для всех   Войти