Особенности формирования индексных экземпляров отчетов путем загрузки из файлов формата Excel при работе в системе 1С (на базе УХ/ЕРПУХа)
Содержание:
Описание демонстрационной задачи
Настройка вида и бланков отчетов на стороне системы 1C:Предприятие
Подготовка данных для загрузки на стороне Exсel
Дополнительные рекомендации при использовании программы 1С
При настройке системы бюджетирования в 1С (на базе конфигураций 1С:Управление холдингом 8 и 1C:ERP Управление холдингом) может возникнуть потребность загрузки в экземпляры отчетов данных из файлов формата Excel.
«Классический» импорт данных из файлов данного формата эффективно справляется с загрузкой данных в экземпляры отчетов, когда каждое поле загружаемых данных зафиксировано на форме файла импорта (см. рис. 1) и/или загружаемые данные имеют детализацию по «типовой» аналитике, такой как «Контрагенты», «Договоры» и т.д. (см. рис. 2).
Пример формы с зафиксированными полями
Пример формы с “типовой” аналитикой
Однако, перед аналитиком, который занимается разработкой и настройкой системы бюджетирования, может возникнуть потребность загрузки в 1С данных (см. рис. 3), которые обладают следующими особенностями:
Детализация каждого показателя представляет собой заранее неизвестное количество значений, заполняемых в свободной форме пользователем в формате Excel. То есть, отсутствует возможность предварительно заполнить соответствующий справочник вариантов аналитики;
Присутствует потребность в сопоставлении каждому значению своего индекса, уникального в рамках данного вида отчета. К примеру, загруженные данные необходимо передавать во внешнюю аналитическую систему, что требует сопоставление каждой строке уникального индекса для передачи.
Пример формы с индексными данными
В данной статье автором предлагается вариант решения поставленной задачи как на стороне подготовки вида бланков отчета на стороне 1С (для демонстрации используется демонстрационная база 1С:Управление холдингом 8 релиз 3.3.3.45, но предлагаемый механизм может быть использован и для иных релизов конфигураций 1С:Управление холдингом 8 и 1С:ERP Управление холдингом релизов 3.2 и 3.3), так и на стороне подготовки данных в Excel
1. Описание демонстрационной задачи
Для демонстрации нашего решения предлагаем использовать следующий кейс:
Пользователям предоставляется бланк в Excel (см. рис. 4) для заполнения причин отклонения плановых и фактических данных;
Бланк для заполнения пользователем
Пользователь, в результате проведения анализа причин отклонений предоставляет заполненный бланк (см. рис. 5) в формате Excel, который необходимо загрузить в экземпляр отчета.
Заполненный бланк
Как видно, каждый показатель, имеющий раскрытие имеет разное количество строк раскрытия.
2. Настройка вида и бланков отчетов на стороне системы 1C:Предприятие
Первоначально создаем новый вид отчета в подсистеме «Бюджетирование, отчетность и анализ» и переходим в конструктор на вкладку «Структура». На данной вкладке мы вносим только строки, которые представлены на рис. 4. и добавляем требуемые столбцы (см. рис. 6).
Структура создаваемого отчета
Далее на вкладке «Аналитики» нам необходимо добавить аналитику по строкам, которые подвергаются раскрытия. Для этого ставим курсов на пересечении строки, которую требуется раскрыть и столбце «Аналитика 1» и производим двойной щелчок левой клавишей мыши (переходим в список плана видов характеристик «Виды аналитик (корпоративные)»).
В открытой форме нажимаем кнопку «Создать» и создаем новый вид аналитик (см. рис. 7) со следующими особенностями:
Тип значения устанавливаем «Произвольный классификатор»;
Устанавливаем флаг на пункте «Создавать при неудачном поиске при импорте»;
Устанавливаем флаги «Макет» и «Ключ» в табличной части формы по строке «Наименование».
Создание нового вида аналитик
Устанавливаем созданный вид аналитик как аналитику для строк, которые требуют раскрытия (см. рис. 8).
Аналитика создаваемого отчета
Далее на вкладке «Формулы» открываем правило расчета и меняем значение поля «Тип информационной базы» на предустановленный вариант «Файлы Microsoft Excel» (см. рис. 9).
Настройка правила расчета
На этом работа с конструктором отчета закончена и можно перейти к настройке бланка отчета для импорта. Бланк импорта настраивается типовым способом и дополнительной работы не требует (см. рис. 10).
Бланк импорта данных
3. Подготовка данных для загрузки на стороне Exсel
Далее выгружаем бланк для импорта и переносим выгруженный лист в файл с заполненными данными (см. рис. 11).
Пример файла импорта
Если сравнить бланк, который заполнил пользователь (см. рис 5) и который требуется заполнить для загрузки в 1С (см. рис. 11), то можно увидеть отличие структуры. Для импортf требуется расшифровки выделить в отдельный столбец.
Автором предлагается избежать ручного труда по заполнению листа с данными для импорта и произвести следующие действия:
Создать отдельный лист «СтрокиОтчета», куда перенести значения строк отчета без учета, требуют они расшифровки или нет (см. рис. 12). Порядок указания строк, наличие пустых строк между ними, шрифт и т.д. не важен и на работу не влияет
Содержание созданного листа “Строки отчета”
2. На листе импорта данных удаляем все строки, ниже наименования столбцов (см. рис. 13).
Подготовка файла к автоматическому заполнению
3. В первом столбце в первой строке ниже наименования столбцов вводим формулу:
ЕСЛИ(
ЕСЛИОШИБКА(
ИНДЕКС(
СтрокиОтчета!A:A;
ПОИСКПОЗ(
ЗаполненныйБланк!B4;
СтрокиОтчета!A:A;
0);
1);
0)=0;
“”;
ЗаполненныйБланк!B4)
В ссылках указываем на первую строку листа с заполненными данными, также ниже наименования столбцов.
4. Во втором столбце в этой же строке вводим формулу:
ЕСЛИ(
$A11=””;
ЕСЛИ(
ЗаполненныйБланк!B4=0;
“”;
ЗаполненныйБланк!B4);
“”)
5. D третий, четвертый и пятый столбцы вводим простую формулу
=ЕСЛИ(
ЗаполненныйБланк!C4=0;
“”;
ЗаполненныйБланк!C4)
6. Далее копируем строку с формулами ниже на произвольное количество строк с гарантированным запасом строк заполнения строк пользователем (см. рис. 14)
Заполнение формулами листа импорта данных
7. В результате получаем заполнение листа, требуемое для импорта (см. рис. 15).
Готовые данные для импорта
4. Дополнительные рекомендации при использовании программы 1С
После загрузки данных в экземпляр отчета вид отчета отличается от того бланка, который заполнял пользователь и отсутствует информация об индексе строки (см. рис. 16).
Загруженные данные
В связи с тем, что бланк импорта не дает возможности внесения изменений, то предлагается сделать дополнительный бланк для вывода печатной формы (см. рис. 17), в котором добавить вывод кода созданного вида аналитики и добавить в дополнительные бланки бланка импорта (см. рис. 18).
Создание бланка для вывода пользователю
Добавление созданного бланка к бланку импорта
В результате, при формировании экземпляра отчета и импорта данных автоматически будет формироваться печатная форма с выводом информации, удобной для представления пользователю (см. рис. 19). Данная форма может быть подвержена дополнительной обработке в части изменении формата вывода данных, более комфортной для представления пользователю.
Визуальное представление загруженных данных
Также в созданном виде аналитике будут собираться значения вариантов причин отклонений, каждая из которых будет иметь свой уникальный индекс в разрезе данного отчета (см. рис. 20).
Содержание загруженного вида аналитики
Как видно, поставленной задаче мы добились и требуемая информация загружена в экземпляр отчета в требуемом виде.
В случае необходимости, специалисты Кодерлайна могут Вам помочь в настройке систем бюджетирования любой сложности, обучить Ваших сотрудников и оказать помощь при внедрении проектов любой сложности.
Особенности формирования индексных экземпляров отчетов путем загрузки из файлов формата Excel при работе в системе 1С (на базе УХ/ЕРПУХа)
Содержание:
При настройке системы бюджетирования в 1С (на базе конфигураций 1С:Управление холдингом 8 и 1C:ERP Управление холдингом) может возникнуть потребность загрузки в экземпляры отчетов данных из файлов формата Excel.
«Классический» импорт данных из файлов данного формата эффективно справляется с загрузкой данных в экземпляры отчетов, когда каждое поле загружаемых данных зафиксировано на форме файла импорта (см. рис. 1) и/или загружаемые данные имеют детализацию по «типовой» аналитике, такой как «Контрагенты», «Договоры» и т.д. (см. рис. 2).
Пример формы с зафиксированными полями
Пример формы с “типовой” аналитикой
Однако, перед аналитиком, который занимается разработкой и настройкой системы бюджетирования, может возникнуть потребность загрузки в 1С данных (см. рис. 3), которые обладают следующими особенностями:
Пример формы с индексными данными
В данной статье автором предлагается вариант решения поставленной задачи как на стороне подготовки вида бланков отчета на стороне 1С (для демонстрации используется демонстрационная база 1С:Управление холдингом 8 релиз 3.3.3.45, но предлагаемый механизм может быть использован и для иных релизов конфигураций 1С:Управление холдингом 8 и 1С:ERP Управление холдингом релизов 3.2 и 3.3), так и на стороне подготовки данных в Excel
1. Описание демонстрационной задачи
Для демонстрации нашего решения предлагаем использовать следующий кейс:
Бланк для заполнения пользователем
Заполненный бланк
Как видно, каждый показатель, имеющий раскрытие имеет разное количество строк раскрытия.
2. Настройка вида и бланков отчетов на стороне системы 1C:Предприятие
Первоначально создаем новый вид отчета в подсистеме «Бюджетирование, отчетность и анализ» и переходим в конструктор на вкладку «Структура». На данной вкладке мы вносим только строки, которые представлены на рис. 4. и добавляем требуемые столбцы (см. рис. 6).
Структура создаваемого отчета
Далее на вкладке «Аналитики» нам необходимо добавить аналитику по строкам, которые подвергаются раскрытия. Для этого ставим курсов на пересечении строки, которую требуется раскрыть и столбце «Аналитика 1» и производим двойной щелчок левой клавишей мыши (переходим в список плана видов характеристик «Виды аналитик (корпоративные)»).
В открытой форме нажимаем кнопку «Создать» и создаем новый вид аналитик (см. рис. 7) со следующими особенностями:
Создание нового вида аналитик
Устанавливаем созданный вид аналитик как аналитику для строк, которые требуют раскрытия (см. рис. 8).
Аналитика создаваемого отчета
Далее на вкладке «Формулы» открываем правило расчета и меняем значение поля «Тип информационной базы» на предустановленный вариант «Файлы Microsoft Excel» (см. рис. 9).
Настройка правила расчета
На этом работа с конструктором отчета закончена и можно перейти к настройке бланка отчета для импорта. Бланк импорта настраивается типовым способом и дополнительной работы не требует (см. рис. 10).
Бланк импорта данных
3. Подготовка данных для загрузки на стороне Exсel
Далее выгружаем бланк для импорта и переносим выгруженный лист в файл с заполненными данными (см. рис. 11).
Пример файла импорта
Если сравнить бланк, который заполнил пользователь (см. рис 5) и который требуется заполнить для загрузки в 1С (см. рис. 11), то можно увидеть отличие структуры. Для импортf требуется расшифровки выделить в отдельный столбец.
Автором предлагается избежать ручного труда по заполнению листа с данными для импорта и произвести следующие действия:
Содержание созданного листа “Строки отчета”
2. На листе импорта данных удаляем все строки, ниже наименования столбцов (см. рис. 13).
Подготовка файла к автоматическому заполнению
3. В первом столбце в первой строке ниже наименования столбцов вводим формулу:
ЕСЛИ(
ЕСЛИОШИБКА(
ИНДЕКС(
СтрокиОтчета!A:A;
ПОИСКПОЗ(
ЗаполненныйБланк!B4;
СтрокиОтчета!A:A;
0);
1);
0)=0;
“”;
ЗаполненныйБланк!B4)
В ссылках указываем на первую строку листа с заполненными данными, также ниже наименования столбцов.
4. Во втором столбце в этой же строке вводим формулу:
ЕСЛИ(
$A11=””;
ЕСЛИ(
ЗаполненныйБланк!B4=0;
“”;
ЗаполненныйБланк!B4);
“”)
5. D третий, четвертый и пятый столбцы вводим простую формулу
=ЕСЛИ(
ЗаполненныйБланк!C4=0;
“”;
ЗаполненныйБланк!C4)
6. Далее копируем строку с формулами ниже на произвольное количество строк с гарантированным запасом строк заполнения строк пользователем (см. рис. 14)
Заполнение формулами листа импорта данных
7. В результате получаем заполнение листа, требуемое для импорта (см. рис. 15).
Готовые данные для импорта
4. Дополнительные рекомендации при использовании программы 1С
После загрузки данных в экземпляр отчета вид отчета отличается от того бланка, который заполнял пользователь и отсутствует информация об индексе строки (см. рис. 16).
Загруженные данные
В связи с тем, что бланк импорта не дает возможности внесения изменений, то предлагается сделать дополнительный бланк для вывода печатной формы (см. рис. 17), в котором добавить вывод кода созданного вида аналитики и добавить в дополнительные бланки бланка импорта (см. рис. 18).
Создание бланка для вывода пользователю
Добавление созданного бланка к бланку импорта
В результате, при формировании экземпляра отчета и импорта данных автоматически будет формироваться печатная форма с выводом информации, удобной для представления пользователю (см. рис. 19). Данная форма может быть подвержена дополнительной обработке в части изменении формата вывода данных, более комфортной для представления пользователю.
Визуальное представление загруженных данных
Также в созданном виде аналитике будут собираться значения вариантов причин отклонений, каждая из которых будет иметь свой уникальный индекс в разрезе данного отчета (см. рис. 20).
Содержание загруженного вида аналитики
Как видно, поставленной задаче мы добились и требуемая информация загружена в экземпляр отчета в требуемом виде.
В случае необходимости, специалисты Кодерлайна могут Вам помочь в настройке систем бюджетирования любой сложности, обучить Ваших сотрудников и оказать помощь при внедрении проектов любой сложности.
Специалист компании ООО “Кодерлайн”,
Вейс Александр