Преобразование выгрузки из 1С в сводную таблицу.
Преобразуем выгрузку из 1С в сводную таблицу Excel.
Предлагаем для просмотра наглядный пример преобразования выгрузки оборотно-сальдовой ведомости в сводную таблицу Excel на практике. В материале наглядно показаны подготовка и создание сводной таблицы на основе реальной оборотно-сальдовой ведомости, загруженной из базы 1С предприятия.
Не секрет, что формат записей и справочников 1С достаточно специфичен. Это связано непосредственно с назначением программы. Программа 1С бухгалтерия предназначена прежде всего для ввода имеющихся данных и формирования различных отчетных документов.
В зависимости от выбранного вида при импорте данных мы получаем ту или иную структуру записей. Однако на практике воспользоваться данной структурой для анализа без предварительной обработки нельзя.
Приведем примеры. Да, 1С прекрасно справляется с начислением заработной платы сотрудникам. Однако она не сможет показать соотношение объемов затрат на выплаты по отделам и филиалам. Да, 1С бухгалтерия она может показать размер краткосрочной дебиторской задолженности. Но пи попытке выяснить область и причины появления задолженности мы потерпим неудачу.
Да, программа 1С позволяет вести реестр номенклатуры материалов и готового производства. Но проверить соответствие запасов материалов планируемому выпуску с учетом производительности каждого отдельного цеха в ней затруднительно. Все дело в том, что фактически нет четко заданной структуры. Пользователи вынуждены выгружать данные в Excel для дальнейшего анализа и переформатировать их нужным образом для дальнейших расчетов.
Пока данных мало, сделать это несложно. Однако с ростом количества позиций и требований к отображению исходных данных задача усложняется. Так как же превратить отчет, выгруженный из 1С в нормальную плоскую таблицу с четкой структурой?
Вы найдете ответ на этот вопрос , просмотрев наше обучающее видео. В нем наглядно показано преобразование выгрузки из 1С в сводную таблицу для создания дальнейшего отчета.
Как из оборотно-сальдовой ведомости сделать сводную таблицу при помощи Power Query
Power Query – специальная надстройка для для Excel 2010 и выше. Начиная с версии Excel 2016 эта надстройка встроена в Excel и все команды расположены на вкладке Данные -группа Скачать и преобразовать. Для 2010 и 2013 после подключения надстройки появится новая вкладка — PowerQuery.
В качестве знакомства с некоторым функционалом и работой с Power Query возьмем печально известный многим отчет – Оборотно-Сальдовая ведомость. Выглядит он примерно так:
А нам надо все это привести в такой вид, чтобы можно было создать сводную таблицу и уже в этой сводной таблице крутить и вертеть данные в любом ракурсе. Т.е. на выходе надо сделать что-то вроде этого:
Результат — плоская таблица
А из этого вот такая сводная таблица:
Проблемы видны сразу: многоуровневая шапка, несколько столбцов значений. Напрямую из такой таблицы не построить нормальную сводную, в которой можно нормально просматривать и сравнивать данные. Поэтому в любом случае необходимо эту таблицу преобразовывать таким образом, как показано на втором скрине «Результат — плоская таблица».
Если делать подобное вручную – то уйдет как минимум час, при этом время будет напрямую зависеть от кол-ва строк. Если же делать это через Power Query – это займет максимум полчаса независимо от количества строк. И то только в первый раз – познакомившись с надстройкой поближе и 10 минут будет за глаза для данной операции. Я постараюсь пошагово расписать весь процесс с иллюстрациями своих действий. Чтобы удобно было повторять действия из статьи можно скачать файл :
Пример работы с Power Query (62,9 KiB, 2 705 скачиваний)
В приложенном есть лист, еще не преобразованной ОСВ. Он называется «Не обработанная ОСВ». Так же есть лист с уже подготовленной ОСВ(лист «Оборотно-сальдовая ведомость»), а так же с готовым запросом Power Query, созданной на его основе умной таблицей(лист «Лист2») и сводной таблицей(лист «Лист3»).
Итак, приступим. Сначала нам надо подготовить саму оборотно-сальдовую ведомость(в дальнейшем я буду называть её кратко – ОСВ). Для этого выделяем ОСВ от заголовков и до конца, без строки итогов: 
Переходим на вкладку Вставка и выбираем – Таблица. Снимаем галку с пункта «Таблица с заголовками» -Ок: 
Сразу переходим на вкладку Данные(или на вкладку Power Query) -группа Загрузить и преобразовать —Из таблицы 
Появится окно редактора запросов: 
Теперь производим преобразования данных, для приведения этой таблицы в нормальную, так называемую «плоскую». Здесь есть два варианта — какой из них использовать выбирать вам:
Вариант 1 (проще в понимании логики, но менее универсальный для таблиц с добавляющимися столбцами)
- Вкладка Главная –Использовать первую строку в качестве заголовков(на рисунке выше выделено красным). Запоминаем где у нас Сальдо на начало периода, где Обороты за период, где Сальдо на конец периода. Это еще пригодится. Так же это можно сделать с вкладки Преобразование —Таблица —Использовать первую строку в качестве заголовков
- Еще раз повышаем заголовки(Главная –Использовать первую строку в качестве заголовков) и теперь у нас вместо Оборотов за период и Сальдо только Дебет и Кредит. Поэтому вспоминаем изначальную структуру и к каждому Дебет и Кредит дописываем через нижнее подчеркивание Тип операции: Сальдо на начало периода, Обороты за период и Сальдо на конец периода:
- Теперь выделяем первые 2 столбца(Номер и Наименование), переходим на вкладку Преобразование —Отменить свертывание других столбцов:
Здесь не мешает пояснить, что делает вообще означает данная команда. Несмотря на странное название, делает она очень нужную вещь: она транспонирует все столбцы, отличные от выделенных, преобразуя их в два столбца: в одном будут записаны имена заголовков, в другом значение каждой строки для этого заголовка:
- Теперь осталось только отделить Типы от Дебета и Кредита. Для этого выделяем столбец Атрибут -правая кнопка мыши —Разделить столбец —По разделителю. В появившемся окне в выпадающем списке «Выберите или введите разделитель» выбираем Пользовательский и записываем в появившееся поле нижнее подчеркивание:
- Переименовываем столбцы, чтобы получилось что-то вроде этого(главное, чтобы было понятно что в каком столбце):
- Теперь выделяем столбец с суммой, переходим на вкладку Преобразование и находим там пункт Тип данных. Указываем тип Десятичное число:
Примечание: если на каком-то этапе что-то сделали неправильно – не надо все переделывать и не спешите закрывать окно. В правой части окна запросов есть история проделанных изменений – Примененные шаги. В этом окне можно удалить один из шагов:
Только удалять надо тоже аккуратно – если пока еще не очень хорошо ориентируетесь в Power Query – лучше удалять только последний шаг, который повлек ошибку. Если удалить шаг из середины – это может повлечь ошибки в дальнейших шагах, поэтому я не рекомендую удалять шаги из середины истории.
- Вкладка Преобразование —Таблица —Транспонировать
- Выделяем первый столбец – вкладка Преобразование —Заполнить— Вниз
- Выделяем два первых столбца -правая кнопка мыши —Объединить столбцы. Разделитель – точка с запятой
- Транспонируем таблицу обратно: вкладка Преобразование —Таблица —Транспонировать
- вкладка Преобразование —Таблица —Использовать первую строку в качестве заголовков
- Выделяем первые 2 столбца -вкладка Преобразование —Отменить свертывание других столбцов
- Выделяем столбец Атрибут(в котором у нас «Сальдо на начало периода;Дебет» и пр.) -правая кнопка мыши —Разделить столбец —По разделителю -Точка с запятой
- Переименовываем столбцы в нормальные названия, т.к. в некоторых у нас Атрибут.1, Атрибут.2 и Значение
- Меняем тип данных для столбца с Суммой: вкладка Преобразование —Тип данных —Десятичное число
На этом преобразования завершены, переходим на вкладку Главная —Закрыть и загрузить. В исходной книге будет создан новый лист, на котором будет создана умная таблица из преобразованных данных. Теперь на основании этой умной таблицы мы можем создать сводную таблицу(выделяем любую ячейку внутри таблицы -вкладка Вставка —Сводная таблица) или производить другие действия для анализа данных.
При этом очень важный нюанс: не придется делать подобные преобразования каждый раз при изменении данных – если мы внесем изменения в исходную таблицу ОСВ, то для получения актуальных данных и преобразования достаточно будет обновить результирующую таблицу(выделять любую ячейку результирующей умной таблицы -вкладка Конструктор —Обновить). Даже если добавить еще пару столбцов с Дебетом и Кредитом(здесь это не уместно, но в других отчетах с подобной структурой это вполне вероятно) и обновить — то эти столбцы так же будут обработаны и включены в итоговую таблицу.
Если же надо применить все тоже самое для другой таблицы – то выделяем любую ячейку результирующей умной таблицы -вкладка Запрос —Изменить. Находим самый первый шаг в Примененных шагах(как правило он называется Источник – выделяем и в строке формул меняем имя таблицы на имя таблицы нужной таблицы):
Надо ли пояснять, что описанные в статье принципы вполне применимы для любых таблиц с многоуровневыми шапками и не только. Главное понять сам принцип работы с запросами Power Query и после этого преобразовывать разные таблицы в правильные и нужные будет делом пяти минут.
Конвертер отчетов 1С
Конвертер отчетов 1С позволяет преобразовать стандартные отчеты 1С (отчет по проводкам, карточка счета, ОСВ по счету, ОСВ) к «плоскому» виду, в том числе разделить аналитику в объединенных ячейках файла Excel.
| Конвертер отчетов 1С (карточка счета, ОСВ по счету). Видео |
Конвертер отчетов 1С распространяется по принципу «как есть», без каких-либо гарантий.
Открытие конвертера отчетов 1С
В строке меню нажмите Средства просмотра и выберите пункт Просмотр файлов Excel. В редакторе Excel Viewer перейдите на вкладку Конвертеры (вкладка доступна при наличии лицензии).
имя_поля=колонка[|колонка][смещение][(строка[+])][=тип], где:
Смотрите, как оперативно преобразовать оборотно-сальдовую ведомость из «1С» в таблицу Excel
Допустим, что для управленческой отчетности финансовой службе требуются данные из бухгалтерской оборотно-сальдовой ведомости. Если выгрузить регистр из «1С» в Excel, таблицу придется преобразовать вручную – разъединить ячейки, удалить пустые столбцы, переместить значения в одну колонку. В каждой строке ведомости – три стоимостных показателя: сальдо на начало, обороты и сальдо на конец периода. Регистр выводит значения по дебету или кредиту счета, ячейки с суммами чередуются с пустыми. Кроме того, заголовки столбцов в ведомости повторяются или находятся в объединенных диапазонах. Построить по такому массиву диаграмму или сводную таблицу в Excel без предварительной ручной корректировки не получится.
Расскажем, как преобразовать сводную оборотно-сальдовую ведомость в плоскую таблицу Excel.
Заполните форму ниже и мы тут же перезвоним!
(Хотя наш KPI — звонит в течение одного рабочего дня, для Вас мы постараемся раньше!)
Заполните форму ниже и мы тут же перезвоним!
(Хотя наш KPI — звонит в течение одного рабочего дня, для Вас мы постараемся раньше!)
Заполните форму ниже и мы тут же перезвоним!
(Хотя наш KPI — звонит в течение одного рабочего дня, для Вас мы постараемся раньше!)
Заполните форму ниже и мы тут же перезвоним!
(Хотя наш KPI — звонит в течение одного рабочего дня, для Вас мы постараемся раньше!)