При работе с однотипными данными, размещенными в разных таблицах, листах или даже книгах, для удобства восприятия лучше собрать информацию воедино. В Microsoft Excel с этой задачей можно справиться с помощью специального инструмента, который называется . Он предоставляет возможность собрать разрозненные данные в одну таблицу. Давайте узнаем, как это делается.
Условия для выполнения процедуры консолидации
Естественно, что не все таблицы можно консолидировать в одну, а только те, которые соответствуют определенным условиям:
- столбцы во всех таблицах должны иметь одинаковое название (допускается лишь перестановка столбцов местами);
- не должно быть столбцов или строк с пустыми значениями;
- шаблоны у таблиц должны быть одинаковыми.
Создание консолидированной таблицы
Рассмотрим, как создать консолидированную таблицу на примере трех таблиц, имеющих одинаковый шаблон и структуру данных. Каждая из них расположена на отдельном листе, хотя по такому же алгоритму можно создать консолидированную таблицу из данных расположенных в разных книгах (файлах).
- Открываем отдельный лист для консолидированной таблицы.
- На открывшемся листе отмечаем ячейку, которая будет являться верхней левой ячейкой новой таблицы.
- Находясь во вкладке кликаем по кнопке , которая расположена на ленте в блоке инструментов .
- Открывается окно настройки консолидации данных.
В поле требуется установить, какое действие с ячейками будет выполняться при совпадении строк и столбцов. Это могут быть следующие действия:
- сумма;
- количество;
- среднее;
- максимум;
- минимум;
- произведение;
- количество чисел;
- смещенное отклонение;
- несмещенное отклонение;
- смещенная дисперсия;
- несмещенная дисперсия.
В большинстве случаев используется функция .
- В поле указываем диапазон ячеек одной из первичных таблиц, которые подлежат консолидации. Если этот диапазон находится в этом же файле, но на другом листе, то жмем кнопку, которая расположена справа от поля ввода данных.
- Переходим на тот лист, где расположена таблица, выделяем нужный диапазон. После ввода данных жмем опять на кнопку расположенную справа от поля, куда был внесен адрес ячеек.
- Вернувшись в окно настроек консолидации, чтобы добавить уже выбранные нами ячейки в список диапазонов, жмем на кнопку .
Как видим, после этого диапазон добавляется в список.
Аналогичным образом, добавляем все другие диапазоны, которые будут участвовать в процессе консолидации данных.
Если же нужный диапазон размещен в другой книге (файле), то сразу жмем на кнопку , выбираем файл на жестком диске или съемном носителе, а уже потом указанным выше способом выделяем диапазон ячеек в этом файле. Естественно, файл должен быть открыт.
- Точно так же можно произвести некоторые другие настройки консолидированной таблицы.
Для того, чтобы автоматически добавить название столбцов в шапку, ставим галочку около параметра . Для того, чтобы производилось суммирование данных устанавливаем галочку около параметра . Если вы хотите, чтобы при обновлении данных в первичных таблицах обновлялась также и вся информация в консолидированной таблице, то обязательно следует установить галочку около параметра . Но, в этом случае нужно учесть, что, если вы захотите в исходную таблицу добавить новые строки, то придется снять галочку с данного пункта и пересчитать значения вручную.
Когда все настройки выполнены, жмем на кнопку .
- Консолидированный отчет готов. Как видим, данные его сгруппированы. Чтобы посмотреть информацию внутри каждой группы, кликаем на плюсик слева от таблицы.
Теперь содержимое группы доступно для просмотра. Аналогичным способом можно раскрыть и любую другую группу.
Как видим, консолидация данных в Эксель является очень удобным инструментом, благодаря которому можно собрать воедино информацию расположенную не только в разных таблицах и на разных листах, но даже размещенную в других файлах (книгах). Делается это относительно просто и быстро.
Мы рады, что смогли помочь Вам в решении проблемы.
Помимо этой статьи, на сайте еще 13048 полезных инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам. Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.