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. En büyük müşteri olan Roto-Rooter'ın toplam gelirin% 9'u olduğuna dikkat edin.
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. Daha önce toplamın% 9'unu oluşturan Roto-Rooter, yeni toplamın% 23'ünü oluşturuyor.
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 veya Ctrl + Shift + L tuşlarına bası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:
- Üstbilgi satırınıza gidin ve ardından 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 Geri Dön
Bir pivot tablodayken Otomatik Süzgeç kullanamayacağınızı söyleyen bir kural vardır. Aşağıya bakınız? Pivot tabloda bir hücre seçtiğim için Filtre simgesi gri.
Microsoft'un bunu neden vurguladığını bilmiyorum. Otomatik Filtre ve pivot tablonun bir arada bulunamayacağını söyleyen dahili bir şey olmalıdır. Yani, Excel ekibinde Filtre simgesini grileştirmekten sorumlu biri var. O kişi sihirli 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, bir pivot tablo filtresinden farklı şekilde çalışır. Gelir açılır menüsüne gidin ve Sayı Filtreleri, İlk 10… öğesini 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 temeldeki verileri değiştirir ve pivot tablonuzu yenilerseniz, Excel filtreyi yenilemeyecektir çünkü Microsoft'un bildiği kadarıyla, bir pivot tabloya filtre uygulamanın bir yolu yoktur!
Not
Amacımız bunu Microsoft'tan bir sır olarak saklamak çünkü 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üm
Size 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. Windows'ta çalışan Excel 2013 veya daha yenisi 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'a tıklamadan önce, Bu Verileri Veri Modeline Ekle onay kutusunu 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 Şerit'teki 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 Toplamlara Dahil Et seçeneğini seçin ve Genel Toplamınız artık aşağıda gösterildiği gibi bir yıldız işareti ve tüm verilerin toplamını içerir.
Bu sihirli hücre numarası bana Dan'den Philadelphia'daki seminerimde geldi ve 15 yıl sonra Cincinnati'deki seminerimden farklı bir Dan tarafından tekrarlandı. Miguel Caballero'ya bu özelliği önerdiği için teşekkürler.
