Cara Membuat Nama Range Dinamis pada Excel

Kita perlu membuat nama range dinamis (Dinamic Name Range) saat se&g bekerja dengan file excel yg datanya selalu bertambah, berkurang atau berubah-ubah setiap saat. Hal ini bertujuan agar kita tidak berulang ulang direpotkan untuk menyesuaikan rumus excel yg se&g kita terapkan pada file.

Membuat range dinamis ini sebenarnya tidaklah terlalu sulit. Namun sebelum itu setidaknya anda harus sdh menguasai cara membuat nama range pada excel dan cara mengedit atau mengubah nama range pada Ms. Excel yg sdh saya tuliskan pada tutorial sebelumnya.

Apabila data Anda sdh menggunakan format tabel excel, sebenarnya Anda sdh tidak membutuhkan lagi tutorial ini. Namun demikian tidak ada salahnya untuk dipelajari.

• Membuat Nama Range Dinamis
• Kolom Acuan Bertipe Angka
• Kolom Acuan Bertipe Teks
• Download File Contoh

Contoh file untuk kasus ini bisa anda download di bagian akhir artikel tutorial ini.

Cara Membuat Nama Range Dinamis

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

1. Menentukan kolom mana & tipe kolomnya apa. Kolom disini adalah kolom data yg akan kita jadikan acuan perubahan besar (Lebar/tinggi) data. Bisa juga berupa baris untuk tabel data yg 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 yg berbeda akan menentukan jenis fungsi excel apa yg 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 yg akan kita gunakan adalah fungsi OFFSET & Fungsi COUNT atau COUNTA.
2. Data bertipe teks (text): Untuk kolom acuan yg berupa text maka fungsi excel yg digunakan adalah fungsi OFFSET & Fungsi COUNTA.

Fungsi Excel utama yg kita pakai adalah fungsi OFFSET. Se&gkan 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.

Nama Range Dinamis Dengan Kolom Acuan Bertipe Angka (Numeric)

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

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

Keterangan:

• OFFSET, fungsi untuk menggeser/ merubah ukuran sebuah range data.
• SelDasar, sel awal yg 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 yg berisi angka (numeric) pada kolom acuan.
• JmlLebarKolom, menyesuaikan kebutuhan & bersifat opsional (tidak maslah jika dikosongkan.

Untuk lebih jelasnya perhatikan contoh brkut:



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 Excel pada 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 yg berisi angka (numeric) pada range A1:A100 pada Sheet2 & 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 sbgai brkut:

=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 sbgai teks. berbeda dengan fungsi count yg hanya menghitung sel yg beriisi angka.

Untuk kasus range data yg kolomnya lebih dari satu, perhatikan cara membuat nama range dinamisnya sprti pada contoh gambar brkut:



Untuk kasus diatas kita ingin tabel siswa tersebut dinamis dengan nomor absen sbgai 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 brkut:

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

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

Nama Range Dinamis Dengan Kolom Acuan Bertipe Teks

Jika kolom acuan perubahan data berjenis teks maka dlm 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 yg 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 yg diitung oleh fungsi COUNTA adalah sel yg ada isinya/ sel yg tidak kosong.

Penjelasan lebih detailnya sebenarnya sama saja dengan yg bertipe numeric pada bagian sebelumnya. maka slahkan langusng saja perhatikan contoh-contoh penerapan pembuatan range dinamis sbgaimana pada gambar-gambar brkut.

contoh-contoh range dinamis pada rumus-rumus excel dibawah ini menjadikan kolom A (Id Barang) sbgai 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)

Download File Contoh Nama Range Dinamis di Excel

File contoh untuk pembahasan kali ini tersedia pada link di bawah ini :

Dropbox
GDrive

*Jika link mati / tidak dapat diakses slahkan lapor via kontak yg tersedia

FacebookTwitterWhatsapp

LINK DOWNLOAD TERKUNCI.
Silahkan SHARE untuk membuka kunci!

Semoga setlah membaca tutorial tentang cara membuat name range dinamis ini, anda tidak lagi mendapatkan masalah ketika dihadapkan pada analisa range data yg dinamis.
Sumber https://www.kelasexcel.id

Bagikan Artikel ini

Berlangganan update artikel terbaru via email: