Краткий обзор ADP Crosstab Studio

ADP Crosstab Studio это универсальная программа для автоматизации процессов очистки и преобразования исходных данных, представленных в виде кросс-таблиц, в плоские таблицы, консолидации данных из заданного перечня кросс-таблиц и получения отчетов в виде кросс-таблиц. Все таблицы представляются в формате Microsoft Excel.

Применяется в процессах учета, анализа, планирования и финансового управления, сбора и агрегирования данных, получения отчетов, подготовки данных для других информационных систем, использующих плоские (реляционные) таблицы.

Особое внимание в ADP Crosstab Studio уделено обеспечению гибкости представления данных и отчетов.

Концепция автоматизации ADP Crosstab Studio состоит в том, чтобы использовать MS Excel для хранения и ввода данных, необходимых для задач бизнеса, в удобном для человека формате, а обработку данных и получение отчетов автоматизировать.

Таким удобным для человека форматом является кросс-таблица или сводная таблица, которая традиционно используются в документах и печатных изданиях. Пример кросс-таблицы приведен на Рисунке 1.

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

Кросс-таблицы хорошо подходят для компактного и иерархического представления данных, но по многим причинам не подходят для автоматической обработки, поэтому необходимо преобразовать кросс-таблицы к формату, «удобному» для компьютера – формату «плоской» таблицы (Рисунок 2). Этот процесс будем называть преобразованием кросс-таблицы.

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

Плоские таблицы широко применяются в базах данных, благодаря простоте обработки информации, так как взаимосвязь между данными распространяется только на строку.

Возможности программы

Функции, реализованные в ADP Crosstab Studio, позволяют автоматически производить очистку исходных данных, представленных в виде кросс-таблиц в MS Excel от нужной информации, преобразовать исходные кросс-таблицы в плоские таблицы, автоматически консолидировать данные из заданного перечня кросс-таблиц и получать отчеты в виде сводной таблицы PivotTable Excel или отчеты по заранее составленному шаблону.

Программа предъявляет минимальные требования к формату кросс-таблиц, что позволяет использовать максимально понятные формы отчетов и исключить затраты на обучение персонала для их заполнения.

ADP Crosstab Studio позволяет выполнять преобразования кросс-таблиц с разными видами проверок для очистки данных от лишней информации без редактирования исходных кросс-таблиц.

Очистка данных. Автоматическая очистка данных может быть произведена тремя способами или их комбинацией:

1. С использованием строк и колонок спецсимволов. Строки и колонки спецсимволов, могут содержать спецсимволы комментариев. Отмеченные такими спецсимволами колонки и строки игнорируются при преобразовании и данные, находящиеся в них в плоскую таблицу не попадают.
2. С проверкой по словарю (боковик и/или головка). Только значения, соответствующие словарю, используются при преобразовании в плоскую таблицу. Наименования, не найденные в словаре, игнорируются и данные, находящиеся в соответствующих строках и/или колонках в плоскую таблицу не попадают.
3. С проверкой по классификатору в боковике. Только наименования, имеющиеся в классификаторе, используются при преобразовании в плоскую таблицу. Наименования, не найденные в классификаторе, игнорируются и данные, находящиеся в соответствующих строках в плоскую таблицу не попадают.

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

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

Классификатор позволяет использовать иерархию терминов, используемых в заголовках кросс-таблиц для группировки данных. Отношения подчинённости из классификатора включаются в таблицу консолидации, что позволяет использовать фильтры PivotTable для агрегирования данных в разрезе этой подчиненности для анализа и отчетов.

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

Шаблон отчета. ADP Crosstab Studio позволяет получать отчеты, на основе шаблонов отчетов в форме кросс-таблицы, которые составляет сам пользователь в MS Excel. В шаблоне можно использовать формулы и ограничивать области автоматического заполнения используя строки/колонки комментариев, словарь и классификатор.

Процесс автоматизации подготовки, преобразования кросс-таблиц и получение отчетов с помощью ADP Crosstab Studio включает в себя ряд шагов:

1. Определение формы кросс-таблиц исходных данных и отчетов
2. Заполнение кросс-таблиц данными в редакторе электронных таблиц MS Excel
3. Ввод описания перечня подготовленных кросс-таблиц в ADP Crosstab Studio в соответствие с требованиями программы
4. Очистка, преобразование и консолидация данных из кросс-таблиц в плоскую таблицу
5. Получение отчетов в формате MS Excel в виде сводных таблиц, в соответствии с задачами, либо PivotTable, либо отчета по шаблону

Шаги 1 — 2 производятся в редакторе MS Excel.

Шаги 3 — 5 производятся в ADP CrossTabs Studio, из них 4 и 5 — автоматизированы.

Рекомендации по автоматизации

Работа в программе ADP CrossTabs Studio начинается с составления описания проекта. Проект это набор кросс-таблиц с исходными данными и описанием их структуры. Файлы кросс-таблиц должны быть подготовлены в формате Excel.

Типовая последовательность действий системы автоматизации на основе ADP Crosstab Studio выглядит следующим образом:

1. Подготовка форм кросс-таблиц исходных данных и отчетов
   • Разработать формы кросс-таблиц для исходных данных
   • Разработать словарь и/или классификатор, если необходимо
   • Разработать формы отчетов, если необходимо
2. Заполнение кросс-таблиц данными в Excel.
3. Создание проекта в ADP Crosstab Studio
   • Дать имя новому проекту
   • Получить таблицы с исходными данными
   • Внести описание таблиц исходных данных в список проекта
   • Если есть словарь и/или классификатор, включить в проект
   • Внести в проект описание таблицы отчета
4. Работа с проектом в ADP Crosstab Studio
   • Задать параметры преобразования
   • Проверить корректность преобразования таблиц исходных данных, при необходимости
   • Получить отчет на основании списка таблиц проекта
5. Продолжение работы с проектом в ADP Crosstab Studio
   • Получить таблицы с новыми исходными данными
   • Если диапазоны таблиц изменились, корректировать данные диапазонов в описании таблиц.
   • Если форма отчета изменилась, редактировать описание таблицы отчета
   • Получить отчет на основании списка таблиц проекта

Руководство по началу работы

С помощью этого руководства вы настроите свой первый проект — получение отчета из двух кросс-таблиц.

Шаг 1. Изучение разделов

Главное диалоговое окно программы ADP Crosstab Studio разбито на вкладки. Вкладки — это набор страниц в одном диалоговом окне, каждая со своим содержимым. Вкладки можно выбрать нажав на заголовок, который находится верху вкладки. В ADP Crosstab Studio используются следующие вкладки: Файл, Проект, Словарь, Классификатор, Отчет по шаблону, Настройки.

• Используйте вкладку Файл для создания нового проекта, открытия или сохранения проекта.
• Используйте вкладку Проект для формирования списка кросс-таблиц проекта и описания атрибутов проекта.
• Используйте вкладку Словарь для создания, открытия, редактирования и сохранения словаря проекта.
• Используйте вкладку Классификатор для создания, открытия, редактирования и сохранения классификатора проекта.
• Используйте вкладку Отчет по Шаблону для описания источника данных отчета и шаблона отчета.
• Используйте вкладку Настройки для выбора правил очистки данных, вариантов преобразования кросс-таблиц, правил записи результатов в Excel.

Шаг 2. Подготовка исходных данных

Исходными данными являются кросс-таблицы, которые создаются в редакторе MS Excel.

Создадим отчеты двух зоомагазинов.

1. Запустите программу MS Excel.
2. Создайте новый файл и сохраните его с именем Зоомагазины.xlsx.
3. На листе Лист1 создайте две таблицы, Зоомагазин 1 и Зоомагазин 2, которые содержат отчеты за 1 квартал и расчеты промежуточных и общих итогов:

Шаг 3. Создание проекта

Если мы уже создали кросс-таблицы, пришло время создать свой первый проект.

1. Откройте программу ADP Crosstab Studio.
2. На вкладке Файл кликните кнопку Создать новый проект, откроется диалоговое окно выбора директории и ввода имени файла проекта.
3. Выберете директорию и введите имя проекта Зоомагазины, кликните кнопку Сохранить, отобразится вкладка Проект.

Шаг 4. Создание описания кросс-таблиц проекта

На вкладке Проект внесите в список кросс-таблиц проекта описание двух кросс-таблиц Зоомагазин 1 и Зоомагазин 2.

1. На ленте команд вкладки Проект щелкните кнопку Добавить таблицу. Вы получите доступ к окну заполнения параметров кросс-таблицы.
2. Щелкните по кнопке Обзор и выберете созданный файл Зоомагазины.xlsx.
3. Введите наименование таблицы Зоомагазин 1.
4. Кликните кнопку Выделить диапазон и, в открывшимся файле Зоомагазины.xlsx, выделите диапазон таблицы Зоомагазины 1.
5. В диалоговом окне Параметры кросс-таблицы появится имя листа книги Excel и адрес диапазона.

6. Кликните кнопку OK.
7. На вкладке Проект, в списке кросс-таблиц проекта появится строка с именем Зоомагазин 1.
8. На ленте команд вкладки Проект щелкните кнопку Добавить таблицу и в той же последовательности создайте описание кросс-таблицы Зоомагазин 2.

Создайте описание заголовков столбцов Боковика и ярусов Головки кросс-таблиц. В проекте у всех кросс-таблиц должно быть одинаковое число столбцов в Боковике и ярусов в Головке. У кросс-таблиц Зоомагазин 1 и 2 в Боковике два столбца и в Головке три яруса.

Чтобы дать наименования трем ярусам Головки, выполните следующие действия:

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

Элемент интерфейса вкладки Проект, ввод и редактирование наименований ярусов головки

Чтобы дать наименования двум столбцам Боковика, выполните следующие действия:

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

Элемент интерфейса вкладки Проект, ввод и редактирование наименований столбцов боковика

Шаг 5. Создание словаря

В идеальной ситуации, исходные кросс-таблицы содержат только нужную информацию и их можно использовать «как есть». В реальности, такое случается редко. В отчетах есть много избыточной или поясняющей информации, которую нужно исключить, иначе она будет препятствовать корректному автоматическому анализу данных. Обычно необходимо исключать подзаголовки, строки/колонки с промежуточными итогами или информацию, которая использовалась для расчетов нужных показателей, исправить формат данных и т.п.

Например, особенностью кросс-таблиц Зоомагазин 1, 2 является то, что у них есть строки и столбцы с итогами, а также в них содержатся данные разной размерности: рубли и штуки. Это наглядно и понятно для человека, но для компьютерного анализа, предварительно, нужно разделить данные в рублях и штуках, и исключить данные итогов. Допустим, нам интересны данные только в рублях.

Исключение ненужных данных при преобразовании называется очисткой данных. В ADP Crosstab Studio очистка данных происходит автоматически и может быть выполнена тремя способами:

1. С использованием строк и колонок спецсимволов. Строки и колонки спецсимволов, могут содержать символы комментариев. Отмеченные как комментарии колонки и строки игнорируются при преобразовании и данные, находящиеся в них, не переносятся в плоскую таблицу.
2. С проверкой по словарю (боковик и/или головка). Заголовки боковика и/или головки проверяются на наличие в словаре. Только значения, соответствующие заголовкам, найденным в словаре, используются при преобразовании в плоскую таблицу. Заголовки, не найденные в словаре, игнорируются и данные, находящиеся в соответствующих строках и/или колонках в плоскую таблицу не попадают.
3. С проверкой по классификатору (только в боковике). Только строки кросс-таблицы с заголовками боковика, имеющимися в классификаторе, используются при преобразовании в плоскую таблицу. Заголовки, не найденные в классификаторе, игнорируются и данные, находящиеся в соответствующих строках в плоскую таблицу не попадают.

Можно использовать все три способа одновременно, в разных сочетаниях.

В данном примере покажем использование словаря для очистки данных.

Основное назначение словаря, — обеспечить унификацию терминов, используемых в кросс-таблицах для интеграции данных. Словарь применяется в боковике или головке кросс-таблиц и Шаблонов отчетов.

Словарь может быть включен в проект и исключен из проекта. Для проверки по словарю заголовков колонки боковика или яруса головки, в списках колонок боковика и ярусов головки на вкладке Проект должен быть указан тип проверки — Словарь.

Если указан тип проверки — Словарь, то все строки или столбцы с заголовками, не входящими в словарь, считаются комментариями и исключаются из процесса преобразования.

Создадим словарь на основании проекта Зоомагазин.

1. Откройте вкладку Словарь.
2. Кликните на ленте команд кнопку Создать словарь из проекта. Появится диалоговое окно, в котором к группам будут отнесены наименования из списков Боковика и Головки на вкладке Проект и, в каждую группу помещены значения из Боковики и Головки кросс-таблиц. В словарь попадут все наименования из кросс-таблиц.
3. Исключите из Словаря ненужные наименования, такие как: Всего, Всего 1 кв., Итого, кол-во, шт. Для удобства, отсортируйте список по Наименованию. Для этого нажмите на заголовок колонки Name. Чтобы удалить наименование из списка перейдите на нужную строку списка и кликните кнопку Удалить строку.
4. Кликните кнопку Сохранить в проект … появится диалоговое окно выбора директории и ввода имени файла.
5. Введите имя файла Словарь Зоомагазины и кликните кнопку Сохранить. Во вкладке Словарь появится имя файла словаря, а также группы и, соответствующие им наименования словаря, отобразятся в списках.

Для того, чтобы использовать словарь каждому заголовку Боковика и Головки на вкладке Проект нужно назначить тип проверки Словарь.

Для назначения типа проверки заголовков Боковика и Головки выполните следующие действия.

1. Откройте вкладку Проект.
2. Выберете строку в списке ярусов головки, кликните на выпадающий список и выберете значение Словарь. В выбранной строке появится тип проверки Словарь.
3. Выберете для всех остальных заголовков ярусов головки и столбцов боковика тип проверки Словарь.

Выбор типа проверки значений боковика и головки

Шаг 6. Преобразование кросс-таблиц

Для того, чтобы получить возможность автоматической обработки данных из кросс-таблиц, кросс-таблицы нужно преобразовать в формат плоских таблиц. Формат плоских таблиц неудобен для человека, но удобен для обработки компьютерными программами, такими как электронные таблицы (MS Excel) и приложения для работы с базами данных.

Теперь, когда описание кросс-таблиц проекта создано, можно использовать функционал операций ADP Crosstab Studio с кросс-таблицами.

ADP Crosstab Studio позволяет, прежде записи результата преобразования кросс-таблиц в файл MS Excel, визуально оценить правильность очистки данных. С помощью функции Проверить преобразование и очистить данные в диалоговое окно выводится диапазон кросс-таблицы и цветом выделяются ячейки кросс-таблицы, данные из которых при преобразовании будут перенесены в плоскую таблицу.

Проверка преобразования кросс-таблицы

1. На вкладке Проект выберете в списке кросс-таблиц проекта кросс-таблицу для проверки.
2. Кликните кнопку Проверить преобразование и очистить данные на ленте команд вкладки Проект — появится диалоговое окно визуализации проверки преобразования кросс-таблицы. В диалоговом окне визуальной проверки представлена следующая информация:
   • Содержание ячеек выбранного диапазона кросс-таблицы, адрес которого указан в верхней части диалогового окна.
   • Серым цветом выделены ячейки, которые считаются значимыми или рабочими, т.е. данные из этих ячеек попадут в плоскую таблицу после преобразования.
   • Ячейки на белом фоне являются незначащими или нерабочими – не участвуют в преобразовании и данные из них не попадут в плоскую таблицу после преобразования.
   • В списке сообщений внизу диалогового окна выводится информация с указанием причины, по которой ячейка отмечена как нерабочая.
3. Кликните кнопку Выход, диалоговое окно проверки кросс-таблицы закроется.

На снимке с экрана видны сообщения о том, что программа не нашла 11 заголовков в словаре. Это те заголовки, которые мы исключили при создании словаря (выше). Найденные в словаре заголовки и соответствующие им значения, представлены на сером фоне, не найденные в словаре заголовки и соответствующие им значения, представлены на белом фоне.

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

Также в диалоговом окне видно как обрабатывается объединение ячеек в боковике и головке — значение объединенной ячейки копируется в каждую ячейку объединенного диапазона.

Преобразование и консолидация кросс-таблиц

Функционал ADP Crosstab Studio позволяет преобразовать в плоскую таблицу как одну кросс-таблицу из списка таблиц проекта, так и все кросс-таблицы проекта.

Для преобразования всех кросс-таблиц проекта в плоскую таблицу кликните кнопку Консолидировать на ленте команд вкладки Проект. В результате будет создана плоская таблица с данными, консолидированными из двух кросс-таблиц проекта.

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

• Имя листа таблицы Данных «Лист 2«

и сообщения об ошибках, в которых перечислены заголовки боковика и головки кросс-таблиц, которые не найдены в словаре.

Шаг 7. Получение сводной таблицы PivotTable

Можно автоматически получить сводную таблицу PivotTable из одной или всех кросс-таблиц проекта.

Для получения сводной таблицы из всех кросс-таблиц проекта кликните кнопку Консолидировать в PivotTable на ленте команд вкладки Проект. В результате будет создана плоская таблица с данными, консолидированными из двух кросс-таблиц проекта и сводная таблица PivotTable .

Шаг 8. Получение отчета по Шаблону

Шаблон отчета (далее – Шаблон) — это кросс-таблица, форма которой создается в MS Excel и копия которой при построении отчета заполняется данными из кросс-таблиц проекта. Параметры Шаблона описываются в диалоге ADP Crosstab Studio подобно параметрам кросс-таблиц проекта.

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

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

Создание шаблона отчета

1. Создайте новый лист в файле Зоомагазин.xlsx и дайте ему имя Шаблон1.
2. Создайте в файле Зоомагазин.xlsx таблицу шаблона отчета для проекта Зоомагазин, показанную на рисунке.
3. Откройте вкладку Отчет по Шаблону программы ADP Crosstab Studio, чтобы создать описание таблицы Данных – источника данных для отчета и создать описание параметров таблицы Шаблона.
4. Кликните кнопку Обзор поля Файл таблицы данных блока Выбор таблицы данных и выберите файл Зоомагазин.xlsx. Одновременно с этим, если поле Файл таблицы Шаблона было пустым, в нем также отобразится адрес файла Зоомагазин.xlsx.
5. Кликните кнопку Диапазон блока Выбор таблицы данных, откроется файл Зоомагазин.xlsx  и диалоговое окно выбора диапазона, перейдите на лист плоской таблицы данных и выделите ее диапазон. Адрес диапазона таблицы данных отобразится в поле Выделите диапазон, нажмите кнопку OK. Файл Excel закроется и в полях Лист и Адрес появятся выбранные лист и адрес диапазона таблицы данных. Также в списке Заголовки таблицы Данных появятся имена заголовков колонок выбранной таблицы Данных.
6. Кликните кнопку Диапазон блока Выбор таблицы шаблона отчета, в открывшемся файле появляется диалоговое окно выбора диапазона, перейдите на лист Шаблон1 и выделите диапазон таблицы шаблона . Адрес диапазона таблицы шаблона отобразится в поле Выберете диапазон, нажмите кнопку OK. Файл Excel закроется и в полях Лист и Адрес появятся выбранные лист и адрес диапазона таблицы шаблона.
7. Создайте описание наименований колонок Боковика, ярусов Головки и Значения таблицы Шаблона из списка заголовков таблицы Данных. Выберете из списка заголовков таблицы Данных нужный заголовок и, удерживая нажатой левую клавишу мыши, перетащите в область списка заголовков ярусов Головки, колонок Боковика и Значения. Порядок расположения заголовков в списках заголовков Боковика и Головки по направлению «сверху-вниз» должен соответствовать их порядку в кросс-таблице шаблона отчета на листе Шаблон1: для Боковика – «слева-направо», для Головка – «сверху-вниз». В списке заголовков таблицы Данных у каждого выбранного заголовка будет установлен флажок.

8. Выберете тип проверки для заголовков Шаблона. На вкладке Отчет по Шаблону в списках заголовков для Боковика и Головки выберете тип проверки Словарь для каждого наименования.
9. Получите отчет по шаблону отчета Шаблон1. Для этого кликните кнопку Отчет по Шаблону по таблице Данных на ленте команд вкладки Отчет по Шаблону. В списке сообщений появится сообщение с именем листа книги Зоомагазин.xlsx, в котором сохранен отчет по Шаблону.

Обратите внимание на диапазон полученного отчета, он всегда совпадает с адресом диапазона Шаблона отчета, по которому он создан и сохраняет его размеры и цвета.

Вы можете визуально проверить, как ADP Crosstab Studio читает таблицу Шаблона1. Для этого кликните кнопку Проверить Шаблон отчета на ленте команд вкладки Отчет по Шаблону. Появится диалоговое окно проверки Шаблона отчета, где серым обозначены рабочие ячейки. В области данных кросс-таблицы такие ячейки будут заполняться найденными значениями. В списке сообщений будут сообщения о наименованиях Боковика и Головки таблицы шаблона, не найденных в Словаре.