Расширенный анализ данных - консолидация данных

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

Вам нужно знать, как вы можете настроить сводный рабочий лист, который консолидирует данные из источников, которые вы настроили, когда захотите. В Excel вы можете легко выполнить эту задачу за несколько шагов с помощьюData Tool – Consolidate.

Подготовка данных к консолидации

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

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

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

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

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

Консолидация данных в одной книге

Если у вас есть все данные, которые вам нужно объединить в одной книге, действуйте следующим образом:

Step 1 - Убедитесь, что данные по каждому региону находятся на отдельном листе.

Step 2 - Добавьте новый рабочий лист и назовите его Сводка.

Step 3 - Щелкните сводную таблицу.

Step 4 - Щелкните ячейку, в которую вы хотите поместить итоговые результаты.

Step 5 - Щелкните значок DATA вкладка на ленте.

Step 6 - Щелкните значок Consolidate кнопка в Data Tools группа.

В Consolidate появится диалоговое окно.

Step 7 - Выбрать Sum из раскрывающегося списка под Function.

Step 8 - Выберите данные из каждого рабочего листа следующим образом.

Щелкните значок в поле под ссылкой.
Выберите рабочий лист - Восток.
Выберите диапазон данных.
Снова щелкните значок в поле под ссылкой.

Выбранный диапазон отображается в поле «Ссылка» -

Step 9 - Щелкните значок Addкнопку справа от поля. Выбранный диапазон данных появится в поле подAll References.

Step 10- Повторите шаги 1–5 для остальных рабочих листов данных - север, юг и запад. Диалоговое окно «Объединить» выглядит следующим образом.

Вы можете видеть, что диапазоны данных отображаются на листе в алфавитном порядке в поле под All references.

Step 11 - Установите флажки Top row и Left column под Use labels in. Щелкните ОК.

Ваши данные суммируются по продуктам для регионов - Восток, Север, Юг и Запад.

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

Автоматическая консолидация данных

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

Step 1 - Установите флажок - Create links to source data в диалоговом окне "Объединить" и нажмите "ОК".

Ваши итоговые результаты отображаются с контуром следующим образом -

Вы заметите, что новый столбец вставлен справа от столбца с именем Product.

Step 2- Щелкните значок + на контуре в строке, содержащей значение продукта с именем Soap. Вы можете видеть, что новый столбец содержит консолидированное значение для каждого набора значений продуктов по регионам.

Консолидация данных из разных книг

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

Step 1 - Откройте книги, содержащие данные, скажем, книги - Восточные продажи, Северные продажи, Южные продажи и Западные продажи.

Step 2 - Откройте новую книгу.

Step 3 - На новом листе щелкните ячейку, в которой должна появиться сводка.

Step 4 - Щелкните вкладку ДАННЫЕ на ленте.

Step 5 - Нажмите «Консолидировать» в окне «Инструменты для данных».

А Consolidateпоявится диалоговое окно. В диалоговом окне "Консолидировать" -