Возможно, вам приходилось сталкиваться с различными ситуациями, когда вам приходилось представлять консолидированные данные. Источник данных может быть из одного места или нескольких мест. Другая проблема может заключаться в том, что данные могут время от времени обновляться другими людьми.
Вам нужно знать, как вы можете настроить сводную рабочую таблицу, которая объединяет данные из источников, которые вы настроили, в любое время. В Excel вы можете легко выполнить эту задачу в несколько шагов с помощью инструмента данных — консолидировать .
Подготовка данных для консолидации
Прежде чем приступить к консолидации данных, убедитесь, что между источниками данных существует согласованность. Это означает, что данные расположены следующим образом:
-
Каждый диапазон данных находится на отдельном рабочем листе.
-
Каждый диапазон данных представлен в виде списка с метками в первой строке.
-
Кроме того, вы можете иметь метки для категорий, если это применимо, в первом столбце.
-
Все диапазоны данных имеют одинаковое расположение.
-
Все диапазоны данных содержат аналогичные факты.
-
В каждом диапазоне нет пустых строк или столбцов.
Каждый диапазон данных находится на отдельном рабочем листе.
Каждый диапазон данных представлен в виде списка с метками в первой строке.
Кроме того, вы можете иметь метки для категорий, если это применимо, в первом столбце.
Все диапазоны данных имеют одинаковое расположение.
Все диапазоны данных содержат аналогичные факты.
В каждом диапазоне нет пустых строк или столбцов.
Если источники данных являются внешними, убедитесь, что используется предопределенный макет в форме шаблона Excel.
Предположим, у вас есть данные о продажах различных товаров из каждого региона — Восток, Север, Юг и Запад. Возможно, вам придется консолидировать эти данные и время от времени представлять краткую информацию о продажах. Подготовка включает в себя следующее —
-
Один лист на регион — т.е. четыре листа с именами Восток, Север, Юг и Запад. Они могут быть в одной и той же книге или в разных книгах.
-
Каждый лист имеет одинаковый макет, представляющий детали продукта, количество единиц и количество.
-
Вам необходимо консолидировать данные продукта мудро. Следовательно, убедитесь, что столбец с меткой «Продукт» является первым столбцом и содержит метки «Продукт».
Один лист на регион — т.е. четыре листа с именами Восток, Север, Юг и Запад. Они могут быть в одной и той же книге или в разных книгах.
Каждый лист имеет одинаковый макет, представляющий детали продукта, количество единиц и количество.
Вам необходимо консолидировать данные продукта мудро. Следовательно, убедитесь, что столбец с меткой «Продукт» является первым столбцом и содержит метки «Продукт».
Консолидация данных в одной книге
Если у вас есть все данные, которые необходимо объединить, в одной и той же книге, выполните следующие действия:
Шаг 1 — Убедитесь, что данные каждого региона находятся на отдельной рабочей таблице.
Шаг 2 — Добавьте новый лист и назовите его Summary.
Шаг 3 — Нажмите на сводную таблицу.
Шаг 4 — Нажмите на ячейку, где вы хотите разместить сводные результаты.
Шаг 5 — Перейдите на вкладку ДАННЫЕ на ленте.
Шаг 6 — Нажмите кнопку « Консолидировать» в группе « Инструменты данных ».
Откроется диалоговое окно « Консолидация ».
Шаг 7 — Выберите Sum из выпадающего списка под Function .
Шаг 8 — Выберите данные из каждой таблицы следующим образом.
- Нажмите на значок в поле под ссылкой.
- Выберите рабочий лист — Восток.
- Выберите диапазон данных.
- Снова щелкните значок в поле под ссылкой.
Выбранный диапазон отображается в поле «Справка» —
Шаг 9 — Нажмите кнопку Добавить справа от поля. Выбранный диапазон данных отображается в поле « Все ссылки» .
Шаг 10 — Повторите шаги 1-5 для остальных таблиц данных — Север, Юг и Запад. Диалоговое окно «Консолидация» выглядит следующим образом.
Вы можете видеть, что диапазоны данных отображаются в виде таблицы в алфавитном порядке, в поле « Все ссылки» .
Шаг 11 — Установите флажки Верхний ряд и Левый столбец под Использовать метки в . Нажмите ОК.
Ваши данные суммированы по регионам — Восток, Север, Юг и Запад.
Вы можете повторить шаги, описанные выше, чтобы обновить сводные результаты вручную, когда они вам понадобятся.
Автоматическая консолидация данных
Предположим, вы хотите, чтобы ваш сводный лист обновлялся автоматически при любых изменениях данных. Для этого вам нужно иметь ссылки на исходные данные.
Шаг 1. Установите флажок. Создайте ссылки на исходные данные в диалоговом окне «Консолидация» и нажмите кнопку «ОК».
Ваши итоговые результаты отображаются следующим образом:
Вы заметите, что новый столбец вставляется справа от столбца с именем Product.
Шаг 2 — Нажмите знак «+» на схеме в строке, содержащей значение «Продукт» с именем «Мыло». Вы можете видеть, что новый столбец содержит консолидированное значение для каждого набора значений продукта по регионам.
Консолидация данных из разных рабочих книг
В предыдущем примере все данные, которые необходимо обобщить, находятся в одной и той же книге. Однако вполне вероятно, что данные хранятся отдельно для каждого региона и обновляются в зависимости от региона. В таком случае вы можете объединить данные следующим образом:
Шаг 1 — Откройте рабочие книги, содержащие данные, скажем, рабочие книги — Восток-Продажи, Север-Продажи, Юг-Продажи и Запад-Продажи.
Шаг 2 — Откройте новую рабочую книгу.
Шаг 3 — На новом листе щелкните ячейку, в которой вы хотите, чтобы появилась сводка.
Шаг 4 — Перейдите на вкладку ДАННЫЕ на ленте.
Шаг 5 — Нажмите Консолидация в окне Инструменты данных.
Откроется диалоговое окно « Консолидация ». В диалоговом окне Консолидация —
- Выберите Sum из выпадающего списка в поле под Function.
- Нажмите на значок в поле под ссылкой .
- Выберите рабочую книгу — East-Sales.xlsx.
- Выберите диапазон данных.
- Снова щелкните значок в поле под ссылкой.
- Нажмите кнопку Добавить справа.
Диалоговое окно «Консолидация» выглядит следующим образом:
- Нажмите на значок справа от поля под ссылками.
- Выберите рабочую книгу — North-Sales.xlsx.
- Выберите диапазон данных.
- Снова щелкните значок справа от поля в разделе «Ссылки».
- Нажмите Добавить.
Шаг 6 — Повторите шаги 1–6, чтобы добавить диапазоны данных из рабочих книг — South-Sales.xlsx и West-Sales.xlsx.
Шаг 7 — В разделе « Использовать метки» установите следующие флажки.
- Верхний ряд.
- Левый столбец.
Шаг 8 — Установите флажок Создать ссылки на исходные данные .
Диалоговое окно «Консолидация» выглядит следующим образом:
Ваши данные обобщены в вашей рабочей тетради.