Создание сводной таблицы из разных листов

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

В этом прелесть сводных таблиц.

Но вот поворот:

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

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

Итак, сегодня я хотел бы поделиться с вами простыми шагами по использованию нескольких листов в сводной таблице.

Проблема!

Допустим, вы хотите проанализировать данные о продажах вашей компании и извлекаете годовые данные за последние 4 года.

Вот как вы получаете дамп данных в Excel.

Обратите внимание, что данные в 4-х листах разные, но структура абсолютно одинаковая, т.е. одинаковое количество столбцов и заголовков.

Теперь, чтобы проанализировать эти данные, вам необходимо создать единый отчет сводной таблицы из этих нескольких листов.

Некоторые из распространенных методов:

1. Вручную скопируйте и вставьте данные с каждого листа и создайте единый набор данных на новом листе.
2. Используйте код VBA для автоматической консолидации данных из нескольких листов.
3. Или вы можете объединить несколько листов в один, используя опцию консолидации Excel.

Но дело в том, что эти методы требуют кодирования, копирования и вставки или повторяются.

Решение

Здесь я расскажу о новом методе с использованием запросов Microsoft, который является динамичным, надежным и простым.

Поверьте, вам это понравится!

Используя Microsoft Query, вы можете создать сводную таблицу из нескольких листов.

Загрузите этот файл данных, чтобы следовать дальше.

Действия по созданию сводной таблицы из нескольких листов

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

• Сначала выберите все данные на каждом листе и назовите их.
  • Данные за 2005 год названы – 2005 год.
  • Названы данные за 2006 год – 2006 год.
  • Названы данные за 2007 год – 2007 год.
  • Названы данные за 2007 год – 2007 год.

• После этого примените таблицы данных ко всем данным на четырех листах.
  • Выберите любую ячейку в диапазоне данных.
  • Используйте Ctrl + T, чтобы преобразовать данные на каждом листе в таблицу.
  • Убедитесь, что каждый раз отмечен флажок «Моя таблица имеет заголовки».
  • Повторите эту операцию в течение 4 лет (листья).

• Начнем с объединения этих данных на новом листе (сочетание клавиш для добавления нового листа: Shift + F11).
• На вкладке «Данные» нажмите «Из других источников» -> выберите «Из Microsoft Query».

• В поле «Выбрать источник данных»:
  • Нажмите «Файлы Excel», затем нажмите «ОК».
  • Выберите путь к файлу Excel, затем выберите файл и нажмите «ОК».
  • Именованные диапазоны появятся в поле «Мастер запросов – Выбор столбцов».

• В мастере запросов:
  • Перетащите каждый именованный диапазон (с помощью кнопки со стрелкой) в «Столбцы в поле запроса».
  • Нажмите «Далее» — забудьте об ошибке.
  • И нажмите ОК

• Теперь у нас есть все данные в нашем редакторе запросов, осталось только объединить данные из всех именованных диапазонов. Для этого нажмите на маленькую кнопку SQL.

• В поле SQL удалите весь текст, напишите новый запрос и нажмите «ОК».

Выбрать * из 2005 годаUnion allSelect * из 2006 годаUnion allSelect * из 2007 годаUnion allSelect * из 2008 года

• Теперь таблица, которая появляется на экране, содержит данные со всех 4 листов.
• Все, что нам нужно сделать, это перейти на вкладку «Файл» и импортировать эту таблицу в Excel.
• В конце импортируйте данные обратно в Excel в виде сводной таблицы.
  • В меню «Файл» -> нажмите «Вернуть данные в Microsoft Excel».
  • Вы можете видеть, что всего (на 4 листах) у нас 592 записи.
  • Кроме того, если вы добавите дополнительные данные на любой из 4 листов, сводная таблица обновится, как только вы ее обновите.