Филтриране на няколко обобщени таблици едновременно

Когато създавате сложни отчети и особено табла за управление в 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 изберете вкл Основната екипи Затворете и заредете – Затворете и заредете (Начало — Затвори&Зареди — Затвори&Зареди в...) и опция за импортиране Просто създайте връзка (Само създаване на връзка):

4. Обединяваме двете таблици в една с командата Данни – Комбиниране на заявки – Добавяне (Данни — Комбиниране на заявки — Добавяне). Колоните с еднакви имена в заглавката ще се поберат една под друга (като колона град), а тези, които не съвпадат, ще бъдат поставени в различни колони (но това не е важно за нас).

5. Изтрийте всички колони с изключение на колоната градкато щракнете с десния бутон върху заглавието му и изберете командата Изтрийте други колони (Премахване на други колони) и след това премахнете всички дублирани имена на градове, като щракнете отново с десния бутон върху заглавието на колоната и изберете командата Премахване на дубликати (Премахване на дубликати):

6. Създаденият референтен списък се качва в модела на данни чрез Начало — Затворете и заредете — Затворете и заредете (Начало — Затвори&Зареди — Затвори&Зареди в...) и изберете опцията Просто създайте връзка (Само създаване на връзка) и най-важното! – поставете отметка в квадратчето Добавете тези данни към модела на данни (Добавете тези данни към модела на данни):

7. Сега можем, връщайки се към прозореца на Power Pivot (таб powerpivot - бутон управление), преминат към Изглед на диаграма (Изглед на диаграма) и свържете нашите таблици с продажби и транспортни разходи чрез създадената междинна директория на градове (чрез плъзгане на полета между таблиците):

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

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