Aynı Anda Birden Fazla PivotTable'ı Filtreleme

Microsoft Excel'de karmaşık raporlar ve özellikle gösterge tabloları oluştururken, çoğu zaman aynı anda birkaç pivot tabloyu aynı anda filtrelemek gerekir. Bunun nasıl uygulanabileceğini görelim.

Yöntem 1: Aynı veri kaynağındaki özetleri filtrelemek için Genel Dilimleyici

Pivotlar tek bir kaynak veri tablosu temelinde oluşturulmuşsa, en kolay yol onları aynı anda filtrelemek için kullanmaktır. Bölüm tüm pivot tablolara aynı anda bağlanan bir grafik düğme filtresidir.

Eklemek için özetteki ve sekmedeki herhangi bir hücreyi seçin. Analiz takım seç Dilim Yapıştır (Analiz et — Dilimleyici ekle). Açılan pencerede, verileri filtrelemek istediğiniz sütunların kutularını işaretleyin ve OK:

Oluşturulan dilimleyici, varsayılan olarak yalnızca oluşturulduğu pivotu filtreleyecektir. Ancak, düğmeyi kullanarak Bağlantıları Bildir (Rapor bağlantıları) çıkıntı Dilim (Dilimler) filtrelenmiş tablolar listesine başka özet tabloları da kolayca ekleyebiliriz:

Yöntem 2. Farklı kaynaklardaki özetleri filtrelemek için genel dilim

Pivotlarınız bire göre değil, farklı kaynak veri tablolarına göre oluşturulmuşsa, yukarıdaki yöntem çalışmayacaktır, çünkü pencerede Bağlantıları Bildir yalnızca aynı kaynaktan oluşturulan özetler görüntülenir.

Ancak, Veri Modelini kullanırsanız bu sınırlamayı kolayca aşabilirsiniz (bu makalede ayrıntılı olarak tartıştık). Tablolarımızı Model'e yükler ve onları oraya bağlarsak, filtreleme aynı anda her iki tabloya da uygulanacaktır.

Diyelim ki girdi verisi olarak satış ve nakliye maliyetleri için iki tablomuz var:

Her biri için kendi özetimizi oluşturma ve ardından ortak bir kesime sahip şehirlere göre aynı anda filtreleme göreviyle karşı karşıya olduğumuzu varsayalım.

Aşağıdakileri yapıyoruz:

1. Klavye Kısayoluyla Orijinal Tablolarımızı Dinamik Akıllı Tablolara Dönüştürme Ctrl+T veya komutlar Ana Sayfa – Tablo olarak biçimlendir (Ana Sayfa — Tablo Olarak Biçimlendir) ve onlara isim ver tablProdaji и sekmeulaşım çıkıntı Inşaatçı (Tasarım).

2. Düğmeyi kullanarak her iki tabloyu sırayla Modele yükleyin Veri Modeline Ekle Power Pivot sekmesinde.

Bu tabloları Model'de doğrudan bağlamak mümkün olmayacaktır, çünkü Power Pivot yalnızca bire-çok ilişkileri desteklerken, yani tablolardan birinin bağlantı oluşturduğumuz sütunda kopya olmamasını gerektirir. Sahada her iki tabloda da aynısı var Şehir tekrarlar var. Bu nedenle, her iki tablodan da benzersiz şehir adlarının bir listesini içeren başka bir ara arama tablosu oluşturmamız gerekiyor. Bunu yapmanın en kolay yolu, 2016 sürümünden bu yana Excel'de yerleşik olarak bulunan Power Query eklenti işlevidir (ve Excel 2010-2013 için Microsoft web sitesinden ücretsiz olarak indirilir).

3. “Akıllı” tablonun içindeki herhangi bir hücreyi seçtikten sonra, onları tek tek Power Query'de düğmesiyle yüklüyoruz. Tablodan/aralıktan çıkıntı Veri (Veri — Tablodan/aralıktan) ve ardından Power Query penceresinde öğesini seçin. Ana takım Kapat ve yükle – Kapat ve yükle (Ana Sayfa — Kapat&Yükle — Kapat&Yükle…) ve içe aktarma seçeneği Sadece bir bağlantı oluşturun (Yalnızca bağlantı oluşturun):

4. komutu ile her iki tabloyu tek bir tablo haline getiriyoruz. Veri – Sorguları Birleştir – Ekle (Veri — Sorguları birleştir — Ekle). Başlıkta aynı ada sahip sütunlar birbirinin altına sığacaktır (bir sütun gibi). Şehir) ve eşleşmeyenler farklı sütunlara yerleştirilecektir (ancak bu bizim için önemli değildir).

5. Sütun hariç tüm sütunları sil Şehirbaşlığına sağ tıklayıp komutu seçerek Diğer sütunları sil (Diğer sütunları kaldırın) ve ardından sütun başlığına tekrar sağ tıklayıp komutu seçerek tüm yinelenen şehir adlarını kaldırın Yinelemeleri Kaldır (Yinelenenleri kaldırın):

6. Oluşturulan referans listesi aracılığıyla Veri Modeline yüklenir. Ana Sayfa — Kapat ve yükle — Kapat ve yükle (Ana Sayfa — Kapat&Yükle — Kapat&Yükle…) ve seçeneği seçin Sadece bir bağlantı oluşturun (Yalnızca bağlantı oluşturun) ve en önemli şey! - onay kutusunu aç Bu verileri veri modeline ekle (Bu verileri Veri Modeline ekleyin):

7. Artık Power Pivot penceresine dönebiliriz (sekme PowerPivot - buton Yönetim), çevirmek Grafik Görünümü (Diyagram görünümü) ve oluşturulan ara şehir dizini aracılığıyla satış ve nakliye maliyetleri tablolarımızı birbirine bağlayın (tablolar arasında alanları sürükleyerek):

8. Artık oluşturulan model için gerekli tüm pivot tabloları düğmesini kullanarak oluşturabilirsiniz. özet tablo (Pivot tablo) on Ana (Ev) sekmesini Power Pivot penceresinde ve herhangi bir pivotta herhangi bir hücreyi seçerek, sekmede Analiz dilim ekle düğmesi Dilim Yapıştır (Analiz — Dilimleyici Ekle) ve liste kutusunda dilimlemeyi seçin Şehir eklenen dizinde:

Şimdi, tanıdık düğmeye tıklayarak Bağlantıları Bildir on Dilim sekmesi (Dilimleyici — Bağlantıları bildir) tüm özetimizi göreceğiz, çünkü bunlar artık ilgili kaynak tablolar üzerine inşa edilmiştir. Eksik onay kutularını etkinleştirmek ve üzerine tıklamak için kalır. OK – ve dilimleyicimiz seçilen tüm pivot tabloları aynı anda filtrelemeye başlayacaktır.