Bagaimana cara memperbarui rentang tabel pivot secara otomatis?

Memperbarui tabel pivot itu menyusahkan, bukan? Jika Anda sering menggunakan tabel pivot dalam pekerjaan Anda, saya yakin Anda bisa memahaminya.

Masalahnya adalah, setiap kali Anda menambahkan data baru ke lembar sumber, Anda perlu memperbarui rentang sumber tabel pivot sebelum menyegarkan tabel pivot Anda.

Sekarang bayangkan jika Anda menambahkan data ke lembar sumber setiap hari, Anda perlu memperbarui rentang sumber setiap hari.

Dan setiap kali mengubah rentang tabel pivot menjadi berantakan. Ya benar, semakin sering Anda menambahkan data, semakin banyak Anda perlu memperbarui rentang sumbernya.

Jadi intinya Anda memerlukan metode untuk memperbarui rentang sumber secara otomatis ketika Anda menambahkan data baru.

CATATAN : Tabel pivot adalah bagian dari KETERAMPILAN EXCEL MENENGAH .

Terapkan tabel untuk memiliki rentang tabel pivot yang diperbarui secara otomatis

Beberapa hari yang lalu saya bertanya kepada John Michaloudis tentang tip tabel pivot jutaan dolarnya. Bunyinya: Masukkan data sumber Anda ke dalam tabel. Percayalah, ini tip satu juta dolar.

Dengan menerapkan tabel di data sumber, Anda tidak perlu mengubah rentang sumber tabel pivot berulang kali.

Setiap kali Anda menambahkan data baru, rentang tabel pivot secara otomatis diperbarui.

Konversi data menjadi tabel sebelum membuat tabel pivot

Setiap kali sebelum membuat tabel pivot, pastikan untuk menerapkan tabel tersebut ke data sumber menggunakan langkah-langkah berikut.

1. Pilih salah satu sel di data Anda.
2. Gunakan tombol pintas Ctrl + T atau navigasikan ke → Sisipkan Tab → Tabel → Tabel.
3. Anda akan mendapatkan pop-up dengan rentang data Anda saat ini.
   
4. Klik Oke.
5. Sekarang untuk membuat tabel pivot, pilih sel mana saja di data Anda. Buka → tab Desain → Alat → Ringkas dengan PivotTable.
   
6. Klik Oke.

Sekarang, setiap kali Anda menambahkan data baru ke lembar data, rentang tabel pivot diperbarui secara otomatis dan Anda hanya perlu menyegarkan tabel pivot Anda.

Ubah data menjadi tabel setelah membuat tabel pivot

Jika Anda sudah memiliki tabel pivot di lembar kerja Anda, Anda bisa menggunakan langkah-langkah berikut untuk mengonversi sumber data Anda menjadi tabel.

1. Pilih salah satu sel di sumber data Anda.
2. Gunakan tombol pintas Ctrl + T atau navigasikan ke → Sisipkan Tab → Tabel → Tabel.
3. Anda akan mendapatkan pop-up dengan rentang data Anda saat ini.
4. Klik Oke.
5. Sekarang, pilih salah satu sel di tabel pivot Anda dan buka → Analisis → Data → Edit Sumber Data → Edit Sumber Data (menu tarik-turun).
6. Anda akan mendapatkan pop-up untuk memilih kembali sumber data Anda atau Anda juga dapat memasukkan nama tabel di entri rentang.
   
7. Klik Oke.

Mulai sekarang, setiap kali Anda menambahkan data baru di lembar sumber, rentang tabel pivot akan bertambah untuk memperbaruinya secara otomatis.

Buat rentang tabel pivot dinamis dengan fungsi OFFSET

Cara terbaik lainnya untuk memperbarui rentang tabel pivot secara otomatis adalah dengan menggunakan rentang dinamis.

Rentang dinamis dapat diperluas secara otomatis setiap kali Anda menambahkan data baru ke lembar sumber Anda. Berikut langkah-langkah membuat rentang dinamis.

1. Buka → Tab Rumus → Nama yang Ditetapkan → Manajer Nama.
2. Setelah Anda mengklik pengelola nama, Anda akan mendapatkan jendela pop-up.
   
3. Di jendela Manajer Nama Anda, klik Baru untuk membuat rentang bernama.
4. Di jendela nama baru Anda, masukkan
   1. Nama untuk rangkaian produk baru Anda. Saya menggunakan nama “SourceData”.
   2. Tentukan luas jangkauannya. Anda bisa menentukan antara lembar kerja atau buku kerja saat ini.
   3. Tambahkan komentar untuk mendeskripsikan rentang nama Anda. Masukkan rumus di bawah ini ke dalam bilah input “Rujuk ke”.
      

       =OFFSET(PivotTableData!$A$1,0,0,COUNTA(PivotTableData!$A:$A),COUNTA(PivotTableData!$1:$1))

5. Pada akhirnya, klik OK.

Sekarang Anda memiliki rentang dinamis untuk membuat tabel pivot.

Yang perlu Anda lakukan hanyalah membuat tabel pivot dengan data sumber Anda dan kemudian mengubah sumber dengan rentang bernama menggunakan metode yang sama yang saya gunakan pada metode tabel pertama.

Setelah Anda menambahkan data baru ke lembar sumber, cukup segarkan tabel pivot Anda.

Bagaimana cara kerja rumus ini?

Pada rumus di atas, saya menggunakan fungsi offset untuk membuat rentang dinamis.

Saya menyebutkan sel A1 sebagai titik awal, lalu tanpa menyebutkan baris dan kolom, saya menentukan tinggi dan lebar rentang menggunakan COUNTA.

COUNTA akan menghitung sel dengan nilai dari kolom A dan baris 1 dan memberitahu offset untuk memperluas tinggi dan lebarnya.

Satu-satunya hal yang perlu Anda perhatikan adalah tidak ada sel kosong antara kolom A dan baris 1.

Perbarui tabel pivot menggunakan kode VBA

Kebanyakan orang suka menggunakan kode VBA. Jadi inilah kode yang digunakan untuk memperbarui rentang tabel pivot dengan VBA.

 Sub UpdatePivotTableRange() Dim Data_Sheet As Worksheet Dim Pivot_Sheet As Worksheet Dim StartPoint As Range Dim DataRange As Range Dim PivotName As String Dim NewRange As String Dim LastCol As Long Dim lastRow As Long 'Set Pivot Table & Source Worksheet Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3") 'Enter in Pivot Table Name PivotName = "PivotTable2" 'Defining Staring Point & Dynamic Range Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol)) NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1) 'Change Pivot Table Data Source Range Address Pivot_Sheet.PivotTables(PivotName). _ ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange) 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated." End Sub

Hal-hal yang perlu Anda ubah sebelum menggunakannya di buku kerja Anda.

1. Baris 13: Ubah nama lembar kerja sumber.
2. Baris 14: Mengubah nama lembar tabel pivot.
3. Baris 17: Ubah nama tabel pivot.

Jika Anda masih menghadapi masalah saat menggunakan kode ini, silakan tulis kepada saya di kotak komentar. Sekarang izinkan saya menunjukkan cara kerja kode ini sehingga Anda dapat dengan mudah memodifikasinya sesuai kebutuhan Anda.

 Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3")

Di bagian kode di atas, kami telah menentukan variabel tabel pivot dan lembar kerja dari data sumber. Anda dapat mengubah nama spreadsheet dari sini.

 PivotName = "PivotTable2"

Di bagian kode di atas, masukkan nama tabel pivot tempat Anda ingin menggunakan kode ini.

 Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))

Bagian kode di atas akan membuat rentang dinamis menggunakan sel A1 pada lembar kerja sumber data.

Ini akan memeriksa kolom terakhir dan baris terakhir dengan data untuk membuat rentang dinamis. Setiap kali Anda menjalankan makro ini, rentang dinamis baru akan dibuat.

 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated."

Bagian kode di atas akan menyegarkan tabel pivot dan menampilkan pesan kepada pengguna bahwa tabel pivot sekarang telah diperbarui.