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