Сводная таблица из нескольких листов «Excel».
Очень часто при формировании отчётов (аналитики) основанных на больших объемах данных приходиться сводить в одну таблицу информацию с нескольких листов книги «Excel» .
В идеале для сбора и аналитики информации с разных листов «Эксель» желательно сделать сводную таблицу, которая будет брать данные из нескольких источников (листов книги).
Такую таблицу можно выполнить двумя разными способами.
- Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.
- Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .
Рассмотрим первый способ.
Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.
Шаг первый.
Необходимо добавить в ленту инструментов кнопку мастера создания сводных таблиц и диаграмм.
Для этого следует кликнуть правой кнопкой мыши по ленте (панели инструментов) и выбрать из выпадающего меню пункт «Настройка ленты»
либо войти во вкладку
«Файл» => «Параметры» => «Настройка ленты».
Далее в настройках ленты из выпадающего списка под надписью «Выбрать команды» выбрать пункт «Все команды». В окне ниже отобразятся все возможные команды, которые можно разместить на панели, в алфавитном порядке.
Из списка выбираем «Мастер сводных таблиц и диаграмм»
В правом окне при помощи кнопки «Создать группу» создаем новую группу инструментов. Для группы можно выбрать удобное для Вас наименование. Например, «Своя группа». Можно выбрать на какой вкладке будет создана группа. В своем примере я выбрал вкладку «Главная».
Когда группа создана, выделите ее курсором, выделите курсором «Мастер сводных таблиц и диаграмм» в левом окне и нажмите кнопку «Добавить >>».
После нажмите «Ок».
Теперь на главной вкладке панели инструментов находится инструмент «Мастер сводных таблиц и диаграмм».
Шаг второй. Построение сводной таблицы из нескольких источников данных.
- Кликнуть по кнопке мастера построения сводных таблиц.
- На первом окне поставить флажок, напротив «в нескольких диапазонах консолидации» и флажок напротив «сводная таблица»
Консолидация диапазонов - Во втором окне выбрать «Создать одно поле страницы»
Поле сводной - В третьем окне добавить все диапазоны, которые Вы хотите консолидировать(соединить в сводной таблице).
Несколько диапазонов - В четвертом окне выбрать лист, на котором будет размещена сводная таблица.
На существующий лист - Нажать кнопку «Готово».
Второй способ.
Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .
Данный способ заключается в использовании запроса надстройки Power Query.
О данной надстройке рассказывалось в статье: «Power Query» в «Excel» — что это?
Создание запроса Power Query для сведения нескольких страниц книги в одну таблицу.
Шаг первый.
Нужно создать два запроса, каждый из которых будет брать информацию из отдельной таблицы.
Шаг два.
Для этого во вкладке Power Query нужно нажать кнопку «Из таблицы» и указать в появившемся окне диапазон – источник данных. После чего нажать «Ок».
Шаг три.
Когда создан второй запрос, нужно во вкладке Power Query кликнуть по кнопке «Слияние запросов» и настроит в появившемся окне вид получившейся общей таблицы.
Шаг четыре.
Когда вид настроен, нужно нажать кнопку «Закрыть и загрузить.»
Надстройка Power Query соберет данные с двух листов и соединит их в одной таблице.