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

Как из оборотно-сальдовой ведомости сделать сводную таблицу при помощи Power Query

Для начала, наверное, неплохо бы пояснить что за зверь такой Power Query.
Power Query – специальная надстройка для для Excel 2010 и выше. Начиная с версии Excel 2016 эта надстройка встроена в Excel и все команды расположены на вкладке Данные -группа Скачать и преобразовать. Для 2010 и 2013 после подключения надстройки появится новая вкладка - PowerQuery.
Что же дает эта надстройка и зачем её вообще устанавливать и применять? Power Query является очень мощным инструментом и позволяет получить данные из различных источников: Excel, CSV, бд Access и SQL, интернет-страницы, OneDrive и многие другие и при этом полученные данные можно сразу же обработать встроенными в Power Query командами и преобразовать в вид, удобный для дальнейшего анализа. Например, для построения той же сводной.
Скачать Power Query для 2010 и 2013 можно по ссылке: http://go.microsoft.com/fwlink/?LinkID=313430


Но, как говорится - лучше один раз увидеть, чем сто раз услышать. Поэтому испытаем сразу на практике, что нам может дать эта надстройка. В качестве знакомства с некоторым функционалом и работой с Power Query возьмем печально известный многим отчет – Оборотно-Сальдовая ведомость. Выглядит он примерно так:
Оборотно-сальдовая ведомость
А нам надо все это безобразие привести в такой вид, чтобы можно было создать сводную таблицу и уже в этой сводной таблице крутить и вертеть данные в любом ракурсе. Т.е. на выходе надо сделать что-то вроде этого:

Результирующая таблица

Результат - плоская таблица


А из этого вот такая сводная таблица:
Сводная таблица

Проблемы видны сразу: многоуровневая шапка, несколько столбцов значений. Напрямую из такой таблицы не построить нормальную сводную, в которой можно нормально просматривать и сравнивать данные. Поэтому в любом случае необходимо эту таблицу преобразовывать таким образом, как показано на втором скрине "Результат - плоская таблица".
Если делать подобное вручную – то уйдет как минимум час, при этом время будет напрямую зависеть от кол-ва строк. Если же делать это через Power Query – это займет максимум полчаса независимо от количества строк. И то только в первый раз – познакомившись с надстройкой поближе и 10 минут будет за глаза для данной операции. Я постараюсь пошагово расписать весь процесс с иллюстрациями своих действий. Чтобы удобно было повторять действия из статьи можно скачать файл:

  Пример работы с Power Query (62,9 KiB, 995 скачиваний)


В приложенном есть лист, еще не преобразованной ОСВ. Он называется "Не обработанная ОСВ". Так же есть лист с уже подготовленной ОСВ(лист "Оборотно-сальдовая ведомость"), а так же с готовым запросом Power Query, созданной на его основе умной таблицей(лист "Лист2") и сводной таблицей(лист "Лист3").


Итак, приступим. Сначала нам надо подготовить саму оборотно-сальдовую ведомость(в дальнейшем я буду называть её кратко – ОСВ). Для этого выделяем ОСВ от заголовков и до конца, без строки итогов:
Выделяем ОСВ
Переходим на вкладку Вставка и выбираем – Таблица. Снимаем галку с пункта "Таблица с заголовками" -Ок:
Создание умной таблицы
Сразу переходим на вкладку Данные(или на вкладку Power Query) -группа Загрузить и преобразовать -Из таблицы
Данные -Из таблицы
Появится окно редактора запросов:
Шаг 1

Теперь производим преобразования данных, для приведения этой таблицы в нормальную, так называемую "плоскую". Здесь есть два варианта - какой из них использовать выбирать вам:
Вариант 1 (проще в понимании логики, но менее универсальный для таблиц с добавляющимися столбцами)

  1. Вкладка ГлавнаяИспользовать первую строку в качестве заголовков(на рисунке выше выделено красным). Запоминаем где у нас Сальдо на начало периода, где Обороты за период, где Сальдо на конец периода. Это еще пригодится. Так же это можно сделать с вкладки Преобразование -Таблица -Использовать первую строку в качестве заголовков
  2. Еще раз повышаем заголовки(ГлавнаяИспользовать первую строку в качестве заголовков) и теперь у нас вместо Оборотов за период и Сальдо только Дебет и Кредит. Поэтому вспоминаем изначальную структуру и к каждому Дебет и Кредит дописываем через нижнее подчеркивание Тип операции: Сальдо на начало периода, Обороты за период и Сальдо на конец периода:
    Шаг 2
  3. Теперь выделяем первые 2 столбца(Номер и Наименование), переходим на вкладку Преобразование -Отменить свертывание других столбцов:
    Шаг 3
    Здесь не мешает пояснить, что делает вообще означает данная команда. Несмотря на странное название, делает она очень нужную вещь: она транспонирует все столбцы, отличные от выделенных, преобразуя их в два столбца: в одном будут записаны имена заголовков, в другом значение каждой строки для этого заголовка:
    Шаг 4
  4. Теперь осталось только отделить Типы от Дебета и Кредита. Для этого выделяем столбец Атрибут -правая кнопка мыши -Разделить столбец -По разделителю. В появившемся окне в выпадающем списке "Выберите или введите разделитель" выбираем Пользовательский и записываем в появившееся поле нижнее подчеркивание:
    Шаг 5
  5. Переименовываем столбцы, чтобы получилось что-то вроде этого(главное, чтобы было понятно что в каком столбце):
    Шаг 6
  6. Теперь выделяем столбец с суммой, переходим на вкладку Преобразование и находим там пункт Тип данных. Указываем тип Десятичное число:
    Шаг 7
  7. Примечание: если на каком-то этапе что-то сделали неправильно – не надо все переделывать и не спешите закрывать окно. В правой части окна запросов есть история проделанных изменений – Примененные шаги. В этом окне можно удалить один из шагов:
    Удаление шагов
    Только удалять надо тоже аккуратно – если пока еще не очень хорошо ориентируетесь в Power Query – лучше удалять только последний шаг, который повлек ошибку. Если удалить шаг из середины – это может повлечь ошибки в дальнейших шагах, поэтому я не рекомендую удалять шаги из середины истории.


Вариант 2(чуть сложнее в понимании, чем Вариант 1, но этот принцип подойдет для преобразования любых таблиц и будет корректно работать даже если мы добавим столбцы в исходную таблицу)
я тут уже покажу минимум скринов, т.к. большинство уже описано в первом варианте
После того как загрузили таблицу в модель:

  1. Вкладка Преобразование -Таблица -Транспонировать
    Шаг 1
  2. Выделяем первый столбец – вкладка Преобразование -Заполнить- Вниз
    Шаг 2
  3. Выделяем два первых столбца -правая кнопка мыши -Объединить столбцы. Разделитель – точка с запятой
    Шаг 3
  4. Транспонируем таблицу обратно: вкладка Преобразование -Таблица -Транспонировать
  5. вкладка Преобразование -Таблица -Использовать первую строку в качестве заголовков
  6. Выделяем первые 2 столбца -вкладка Преобразование -Отменить свертывание других столбцов
  7. Выделяем столбец Атрибут(в котором у нас "Сальдо на начало периода;Дебет" и пр.) -правая кнопка мыши -Разделить столбец -По разделителю -Точка с запятой
    Шаг 7
  8. Переименовываем столбцы в нормальные названия, т.к. в некоторых у нас Атрибут.1, Атрибут.2 и Значение
  9. Меняем тип данных для столбца с Суммой: вкладка Преобразование -Тип данных -Десятичное число

На этом преобразования завершены, переходим на вкладку Главная -Закрыть и загрузить. В исходной книге будет создан новый лист, на котором будет создана умная таблица из преобразованных данных. Теперь на основании этой умной таблицы мы можем создать сводную таблицу(выделяем любую ячейку внутри таблицы -вкладка Вставка -Сводная таблица) или производить другие действия для анализа данных.
При этом очень важный нюанс: не придется делать подобные преобразования каждый раз при изменении данных – если мы внесем изменения в исходную таблицу ОСВ, то для получения актуальных данных и преобразования достаточно будет обновить результирующую таблицу(выделять любую ячейку результирующей умной таблицы -вкладка Конструктор -Обновить). Даже если добавить еще пару столбцов с Дебетом и Кредитом(здесь это не уместно, но в других отчетах с подобной структурой это вполне вероятно) и обновить - то эти столбцы так же будут обработаны и включены в итоговую таблицу.
Если же надо применить все тоже самое для другой таблицы – то выделяем любую ячейку результирующей умной таблицы -вкладка Запрос -Изменить. Находим самый первый шаг в Примененных шагах(как правило он называется Источник – выделяем и в строке формул меняем имя таблицы на имя таблицы нужной таблицы):
Изменить источник


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

 

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


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

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

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

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

    Спасибо!

    • Первое, что приходит в голову: работаете на корпоративном ПК и Power Query после создания не может подключиться к источнику из-за ограничений доступа. Либо у Вас сбой библиотек самого Excel(в этом случае требуется переустановка или восстановление Excel).
      Если проблемы с правами доступа, то можете для начала посмотреть параметры источника данных:

      1. Вкладка Данные -Создать запрос -Параметры источника данных(если Excel младше 2016 -Power Query -группа Параметры -Параметры источника данных)
      2. В появившемся окне выберите созданный проблемный источник данных и нажмите кнопку Изменить
      3. В появившемся окне в разделе Учетные данные нажмите Изменить
      4. Выберите тип учетных данных слева(Анонимно по умолчанию и в большинстве случаев работает) и при необходимости укажите имя пользователя и пароль (или другие необходимые данные)
      5. Возможно, Вам потребуется указать учетные данные организации или обратиться в отдел ИТ за помощью.

      • Дмитрий:

        Корпоративный ПК. Excel 2016. Открываю файл - создать запрос - параметры источника данных (список пустой "На этом компьютере нет сохраненных источников данных"). Возможно что-то делаю не корректно при создании запроса.

        • А что все-таки с правами доступа? Возможно, что запрос как раз и не получается сохранить из-за ограничений прав. ПК-то корпоративный - там такое сплошь и рядом.

      • Евгений:

        Добрый день.

        Подскажите, о каких именно правах доступа идет речь?
        Имеется ввиду сетевые или какие-то специальные права доменной УЗ?

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

  2. Денис:

    Дмитрий, подскажите, а обязательно ли указывать полный путь к файлу "С:\..." или можно как-то относительный?
    У меня в основной файл через Power Query тянутся ОСВ, остатки и еще много чего, и все они лежат в одной папке на гугл драйве, папка синхронизируется между домашним и рабочим компьютерами через настольный GDrive, но абсолютные пути у папок на ПК разные. И возникает конфликт - Power Query пишет, что не может найти файл по абсолютному пути.

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

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


Для оформления сообщений Вы можете использовать следующие тэги:
<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 для всех   Войти