Cara menciptakan Nama Range Dinamis pada Excel

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

Membuat range dinamis ini pundak-membahu tidaklah terlalu sulit. Namun sebelum itu setidaknya anda harus sudah menguasai cara membuat nama range pada excel serta cara mengedit atau mengubah nama range pada Ms. Excel yang sudah admin tuliskan pada tutorial sebelumnya.

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

• Membuat Nama Range Dinamis
• Kolom tumpuan Bertipe Angka
• Kolom tumpuan Bertipe Teks
• Download File pola

Contoh file untuk masalah ini mampu anda download di kepingan akhir artikel tutorial ini.

Cara menciptakan Nama Range Dinamis

Langkah-langkah yang dibutuhkan untuk menciptakan nama range dinamis ialah:

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

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

1. Data bertipe numeric (angka): kalau kolom rujukan besar data berupa numeric (Format angka, tanggal, waktu) , fungsi excel yang akan kita gunakan ialah fungsi OFFSET dan Fungsi COUNT atau COUNTA.
2. Data bertipe teks (text): Untuk kolom rujukan yang berupa text maka fungsi excel yang dipakai ialah fungsi OFFSET dan Fungsi COUNTA.

Fungsi Excel utama yang kita pakai ialah fungsi OFFSET. Sedangkan untuk menentukan lebar atau tinggi range datanya sanggup menggunakan fungsi COUNT atau Fungsi COUNTA pada microsoft Excel. Untuk lebih detailnya akan aku teladankan pada cuilan selanjutnya.

Nama Range Dinamis Dengan Kolom rujukan 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 rujukan awal pergeseran range data. Sebaiknya sel dasar ini tidak menjadi penggalan dari hasil akhir dari range data dinamis. Biasanya posisinya pada header sebuah tabel data.
• JmlBarisGeser, jikalau sel tumpuan pada header maka range akibat 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 rujukan.
• JmlLebarKolom, menyesuaikan kebutuhan dan bersifat opsional (tidak maslah jikalaulau dikosongkan.

Untuk lebih jelasnya perhatikan acuan berikut:



Pada teladan 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 ialah 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. supaya lebih dinamis anda sanggup juga menulis dengan Tutorial: COUNT(Sheet2!$A:$A), tanpa menentukan row/baris. Namun hal ini sebaiknya dihindari sebab ialah mampu memeperberat kinerja komputer anda.

Selain menggunakan fungsi COUNT, range dinamis pada teladan diatas juga mampu menggunakan fungsi COUNTA. Fungsi COUNTA dipakai untuk menghitung sel tidak kosong pada range data. Rumus range dinamisnya ialah 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 lantaran 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 Tutorial menciptakan nama range dinamisnya seperti pada rujukan gambar berikut:



Untuk masalah diatas kita ingin tabel siswa tersebut dinamis dengan nomor bolos sebagai rujukan. maka rumus range dinamisnya ialah:

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

atau sanggup juga memakai rumus excel berikut:

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

Yang membedakan kedua rumus diatas yaitu sel/range rujukan dasar dan penulisan argument lebar tabel. Rumus diatas tentunya sanggup juga nada sesuaikan dengan fungsi COUNTA seperti potongan sebelumnya.

Nama Range Dinamis Dengan Kolom rujukan Bertipe Teks

Jika kolom rujukan perubahan data berjenis teks maka dalam argument fungsi OFFSET tidak mampu menggunakan fungsi COUNT, alasannya adalah ialah fungsi COUNT hanya menghitung sel berisi angka/numeric saja.Dalam masalah 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. karena yaitu fungsi ini lebih fleksibel mampu dipakai untuk jenis data apapun, karena yang diitung oleh fungsi COUNTA ialah sel yang ada isinya/ sel yang tidak kosong.

Penjelasan lebih detailnya bekerjsama sama saja dengan yang bertipe numeric pada belahan 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 menyebabkan 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)

Download File teladan Nama Range Dinamis di Excel

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

Dropbox
GDrive

*Jika link mati / tidak dapat diakses silahkan lapor via kontak yang tersedia

FacebookTwitterWhatsapp

LINK DOWNLOAD TERKUNCI.
Silahkan SHARE untuk membuka kunci!

Semoga setelah membaca tutorial ihwal cara menciptakan name range dinamis ini, anda tidak lagi mendapatkan kasus ketika dihadapkan pada analisa range data yang dinamis.