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

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:

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

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

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

1. Создание табличных массивов

Первым делом все таблицы с исходными данными приводим в формат «умной таблицы». Делается – это на вкладке «Вставка», потом выбираем «Таблица» или можно использовать Ctrl+T:

Создание умной таблицы

2. Создание подключения через Microsoft Query

На этом этапе нужно сохранить файл. Затем нужно открыть лист, но котором мы хотим видеть сводную таблицу из нескольких листов. Там заходим в «Данные» и нажимаем «Из других источников», а там «из Microsoft Query»:

Создание подключения Microsoft Query

В окне выбора источника данных выбираем «Excel files» и убираем галку «Использовать мастер запросов» и нажимаем «OK»:

Выбор источника данных Excel files

Выбираем расположение нашей же книги и нажимаем «OK».

В окне «Добавление таблицы» нажимаем «Параметры» и там отмечаем флажок в поле «Системные таблицы», далее выбираем первую таблицу на первом листе и нажимаем «Добавить» и «Закрыть»

Настройка запроса для первого листа

3. Настройка SQL запроса

Останется открытым окно Microsoft Query, в нем дважды кликаем по названию каждого столбца таблицы с первого листа:

Создание запроса SQL
Далее нажимаем кнопку «SQL» и копируем все, что там написано:

Копирование запроса SQL

Потом внизу под существующим запросом пишем слово UNION и вставляем ранее скопированный код. В части кода под Union меняем название листа:

Правка кода для таблицы второго листа

Если Вам нужна сводная болеет чем с двух листов, то соответственно повторяете Union, вставку и редактирование название листа несколько раз.

Далее «OK» на сообщение о невозможности представить результат графически отвечаем положительно.

Далее в окне Microsoft Query нажимаем «Файл» и «Вернуть данные в Microsoft Excel».

В окне «Импорт данных» выбираем место куда поместим сводную и отмечаем точкой «Отчет сводной таблицы».

4. Настройка сводной таблицы

Далее, уже идет обычная настройка сводной таблицы. Как настраивать сводную можно почитать в статье «Сводные таблицы в Microsoft Excel»

Все прекрасно обновляется. Если Вы допишите на любой из листов новые строки, то просто обновите сводную таблицу. Как обновляется сводная таблица можно почитать в статье «Сводные таблицы в Microsoft Excel»

Ещё у нас есть online курс Умные и сводные таблицы, пройдя который Вы получите практические навыки в обработке больших массивов данных в том числе с помощью сводных.

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

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

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

Объединение нескольких диапазонов

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

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

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

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

Использование полей страницы

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

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

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

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

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

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

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

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

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

Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
1. Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.
2. Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.
3. В списке Выбрать команды из выберите пункт Все команды.
4. Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.
Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.

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

Чтобы включить одно поле страницы, содержащее элемент для каждого исходного диапазона, а также элемент для консолидации всех диапазонов, сделайте следующее:
1. Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
  1. Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.
  2. Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.
  3. В списке Выбрать команды из выберите пункт Все команды.
  4. Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.
  Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.
  
  Консолидация данных с использованием нескольких полей страницы
  
  Вы можете создать несколько полей страницы и назначить имена элементов каждому из исходных диапазонов. Это позволяет выполнять частичную или полную консолидацию. Например, в одном поле страницы могут консолидироваться данных отдела маркетинга и отдела продаж без данных производственного отдела, а в другом поле — данные всех трех отделов. Чтобы создать консолидацию с использованием нескольких полей страницы, сделайте следующее:
  1. Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
    1. Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.
    2. Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.
    3. В списке Выбрать команды из выберите пункт Все команды.
    4. Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.
    Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.
    - Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 1, выберите каждый из диапазонов, а затем введите уникальное имя в поле Первое поле. Если у вас четыре диапазона, каждый из которых соответствует кварталу финансового года, выберите первый диапазон, введите имя «Кв1», выберите второй диапазон, введите имя «Кв2» и повторите процедуру для диапазонов «Кв3» и «Кв4».
    - Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 2, выполните аналогичные действия в поле Первое поле. Затем выберите два диапазона и введите в поле Второе поле одинаковое имя, например «Пг1» и «Пг2». Выберите первый диапазон и введите имя «Пг1», выберите второй диапазон и введите имя «Пг1», выберите третий диапазон и введите имя «Пг2», выберите четвертый диапазон и введите имя «Пг2».
    покупка
    
    Как объединить несколько листов в сводную таблицу в Excel?
    
    Предположим, у вас есть несколько листов с идентичной структурой столбцов, как показано на скриншоте ниже. Теперь вам нужно создать сводную таблицу из указанных данных этих листов, как вы можете сделать для этого? Эта статья покажет вам способ, как это сделать в деталях.
    
    Combine multiple worksheets/workbooks into one worksheet / workbook:
    
    Combine multiple worksheets or workbooks into one single worksheet or workbook may be a huge task in your daily work. But, if you have Kutools for Excel, its powerful utility – Combine can help you quickly combine multiple worksheets, workbooks into one worksheet or workbook
    
    Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
    
    Объединение нескольких листов в сводную таблицу
    
    Чтобы объединить данные нескольких рабочих листов в сводную таблицу, сделайте следующее.
    
    1. Нажмите Настройка панели быстрого доступа > Дополнительные команды как показано ниже.
    
    2. в Параметры Excel диалоговое окно, вам необходимо:
    
    2.1 Выбрать Все команды из Выберите команды из раскрывающийся список;
    
    2.2 Выбрать Мастер сводных таблиц и диаграмм в поле списка команд;
    
    2.3 Щелкните значок Добавить кнопка;
    
    2.4 Щелкните значок OK кнопка. Смотрите скриншот:
    
    3. Затем Мастер сводных таблиц и диаграмм кнопка отображается на Панель быстрого доступа. Нажмите кнопку, чтобы открыть Мастер сводных таблиц и диаграмм. В мастере выберите Несколько диапазонов консолидации вариант и PivotTable вариант, а затем щелкните Следующая кнопка. Смотрите скриншот:
    
    4. Во втором мастере выберите Я создам поля страницы и нажмите Следующая кнопку.
    
    5. В третьем мастере щелкните значок кнопку, чтобы выбрать данные из первого рабочего листа, который вы объедините в сводную таблицу, и нажмите кнопку Добавить кнопка. Затем повторите этот шаг, чтобы добавить данные других листов в Все диапазоны коробка. Выберите 0 вариант в Сколько полей страницы вы хотите раздел, а затем щелкните Следующая кнопку.
    
    Внимание: Вы можете выбрать 1, 2 или другие варианты в разделе Сколько полей страницы вы хотите, сколько вам нужно. И введите другое имя в поле Поле для каждого диапазона.
    
    6. В последнем мастере выберите, где вы хотите разместить сводную таблицу (здесь я выбираю Новый рабочий лист вариант), а затем щелкните Завершить кнопку.
    
    Затем создается сводная таблица с указанными данными рабочих листов. Вы можете расположить его в полях сводной таблицы по своему усмотрению.
    
    Статьи по теме:
    - Как создать сводную таблицу из текстового файла в Excel?
    - Как отфильтровать сводную таблицу на основе определенного значения ячейки в Excel?
    - Как привязать фильтр сводной таблицы к определенной ячейке в Excel?
    Похожие публикации: