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

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

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

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

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

Добавление среза к сводной

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

Подключение среза к нескольким сводным таблицам

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

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

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

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

Две исходных таблицы данных

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

Делаем следующее:

1. Превращаем наши исходные таблицы в динамические "умные" с помощью сочетания клавиш Ctrl+T или команды Главная - Форматировать как таблицу (Home - Format as Table) и даём им имена таблПродажи и таблТранспорт на вкладке Конструктор (Design).

2. Загружаем по очереди обе таблицы в Модель с помощью кнопки Добавить в модель данных (Add to Data Model) на вкладке Power Pivot.

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

3. Выделив любую ячейку внутри "умной" таблицы, загружаем их по очереди в Power Query кнопкой Из таблицы / диапазона на вкладке Данные (Data - From table/range) и затем в окне Power Query выбираем на Главной команды Закрыть и загрузить - Закрыть и загрузить в (Home - Close&Load - Close&Load to...) и вариант импорта Только создать подключение (Only create connection):

Только создать подключение

4. Соединяем обе таблицы в одну командой Данные - Объединить запросы - Добавить (Data - Combine queries - Append). Совпадающие по названиям в шапке колонки встанут друг под друга (как столбец Город), а не совпадающие будут разнесены в разные столбцы (но для нас это не важно).

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

Удаляем лишние столбцы и дубликаты

6. Созданный список-справочник выгружаем в Модель Данных через Главная - Закрыть и загрузить - Закрыть и загрузить в (Home - Close&Load - Close&Load to...) и выбираем вариант Только создать подключение (Only create connection) и - самое главное! - включаем флажок Добавить эти данные в модель данных (Add this data to Data Model):

Загружаем справочник в Модель Данных Excel

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

Создаем связи между таблицами

8. Теперь можно создать все требуемые сводные таблицы по созданной модели с помощью кнопки Сводная таблица (Pivot Table) на Главной (Home) вкладке в окне Power Pivot и, выделив любую ячейку в любой сводной, на вкладке Анализ добавить срез кнопкой Вставить срез (Analyze - Insert Slicer) и выбрать для среза в списке поле Город в добавленном справочнике:

Добавляем срез по справочнику

Теперь, нажав на знакомую кнопку Подключения к отчетам на вкладке Срез (Slicer - Report connections) мы увидим все наши сводные, т.к. построены они теперь по связанным исходным таблицам. Останется включить недостающие флажки и нажать на ОК - и наш срез начнёт фильтровать все выбранные сводные таблицы одновременно.

Ссылки по теме


22.04.2021 22:10:00
При формировании списков в Power Query и дальнейшее использование его в Power Pivot есть некий баг или особенность. Например "Хабаровск" и "Хабаровск  " для Power Query будут разными значениями и при удалении повторов он их оставит оба, что в принципе и правильно т.к. есть пробел в конце, а вот при дальнейшем использовании таких названий в Power Pivot они будут одинаковыми для Power Pivot и он ругается, что в таблице есть повторяющиеся значения.
09.09.2021 12:26:28
Проблемы в управлении сводных, построенных по разным источникам данных, начинаются при добавлении второго, третьего, и т.д., срезов. Например, при выборе трех срезов "Территория", "Район", "Улица", которые построены строго по методике, описанной выше в разделе "Способ 2. Общий срез для фильтрации сводных на разных источниках". В итоге, при выборе любого наименования в срезе "Территория", срезы "Район" и "Улица" остаются без изменений. И соответственно, сводные таблицы показывают все те же наборы значений, что были до выбора наименования в срезе "Территория". Например при выборе в срезе "Территория" Алтайский край, все значения в срезе "Район" должны соответствовать только тем районам, которые относятся исключительно к Алтайскому краю. Ведь после выбора Алтайского края все сводные таблицы должны показать только те значения, которые относятся к Алтайскому краю. И соответственно, срезы "Район" и "Улица" также должны изменить набор значений под выбранный Алтайский край. Но этого, к сожалению, не происходит. Как же тогда организовать сквозную фильтрацию данных в сводных таблицах, построенных по разным источникам данных, с помощью нескольких взаимосвязанных срезов?
Наверх