У нас есть две исходные таблицы с фактическими и бюджетными данными по сделкам агентства недвижимости. Таблицы имеют разные структуры:
- таблица с фактическими данными разбита по месяцам и отделам
- таблица с плановым бюджетом разбита только по отделам (план на год)
Задача: сравнить факт и план по отделам и вывести процент выполнения плана.
Сообщество ВКонтакте
По умолчанию сводную таблицу можно создать только из одной исходной таблицы.
Чтобы включить в сводную таблицу данные из нескольких таблиц, можно, например, собирать данные из всех таблиц при помощи функций СУММЕСЛИМН, ВПР и т.д. Но это не всегда удобно и может значительно “подвесить” excel.
Есть более профессиональный способ собрать данные в сводную таблицу из нескольких исходных - при помощи модели данных.
Если кого-то вдруг напугали незнакомые слова “модель данных”, сразу скажу - Power Pivot мы использовать не будем.
Итак, две исходные таблицы представлены на картинке. Обратите внимание, что обе таблицы представлены в виде “умных” таблиц. Таблицам присвоены имена “Факт” и “Бюджет”.
Нужно сделать сводную таблицу со следующими столбцами: Отдел, Фактическая сумма сделок, Плановая сумма сделок, Процент выполнения плана.
Шаг 1. Создаем сводную таблицу из первой таблицы (Факт)
Встаем на любую ячейку первой таблицы (всю таблицу можно не выделять, т.к.это “умная” таблица) и переходим в меню Вставка - Сводная таблица.
Далее выбираем переключатель Новый лист и - внимание! - обязательно устанавливаем галочку Добавить эти данные в модель данных.
На новом листе создается пустая заготовка под сводную таблицу. Но мы пока ее не трогаем.
Шаг 2. Добавляем вторую таблицу в созданную сводную
Чтобы добавить вторую таблицу (Бюджет) в уже созданную сводную таблицу, ничего делать не нужно. Достаточно перейти на листе созданной сводной таблицы в окне Поля сводной таблицы на вкладку Все (по умолчанию открыта вкладка Активная).
Как видите, обе таблицы уже находятся в модели данных.
Шаг 3. Создадим общую сводную таблицу из двух таблиц
Создаем сводную таблицы как обычно.
- в блок Строки перетягиваем поле Отдел из таблицы Бюджет. Здесь важно, чтобы поле Отдел выбрать из таблицы Бюджет, а не Факт, т.к. в данном случае именно в таблице Бюджет содержатся уникальные значения отделов.
- в блок Значения - поле Фактическая сумма сделок из таблицы Факт и поле План годовой из таблицы План.
Сводная таблица из двух таблиц готова.
Осталось добавить столбец, в котором посчитаем выполнение бюджетного плана.
Поскольку мы использовали модель данных для создания сводной таблицы, как обычно создать вычисляемое поле в данном случае не получится. Здесь нужно создать меру.
Шаг 4. Создадим меру для расчета выполнения планового бюджета
В окне Поля сводной таблицы щелкнем правой кнопкой мыши на таблице Бюджет и выберем Добавить меру.
В окне Мера зададим имя меры (Выполнение) и далее, установив курсор в поле Формула, введем значок верхнего апострофа ‘
Появится выпадающий список столбцов, из которых выберем нужный нам (сумма по полю Факт). Далее напишем значок деления и также через апостроф выберем поле План.
Добавим созданную меру Выполнение в поле Значения и изменим для него формат на Процентный.
Вот теперь мы достигли своей цели - создали сводную таблицу из нескольких таблиц Excel и посчитали выполнение планового бюджета. При этом мы не обошлись без использования Power Pivot.