Cara Menghasilkan Nama Range Dinamis Pada Excel | Kelas Excel

Menjelaskan tindakan atau cara menghasilkan nama range dinamis (dinamic name range) pada microsoft excel.

Kita perlu menghasilkan nama range dinamis (Dinamic Name Range) di saat sedang melakukan pekerjaan dengan file excel yang datanya senantiasa bertambah , menyusut atau berubah-ubah setiap saat. Hal ini berencana mudah-mudahan kita tidak berulang ulang disibukkan untuk menyesuaikan rumus excel yang sedang kita terapkan pada file.

Membuat range dinamis ini gotong royong tidaklah terlalu sulit. Namun sebelum itu setidaknya anda mesti sudah menguasai cara menghasilkan nama range pada excel serta cara mengedit atau merubah nama range pada Ms. Excel yang sudah aku tuliskan pada panduan sebelumnya.

Apabila data Anda sudah menggunakan format tabel excel , gotong royong Anda sudah tidak memerlukan lagi panduan 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 kendala ini sanggup anda download di bab simpulan postingan panduan ini.

Cara Membuat Nama Range Dinamis

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

1. Menentukan kolom mana dan tipe kolomnya apa. Kolom disini merupakan kolom data yang hendak kita jadikan contoh perubahan besar (Lebar/tinggi) data. Bisa juga berupa baris untuk tabel data yang horizontal.
2. Buat suatu nama range baru.
3. Pada bab refers to: tuliskan rumus excel untuk menghasilkan range dinamis menggunakan fungsi OFFSET.

Tipe kolom (column) data ini perlu kita pahami lantaran jenis/tipe yang berlawanan akan menyeleksi jenis fungsi excel apa yang hendak kita gunakan untk menghasilkan range dinamis. Lazimnya ada dua tipe:

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

Fungsi Excel utama yang kita pakai merupakan fungsi OFFSET. Sedangkan untuk menyeleksi lebar atau tinggi range datanya sanggup menggunakan fungsi COUNT atau Fungsi COUNTA pada microsoft Excel. Untuk lebih detailnya akan aku contohkan pada bab selanjutnya.

Nama Range Dinamis Dengan 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 suatu range data.
• SelDasar , sel permulaan yang dijadikan contoh permulaan pergantian range data. Sebaiknya sel dasar ini tidak menjadi bab dari hasil simpulan dari range data dinamis. Biasanya posisinya pada header suatu tabel data.
• JmlBarisGeser , Jika sel contoh pada header maka range simpulan umunya bergesser 1 baris ke bawah dari SelDasar
• JmlKolomGeser , Untuk data berupa tabel vertikal lazimnya diisi dengan nol (0). Kolom tidak bergeser.
• COUNT(KolomData) , menjumlah jumlah sel yang berisi angka (numeric) pada kolom acuan.
• JmlLebarKolom , menyesuaikan keperluan dan bersifat opsional (tidak maslah apabila dikosongkan.

Untuk lebih jelasnya amati 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 menyertakan 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 merupakan jumlah sel yang berisi angka (numeric) pada range A1:A100 pada Sheet2 dan lebar kolom merupakan satu (1).

Angka 100 pada rumus COUNT(Sheet2!$A$1:$A$100)bisa anda sesuaikan sebesar kemungkinan tinggi optimal data anda. Agar lebih dinamis anda sanggup juga menulis dengan cara: COUNT(Sheet2!$A:$A) , tanpa menyeleksi row/baris. Namun hal ini semestinya disingkirkan lantaran sanggup memeperberat kinerja komputer anda.

Selain menggunakan fungsi COUNT , range dinamis pada contoh diatas juga sanggup menggunakan fungsi COUNTA. Fungsi COUNTA dipakai untuk menjumlah sel tidak kosong pada range data. Rumus range dinamisnya merupakan selaku 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 selaku teks. berlawanan dengan fungsi count yang cuma menjumlah sel yang beriisi angka.

Untuk kendala range data yang kolomnya lebih dari satu , amati cara menghasilkan nama range dinamisnya seumpama pada contoh gambar berikut:



Untuk kendala diatas kita ingin tabel siswa tersebut dinamis dengan nomor bolos selaku acuan. maka rumus range dinamisnya adalah:

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

atau sanggup juga menggunakan rumus excel berikut:

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

Yang membedakan kedua rumus diatas merupakan sel/range contoh dasar dan penulisan argument lebar tabel. Rumus diatas pastinya sanggup juga nada sesuaikan dengan fungsi COUNTA seumpama bab sebelumnya.

Nama Range Dinamis Dengan Kolom Acuan Bertipe Teks

Jika kolom contoh perubahan data berjenis teks maka dalam argument fungsi OFFSET tidak sanggup menggunakan fungsi COUNT , lantaran fungsi COUNT cuma menjumlah sel berisi angka/numeric saja.Dalam kendala ini tinggi data diputuskan dengan fungsi COUNTA.

Rumus dasar yang dipakai 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 menyeleksi tinggi/lebar data untuk range dinamis memang menggunakan fungsi COUNTA ini. lantaran fungsi ini lebih fleksibel sanggup dipakai untuk jenis data apapun , lantaran yang diitung oleh fungsi COUNTA merupakan sel yang ada isinya/ sel yang tidak kosong.

Penjelasan lebih detailnya gotong royong sama saja dengan yang bertipe numeric pada bab sebelumnya. maka silahkan langusng saja amati contoh-contoh penerapan pengerjaan range dinamis sebagaimana pada gambar-gambar berikut.

contoh-contoh range dinamis pada rumus-rumus excel dibawah ini menyebabkan kolom A (Id Barang) selaku contoh pengerjaan 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 sanggup diakses silahkan lapor via kontak yang tersedia

FacebookTwitterWhatsapp

LINK DOWNLOAD TERKUNCI.
Silahkan SHARE untuk membuka kunci!

Semoga setelah membaca panduan tentang cara menghasilkan name range dinamis ini , anda tidak lagi mendapat duduk kendala di saat dihadapkan pada evaluasi range data yang dinamis.

>> Gabung Telegram Channel <<