Консолидация в excel как

Как свести данные? Консолидация данных в Excel

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

Консолидация в Excel

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

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

Различают консолидацию по расположению и консолидацию по категориям. Различие заключается в степени упорядоченности исходных данных.

Консолидация по расположению

Для обобщения данных из различных таблиц, все эти таблицы должны быть одинаковыми.

Консолидация по категориям

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

Сведение данных при помощи формул

Консолидирование данных подразумевает использование какой-либо функции, например, сумма или произведение значений, поиск средних, минимальных и максимальных значений. Простой свод данных из нескольких однотипных таблиц можно сделать обычными, стандартными формулами при помощи функций «СУММ», «ПРОИЗВЕД», «МАКС», «МИН» и т.д.

Стандартная консолидация

Для решения задач по сбору разрозненных данных в обобщенную таблицу, существует специальный инструмент – «Консолидация» (кнопку можно найти на вкладке Данные/Работа с данными/Консолидация). Инструмент достаточно мощный, позволяет осуществлять консолидацию как по расположению, так и по категориям, имеет более десяти используемых функций, позволяет создавать связи с исходными данными. Все достаточно просто, нажатие на кнопку вызывает окно «Консолидация», в котором выбирается функция, создается список из диапазонов исходных данных. При нарушении очередности строк и/или столбцов в исходных диапазонах, задаются адреса диапазонов, включающие в себя подписи строк и столбцов, а также ставятся флажки в полях «Подписи верхней строки» и «Значения левого столбца».

Консолидация при помощи надстройки

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

Для решения задач, выходящих за рамки возможностей стандартных средств Excel, можно использовать надстройки – процедуры и функции, написанные на встроенном в приложения Microsoft Office, языке программирования. Надстройки легко интегрируются в приложения и расширяют их стандартные возможности.

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

Надстройка позволяет:

1. Быстро создавать список исходных рабочих книг для консолидации;

2. Гибко настраивать листы, содержащие исходные данные, по их видимости, номерам, именам, наличию определенных значений и так далее;

3. Задавать адреса на итоговом (активном) листе как для одного, так и для нескольких диапазонов ячеек;

4. Выбирать одну из наиболее используемых функций (сумма, произведение, максимум, минимум);

5. Выбирать тип сведения данных (по расположению или по категориям).

Консолидация данных в программе Microsoft Excel

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

Условия для выполнения процедуры консолидации

Естественно, что не все таблицы можно консолидировать в одну, а только те, которые соответствуют определенным условиям:

• столбцы во всех таблицах должны иметь одинаковое название (допускается лишь перестановка столбцов местами);
• не должно быть столбцов или строк с пустыми значениями;
• шаблоны у таблиц должны быть одинаковыми.

Создание консолидированной таблицы

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

1. Открываем отдельный лист для консолидированной таблицы.

Открывается окно настройки консолидации данных.

В поле «Функция» требуется установить, какое действие с ячейками будет выполняться при совпадении строк и столбцов. Это могут быть следующие действия:

• сумма;
• количество;
• среднее;
• максимум;
• минимум;
• произведение;
• количество чисел;
• смещенное отклонение;
• несмещенное отклонение;
• смещенная дисперсия;
• несмещенная дисперсия.

В большинстве случаев используется функция «Сумма».

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

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

Как видим, после этого диапазон добавляется в список.

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

Если же нужный диапазон размещен в другой книге (файле), то сразу жмем на кнопку «Обзор…», выбираем файл на жестком диске или съемном носителе, а уже потом указанным выше способом выделяем диапазон ячеек в этом файле. Естественно, файл должен быть открыт.

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

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

Когда все настройки выполнены, жмем на кнопку «OK».

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

Теперь содержимое группы доступно для просмотра. Аналогичным способом можно раскрыть и любую другую группу.

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

Мы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 11905 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.