Работа со сводными таблицами Excel

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

Один из самых простых способов реализации в 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 курс Умные и сводные таблицы, пройдя который Вы получите практические навыки в обработке больших массивов данных в том числе с помощью сводных.

Пример построения сводной таблицы
Обновление данных в сводной таблице excel
Made on
Tilda