Tabel pivot menawarkan filter 10 Teratas. Ini keren. Itu fleksibel. Tapi aku membencinya, dan aku akan memberitahumu alasannya.
Berikut adalah tabel pivot yang menunjukkan pendapatan oleh pelanggan. Total pendapatan adalah $ 6,7 juta. Perhatikan bahwa pelanggan terbesar, Roto-Rooter, adalah 9% dari total pendapatan.
Bagaimana jika manajer saya memiliki rentang perhatian seperti ikan mas dan hanya ingin melihat lima pelanggan teratas? Untuk memulai, buka dropdown di A3 dan pilih Value Filters, Top 10.
Dialog Top 10 Filter super-fleksibel memungkinkan Atas / Bawah. Itu bisa melakukan 10, 5, atau nomor lain. Anda dapat meminta lima item teratas, 80% teratas, atau cukup pelanggan untuk mendapatkan $ 5 juta.
Tapi inilah masalahnya: Laporan yang dihasilkan menunjukkan lima pelanggan dan total dari pelanggan tersebut, bukan total dari semua orang. Roto-Rooter, yang sebelumnya 9% dari total adalah 23% dari total baru.
Tapi Pertama, Beberapa Kata Penting Tentang Filter Otomatis
Saya menyadari ini sepertinya pertanyaan yang tidak biasa. Jika Anda ingin mengaktifkan menu tarik-turun Filter pada kumpulan data biasa, bagaimana Anda melakukannya? Berikut tiga cara yang sangat umum:
- Pilih satu sel dalam data Anda dan klik ikon Filter pada tab Data atau tekan Ctrl + Shift + L.
- Pilih semua data Anda dengan Ctrl + * dan klik ikon Filter pada tab Data.
- Tekan Ctrl + T untuk memformat data sebagai tabel.
Ini adalah tiga cara yang sangat bagus. Selama Anda tahu salah satunya, sama sekali tidak perlu tahu cara lain. Tapi inilah cara yang sangat tidak jelas tapi ajaib untuk mengaktifkan filter:
- Pergi ke baris header Anda dan kemudian pergi ke sel judul paling kanan. Pindah satu sel ke kanan. Untuk beberapa alasan yang tidak diketahui, saat Anda berada di sel ini dan mengklik ikon Filter, Excel memfilter kumpulan data di sebelah kiri Anda. Saya tidak tahu mengapa ini berhasil. Ini benar-benar tidak layak untuk dibicarakan karena sudah ada tiga cara yang sangat bagus untuk mengaktifkan dropdown Filter. Saya menyebut sel ini sel ajaib.
Dan Sekarang, Kembali ke Tabel Pivot
Ada aturan yang mengatakan Anda tidak bisa menggunakan Filter Otomatis saat Anda berada di tabel pivot. Lihat di bawah? Ikon Filter berwarna abu-abu karena saya telah memilih sel di tabel pivot.
Saya tidak tahu mengapa Microsoft mengabaikannya. Pasti ada sesuatu yang internal yang mengatakan Filter Otomatis dan tabel pivot tidak dapat hidup berdampingan. Jadi, ada seseorang di tim Excel yang bertanggung jawab untuk mengubah ikon Filter menjadi abu-abu. Orang itu belum pernah mendengar tentang sel ajaib. Pilih sel di tabel pivot, dan Filter berwarna abu-abu. Klik di luar tabel pivot, dan Filter diaktifkan kembali.
Tapi tunggu. Bagaimana dengan sel ajaib yang baru saja saya ceritakan? Jika Anda mengklik di sel di sebelah kanan judul terakhir, Excel lupa ikon Filter berwarna abu-abu!
Benar saja, Excel menambahkan dropdown AutoFilter ke baris atas tabel pivot Anda. Dan Filter Otomatis beroperasi secara berbeda dari filter tabel pivot. Buka menu tarik-turun Pendapatan dan pilih Filter Angka, 10 Teratas….
Dalam dialog 10 Filter Otomatis Teratas, pilih 6 Item Teratas. Itu bukan kesalahan ketik … jika Anda ingin lima pelanggan, pilih 6. Jika Anda ingin 10 pelanggan, pilih 11.
Untuk AutoFilter, baris grand total adalah item terbesar dalam data. Lima pelanggan teratas menempati posisi 2 hingga 6 dalam data.
Peringatan
Jelas, Anda sedang merobek struktur Excel dengan trik ini. Jika nanti Anda mengubah data pokok dan menyegarkan tabel pivot, Excel tidak akan menyegarkan filter karena, sejauh yang diketahui Microsoft, tidak ada cara untuk menerapkan filter ke tabel pivot!
Catatan
Tujuan kami adalah merahasiakan ini dari Microsoft karena ini adalah fitur yang cukup keren. Sudah "rusak" untuk beberapa waktu, jadi ada banyak orang yang mungkin mengandalkannya sekarang.
Solusi yang Sepenuhnya Legal di Excel 2013+
Jika Anda ingin tabel pivot menampilkan lima pelanggan teratas tetapi total dari semua pelanggan, Anda harus memindahkan data Anda ke luar Excel. Jika Anda menjalankan Excel 2013 atau yang lebih baru di Windows, ada cara yang sangat mudah untuk melakukannya. Untuk menunjukkan ini kepada Anda, saya telah menghapus tabel pivot asli. Pilih Sisipkan, Tabel Pivot. Sebelum mengklik OK, pilih kotak centang Tambahkan Data Ini Ke Model Data.
Buat tabel pivot Anda seperti biasa. Gunakan tarik-turun di A3 untuk memilih Filter Nilai, 10 Teratas, dan minta lima pelanggan teratas. Dengan satu sel di tabel pivot dipilih, buka tab Desain di Pita dan buka dropdown Subtotal. Pilihan terakhir di tarik-turun adalah Sertakan Item yang Difilter dalam Total. Biasanya, pilihan ini berwarna abu-abu. Namun karena data disimpan dalam Model Data, bukan di cache pivot normal, opsi ini sekarang tersedia.
Pilih opsi Sertakan Item yang Difilter dalam Total, dan Total Keseluruhan Anda sekarang menyertakan tanda bintang dan total semua data, seperti yang ditunjukkan di bawah ini.
Trik sel ajaib ini awalnya datang kepada saya dari Dan dalam seminar saya di Philadelphia dan diulangi 15 tahun kemudian oleh Dan yang berbeda dari seminar saya di Cincinnati. Terima kasih kepada Miguel Caballero karena telah menyarankan fitur ini.
