Cara Menciptakan Nama Range Dinamis Pada Excel

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

Membuat range dinamis ini sebetulnya tidaklah terlalu sulit. Namun sebelum itu setidaknya anda harus sudah menguasai cara menciptakan nama range pada excel serta cara mengedit atau mengubah nama range pada Ms. Excel yang sudah aku tuliskan pada tutorial sebelumnya.

Apabila data Anda sudah memakai format tabel excel, sebetulnya Anda sudah 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 masalah ini sanggup anda download di bab tamat artikel tutorial ini.

Cara Membuat Nama Range Dinamis

Langkah-langkah yang diharapkan untuk menciptakan nama range dinamis adalah:

1. Menentukan kolom mana dan tipe kolomnya apa. Kolom disini ialah kolom data yang akan kita jadikan pola perubahan besar (Lebar/tinggi) data. Bisa juga berupa baris untuk tabel data yang horizontal.
2. Buat sebuah nama range baru.
3. Pada bab refers to: tuliskan rumus excel untuk menciptakan range dinamis memakai 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 menciptakan range dinamis. Lazimnya ada dua tipe:

1. Data bertipe numeric (angka): Jika kolom pola 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 pola 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 memilih lebar atau tinggi range datanya sanggup memakai 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 memakai 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 pola awal pergeseran range data. Sebaiknya sel dasar ini tidak menjadi bab dari hasil tamat dari range data dinamis. Biasanya posisinya pada header sebuah tabel data.
• JmlBarisGeser, Jika sel pola pada header maka range tamat 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 bila 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 memakai 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 ialah 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 sanggup juga menulis dengan cara: COUNT(Sheet2!$A:$A), tanpa memilih row/baris. Namun hal ini sebaiknya dihindari lantaran sanggup memeperberat kinerja komputer anda.

Selain memakai fungsi COUNT, range dinamis pada contoh diatas juga sanggup memakai 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 memakai 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 masalah range data yang kolomnya lebih dari satu, perhatikan cara menciptakan nama range dinamisnya ibarat pada contoh gambar berikut:



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

=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 ialah sel/range pola dasar dan penulisan argument lebar tabel. Rumus diatas tentunya sanggup juga nada sesuaikan dengan fungsi COUNTA ibarat bab sebelumnya.

Nama Range Dinamis Dengan Kolom Acuan Bertipe Teks

Jika kolom pola perubahan data berjenis teks maka dalam argument fungsi OFFSET tidak sanggup memakai fungsi COUNT, lantaran fungsi COUNT hanya menghitung sel berisi angka/numeric saja.Dalam masalah ini tinggi data ditentukan 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 memilih tinggi/lebar data untuk range dinamis memang memakai fungsi COUNTA ini. lantaran fungsi ini lebih fleksibel sanggup dipakai untuk jenis data apapun, lantaran yang diitung oleh fungsi COUNTA ialah sel yang ada isinya/ sel yang tidak kosong.

Penjelasan lebih detailnya sebetulnya sama saja dengan yang bertipe numeric pada bab 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 mengakibatkan kolom A (Id Barang) sebagai pola pembuatan range dinamis.

Defined name "Harga_Barang" memakai rumus:

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

Defined name "Tbl_Barang1" memakai rumus excel:

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

Defined name "Tbl_Barang2" memakai 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 sesudah membaca tutorial wacana cara menciptakan name range dinamis ini, anda tidak lagi mendapat dilema ketika dihadapkan pada analisa range data yang dinamis.

Location:

Share :