Filter 10 Teratas tabel pivot memberikan total baris yang terlihat
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.
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.
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.
- 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:
- Buka baris tajuk Anda, ke sel tajuk 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…
Jadi, ada aturan yang mengatakan Anda tidak bisa menggunakan AutoFilters saat Anda berada di tabel pivot. Lihat di bawah? Ikon Filter berwarna abu-abu karena saya telah memilih sel di tabel pivot.
Saya tidak pernah benar-benar mempertimbangkan mengapa Microsoft mengabaikan ini. Itu pasti sesuatu yang internal yang mengatakan Filter Otomatis dan Tabel Pivot tidak bisa 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 salah 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 Anda, 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 memiliki Excel 2013 atau 2016, 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 yang bertuliskan 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.
Trik ini awalnya datang kepada saya dari Dan dalam seminar saya di Philadelphia. Terima kasih kepada Miguel Caballero karena telah menyarankan fitur ini.
Menonton video
- Filter 10 Teratas tabel pivot memberikan total baris yang terlihat
- Sertakan Item yang Difilter dalam Total Berwarna Abu-abu
- Cara aneh untuk mengaktifkan Filter Data dari sel ajaib
- Filter Data tidak diperbolehkan dalam tabel pivot
- Excel gagal membuat Filter Data menjadi abu-abu dari sel ajaib
- Minta 6 teratas untuk mendapatkan 5 teratas ditambah Grand Total
- Berguna untuk memfilter berdasarkan item pivot tertentu
- Excel 2013 atau yang lebih baru: Cara Berbeda untuk mendapatkan Total Sebenarnya
- Kirim data Anda melalui Model Data
- Sertakan Item yang Difilter dalam Total akan tersedia
- Dapatkan Total dengan tanda bintang
- Saya mempelajari trik ini 10+ tahun yang lalu dari Dan di Philadelphia
Transkrip Video
Pelajari Excel untuk Podcast, Episode 1999 - Tabel Pivot Lima Teratas Benar
Saya membuat podcasting seluruh buku ini. Ada playlist, klik I di pojok kanan atas untuk mengikuti playlist tersebut. Selamat datang kembali di netcast. Saya Bill Jelen.
Baiklah, jadi kami akan membuat Tabel Pivot dan kami ingin menunjukkan, tidak semua pelanggan, tetapi hanya lima pelanggan teratas. MASUKKAN, Tabel Pivot. Oke, saya akan menempatkan Pelanggan di sisi kiri dan Pendapatan. Baiklah, inilah seluruh daftar pelanggan kami yang tercatat sebagai 6,7 juta dolar. Excel, memudahkan untuk melakukan lima besar. Pergilah ke Label Baris, Filter Nilai, 10 teratas. Tidak harus menjadi yang teratas. Bisa atas atau bawah. Tidak harus lima. Bisa dua puluh, empat puluh, bisa apa saja. Delapan puluh persen teratas, beri saya catatan yang cukup untuk mencapai tiga juta dolar atau empat juta dolar, tapi ini dia. Lima item teratas. Sekarang ingat 6,7 juta dolar, klik OK dan masalah besar saya di sini, adalah, total keseluruhan itu bukan 6,7 juta. Saat saya memberikan ini kepada VP penjualan, dia akan ketakutan, berkata, tunggu sebentar,Saya tahu saya menghasilkan lebih dari 3,3 juta dolar. Benar, jadi kami akan membatalkan, membatalkannya, dan kembali ke data asli.
Trik berikut ini saya pelajari selama salah satu Seminar Power Excel saya di Philadelphia. Seorang pria bernama Dan di baris dua, menunjukkan ini padaku. Sudah lebih dari sepuluh tahun yang lalu dia menunjukkan kepada saya trik ini, dan pertama-tama kita harus berbicara tentang Filter. Jadi biasanya, jika Anda akan menggunakan Filter biasa, Filter ini di sini, Anda memilih salah satu sel dalam kumpulan data Anda dan mengklik Ikon Filter, atau beberapa orang memilih seluruh Kumpulan Data, CONTROL * dan klik Ikon Filter, tapi ada cara ketiga. Cara yang tidak dipedulikan oleh siapa pun. Jika Anda pergi ke Sel Heading terakhir, dalam kasus saya, itu adalah Biaya di L1, dan pindah satu sel ke kanan. Saya menyebutnya sel ajaib, saya tidak tahu mengapa tetapi untuk beberapa alasan yang tidak diketahui, dari sel ini, saya dapat memfilter Kumpulan Data yang berdekatan. Baiklah, ini seperti cara yang aneh dan tidak ada yang peduli tentang ini.
Benar, karena ada dua cara lain yang sangat bagus untuk mengaktifkan Filter, tidak ada yang perlu tahu tentang sel ajaib, tapi inilah masalahnya, lihat di dalam Tabel Pivot, warnanya abu-abu. Anda tidak diizinkan menggunakan Filter tersebut. Itu melanggar aturan. Sekarang, jika saya keluar ke sini, saya lebih dari dipersilahkan untuk menggunakan Filter tetapi di dalamnya mereka menilai. Saya tidak tahu siapa orang yang mengatakan ini, tetapi mereka tidak pernah mendengar pembicaraan kecil saya tentang sel ajaib, karena jika saya pergi ke Sel Heading terakhir dan pergi satu sel ke kanan, lihat itu, mereka lupa untuk mengubah Filter menjadi abu-abu dan sekarang saya baru saja menambahkan Filter Otomatis lama ke Tabel Pivot. Jadi saya datang ke sini, pergi ke Filter Angka, itu berbeda dari Filter Nilai. Ini masih disebut Sepuluh Teratas. Sedikit berbeda, saya akan meminta lima teratas, bukan enam teratas.Enam teratas karena Filter ini Total Keseluruhan hanyalah baris lain, dan Total Keseluruhan adalah item terbesar dan kemudian ketika diminta untuk item 2 sampai 6, saya mendapatkan lima item teratas.
Baiklah, jadi itu dia. Peretasan Filter yang keren, yang memberi kita lima item teratas dan total sebenarnya dari semua orang. Baiklah sekarang, beberapa hal. Jangan lupa tentang sel ajaib. Baiklah tidak ada cara untuk mematikan Filter ini, kecuali Anda kembali ke sel ajaib. Baiklah jadi Anda perlu mengingat sel ajaib. Selain itu, jika Anda mengubah data yang mendasari dan Anda merefresh Tabel Pivot, mereka tidak akan me-refresh Filter karena sejauh yang diketahui Microsoft, Anda tidak diizinkan untuk memiliki Filter.
Ini berguna untuk hal lain. Terkadang kami memiliki produk yang melewati bagian atas. Mari kita pergi ke sini dalam bentuk tabel. Tidak perlu, saya hanya ingin mendapatkan judul asli. Gizmo, Widget, Gadget, Doodads. Baiklah dan mungkin Anda adalah manajer Doodads dan Anda hanya perlu melihat pelanggan yang memiliki nilai dan Doodads tertentu. Jadi saya pergi ke sel ajaib, menyalakan Filter dan kemudian di bawah Doodads saya bisa meminta item yang lebih besar dari nol. Klik OK. Baiklah, jenis Pemfilteran itu tidak akan mungkin dilakukan pada Tabel Pivot biasa, tetapi dimungkinkan menggunakan sel ajaib.
Baiklah sekarang mari kita urungkan daftarnya. Mari matikan Filter ini dan hapus Tabel Pivot, dan jika Anda menggunakan Excel 2013 atau baru, saya akan menunjukkan kepada Anda cara yang sepenuhnya legal untuk mendapatkan total yang benar di bagian bawah. Sisipkan Tabel Pivot, di sini di bagian bawah, mulai di Excel 2013 kotak yang sangat tidak berbahaya ini, kedengarannya tidak terlalu menarik, tambahkan data ini ke Model Data. Itu mengirim data, di balik layar, ke Power Pivot Engine. Buat laporan yang sama persis. Pelanggan di sisi kiri. Pendapatan di jantung Tabel Pivot. Lalu, pergi ke Filter biasa, Filter Nilai 10 teratas. Minta lima teratas. Perhatikan lagi kami memiliki 6,7 juta dolar setelah saya melakukan ini, 3,3 juta dolar tetapi inilah perbedaannya. Saat saya masuk ke Tab Desain, di bawah Subtotal, fitur ini disebut Sertakan Item yang Difilter dalam Total,tidak lagi berwarna abu-abu. Pada Tabel Pivot biasa tidak tersedia. Kami mendapatkan tanda bintang kecil di sana dan itu adalah total dari semuanya. Baiklah, sekarang tentu saja itu hanya berfungsi di Excel 2013 atau yang lebih baru.
Baiklah, perlu enam minggu bagi saya untuk mengeluarkan seluruh buku ini di sini di YouTube. Ada banyak tip bagus di sini. Kiat yang bisa mulai menghemat waktu Anda, segera. Beli seluruh bukunya sekarang dan Anda akan memiliki akses ke semua 40, Sebenarnya lebih dari 40 tip. Tombol pintas Excel. Segala macam hal hebat dalam buku ini.
Baiklah, rekap. Jadi ketika kita melakukan Filter 10 teratas Tabel Pivot, ini memberi kita total tetapi hanya baris yang terlihat, bukan hal-hal yang difilter. Ya jika kita pergi ke tab kedua dan mencari Subtotal, Item Tersaring, dan Total, warnanya menjadi abu-abu, tetapi ada cara aneh untuk mengaktifkan Filter Data Lama dari sel ajaib. Sel judul terakhir, pindah satu sel ke kanan, Anda tidak dapat menggunakan Filter dan Tabel Pivot, tetapi jika Anda pergi ke sel ajaib, mereka lupa untuk mengubahnya menjadi abu-abu. Sekarang di Filter Angka, Anda meminta enam teratas untuk mendapatkan lima teratas, ditambah total keseluruhan. Juga berguna untuk memfilter ke Item Pivot tertentu: Doodad, apa pun yang memiliki lebih dari 0 di Doodad atau 5 Doodad teratas. Excel 2013 atau yang lebih baru, ada cara berbeda untuk mendapatkan Total Benar.Centang kotak itu untuk Model Data dan kemudian sertakan Item yang Difilter dalam Total akan tersedia. Anda mendapatkan total dengan tanda bintang. Dan terima kasih kepada Dan di Philadelphia yang menunjukkan kepada saya di salah satu Seminar Power Excel saya, lebih dari sepuluh tahun yang lalu, dan memberi saya trik kecil yang hebat ini. Sebuah cara bagi Filter untuk menyelinap melalui Dinding Tabel Pivot Klub. Mereka biasanya tidak mengizinkan Filter Otomatis itu.
Hei, saya ingin mengucapkan terima kasih sudah mampir. Sampai jumpa di lain waktu, untuk netcast lain dari MRExcel.
Unduh berkas
Unduh file contoh di sini: Podcast1999.xlsx
