Консолидация нескольких листов в одной сводной таблице
Консолидация данных представляет собой удобный способ объединения данных из нескольких источников в одном отчете. Например, если в каждом из региональных филиалов вашей компании есть сводная таблица расходов, с помощью консолидации данных можно объединить такие значения в корпоративный отчет о расходах. Такой отчет может содержать итоговые и средние данные по продажам, текущим уровням запасов и наиболее популярным продуктам в рамках всей организации.
Примечание: Другой способ консолидации данных — использование Power Query. Дополнительные сведения см. в справке по Power Query для Excel.
Объединение нескольких диапазонов
Для подведения итогов и обобщения результатов по данным в отдельных диапазонах можно консолидировать их в сводной таблице на главном листе. Диапазоны могут находиться в той же книге, что и главный лист, или в других книгах. Консолидированные данные легче обновлять и обобщать на регулярной основе или по мере необходимости.
Итоговый консолидированный отчет сводной таблицы может содержать следующие поля в области Список полей сводной таблицы, добавляемой в сводную таблицу: «Строка», «Столбец» и «Значение». Кроме того, в отчет можно включить до четырех полей фильтра, которые называются «Страница1», «Страница2», «Страница3» и «Страница4».
Настройка исходных данных
Каждый из диапазонов данных следует преобразовать в формат перекрестной таблицы с совпадающими именами строк и столбцов для элементов, которые вы хотите объединить. В выбранные данные не следует включать итоговые строки и итоговые столбцы. В приведенном ниже примере показано четыре диапазона в формате перекрестной таблицы.
Использование полей страницы
При консолидации данных можно использовать поля страницы, содержащие элементы, которые представляют один или несколько исходных диапазонов. Например, при консолидации данных бюджета отдела маркетинга, отдела продаж и производственного отдела поле страницы может содержать отдельный элемент с данными по каждому из этих отделов, а также элемент, содержащий сводные данные. В примере ниже показана сводная таблица, в которой выбраны одно поле страницы и несколько элементов.
Использование именованных диапазонов
Если велика вероятность того, что в следующий раз при консолидации данных исходный диапазон данных изменится (например, изменится число строк), рекомендуется задать имя для каждого из исходных диапазонов на разных листах. Эти имена можно использовать при консолидации диапазонов на главном листе. В случае расширения исходного диапазона перед обновлением сводной таблицы можно обновить диапазон на отдельном листе для указанного имени таким образом, чтобы включить в него новые данные.
Использование трехмерных привязок или команды «Консолидировать»
В Excel также доступны другие способы консолидации данных, которые позволяют работать с данными в разных форматах и макетах. Например, вы можете создавать формулы с объемными ссылками или использовать команду Консолидация (доступную на вкладке Данные в группе Работа с данными).
Консолидация нескольких диапазонов
Для консолидации нескольких диапазонов вы можете воспользоваться мастером сводных таблиц и диаграмм. В нем можно указать, сколько полей страницы будет использоваться: ни одного, одно или несколько.
Консолидация данных без использования полей страницы
Чтобы объединить данные всех диапазонов и создать консолидированный диапазон без полей страницы, сделайте следующее:
- Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
- Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.
- Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.
- В списке Выбрать команды из выберите пункт Все команды.
- Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.
Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.
Консолидация данных с использованием одного поля страницы
Чтобы включить одно поле страницы, содержащее элемент для каждого исходного диапазона, а также элемент для консолидации всех диапазонов, сделайте следующее:
- Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
- Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.
- Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.
- В списке Выбрать команды из выберите пункт Все команды.
- Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.
Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.
Консолидация данных с использованием нескольких полей страницы
Вы можете создать несколько полей страницы и назначить имена элементов каждому из исходных диапазонов. Это позволяет выполнять частичную или полную консолидацию. Например, в одном поле страницы могут консолидироваться данных отдела маркетинга и отдела продаж без данных производственного отдела, а в другом поле — данные всех трех отделов. Чтобы создать консолидацию с использованием нескольких полей страницы, сделайте следующее:
- Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
- Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.
- Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.
- В списке Выбрать команды из выберите пункт Все команды.
- Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.
Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.
- Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 1, выберите каждый из диапазонов, а затем введите уникальное имя в поле Первое поле. Если у вас четыре диапазона, каждый из которых соответствует кварталу финансового года, выберите первый диапазон, введите имя «Кв1», выберите второй диапазон, введите имя «Кв2» и повторите процедуру для диапазонов «Кв3» и «Кв4».
- Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 2, выполните аналогичные действия в поле Первое поле. Затем выберите два диапазона и введите в поле Второе поле одинаковое имя, например «Пг1» и «Пг2». Выберите первый диапазон и введите имя «Пг1», выберите второй диапазон и введите имя «Пг1», выберите третий диапазон и введите имя «Пг2», выберите четвертый диапазон и введите имя «Пг2».
Использование нескольких таблиц для создания сводной таблицы
Сводные таблицы удобно использовать для анализа данных и создания отчетов с ними. А если это реляционные данные (т. е. такие, которые хранятся в отдельных таблицах, но при этом их можно объединить благодаря общим значениям), вы можете создать сводную таблицу.
Чем отличается эта сводная таблица? Обратите внимание, что в списке полей справа отображается не одна таблица, а целый набор таблиц. Каждая из этих таблиц содержит поля, которые можно объединить в одну сводную таблицу для получения различных срезов данных. Не требуются ручное форматирование и подготовка данных. Сразу после импорта данных можно создать сводную таблицу на основе связанных таблиц.
Создание сводной таблицы с использованием нескольких таблиц
Ниже приведены три основных шага для добавления нескольких таблиц в список полей сводной таблицы.
Шаг 1. Импорт связанных таблиц из базы данных
Импортируйте их из реляционной базы данных, например Microsoft SQL Server, Oracle или Access. Вы можете импортировать несколько таблиц одновременно:
Может потребоваться установить дополнительное клиентское программное обеспечение. Обратитесь к администратору базы данных, чтобы уточнить, есть ли такая необходимость.
Шаг 2. Добавление полей в сводную таблицу
Обратите внимание: список полей содержит несколько таблиц.
Это все таблицы, выбранные вами во время импорта. Каждую таблицу можно развернуть и свернуть для просмотра ее полей. Так как таблицы связаны, вы можете создать сводную таблицу, перетянув поля из любой таблицы в область ЗНАЧЕНИЯ, СТРОКИ или СТОЛБЦЫ. Вы можете:
- Перетащите числовые поля в область ЗНАЧЕНИЯ. Например, если используется образец базы данных Adventure Works, вы можете перетащить поле «ОбъемПродаж» из таблицы «ФактПродажиЧерезИнтернет».
- Перетащите поля даты или территории в область СТРОКИ или СТОЛБЦЫ, чтобы проанализировать объем продаж по дате или территории сбыта.
Шаг 3. Создание связей при необходимости
Иногда нужно создать связь между двумя таблицами, прежде чем использовать их в сводной таблице. Если появится сообщение о необходимости такой связи между таблицами, щелкните Создать, чтобы начать работу с ними.
Использование модели данных для создания новой сводной таблицы
Примечание Модели данных не поддерживаются в Excel для Mac.
Реляционные базы данных — это не единственный источник данных, который поддерживает работу с несколькими таблицами в списке полей сводной таблицы. Вы можете использовать таблицы в своей книге или импортировать каналы данных, а затем интегрировать их с другими таблицами данных в книге. Дополнительные сведения см. в статье Импорт нескольких таблиц из других источников данных.
Чтобы все эти несвязанные данные работали вместе, нужно каждую таблицу добавить в модель данных, а затем создать связи между ними с помощью соответствующих значений полей. Дополнительные сведения см. в статьях Добавление данных с листа в модель данных с помощью связанной таблицы,Создание связи между двумя таблицами иСоздание связей в представлении схемы.
После создания модели данных эти данные можно использовать в анализе. Ниже описано, как создать новую сводную таблицу или сводную диаграмму с помощью модели данных в книге.
- Щелкните любую ячейку на листе.
- Выберите Вставка и щелкните стрелку вниз элемента Сводная таблица.
Выберите Из внешнего источника данных.
См. также
- Создание модели данных в Excel
- Получение данных с помощью надстройки Power Pivot
- Упорядочение полей сводной таблицы с помощью списка полей
- Создание сводной таблицы для анализа данных на листе
- Создание сводной таблицы для анализа внешних данных
- Создание сводной таблицы, подключенной к наборам данных Power BI
- Изменение диапазона исходных данных для сводной таблицы
- Обновление данных в сводной таблице
- Удаление сводной таблицы
Excel: как создать сводную таблицу из нескольких листов
В следующем пошаговом примере показано, как создать сводную таблицу из нескольких листов в Excel.
Шаг 1: введите данные
Предположим, у нас есть электронная таблица с двумя листами, названными неделя1 и неделя2 :
1 неделя:
Неделя 2:
Предположим, мы хотим создать сводную таблицу, используя данные с обоих листов.
Шаг 2. Объедините данные в один лист
Прежде чем мы сможем создать сводную таблицу, используя оба листа, мы должны объединить все данные в один лист.
Для этого мы можем использовать следующую формулу FILTER :
=FILTER(week2!A2:C11, week2!A2:A11<>"")Мы можем ввести эту формулу в ячейку A12 листа week1 :
Эта формула указывает Excel вернуть все строки из листа week2 , где значение в диапазоне A2: A11 этого листа не является пустым.
Все строки из листов неделя1 и неделя2 теперь объединены в один лист.
Шаг 3: Создайте сводную таблицу
Чтобы создать сводную таблицу, щелкните вкладку « Вставка », затем щелкните « Сводная таблица» в группе « Таблицы ».
В появившемся новом окне введите следующую информацию и нажмите OK :
На панели « Поля сводной таблицы », которая появляется в правой части экрана, перетащите « Магазин » в поле «Строки», перетащите « Продукт » в поле «Столбцы» и перетащите « Продажи » в поле «Значения».
Автоматически будет создана следующая сводная таблица:
Окончательная сводная таблица включает данные как из листов неделя 1, так и за неделю 2.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:
Как создать сводную таблицу из нескольких листов в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ruИспользование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071