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 setiap laporan atau dasbor, di excel mudah membuat tabel dengan tombol tetapi di VBA kita harus menulis beberapa kode untuk mengotomatiskan tabel pivot kita, sebelum excel 2007 dan versi yang lebih lama di VBA kita tidak perlu melakukannya buat cache untuk tabel pivot tetapi di excel 2010 dan versi yang lebih baru, cache diperlukan.
VBA dapat menghemat banyak waktu kami di tempat kerja. Meskipun menguasainya tidak semudah itu 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
Untuk membuat tabel pivot, penting untuk memiliki data. Untuk ini, saya telah membuat beberapa data dummy. Anda bisa 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 () Redup PTable Sebagai PivotTable End Sub
Langkah 2: Sebelum kita membuat tabel pivot terlebih dahulu, kita perlu membuat cache pivot untuk menentukan sumber data.
Di lembar kerja biasa, tabel pivot 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 memerlukan lembar terpisah untuk menambahkan lembar kerja pada tabel pivot untuk mendeklarasikan variabel sebagai lembar kerja.
Kode:
Redupkan PSheet Sebagai Lembar Kerja
Langkah 5: Demikian pula, untuk mereferensikan data yang berisi lembar kerja, nyatakan satu variabel lagi sebagai Lembar Kerja.
Kode:
Redupkan Lembar Kerja Sebagai Lembar Kerja
Langkah 6: Terakhir, 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. Sebelumnya, jika ada lembar pivot di sana, maka kita perlu menghapusnya.
Langkah 8: Sekarang, atur variabel objek PSheet dan DSheet ke Lembar Pivot dan Lembar Data.
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, bahkan jika ada penambahan atau penghapusan data di lembar data.
Langkah 11: Sebelum kita membuat tabel pivot, kita perlu membuat cache pivot. Atur 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 yang 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 kolom baris, kita perlu memasukkan nilai ke kolom kolom. Saya akan memasukkan "Segmen" ke bidang kolom.
Langkah 16: Sekarang, kita perlu memasukkan angka ke dalam 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.
Untuk memperlihatkan 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() 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
