Как создать сводную консолидированную таблицу в excel

Консолидация нескольких листов в одной сводной таблице

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

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

Итоговый консолидированный отчет сводной таблицы может содержать следующие поля в области Список полей сводной таблицы, добавляемой в сводную таблицу: "Строка", "Столбец" и "Значение". Кроме того, в отчет можно включить до четырех полей фильтра, которые называются "Страница1", "Страница2", "Страница3" и "Страница4".

Настройка исходных данных

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

Поля страницы при консолидации данных

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

Использование именованных диапазонов

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

Другие способы консолидации данных

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

Консолидация нескольких диапазонов

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

Консолидация данных без использования полей страницы

Чтобы объединить данные всех диапазонов и создать консолидированный диапазон без полей страницы, сделайте следующее:

Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:

Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.

В списке Выбрать команды из выберите пункт Все команды.

Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.

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

Щелкните значок мастера на панели быстрого доступа.

На странице Шаг 1 мастера выберите параметр в нескольких диапазонах консолидации, а затем нажмите кнопку Далее.

На странице Шаг 2а выберите параметр Создать поля страницы, а затем нажмите кнопку Далее.

На странице Шаг 2б сделайте следующее:

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

Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.

В разделе Во-первых, укажите количество полей страницы сводной таблицы введите 0, а затем нажмите кнопку Далее.

На странице Шаг 3 укажите, следует ли добавлять сводную таблицу на новый или же на существующий лист, и нажмите кнопку Готово.

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

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

Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:

Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.

В списке Выбрать команды из выберите пункт Все команды.

Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.

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

На странице Шаг 1 мастера выберите параметр в нескольких диапазонах консолидации, а затем нажмите кнопку Далее.

На странице Шаг 2а выберите параметр Создать одно поле страницы, а затем нажмите кнопку Далее.

На странице Шаг 2б сделайте следующее:

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

Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.

Нажмите кнопку Далее.

На странице Шаг 3 укажите, следует ли добавлять сводную таблицу на новый или же на существующий лист, и нажмите кнопку Готово.

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

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

Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:

Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.

В списке Выбрать команды из выберите пункт Все команды.

Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.

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

На странице Шаг 1 мастера выберите параметр в нескольких диапазонах консолидации, а затем нажмите кнопку Далее.

На странице Шаг 2а выберите параметр Создать поля страницы, а затем нажмите кнопку Далее.

На странице Шаг 2б сделайте следующее:

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

Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.

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

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

Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 1, выберите каждый из диапазонов, а затем введите уникальное имя в поле Первое поле. Если у вас четыре диапазона, каждый из которых соответствует кварталу финансового года, выберите первый диапазон, введите имя "Кв1", выберите второй диапазон, введите имя "Кв2" и повторите процедуру для диапазонов "Кв3" и "Кв4".

Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 2, выполните аналогичные действия в поле Первое поле. Затем выберите два диапазона и введите в поле Второе поле одинаковое имя, например "Пг1" и "Пг2". Выберите первый диапазон и введите имя "Пг1", выберите второй диапазон и введите имя "Пг1", выберите третий диапазон и введите имя "Пг2", выберите четвертый диапазон и введите имя "Пг2".

Нажмите кнопку Далее.

На странице Шаг 3 укажите, следует ли добавлять сводную таблицу на новый или же на существующий лист, и нажмите кнопку Готово.

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

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

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

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

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

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

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

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

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

Добавление нового листа в Microsoft Excel

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

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

    Настройки консолидации в Microsoft Excel

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

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

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

    Выбор функции для консолидации в Microsoft Excel

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

    Переход к выбору диапазона для консолидации в Microsoft Excel

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

    Выбор диапазона для консолидации в Microsoft Excel

    Добавление диапазона в Microsoft Excel

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

    Диапазон добавлен в Microsoft Excel

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

    Все диапазоны добавлены для консолидации в Microsoft Excel

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

    Выбор файла для консолидации в Microsoft Excel

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

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

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

    Установка настроек для консолидации в Microsoft Excel

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

    Просмотр содержимого группы консолидированной таблицы в Microsoft Excel

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

    Содержимое группы группы консолидированной таблицы в Microsoft Excel

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

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

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

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

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

    Ссылка на основную публикацию