Как связать два сводных отчета Excel одним срезом через модель данных
Один из самых частых и сложных вопросов в Excel — как заставить два сводных отчета из разных источников данных реагировать на один общий срез. Решение лежит в использовании встроенной модели данных и правильной настройке связей.
Шаги рассчитаны на настольный Excel для Windows с поддержкой модели данных. Сначала обе исходные таблицы нужно включить в одну модель и на её основе создать оба отчёта: обычные сводные по двум независимым диапазонам одним срезом не соединить.
Вместо сложных обходных путей, описанных на форумах, мы используем стандартный функционал Excel для создания единой точки управления.
- Убедитесь, что ваши две исходные таблицы имеют хотя бы одно общее поле (например, «Название магазина»).
- Создайте третью таблицу, содержащую уникальный список значений из этого общего поля, взятый из обеих исходных таблиц.
- Преобразуйте все три диапазона в таблицы Excel с помощью
Ctrl + Tи добавьте каждую в одну модель данных через Power Pivot → «Добавить в модель данных». Проверьте одинаковый тип ключей и отсутствие повторов и пустых ключей в справочнике «Магазины».
- На вкладке «Данные» нажмите «Связи» и свяжите ключ магазина в каждой исходной таблице с уникальным ключом справочника «Магазины» — две связи «многие к одному». Затем создайте заново оба сводных отчёта из этой модели: показатели первого берите из первой таблицы, второго — из второй. Подписи и общие фильтры берите из «Магазины».
- В любой из сводных таблиц выберите «Вставить срез». Изначально срез будет работать только с этой таблицей.
- В диалоговом окне «Вставить срез» перейдите на вкладку «Все» и выберите поле «Название магазина» из справочника «Магазины».
- После вставки среза щелкните по нему. На вкладке ленты «Работа со срезами» → «Конструктор» нажмите «Подключения к отчетам».
- В открывшемся диалоговом окне отметьте вторую сводную таблицу, чтобы подключить срез и к ней.
- Теперь при выборе элементов в срезе обе сводные таблицы будут синхронно фильтроваться. Для загрузки новых исходных строк отдельно выполните «Обновить всё» и при необходимости обновите справочник магазинов.
Итог: Используя модель данных Excel и центральную таблицу-справочник, вы создаете элегантное и надежное решение для управления несколькими отчетами. Этот метод избавляет от необходимости в сложных формулах и макросах, предоставляя нативную функциональность Excel для профессиональной аналитики.