Bir pivot tablo aralığını otomatik olarak nasıl güncellerim?

Bir pivot tabloyu güncellemek zahmetli bir iş, değil mi? Eğer işinizde pivot tabloları sıklıkla kullanıyorsanız, eminim ki anlayabilirsiniz.

Sorun şu ki, kaynak sayfaya her yeni veri eklediğinizde, pivot tablonuzu yenilemeden önce pivot tablo kaynak aralığını güncellemeniz gerekir.

Şimdi, kaynak sayfanıza her gün veri eklerseniz kaynak aralığını her gün güncellemeniz gerektiğini hayal edin.

Ve her defasında pivot tablo aralığını değiştirmek tam bir karmaşadır. Evet, doğru, ne kadar sık veri eklerseniz kaynak aralığını da o kadar güncellemeniz gerekir.

Yani, yeni veriler eklediğinizde kaynak aralığını otomatik olarak güncelleyecek bir yönteme ihtiyacınız var.

NOT : Pivot tablolar ORTA EXCEL BECERİLERİNİN bir parçasıdır.

Otomatik güncellenen pivot tablo aralığına sahip olmak için tabloyu uygulayın

Birkaç gün önce John Michaloudis’e milyon dolarlık pivot masa bahşişini sordum. Şöyle diyor: Kaynak verilerinizi bir tabloya koyun. İnan bana, bu bir milyon dolarlık bahşiş.

Kaynak verilere bir tablo uyguladığınızda pivot tablonuzun kaynak aralığını tekrar tekrar değiştirmenize gerek kalmaz.

Her yeni veri eklediğinizde pivot tablo aralığını otomatik olarak günceller.

Pivot tablo oluşturmadan önce verileri tabloya dönüştürün

Her seferinde bir pivot tablo oluşturmadan önce, aşağıdaki adımları kullanarak tabloyu kaynak verilere uyguladığınızdan emin olun.

1. Verilerinizdeki hücrelerden birini seçin.
2. Ctrl + T kısayol tuşunu kullanın veya → Sekme Ekle → Tablolar → Tablo seçeneğine gidin.
3. Mevcut veri aralığınızı içeren bir açılır pencere göreceksiniz.
   
4. Tamam’ı tıklayın.
5. Şimdi bir pivot tablo oluşturmak için verilerinizdeki herhangi bir hücreyi seçin. → Tasarım sekmesi → Araçlar → PivotTable ile Özetle seçeneğine gidin.
   
6. Tamam’ı tıklayın.

Artık veri sayfanıza yeni veri eklediğinizde pivot tablo aralığı otomatik olarak güncellenir ve pivot tablonuzu yenilemeniz yeterlidir.

Pivot tablo oluşturduktan sonra verileri tabloya dönüştürün

Çalışma sayfanızda zaten bir pivot tablonuz varsa veri kaynağınızı tabloya dönüştürmek için aşağıdaki adımları kullanabilirsiniz.

1. Veri kaynağınızdaki hücrelerden birini seçin.
2. Ctrl + T kısayol tuşunu kullanın veya → Sekme Ekle → Tablolar → Tablo seçeneğine gidin.
3. Mevcut veri aralığınızı içeren bir açılır pencere göreceksiniz.
4. Tamam’ı tıklayın.
5. Şimdi pivot tablonuzdaki hücrelerden birini seçin ve → Analiz Et → Veri → Veri Kaynağını Düzenle → Veri Kaynağını Düzenle (açılır menü) seçeneğine gidin.
6. Veri kaynağınızı yeniden seçmeniz için bir açılır pencere göreceksiniz veya aralık girişine tablo adını da girebilirsiniz.
   
7. Tamam’ı tıklayın.

Artık kaynak sayfanıza her yeni veri eklediğinizde, pivot tablo aralığı otomatik olarak güncellenecek şekilde artacaktır.

OFFSET işleviyle dinamik bir pivot tablo aralığı oluşturun

Pivot tablo aralığını otomatik olarak güncellemenin diğer en iyi yolu dinamik aralık kullanmaktır.

Kaynak sayfanıza her yeni veri eklediğinizde dinamik aralık otomatik olarak genişleyebilir. Dinamik aralık oluşturma adımları aşağıda verilmiştir.

1. → Formüller Sekmesi → Tanımlı Adlar → Ad Yöneticisi seçeneğine gidin.
2. Ad yöneticisine tıkladığınızda bir açılır pencere göreceksiniz.
   
3. Adlandırılmış bir aralık oluşturmak için Ad Yöneticisi pencerenizde Yeni’yi tıklayın.
4. Yeni ad pencerenize şunu girin:
   1. Yeni seriniz için bir isim. “SourceData” adını kullanıyorum.
   2. Aralığın kapsamını belirtin. Geçerli çalışma sayfası veya çalışma kitabı arasında belirtebilirsiniz.
   3. Adlandırılmış aralığınızı açıklamak için bir yorum ekleyin. Aşağıdaki formülü “Başvur” giriş çubuğuna girin.
      

       =OFFSET(PivotTableData!$A$1,0,0,COUNTA(PivotTableData!$A:$A),COUNTA(PivotTableData!$1:$1))

5. Sonunda Tamam’ı tıklayın.

Artık bir pivot tablo oluşturmak için dinamik aralığa sahipsiniz.

Tek yapmanız gereken kaynak verilerinizle bir pivot tablo oluşturmak ve ardından ilk tablolar yönteminde kullandığım yöntemin aynısını kullanarak kaynağı adlandırılmış aralıkla değiştirmek.

Kaynak sayfanıza yeni veriler ekledikten sonra pivot tablonuzu yenilemeniz yeterlidir.

Bu formül nasıl çalışıyor?

Yukarıdaki formülde dinamik aralık oluşturmak için ofset fonksiyonunu kullandım.

Başlangıç olarak A1 hücresinden bahsettim, ardından satır ve sütunlardan bahsetmeden aralığın yüksekliğini ve genişliğini COUNTA kullanarak belirttim.

COUNTA, A sütunu ve 1. satırdaki değerleri içeren hücreleri sayacak ve ofset’e yüksekliğini ve genişliğini buna göre genişletmesini söyleyecektir.

Dikkat etmeniz gereken tek şey A sütunu ile 1. satır arasında boş hücre bulunmamasıdır.

VBA kodunu kullanarak pivot tabloyu güncelleme

Çoğu kişi VBA kodlarını kullanmayı sever. İşte pivot tablo aralığını VBA ile güncellemek için kullanılacak kod.

 Sub UpdatePivotTableRange() Dim Data_Sheet As Worksheet Dim Pivot_Sheet As Worksheet Dim StartPoint As Range Dim DataRange As Range Dim PivotName As String Dim NewRange As String Dim LastCol As Long Dim lastRow As Long 'Set Pivot Table & Source Worksheet Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3") 'Enter in Pivot Table Name PivotName = "PivotTable2" 'Defining Staring Point & Dynamic Range Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol)) NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1) 'Change Pivot Table Data Source Range Address Pivot_Sheet.PivotTables(PivotName). _ ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange) 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated." End Sub

Çalışma kitabınızda kullanmadan önce değiştirmeniz gereken şeyler.

1. 13. Satır: Kaynak çalışma sayfasının adını değiştirin.
2. Satır 14: Pivot tablo sayfasının adını değiştirin.
3. Satır 17: Pivot tablonun adını değiştirin.

Bu kodu kullanırken hala sorun yaşıyorsanız lütfen yorum kutusuna yazın. Şimdi size bu kodun nasıl çalıştığını göstereyim, böylece onu ihtiyaçlarınıza göre kolayca değiştirebilirsiniz.

 Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3")

Kodun yukarıdaki kısmında kaynak verinin pivot tablo ve çalışma sayfası değişkenlerini belirttik. E-tablo adını buradan değiştirebilirsiniz.

 PivotName = "PivotTable2"

Kodun yukarıdaki kısmına bu kodu kullanmak istediğiniz pivot tablonun adını girin.

 Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))

Kodun yukarıdaki kısmı, veri kaynağı çalışma sayfasının A1 hücresini kullanarak dinamik bir aralık oluşturacaktır.

Dinamik bir aralık oluşturmak için son sütunu ve son satırı verilerle kontrol edecektir. Bu makroyu her çalıştırdığınızda yeni bir dinamik aralık oluşturulacaktır.

 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated."

Kodun yukarıdaki kısmı pivot tabloyu yenileyecek ve kullanıcıya pivot tablonun artık güncellendiğini belirten bir mesaj görüntüleyecektir.