Bagaimana cara membuat tabel pivot dengan vba?

Sebelum saya memberikan panduan ini dan mulai menggunakan VBA untuk membuat tabel pivot , izinkan saya memberi tahu Anda sesuatu.

Saya belajar menggunakan VBA ENAM tahun yang lalu. Dan pertama kali saya menulis kode makro untuk membuat tabel pivot, gagal.

Sejak itu, saya belajar lebih banyak dari pengkodean saya yang buruk daripada kode yang benar-benar berfungsi.

Hari ini saya akan menunjukkan cara sederhana untuk mengotomatisasi tabel pivot Anda menggunakan kode makro.

Biasanya, saat Anda memasukkan tabel pivot ke dalam spreadsheet, hal itu terjadi melalui proses yang sederhana, namun keseluruhan proses ini sangat cepat sehingga Anda tidak pernah menyadari apa yang terjadi.

Di VBA seluruh prosesnya sama, hanya dijalankan menggunakan kode. Dalam panduan ini, saya akan menunjukkan setiap langkah dan menjelaskan cara menulis kode untuk itu.

Lihat saja contoh di bawah ini, di mana Anda bisa menjalankan kode makro ini dengan sebuah tombol, dan ini mengembalikan tabel pivot baru di lembar kerja baru dalam sekejap.

Tanpa basa-basi lagi, mari mulai menulis kode makro untuk membuat tabel pivot.

8 Langkah Sederhana Menulis Kode Makro di VBA untuk Membuat Tabel Pivot di Excel

Demi kenyamanan Anda, saya telah membagi seluruh proses menjadi 8 langkah sederhana. Setelah mengikuti langkah-langkah ini, Anda akan dapat mengotomatiskan semua tabel pivot Anda.

Pastikan untuk mengunduh file ini dari sini untuk mengikuti.

1. Mendeklarasikan variabel

Langkah pertama adalah mendeklarasikan variabel yang perlu kita gunakan dalam kode kita untuk mendefinisikan berbagai hal.

 'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long

Dalam kode di atas kami mendeklarasikan:

PSheet: untuk membuat sheet untuk tabel pivot baru.
DSheet: untuk digunakan sebagai lembar teknis.
PChache: Gunakan sebagai nama untuk cache tabel pivot.
PTable: Gunakan sebagai nama untuk tabel pivot kita.
PRrange: untuk mengatur rentang data sumber.
LastRow dan LastCol: untuk mendapatkan baris dan kolom terakhir dari rentang data kita.

2. Masukkan spreadsheet baru

Sebelum membuat tabel pivot, Excel menyisipkan lembar kosong lalu membuat tabel pivot baru di atasnya.

masukkan lembar kerja baru untuk menggunakan vba untuk membuat tabel pivot di excel

Dan kode di bawah ini akan melakukan hal yang sama untuk Anda.

Ini akan menyisipkan lembar kerja baru dengan nama “Pivot Table” sebelum lembar kerja aktif dan jika sudah ada lembar kerja dengan nama yang sama maka akan dihapus terlebih dahulu.

Setelah menyisipkan lembar kerja baru, kode ini akan mengatur nilai variabel PSsheet pada lembar kerja tabel pivot dan DSheet pada lembar kerja data sumber.

 'Declare Variables On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data")

Pastikan untuk mengganti nama lembar kerja di kode dengan nama yang Anda miliki di data Anda.

3. Tetapkan rentang data

Sekarang langkah selanjutnya adalah mengatur rentang data dari lembar kerja sumber. Di sini Anda perlu memperhatikan satu hal, Anda tidak dapat menentukan rentang sumber tetap.

Anda memerlukan kode yang dapat mengidentifikasi semua data di lembar sumber. Dan di bawah ini adalah kodenya:

 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

Kode ini akan dimulai dari sel pertama tabel data dan pilih hingga baris terakhir, lalu hingga kolom terakhir.

Dan terakhir, tetapkan rentang yang dipilih ini sebagai sumbernya. Bagian terbaiknya adalah Anda tidak perlu mengubah sumber data setiap kali membuat tabel pivot.

4. Buat cache pivot

Di Excel 2000 dan yang lebih baru, sebelum membuat tabel pivot, Anda harus membuat cache pivot untuk mengatur sumber data.

Biasanya, saat Anda membuat tabel pivot, Excel secara otomatis membuat cache pivot tanpa meminta Anda, namun saat Anda perlu menggunakan VBA, Anda harus menulis beberapa kode untuk itu.

 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable")

Kode ini bekerja dalam dua cara, pertama, mengatur cache pivot menggunakan sumber data, dan kedua, mengatur alamat sel di lembar kerja yang baru disisipkan untuk menyisipkan tabel pivot.

Anda dapat mengubah posisi tabel pivot dengan memodifikasi kode ini.

5. Sisipkan tabel pivot kosong

Setelah cache pivot, langkah selanjutnya adalah memasukkan tabel pivot yang kosong. Ingatlah bahwa saat Anda membuat tabel pivot, Anda selalu mendapatkan pivot kosong terlebih dahulu, lalu Anda menetapkan semua nilai, kolom, dan baris.

masukkan pivot kosong untuk menggunakan vba untuk membuat tabel pivot di excel

Kode ini akan melakukan hal yang sama:

 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

Kode ini membuat tabel pivot kosong dan menamainya “SalesPivotTable”. Anda dapat mengubah nama ini dari kode itu sendiri.

6. Sisipkan kolom baris dan kolom

Setelah membuat tabel pivot kosong, langkah selanjutnya adalah menyisipkan kolom baris dan kolom seperti yang biasa Anda lakukan.

Untuk setiap kolom baris dan kolom, Anda harus menulis kode. Disini kita ingin menambahkan tahun dan bulan pada kolom baris dan luas pada kolom.

masukkan kolom baris kolom untuk menggunakan vba untuk membuat tabel pivot di excel

Ini kodenya:

 'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With

Dalam kode ini Anda menyebutkan tahun dan bulan sebagai dua bidang. Sekarang jika Anda melihat kodenya, Anda akan melihat bahwa ada nomor posisi juga. Nomor posisi ini menentukan urutan bidang.

Setiap kali Anda perlu menambahkan beberapa bidang (Baris atau Kolom), tentukan posisinya. Dan Anda dapat memodifikasi kolom dengan mengubah namanya dari kode.

7. Masukkan kolom data

Hal utama adalah menentukan bidang nilai di tabel pivot Anda.

Kode untuk mendefinisikan nilai berbeda dengan mendefinisikan baris dan kolom karena disini kita perlu mendefinisikan format angka, posisi dan fungsi.

 'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With

Anda dapat menambahkan jumlah sebagai kolom nilai dengan kode di atas. Dan kode ini akan memformat nilainya sebagai angka dengan pemisah (,).

Kami menggunakan xlsum untuk menambahkan nilai, tetapi Anda juga dapat menggunakan xlcount dan fungsi lainnya.

8. Format tabel pivot

Pada akhirnya, Anda perlu menggunakan kode untuk memformat tabel pivot Anda. Biasanya ada format default dalam tabel pivot, namun Anda dapat mengubah format ini.

Dengan VBA, Anda dapat mengatur gaya pemformatan dalam kode.

gunakan vba untuk membuat tabel pivot dalam format excel

Kodenya adalah:

 'Format Pivot TableActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"

Kode di atas akan menerapkan strip garis dan gaya “Pivot Style Medium 9”, tetapi Anda juga dapat menggunakan gaya lain dari tautan ini .

Akhirnya, kode Anda siap digunakan.

[KODE LENGKAP] Gunakan VBA untuk Membuat PivotTable di Excel – Salin dan Tempel Makro

 Sub InsertPivotTable() 'Macro By Exceladvisor.org 'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long 'Insert a New Blank Worksheet On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data") 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol) 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable") 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable") 'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With 'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields ("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With 'Format Pivot Table ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9" End Sub

Unduh file contoh

Siap

Tabel pivot pada lembar kerja yang ada

Kode yang kita gunakan di atas membuat PivotTable pada lembar kerja baru, namun terkadang Anda perlu menyisipkan PivotTable ke dalam lembar kerja yang sudah ada di buku kerja.

Pada kode di atas (tabel pivot di lembar kerja baru), pada bagian tempat Anda menulis kode untuk menyisipkan lembar kerja baru, lalu beri nama. Harap buat beberapa perubahan pada kode.

Jangan khawatir; Saya akan menunjukkan kepada Anda.

Pertama, Anda perlu menentukan lembar kerja (sudah ada di buku kerja) tempat Anda ingin menyisipkan tabel pivot.

Dan untuk ini, Anda harus menggunakan kode di bawah ini:

Daripada menyisipkan lembar kerja baru, Anda harus menentukan nama lembar kerja di variabel PSsheet.

 Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”)

Masih ada sedikit lagi yang harus dilakukan. Kode pertama yang Anda gunakan akan menghapus lembar kerja dengan nama yang sama (jika ada) sebelum memasukkan pivot.

Saat Anda menyisipkan PivotTable ke dalam lembar kerja yang sudah ada, Anda mungkin sudah memiliki PivotTable di sana dengan nama yang sama.

Maksud saya adalah Anda harus menghapus poros itu terlebih dahulu.

Untuk melakukan ini, Anda perlu menambahkan kode yang harus menghapus pivot dengan nama yang sama dari lembar kerja (jika ada) sebelum memasukkan yang baru.

Berikut adalah kode yang perlu Anda tambahkan:

 Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”) Worksheets("PivotTable").Activate On Error Resume Next ActiveSheet.PivotTables("SalesPivotTable").TableRange2.Clear

Izinkan saya memberi tahu Anda apa fungsi kode ini.

Pertama, itu hanya menetapkan PSsheet sebagai lembar kerja yang ingin Anda sisipkan tabel pivot yang sudah ada di buku kerja Anda dan menetapkan Lembar Kerja Data sebagai DSheet.

Setelah itu, ia mengaktifkan lembar kerja dan menghapus “Tabel Pivot Penjualan” darinya.

Penting: Jika nama lembar kerja di buku kerja Anda berbeda, Anda bisa mengubahnya dari kode. Saya telah menyoroti kode di mana Anda perlu melakukan ini.

Pada akhirnya,

Dengan menggunakan kode ini kami dapat mengotomatiskan tabel pivot Anda. Dan bagian terbaiknya adalah pengaturannya unik; setelah itu kita hanya perlu satu klik untuk membuat tabel pivot dan Anda dapat menghemat banyak waktu. Sekarang beritahu aku satu hal.

Pernahkah Anda menggunakan kode VBA untuk membuat tabel pivot?

Silakan bagikan pendapat Anda dengan saya di kotak komentar; Saya ingin membaginya dengan Anda dan membagikan tip ini dengan teman-teman Anda.

Artikel serupa: