PivotCache

PivotCache nedir, nasıl çalışır​

Söz dizimi​

Set pc = ThisWorkbook.PivotCaches.Create(SourceType, SourceData, Version)
Set pt = pc.CreatePivotTable(TableDestination, TableName, ReadData, DefaultVersion)

• SourceType: verinin türü; xlDatabase (Excel aralığı ya da tablo), xlExternal (dış veri kaynağı) ve xlConsolidation (birden çok aralık) değerlerinden biri. Zorunludur.
• SourceData: kaynak; bir Range nesnesi, bir tablo adı ya da R1C1 biçiminde aralık metni. xlExternal için bu parametre kullanılmaz, bağlantı sonradan kurulur. İsteğe bağlıdır.
• Version: PivotTable sürüm sabiti (xlPivotTableVersion12, xlPivotTableVersion14, xlPivotTableVersion15 gibi). İsteğe bağlıdır; verilmezse Excel'in geçerli sürümü kullanılır.

• TableDestination: PivotTable'ın sol üst hücresi (Range). Zorunludur. Hedef aralıkta başka bir PivotTable bulunmamalıdır.
• TableName: PivotTable'ın adı. İsteğe bağlıdır; verilmezse Excel kendisi ad verir (PivotTable1 gibi).
• ReadData: True ise dış kaynaktaki veri önbelleğe hemen okunur. İsteğe bağlıdır; varsayılan True.
• DefaultVersion: PivotTable'ın varsayılan sürümü. İsteğe bağlıdır.

Önemli özellikler​

• RecordCount: önbellekteki kayıt (veri satırı) sayısı. Salt okunurdur.
• SourceData: kaynağı okur ya da değiştirir. Değiştirdikten sonra Refresh çağırmak gerekir.
• RefreshOnFileOpen: True ise dosya her açıldığında önbellek yenilenir.
• MissingItemsLimit: her alan için saklanacak, kaynakta artık bulunmayan öğe sayısı. xlMissingItemsDefault (-1) otomatik, xlMissingItemsNone (0) hiç saklama, xlMissingItemsMax en büyük sınır anlamına gelir. Eski öğelerin filtrede görünmesini önlemek için 0 yapılır.
• RefreshDate: son yenileme tarihi; RefreshName: yenilemeyi yapan kullanıcı adı.
• BackgroundQuery: dış veri yenilenirken arka planda çalışılıp çalışılmayacağı. EnableRefresh: yenilemeye izin verilip verilmediği.
• IsConnected: önbelleğin dış veri kaynağına bağlı olup olmadığı. MemoryUsed: önbelleğin kullandığı bellek miktarı (bayt).
• Index: önbelleğin koleksiyondaki sıra numarası; PivotTable'ın CacheIndex özelliği ile eşleşir.

Önemli yöntemler​

• CreatePivotTable: önbellekten yeni bir PivotTable üretir.
• Refresh: önbelleği kaynaktan yeniden okur; önbelleği kullanan tüm PivotTable'lar güncellenir.
• MakeConnection: önbelleği, önceden tanımlı bir bağlantıya yeniden bağlar.
• ResetTimer: otomatik yenileme zamanlayıcısını sıfırlar.

1. Önbellek oluşturma ve kayıt sayısını okuma​

Option Explicit

Sub OnbellekOlustur()
    Dim ws As Worksheet, wsGecici As Worksheet
    Dim pc As PivotCache, pt As PivotTable

    Set ws = ThisWorkbook.Worksheets("Satış Verisi")

    ' Önbelleği kaynak aralıktan oluştur
    Set pc = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=ws.Range("A1:C9"))

    ' Geçici sayfada PivotTable üret (önbellek bu sırada dolar)
    Set wsGecici = ThisWorkbook.Worksheets.Add(After:=ws)
    Set pt = pc.CreatePivotTable(TableDestination:=wsGecici.Range("A3"), _
        TableName:="GeciciPivot")

    ' Eski öğeler saklanmasın
    pc.MissingItemsLimit = xlMissingItemsNone

    ' Sonuçları yaz
    ws.Range("E2").Value = pc.RecordCount
    ws.Range("E3").Value = pc.MissingItemsLimit

    ' Geçici sayfayı sil
    Application.DisplayAlerts = False
    wsGecici.Delete
    Application.DisplayAlerts = True
End Sub

2. İki PivotTable için ortak önbellek​

Option Explicit

Sub PaylasilanOnbellek()
    Dim ws As Worksheet, wsG As Worksheet
    Dim pc As PivotCache
    Dim pt1 As PivotTable, pt2 As PivotTable

    Set ws = ThisWorkbook.Worksheets("Aylık Satış")

    ' Tek önbellek oluştur
    Set pc = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=ws.Range("A1:C9"))

    ' Aynı önbellekten iki PivotTable üret
    Set wsG = ThisWorkbook.Worksheets.Add(After:=ws)
    Set pt1 = pc.CreatePivotTable(wsG.Range("A3"), "OzetUrun")
    Set pt2 = pc.CreatePivotTable(wsG.Range("F3"), "OzetAy")

    ' Ürüne göre toplam adet
    pt1.PivotFields("Ürün").Orientation = xlRowField
    pt1.AddDataField pt1.PivotFields("Adet"), "Toplam Adet", xlSum

    ' Aya göre toplam adet
    pt2.PivotFields("Ay").Orientation = xlRowField
    pt2.AddDataField pt2.PivotFields("Adet"), "Toplam Adet", xlSum

    ' Dosya her açıldığında önbellek yenilensin
    pc.RefreshOnFileOpen = True

    ' Sonuçları yaz
    ws.Range("E2").Value = (pt1.CacheIndex = pt2.CacheIndex)
    ws.Range("E3").Value = pc.RefreshOnFileOpen
    ws.Range("E4").Value = pt1.GetPivotData("Toplam Adet").Value
    ws.Range("E5").Value = pt2.GetPivotData("Toplam Adet").Value

    ' Geçici sayfayı sil
    Application.DisplayAlerts = False
    wsG.Delete
    Application.DisplayAlerts = True
End Sub

3. Tablo kaynaklı önbelleği yenileme​

Option Explicit

Sub TabloKaynakliOnbellek()
    Dim ws As Worksheet, wsGecici As Worksheet
    Dim lo As ListObject, lr As ListRow
    Dim pc As PivotCache, pt As PivotTable

    Set ws = ThisWorkbook.Worksheets("Tablo Kaynak")

    ' Veri aralığını tabloya dönüştür
    Set lo = ws.ListObjects.Add(xlSrcRange, ws.Range("A1:C9"), , xlYes)
    lo.Name = "tblSatis"

    ' Kaynak olarak tablo adını kullan
    Set pc = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:="tblSatis")

    Set wsGecici = ThisWorkbook.Worksheets.Add(After:=ws)
    Set pt = pc.CreatePivotTable(wsGecici.Range("A3"), "GeciciPivot")
    pt.PivotFields("Şehir").Orientation = xlRowField
    pt.AddDataField pt.PivotFields("Tutar"), "Toplam Tutar", xlSum

    ' Yenilemeden önceki toplam
    ws.Range("F2").Value = pt.GetPivotData("Toplam Tutar").Value

    ' Tabloya yeni satır ekle
    Set lr = lo.ListRows.Add
    lr.Range.Cells(1, 1).Value = "Antalya"
    lr.Range.Cells(1, 2).Value = "Defter"
    lr.Range.Cells(1, 3).Value = 1500

    ' Önbelleği yenile
    pc.Refresh

    ' Yenilemeden sonraki toplam ve kayıt sayısı
    ws.Range("F3").Value = pt.GetPivotData("Toplam Tutar").Value
    ws.Range("F4").Value = pc.RecordCount

    Application.DisplayAlerts = False
    wsGecici.Delete
    Application.DisplayAlerts = True
End Sub

Kullanırken dikkat edin​

• Yenilemeden değişiklik görünmez: Kaynak hücreleri değiştirdiğinizde PivotTable otomatik güncellenmez. Değişikliklerin yansıması için Refresh yöntemini çağırın ya da RefreshOnFileOpen özelliğini True yapın.
• Sabit aralık yeni satırları kapsamaz: SourceData olarak A1:C9 gibi sabit bir aralık verirseniz sonradan eklenen satırlar önbelleğe girmez. Büyüyen veriler için kaynağı tablo (ListObject) ya da tablo adı olarak verin; ya da SourceData özelliğini yeni aralıkla güncelleyip Refresh çağırın.
• PivotTable hedefi boş olmalıdır: CreatePivotTable ile verdiğiniz hedef hücrede ya da PivotTable'ın yayılacağı alanda başka bir PivotTable bulunmamalıdır; aksi durumda çalışma zamanı hatası alırsınız. Çıktının alt tarafındaki hücrelerde veri varsa da Excel üzerine yazma uyarısı verebilir.
• Önbellek paylaşımı dosya boyutunu küçültür: Aynı kaynaktan birden çok PivotTable gerekiyorsa her seferinde yeni PivotCaches.Create çağırmak yerine aynı PivotCache nesnesinden CreatePivotTable yöntemini kullanın. Her yeni önbellek, kaynak verinin dosya içinde ayrı bir kopyası demektir.
• Eski öğeler filtrede kalabilir: Kaynaktan silinen değerler, MissingItemsLimit varsayılan ayarındayken filtre listesinde görünmeye devam edebilir. MissingItemsLimit özelliğini xlMissingItemsNone yapıp önbelleği yenilediğinizde bu öğeler temizlenir.
• Dış veri kaynaklarında yenileme: Dış bağlantılı önbelleklerde BackgroundQuery True ise Refresh arka planda çalışır ve sonraki kod satırı yenileme bitmeden işlenebilir. Sonucu hemen okumak istiyorsanız BackgroundQuery değerini False yapın.
• Aynı makroyu tekrar çalıştırma: Örneklerdeki gibi tablo adı atayan makrolarda aynı ad ikinci çalıştırmada hata verir. Tekrar denemeden önce tabloyu ve gerekirse geçici sayfayı silin ya da koda önce var olup olmadığını denetleyen bir adım ekleyin.
• İlgili konular:
  • PivotTable: önbellekten oluşturulan özet tablo nesnesi.
  • PivotTable.CacheIndex: tablonun kullandığı önbelleğin sıra numarası.
  • PivotTable.RefreshTable: yalnızca tek bir tabloyu yenileme yöntemi.
  • PivotField ve PivotFields: alanları satır, sütun ve veri alanı olarak yerleştirme.
  • ListObject: kaynak olarak kullanılabilen Excel tablosu.
  • Application.DisplayAlerts: sayfa silme gibi işlemlerde uyarıları kapatma.
  • PivotTable Çözümle > Veri > Yenile (PivotTable Analyze > Data > Refresh): elle yenileme komutu.
  • ÖZETVERİAL: PivotTable içinden değer okuyan işlev (GETPIVOTDATA).