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

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

Очень часто при формировании отчётов (аналитики) основанных на больших объемах данных приходиться сводить в одну таблицу информацию с нескольких листов книги «Excel» .
В идеале для сбора и аналитики информации с разных листов «Эксель» желательно сделать сводную таблицу, которая будет брать данные из нескольких источников (листов книги).

Такую таблицу можно выполнить двумя разными способами.

  1. Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.
  2. Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .
Рассмотрим первый способ.

Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.

Шаг первый.

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

Для этого следует кликнуть правой кнопкой мыши по ленте (панели инструментов) и выбрать из выпадающего меню пункт «Настройка ленты»

Настройка ленты

Настройка ленты

либо войти во вкладку

«Файл» => «Параметры» => «Настройка ленты».

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

Из списка выбираем «Мастер сводных таблиц и диаграмм»

Добавление мастера сводных таблиц

Добавление мастера сводных таблиц

В правом окне при помощи кнопки «Создать группу» создаем новую группу инструментов. Для группы можно выбрать удобное для Вас наименование. Например, «Своя группа». Можно выбрать на какой вкладке будет создана группа. В своем примере я выбрал вкладку «Главная».

Когда группа создана, выделите ее курсором, выделите курсором «Мастер сводных таблиц и диаграмм» в левом окне и нажмите кнопку «Добавить >>».

После нажмите «Ок».

Теперь на главной вкладке панели инструментов находится инструмент «Мастер сводных таблиц и диаграмм».

Мастер сводных на панели

Шаг второй. Построение сводной таблицы из нескольких источников данных.

  • Кликнуть по кнопке мастера построения сводных таблиц.
  • На первом окне поставить флажок, напротив «в нескольких диапазонах консолидации» и флажок напротив «сводная таблица»

Консолидация диапазонов

Консолидация диапазонов
Во втором окне выбрать «Создать одно поле страницы»

Поле сводной

Поле сводной
В третьем окне добавить все диапазоны, которые Вы хотите консолидировать(соединить в сводной таблице).

Несколько диапазонов

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

На существующий лист

На существующий лист

  • Нажать кнопку «Готово».
  • Второй способ.

    Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .

    Данный способ заключается в использовании запроса надстройки Power Query.

    О данной надстройке рассказывалось в статье: «Power Query» в «Excel» — что это?

    Создание запроса Power Query для сведения нескольких страниц книги в одну таблицу.

    Шаг первый.

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

    Шаг два.

    Для этого во вкладке Power Query нужно нажать кнопку «Из таблицы» и указать в появившемся окне диапазон – источник данных. После чего нажать «Ок».

    Power Query из таблицы

    Шаг три.

    Когда создан второй запрос, нужно во вкладке Power Query кликнуть по кнопке «Слияние запросов» и настроит в появившемся окне вид получившейся общей таблицы.

    Слияние запросов

    Шаг четыре.

    Когда вид настроен, нужно нажать кнопку «Закрыть и загрузить.»

    Закрыть и загрузить

    Закрыть и загрузить

    Надстройка Power Query соберет данные с двух листов и соединит их в одной таблице.

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

    Типичная задача при обработке информации полученной из разных источников. Типовое решение — взять и свести все таблицы в одну. Но что делать, когда таблиц много (например, 20), или свести их в одну нет возможности, на листе просто не хватает строк (все таблицы в сумме дают больше 1 100 000 строк)?

    Однако решение существует! И оно не очень сложное.

    Для решения этой задачи нам понадобиться надстройка ЁXCEL .

    Итак, приступим к решению. Мы имеем на входе 3 таблицы, расположенные на разных листах рабочей книги:

    Как построить сводную таблицу по нескольким массивам (листам)?

    Начинаем манипуляции. Переходим в главном меню во вкладку ЁXCEL и нажимаем кнопку "Таблицы", в выпавшем меню выбираем команду "Объединить таблицы":

    kak-postroit-svodnuyu-tablitsu-po-neskolkim-massivam-listam_2.png

    В открывшемся диалоговом окне выделяем листы с таблицами, которые необходимо объединить и нажимаем "ОК":

    kak-postroit-svodnuyu-tablitsu-po-neskolkim-massivam-listam_3.png

    Программа сформирует запрос — объединит таблицы и выведет информационное сообщение:

    Осталось совсем немного. Переходим в главном меню во вкладку "Данные" в разделе "Получение внешних данных" нажимаем кнопку "Существующие подключения":

    kak-postroit-svodnuyu-tablitsu-po-neskolkim-massivam-listam_4.png

    В открывшемся диалоговом окне выбираем "Подключения в этой книге" — "Запрос из Excel Files" и нажимаем "Открыть":

    В открывшемся диалоговом окне устанавливаем переключатели в положения "Отчет сводной таблицы" и "Новый лист", нажимаем "ОК":

    kak-postroit-svodnuyu-tablitsu-po-neskolkim-massivam-listam_5.png

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

    kak-postroit-svodnuyu-tablitsu-po-neskolkim-massivam-listam_6.png

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

    Чтобы обновление сводной таблицы происходило автоматически вставьте в модуль каждого листа содержащего таблицы следующий код ( Как вставлять макросы? ):

    Чтобы запрос работал не зависимо от того в какой папке лежит файл вставьте в модуль "ЭтаКнига" следующий код:

    Данный макрос при открытии книги будет определять текущий путь к файлу и менять путь к файлу в запросе.

    Файлы для скачивания:

    Файл Описание Размер файла: Скачивания
    Пример 27 Кб 1524

    Чтобы оценить всю прелесть — выньте файл из архива и при загрузке файла включите макросы.

    Возможные ошибки при использовании этого метода:

    Ссылка на основную публикацию