Одновременная фильтрация нескольких сводных таблиц

При создании сложных отчетов и, особенно, информационных панелей в Microsoft Excel очень часто возникает необходимость одновременно фильтровать сразу несколько сводных таблиц. Давайте посмотрим, как это можно реализовать.

Метод 1: Общий срез для фильтрации сводных данных в одном и том же источнике данных.

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

Чтобы добавить его, выберите любую ячейку в одной из сводок и на вкладке Анализ выбрать команду Вставить фрагмент (Анализ — Вставка среза). В открывшемся окне установите флажки напротив тех столбцов, по которым вы хотите фильтровать данные, и нажмите кнопку OK:

Созданный срез по умолчанию будет фильтровать только тот свод, для которого он был создан. Однако с помощью кнопки Сообщить о соединениях (Сообщить о соединениях) таб Ломтик (Ломтики) мы можем легко добавить в список отфильтрованных таблиц другие сводные таблицы:

Способ 2. Общий срез для фильтрации сводок по разным источникам

Если ваши своды были построены не по одной, а по разным таблицам исходных данных, то вышеописанный метод не подойдет, поскольку в окне Сообщить о соединениях отображаются только те сводки, которые были созданы на основе одного и того же источника.

Однако это ограничение можно легко обойти, если использовать Модель данных (подробно мы обсуждали ее в этой статье). Если мы загрузим наши таблицы в Модель и свяжем их там, то фильтрация будет применяться к обеим таблицам одновременно.

Допустим, у нас есть две таблицы затрат на продажу и транспортировку в качестве входных данных:

Предположим, что перед нами стоит задача построить собственную сводку для каждого из них и затем отфильтровать их одновременно по городам общего разреза.

Мы делаем следующее:

1. Превращение наших оригинальных таблиц в динамические смарт-таблицы с помощью сочетания клавиш Ctrl+T или команды Главная – Форматировать в виде таблицы (Главная страница — Форматировать как таблицу) и дать им имена таблПродажи и tabTransport таб Конструктор (Дизайн).

2. Загрузите обе таблицы по очереди в Модель с помощью кнопки Добавить в модель данных на вкладке Power Pivot.

Невозможно напрямую связать эти таблицы в модели, поскольку Power Pivot поддерживает только связи «один-ко-многим», т. е. требует, чтобы одна из таблиц не имела дубликатов в столбце, на который мы связываем. У нас в обеих таблицах в поле одинаково Город есть повторы. Поэтому нам нужно создать еще одну промежуточную таблицу поиска со списком уникальных названий городов из обеих таблиц. Проще всего это сделать с помощью функции надстройки Power Query, которая встроена в Excel начиная с версии 2016 года (а для Excel 2010-2013 загружается бесплатно с сайта Microsoft).

3. Выделив любую ячейку внутри «умной» таблицы, поочередно загружаем их в Power Query кнопкой Из таблицы/диапазона таб Данные (Данные — из таблицы/диапазона) а затем в окне Power Query выберите Основной команды Закрыть и загрузить – Закрыть и загрузить (Главная страница — Закрыть и загрузить — Закрыть и загрузить в…) и возможность импорта Просто создайте соединение (Только создать соединение):

4. Объединяем обе таблицы в одну командой Данные – Объединить запросы – Добавить (Данные — Объединить запросы — Добавить). Столбцы с одинаковыми именами в заголовке будут подходить друг под другом (как столбец Город), а те, что не совпадают, будут размещены в разных столбцах (но для нас это не важно).

5. Удалить все столбцы, кроме столбца Городщелкнув правой кнопкой мыши по его названию и выбрав команду Удалить другие столбцы (Удалить другие столбцы) а затем удалите все повторяющиеся названия городов, еще раз щелкнув правой кнопкой мыши заголовок столбца и выбрав команду Удалить дубликаты (удалить дубликаты):

6. Созданный список ссылок загружается в модель данных через На главную — Закрыть и загрузить — Закрыть и загрузить (Главная страница — Закрыть и загрузить — Закрыть и загрузить в…) и выберите вариант Просто создайте соединение (Только создать соединение) и самое главное! - поставьте галочку Добавьте эти данные в модель данных (Добавьте эти данные в модель данных):

7. Теперь мы можем, вернувшись в окно Power Pivot (вкладка PowerPivot - кнопка Руководство), переключить на Просмотр диаграммы (Просмотр схемы) и свяжем наши таблицы продаж и транспортных расходов через созданный промежуточный справочник городов (перетаскиванием полей между таблицами):

8. Теперь вы можете создать все необходимые сводные таблицы для созданной модели с помощью кнопки Таблица результатов (Сводная таблица) on Основной (Главная) в окне Power Pivot и, выбрав любую ячейку в любой сводной таблице, на вкладке Анализ кнопка добавить фрагмент Вставить фрагмент (Анализ — Вставка среза) и выберите фрагмент в списке Город в добавленном каталоге:

Теперь, нажав на знакомую кнопку Сообщить о соединениях on Вкладка «Срез» (Срез — отчет о соединениях) мы увидим все наши сводки, поскольку теперь они построены на связанных исходных таблицах. Осталось включить недостающие галочки и нажать на OK – и наш слайсер начнет фильтровать все выбранные сводные таблицы одновременно.