Önceki bir makalede, küçülen veya genişleyen veri kaynaklarıyla tek tek pivot tabloları dinamik olarak nasıl değiştirebileceğinizi ve güncelleyebileceğinizi öğrendik.
Bu yazıda, bir çalışma kitabındaki tüm pivot tabloların veri kaynağını otomatik olarak değiştirmesini nasıl sağlayabileceğimizi öğreneceğiz. Başka bir deyişle, her seferinde bir pivot tabloyu değiştirmek yerine, çalışma kitabındaki tüm pivot tabloların veri kaynağını, kaynak tablolara eklenen yeni satırları ve sütunları dinamik olarak içerecek ve pivot tablolardaki değişikliği anında yansıtacak şekilde değiştirmeye çalışacağız.
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 tabloları 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.
Çalışma Kitabındaki Tüm Özet Tabloları 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ı üç sayfa içerir. Sayfa1 değişebilen kaynak verileri içerir. Sayfa2 ve Sayfa3, sayfa2'nin kaynak verilerine bağlı olan özet tablolar 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 source_data As Range 'Son satır ve sütun numarasının belirlenmesi lstrow = Cells(Rows.Count, 1).End(xlUp).Row lstcol = Cells(1, Columns.Count).End(xlToLeft).Column 'Yeni aralığı ayarlama Source_data'yı ayarla = Aralık(Hücreler(1, 1), Hücreler(lstrow, lstcol)) 'Bu Çalışma Kitabındaki Her ws için Her sayfa ve pivot tablo arasında döngü oluşturacak kod.Her pt için ws.PivotTables pt'de Çalışma Sayfaları. ChangePivotCache _ ThisWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=source_data) Sonraki pt Sonraki ws End Sub
Benzer bir çalışma kitabınız varsa, bu verileri doğrudan kopyalayabilirsiniz. İhtiyaçlarınıza göre değiştirebilmeniz için bu kodun çalıştığını aşağıda açıkladı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.
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. Kendi başlangıç hücre referansınızı tanımlayabilirsiniz.
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.
Bir çalışma kitabının aynı anda kaç tane pivot tablo içereceğini bilmediğimiz için, her sayfa ve her sayfanın pivot tabloları arasında dolaşacağız. Pivot tablo kalmasın diye. Bunun için iç içe for döngülerini kullanıyoruz.
ThisWorkbook.Worksheets'teki Her ws için
ws.PivotTables'da Her pt için
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=source_data)
Sonraki nokta
Sonraki ws
İlk döngü her bir sayfa boyunca dolanır. İkinci döngü, bir sayfadaki her bir pivot tablo üzerinde yinelenir.
Pivot tablolar değişkene atanır pt. pt nesnesinin ChangePivotCache yöntemini kullanıyoruz. ThisWorkbook.PivotCaches.Create kullanarak dinamik olarak bir pivot önbellek oluşturuyoruz
Yöntem. Bu yöntem, SourceType ve SourceData olmak üzere iki değişken alır. Source type olarak xlDatabase bildiriyoruz ve SourceData olarak daha önce hesapladığımız source_data aralığını geçiyoruz.
Ve işte bu. Pivot tablolarımızı otomatik hale getirdik. Bu, çalışma kitabındaki tüm pivot tabloları otomatik olarak güncelleyecektir.
Evet arkadaşlar, Excel'deki bir çalışma kitabındaki tüm pivot tabloların veri kaynağı aralıkları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.
Excel'de Pivot Tablo Veri Kaynağı Aralığını Dinamik Olarak Güncelleme: Özet tabloların kaynak veri aralığını dinamik olarak değiştirmek için özet önbellekleri kullanıyoruz. Bu birkaç satır, kaynak veri aralığını değiştirerek herhangi bir pivot tabloyu dinamik olarak güncelleyebilir.
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.