При сведении данных из различных однотипных отчетов, таблиц, диапазонов ячеек в один общий, сводный отчет можно использовать различные способы, от простых формул, до мощных и гибких инструментов по консолидации данных.
Консолидация в Excel
Объединение или сведение данных из разных диапазонов ячеек в один выходной диапазон, с использованием какой-либо функции (например, суммирования) называется консолидацией.
Простой пример консолидированной отчетности - любой квартальный отчет, полученный суммированием трех месячных отчетов.
Различают консолидацию по расположению и консолидацию по категориям. Различие заключается в степени упорядоченности исходных данных.
Консолидация по расположению
Для обобщения данных из различных таблиц, все эти таблицы должны быть одинаковыми.
Консолидация по категориям
Для сведения данных, расположенных в разном порядке, но имеющих одинаковые подписи строк и столбцов используется консолидацию по категориям значений.
Сведение данных при помощи формул
Консолидирование данных подразумевает использование какой-либо функции, например, сумма или произведение значений, поиск средних, минимальных и максимальных значений. Простой свод данных из нескольких однотипных таблиц можно сделать обычными, стандартными формулами при помощи функций «СУММ», «ПРОИЗВЕД», «МАКС», «МИН» и т.д.
Стандартная консолидация
Для решения задач по сбору разрозненных данных в обобщенную таблицу, существует специальный инструмент – «Консолидация» (кнопку можно найти на вкладке Данные/Работа с данными/Консолидация). Инструмент достаточно мощный, позволяет осуществлять консолидацию как по расположению, так и по категориям, имеет более десяти используемых функций, позволяет создавать связи с исходными данными. Все достаточно просто, нажатие на кнопку вызывает окно «Консолидация», в котором выбирается функция, создается список из диапазонов исходных данных. При нарушении очередности строк и/или столбцов в исходных диапазонах, задаются адреса диапазонов, включающие в себя подписи строк и столбцов, а также ставятся флажки в полях «Подписи верхней строки» и «Значения левого столбца».
Консолидация при помощи надстройки
При обработке больших объемов информации, использование формул и стандартной консолидации может быть неудобным. Формулы могут быть слишком громоздкими и в них легко может закрасться ошибка, диапазонов в списке для консолидации также может оказаться очень много, что сделает использование этого инструмента неприемлемым.
Для решения задач, выходящих за рамки возможностей стандартных средств Excel, можно использовать надстройки – процедуры и функции, написанные на встроенном в приложения Microsoft Office, языке программирования. Надстройки легко интегрируются в приложения и расширяют их стандартные возможности.
При помощи надстройки можно быстро консолидировать данные из большого количества рабочих книг, по различным листам и диапазонам.
надстройка для консолидации данных в Excel
Надстройка позволяет:
1. Быстро создавать список исходных рабочих книг для консолидации;
2. Гибко настраивать листы, содержащие исходные данные, по их видимости, номерам, именам, наличию определенных значений и так далее;
3. Задавать адреса на итоговом (активном) листе как для одного, так и для нескольких диапазонов ячеек;
4. Выбирать одну из наиболее используемых функций (сумма, произведение, максимум, минимум);
5. Выбирать тип сведения данных (по расположению или по категориям).
Видео по сведению данных
Другие материалы по теме:
|
|
