VBA Pivot Tablosu - VBA'da Pivot Tablo Oluşturma Adımları

Excel VBA Özet Tablosu

Pivot Tablolar , büyük miktarda verinin raporunu özetlemenin kalbidir. VBA kodlamasıyla bir pivot tablo oluşturma sürecini de otomatikleştirebiliriz. Bunlar, herhangi bir raporun veya panonun önemli bir parçasıdır, excel'de bir düğmeyle tablo oluşturmak kolaydır, ancak VBA'da pivot tablomuzu otomatikleştirmek için bazı kodlar yazmamız gerekir, excel 2007'den önce ve VBA'daki eski sürümlerinden önce buna ihtiyacımız yoktu pivot tablolar için bir önbellek oluşturun, ancak excel 2010'da ve daha yeni sürümlerinde önbellekler gereklidir.

VBA, iş yerimizde bize tonlarca zaman kazandırabilir. Her ne kadar ustalaşmak o kadar kolay değil ama bunu öğrenmek için zaman harcamaya değer. VBA aracılığıyla pivot tablo oluşturma sürecini anlamak 6 ay sürdü. Bu 6 ayın benim için harikalar yarattığını biliyorsunuz çünkü pivot tabloyu oluşturmaya çalışırken çok fazla hata yaptım.

Ama asıl mesele şu ki, hatalarımdan öğrendim ve şimdi size kodu kullanarak pivot tabloların nasıl oluşturulacağını göstermek için bu makaleyi yazıyorum.

Sadece bir düğmeye tıklayarak raporlar oluşturabiliriz.

VBA'da Pivot Tablo Oluşturma Adımları

Bir pivot tablo oluşturmak için verilere sahip olmak önemlidir. Bunun için bazı sahte veriler oluşturdum. Benimle aynı verilerle takip etmek için çalışma kitabını indirebilirsiniz.

Adım 1: Pivot Tablo, pivot tabloya başvuran bir nesnedir ve değişkeni PivotTable olarak bildirir.

Kod:

Alt Özet Tablo () Özet Tablo Son Alt Tablosu Olarak Dim

Adım 2: Önce bir pivot tablo oluşturmadan önce, verilerin kaynağını tanımlamak için bir pivot önbellek oluşturmamız gerekiyor.

Normal çalışma sayfalarında, bizi rahatsız etmeden pivot tablo arka planda bir pivot önbellek oluşturacaktır. Ancak VBA'da yaratmak zorundayız.

Bunun için değişkeni bir PivotCache olarak tanımlayın.

Kod:

PivotCache Olarak Dim PCache

Adım 3: Özet veri aralığını belirlemek için değişkeni aralık olarak tanımlayın.

Kod:

Dim PRange As Range

Adım 4: Bir pivot tablo eklemek için, değişkeni bir çalışma sayfası olarak bildirmek üzere pivot tabloya çalışma sayfaları eklemek için ayrı bir sayfaya ihtiyacımız var.

Kod:

PSheet'i Çalışma Sayfası Olarak Karart

Adım 5: Benzer şekilde, çalışma sayfasını içeren verilere başvurmak için bir değişken daha Çalışma Sayfası olarak bildirin.

Kod:

DSheet'i Çalışma Sayfası Olarak Dim

Adım 6: Son olarak, son kullanılan satırı ve sütunu bulmak için, Long olarak iki değişken daha tanımlayın.

Kod:

Sönük LR, Uzun Loş LC Kadar Uzun

7. Adım: Şimdi, bir pivot tablo oluşturmak için yeni bir sayfa eklememiz gerekiyor. Bundan önce, herhangi bir pivot sayfası varsa, onu silmemiz gerekir.

Adım 8: Şimdi, nesne değişkenini PSheet ve DSheet'i sırasıyla Pivot Sheet ve Data Sheet olarak ayarlayın.

Adım 9: Veri sayfasında son kullanılan satırı ve son kullanılan sütunu bulun.

Adım 10: Şimdi son satırı ve son sütunu kullanarak pivot aralığını ayarlayın.

Bu, veri aralığını mükemmel bir şekilde ayarlayacaktır. Veri sayfasında herhangi bir veri eklenmesi veya silinmesi olsa bile veri aralığını otomatik olarak seçecektir.

Adım 11: Bir pivot tablo oluşturmadan önce, bir pivot önbellek oluşturmamız gerekiyor. Aşağıdaki VBA kodunu kullanarak pivot önbellek değişkenini ayarlayın.

Adım 12: Şimdi boş bir pivot tablo oluşturun.

Adım 13: Pivot tabloyu ekledikten sonra, önce satır alanını eklememiz gerekiyor. Bu yüzden satır alanını Ülke sütunum olarak ekleyeceğim.

Not: Veri sütunlarını anlamak için çalışma kitabını indirin.

Adım 14: Şimdi, ikinci konum öğesi olarak satır alanına bir öğe daha ekleyeceğim. Ürünü ikinci satır öğesi olarak satır alanına ekleyeceğim.

Adım 15: Sütunları satır alanına ekledikten sonra, sütun alanına değerler eklememiz gerekir. "Segment" i sütun alanına ekleyeceğim.

Adım 16: Şimdi, veri alanına sayılar eklememiz gerekiyor. Bu yüzden veri alanına "Satış" yazın.

Adım 17: Pivot tablo özet kısmı ile işimiz bitti. Şimdi tabloyu biçimlendirmemiz gerekiyor. Pivot tabloyu biçimlendirmek için aşağıdaki kodu kullanın.

Not: Daha farklı tablo stillerine sahip olmak için bunları makro kaydedin ve tablo stillerini alın.

Satır dosyalanmış değerleri öğelerini tablo biçiminde göstermek için, aşağıdaki kodu aşağıya ekleyin.

Tamam, bu kodu F5 tuşunu kullanarak veya manuel olarak çalıştırırsak işimiz bitmiş demektir, pivot tabloyu böyle almalıyız.

Bunun gibi, VBA kodlamasını kullanarak bir pivot tablo oluşturma sürecini otomatikleştirebiliriz.

Referans için aşağıdaki kodu verdim.

Sub PivotTable() Dim PTable As PivotTable Dim PCache As PivotCache Dim PRange As Range Dim PSheet As Worksheet Dim DSheet As Worksheet Dim LR As Long Dim LC As Long On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets("Pivot Sheet").Delete 'This will delete the exisiting pivot table worksheet Worksheets.Add After:=ActiveSheet ' This will add new worksheet ActiveSheet.Name = "Pivot Sheet" ' This will rename the worksheet as "Pivot Sheet" On Error GoTo 0 Set PSheet = Worksheets("Pivot Sheet") Set DSheet = Worksheets("Data Sheet") 'Find Last used row and column in data sheet LR = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LC = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Set the pivot table data range Set PRange = DSheet.Cells(1, 1).Resize(LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=PRange) 'Create blank pivot table Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="Sales_Report") 'Insert country to Row Filed With PSheet.PivotTables("Sales_Report").PivotFields("Country") .Orientation = xlRowField .Position = 1 End With 'Insert Product to Row Filed & position 2 With PSheet.PivotTables("Sales_Report").PivotFields("Product") .Orientation = xlRowField .Position = 2 End With 'Insert Segment to Column Filed & position 1 With PSheet.PivotTables("Sales_Report").PivotFields("Segment") .Orientation = xlColumnField .Position = 1 End With 'Insert Sales column to the data field With PSheet.PivotTables("Sales_Report").PivotFields("Sales") .Orientation = xlDataField .Position = 1 End With 'Format Pivot Table PSheet.PivotTables("Sales_Report").ShowTableStyleRowStripes = True PSheet.PivotTables("Sales_Report").TableStyle2 = "PivotStyleMedium14" 'Show in Tabular form PSheet.PivotTables("Sales_Report").RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub