Vba ile pivot tablo nasıl oluşturulur?

Size bu kılavuzu vermeden ve pivot tablo oluşturmak için VBA’yı kullanmaya başlamadan önce size bir şey söyleyeyim.

VBA’yı kullanmayı yalnızca ALTI yıl önce öğrendim. Ve pivot tablo oluşturmak için ilk kez makro kodu yazdığımda bu bir başarısızlıktı.

O zamandan beri, kötü kodlamamdan gerçekten işe yarayan kodlardan daha fazlasını öğrendim.

Bugün size makro kodunu kullanarak pivot tablolarınızı otomatikleştirmenin basit bir yolunu göstereceğim.

Normalde bir elektronik tabloya pivot tablo eklediğinizde bu basit bir işlemle gerçekleşir, ancak tüm bu süreç o kadar hızlıdır ki ne olduğunu asla fark etmezsiniz.

VBA’da tüm bu süreç aynıdır, sadece kod kullanılarak çalışır. Bu kılavuzda size her adımı göstereceğim ve bunun için nasıl kod yazılacağını anlatacağım.

Bu makro kodunu bir düğmeyle çalıştırabileceğiniz aşağıdaki örneğe bakın ve bu, yeni bir çalışma sayfasında anında yeni bir pivot tablo döndürür.

Lafı fazla uzatmadan pivot tablo oluşturmak için makro kodumuzu yazmaya başlayalım.

Excel’de Pivot Tablo Oluşturmak İçin VBA’da Makro Kodu Yazmanın 8 Basit Adımı

Size kolaylık sağlamak için tüm süreci 8 basit adıma böldüm. Bu adımları izledikten sonra tüm pivot tablolarınızı otomatikleştirebileceksiniz.

Devam etmek için bu dosyayı buradan indirdiğinizden emin olun.

1. Değişkenleri bildirin

İlk adım, farklı şeyleri tanımlamak için kodumuzda kullanmamız gereken değişkenleri bildirmektir.

 'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long

Yukarıdaki kodda şunları beyan ettik:

1. PSsheet: Yeni bir pivot tablo için sayfa oluşturmak için.
2. DSheet: Teknik sayfa olarak kullanılacaktır.
3. PChache: Pivot tablo önbelleğinin adı olarak kullanın.
4. PTable: Pivot tablomuzun adı olarak kullanın.
5. PRange: kaynak veri aralığını ayarlamak için.
6. LastRow ve LastCol: veri aralığımızın son satırını ve sütununu almak için.

2. Yeni bir e-tablo ekleyin

Excel, bir pivot tablo oluşturmadan önce boş bir sayfa ekler ve ardından bu sayfa üzerinde yeni bir pivot tablo oluşturur.

Ve aşağıdaki kod da sizin için aynı şeyi yapacak.

Aktif çalışma sayfasının önüne “Pivot Table” adında yeni bir çalışma sayfası ekleyecek ve aynı isimde bir çalışma sayfası varsa önce onu silecektir.

Yeni bir çalışma sayfası ekledikten sonra bu kod, pivot tablo çalışma sayfasındaki PSheet değişkeninin ve kaynak veri çalışma sayfasındaki DSheet değişkeninin değerini ayarlayacaktır.

 'Declare Variables On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data")

Koddaki çalışma sayfası adlarını, verilerinizdeki adlarla değiştirdiğinizden emin olun.

3. Veri aralığını ayarlayın

Şimdi bir sonraki adım, veri aralığını kaynak çalışma sayfasından ayarlamaktır. Burada bir şeye dikkat etmeniz gerekiyor; sabit bir kaynak aralığı belirtemezsiniz.

Kaynak sayfadaki tüm verileri tanımlayabilecek koda ihtiyacınız var. Ve aşağıda kod:

 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

Bu kod, veri tablosunun ilk hücresinden başlayacak ve son satıra, ardından son sütuna kadar seçilecektir.

Ve son olarak seçilen bu aralığı kaynak olarak ayarlayın. En iyi yanı, pivot tabloyu oluştururken her seferinde veri kaynağını değiştirmenize gerek olmamasıdır.

4. Bir pivot önbellek oluşturun

Excel 2000 ve üzeri sürümlerde, pivot tablo oluşturmadan önce veri kaynağını ayarlamak için bir pivot önbellek oluşturmanız gerekir.

Normalde pivot tablo oluşturduğunuzda Excel size sormadan otomatik olarak pivot önbellek oluşturur ancak VBA kullanmanız gerektiğinde bunun için bazı kodlar yazmanız gerekir.

 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable")

Bu kod iki şekilde çalışır; birincisi, bir veri kaynağı kullanarak bir pivot önbellek ayarlamak ve ikinci olarak, pivot tabloyu eklemek için yeni eklenen çalışma sayfasına hücre adresini ayarlamak.

Bu kodu değiştirerek pivot tablonun konumunu değiştirebilirsiniz.

5. Boş bir pivot tablo ekleyin

Pivot önbelleğinden sonraki adım boş bir pivot tablo eklemektir. Bir pivot tablo oluşturduğunuzda, her zaman önce boş bir pivot alacağınızı, ardından tüm değerleri, sütunları ve satırları ayarlayacağınızı unutmayın.

Bu kod aynı şeyi yapacak:

 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

Bu kod boş bir pivot tablo oluşturur ve onu “SalesPivotTable” olarak adlandırır. Bu adı kodun kendisinden değiştirebilirsiniz.

6. Satır ve sütun alanlarını ekleyin

Boş bir pivot tablo oluşturduktan sonraki adım, normalde yaptığınız gibi satır ve sütun alanlarını eklemektir.

Her satır ve sütun alanı için bir kod yazmanız gerekir. Burada satır alanına yıl ve ayları, sütun alanına ise alanları eklemek istiyoruz.

İşte kod:

 'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With

Bu kodda yıl ve ayı iki alan olarak belirttiniz. Şimdi koda bakarsanız orada bir pozisyon numarasının da olduğunu göreceksiniz. Bu konum numarası alanların sırasını tanımlar.

Her defasında birkaç alan (Satır veya Sütun) eklemeniz gerektiğinde, bunların konumlarını belirtin. Ve alanları koddan adlarını değiştirerek değiştirebilirsiniz.

7. Bir veri alanı ekleyin

Önemli olan pivot tablonuzdaki değer alanını tanımlamaktır.

Değerleri tanımlamaya yönelik kod, satırları ve sütunları tanımlamaktan farklıdır çünkü burada sayıların, konumların ve işlevlerin formatını tanımlamamız gerekir.

 'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With

Yukarıdaki kod ile tutarı değer alanı olarak ekleyebilirsiniz. Ve bu kod, değerleri ayırıcı (,) içeren bir sayı olarak biçimlendirecektir.

Değerleri eklemek için xlsum’u kullanırız, ancak siz xlcount ve diğer işlevleri de kullanabilirsiniz.

8. Pivot tabloyu biçimlendirin

Sonuçta pivot tablonuzu biçimlendirmek için kod kullanmanız gerekir. Pivot tabloda genellikle varsayılan bir biçimlendirme vardır ancak bu biçimlendirmeyi değiştirebilirsiniz.

VBA ile koddaki biçimlendirme stilini ayarlayabilirsiniz.

Kod:

 'Format Pivot TableActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"

Yukarıdaki kod, çizgi şeritlerini ve “Pivot Stili Orta 9” stilini uygulayacaktır, ancak bu bağlantıdan başka bir stil de kullanabilirsiniz.

Sonunda kodunuz kullanıma hazır.

[TAM KOD] Excel’de PivotTable Oluşturmak için VBA’yı Kullanın – Makroyu Kopyalayıp Yapıştırın

 Sub InsertPivotTable() 'Macro By Exceladvisor.org 'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long 'Insert a New Blank Worksheet On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data") 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol) 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable") 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable") 'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With 'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields ("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With 'Format Pivot Table ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9" End Sub

Mevcut çalışma sayfasındaki pivot tablo

Yukarıda kullandığımız kod, yeni bir çalışma sayfasında PivotTable oluşturur, ancak bazen zaten çalışma kitabında bulunan bir çalışma sayfasına PivotTable eklemeniz gerekir.

Yukarıdaki kodda (yeni çalışma sayfasındaki pivot tablo), yeni çalışma sayfası eklemek için kodu yazdığınız kısma bir ad verin. Lütfen kodda bazı değişiklikler yapın.

Üzülmeyin; Sana göstereceğim.

Öncelikle pivot tablonuzu eklemek istediğiniz çalışma sayfasını (zaten çalışma kitabında) belirtmeniz gerekir.

Ve bunun için aşağıdaki kodu kullanmalısınız:

Yeni bir çalışma sayfası eklemek yerine, çalışma sayfasının adını PSsheet değişkeninde belirtmeniz gerekir.

 Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”)

Yapılacak biraz daha şey var. Kullandığınız ilk kod, pivotu eklemeden önce aynı adlı çalışma sayfasını (varsa) siler.

Mevcut çalışma sayfasına bir PivotTable eklediğinizde, orada aynı ada sahip bir PivotTable’ınız zaten olabilir.

Demek istediğim, önce o pivotu çıkarman gerekiyor.

Bunu yapmak için, yenisini eklemeden önce aynı isimli pivotu çalışma sayfasından (varsa) kaldırması gereken kodu eklemeniz gerekir.

Eklemeniz gereken kod aşağıdadır:

 Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”) Worksheets("PivotTable").Activate On Error Resume Next ActiveSheet.PivotTables("SalesPivotTable").TableRange2.Clear

Bu kodun ne işe yaradığını size anlatayım.

İlk olarak, PSsheet’i zaten çalışma kitabınızda bulunan pivot tabloyu eklemek istediğiniz çalışma sayfası olarak ayarlar ve Veri Çalışma Sayfalarını DSheet olarak ayarlar.

Bundan sonra çalışma sayfasını etkinleştirir ve “Satış Özet Tablosunu” buradan kaldırır.

Önemli: Çalışma kitabınızdaki çalışma sayfalarının adları farklıysa bunları koddan değiştirebilirsiniz. Bunu yapmanız gereken kodu vurguladım.

Sonunda,

Bu kodu kullanarak pivot tablolarınızı otomatikleştirebiliriz. Ve en iyi yanı, bunun benzersiz bir kurulum olmasıdır; bundan sonra pivot tablo oluşturmak için tek tıklamamız yeterli ve zamandan büyük ölçüde tasarruf edebilirsiniz. Şimdi bana bir şey söyle.

Pivot tablo oluşturmak için hiç VBA kodunu kullandınız mı?

Lütfen görüşlerinizi yorum kutusunda benimle paylaşın; Bunları sizinle paylaşmak ve bu ipucunu arkadaşlarınızla paylaşmak istiyorum.

Benzer makaleler:

• Excel’deki bir pivot tabloya genel toplam ekleme veya kaldırma
• Excel’de bir pivot tabloya değişen toplamı ekleme
• Excel’de bir pivot tabloyu otomatik olarak güncelleme
• Hesaplanan alan ve öğe ekleme
• Excel’de PivotTable’ı silme