Jadual Pangsi VBA | Langkah Membuat Jadual Pangsi di VBA

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 bernilai 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 melakukan 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

Anda boleh memuat turun Templat Jadual Pangsi VBA ini di sini - Templat Jadual Pangsi 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: Jadual Pivot adalah objek untuk merujuk jadual pangsi menyatakan pemboleh ubah sebagai Jadual Pangsi.

Kod:

 Sub PivotTable () Dim PTable Sebagai Sub PivotTable Sub

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

Dalam jadual pangsi lembaran kerja biasa tanpa menyusahkan kita akan membuat pivot cache di latar belakang. Tetapi dalam VBA kita harus mencipta.

Untuk ini tentukan pemboleh ubah PivotCache.

Kod:

 Kekurangan 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 yang terpisah untuk menambah lembaran kerja untuk jadual pangsi 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 ada 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 lembar data.

Langkah 10: Sekarang tetapkan julat pangsi dengan menggunakan baris terakhir & lajur terakhir.

Ini akan menetapkan julat data dengan sempurna. Secara automatik ia akan memilih julat data walaupun terdapat 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 item lagi 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 medan data. Oleh itu, masukkan "Penjualan" ke medan data.

Langkah 17: Kita selesai dengan bahagian ringkasan jadual pangsi, sekarang kita perlu memformat jadual. Untuk memformat jadual pangsi, gunakan kod di bawah.

Catatan: Untuk mempunyai gaya jadual yang lebih berbeza, merekamnya secara makro dan dapatkan gaya jadual.

Untuk menunjukkan item baris yang diisi nilai 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 Sebagai PivotTable Dim PCache Sebagai 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 Lembar "). Padam 'Ini akan menghapus lembaran kerja jadual pangsi yang ada. Tambah Selepas: = Lembaran Aktif' Ini akan menambahkan lembaran kerja baru ActiveSheet.Name =" Lembaran Pivot "'Ini akan menamakan semula lembaran kerja sebagai" Lembaran Pivot "Pada Kesalahan GoTo 0 Set PSheet = Worksheets ("Pivot Sheet") Set DSheet = Worksheets ("Data Sheet") 'Cari baris dan lajur terakhir yang digunakan dalam lembaran data LR = DSheet.Cells (Rows.Count, 1). End (xlUp) .Row LC = DSheet.Cells (1, Columns.Count) .End (xlToLeft). Column 'Tetapkan julat data jadual pangsi Tetapkan PRange = DSheet.Sel (1, 1). Ubah ukuran (LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = PRange)' Buat jadual pangsi kosong Set PTable = PCache.CreatePivotTable (TableDestination: = PSheet. Sel (1, 1), TableName: = "Sales_Report") 'Masukkan negara ke baris yang difailkan dengan PSheet.PivotTables ("Sales_Report"). PivotFields ("Country"). Orientasi = xlRowField .Posisi = 1 Tamat Dengan' Masukkan Produk ke Row Filed & position 2 With PSheet.PivotTables ("Sales_Report"). PivotFields ("Product"). Orientasi = xlRowField .Position = 2 Tamat Dengan 'Insert Segment to Column Filed & position 1 With PSheet.PivotTables ("Sales_Report"). PivotFields ("Segmen"). Orientasi = xlColumnField .Posisi = 1 Berakhir dengan 'Masukkan Jualan lajur ke medan data Dengan PSheet.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 End with 'Format Pivot Table PSheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True PSheet.PivotTables ("Sales_Reptyables (" Sales_Reptyable) ". "PivotStyleMedium14" 'Tunjukkan dalam bentuk Jadual PSheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub