Tabel Dinamis di Excel

Tabel dinamis di excel adalah tabel di mana ketika nilai baru dimasukkan ke dalamnya, tabel menyesuaikan ukurannya dengan sendirinya, untuk membuat tabel dinamis di excel kita memiliki dua metode berbeda yaitu sekali yaitu membuat tabel data dari bagian tabel sementara yang lain adalah dengan menggunakan fungsi offset, dalam tabel dinamis laporan dan tabel pivot juga berubah saat data di tabel dinamis berubah.

Tabel Dinamis di Excel

Dinamis itu sendiri berarti sistem prosesor yang ditandai untuk perubahan konstan atau perubahan aktivitas. Demikian pula, di Excel ketika kita membuat daftar atau data di buku kerja dan membuat laporan darinya, tetapi jika kita menambahkan data atau menghapus satu atau memindahkan atau mengubah data maka seluruh laporan bisa menjadi tidak akurat. Excel memiliki solusi untuk itu sebagai Tabel Dinamis.

Sekarang muncul pertanyaan mengapa kita membutuhkan Dynamic Range atau Dynamic Tables. Jawabannya adalah karena setiap kali daftar atau rentang data diperbarui atau diubah, tidak ada kepastian bahwa laporan akan diubah sesuai perubahan data.

Pada dasarnya, ada dua keunggulan utama Tabel Dinamis:

  1. Rentang dinamis secara otomatis akan meluas atau menyusut sesuai perubahan data.
  2. Tabel pivot berdasarkan tabel dinamis di excel dapat diperbarui secara otomatis saat pivot di-refresh.

Bagaimana Cara Membuat Tabel Dinamis di Excel?

Ada dua cara dasar untuk menggunakan tabel dinamis di excel - 1) Menggunakan TABLES dan 2) Menggunakan Fungsi OFFSET.

Anda dapat mendownload Template Excel Tabel Dinamis ini di sini - Template Excel Tabel Dinamis

# 1 - Menggunakan Tabel untuk membuat Tabel Dinamis di Excel

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 pivot dengan range data normal ini dari A1: E6 maka jika kita memasukkan data pada baris 7 maka tidak akan terlihat pada tabel pivot.

Jadi pertama-tama kita akan membuat rentang dinamis.

# 1 - Pilih datanya yaitu A1: E6.

# 2 -  Di sisipan, klik Tab pada Tabel di bawah bagian tabel.

# 3 -  Kotak dialog muncul.

Karena data kita memiliki header, jadi ingatlah untuk mencentang kotak "Tabel Saya memiliki Header" dan klik ok.

# 4 -  Jangkauan Dinamis kami dibuat.

# 5 -  Pilih data dan di Tab Sisipkan di bawah bagian tabel excel, klik tabel pivot.

# 6 -  Karena kita telah membuat tabel, dibutuhkan kisaran seperti Tabel 2. Klik OK dan di tabel pivot, Tarik Produk dalam Baris dan Penjualan dalam Nilai.

# 7 -  Sekarang di Sheet di mana kita memiliki tabel kita masukkan Data Lain di 7

Di tabel pivot, muat ulang tabel pivot.

Tabel Pivot dinamis kami secara otomatis memperbarui data Produk 6 di tabel pivot.

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

Kami juga dapat menggunakan Fungsi OFFSET untuk membuat tabel dinamis di excel. Mari kita lihat salah satu contohnya.

Contoh

Saya memiliki daftar harga untuk produk saya yang saya gunakan untuk perhitungan saya,

Pilih data dan beri nama

Sekarang, setiap kali saya merujuk ke daftar harga kumpulan data, saya akan dibawa ke data dalam kisaran B2: C7 yang memiliki daftar harga saya. Tetapi jika saya 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 - Di bawah Tab Rumus di Rentang yang Ditentukan, klik Nama yang Ditentukan dan kotak dialog akan muncul.

# 2 - Di kotak Nama ketik nama apa pun, saya akan menggunakan PriceA. Ruang lingkup adalah buku kerja saat ini dan saat ini mengacu pada sel saat ini yang dipilih yaitu B2.

Dalam Merujuk untuk menulis rumus berikut,

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

= offset (

# 3 - Sekarang pilih sel awal yaitu B2,

# 4 - Sekarang kita perlu mengetik 1,0 karena itu akan menghitung berapa banyak baris atau kolom yang harus dituju

# 5 - Sekarang kita membutuhkannya untuk menghitung data apa pun yang ada di kolom B dan menggunakannya sebagai jumlah baris, jadi gunakan fungsi COUNTA dan pilih kolom B.

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

# 7 - Sekarang jumlah kolom akan selalu dua jadi ketik 2 dan klik OK.

# 8 - Rentang data ini tidak akan terlihat secara default jadi untuk melihat ini kita perlu mengklik Manajer Nama di bawah Tab Formula dan pilih Produk,

# 9 - Jika kita mengklik mengacu pada itu menunjukkan kisaran data,

# 10 - Sekarang tambahkan produk lain ke dalam tabel Produk 6.

# 11 - Sekarang klik pada Tabel Produk di Manajer nama itu juga mengacu pada data baru yang dimasukkan,

Ini adalah bagaimana kita dapat menggunakan fungsi Offset untuk membuat Tabel dinamis.

Hal-hal untuk diingat

  1. Tabel Pivot berdasarkan rentang Dinamis otomatis diperbarui saat disegarkan.
  2. Menggunakan fungsi offset dalam Nama yang ditentukan dapat dilihat dari Name Manager di Tab rumus.