Консолидация нескольких листов в одной сводной таблице
Консолидация данных представляет собой удобный способ объединения данных из нескольких источников в одном отчете. Например, если в каждом из региональных филиалов вашей компании есть сводная таблица расходов, с помощью консолидации данных можно объединить такие значения в корпоративный отчет о расходах. Такой отчет может содержать итоговые и средние данные по продажам, текущим уровням запасов и наиболее популярным продуктам в рамках всей организации.
Для подведения итогов и обобщения результатов по данным в отдельных диапазонах можно консолидировать их в сводной таблице на главном листе. Диапазоны могут находиться в той же книге, что и главный лист, или в других книгах. Консолидированные данные легче обновлять и обобщать на регулярной основе или по мере необходимости.
Итоговый консолидированный отчет сводной таблицы может содержать следующие поля в области Список полей сводной таблицы, добавляемой в сводную таблицу: "Строка", "Столбец" и "Значение". Кроме того, в отчет можно включить до четырех полей фильтра, которые называются "Страница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 укажите, следует ли добавлять сводную таблицу на новый или же на существующий лист, и нажмите кнопку Готово.
Как объединить две сводные таблицы в одну в excel 2007
Типичная задача — имеем несколько однотипных таблиц на разных листах рабочей книги. Хотим, чтобы при внесении данных в любую из этих таблиц — данные добавлялись в одну общую таблицу, расположенную на отдельном листе.
Инструкция
Устанавливаем себе надстройку ЁXCEL . Читаем справку.
Приступаем к решению. Переходим во вкладку ЁXCEL главного меню, нажимаем кнопку "Таблицы", в выпавшем списке выбираем команду "Объединить таблицы":
В открывшемся диалоговом окне выделяем листы с таблицами, которые необходимо объединить и нажимаем "ОК":
Программа сформирует запрос — объединит таблицы и выведет информационное сообщение:
Переходим на итоговый лист (тот где хотим вывести общую таблицу), устанавливаем курсор в ячейку "A1". Переходим в главном меню во вкладку "Данные" в разделе "Получение внешних данных" нажимаем кнопку "Существующие подключения":
В открывшемся диалоговом окне выбираем "Подключения в этой книге" — "Запрос из Excel Files" и нажимаем "Открыть":
В открывшемся диалоговом окне устанавливаем переключатели в положения "Таблица" и "Имеющийся лист", нажимаем "ОК":
В активном листе будет создана таблица, которая будет объединять таблицы, расположенные на указанных нами листах:
Теперь все таблицы связаны. Добавьте новые строчки в любую из объединенных таблиц. Перейдите во вкладку "Данные" и нажмите кнопку "Обновить все":
В итоговой таблице появятся строчки, добавленные в выбранный вами лист.
Чтобы обновление итоговой таблицы происходило автоматически вставьте в модуль каждого листа (кроме Итогового) следующий код (Как вставлять макросы?):
Видео-пример
Чтобы запрос работал не зависимо от того в какой папке лежит файл вставьте в модуль "ЭтаКнига" следующий код:
Данный макрос при открытии книги будет определять текущий путь к файлу и менять путь к файлу в запросе.
| Файл | Описание | Размер файла: | Скачивания |
|---|---|---|---|
| Пример | 21 Кб | 2254 |
Чтобы оценить всю прелесть — выньте файл из архива и при загрузке файла включите макросы.
Возможные ошибки при использовании этого метода: