Tabel Pivot VBA

Tabel Pivot Excel VBA

Tabel Pivot adalah inti dari meringkas laporan sejumlah besar data. Kami juga dapat mengotomatiskan pembuatan Tabel Pivot melalui pengkodean VBA. Mereka adalah bagian penting dari setiap laporan atau dasbor. Membuat tabel dengan tombol di Excel itu mudah, tetapi di VBA, kita harus menulis beberapa kode untuk mengotomatiskan Tabel Pivot kita. Sebelum Excel 2007 dan versi yang lebih lama, kami tidak perlu membuat cache untuk Tabel Pivot. Namun di Excel 2010 dan versi yang lebih baru, diperlukan cache.

VBA dapat menghemat banyak waktu bagi kita di tempat kerja. Meskipun menguasainya tidak mudah, ada baiknya waktu belajar. Misalnya, kami membutuhkan waktu 6 bulan untuk memahami proses pembuatan tabel pivot melalui VBA. Kamu tahu apa? 6 bulan tersebut telah memberikan keajaiban bagi saya karena kami membuat banyak kesalahan saat mencoba membuat Tabel Pivot.

Tapi sebenarnya kami telah belajar dari kesalahan saya. Jadi, kami menulis artikel ini untuk menunjukkan cara membuat Tabel Pivot menggunakan kode.

Hanya dengan satu klik tombol, kita dapat membuat laporan.

Langkah-langkah Membuat Tabel Pivot di VBA

Penting untuk memiliki data untuk membuat Tabel Pivot. Untuk ini, kami telah membuat beberapa data dummy. Anda dapat mengunduh buku kerja untuk diikuti dengan data yang sama.

Langkah 1: Tabel Pivot adalah objek yang mereferensikan Tabel Pivot dan mendeklarasikan variabel sebagai PivotTable.

Kode:

Sub PivotTable() Redupkan PTable Sebagai PivotTable End Sub

Langkah 2: Sebelum membuat Tabel Pivot, kita perlu membuat cache pivot untuk menentukan sumber data.

Tabel Pivot membuat cache pivot di latar belakang tanpa mengganggu kita di lembar kerja biasa. Tapi di VBA, kita harus membuat.

Untuk ini, tentukan variabel “PivotCache”.

Kode:

Redupkan PCache Sebagai PivotCache

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

Kode:

Dim PRange As Range

Langkah 4: Untuk menyisipkan Tabel Pivot, kita memerlukan lembar terpisah untuk menambahkan lembar kerja agar Tabel Pivot mendeklarasikan variabel sebagai ‘Lembar Kerja.’

Kode:

Redupkan Lembar Kerja Sebagai Lembar Kerja

Langkah 5: Demikian pula, untuk mereferensikan data lembar kerja, nyatakan satu variabel lagi sebagai “Lembar Kerja”.

Kode:

Dim DSheet Sebagai Lembar Kerja

Langkah 6: Terakhir, untuk menemukan baris & kolom yang terakhir digunakan, tentukan dua variabel lagi sebagai “Panjang”.

Kode:

Redupkan LR Selama Redupkan LC Selamanya

Langkah 7: Kita perlu menyisipkan lembar baru untuk membuat Tabel Pivot. Sebelum itu, jika ada pivot sheet, maka kita perlu menghapusnya.

Langkah 8: Sekarang, atur variabel objek “PSheet” dan “DSheet” menjadi “Pivot Sheet” dan “Data Sheet,” masing-masing.

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

Langkah 10: Tetapkan rentang pivot menggunakan baris terakhir dan kolom terakhir.

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

Langkah 11: Sebelum membuat PivotTable, kita perlu membuat cache pivot. Tetapkan variabel cache pivot dengan menggunakan kode VBA di bawah ini Kode VBA Kode VBA mengacu pada serangkaian instruksi yang ditulis oleh pengguna dalam bahasa pemrograman Aplikasi Visual Basic pada Editor Visual Basic (VBE) untuk melakukan tugas tertentu.Baca lebih lanjut.

Langkah 12: Sekarang, buat PivotTable kosong.

Langkah 13: Setelah memasukkan PivotTable, kita harus memasukkan field baris terlebih dahulu. Jadi, kami akan memasukkan bidang baris sebagai kolom ‘Negara’ saya.

Catatan: Unduh buku kerja untuk memahami kolom data.

Langkah 14: Kami akan memasukkan satu item lagi ke kolom baris sebagai item posisi kedua. Kami akan memasukkan “Produk” sebagai item baris kedua ke kolom baris.

Langkah 15: Setelah memasukkan kolom ke dalam bidang baris, kita perlu memasukkan nilai ke dalam bidang kolom. Kami akan memasukkan “Segmen” ke dalam bidang kolom.

Langkah 16: Kita perlu memasukkan angka ke dalam bidang data. Jadi masukkan “Penjualan” ke dalam bidang data.

Langkah 17: Kami telah menyelesaikan bagian ringkasan Tabel Pivot. Sekarang, kita perlu memformat tabel. Untuk memformat tabel pivot, gunakan kode di bawah ini.

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

Untuk menampilkan item nilai bidang baris dalam bentuk tabel, tambahkan kode di bawah ini di bagian bawah.

Kami telah menyelesaikannya sekarang. Jika kita menjalankan kode ini menggunakan tombol F5 atau secara manual, kita akan mendapatkan PivotTable seperti ini.

Seperti ini, kita dapat mengotomatiskan pembuatan PivotTable menggunakan pengkodean VBA.

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

Sub PivotTable() Dim PTable Sebagai PivotTable Dim PCache Sebagai PivotCache Dim PRange As Range Dim Psheet As Worksheet Dim DSheet As Worksheet Dim LR As Long Dim LC Selama Error Resume NextError Resume NextVBA On Error Resume Statement adalah aspek penanganan kesalahan yang digunakan untuk mengabaikan baris kode yang menyebabkan kesalahan terjadi dan melanjutkan dengan baris berikutnya tepat setelah baris kode dengan kesalahan.baca lebih lanjut Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets(“Pivot Sheet”).Delete ‘This will delete lembar kerja tabel pivot yang ada Worksheets.Add After:=ActiveSheet ‘ Ini akan menambahkan lembar kerja baru ActiveSheet.Name = “Pivot Sheet” ‘ Ini akan mengganti nama lembar kerja menjadi “Pivot Sheet” On Error GoTo 0 Set PSheet = Worksheets(“Pivot Sheet” ) Atur 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 ‘Atur rentang data tabel pivot Atur PRange = DSheet.Cells(1, 1).Resize(LR, LC) ‘Set pivot cahe Setel PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=PRange) ‘Buat tabel pivot kosong Setel PTable = PCache.CreatePivotTable(TableDestination: =PSheet.Cells(1, 1), TableName:=”Sales_Report”) ‘Masukkan negara ke Baris Diarsipkan Dengan PSheet.PivotTables(“Sales_Report”).PivotFields(“Negara”) .Orientasi = xlRowField .Position = 1 Akhiri Dengan ‘ Sisipkan 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(“Segmen”) .Orientasi = xlColumnField .Position = 1 Akhiri Dengan ‘Masukkan kolom Penjualan ke kolom 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” ‘Tampilkan dalam bentuk Tabular PSheet.PivotTables(“Sales_Report”).RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub

Artikel yang Direkomendasikan

Artikel ini adalah panduan untuk Tabel Pivot VBA. Di sini, kita mempelajari cara membuat PivotTable di Excel menggunakan kode VBA, contoh praktis, dan template yang dapat diunduh. Di bawah ini Anda dapat menemukan beberapa artikel Excel VBA yang berguna: –