Menapis Berbilang Jadual Pangsi Serentak

Apabila membuat laporan yang kompleks dan, terutamanya, papan pemuka dalam Microsoft Excel, ia selalunya diperlukan untuk menapis beberapa jadual pangsi serentak secara serentak. Mari lihat bagaimana ini boleh dilaksanakan.

Kaedah 1: Penghiris Umum untuk menapis pangsi pada sumber data yang sama

Jika pangsi dibina berdasarkan satu jadual data sumber, maka cara paling mudah ialah menggunakannya untuk menapisnya secara serentak seksyen ialah penapis butang grafik yang disambungkan ke semua jadual pangsi sekaligus.

Untuk menambahkannya, pilih mana-mana sel dalam salah satu ringkasan dan pada tab Analisis pilih pasukan Tampal Slice (Analisis - Sisipkan penghiris). Dalam tetingkap yang terbuka, tandai kotak untuk lajur yang anda mahu menapis data dan klik OK:

Penghiris yang dibuat akan, secara lalai, menapis hanya pangsi yang ia dicipta. Walau bagaimanapun, menggunakan butang Laporkan Sambungan (Laporkan sambungan) tab Slice (Kepingan) kita boleh dengan mudah menambah jadual ringkasan lain pada senarai jadual yang ditapis:

Kaedah 2. Potongan umum untuk menapis ringkasan pada sumber yang berbeza

Jika pangsi anda dibina bukan mengikut satu, tetapi mengikut jadual data sumber yang berbeza, maka kaedah di atas tidak akan berfungsi, kerana dalam tetingkap Laporkan Sambungan hanya ringkasan yang dibina daripada sumber yang sama dipaparkan.

Walau bagaimanapun, anda boleh mengatasi had ini dengan mudah jika anda menggunakan Model Data (kami membincangkannya secara terperinci dalam artikel ini). Jika kami memuatkan jadual kami ke dalam Model dan memautkannya di sana, maka penapisan akan digunakan pada kedua-dua jadual pada masa yang sama.

Katakan kita mempunyai dua jadual untuk kos jualan dan pengangkutan sebagai data input:

Katakan bahawa kita berhadapan dengan tugas untuk membina ringkasan kita sendiri untuk setiap daripada mereka dan kemudian menapisnya secara serentak oleh bandar-bandar dengan potongan yang sama.

Kami melakukan perkara berikut:

1. Mengubah Jadual Asal Kami menjadi Jadual Pintar Dinamik dengan Pintasan Papan Kekunci Ctrl+T atau perintah Laman Utama – Format sebagai jadual (Laman Utama — Format sebagai Jadual) dan beri mereka nama tablProdaji и tabPengangkutan tab Pembina (Reka Bentuk).

2. Muatkan kedua-dua jadual secara bergilir-gilir ke dalam Model menggunakan butang Tambahkan pada Model Data pada tab Power Pivot.

Ia tidak akan dapat memautkan jadual ini secara langsung dalam Model, kerana walaupun Power Pivot hanya menyokong perhubungan satu-ke-banyak, iaitu memerlukan salah satu jadual untuk tidak mempunyai pendua dalam lajur yang kami pautkan. Kami mempunyai yang sama dalam kedua-dua jadual di lapangan City terdapat pengulangan. Jadi kita perlu mencipta satu lagi jadual carian perantaraan dengan senarai nama bandar unik daripada kedua-dua jadual. Cara paling mudah untuk melakukan ini ialah dengan kefungsian tambah Power Query, yang telah dibina ke dalam Excel sejak versi 2016 (dan untuk Excel 2010-2013 ia dimuat turun secara percuma dari tapak web Microsoft).

3. Setelah memilih mana-mana sel dalam jadual "pintar", kami memuatkannya satu demi satu dalam Power Query dengan butang Daripada jadual/julat tab Tarikh (Data — Daripada jadual/julat) dan kemudian dalam tetingkap Power Query pilih hidupkan Utama pasukan Tutup dan muat – Tutup dan muatkan (Laman Utama — Tutup&Muatkan — Tutup&Muat ke…) dan pilihan import Buat sambungan sahaja (Hanya buat sambungan):

4. Kami menggabungkan kedua-dua jadual menjadi satu dengan arahan Data – Gabungkan Pertanyaan – Tambah (Data — Gabungkan pertanyaan — Tambah). Lajur dengan nama yang sama dalam pengepala akan dimuatkan di bawah satu sama lain (seperti lajur City), dan yang tidak sepadan akan diletakkan dalam lajur yang berbeza (tetapi ini tidak penting untuk kami).

5. Padam semua lajur kecuali lajur Citydengan mengklik kanan pada tajuknya dan memilih arahan Padamkan lajur lain (Alih keluar lajur lain) dan kemudian alih keluar semua nama bandar pendua dengan mengklik kanan tajuk lajur sekali lagi dan memilih arahan Keluarkan Duplikat (Alih keluar pendua):

6. Senarai rujukan yang dibuat dimuat naik ke Model Data melalui Rumah — Tutup dan muat — Tutup dan muatkan (Laman Utama — Tutup&Muatkan — Tutup&Muat ke…) dan pilih pilihan Buat sambungan sahaja (Hanya buat sambungan) dan yang paling penting! – hidupkan kotak semak Tambahkan data ini pada model data (Tambahkan data ini pada Model Data):

7. Sekarang kita boleh, kembali ke tetingkap Power Pivot (tab Powerpivot - butang pengurusan), bertukar kepada Paparan Carta (Pandangan rajah) dan pautkan jadual jualan dan kos pengangkutan kami melalui direktori perantaraan bandar yang dicipta (dengan menyeret medan antara jadual):

8. Kini anda boleh mencipta semua jadual pangsi yang diperlukan untuk model yang dibuat menggunakan butang jadual ringkasan (Jadual Pangsi) on Utama (Rumah) tab dalam tetingkap Power Pivot dan, dengan memilih mana-mana sel dalam mana-mana pangsi, pada tab Analisis tambah butang hirisan Tampal Slice (Analisis — Sisipkan Penghiris) dan pilih untuk menghiris dalam kotak senarai City dalam direktori tambahan:

Sekarang, dengan mengklik pada butang biasa Laporkan Sambungan on Tab potong (Slicer - Laporkan sambungan) kami akan melihat semua ringkasan kami, kerana ia kini dibina di atas jadual sumber yang berkaitan. Ia kekal untuk membolehkan kotak semak yang hilang dan klik pada OK – dan penghiris kami akan mula menapis semua jadual pangsi yang dipilih pada masa yang sama.