Lost your password?


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

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

Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе?

Power Query – специальная надстройка для для Excel 2010 и выше. Начиная с версии Excel 2016 эта надстройка встроена в Excel и все команды расположены на вкладке Данные -группа Скачать и преобразовать. Для 2010 и 2013 после подключения надстройки появится новая вкладка - PowerQuery.


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

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

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


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

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

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

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

Преобразовать подобную таблицу в "нормальную" можно как минимум двумя вариантами(из тех, что можно через интерфейс PowerQuery наколдовать):

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


    Вариант 1
    Сначала нам надо подготовить саму оборотно-сальдовую ведомость(в дальнейшем я буду называть её кратко – ОСВ). Для этого выделяем ОСВ от заголовков и до конца, без строки итогов:
    Выделяем ОСВ
    Переходим на вкладку Вставка и выбираем – Таблица. Снимаем галку с пункта "Таблица с заголовками" -Ок:
    Создание умной таблицы
    Сразу переходим на вкладку Данные(или на вкладку 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 Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
    Обсуждение: 12 комментариев
    1. Дмитрий:

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

      Спасибо!

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

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

        • Дмитрий:

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

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

        • Евгений:

          Добрый день.

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

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

    2. Денис:

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

    3. Коновалов Олег:

      Дмитрий, добрый день!
      Благодарю за такое открытие для меня как PQ, а особенно за преобразования таблиц. До этого самому приходилось вручную преобразовывать таблицы отчетов 1С по периодам в удобоваримый вид для источника сводной таблицы.
      Потестил по второму варианту преобразований, получил практически то, что нужно. Остался один "затык", который пока не понял как можно сделать в PQ. А именно: остался столбец со строками: количество и выручка. В соседнем столбце, соответственно, значения этих строк. Строки повторяются на каждую позиции и на каждый период.
      Вопрос заключается в следующем: как сделать разделение столбца на столбцы с названиями "количество" и "выручка" с заполненными значениями?

      • Олег, без примера данных помочь сложно. Возможно, здесь поможет что-то вроде Отмены свертывания столбцов, а может и более "хитрые" манипуляции. Обратитесь на форум: PowerQuery, PowerPivot, PowerBI и запросы - там можно приложить файл.

        • Коновалов Олег:

          Дмитрий, благодарю за оперативный ответ!
          Сейчас размещу на форуме с тестовым файлом

          • Коновалов Олег:

            Написал в форуме в теме "Преобразование выгрузки из 1С" Файл приложен

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

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


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