Преобразовать выгрузку 1с в плоскую таблицу

Обновлено: 05.07.2024

Преобразуем выгрузку из 1С в сводную таблицу Excel.

Предлагаем для просмотра наглядный пример преобразования выгрузки оборотно-сальдовой ведомости в сводную таблицу Excel на практике. В материале наглядно показаны подготовка и создание сводной таблицы на основе реальной оборотно-сальдовой ведомости, загруженной из базы 1С предприятия.

Не секрет, что формат записей и справочников 1С достаточно специфичен. Это связано непосредственно с назначением программы. Программа 1С бухгалтерия предназначена прежде всего для ввода имеющихся данных и формирования различных отчетных документов.

В зависимости от выбранного вида при импорте данных мы получаем ту или иную структуру записей. Однако на практике воспользоваться данной структурой для анализа без предварительной обработки нельзя.

Приведем примеры. Да, 1С прекрасно справляется с начислением заработной платы сотрудникам. Однако она не сможет показать соотношение объемов затрат на выплаты по отделам и филиалам. Да, 1С бухгалтерия она может показать размер краткосрочной дебиторской задолженности. Но пи попытке выяснить область и причины появления задолженности мы потерпим неудачу.

Да, программа 1С позволяет вести реестр номенклатуры материалов и готового производства. Но проверить соответствие запасов материалов планируемому выпуску с учетом производительности каждого отдельного цеха в ней затруднительно. Все дело в том, что фактически нет четко заданной структуры. Пользователи вынуждены выгружать данные в Excel для дальнейшего анализа и переформатировать их нужным образом для дальнейших расчетов.

Пока данных мало, сделать это несложно. Однако с ростом количества позиций и требований к отображению исходных данных задача усложняется. Так как же превратить отчет, выгруженный из 1С в нормальную плоскую таблицу с четкой структурой?

Вы найдете ответ на этот вопрос , просмотрев наше обучающее видео. В нем наглядно показано преобразование выгрузки из 1С в сводную таблицу для создания дальнейшего отчета.

Проблема возникла когда понадобилось привести отчет из 1С к обычному Excel виду.

Есть данные которые сделаны в виде сводной таблице т.е. например:

Наименование плательщика.1
Платеж.1
Платеж.2
Наименование плательщика.2
Платеж.1
Платеж.2
Платеж.3
Платеж.4
Наименование плательщика.3
Платеж.1

Нужно их все привести к виду:
Платеж.1 - Наименование плательщика.1 - Сумма
Платеж.2 - Наименование плательщика.1 - Сумма
Платеж.1 - Наименование плательщика.2 - Сумма
И т.д.

И то есть к обычному Excel виду.

По скольку платежей может быть достаточно много решить задачу использую формулу если получается слишком громоздко. Хотелось бы найти более красивый способ. Заранее спасибо!

__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

Приведение double (9.738302323967218E-4) к обычному виду
Добрый день. Использую библиотеку Colt, а именно MersenneTwister. Значение, выдаваемое этим.

С виду простое однородное уравнение. Нужно привести к виду F(x,y)=C
Из 250 задач ВУЗа решил все, за исключением единственной! (Хотя по части задач были недопонимания -.

DataTimePicker и MS SQL server 2005: как преобразовать дату к обычному виду
Добрый вечер, форумчане. Возникла проблема с датами. Использую Builder C++ 6, ADOQuery. В.

Спасибо огромное, решение отличное, единственное не могли бы уточнить такой момент, есть такая возможность при изменение размера таблице предположим до 300, изменить показатели размеров таблиц. То есть чтобы при увеличение одной таблицы увеличивалась и другая.

Извиняюсь заранее что ухожу от основной темы. Этот вопрос давно мучает меня не только в конкретном примере. Думаю было бы полезно многим в схожей ситуации.

ОБНОВЛЕНО 19.04.15 до Версии 2 (макрос не изменился но был добавлен видеобзор на данную примочку)

Добрый день, друзья и гости.
Оформить данное решение навеяла очередная тема на нашем форуме о том "как преобразовать таблицу разбитую по столбцам в плоскую?".

- Данное решение построено на массивах, авторство принадлежит МСН.
- Внес небольшую коррективу, а именно запрос на выделение диапазона - Максим Зеленский , с дружеского форума.
- Дополнительные улучшения и полезности - SLAVICK .
- Небольшие дополнения и коррективы - DJ Marker MC

В итоге получился вот такой вот макрос:

1. К примеру имеем таблицу такого вида:

После запуска Редизайнера видим диалоговое окно в котором Вам предложено указать диапазон таблицы которую необходимо преобразовать.

2. Указываем диапазон таблицы: (если таблица была выделена до запуска макроса, то выделенный диапазон будет подхвачен автоматически)

3. Третий шаг, в диалоговом окне необходимо указать сколько строк находится в шапке таблицы. В нашем примере - три строки.

4. Следующим шагом указываем количество столбцов с данными в левой части таблицы. В нашем примере их пять - Код, Цена, Направление, Страна, Мин.уп!

5. Если мы ходим получить ПЛОСКУЮ ТАБЛИЦУ, то оставляем тут значение по умолчанию 1, если Вам будет необходимо чтоб вправо таблица была разбита помесячно, а года были разбиты вниз то поставьте 12 (вообще на этом шаге поэкспериментируйте и сами поймете как это работает.
Я же оставлю тут 1, так как желаю получить плоскую таблицу

6. Последний шаг - вопрос: Хотите Вы уменьшить шапку таблицы или нет? Данный вопрос задается только в том случае, если в предыдущем шаге Вы указали - 1 или же если в шапку таблицы попадает всего одна строка (тоже попробуйте как это работает в двух вариантах методом "ТЫКА")

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

Попробовать как это работает можно с помощью приложенного файла.
В целом все очень быстро и красиво!
Всем приятного пользования!

ОБНОВЛЕНО 19.04.15 до Версии 2 (макрос не изменился но был добавлен видеобзор на данную примочку)

В итоге получился вот такой вот макрос:

1. К примеру имеем таблицу такого вида:

Попробовать как это работает можно с помощью приложенного файла.
В целом все очень быстро и красиво!
Всем приятного пользования! DJ_Marker_MC

В итоге получился вот такой вот макрос:

1. К примеру имеем таблицу такого вида:

Попробовать как это работает можно с помощью приложенного файла.
В целом все очень быстро и красиво!
Всем приятного пользования! Автор - DJ_Marker_MC
Дата добавления - 04.02.2015 в 10:40

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

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

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

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

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

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

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

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

Переходим на вкладку Вставка и выбираем – Таблица. Снимаем галку с пункта "Таблица с заголовками" -Ок:

Сразу переходим на вкладку Данные(или на вкладку Power Query) -группа Загрузить и преобразовать -Из таблицы

Появится окно редактора запросов:

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

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

Здесь не мешает пояснить, что делает вообще означает данная команда. Несмотря на странное название, делает она очень нужную вещь: она транспонирует все столбцы, отличные от выделенных, преобразуя их в два столбца: в одном будут записаны имена заголовков, в другом значение каждой строки для этого заголовка:
Теперь осталось только отделить Типы от Дебета и Кредита. Для этого выделяем столбец Атрибут -правая кнопка мыши -Разделить столбец -По разделителю. В появившемся окне в выпадающем списке "Выберите или введите разделитель" выбираем Пользовательский и записываем в появившееся поле нижнее подчеркивание:
Переименовываем столбцы, чтобы получилось что-то вроде этого(главное, чтобы было понятно что в каком столбце):
Теперь выделяем столбец с суммой, переходим на вкладку Преобразование и находим там пункт Тип данных. Указываем тип Десятичное число:

Примечание: если на каком-то этапе что-то сделали неправильно – не надо все переделывать и не спешите закрывать окно. В правой части окна запросов есть история проделанных изменений – Примененные шаги. В этом окне можно удалить один из шагов:

Только удалять надо тоже аккуратно – если пока еще не очень хорошо ориентируетесь в Power Query – лучше удалять только последний шаг, который повлек ошибку. Если удалить шаг из середины – это может повлечь ошибки в дальнейших шагах, поэтому я не рекомендую удалять шаги из середины истории.

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

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

Читайте также: