Bir vaqtning o'zida bir nechta pivot jadvallarni filtrlash

Murakkab hisobotlarni va ayniqsa, Microsoft Excel-da asboblar panelini yaratishda bir vaqtning o'zida bir nechta pivot jadvallarni filtrlash juda tez-tez talab qilinadi. Keling, buni qanday amalga oshirish mumkinligini ko'rib chiqaylik.

1-usul: Bir xil ma'lumot manbasida pivotlarni filtrlash uchun umumiy Slicer

Agar pivotlar bitta manba ma'lumotlar jadvali asosida qurilgan bo'lsa, unda eng oson yo'li ularni bir vaqtning o'zida filtrlash uchun ishlatishdir. Bo'lim bir vaqtning o'zida barcha pivot jadvallarga ulangan grafik tugma filtri.

Uni qo'shish uchun xulosalardan birida va yorliqda istalgan katakchani tanlang Tahlil jamoani tanlang Bo'lakni yopishtiring (Tahlil qilish - tilimlagichni kiritish). Ochilgan oynada ma'lumotlarni filtrlamoqchi bo'lgan ustunlar uchun katakchalarni belgilang va ustiga bosing OK:

Yaratilgan slicer, sukut bo'yicha, faqat o'zi yaratilgan pivotni filtrlaydi. Biroq, tugma yordamida Ulanishlar haqida xabar berish (Ulanishlar haqida xabar berish) tab Tilim (bo'laklar) Biz filtrlangan jadvallar ro'yxatiga boshqa yig'ma jadvallarni osongina qo'shishimiz mumkin:

2-usul. Turli manbalar bo'yicha xulosalarni filtrlash uchun umumiy bo'lak

Agar sizning pivotlaringiz bitta emas, balki turli xil manba ma'lumotlar jadvallari bo'yicha qurilgan bo'lsa, unda yuqoridagi usul ishlamaydi, chunki oynada Ulanishlar haqida xabar berish faqat bitta manbadan tuzilgan xulosalar ko'rsatiladi.

Biroq, agar siz Ma'lumotlar modelidan foydalansangiz, ushbu cheklovni osongina engishingiz mumkin (biz buni ushbu maqolada batafsil muhokama qildik). Agar biz jadvallarimizni Modelga yuklasak va ularni u erga bog'lasak, filtrlash bir vaqtning o'zida ikkala jadvalga ham tegishli bo'ladi.

Aytaylik, bizda kirish ma'lumotlari sifatida sotish va transport xarajatlari uchun ikkita jadval mavjud:

Aytaylik, biz ularning har biri uchun o'z xulosamizni yaratish va keyin ularni bir vaqtning o'zida umumiy kesimga ega shaharlar bo'yicha filtrlash vazifasiga duch keldik.

Biz quyidagilarni bajaramiz:

1. Asl jadvallarimizni klaviatura yorlig'i yordamida dinamik aqlli jadvallarga aylantirish Ctrl+T yoki buyruqlar Bosh sahifa - Jadval sifatida formatlash (Uy — Jadval sifatida formatlash) va ularga nom bering tablProdaji и tabTransport tab konstruktor (Dizayn).

2. Tugma yordamida ikkala jadvalni navbatma-navbat Modelga yuklang Ma'lumotlar modeliga qo'shish Power Pivot yorlig'ida.

Ushbu jadvallarni Modelda to'g'ridan-to'g'ri bog'lash mumkin bo'lmaydi, chunki Power Pivot faqat bittadan ko'pga munosabatlarni qo'llab-quvvatlaydi, ya'ni jadvallardan biri biz bog'layotgan ustunda dublikat bo'lmasligini talab qiladi. Maydondagi ikkala jadvalda ham xuddi shunday tuyg'ular takrorlashlar mavjud. Shunday qilib, biz ikkala jadvaldagi noyob shahar nomlari ro'yxati bilan yana bir oraliq qidiruv jadvalini yaratishimiz kerak. Buning eng oson yo'li 2016 yil versiyasidan beri Excelga o'rnatilgan Power Query plaginining funksionalligidir (va Excel 2010-2013 uchun u Microsoft veb-saytidan bepul yuklab olingan).

3. “Aqlli” jadvaldagi istalgan katakchani tanlab, tugma yordamida ularni Power Query-ga birma-bir yuklaymiz. Jadvaldan/diapazondan tab ma'lumotlar (Ma'lumotlar - jadvaldan/diapazondan) va keyin Power Query oynasida yoqish-ni tanlang Asosiy Jamoalari Yopish va yuklash - Yopish va yuklash (Uy — Yopish va yuklash — Yopish va yuklash…) va import opsiyasi Faqat ulanishni yarating (Faqat ulanish yaratish):

4. Buyruq yordamida ikkala jadvalni bittaga birlashtiramiz Ma'lumotlar - So'rovlarni birlashtirish - Qo'shish (Ma'lumotlar - so'rovlarni birlashtirish - qo'shish). Sarlavhadagi bir xil nomli ustunlar bir-birining ostiga joylashadi (masalan, ustun tuyg'ular), va mos kelmaydiganlar turli ustunlarga joylashtiriladi (lekin bu biz uchun muhim emas).

5. Ustundan tashqari barcha ustunlarni o'chiring tuyg'ularuning sarlavhasini sichqonchaning o'ng tugmasi bilan bosing va buyruqni tanlang Boshqa ustunlarni o'chiring (Boshqa ustunlarni olib tashlash) va keyin ustun sarlavhasini yana sichqonchaning o'ng tugmasini bosib, buyruqni tanlash orqali barcha takroriy shahar nomlarini olib tashlang Dublikatlarni olib tashlang (Dublikatlarni olib tashlash):

6. Yaratilgan ma'lumotnomalar ro'yxati orqali Ma'lumotlar modeliga yuklanadi Bosh sahifa — Yopish va yuklash — Yopish va yuklash (Uy — Yopish va yuklash — Yopish va yuklash…) variantni tanlang Faqat ulanishni yarating (Faqat ulanish yaratish) va eng muhimi! – belgilash katakchasini yoqing Ushbu ma'lumotlarni ma'lumotlar modeliga qo'shing (Ushbu ma'lumotlarni Ma'lumotlar modeliga qo'shing):

7. Endi biz Power Pivot oynasiga qaytishimiz mumkin (tab quvvat pivoti - tugma boshqaruv), ga oʻtish Diagramma ko'rinishi (Diagramma ko'rinishi) va shaharlarning yaratilgan oraliq katalogi orqali savdo va transport xarajatlari jadvallarini bog'lang (jadvallar orasidagi maydonlarni sudrab):

8. Endi tugma yordamida yaratilgan model uchun barcha kerakli pivot jadvallarini yaratishingiz mumkin xulosa jadvali (Pivot jadvali) on Asosiy (Uy) yorlig'ini Power Pivot oynasida va yorliqda istalgan pivotdagi istalgan katakchani tanlash orqali Tahlil tilim qo'shish tugmasi Bo'lakni yopishtiring (Tahlil qilish - Dilimlagichni kiritish) va roʻyxat oynasida kesishni tanlang tuyg'ular qo'shilgan katalogda:

Endi tanish tugmani bosish orqali Ulanishlar haqida xabar berish on Bo'lak yorlig'i (Slicer - ulanishlar haqida xabar berish) biz barcha xulosalarimizni ko'ramiz, chunki ular endi tegishli manba jadvallarida qurilgan. Yo'qolgan tasdiqlash qutilarini yoqish va ustiga bosing OK – va bizning slayderimiz bir vaqtning o'zida barcha tanlangan pivot jadvallarni filtrlashni boshlaydi.