Şu anda, Excel Tablolarını veya Dinamik Adlandırılmış Aralıkları kullanarak pivot tabloları dinamik olarak değiştirebilir veya güncelleyebiliriz. Ancak bu teknikler kusursuz değildir. Yine de pivot tabloyu manuel olarak yenilemeniz gerekecek. Binlerce satır ve sütun içeren büyük verileriniz varsa, excel tabloları size pek yardımcı olmaz. Bunun yerine dosyanızı ağırlaştıracaktır. Yani geriye kalan tek yol VBA.
Bu yazımızda pivot tablomuzun veri kaynağını otomatik olarak değiştirmesini nasıl sağlayabileceğimizi öğreneceğiz. Başka bir deyişle, kaynak tablolara eklenen yeni satırları ve sütunları dinamik olarak dahil etmek ve pivot tablodaki değişikliği anında yansıtmak için veri kaynağını manuel olarak değiştirme işlemini otomatikleştireceğiz.
Kaynak Veri Sayfasına Kod Yaz
Bunun tamamen otomatik olmasını istediğimiz için, kod yazmak için çekirdek modül yerine sayfa modüllerini kullanacağız. Bu, çalışma sayfası olaylarını kullanmamıza izin verecektir.
Kaynak veriler ve pivot tablolar farklı sayfalardaysa, kaynak verileri içeren (pivot tablo içermeyen) sayfa nesnesindeki pivot tablo veri kaynağını değiştirmek için VBA kodunu yazacağız.
VB düzenleyicisini açmak için CTRL+F11 tuşlarına basın. Şimdi proje gezginine gidin ve kaynak verileri içeren sayfayı bulun. Üzerine çift tıklayın.
Yeni bir kodlama alanı açılacaktır. Herhangi bir değişiklik görmeyebilirsiniz, ancak artık çalışma sayfası olaylarına erişiminiz var.
Soldaki açılır menüyü tıklayın ve çalışma sayfasını seçin. Soldaki açılır menüden devre dışı bırak'ı seçin. worksheet_deativate kod alanı adına yazılmış boş bir alt göreceksiniz. Dinamik olarak kaynak verileri değiştirme ve pivot tabloyu yenileme kodumuz bu kod bloğuna girecektir. Bu kod, veri sayfasından başka bir sayfaya geçiş yaptığınızda çalışacaktır. Tüm çalışma sayfası olaylarını buradan okuyabilirsiniz.
Artık kodu uygulamaya hazırız.
Pivot Tabloyu Yeni Aralıkla Dinamik Olarak Güncellemek için Kaynak Kodu
Nasıl çalıştığını açıklamak için bir çalışma kitabım var. Bu çalışma kitabı iki sayfa içerir. Sayfa1 değişebilen kaynak verileri içerir. Sayfa2, sayfa2'nin kaynak verilerine bağlı olan pivot tabloyu içerir.
Şimdi bu kodu sayfa1'in kodlama alanına yazdım. Worksheet_Deactivate olayını kullanıyorum, böylece kaynak veri sayfasından her geçiş yaptığımızda bu kod pivot tabloyu güncellemek için çalışır.
Private Sub Worksheet_Deactivate() Dim pt As PivotTable Dim pc As PivotCache Dim source_data As Range lstrow = Cells(Rows.Count, 1).End(xlUp).Row lstcol = Cells(1, Columns.Count).End(xlToLeft). Sütun Kümesi source_data = Aralık(Hücreler(1, 1), Hücreler(lstrow, lstcol)) Küme pc = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data) Küme pt = Sheet2.PivotTables("PivotTable1") pt.ChangePivotCache pc Bitiş Alt
Buna benzer bir çalışma kitabınız varsa bu datayı direk kopyalayabilirsiniz. Bu kodun çalıştığını aşağıda anlattım.
Bu kodun etkisini aşağıdaki gif'te görebilirsiniz.
Bu kod, kaynak verileri otomatik olarak nasıl değiştirir ve özet tabloları nasıl günceller?
Öncelikle worksheet_deactivate olayını kullandık. Bu olay, yalnızca kodu içeren sayfa değiştirildiğinde veya devre dışı bırakıldığında tetiklenir. Yani kod otomatik olarak bu şekilde çalışır.
Şimdi pivot tablonun kaynak verilerini değiştirmek için pivot önbellekteki verileri değiştiriyoruz.
Pivot önbellek kullanılarak bir pivot tablo oluşturulur. Özet önbellek, özet tablo manuel olarak yenilenmedikçe veya kaynak veri aralığı manuel olarak değiştirilinceye kadar eski kaynak verileri içerir.
pt adında pivot tablolar, pc adında pivot önbellek ve source_data adında bir aralık referansları oluşturduk. Kaynak veriler tüm verileri içerecektir.
Tüm tabloyu dinamik olarak veri aralığı olarak almak için son satırı ve son sütunu belirliyoruz.
lstrow = Cells(Rows.Count, 1).End(xlUp).Row
lstcol = Cells(1, Columns.Count).End(xlToLeft).Column
Bu iki sayıyı kullanarak source_data'yı tanımlarız. Kaynak veri aralığının her zaman A1'den başlayacağından eminiz.
kaynak_verilerini ayarla = Aralık(Hücreler(1, 1), Hücreler(lstrow, lstcol))
Artık dinamik olan kaynak verilere sahibiz. Sadece pivot tabloda kullanmamız gerekiyor.
Pivot önbelleğin tüm verileri sakladığını bildiğimiz için bu verileri pivot önbellekte saklarız.
pc ayarla = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data)
Ardından güncellemek istediğimiz pivot tabloyu tanımlıyoruz. Sayfa1'de PivotTable1'i (pivot tablonun adı. Pivot tablonun adını, pivot tablo seçerken analiz sekmesinden kontrol edebilirsiniz.) güncellemek istediğimiz için pt'yi aşağıdaki gibi ayarladık.
Set pt = Sheet2.PivotTables("PivotTable1")
Şimdi, pivot tabloyu güncellemek için bu pivot önbelleğini kullanıyoruz. pt nesnesinin changePivotCache yöntemini kullanıyoruz.
pt.ChangePivotCache pc
Ve pivot tablomuzu otomatik hale getirdik. Bu, pivot tablonuzu otomatik olarak güncelleyecektir. Aynı veri kaynağına sahip birden fazla tablonuz varsa, her bir pivot tablo nesnesinde aynı önbelleği kullanmanız yeterlidir.
Evet arkadaşlar, Excel'de veri kaynağı aralığını dinamik olarak bu şekilde değiştirebilirsiniz. Umarım yeterince açıklayıcı olabilmişimdir. Bu makaleyle ilgili herhangi bir sorunuz varsa, aşağıdaki yorumlar bölümünde bana bildirin.
VBA Kullanarak Özet Tabloları Otomatik Yenileme: Pivot tablolarınızı otomatik olarak yenilemek için VBA olaylarını kullanabilirsiniz. Pivot tablonuzu otomatik olarak güncellemek için bu basit kod satırını kullanın. Otomatik yenileme pivot tablolarının 3 yönteminden birini kullanabilirsiniz.
Sayfada Belirlenen Aralıkta Değişiklik Yapılırsa Makroyu Çalıştır: VBA uygulamalarınızda, belirli bir aralık veya hücre değiştiğinde makro çalıştırmanız gerekir. Bu durumda, bir hedef aralıkta değişiklik yapıldığında makroları çalıştırmak için change olayını kullanırız.
Sayfada Herhangi Bir Değişiklik Yapıldığında Makroyu Çalıştır | Bu nedenle, sayfa her güncellendiğinde makronuzu çalıştırmak için VBA'nın Çalışma Sayfası Olaylarını kullanıyoruz.
Geçerli Satırı ve Sütunu Kullanarak Vurgulamak için En Basit VBA Kodu | Sayfanın geçerli satırını ve sütununu vurgulamak için bu küçük VBA parçacığını kullanın.
Excel VBA'daki Çalışma Sayfası Olayları | Çalışma sayfası olayı, sayfada belirli bir olay meydana geldiğinde makrolarınızın çalışmasını istediğinizde gerçekten kullanışlıdır.
Popüler Makaleler:
Verimliliğinizi Artıracak 50 Excel Kısayolu | Görevinizde daha hızlı olun. Bu 50 kısayol, Excel'de daha da hızlı çalışmanızı sağlayacaktır.Excel'de DÜŞEYARA İşlevi | Bu, farklı aralıklardan ve sayfalardan değer aramak için kullanılan excel'in en çok kullanılan ve popüler işlevlerinden biridir.
Excel 2016'da EĞERSAY | Bu şaşırtıcı işlevi kullanarak değerleri koşullarla sayın. Belirli bir değeri saymak için verilerinizi filtrelemeniz gerekmez. Gösterge tablonuzu hazırlamak için Countif işlevi gereklidir.
Excel'de SUMIF İşlevi Nasıl Kullanılır | Bu, başka bir gösterge panosu temel işlevidir. Bu, belirli koşullardaki değerleri özetlemenize yardımcı olur.