Pivot tablolar, güçlü bir veri görselleştirme aracıdır, ancak kurulumları zor olabilir. Bir VBA betiği ile bu sürecin hayal kırıklığını giderin.
Excel’deki pivot tablolar, verilerin anlaşılmasını ve anlaşılmasını kolaylaştırmak için çok önemlidir. Bir pivot tablo, verileri anlamlı yapılara sıkıştırabilir ve sıkıştırabilir. MS Excel kullanıcıları bunları veri endüstrisinde yaygın olarak benimsemiştir.
Özet tablolarınızı Excel’de otomatikleştirebileceğinizi ve tek tıkla oluşturabileceğinizi biliyor muydunuz? MS Excel, VBA ile iyi bir şekilde bütünleşir ve tekrarlayan görevleri otomatikleştirmek için mükemmel bir araç haline gelmiştir.
MS Excel VBA’da bir pivot tabloyu bir makroyla nasıl otomatikleştirebileceğiniz aşağıda açıklanmıştır.
Bir Alıştırma Veri Kümesi Kullanın
Bu kılavuzdaki VBA komut dosyasını takip etmek için Tableau’dan sahte bir veri kümesi indirebilir ve kullanabilirsiniz . VBA kodu, birkaç temel ince ayar ile başka herhangi bir veri kümesiyle çalışacaktır. Başlamadan önce, Excel çalışma kitabınızda makroları etkinleştirdiğinizden emin olun .
Pivot tabloda kullanabileceğiniz birkaç temel sütun vardır. Tablonun nüanslarını ve son yapısını anlamak için aşağıdaki öğelerle manuel olarak temel bir pivot tablo oluşturabilirsiniz :
- Filtre: Bölge
- Satırlar: Alt Kategori
- Sütunlar: Devlet
- Değerler: Satış
Son pivot aşağıdaki gibi görünmelidir:
- MS Excel’de Sample Superstore verileri için temel hesaplamaları içeren pivot tablo
- Ancak, manuel olarak hazırlamak yerine VBA’nın otomatik olarak yapmasına izin verebilirsiniz.
Excel’de Otomatik Olarak Özet Tablolar Nasıl Oluşturulur
Özet tablolarınızı VBA ile otomatikleştirmek için yeni bir Excel dosyası açın ve sayfaları aşağıdaki gibi yeniden adlandırın:
İlk sayfa: Makro
İkinci sayfa: Veri
Makro sayfası makro komut dosyasını içerirken Veri sayfası verilerinizi içerir. Makro sayfasında istediğiniz şekli ekleyebilir ve makroyu ona atayabilirsiniz. Şekle sağ tıklayın ve Makro Ata’ya tıklayın.
Excel çalışma kitabında bir iletişim kutusu açık olan bir daire
Aşağıdaki iletişim kutusunda, makronuzun adına tıklayın ve Tamam’a tıklayın. Bu adım, makroyu şekle atar.
1. Excel VBA Kodlama Düzenleyicisini açın
Kod düzenleyiciyi açmak için Alt + F11 tuşlarına basın . Kod düzenleyiciye girdikten sonra, dosya adına sağ tıklayın, ardından Insert ve Module seçeneğine tıklayın . Çalıştırmadan önce tüm VBA kodunu bir modül içine yazacağınızı hatırlamak önemlidir.
MS Excel’deki kod düzenleyicideki VBA modülü
Kodun amacına uygun bir modül adı kullanmak iyi bir uygulamadır. Bu bir demo olduğu için modül adını şu şekilde tanımlayabilirsiniz:
sub pivot_demo()
Modül adı , bir modülün bitiş komutu olan End Sub ile biter:
End Sub
2. Değişkenleri Bildirin
Modül içinde, komut dosyasında kullanacağınız bazı kullanıcı tanımlı değerleri depolamak için değişkenler bildirerek başlayın. Değişkenleri bildirmek için Dim ifadesini aşağıdaki gibi kullanabilirsiniz :
- Dim PSheet As Worksheet, DSheet As Worksheet
- Dim PvtCache As PivotCache
- Dim PvtTable As PivotTable
- Dim PvtRange As Range
- Dim Last_Row As Long, Last_Col As Long
- Dim sht1 as Variant
Bu değişkenleri aşağıdakiler için kullanacaksınız:
- PSheet: VBA’nın bir pivot oluşturacağı hedef sayfa.
- DSheet: Veri sayfası.
- PvtCache: Bir pivot önbellek, pivotu tutar.
- PvtTable: Pivot tablo nesnesi.
- PvtRange: Pivot için bir veri aralığı.
- Last_Row ve Last_Col: Veri sayfasındaki (DSheet) son doldurulan satır ve sütun.
- Sht1: Bu değişken bir varyanttır.
- Excel VBA kod düzenleyici pencere arayüzü
3. Uyarıları ve Mesajları Bastırın
Gereksiz hatalar, uyarılar ve mesajlar VBA kodlarınızı yavaşlatır. Bu tür mesajları bastırarak süreci önemli ölçüde hızlandırabilirsiniz.
Aşağıdaki kodu kullanın:
On Error Resume Next
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
Neresi:
- On Error Resume Next: Bu madde, herhangi bir çalışma zamanı hatasını bastırır.
- Uygulama: Uygulama MS Excel’i ifade eder.
- DisplayAlerts: DisplayAlerts özelliği, uyarıların gösterilip gösterilmeyeceğini tanımlar.
- ScreenUpdating: Bu özellik, değişikliklerin gerçek zamanlı olarak mı yoksa yalnızca kodun çalışması bittiğinde mi güncelleneceğini tanımlar.
- Bu kod çalıştığında, Excel’in aksi takdirde göstereceği tüm uyarıları, uyarıları ve mesajları bastırır. Değerlerini False olarak ayarlayarak DisplayAlerts ve ScreenUpdating parametrelerini kapatabilirsiniz.
Kodun sonuna doğru değeri True olarak ayarlayarak bunları tekrar açabilirsiniz.
4. Mevcut Pivot Sayfaları Silin
Yeni bir pivot tablo oluşturmak için iki seçeneğiniz vardır. İlk olarak, mevcut pivot sayfasını silin ve pivotu depolamak için yeni bir sayfa oluşturmak için VBA’yı kullanın. Alternatif olarak, pivotu tutmak için mevcut bir çalışma sayfasını kullanabilirsiniz.
Bu kılavuzda, pivot tabloyu depolamak için yeni bir pivot sayfası oluşturalım.
for her döngüsü, çalışma kitabındaki her sayfa arasında geçiş yapar ve sayfa adını sht1 değişkeninde saklar. Sayfa adını tutmak için herhangi bir değişken adını (sht1) kullanabilirsiniz. Döngü, geçerli çalışma kitabındaki her sayfada dönerek belirli bir ada ( Pivot ) sahip birini arar.
Sayfa adı eşleştiğinde, sayfayı siler ve sonraki sayfaya geçer. Kod tüm sayfaları kontrol ettikten sonra döngüden çıkar ve kodun yeni bir sayfa ekleyen Pivot bölümüne geçer .
Bunu şu şekilde yapabilirsiniz:
For Each sht1 In ActiveWorkbook.Worksheets
If sht1.Name = “Pivot” Then
sht1.Delete
End If
Next sht1
Worksheets.Add.Name = “Pivot”
5. Veri Kaynağını ve Özet Sayfaları Tanımlayın
Pivot ve Veri sayfalarının referanslarını depolamak için değişkenler oluşturmak önemlidir. Bunlar, kodun geri kalanında başvurabileceğiniz kısayollar olarak çalışır.
Set PSheet = Worksheets(“Pivot”)
Set DSheet = Worksheets(“Data”)
6. Son Kullanılan Satırı ve Sütunu Tanımlayın
Kodun bu kısmı, veriler içindeki son doldurulmuş satırı ve sütunu boyutlandırdığı için dinamik olarak çalışır.
Last_Row = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
Last_Col = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PvtRange = DSheet.Cells(1, 1).Resize(Last_Row, Last_Col)
Neresi:
- Last_Row: Son doldurulan satır numarasını saklamak için değişken, yani 9995
- Last_Col: Son doldurulan sütun numarasını saklamak için değişken, yani 21
- PvtRange: PvtRange, pivot için tüm veri aralığına başvurur
- Excel VBA’da veri aralıklarını tanımlamak için kod
7. Bir Özet Önbellek ve Bir Özet Tablo oluşturun
Bir özet önbellek, özet tabloyu tutar; bu nedenle, bir pivot tablo oluşturmadan önce bir önbellek oluşturmanız gerekir. Özet sayfasında özet önbelleği oluşturmak için VBA’nın sözdizimi referanslarını kullanmalısınız.
Özet önbelleğe başvurarak bir özet tablo oluşturmanız gerekir. Pivot tablonun bir parçası olarak, sayfayı, hücre referansını ve pivot tablonun adını tanımlayabilirsiniz.
Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PvtRange).CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), TableName:=”MUODemoTable”)
Set PvtTable = PvtCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:=”MUODemoTable”)
Neresi:
- ActiveWorkbook: Veri ve Özet sayfasına sahip olduğunuz geçerli çalışma kitabı.
- PivotCaches.Create: Özet önbellek oluşturmak için varsayılan sözdizimi.
- SourceType: Veriler çalışma kitabı içinde olduğu için xlDatabase olarak tanımlayabilirsiniz . Diğer bazı seçenekler arasında xlConsolidation , xlExternal veya xlPivotTable bulunur .
- SourceData: Kaynak veri olarak önceki pivot aralığına başvurabilirsiniz.
- CreatePivotTable: Pivot tablo oluşturmak için varsayılan komut.
- TableDestination: Pivotu oluşturmak istediğiniz sayfa ve hücre referanslarını belirtmeniz gerekir.
- TableName: Bir pivot tablo adı belirtin.
- CreatePivotTable: Özet önbellek içinde özet tablo oluşturmak için varsayılan komut.
- Özet önbellek ve özet tablonun nasıl oluşturulacağını gösteren VBA kodu
8. Satırları, Sütunları, Filtreleri ve Değerleri Ekleme
Pivot tablo hazır olduğundan, filtreler, satırlar, sütunlar ve toplama değerleri içindeki parametreleri eklemeye başlamanız gerekir. Ayrıntıları bildirmeye başlamak için VBA pivotfields komutunu kullanabilirsiniz.
Filtre Değerleri Eklemek İçin:
With ActiveSheet.PivotTables(“MUODemoTable”).PivotFields(“Region”)
.Orientation = xlPageField
End With
Satır Değerleri Eklemek İçin:
With ActiveSheet.PivotTables(“MUODemoTable”).PivotFields(“Sub-Category”)
.Orientation = xlRowField
End With
Sütun Değerleri Eklemek İçin:
With ActiveSheet.PivotTables(“MUODemoTable”).PivotFields(“State”)
.Orientation = xlColumnField
End With
Toplama Değerleri Eklemek İçin:
With ActiveSheet.PivotTables(“MUODemoTable”).PivotFields(“Sales”)
.Orientation = xlDataField
.Function = xlSum
End With
Etkin sayfaya (Özet sayfa), ardından pivot tablo adına ve değişken adına başvurmanız gerektiğini unutmamak önemlidir. Filtre(ler), satır(lar) ve sütun(lar) eklemeniz gerektiğinde, aşağıdakileri içeren çeşitli sözdizimi arasında geçiş yapabilirsiniz:
xlPageField: Filtre eklemek için.
xlRowField: Satır eklemek için.
xlRowField: Sütun eklemek için.
Son olarak, değer toplamalarını hesaplamak için xlDataField komutunu kullanabilirsiniz. xlSum, xlAverage, xlCount, xlMax, xlMin ve xlProduct gibi diğer toplama işlevlerini kullanabilirsiniz.
9. Otomatik Özetler Oluşturmak için Excel VBA Kodunu Çalıştırma
Son olarak, tüm program hazır olduğunda, F5 tuşuna basarak veya oynat düğmesine tıklayarak çalıştırabilirsiniz. Çalışma kitabınızdaki Pivot sayfasına geri döndüğünüzde, gözden geçirmeniz için yeni bir pivot tablonun hazır olduğunu göreceksiniz.
Kod komutunun satır satır nasıl yürütüldüğünü adım adım görmek istiyorsanız, kod düzenleyiciye gidebilir ve birkaç kez F8 tuşuna basabilirsiniz. Bu şekilde, her bir kod satırının nasıl çalıştığını ve VBA’nın pivotlarınızı nasıl otomatik olarak oluşturduğunu görebilirsiniz.
Pivot Tabloları Otomatik Olarak Kodlamayı Öğrenme
Pivotlar yalnızca MS Excel ile sınırlı değildir. Python gibi programlama dilleri, yalnızca birkaç satır kodla optimize edilmiş pivotlar oluşturmanıza olanak tanır.
Veri optimizasyonu bundan daha kolay olamaz. Python’da komutlarınızı etkili bir şekilde seçip seçebilir ve benzer bir Excel benzeri pivot yapısını kolaylıkla gerçekleştirebilirsiniz.