Tabel Dinamis di Excel

Tabel dinamis di Excel adalah tabel tempat nilai baru dimasukkan ke dalamnya. Akibatnya, tabel menyesuaikan ukurannya dengan sendirinya. Untuk membuat tabel dinamis di Excel, kami memiliki dua metode berbeda: membuat tabel data dari bagian tabel sementara yang lain menggunakan fungsi offset. Laporan dan tabel pivot juga berubah saat data dalam tabel dinamis berubah dalam tabel dinamis.

Tabel Dinamis di Excel

Dinamis sendiri berarti suatu proses atau sistem yang dicirikan oleh perubahan atau perubahan yang konstan dalam setiap aktivitas. Demikian pula, di Excel saat kita membuat daftarExcel Saat Kita Membuat DaftarDaftar dapat dibuat di Excel untuk menentukan daftar item/nilai sebagai nilai yang telah ditentukan sebelumnya. Itu dapat dibuat menggunakan alat Validasi Data sehingga pengguna dapat memilih dari daftar daripada memasukkan nilai mereka sendiri. membaca lebih lanjut atau data dalam buku kerja dan membuat laporan darinya. Namun jika kami menambahkan data apa pun atau menghapus salah satu atau memindahkan atau mengubah data, maka keseluruhan laporan bisa menjadi tidak akurat. Excel memiliki solusi untuk itu sebagai tabel dinamis.

Sekarang, mengapa kita membutuhkan rentang dinamis atau tabel dinamis? Jawabannya adalah setiap kali daftar atau rentang data diperbarui atau dimodifikasi, itu tidak memastikan bahwa itu akan mengubah laporan sesuai dengan perubahan data.

Ada dua keuntungan utama dari tabel dinamis:

Rentang dinamis akan secara otomatis meluas atau menyusut sesuai perubahan data.
PivotTable berdasarkan tabel dinamis di Excel dapat diperbarui secara otomatis saat pivot disegarkanPivot DisegarkanUntuk menyegarkan tabel pivot, Anda dapat menggunakan metode berikut – menyegarkan tabel pivot dengan mengubah sumber data, menyegarkan tabel pivot menggunakan opsi klik kanan, menyegarkan otomatis tabel pivot menggunakan Kode VBA, segarkan tabel pivot saat Anda membuka buku kerja.baca lebih lanjut.

Bagaimana Cara Membuat Tabel Dinamis di Excel?

Ada dua cara dasar menggunakan tabel dinamis di excel – 1) Menggunakan TABLES dan 2) Menggunakan fungsi OFFSET.

#1 – Menggunakan Tabel untuk membuat Tabel Dinamis di Excel

Dengan menggunakan tabel, kita dapat membuat tabel dinamis di excel dan mendasarkan pivot di atas tabel dinamis.

Contoh

Kami memiliki data berikut,

Jika kita membuat tabel pivotBuat Tabel PivotTabel Pivot adalah alat Excel yang memungkinkan Anda mengekstrak data dalam format pilihan (dashboard/laporan) dari kumpulan data besar yang terdapat dalam lembar kerja. Itu dapat meringkas, mengurutkan, mengelompokkan, dan mengatur ulang data, serta menjalankan perhitungan kompleks lainnya di atasnya.Baca selengkapnya dengan rentang data normal ini dari A1:E6, lalu jika kita memasukkan data di baris 7, itu tidak akan tercermin di PivotTable .

Jadi, pertama-tama kita akan membuat rentang dinamis.

Pertama-tama kita harus memilih data, A1:E6.

Sekarang, di tab “Sisipkan”, kita perlu mengklik “Tabel” di bawah bagian “Tabel”.

Selanjutnya, kita harus memilih data. Kemudian, di tab “Sisipkan” di bawah bagian “Tabel” Excel, klik “Tabel Pivot”.

Akibatnya, kotak dialog akan muncul, seperti yang ditunjukkan di bawah ini, lalu klik “OK.”

Karena data kita memiliki header, kita harus ingat untuk mencentang kotak “Tabel saya memiliki header” dan klik “OK”.

Sekarang, rentang dinamis kami dibuat

Karena kami telah membuat tabel, dibutuhkan rentang seperti Tabel 2. Klik “OK”, dan di “PivotTable”, seret “Produk” di baris dan “Penjualan” di nilai.

Di lembar tempat kami memiliki tabel kami, kami harus memasukkan data lain pada tanggal 7.

Perbarui tabel pivot.

PivotTabl dinamis kami telah secara otomatis memperbarui data “Produk 6” di PivotTable.

#2 – Menggunakan Fungsi OFFSET untuk membuat Tabel Dinamis di Excel

Kita juga bisa menggunakan Fungsi OFFSETFungsi OFFSETFungsi OFFSET di excel mengembalikan nilai sel atau rentang (sel yang berdekatan) yang merupakan jumlah baris dan kolom tertentu dari titik referensi. baca selengkapnya untuk membuat tabel dinamis di Excel. Mari kita lihat salah satu contohnya.

Contoh

Kami memiliki daftar harga untuk produk yang kami gunakan untuk perhitungan kami.

Pertama, kita perlu memilih data dan memberinya nama.

Setiap kali kita merujuk ke daftar harga set data, itu akan membawa kita ke data dalam rentang B2:C7, yang memiliki daftar harga kita. Tetapi jika kami memperbarui baris lain ke data, itu masih akan membawa saya ke kisaran B2:C7 karena daftar kami statis.

Kami akan menggunakan fungsi OFFSET untuk membuat rentang data menjadi dinamis.

#1 – Sekarang, di bawah tab “Formulas” di “Defined Range,” kita harus mengklik “Define Name,” dan kotak dialog akan muncul.

#2 – Kita bisa mengetik nama apa saja di Kotak NamaKotak NamaDi Excel, kotak nama terletak di sisi kiri jendela dan digunakan untuk memberi nama pada tabel atau sel. Nama biasanya berupa karakter baris diikuti dengan nomor kolom, seperti sel A1.baca selengkapnya. Kami akan menggunakan “Produk”. Cakupannya adalah buku kerja saat ini, dan saat ini merujuk ke sel saat ini yang dipilih, yaitu B2.

Dalam “Mengacu” kita harus menulis rumus berikut:

=offset(Sheet2!$B$2,1,0,counta(Sheet2!$B:$B)-1,2)

=offset(

#3 – Sekarang, kita harus memilih sel awal, yaitu B2.

#4 – Sekarang, kita perlu mengetikkan 1,0 karena ini akan menghitung berapa banyak baris atau kolom yang tersisa.

#5 – Sekarang, kita membutuhkannya untuk menghitung data di kolom B dan menggunakannya sebagai jumlah baris sehingga kita dapat menggunakan fungsi COUNTA. sel (tidak kosong) dalam rentang sel atau referensi sel. Misalnya, sel A1 dan A3 berisi nilai, tetapi sel A2 kosong. Rumus ‘=COUNTA(A1,A2,A3)’ mengembalikan 2. baca lebih lanjut dan pilih kolom B.

#6 – Karena kita tidak ingin baris pertama, header produk, dihitung, jadi (-) 1 darinya.

#7 – Sekarang, jumlah kolom akan selalu dua, jadi kita harus mengetikkan ‘2’ dan klik “OK.”

#8 – Rentang data ini tidak akan terlihat secara default, jadi untuk melihatnya, kita perlu mengklik Manajer Nama Manajer Nama Manajer nama di Excel digunakan untuk membuat, mengedit, dan menghapus rentang bernama. Misalnya, terkadang kami menggunakan nama alih-alih memberikan referensi sel. Dengan menggunakan pengelola nama, kita dapat membuat referensi baru, mengeditnya, atau menghapusnya. Baca selengkapnya di bawah tab “Formula” dan pilih “Produk”.

#9 – Jika kita mengklik “Refers to,” ini menunjukkan jangkauan data,

#10 – Kami akan menambahkan produk lain -“Produk 6.”

#11 – Terakhir, klik “Product Table” di “Name Manager.” Ini juga mengacu pada data baru yang dimasukkan.

Seperti ini, kita bisa menggunakan fungsi OFFSET untuk membuat tabel dinamis.

Hal-hal untuk diingat

PivotTable berdasarkan rentang dinamis secara otomatis diperbarui saat disegarkan.
Penggunaan fungsi OFFSET pada “Defined Name” dapat dilihat dari “Name Manager” pada tab “Formula”.

Artikel yang Direkomendasikan

Artikel ini adalah panduan untuk Tabel Dinamis di Excel. Di sini, kita membahas pembuatan tabel dinamis di Excel menggunakan fungsi TABLE dan OFFSET serta contoh praktis, dan template yang dapat diunduh. Anda dapat mempelajari lebih lanjut tentang Excel dari artikel berikut: –