Одночасне фільтрування кількох зведених таблиць

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

Спосіб 1: Загальний зріз для фільтрації зведених даних на одному джерелі даних

Якщо зведені таблиці будуються на основі однієї таблиці вихідних даних, то найпростіше використовувати їх для одночасного фільтрування розділ це графічний кнопковий фільтр, підключений до всіх зведених таблиць одночасно.

Щоб додати його, виділіть будь-яку комірку в одному з підсумків і на вкладці аналіз виберіть команду Вставити фрагмент (Аналіз — Вставити роздільник). У вікні, що відкриється, встановіть прапорці біля стовпців, за якими потрібно фільтрувати дані, і натисніть OK:

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

Спосіб 2. Загальний зріз для фільтрації зведень за різними джерелами

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

Однак ви можете легко обійти це обмеження, якщо використовуєте модель даних (ми детально обговорювали її в цій статті). Якщо ми завантажимо наші таблиці в модель і пов’яжемо їх там, тоді фільтрація застосовуватиметься до обох таблиць одночасно.

Скажімо, у нас є дві таблиці для витрат на продаж і транспортування як вхідних даних:

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

Ми робимо наступне:

1. Перетворення наших оригінальних таблиць на динамічні розумні таблиці за допомогою комбінації клавіш Ctrl+T або команди Домашня сторінка – Формат у вигляді таблиці (Домашня сторінка — форматувати як таблицю) і дайте їм імена tablProdaji и вкладка Транспорт таб Конструктор (Дизайн).

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

Неможливо буде безпосередньо зв’язати ці таблиці в моделі, оскільки Power Pivot підтримує лише зв’язки «один до багатьох», тобто вимагає, щоб одна з таблиць не мала дублікатів у стовпці, який ми зв’язуємо. Маємо те саме в обох таблицях на полі Місто є повтори. Тому нам потрібно створити ще одну проміжну таблицю пошуку зі списком унікальних назв міст з обох таблиць. Найпростіший спосіб зробити це за допомогою функції надбудови Power Query, яка вбудована в Excel з версії 2016 (а для Excel 2010-2013 її безкоштовно завантажують із веб-сайту Microsoft).

3. Виділивши будь-яку комірку всередині «розумної» таблиці, завантажуємо їх одну за одною в Power Query кнопкою З таблиці/діапазону таб дані (Дані — з таблиці/діапазону) а потім у вікні Power Query виберіть увімк Головний команди Close and load – Закрити та завантажити (Додому — Закрити&Завантажити — Закрити&Завантажити до…) і варіант імпорту Просто створіть зв'язок (тільки створити з'єднання):

4. З’єднуємо обидві таблиці в одну командою Дані – Об’єднати запити – Додати (Дані — Об’єднати запити — Додати). Стовпці з однаковими назвами в заголовку будуть підходити один під одним (як стовпець Місто), а ті, що не збігаються, будуть розміщені в різних стовпцях (але це для нас не важливо).

5. Видалити всі стовпці, крім стовпця Містоклацнувши правою кнопкою миші на його заголовку та вибравши команду Видалити інші стовпці (Видалити інші стовпці) а потім видаліть усі повторювані назви міст, знову клацнувши правою кнопкою миші заголовок стовпця та вибравши команду Видалити дублікати (Видалити дублікати):

6. Створений список посилань завантажується в модель даних через Головна — Закрийте та завантажте — Закрийте та завантажте (Додому — Закрити&Завантажити — Закрити&Завантажити до…) і виберіть опцію Просто створіть зв'язок (тільки створити з'єднання) і найголовніше! – встановіть прапорець Додайте ці дані до моделі даних (Додайте ці дані до моделі даних):

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

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

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