Penapis Top 10 jadual pangsi memberikan jumlah baris yang kelihatan
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.
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.
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.
- 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, pergi 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. Ini sebenarnya tidak patut dibincangkan kerana sudah ada tiga cara yang sangat baik untuk menghidupkan dropdown Penapis. Saya memanggil sel ini sebagai sel Magic.
Dan sekarang, Kembali ke Jadual Pangsi …
Jadi, ada peraturan yang mengatakan bahawa anda tidak boleh menggunakan Auto Filter semasa anda berada di pivot table. Lihat di bawah? Ikon Penapis berwarna kelabu kerana saya telah memilih sel di jadual pangsi.
Saya tidak pernah memikirkan mengapa Microsoft mengatasinya. Ia mestilah sesuatu yang dalaman yang mengatakan AutoFilter dan Pivot Table tidak dapat wujud bersama. Jadi, ada seseorang dalam pasukan Excel yang bertanggungjawab mengaburkan ikon Penapis. Orang itu tidak pernah mendengar tentang sel Magic. Pilih sel dalam jadual pangsi, dan Penapis menjadi kelabu. Klik di luar jadual pangsi, dan Penapis diaktifkan sekali lagi.
Tapi tunggu. Bagaimana dengan sel Magic 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 menu lungsur Pendapatan dan pilih Penapis Nombor, 10 Teratas…
Dalam dialog AutoFilter 10 Teratas, pilih 6 Item Teratas. Itu bukan typo…. Sekiranya 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 menyegarkan jadual pangsi anda, Excel tidak akan menyegarkan penapis, kerana, sejauh yang diketahui Microsoft, tidak ada cara untuk menerapkan penapis ke meja 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 mempunyai Excel 2013 atau 2016, 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 yang bertuliskan Tambah 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 Besar anda sekarang merangkumi tanda bintang dan jumlah semua data.
Silap mata ini awalnya datang kepada saya dari Dan dalam seminar saya di Philadelphia. Terima kasih kepada Miguel Caballero kerana mencadangkan ciri ini.
Tonton Video
- Penapis Top 10 jadual pangsi memberikan jumlah baris yang kelihatan
- Sertakan Item yang Disaring dalam Jumlah yang Kelabu Keluar
- Cara ganjil untuk menggunakan Penapis Data dari sel sihir
- Penapis Data tidak dibenarkan dalam jadual pangsi
- Excel gagal mengaburkan Penapis Data dari sel ajaib
- Minta 6 teratas untuk mendapat 5 teratas ditambah Grand Total
- Berguna untuk menapis berdasarkan item pangsi tertentu
- Excel 2013 atau lebih baru: Cara berbeza untuk mendapatkan Jumlah Sebenar
- Hantarkan data anda melalui Model Data
- Sertakan Item yang Disaring dalam Jumlah akan tersedia
- Dapatkan Jumlah dengan tanda bintang
- Saya belajar helah ini 10+ tahun yang lalu dari Dan di Philadelphia
Transkrip Video
Belajar Excel untuk Podcast, Episode 1999 - Pivot Table True Top Five
Saya menyiarkan keseluruhan buku ini. Terdapat senarai main, klik I di sudut kanan atas untuk mengikuti senarai main itu. Selamat kembali ke netcast. Saya Bill Jelen.
Baiklah, jadi kami akan membuat Jadual Pangsi dan kami ingin menunjukkan, bukan semua pelanggan, tetapi hanya lima pelanggan teratas. INSERT, Jadual Pangsi. Baiklah, saya akan meletakkan Pelanggan di sebelah kiri dan Hasil. Baiklah, inilah senarai pelanggan kami yang berjumlah 6.7 juta dolar. Excel, memudahkan anda melakukan lima teratas. Pergi ke Row Label, Penapis Nilai, teratas 10. Tidak semestinya teratas. Ia boleh menjadi bahagian atas atau bawah. Tidak perlu lima. Ia boleh menjadi dua puluh, empat puluh, boleh jadi apa sahaja. Lapan puluh peratus teratas, beri saya rekod yang cukup untuk mencapai tiga juta dolar atau empat juta dolar, tetapi ini juga. Lima item teratas. Sekarang ingat 6.7 juta dolar, klik OK dan masalah besar saya di sini, adalah bahawa, jumlah keseluruhan bukan 6.7 juta. Apabila saya memberikan ini kepada VP penjualan dia akan panik, berkata, tunggu sebentar,Saya tahu saya memperoleh lebih daripada 3.3 juta dolar. Betul, jadi kita akan membuat asal, buat asal dan kembali ke data asal.
Inilah helah seterusnya yang saya pelajari semasa salah satu Seminar Power Excel saya di Philadelphia. Seorang lelaki bernama Dan di barisan dua, menunjukkan ini kepada saya. Lebih dari sepuluh tahun yang lalu dia menunjukkan trik ini kepada saya, dan pertama-tama kita harus bercakap mengenai Penapis. Jadi biasanya, jika anda akan menggunakan Penapis biasa, Penapis ini di sini, anda memilih mana-mana satu sel dalam set data anda dan klik Ikon Penapis, atau sebilangan orang memilih keseluruhan Kumpulan Data, KAWALAN * dan klik Ikon Penapis, tetapi ada cara ketiga. Cara yang tidak dipedulikan oleh sesiapa pun. Sekiranya anda pergi ke Sel Tajuk terakhir, dalam kes saya, itu adalah Kos di L1, dan pergi satu sel di sebelah kanan. Saya menyebutnya sel ajaib, saya tidak tahu mengapa tetapi atas sebab yang tidak diketahui, dari sel ini, saya dapat menapis Set Data yang berdekatan. Baiklah, ini seperti cara yang pelik dan tidak ada yang peduli akan perkara ini.
Betul, kerana ada dua cara lain yang sangat baik untuk menggunakan Penapis, tidak ada yang perlu mengetahui tentang sel sihir, tetapi inilah masalahnya, lihat di dalam Pivot Table, ia berwarna kelabu. Anda tidak dibenarkan menggunakan Penapis tersebut. Ini bertentangan dengan peraturan. Sekarang, jika saya keluar di sini, saya lebih senang menggunakan Penapis tetapi di dalamnya mereka tidak dapat menilai. Saya tidak tahu siapa orang yang mengatasinya, tetapi mereka tidak pernah mendengar ceramah kecil saya mengenai sel sihir, kerana jika saya pergi ke Heading Cell terakhir dan pergi satu sel di sebelah kanan, lihatlah, mereka lupa untuk mengaburkan Penapis dan sekarang saya baru sahaja menambahkan Penapis Auto lama ke Jadual Pangsi. Jadi saya datang ke sini, pergi ke Penapis Nombor, itu berbeza daripada Penapis Nilai. Ia masih dipanggil Sepuluh Teratas. Agak berbeza, saya akan meminta lima teratas, bukan enam teratas.Enam teratas kerana untuk Penapis ini Grand Total hanyalah baris lain, dan Grand Total adalah item terbesar dan ketika diminta item 2 hingga 6, saya mendapat lima item teratas.
Baiklah, jadi kita ada. Peretasan Penapis yang hebat, yang memberi kita lima item teratas dan jumlah sebenar semua orang. Baiklah sekarang, beberapa perkara. Jangan lupa tentang sel ajaib. Baiklah, tidak ada cara untuk mematikan Penapis ini, kecuali anda kembali ke sel sihir. Baiklah, jadi anda perlu ingat sel ajaib. Juga, jika anda mengubah data yang mendasari dan menyegarkan Jadual Pangsi, mereka tidak akan menyegarkan Penapis kerana setahu Microsoft, anda tidak dibenarkan memiliki Penapis.
Ini berguna untuk perkara lain. Kadang-kadang kita mempunyai produk di bahagian atas. Mari ke sini dalam bentuk jadual. Tidak perlu, saya hanya ingin mendapatkan tajuk sebenar. Gizmo, Widget, Gadget, Doodads. Baiklah dan mungkin anda adalah pengurus Doodads dan anda perlu melihat hanya pelanggan yang mempunyai nilai dan Doodads tertentu. Oleh itu, saya pergi ke sel sihir, hidupkan Penapis dan kemudian di bawah Doodads saya boleh meminta item yang lebih besar daripada sifar. Klik OK. Baiklah, jenis Penapisan itu tidak mungkin dilakukan pada Jadual Pangsi biasa, tetapi mungkin menggunakan sel ajaib.
Baiklah sekarang mari buat asal senarai. Mari matikan Penapis ini dan hapus Jadual Pangsi, dan jika anda berada di Excel 2013 atau yang baru, saya akan menunjukkan cara yang betul untuk mendapatkan jumlah yang betul di bahagian bawah. Masukkan Jadual Pivot, di sini di bahagian bawah, bermula di Excel 2013 kotak yang sangat tidak berbahaya ini, tidak terdengar sangat menarik, tambahkan data ini ke Model Data. Itu menghantar data, di belakang tabir, ke Power Pivot Engine. Bina laporan yang sama. Pelanggan di sebelah kiri. Pendapatan di tengah-tengah Jadual Pangsi. Kemudian, pergi ke Penapis biasa, Penapis Nilai teratas 10. Minta lima teratas. Perhatikan lagi kita mempunyai 6.7 juta dolar setelah saya melakukan ini, 3.3 juta dolar tetapi inilah perbezaannya. Ketika saya pergi ke Tab Reka Bentuk, di bawah Subtotal, ciri ini disebut Sertakan Item yang Disaring dalam Jumlah,tidak lagi kelabu. Jadual Pivot biasa tidak tersedia. Kami mendapat sedikit tanda bintang di sana dan jumlahnya semuanya. Baiklah, sekarang sudah tentu hanya berfungsi di Excel 2013 atau yang lebih baru.
Baiklah, saya akan mengambil masa enam minggu untuk mengeluarkan keseluruhan buku ini di YouTube. Terdapat banyak petua baik di sini. Petua yang boleh mula menjimatkan masa anda dengan segera. Beli keseluruhan buku sekarang dan anda akan mendapat akses ke semua 40, Ini sebenarnya lebih daripada 40 petua. Kekunci pintasan Excel. Semua jenis barang hebat dalam buku ini.
Baiklah, rekap. Oleh itu, apabila kita melakukan Pivot Table top 10 Filter, ia memberi kita jumlah tetapi hanya baris yang dapat dilihat, bukan perkara yang disaringnya. Ya, jika kita pergi ke tab kedua dan mencari Subtotal, Item yang Ditapis dan Jumlahnya, ia berwarna kelabu, tetapi ada cara yang aneh untuk menggunakan Penapis Data Lama dari sel ajaib. Sel tajuk terakhir, pergi satu sel di sebelah kanan, anda tidak boleh menggunakan Tapisan dan Jadual Pangsi, tetapi jika anda pergi ke sel ajaib mereka lupa untuk mengaburkannya. Sekarang di Penapis Nombor, anda meminta enam teratas untuk mendapatkan lima teratas, ditambah jumlah keseluruhan. Juga berguna untuk menapis ke Pivot Item tertentu: Doodads, apa-apa yang mempunyai lebih daripada 0 di Doodads atau 5 Doodads teratas. Excel 2013 atau lebih baru, ada cara yang berbeza untuk mendapatkan Jumlah Sejati.Tandakan kotak untuk Model Data dan kemudian sertakan Item yang Disaring dalam Jumlah akan tersedia. Anda mendapat jumlah dengan tanda bintang. Dan terima kasih kepada Dan di Philadelphia yang menunjukkan saya di salah satu Seminar Power Excel saya, lebih daripada sepuluh tahun yang lalu, dan memberikan saya sedikit muslihat yang hebat ini. Cara untuk Penapis menyelinap melalui Wall Pivot Table Wall. Mereka biasanya tidak membenarkan Penapis Auto itu.
Hei, saya ingin mengucapkan terima kasih kerana berhenti. Kami akan berjumpa anda di lain masa, untuk siaran net lain dari MRExcel.
Muat turun fail
Muat turun fail contoh di sini: Podcast1999.xlsx
