Memfilter Beberapa PivotTable Secara Bersamaan

Saat membuat laporan kompleks dan, terutama, dasbor di Microsoft Excel, sangat sering diperlukan untuk memfilter beberapa tabel pivot sekaligus. Mari kita lihat bagaimana ini bisa diterapkan.

Metode 1: Pemotong Umum untuk memfilter pivot pada sumber data yang sama

Jika pivot dibuat berdasarkan satu tabel data sumber, maka cara termudah adalah menggunakannya untuk memfilternya secara bersamaan bagian adalah filter tombol grafis yang terhubung ke semua tabel pivot sekaligus.

Untuk menambahkannya, pilih sel mana saja di salah satu ringkasan dan di tab Analisis pilih tim Tempel Irisan (Analisis — Sisipkan pemotong). Di jendela yang terbuka, centang kotak untuk kolom yang ingin Anda gunakan untuk memfilter data dan klik OK:

Pemotong yang dibuat akan, secara default, hanya memfilter pivot yang membuatnya dibuat. Namun, menggunakan tombol Laporkan Koneksi (Laporkan koneksi) tab Iris (Irisan) kita dapat dengan mudah menambahkan tabel ringkasan lainnya ke daftar tabel yang difilter:

Metode 2. Irisan umum untuk memfilter ringkasan pada sumber yang berbeda

Jika pivot Anda dibangun tidak menurut satu, tetapi menurut tabel data sumber yang berbeda, maka metode di atas tidak akan berfungsi, karena di jendela Laporkan Koneksi hanya ringkasan yang dibuat dari sumber yang sama yang ditampilkan.

Namun, Anda dapat dengan mudah mengatasi batasan ini jika Anda menggunakan Model Data (kami membahasnya secara rinci dalam artikel ini). Jika kita memuat tabel kita ke dalam Model dan menautkannya di sana, maka pemfilteran akan diterapkan ke kedua tabel secara bersamaan.

Katakanlah kita memiliki dua tabel untuk biaya penjualan dan transportasi sebagai data input:

Misalkan kita dihadapkan dengan tugas membangun ringkasan kita sendiri untuk masing-masing dan kemudian memfilternya secara bersamaan berdasarkan kota dengan potongan yang sama.

Kami melakukan hal berikut:

1. Mengubah Tabel Asli Kami menjadi Tabel Cerdas Dinamis dengan Pintasan Keyboard Ctrl+T atau perintah Beranda – Format sebagai tabel (Beranda — Format sebagai Tabel) dan beri mereka nama tablProdaji и tabTransportasi tab Pembina (Rancangan).

2. Muat kedua tabel secara bergantian menjadi Model menggunakan tombol Tambahkan ke Model Data pada tab Power Pivot.

Tidak mungkin untuk langsung menautkan tabel ini dalam Model, karena sementara Power Pivot hanya mendukung hubungan satu ke banyak, yaitu mengharuskan salah satu tabel tidak memiliki duplikat di kolom yang kita tautkan. Kami memiliki yang sama di kedua tabel di lapangan Kota ada pengulangan. Jadi kita perlu membuat tabel pencarian perantara lain dengan daftar nama kota yang unik dari kedua tabel. Cara termudah untuk melakukannya adalah dengan fungsionalitas add-in Power Query, yang telah dibangun ke dalam Excel sejak versi 2016 (dan untuk Excel 2010-2013 diunduh secara gratis dari situs web Microsoft).

3. Setelah memilih sel apa pun di dalam tabel "pintar", kami memuatnya satu per satu di Power Query dengan tombol Dari tabel/rentang tab Data (Data — Dari tabel/rentang) lalu di jendela Power Query pilih pada Utama tim Tutup dan muat – Tutup dan muat masuk (Beranda — Tutup&Muat — Tutup&Muat ke…) dan opsi impor Buat saja koneksi (Hanya buat koneksi):

4. Kami menggabungkan kedua tabel menjadi satu dengan perintah Data – Gabungkan Kueri – Tambahkan (Data — Gabungkan kueri — Tambahkan). Kolom dengan nama yang sama di header akan cocok di bawah satu sama lain (seperti kolom Kota), dan yang tidak cocok akan ditempatkan di kolom yang berbeda (tetapi ini tidak penting bagi kami).

5. Hapus semua kolom kecuali kolom Kotadengan mengklik kanan pada judulnya dan memilih perintah Hapus kolom lain (Hapus kolom lain) dan kemudian hapus semua nama kota duplikat dengan mengklik kanan judul kolom lagi dan memilih perintah Hapus Duplikat (Hapus duplikat):

6. Daftar referensi yang dibuat diunggah ke Model Data melalui Beranda — Tutup dan muat — Tutup dan muat masuk (Beranda — Tutup&Muat — Tutup&Muat ke…) dan pilih opsi Buat saja koneksi (Hanya buat koneksi) dan yang terpenting! – nyalakan kotak centang Tambahkan data ini ke model data (Tambahkan data ini ke Model Data):

7. Sekarang kita bisa, kembali ke jendela Power Pivot (tab Powerpivot - tombol Pengelolaan), beralih ke Tampilan Grafik (Tampilan diagram) dan tautkan tabel biaya penjualan dan transportasi kami melalui direktori kota perantara yang dibuat (dengan menyeret bidang di antara tabel):

8. Sekarang Anda dapat membuat semua tabel pivot yang diperlukan untuk model yang dibuat menggunakan tombol tabel ringkasan (Tabel pivot) on Utama (Rumah) di jendela Power Pivot dan, dengan memilih sel mana pun di pivot mana pun, pada tab Analisis tambahkan tombol irisan Tempel Irisan (Analisis — Sisipkan Pemotong) dan pilih untuk mengiris di kotak daftar Kota di direktori yang ditambahkan:

Sekarang, dengan mengklik tombol yang sudah dikenal Laporkan Koneksi on tab irisan (Pemotong — Laporkan koneksi) kita akan melihat semua ringkasan kita, karena sekarang dibangun di atas tabel sumber terkait. Tetap mengaktifkan kotak centang yang hilang dan klik OK – dan alat pengiris kami akan mulai memfilter semua tabel pivot yang dipilih secara bersamaan.