Использование инструмента Консолидация при обработке данных мониторингов
материал

Использование инструмента Консолидация при обработке данных мониторингов

При выполнении различных запросов, проведении мониторингов по различным направлениям деятельности, других задач часто приходится использовать множество однотипных таблиц в программе Microsoft Excel.

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

В данном материале в качестве примера приведена упрощенная таблица из Паспорта организации, заполняемая образовательными организациями в рамках мониторинга реабилитационного паспорта организаций «Раздел 6.0. Сведения о наличии в Организации лицензии на осуществление различных видов образовательной деятельности (любой подведомственности)».

Рисунок 1

В работах по обобщению данных использовалась функция суммирования числовых значений, поэтому алгоритм составлен для простого подсчета сумм в каждой ячейке таблицы. Каждый файл состоит из одного листа и содержит заголовок и несколько строк с данными, которые требуется суммировать. Таблицы содержат 3 столбца и 14 строк. Первую, вторую и третью строки занимают заголовок, подзаголовок и нумерация столбцов, суммируемые данные размещены в строках с четвертой по четырнадцатую. Файл с консолидированными данными и файлы, из которых собираются данные размещены в одной папке в файловой системе и названы «Консолидация», «Пример1» и «Пример2» соответственно.

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

Курсор устанавливается в первую ячейку объединенного диапазона.

Рисунок 2

Переходим на вкладку «Данные». В группе «Работа с данными» нажимаем кнопку «Консолидация».

Рисунок 3

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

Рисунок 4

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

Рисунок 5

Затем выделяем диапазон в файле «пример2» и также нажимаем «добавить».

Рисунок 6

Рисунок 7

Таблицы для консолидации добавляются в списке диапазонов.

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

Рисунок 8

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

При предварительной обработке требуется проверить, не сместились ли в таблицах столбцы или строки, являются ли содержащиеся в них сведения однородными (числа либо текстовые данные).  Самой распространенной ошибкой при заполнении является несоответствие типов данных: в ячейки с числовым форматом добавляется текст, например: "1 шт." вместо "1", "2,5 ст." вместо "2,5", а также различные примечания, такие как: "не имеется", "нет сведений" "--" (прочерк вместо цифры "0") и т.п. При обнаружении таких ошибок вносятся соответствующие исправления.

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

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

Можно выделять не только диапазоны ячеек, но и всю таблицу вместе с заголовками, при этом, чтоб сохранялись надписи, нужно поставить галочку в разделе «Использовать в качестве имен» в значение «подписи верхней строки» и/или «значения левого столбца». Если планируется вносить данные в исходные таблицы и далее, то нужно отметить галочкой «Создавать связи с исходными данными».

Инструмент «Консолидация» помогает быстро решать задачи объединения данных, находящихся в большом числе разрозненных диапазонов в один для быстрого упорядочения и подготовки к анализу.