Pivot tablo İlk 10 Filtresi, görünür satırların toplamını verir
Pivot tablolar, bir İlk 10 filtresi sunar. Bu harika. Esnektir. Ama bundan nefret ediyorum ve size nedenini söyleyeceğim.
İşte müşteriye göre geliri gösteren bir pivot tablo. Toplam gelir 6.7 milyon dolar.
Ya yöneticim bir akvaryum balığı kadar dikkat süresine sahipse ve yalnızca ilk beş müşteriyi görmek isterse?
Başlamak için, A3'teki açılır menüyü açın ve Değer Filtreleri, İlk 10'u seçin.
Süper esnek Top 10 Filter iletişim kutusu Üst / Alt'a izin verir. 10, 5 veya başka bir sayı yapabilir. İlk beş öğeyi, en iyi% 80'i veya 5 milyon dolara ulaşmak için yeterli müşteri isteyebilirsiniz.
Ancak sorun şudur: Ortaya çıkan rapor, herkesin toplamı yerine beş müşteriyi ve bu müşterilerin toplamını gösterir.
Ama İlk olarak, Otomatik Filtreleme Hakkında Birkaç Önemli Söz
Bunun alışılmadık bir soru gibi göründüğünün farkındayım. Normal bir veri kümesinde Filtre açılır listelerini açmak istiyorsanız, bunu nasıl yaparsınız? İşte gerçekten yaygın olan üç yöntem:
- Verilerinizde bir hücre seçin ve Veri sekmesindeki Filtre simgesini tıklayın.
- Ctrl + * ile tüm verilerinizi seçin ve Veri sekmesindeki Filtre simgesine tıklayın.
- Verileri tablo olarak biçimlendirmek için Ctrl + T tuşlarına basın.
Bunlar gerçekten iyi üç yol. Bunlardan herhangi birini bildiğiniz sürece, başka bir yol bilmenize kesinlikle gerek yoktur. Ancak, filtreyi açmanın inanılmaz derecede belirsiz ama sihirli bir yolu:
- Başlık sıranıza gidin, en sağdaki başlık hücresine gidin. Bir hücre sağa gitme. Bilinmeyen bir nedenden ötürü, bu hücrenin içindeyken Filtre simgesine tıkladığınızda, Excel solunuzdaki veri kümesini filtreler. Bunun neden işe yaradığına dair hiçbir fikrim yok. Aslında bahsetmeye değmez çünkü Filtre açılır listelerini açmanın gerçekten iyi üç yolu vardır. Bu hücreye Sihirli hücre diyorum.
Ve şimdi, Özet Tablolara Dön…
Bu nedenle, bir pivot tablodayken Otomatik Filtreleri kullanamayacağınızı söyleyen bir kural var. Aşağıya bakınız? Pivot tabloda bir hücre seçtiğim için Filtre simgesi gri.
Microsoft'un bunu neden gri yaptığını hiç düşünmemiştim. Otomatik Filtre ve Pivot Tablonun bir arada olamayacağını söyleyen dahili bir şey olmalıdır. Yani, Excel ekibinde Filtre simgesini grileştirmekten sorumlu biri var. Bu kişi Büyülü hücreyi hiç duymamış Pivot tablodan bir hücre seçin ve Filtre grileşir. Pivot tablonun dışına tıklayın ve Filtre yeniden etkinleştirilir.
Fakat bekle. Ya sana bahsettiğim Sihirli hücre? Son başlığın sağındaki hücreye tıklarsanız, Excel Filtre simgesini grileştirmeyi unutur!
Elbette, Excel, pivot tablonuzun en üst satırına Otomatik Filtre açılır listeleri ekler. Ve Otomatik Filtre, pivot tablo filtrelerinden farklı şekilde çalışır. Gelir açılır menüsüne gidin ve Sayı Filtreleri, İlk 10'u seçin…
İlk 10 Otomatik Filtre iletişim kutusunda İlk 6 Öğe'yi seçin. Bu bir yazım hatası değil…. Beş müşteri istiyorsanız 6'yı seçin. 10 müşteri istiyorsanız 11'i seçin.
Otomatik Filtre için, genel toplam satırı, verilerdeki en büyük öğedir. İlk beş müşteri, verilerde 2'den 6'ya kadar olan pozisyonlarda yer alıyor.
Dikkat
Açıkça, bu numara ile Excel kumaşında bir delik açıyorsunuz. Daha sonra temel verileri değiştirir ve pivot tablonuzu yenilerseniz, Excel filtreyi yenilemeyecektir, çünkü Microsoft'un bildiği kadarıyla pivot tabloya filtre uygulamanın bir yolu yoktur!
Not
Amacımız bunu Microsoft'tan bir sır olarak saklamak çünkü bu oldukça güzel bir özellik. Bir süredir "kırıldı", bu yüzden şimdiye kadar ona güvenebilecek birçok insan var.
Excel 2013 + 'da Tamamen Yasal Bir ÇözümSize ilk beş müşteriyi ancak tüm müşterilerden gelen toplamı gösteren bir pivot tablo istiyorsanız, verilerinizi Excel'in dışına taşımanız gerekir. Excel 2013 veya 2016'nız varsa, bunu yapmanın çok uygun bir yolu vardır. Bunu size göstermek için orijinal pivot tabloyu sildim. Ekle, Pivot Tablo'yu seçin. Tamam'ı tıklamadan önce, Bu Verileri Veri Modeline Ekle yazan kutuyu seçin.
Pivot tablonuzu normal şekilde oluşturun. Değer Filtreleri, İlk 10'u seçmek ve ilk beş müşteriyi istemek için A3'teki açılır menüyü kullanın. Pivot tablodaki bir hücre seçiliyken, şeritteki Tasarım sekmesine gidin ve Alt Toplamlar açılır menüsünü açın. Açılır listedeki son seçim Filtrelenmiş Öğeleri Toplamlara Dahil Et seçeneğidir. Normalde bu seçim gri renktedir. Ancak veriler normal bir özet önbellek yerine Veri Modeli'nde depolandığından, bu seçenek artık kullanılabilir.
Filtrelenen Öğeleri Toplama Dahil Et seçeneğini seçin ve Genel Toplamınız artık bir yıldız işareti ve tüm verilerin toplamını içerir.
Bu numara bana aslen Philadelphia'daki seminerimde Dan'den geldi. Miguel Caballero'ya bu özelliği önerdiği için teşekkürler.
Video izle
- Pivot tablo İlk 10 Filtresi, görünür satırların toplamını verir
- Filtrelenmiş Öğeleri Toplamlara Dahil Et Gri Renkte
- Veri Filtresini sihirli hücreden çağırmanın garip yolu
- Özet tablolarda Veri Filtrelerine izin verilmez
- Excel, Veri Filtresini sihirli hücreden grileştiremiyor
- İlk 6'dan ilk 5'e artı Genel Toplam almasını isteyin
- Belirli bir pivot öğesine göre filtrelemek için kullanışlıdır
- Excel 2013 veya daha yenisi: Gerçek Toplamı Almanın Farklı Yolu
- Verilerinizi Veri Modeli aracılığıyla gönderin
- Filtrelenmiş Öğeleri Toplamlara Dahil Et seçeneği mevcut olacak
- Yıldız işaretiyle Total alın
- Bu numarayı 10+ yıl önce Philadelphia'daki Dan'den öğrendim.
Video Metni
Podcast için Excel'i Öğrenin, Bölüm 1999 - Özet Tablo Gerçek İlk Beşi
Tüm bu kitabın podcast'ini yapıyorum. Bir oynatma listesi var, o oynatma listesini takip etmek için sağ üst köşedeki I düğmesine tıklayın. İnternet yayınına tekrar hoş geldiniz. Ben Bill Jelen.
Pekala, bir Pivot Tablo oluşturacağız ve tüm müşterileri değil, sadece ilk beş müşteriyi göstermek istiyoruz. INSERT, Pivot Tablo. Tamam, Müşteri'yi sol tarafa ve Gelir'e koyacağım. Pekala, işte 6.7 milyon dolar olarak kaydedilen tüm müşteri listemiz. Excel, ilk beşi yapmayı kolaylaştırır. Satır Etiketleri, Değer Filtreleri, ilk 10'a gidin. En üstte olması gerekmez. Üstte veya altta olabilir. Beş olmak zorunda değil. Yirmi, kırk olabilir, her ne olabilir. En yüksek yüzde seksen, bana üç milyon doları veya dört milyon doları bulmaya yetecek kadar kayıt verin, ama işte başlıyoruz. İlk beş öğe. Şimdi 6,7 milyon doları hatırlayın, Tamam'ı tıklayın ve buradaki en büyük sorunum, bu genel toplamın 6,7 milyon olmamasıdır. Bunu satış başkan yardımcısına verdiğimde çıldıracak ve şöyle diyecek, bir saniye bekle,3,3 milyon dolardan fazla yaptığımı biliyorum. Doğru, bu yüzden geri alacağız, geri alacağız ve orijinal verilere geri döneceğiz.
Şimdi Philadelphia'daki Power Excel Seminerlerimden birinde öğrendiğim bir sonraki numara. İkinci sıradaki Dan adında bir adam bunu bana gösterdi. Bana bu numarayı on yıldan fazla bir süre önce gösterdi ve önce Filtreler hakkında konuşmalıyız. Normalde, normal Filtreyi, bu Filtreyi kullanacaksanız, veri kümenizdeki herhangi bir hücreyi seçip Filtre Simgesini tıklarsınız veya bazı kişiler tüm Veri Kümesini seçer, CONTROL * ve Filtre Simgesini tıklar, ama üçüncü bir yol var. Kimsenin umursamadığı bir yol. En son Başlık Hücresine giderseniz, benim durumumda, bu L1'deki Maliyet ve bir hücre sağa gidin. Buna sihirli hücre diyorum, neden olduğu hakkında hiçbir fikrim yok ama bilinmeyen bir nedenle, bu hücreden bitişik Veri Kümesini filtreleyebilirim. Pekala, bu garip bir yol ve kimsenin umurunda değil.
Doğru, çünkü bir Filtreyi çağırmanın gerçekten iyi iki yolu daha var, kimsenin sihirli hücre hakkında bilgi sahibi olmasına gerek yok, ama işte mesele şu, bir Pivot Tablonun içine bakın, gri renkte. Bu Filtreleri kullanmanıza izin verilmiyor. Bu kurallara KARŞI. Şimdi, eğer buraya gelirsem, Filtreyi kullanmaktan memnuniyet duyarım ama içeriden not alıyorlar. Bunu grileştiren kişinin kim olduğunu bilmiyorum, ama sihirli hücre hakkındaki küçük konuşmamı hiç duymamışlar, çünkü en son Heading Cell'e gidip bir hücre sağa gidersem, şuna bak, Filtreyi grileştirmeyi unutuyorlar ve şimdi eski Otomatik Filtreleri Pivot Tabloya ekledim. Bu yüzden buraya geliyorum, Sayı Filtrelerine gidiyorum, bu Değer Filtrelerinden farklı. Hâlâ İlk On olarak adlandırılıyor. Biraz farklı, ilk beşi isteyeceğim, ilk altı değil.İlk altı, çünkü bu Filtreleme için Genel Toplam sadece başka bir satırdır ve Genel Toplam en büyük maddedir ve sonra 2'den 6'ya kadar olan maddeler sorulduğunda, ilk beş maddeyi alırım.
Pekala, işte buradayız. Bize en iyi beş öğeyi ve herkesin gerçek toplamını veren harika bir Filtre kesmesi. Pekala, birkaç şey. Sihirli hücreyi unutma. Pekala, sihirli hücreye geri dönmediğiniz sürece bu Filtreyi kapatmanın bir yolu yok. Pekala, sihirli hücreyi hatırlaman gerekiyor. Ayrıca, temeldeki verileri değiştirirseniz ve Pivot Tabloyu yenilerseniz, Filtreyi yenilemeyeceklerdir çünkü Microsoft'un bildiği kadarıyla, bir Filtreniz olamaz.
Bu başka şeyler için kullanışlıdır. Bazen zirveye çıkan ürünlerimiz oluyor. Buraya tablo şeklinde gidelim. Gerekli değil, sadece gerçek başlıklar almayı seviyorum. Gizmo, Widget, Gadget'lar, Doodads. Pekala ve belki de Doodads'ın yöneticisisiniz ve sadece belirli bir değeri olan müşterileri ve Doodads'ı görmeniz gerekiyor. Bu yüzden sihirli hücreye gidiyorum, Filtreyi açıyorum ve ardından Doodads altında sıfırdan büyük olan öğeleri isteyebilirim. Tamam'ı tıklayın. Pekala, bu tür bir Filtreleme normal bir Pivot Tabloda mümkün değildir, ancak sihirli hücre kullanılarak mümkündür.
Pekala, şimdi listeyi geri alalım. Bu Filtreyi kapatalım ve Pivot Tabloyu kaldıralım ve Excel 2013'teyseniz veya yeniyseniz, alt kısımda doğru toplamı elde etmenin tamamen yasal bir yolunu göstereceğim. Excel 2013'ten başlayarak Pivot Tabloyu aşağıya yerleştirin, bu çok zararsız kutu kulağa pek heyecan verici gelmiyor, bu verileri Veri Modeli'ne ekleyin. Bu, verileri arka planda Power Pivot Engine'e gönderir. Tam olarak aynı raporu oluşturun. Sol taraftaki müşteriler. Pivot Tablonun kalbindeki gelir. Ardından, normal Filtreler, Değer Filtreleri baş 10'a gidin. İlk beşi isteyin. Tekrar dikkat edin, bunu yaptıktan sonra 6.7 milyon dolarımız var, 3.3 milyon dolar ama işte fark burada. Tasarım Sekmesine gittiğimde, Alt Toplamlar altında, Filtrelenmiş Öğeleri Toplamlara Dahil Et adlı bu özellik,artık grileşmiyor. Normal bir Pivot Tablo'da mevcut değildir. Orada küçük bir yıldız işareti alıyoruz ve bu her şeyin toplamı. Pekala, şimdi elbette bu yalnızca Excel 2013 veya daha yeni sürümlerde çalışıyor.
Pekala, bu kitabın tamamını YouTube'da yayınlamam altı haftamı alacak. Burada çok sayıda iyi ipucu var. Size hemen zaman kazandırmaya başlayabilecek ipuçları. Kitabın tamamını hemen satın alın ve 40'ın tamamına erişebileceksiniz. Aslında bu, 40'tan çok daha fazla ipucu. Excel kısayol tuşları. Bu kitaptaki her türden harika şeyler.
Pekala, özet. Dolayısıyla, Pivot Tablosu üst 10 Filtresini yaptığımızda, bize toplamı verir, ancak filtrelediği şeyleri değil, yalnızca görünen satırları verir. Evet, ikinci sekmeye gidip Alt Toplamları, Filtrelenmiş Öğeleri ve Toplamları ararsak, gri görünür, ancak Eski Veri Filtresini sihirli hücreden çağırmanın tuhaf bir yolu vardır. En son başlık hücresinde, bir hücre sağa gidin, Filtreleri ve Özet Tabloları kullanamazsınız, ancak sihirli hücreye giderseniz, griye çevirmeyi unuturlar. Şimdi Sayı Filtresinde, ilk altıdan ilk beşi ve genel toplamı almasını istiyorsunuz. Ayrıca belirli bir Pivot Öğesine filtre uygulamak için de kullanışlıdır: Doodads, Doodads veya en iyi 5 Doodads'da 0'dan büyük olan her şey. Excel 2013 veya daha yenisi, Gerçek Toplamı almanın farklı bir yolu vardır.Veri Modeli için bu kutuyu işaretleyin ve ardından Filtrelenmiş Öğeleri Toplamlara dahil edin. Toplamı bir yıldız işaretiyle alırsınız. Ve bana on yıldan fazla bir süre önce Power Excel Seminerlerimden birinde göstermiş olan ve bana bu harika küçük numarayı veren Philadelphia'daki Dan'e teşekkürler. Filtrenin Club Pivot Masa Duvarından gizlice geçmesinin bir yolu. Normalde bu Otomatik Filtreye izin vermezler.
Hey, uğradığın için teşekkür ederim. Bir dahaki sefere MRExcel'den başka bir internet yayını için görüşürüz.
Dosyayı indir
Örnek dosyayı buradan indirin: Podcast1999.xlsx
