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

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

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

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

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

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

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



2. На открывшемся листе отмечаем ячейку, которая будет являться верхней левой ячейкой новой таблицы.
3. Находясь во вкладке «Данные» кликаем по кнопке «Консолидация» , которая расположена на ленте в блоке инструментов «Работа с данными» .



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

   

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

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

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



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



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



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

   

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

   

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

   

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



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

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

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



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

   

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

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

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

Пример использования:

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

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

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

Консолидация данных таблиц:

После того, как мы поймем использование инструмента консолидации, мы будем использовать приведенный выше пример, чтобы показать шаг за шагом, как консолидировать электронные таблицы. См. Ниже:

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

2 - Откройте новую таблицу и перейдите на вкладку «Данные», выбрав опцию «Из других источников» и снова выберите вариант «Microsoft Query».

3 - После выбора опции «Microsoft Query» откроется новое окно, в котором вы должны выбрать опцию «Файлы Excel» и нажмите «ОК».

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

Выберите первый рабочий лист и нажмите «ОК». На следующем шаге добавьте нужные таблицы в поле «Столбцы в запросе» и продолжайте, пока не закончите.

6 - Когда вы нажмете «Готово», обратите внимание, что первый рабочий лист уже консолидирован. Повторите шаги от 2 до 4, чтобы добавить оставшиеся листы, которые нужно консолидировать. В конце процедуры у нас будут все рабочие листы, сгруппированные в один рабочий лист, как показано ниже:

Таким образом, каждое изменение, сделанное на любом из трех рабочих листов, также будет изменено в электронной таблице, которую мы консолидируем. Как раз в этом случае, перейдите на вкладку «Данные» и нажмите кнопку «Обновить все».

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

У вас были какие-то сомнения по поводу этого контента? Оставьте комментарий ниже, чтобы мы могли вам помочь!

Консолидация - это объединение данных из одной или нескольких областей данных и вывод их в виде таблицы в итоговом листе.

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

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

Существует два основных способа консолидации данных.

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

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

Чтобы консолидировать данные, следует воспользоваться командой Консолидация (вкладка Данные , группа Работа с данными ).

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

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

На вкладке Данные в группе Работа с данными выберите команду Консолидация .

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

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

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

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

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

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

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

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

Консолидация по расположению. Консолидация по расположению используется, если данные исходных областей расположены в одном и том же порядке и имеют одни и те же заголовки. Используется этот способ для консолидации данных листов, например бюджетов отделов предприятия, созданных на основе единого шаблона. Рассмотрим пример учета метизов, поступавших в три разных магазина в первом квартале (рис. 6.54). Для сравнения учетные данные, расположенные на разных листах (Январь, Февраль, Март) рабочей книги, на рисунке изображены рядом, что позволяет заметить, что взаимное расположение данных на каждом из трех листов полностью совпадает и по строкам, и по столбцам.

Рис. 6.54. Данные учета

Для консолидации по расположению пользователю следует заранее создать область назначения, где будет строка с заголовками столбцов (Marl, Маг2 и МагЗ) и строка с названиями строк (болты, гайки, шайбы и шурупы). Разумеется, расположение этих заголовков и названий должно соответствовать их расположению в исходных таблицах. После этого необходимо выделить курсором верхнюю левую ячейку, находящуюся на пересечении столбца Marl и строки болты, и дать команду Данные/Консолидация... . Затем в диалоговом окне Консолидация, вызванном этой командой, следует уточнить функцию, с помощью которой должны обрабатываться консолидируемые данные, и ввести в поле Список диапазонов ссылки на области-источники.

Рис. 6.55. Диалоговое окно Консолидация

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

Консолидация по категориям. Консолидация по категориям применяется, если данные исходных областей не упорядочены, но имеют одни и те же заголовки. Используется этот способ для консолидации данных списков, имеющих разную структуру, но одинаковые заголовки. В этом случае табличный процессор MS Excel в процессе обработки данных присваивает им имена, используя при этом заголовки столбцов и названия строк. При этом способе консолидации первый выделенный столбец определяет поле группировки данных. Поясним это примером. Пусть на два склада (№ 1 и № 2), принадлежащих одной фирме, в разное время поступали товары от разных поставщиков (рис. 6.56). Используя эти данные как области источника, несложно получить различные варианты данных в области назначения.

Рис. 6.56. Сведения о поступлении товаров

Пример 10. Предположим, что нам необходимо определить суммарную стоимость каждого из наименований всех товаров, поступивших в течение полугодия на оба склада от всех поставщиков. Для решения этой задачи следует группировать данные по столбцу Наименование товара. В этом случае пользователь должен сообщить программе необходимые уточнения, указав их в диалоговом окне Консолидация. Это окно приведено на рис. 6.57, а результаты консолидации - на рис. 6.58. Нетрудно заметить, что в этом случае программе необходимо указать, какие элементы оформления списка следует использовать в качестве имен данных, так как в этом случае имена используются в качестве аргументов формул. Следует обратить внимание на то,

Рис. 6.58. Результаты консолидации для примера 10

что диапазоны, включенные в список консолидации, начинаются со столбца Наименование товара.

Пример 11. Необходимо оценить суммарную стоимость каждого наименования товара, поступившего на оба склада от разных поставщиков. В этом случае группировать данные следует по столбцу Поставщик, т. е. включать в диапазон консолидации данные, начиная со второго столбца - Поставщик. Следует помнить, что перед включением в диапазон консолидации новых данных из него необходимо удалить ссылки, использовавшиеся ранее. Для этого требуется выделить ссылку на диапазон в поле Список диапазонов и нажать кнопку Удалить. Диалоговое окно с указанием сведений, необходимых для консолидации в этом случае, приведено на рис. 6.59.

Результаты консолидации данных в этом случае можно увидеть на рис. 6.60.

Рис. 6.59. Диалоговое окно с параметрами консолидации для примера 11

Рис. 6.60. Результаты консолидации для примера 11

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

Рис. 6.61. Диалоговое окно с параметрами консолидации для примера 12

Рис. 6.62. Результаты консолидации для примера 12

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

Результаты консолидации представлены на рис. 6.62.

Консолидация — объединение, слияние двух или нескольких форм и/или данных. Т.е. если нужно собрать и объединить данные из разных мест в одном. Для чего это может пригодиться? Проанализировать несколько таблиц с разных файлов/листов. Собрать данные в одну таблицу из разных источников. Далее, подробнее, консолидация в Excel.

Всегда есть способ собрать данные с разных листов, не применяя особых инструментов. Предположим, что у нас есть несколько таблиц на разных листах (например с данными по филиалам). Как данные со всех листов и отобразить результат на отдельном листе?Проще всего формулой, ставим «=» и выбираем ячейки или формулы с каждого листа

Можно воспользоваться хитрой (но только если все суммируемые ячейки имеют одинаковый адрес) когда листов много.

Консолидация в Excel. Специальный инструмент

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

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

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

Значения левого столбца — значения сгруппируются по левому столбцу.

В итоге все данные сгруппируются по левому столбцу: