Jadual Pivot Excel VBA
Jadual Pivot adalah nadi untuk merangkum laporan sejumlah besar data. Kami juga dapat mengautomasikan proses membuat jadual pangsi melalui pengekodan VBA. Mereka adalah bahagian penting dari sebarang laporan atau papan pemuka, di excel mudah membuat jadual dengan butang tetapi di VBA kita harus menulis beberapa kod untuk mengautomasikan jadual pangsi kita, sebelum excel 2007 dan versi lama di VBA kita tidak perlu buat cache untuk jadual pangsi tetapi pada excel 2010 dan versi yang lebih baru diperlukan cache.
VBA dapat menjimatkan banyak masa untuk kita di tempat kerja kita. Walaupun menguasainya tidak semudah itu tetapi perlu menghabiskan masa untuk belajar ini. Saya mengambil masa selama 6 bulan untuk memahami proses membuat jadual pangsi melalui VBA. Anda tahu apa yang telah membuat keajaiban selama 6 bulan itu kerana saya membuat banyak kesilapan semasa membuat jadual pangsi.
Tetapi yang sebenarnya ialah saya telah belajar dari kesilapan saya, dan sekarang saya menulis artikel ini untuk menunjukkan kepada anda cara membuat jadual pangsi menggunakan kod.
Dengan hanya satu klik butang, kita dapat membuat laporan.
Langkah Membuat Jadual Pangsi di VBA
Untuk membuat jadual pangsi, penting untuk mempunyai data. Untuk ini, saya telah membuat beberapa data palsu. Anda boleh memuat turun buku kerja untuk diikuti dengan saya dengan data yang sama.
Langkah 1: Pivot Table adalah objek untuk merujuk jadual pangsi menyatakan pemboleh ubah sebagai PivotTables.
Kod:
Sub PivotTable () Dim PTable Sebagai PivotTable End Sub
Langkah 2: Sebelum kita membuat jadual pangsi terlebih dahulu, kita perlu membuat pivot cache untuk menentukan sumber data.
Dalam lembaran kerja biasa, jadual pangsi tanpa menyusahkan kita akan membuat pivot cache di latar belakang. Tetapi di VBA, kita harus membuat.
Untuk ini, tentukan pemboleh ubah PivotCache.
Kod:
Redupkan PCache Sebagai PivotCache
Langkah 3: Untuk menentukan julat data pangsi, tentukan pemboleh ubah sebagai julat.
Kod:
Dim PRange Sebagai Julat
Langkah 4: Untuk memasukkan jadual pangsi, kita memerlukan lembaran berasingan untuk menambahkan lembaran kerja untuk jadual pangsi untuk menyatakan pemboleh ubah sebagai lembaran kerja.
Kod:
Dim PSheet Sebagai Lembaran Kerja
Langkah 5: Begitu juga, untuk merujuk data yang mengandungi lembaran kerja menyatakan satu pemboleh ubah lagi sebagai Lembaran Kerja.
Kod:
Dim DSheet Sebagai Lembaran Kerja
Langkah 6: Akhirnya, untuk mencari baris & lajur yang terakhir digunakan, tentukan dua lagi pemboleh ubah sebagai Long.
Kod:
Dim LR Selagi Dim LC Selagi
Langkah 7: Sekarang, kita perlu memasukkan helaian baru untuk membuat jadual pangsi. Sebelum itu, jika ada kepingan pivot di sana, maka kita perlu menghapusnya.
Langkah 8: Sekarang, tetapkan objek PSheet dan DSheet pemboleh ubah ke Pivot Sheet dan Data Sheet, masing-masing.
Langkah 9: Cari baris terakhir dan lajur terakhir yang digunakan dalam lembaran data.
Langkah 10: Sekarang tetapkan julat pangsi dengan menggunakan baris terakhir & lajur terakhir.
Ini akan menetapkan julat data dengan sempurna. Secara automatik akan memilih julat data, walaupun ada penambahan atau penghapusan data dalam lembar data.
Langkah 11: Sebelum kita membuat jadual pangsi, kita perlu membuat pivot cache. Tetapkan pemboleh ubah pivot cache dengan menggunakan kod VBA di bawah.
Langkah 12: Sekarang, buat jadual pangsi kosong.
Langkah 13: Setelah memasukkan jadual pangsi, kita perlu memasukkan medan baris terlebih dahulu. Oleh itu, saya akan memasukkan medan baris sebagai lajur Negara saya.
Catatan: Muat turun buku kerja untuk memahami lajur data.
Langkah 14: Sekarang, satu lagi item yang akan saya masukkan ke medan baris sebagai item kedudukan kedua. Saya akan memasukkan Produk sebagai item baris kedua ke medan baris.
Langkah 15: Setelah memasukkan lajur ke medan baris, kita perlu memasukkan nilai ke medan lajur. Saya akan memasukkan "Segmen" ke medan lajur.
Langkah 16: Sekarang, kita perlu memasukkan nombor ke dalam medan data. Oleh itu, masukkan "Penjualan" ke medan data.
Langkah 17: Kami selesai dengan bahagian ringkasan jadual pangsi. Sekarang kita perlu memformat jadual. Untuk memformat jadual pangsi, gunakan kod di bawah.
Untuk menunjukkan item item baris yang diajukan dalam bentuk jadual, tambahkan kod di bawah di bahagian bawah.
Ok, kita selesai sekiranya kita menjalankan kod ini menggunakan kekunci F5 atau secara manual, kita harus mendapatkan jadual pangsi seperti ini.
Seperti ini, menggunakan pengekodan VBA, kita dapat mengotomatisasi proses membuat jadual pangsi.
Untuk rujukan anda, saya telah memberikan kod di bawah.
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
