Birden çok veri aralığında pivot tablo

Sorunun formülasyonu

Özet tablolar, Excel'deki en şaşırtıcı araçlardan biridir. Ancak şimdiye kadar, ne yazık ki, Excel sürümlerinden hiçbiri, örneğin farklı sayfalarda veya farklı tablolarda bulunan birkaç ilk veri aralığı için bir özet oluşturmak gibi anında bu kadar basit ve gerekli bir şeyi yapamaz:

Başlamadan önce, birkaç noktaya açıklık getirelim. Önsel olarak, verilerimizde aşağıdaki koşulların karşılandığına inanıyorum:

• Tablolarda herhangi bir veri içeren herhangi bir sayıda satır olabilir, ancak aynı başlığa sahip olmaları gerekir.
• Kaynak tabloların bulunduğu sayfalarda fazladan veri olmamalıdır. Bir sayfa – bir masa. Kontrol etmek için bir klavye kısayolu kullanmanızı öneririm Ctrl+Son, sizi çalışma sayfasında en son kullanılan hücreye taşır. İdeal olarak, bu veri tablosundaki son hücre olmalıdır. Eğer üzerine tıkladığınızda Ctrl+Son tablonun sağındaki veya altındaki herhangi bir boş hücre vurgulanır - tablodan sonra sağdaki bu boş sütunları veya tablonun altındaki satırları silin ve dosyayı kaydedin.

Yöntem 1: Power Query kullanarak bir özet için tablolar oluşturun

Excel için 2010 sürümünden başlayarak, herhangi bir veriyi toplayıp dönüştürebilen ve ardından bir pivot tablo oluşturmak için kaynak olarak verebilen ücretsiz bir Power Query eklentisi vardır. Bu eklenti yardımıyla sorunumuzu çözmek hiç de zor değil.

İlk önce Excel'de yeni bir boş dosya oluşturalım - içinde montaj yapılacak ve ardından içinde bir pivot tablo oluşturulacak.

Daha sonra sekmede Veri (Excel 2016 veya sonraki bir sürümüne sahipseniz) veya sekmesinde Güç Sorgu (Excel 2010-2013'e sahipseniz, komutu seçin) Sorgu Oluştur – Dosyadan – Excel (Veri Al — Dosyadan — Excel) ve toplanacak tablolarla kaynak dosyayı belirtin:

Görünen pencerede herhangi bir sayfayı seçin (hangisi olduğu önemli değil) ve aşağıdaki düğmeye basın değişim (Edit):

Power Query Sorgu Düzenleyicisi penceresi Excel'in üstünde açılmalıdır. Paneldeki pencerenin sağ tarafında Parametreleri Talep Et ilki hariç otomatik olarak oluşturulan tüm adımları sil – Kaynak (Kaynak):

Şimdi tüm sayfaların genel bir listesini görüyoruz. Dosyada veri sayfalarına ek olarak başka yan sayfalar varsa, o zaman bu adımda görevimiz, tablo başlığındaki filtreyi kullanan diğerleri hariç, yalnızca bilgilerin yüklenmesi gereken sayfaları seçmektir:

Sütun hariç tüm sütunları sil Veribir sütun başlığına sağ tıklayıp seçerek Diğer sütunları sil (Kaldırmak diğer sütunlar):

Ardından, sütunun üst kısmındaki çift oka tıklayarak toplanan tabloların içeriğini genişletebilirsiniz (onay kutusu Önek olarak orijinal sütun adını kullan Kapatabilirsin):

Her şeyi doğru yaptıysanız, bu noktada alt alta toplanan tüm tabloların içeriğini görmelisiniz:

Düğme ile ilk satırı tablo başlığına yükseltmek için kalır Başlık olarak ilk satırı kullan (İlk satırı başlık olarak kullanın) çıkıntı Ana Sayfa (Ev) ve bir filtre kullanarak verilerden yinelenen tablo başlıklarını kaldırın:

Komutla yapılan her şeyi kaydedin Kapat ve yükle – Kapat ve yükle… (Kapat ve Yükle — Kapat ve Yükle…) çıkıntı Ana Sayfa (Ev)ve açılan pencerede seçeneği seçin Yalnızca bağlantı (Yalnızca Bağlantı):

Her şey. Sadece bir özet oluşturmak için kalır. Bunu yapmak için sekmeye gidin Ekle – Özet Tablo (Ekle — Özet Tablo), seçeneği seçin Harici veri kaynağı kullan (Harici veri kaynağı kullanın)ve ardından düğmesine tıklayarak Bağlantısını seçin, bizim isteğimiz. Pivotun daha fazla oluşturulması ve yapılandırılması, ihtiyacımız olan alanları satırlar, sütunlar ve değerler alanına sürükleyerek tamamen standart bir şekilde gerçekleşir:

Gelecekte kaynak veriler değişirse veya birkaç mağaza sayfası daha eklenirse, sorguyu ve özetimizi şu komutu kullanarak güncellemek yeterli olacaktır. Hepsini yenile çıkıntı Veri (Veri — Tümünü Yenile).

Yöntem 2. Bir makroda UNION SQL komutuyla tabloları birleştiriyoruz

Sorunumuzun başka bir çözümü, komutu kullanarak pivot tablo için bir veri seti (önbellek) oluşturan bu makro ile temsil edilir. BİRLİK SQL sorgu dili. Bu komut dizide belirtilen tüm tabloları birleştirir Sayfa Adları kitabın sayfalarını tek bir veri tablosuna dönüştürün. Yani, farklı sayfalardan bir sayfaya fiziksel olarak kopyalayıp yapıştırmak yerine, aynısını bilgisayarın RAM'inde yapıyoruz. Ardından makro, verilen ada sahip yeni bir sayfa ekler (değişken Sonuç SayfasıAdı) ve toplanan önbelleğe dayalı olarak tam teşekküllü (!) bir özet oluşturur.

Makro kullanmak için, sekmedeki Visual Basic düğmesini kullanın. geliştirici (Geliştirici) veya klavye kısayolu Ara Toplam+F11. Ardından menüden yeni bir boş modül ekliyoruz Ekle – Modül ve aşağıdaki kodu oraya kopyalayın:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetNames As Variant Dim SheetsNames As Variant 'sayfa adı, sonuçta ortaya çıkan pivotun görüntüleneceği sayfa adı ResultSheetName = "sayfanın Pivot" dizisi kaynak tabloları olan isimler SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'Etkileşimli Sayfa Adları'ndan tablolar için bir önbellek oluşturuyoruz ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) For i = LBound (SheetsNames) UBound(SheetsNames) için arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Sonraki i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Genişletilmiş Özellikler=""Excel 8.0;" ""), vbNullString ) İle Bitir 'Sonraki pivot tabloyu görüntülemek için sayfayı yeniden oluşturun Hatada Sonraki Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'bu sayfada oluşturulan önbellek özetini görüntüle Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotTable TableDestination:=w Set" objPivotCache = Hiçbir Şey Aralığı ("A3"). End Sub ile End'i seçin    

Bitmiş makro daha sonra bir klavye kısayoluyla çalıştırılabilir Ara Toplam+F8 veya sekmedeki Makrolar düğmesi geliştirici (Geliştirici — Makrolar).

Bu yaklaşımın eksileri:

• Önbelleğin kaynak tablolarla bağlantısı olmadığı için veriler güncellenmez. Kaynak verileri değiştirirseniz, makroyu yeniden çalıştırmanız ve özeti yeniden oluşturmanız gerekir.
• Sayfa sayısını değiştirirken makro kodunu (dizi Sayfa Adları).
  

Ancak sonunda, farklı sayfalardan çeşitli aralıklar üzerine kurulmuş gerçek bir tam teşekküllü pivot tablo elde ediyoruz:

Voilà!

Teknik not: makroyu çalıştırırken "Sağlayıcı kayıtlı değil" gibi bir hata alırsanız, büyük olasılıkla Excel'in 64 bit sürümüne sahipsiniz veya Office'in eksik bir sürümü yüklü (Erişim yok). Durumu düzeltmek için makro kodundaki parçayı değiştirin:

	 Sağlayıcı=Microsoft.Jet.OLEDB.4.0;  

için:

	Sağlayıcı=Microsoft.ACE.OLEDB.12.0;  

Ve Microsoft web sitesinden Access'ten ücretsiz veri işleme motorunu indirip yükleyin – Microsoft Access Veritabanı Motoru 2010 Yeniden Dağıtılabilir

Yöntem 3: Özet Tablo Sihirbazını Excel'in Eski Sürümlerinden Konsolide Etme

Bu yöntem biraz modası geçmiş, ancak yine de bahsetmeye değer. Resmi olarak, 2003'e kadar olan tüm sürümlerde, PivotTable Sihirbazı'nda "birkaç konsolidasyon aralığı için bir pivot oluşturma" seçeneği vardı. Ancak, bu şekilde oluşturulmuş bir rapor, ne yazık ki, gerçek tam teşekküllü bir özetin acınası bir görünümünden ibaret olacak ve geleneksel pivot tabloların pek çok “yongasını” desteklemeyecektir:

Böyle bir pivotta alan listesinde sütun başlıkları yoktur, esnek yapı ayarı yoktur, kullanılan işlevler kümesi sınırlıdır ve genel olarak tüm bunlar bir pivot tabloya çok benzemez. Belki de bu nedenle, 2007'den başlayarak Microsoft, pivot tablo raporları oluştururken bu işlevi standart iletişim kutusundan çıkardı. Artık bu özellik yalnızca özel bir düğme aracılığıyla kullanılabilir Özet Tablo Sihirbazı(Pivot Tablo Sihirbazı), istenirse aracılığıyla Hızlı Erişim Araç Çubuğuna eklenebilen Dosya – Seçenekler – Hızlı Erişim Araç Çubuğunu Özelleştir – Tüm Komutlar (Dosya — Seçenekler — Hızlı Erişim Araç Çubuğunu Özelleştir — Tüm Komutlar):

Eklenen düğmeye tıkladıktan sonra, sihirbazın ilk adımında uygun seçeneği seçmeniz gerekir:

Ardından bir sonraki pencerede sırayla her aralığı seçin ve genel listeye ekleyin:

Ama yine de, bu tam bir özet değil, bu yüzden ondan çok fazla bir şey beklemeyin. Bu seçeneği yalnızca çok basit durumlarda önerebilirim.

• PivotTable'larla Rapor Oluşturma
• PivotTable'larda hesaplamaları ayarlama
• Makrolar nedir, nasıl kullanılır, VBA kodunun nereye kopyalanacağı vb.
• Birden çok sayfadan bire veri toplama (PLEX eklentisi)