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

Пры стварэнні складаных справаздач і, асабліва, панэляў у 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 – і наш слайсер адначасова пачне фільтраваць усе выбраныя зводныя табліцы.