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

Как сделать сводную таблицу из нескольких листов/диапазонов Excel
На чтение
9 мин.
Просмотров
29
Дата обновления
06.11.2024

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

В идеале для сбора и анализа информации из разных листов Excel желательно создать сводную таблицу, которая берет данные из разных источников (книжные листы).

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

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

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

Первый шаг.

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

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

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

или откройте вкладку

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

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

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

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

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

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

Затем нажмите «ОК».

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

Мастер сводной панели инструментовМастер сводной панели инструментов

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

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

    Консолидация ассортиментаКонсолидация ассортимента

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

    Сводное полеСводное поле

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

    Несколько интерваловНесколько интервалов

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

    К существующему листуК существующему листу

  • Щелкните кнопку «Готово».

Второй способ.

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

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

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

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

Первый шаг.

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

Шаг второй.

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

Power Query из таблицыPower Query из таблицы

Третья фаза.

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

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

Шаг четвертый.

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

Закройте и загрузитеЗакройте и загрузите

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

0 Комментариев
Комментариев на модерации: 0
Оставьте комментарий