Jadual pangsi menawarkan penapis 10 teratas. Ia sejuk. Ia fleksibel. Tetapi saya membencinya, dan saya akan memberitahu anda mengapa.
Berikut adalah jadual pangsi yang menunjukkan pendapatan oleh pelanggan. Jumlah pendapatan adalah $ 6.7 juta. Perhatikan bahawa pelanggan terbesar, Roto-Rooter, adalah 9% dari jumlah pendapatan.
Bagaimana jika pengurus saya mempunyai perhatian ikan mas dan hanya mahu melihat lima pelanggan teratas? Untuk memulakan, buka dropdown di A3 dan pilih Penapis Nilai, 10 Teratas.
Dialog Top 10 Filter yang sangat fleksibel membolehkan Atas / Bawah. Ia boleh menghasilkan 10, 5, atau nombor lain. Anda boleh meminta lima item teratas, 80% teratas, atau cukup pelanggan sehingga mencecah $ 5 juta.
Tetapi inilah masalahnya: Laporan yang dihasilkan menunjukkan lima pelanggan dan jumlah keseluruhan dari pelanggan tersebut dan bukannya jumlah keseluruhan dari semua orang. Roto-Rooter, yang sebelumnya 9% dari jumlah keseluruhan adalah 23% dari jumlah baru.
Tetapi Pertama, Beberapa Perkataan Penting Tentang AutoFilter
Saya menyedari ini seolah-olah seperti soalan luar dinding. Sekiranya anda ingin menghidupkan dropdown Filter pada set data biasa, bagaimana anda melakukannya? Berikut adalah tiga cara yang sangat biasa:
- Pilih satu sel dalam data anda dan klik ikon Tapis pada tab Data atau tekan Ctrl + Shift + L.
- Pilih semua data anda dengan Ctrl + * dan klik ikon Tapis pada tab Data.
- Tekan Ctrl + T untuk memformat data sebagai jadual.
Ini adalah tiga cara yang sangat baik. Selagi anda mengetahui salah satu daripadanya, sama sekali tidak perlu mengetahui cara lain. Tetapi inilah cara yang sangat kabur tetapi ajaib untuk menghidupkan penapis:
- Pergi ke baris tajuk anda dan kemudian ke sel tajuk paling kanan. Gerakkan satu sel ke kanan. Atas sebab yang tidak diketahui, semasa anda berada di sel ini dan mengklik ikon Tapis, Excel menapis set data di sebelah kiri anda. Saya tidak tahu mengapa ini berfungsi. Sebenarnya tidak patut dibincangkan kerana sudah ada tiga cara yang sangat baik untuk menghidupkan dropdown Penapis. Saya memanggil sel ini sebagai sel sihir.
Dan Sekarang, Kembali ke Jadual Pangsi
Terdapat peraturan yang mengatakan bahawa anda tidak boleh menggunakan AutoFilter ketika anda berada di pivot table. Lihat di bawah? Ikon Penapis berwarna kelabu kerana saya telah memilih sel di jadual pangsi.
Saya tidak tahu mengapa Microsoft mengatasinya. Ia mestilah sesuatu yang dalaman yang mengatakan AutoFilter dan jadual pangsi tidak dapat wujud bersama. Jadi, ada seseorang dalam pasukan Excel yang bertanggungjawab mengaburkan ikon Penapis. Orang itu tidak pernah mendengar tentang sel sihir. Pilih sel dalam jadual pangsi, dan Penapis menjadi kelabu. Klik di luar jadual pangsi, dan Penapis diaktifkan sekali lagi.
Tapi tunggu. Bagaimana dengan sel sihir yang baru saya ceritakan? Sekiranya anda mengklik di sel di sebelah kanan tajuk terakhir, Excel lupa untuk mengaburkan ikon Penapis!
Sudah tentu, Excel menambah dropdown AutoFilter ke baris atas jadual pangsi anda. Dan AutoFilter beroperasi berbeza daripada penapis jadual pangsi. Pergi ke dropdown Pendapatan dan pilih Penapis Nombor, 10 Teratas….
Dalam dialog AutoFilter 10 Teratas, pilih 6 Item Teratas. Itu bukan kesalahan ketik… jika anda mahukan lima pelanggan, pilih 6. Sekiranya anda mahukan 10 pelanggan, pilih 11.
Untuk AutoFilter, baris keseluruhan adalah item terbesar dalam data. Lima pelanggan teratas menempati kedudukan 2 hingga 6 dalam data.
Awas
Jelas sekali, anda merobek lubang kain Excel dengan muslihat ini. Sekiranya anda kemudian mengubah data yang mendasari dan memuat semula jadual pangsi anda, Excel tidak akan memuat semula penapis kerana, sejauh yang diketahui oleh Microsoft, tidak ada cara untuk menerapkan penapis pada jadual pangsi!
Catatan
Tujuan kami adalah merahsiakan perkara ini dari Microsoft kerana ia adalah ciri yang cukup menarik. Sudah "rusak" untuk beberapa waktu, jadi ada banyak orang yang mungkin bergantung padanya sekarang.
Penyelesaian Undang-undang Sepenuhnya di Excel 2013+
Sekiranya anda mahukan jadual pangsi yang menunjukkan kepada anda lima pelanggan teratas tetapi jumlahnya dari semua pelanggan, anda harus memindahkan data anda ke luar Excel. Sekiranya anda menjalankan Excel 2013 atau yang lebih baru di Windows, ada cara yang sangat mudah untuk melakukannya. Untuk menunjukkan ini kepada anda, saya telah memadamkan jadual pangsi asal. Pilih Masukkan, Jadual Pangsi. Sebelum mengklik OK, pilih kotak centang Tambahkan Data Ini Ke Model Data.
Bina jadual pangsi anda seperti biasa. Gunakan dropdown di A3 untuk memilih Penapis Nilai, 10 Teratas, dan minta lima pelanggan teratas. Dengan satu sel di jadual pangsi dipilih, pergi ke tab Reka bentuk di Pita dan buka drop-down Subtotal. Pilihan terakhir dalam dropdown adalah Sertakan Item yang Disaring dalam Jumlah. Biasanya, pilihan ini berwarna kelabu. Tetapi kerana data disimpan dalam Model Data dan bukannya cache pivot biasa, pilihan ini kini tersedia.
Pilih pilihan Sertakan Item yang Disaring dalam Jumlah, dan Jumlah Total anda sekarang termasuk tanda bintang dan jumlah semua data, seperti yang ditunjukkan di bawah.
Tipu daya sel sihir ini pada mulanya datang kepada saya dari Dan dalam seminar saya di Philadelphia dan diulang 15 tahun kemudian oleh Dan yang berbeza dari seminar saya di Cincinnati. Terima kasih kepada Miguel Caballero kerana mencadangkan ciri ini.
