Tabel Pivot VBA

Tabel Pivot VBA Excel

Tabel Pivot adalah jantung dari meringkas laporan dari sejumlah besar data. Kami juga dapat mengotomatiskan proses pembuatan tabel pivot melalui pengkodean VBA. Mereka adalah bagian penting dari laporan atau dasbor mana pun, di excel mudah membuat tabel dengan tombol tetapi di VBA kami harus menulis beberapa kode untuk mengotomatiskan tabel pivot kami, sebelum excel 2007 dan versi yang lebih lama di VBA kami tidak perlu melakukannya buat cache untuk tabel pivot tetapi di excel 2010 dan versi yang lebih baru, cache diperlukan.

VBA dapat menghemat banyak waktu untuk kita di tempat kerja kita, meskipun menguasainya tidaklah mudah tetapi layak menghabiskan waktu untuk mempelajarinya. Saya membutuhkan waktu 6 bulan untuk memahami proses pembuatan tabel pivot melalui VBA. Anda tahu apa yang 6 bulan itu telah lakukan keajaiban bagi saya karena saya membuat begitu banyak kesalahan saat mencoba membuat tabel pivot.

Tetapi sebenarnya saya telah belajar dari kesalahan saya dan sekarang saya menulis artikel ini untuk menunjukkan kepada Anda cara membuat tabel pivot menggunakan kode.

Hanya dengan satu klik tombol, kita dapat membuat laporan.

Langkah-langkah Membuat Tabel Pivot di VBA

Anda dapat mengunduh Templat Tabel Pivot VBA ini di sini - Templat Tabel Pivot VBA

Untuk membuat tabel pivot penting untuk memiliki data. Untuk ini saya telah membuat beberapa data dummy, Anda dapat mengunduh buku kerja untuk diikuti dengan saya dengan data yang sama.

Langkah 1:  Tabel Pivot adalah objek untuk mereferensikan tabel pivot yang menyatakan variabel sebagai PivotTable.

Kode:

 Sub PivotTable () Dim PTable Sebagai PivotTable End Sub 

Langkah 2:  Sebelum kita membuat tabel pivot terlebih dahulu kita perlu membuat cache pivot untuk menentukan sumber data.

Dalam tabel pivot lembar kerja biasa tanpa mengganggu kita akan membuat cache pivot di latar belakang. Tapi di VBA kita harus membuatnya.

Untuk ini, tentukan variabel PivotCache.

Kode:

 Redupkan PCache Sebagai PivotCache

Langkah 3:  Untuk menentukan rentang data pivot, tentukan variabel sebagai rentang.

Kode:

 Redupkan PRange As Range

Langkah 4:  Untuk menyisipkan tabel pivot kita membutuhkan lembar terpisah untuk menambahkan lembar kerja karena tabel pivot menyatakan variabel sebagai lembar kerja.

Kode:

 Redupkan PSheet Sebagai Lembar Kerja

Langkah 5:  Demikian pula untuk referensi data yang berisi lembar kerja menyatakan satu variabel lagi sebagai Lembar Kerja.

Kode:

 Redupkan Lembar Kerja Sebagai Lembar Kerja

Langkah 6: Akhirnya untuk menemukan baris & kolom yang terakhir digunakan, tentukan dua variabel lagi sebagai Long.

Kode:

 Redupkan LR Selama LC Redup Selama 

Langkah 7: Sekarang kita perlu memasukkan lembar baru untuk membuat tabel pivot. Sebelum itu, jika ada lembar pivot di sana maka kita perlu menghapusnya.

Langkah 8: Sekarang atur variabel objek PSheet dan DSheet ke Pivot Sheet dan Data Sheet masing-masing.

Langkah 9: Temukan baris yang terakhir digunakan dan kolom yang terakhir digunakan di lembar data.

Langkah 10: Sekarang atur rentang pivot dengan menggunakan baris terakhir & kolom terakhir.

Ini akan mengatur rentang data dengan sempurna. Ini akan secara otomatis memilih rentang data meskipun ada penambahan atau penghapusan data dalam lembar data.

Langkah 11: Sebelum membuat tabel pivot, kita perlu membuat cache pivot. Setel variabel cache pivot dengan menggunakan kode VBA di bawah ini.

Langkah 12: Sekarang buat tabel pivot kosong.

Langkah 13: Setelah memasukkan tabel pivot, kita perlu memasukkan bidang baris terlebih dahulu. Jadi saya akan memasukkan bidang baris sebagai kolom Negara saya.

Catatan: Unduh buku kerja untuk memahami kolom data.

Langkah 14: Sekarang satu item lagi akan saya masukkan ke kolom baris sebagai item posisi kedua. Saya akan memasukkan Produk sebagai item baris kedua ke bidang baris.

Langkah 15: Setelah memasukkan kolom ke bidang baris, kita perlu memasukkan nilai ke bidang kolom. Saya akan memasukkan "Segmen" ke bidang kolom.

Langkah 16: Sekarang kita perlu memasukkan angka ke bidang data. Jadi masukkan "Penjualan" ke bidang data.

Langkah 17: Kita selesai dengan bagian ringkasan tabel pivot, sekarang kita perlu memformat tabel. Untuk memformat tabel pivot gunakan kode di bawah ini.

Catatan: Untuk memiliki lebih banyak gaya tabel yang berbeda, rekam makro mereka dan dapatkan gaya tabel.

Untuk menampilkan baris item nilai yang diajukan dalam bentuk tabel tambahkan kode di bawah ini di bagian bawah.

Ok, kita sudah selesai jika kita menjalankan kode ini menggunakan tombol F5 atau secara manual, kita harus mendapatkan tabel pivot seperti ini.

Seperti ini menggunakan pengkodean VBA, kita dapat mengotomatiskan proses pembuatan tabel pivot.

Untuk referensi Anda, saya telah memberikan kode di bawah ini.

Sub PivotTable () Redupkan PTable Sebagai PivotTable Redup PCache Sebagai PivotCache Redupkan PRange As Range Redup PSheet Sebagai Lembar Kerja Dim DSheet Sebagai Lembar Kerja Dim LR Selama Redup LC Selama Terjadi Kesalahan Lanjutkan Aplikasi Berikutnya.DisplayAlerts = Aplikasi Palsu.ScreenUpdating = Lembar Kerja Palsu ("PivotUpdating = Lembar Kerja Palsu (" Pivot Sheet "). Delete 'Ini akan menghapus worksheets worksheets pivot table yang ada.Add After: = ActiveSheet' Ini akan menambah worksheet baru ActiveSheet.Name =" Pivot Sheet "'Ini akan mengganti nama worksheet menjadi" Pivot Sheet "Pada Kesalahan GoTo 0 Set PSheet = Worksheets ("Pivot Sheet") Set DSheet = Worksheets ("Data Sheet") 'Temukan baris dan kolom yang terakhir digunakan di lembar data LR = DSheet.Cells (Rows.Count, 1) .End (xlUp) .Row LC = DSheet.Cells (1, Columns.Count) .End (xlToLeft) .Column 'Mengatur rentang data tabel pivot Set PRange = DSheet.Sel (1, 1) .Resize (LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = PRange)' Buat tabel pivot kosong Set PTable = PCache.CreatePivotTable (TableDestination: = PSheet. Sel (1, 1), TableName: = "Sales_Report") 'Masukkan negara ke Baris yang Diarsipkan dengan PSheet.PivotTables ("Sales_Report"). PivotFields ("Country") .Orientation = xlRowField .Position = 1 End With' Insert Product to Row Diarsipkan & posisi 2 Dengan PSheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 2 Akhiri Dengan 'Sisipkan Segmen ke Kolom Diarsipkan & posisi 1 Dengan PSheet.PivotTables ("Sales_Report"). PivotFields ("Segment") .Orientation = xlColumnField .Position = 1 Akhiri Dengan 'Sisipkan kolom Penjualan ke bidang data Dengan PSheet.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 Akhiri Dengan 'Format Pivot Table PSheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True PSheet.PivotTables ("Sales_Report"). TableStyle2 =. "PivotStyleMedium14" 'Tampil dalam bentuk Tabel PSheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = Aplikasi Sejati.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = Aplikasi Sejati.ScreenUpdating = True End Sub