Cara Membuat Nama Range Dinamis pada Excel

Kita perlu membuat nama range dinamis (Dinamic Name Range) saat sedang bekerja dengan file excel yang datanya selalu bertambah, berkurang atau berubah-ubah setiap saat. Hal ini bertujuan agar kita tidak berulang ulang direpotkan untuk menyesuaikan rumus excel yang sedang kita terapkan pada file.
Contoh file untuk kasus ini bisa anda download di bagian akhir artikel tutorial ini.

LANGKAH-LANGKAH MEMBUAT NAMA RANGE DINAMIS

Langkah-langkah yang diperlukan untuk membuat nama range dinamis adalah:

1. Menentukan kolom mana dan tipe kolomnya apa. Kolom disini adalah kolom data yang akan kita jadikan acuan perubahan besar (Lebar/tinggi) data. Bisa juga berupa baris untuk tabel data yang horizontal.
2. Buat sebuah nama range baru.
3. Pada bagian refers to: tuliskan rumus excel untuk membuat range dinamis menggunakan fungsi OFFSET.

Tipe kolom (column) data ini perlu kita ketahui karena jenis/tipe yang berbeda akan menentukan jenis fungsi excel apa yang akan kita gunakan untk membuat range dinamis. Lazimnya ada dua tipe:

1. Data bertipe numeric (angka): Jika kolom acuan besar data berupa numeric (Format angka, tanggal, waktu) , fungsi excel yang akan kita gunakan adalahfungsi OFFSET dan Fungsi COUNT atau COUNTA.
2. Data bertipe teks (text): Untuk kolom acuan yang berupa text maka fungsi excel yang digunakan adalah fungsi OFFSET dan Fungsi COUNTA.

Fungsi Excel utama yang kita pakai adalah fungsi OFFSET. Sedangkan untuk menentukan lebar atau tinggi range datanya bisa menggunakan fungsi COUNT atau Fungsi COUNTA pada microsoft Excel. Untuk lebih detailnya akan saya contohkan pada bagian selanjutnya.

KOLOM ACUAN BERTIPE ANGKA (NUMERIC)

Untuk data yang menggunakan kolom berjenis data numeric rumus excel yang digunakan:

=OFFSET(SelDasar;JmlBarisGeser;JmlKolomGeser;COUNT(KolomData);JmlLebarKolom)

Keterangan:

• OFFSET, fungsi untuk menggeser/ merubah ukuran sebuah range data.
• SelDasar, sel awal yang dijadikan acuan awal pergeseran range data. Sebaiknya sel dasar ini tidak menjadi bagian dari hasil akhir dari range data dinamis. Biasanya posisinya pada header sebuah tabel data.
• JmlBarisGeser, Jika sel acuan pada header maka range akhir umunya bergesser 1 baris ke bawah dari SelDasar
• JmlKolomGeser, Untuk data berbentuk tabel vertikal biasanya diisi dengan nol (0). Kolom tidak bergeser.
• COUNT(KolomData), menghitung jumlah sel yang berisi angka (numeric) pada kolom acuan.
• JmlLebarKolom, menyesuaikan kebutuhan dan bersifat opsional (tidak maslah jika dikosongkan.

Untuk lebih jelasnya perhatikan contoh berikut:



Pada contoh diatas refers to pada defined range "Data1" berisi rumus:

=OFFSET(Sheet2!$A$1;1;0;COUNT(Sheet2!$A$1:$A$100);1)

Dengan menggunakan named range diatas setiap anda menambahkan angka pada kolom A maka akan otomatis ikut terhitung oleh fungsi SUM Excelpada sel D2.
Rumus excel diatas artinya bahwa nama range "Data1" bergeser 1 baris ke bawah dari sel A1 pada Sheet2, kolom tidak bergeser (0), dengan tinggi data adalah jumlah sel yang berisi angka (numeric) pada range A1:A100 pada Sheet2 dan lebar kolom adalah satu (1).

Angka 100 pada rumus COUNT(Sheet2!$A$1:$A$100)bisa anda sesuaikan sebesar kemungkinan tinggi maksimal data anda. Agar lebih dinamis anda bisa juga menulis dengan cara: COUNT(Sheet2!$A:$A), tanpa menentukan row/baris. Namun hal ini sebaiknya dihindari sebab bisa memeperberat kinerja komputer anda.

Selain menggunakan fungsi COUNT, range dinamis pada contoh diatas juga bisa menggunakan fungsi COUNTA. Fungsi COUNTA digunakan untuk menghitung sel tidak kosong pada range data. Rumus range dinamisnya adalah sebagai berikut:

=OFFSET(Sheet2!$A$1;1;0;COUNTA(Sheet2!$A$1:$A$100)-1;1)

Kenapa pada rumus tersebut menggunakan minus satu (-1), hal ini karena kolom header juga ikut terhitung sebagai teks. berbeda dengan fungsi count yang hanya menghitung sel yang beriisi angka.

Untuk kasus range data yang kolomnya lebih dari satu, perhatikan cara membuat nama range dinamisnya seperti pada contoh gambar berikut:



Untuk kasus diatas kita ingin tabel siswa tersebut dinamis dengan nomor absen sebagai acuan. maka rumus range dinamisnya adalah:

=OFFSET(Sheet3!$A$1;1;0;COUNT(Sheet3!$A$1:$A$100);4)

atau bisa juga menggunakan rumus excel berikut:

=OFFSET(Sheet3!$A$1:$D$1;1;0;COUNT(Sheet3!$A$1:$A$100))

Yang membedakan kedua rumus diatas adalah sel/range acuan dasar dan penulisan argument lebar tabel. Rumus diatas tentunya bisa juga nada sesuaikan dengan fungsi COUNTA seperti bagian sebelumnya.

KOLOM ACUAN BERTIPE TEKS

Jika kolom acuan perubahan data berjenis teks maka dalam argument fungsi OFFSET tidak bisa menggunakan fungsi COUNT, sebab fungsi COUNT hanya menghitung sel berisi angka/numeric saja.Dalam kasus ini tinggi data ditentukan dengan fungsi COUNTA.

Rumus dasar yang digunakan untuk mengisi refers to: pada nama range adalah:

=OFFSET(SelDasar;JmlBarisGeser;JmlKolomGeser;COUNTA(KolomData)-1;JmlLebarKolom)

Jika argument "JmlLebarKolom" dikosongkan maka lebar kolom akan mengikuti "Sel Dasar".

Lazimnya untuk menentukan tinggi/lebar data untuk range dinamis memang menggunakan fungsi COUNTA ini. sebab fungsi ini lebih fleksibel bisa digunakan untuk jenis data apapun, sebab yang diitung oleh fungsi COUNTA adalah sel yang ada isinya/ sel yang tidak kosong.

Penjelasan lebih detailnya sebenarnya sama saja dengan yang bertipe numeric pada bagian sebelumnya. maka silahkan langusng saja perhatikan contoh-contoh penerapan pembuatan range dinamis sebagaimana pada gambar-gambar berikut.

contoh-contoh range dinamis pada rumus-rumus excel dibawah ini menjadikan kolom A (Id Barang) sebagai acuan pembuatan range dinamis.

Defined name "Harga_Barang" menggunakan rumus:

=OFFSET(Sheet1!$D$1;1;0;COUNTA(Sheet1!$A1:$A100)-1;1)

Defined name "Tbl_Barang1" menggunakan rumus excel:

=OFFSET(Sheet1!$A$1;1;0;COUNTA(Sheet1!$A:$A)-1;4)

Defined name "Tbl_Barang2" menggunakan rumus excel:

=OFFSET(Sheet1!$A$1:$D$1;1;0;COUNTA(Sheet1!$A:$A)-1)

Semoga bermanfaat

Sumber : kelasexcel.web.id