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

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

У нас есть две исходные таблицы с фактическими и плановыми данными по сделкам агентства недвижимости. Таблицы имеют разные структуры:

  • таблица с фактическими данными разбита по месяцам и отделам
  • таблица с планами разбита только по отделам (план на год)

Задача: сравнить факт и бюджет по отделам и вывести процент выполнения плана.

По умолчанию сводную таблицу можно создать только из одной исходной таблицы.

 

 

Чтобы включить в сводную таблицу данные из нескольких таблиц, можно, например, собирать данные из всех таблиц при помощи функций СУММЕСЛИМН, ВПР и т.д. Но это не всегда удобно и может значительно “подвесить” excel.

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

Если кого-то вдруг напугали незнакомые слова “модель данных”, сразу скажу — Power Pivot мы использовать не будем.

 

Итак, две исходные таблицы представлены на картинке. Обратите внимание, что обе таблицы представлены в виде “умных” таблиц. Таблицам присвоены имена “Факт” и “Бюджет”.

 

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

 

Нужно сделать сводную таблицу со следующими столбцами: Отдел, Фактическая сумма сделок, Плановая сумма сделок, Процент выполнения плана.

 

Шаг 1. Создаем сводную таблицу из первой таблицы (Факт)

 

Встаем на любую ячейку первой таблицы (всю таблицу можно не выделять, т.к.это “умная” таблица) и переходим в меню ВставкаСводная таблица.

 

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

 

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

 

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

 

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

 

Шаг 2. Добавляем вторую таблицу в созданную сводную

 

Чтобы добавить вторую таблицу (Бюджет) в уже созданную сводную таблицу, ничего делать не нужно. Достаточно перейти на листе созданной сводной таблицы в окне Поля сводной таблицы на вкладку Все (по умолчанию открыта вкладка Активная).

Как видите, обе таблицы уже находятся в модели данных.

 

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

Шаг 3. Создадим общую сводную таблицу из двух таблиц

 

Создаем сводную таблицы как обычно. 

  1. в блок Строки перетягиваем поле Отдел из таблицы Бюджет. Здесь важно, чтобы поле отдел выбрать из таблицы Бюджет, а не Факт, т.к. в данном случае именно в таблице Бюджет содержатся уникальные значения отделов.
  2. в блок Значения — поле Фактическая сумма сделок из таблицы Факт и поле План годовой из таблицы План.

 

Сводная таблица из двух таблиц готова.

 

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

 

Осталось добавить столбец, в котором посчитаем выполнение бюджетного плана. 

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

 

Шаг 4. Создадим меру для расчета выполнения планового бюджета

 

В окне Поля сводной таблицы щелкнем правой кнопкой мыши на таблице Бюджет и выберем Добавить меру

 

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

 

В окне Мера зададим имя меры (Выполнение) и далее, установив курсор в поле Формула, введем значок верхнего апострофа

 

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

 

Появится выпадающий список столбцов, из которых выберем нужный нам (сумма по полю Факт). Далее напишем значок деления и также через апостроф выберем поле План.

 

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

 

 

Добавим созданную меру Выполнение в поле Значения и изменим для него формат на Процентный.

 

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

 

В этой статье мы узнали, как создать сводную таблицу из нескольких таблиц Excel и посчитали выполнение планового бюджета. При этом мы не обошлись без использования Power Pivot.

 

Статья на моем канале Яндекс.Дзен

Сообщество ВКонтакте

Оставьте комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *