Membuat Name Range Dinamis

Apalah arti sebuah nama?

Di Excel, nama sangat berarti. Mari kita lihat penampakan berikut.

Misal kita ingin mengisi nilai Fiqih dari Nama Awaluddin pada sel L7, maka rumus yang biasa kita tulis adalah : =VLOOKUP(L$4,B5:H9,3)

Kalau sebelumnya kita buat nama range untuk B5:H9 dengan nama “DataNilai”, maka rumusnya menjadi : =VLOOKUP(L$4,DataNilai,3). Rumus seperti ini tentunya lebih “manusiawi”, artinya lebih bisa dipahami oleh kita yang membaca rumus tersebut.

Lalu, bagaimana cara membuat nama range?

Misalnya dalam tabel di atas, kita blok B5:H9, Lalu kita tuliskan nama yang diinginkan di Name Box. Name Box adalah sebuah kotak di sebelah kiri Formula Bar, di atas judul Kolom.  Lalu tekan Enter.

Catatan: Penulisan nama range tidak boleh ada spasi, tidak boleh dimulai dengan angka, serta tidak boleh mirip dengan alamat sel. Nama ‘DAF1’ misalnya tidak diijinkan.

Cara kedua untuk membuat nama range adalah:

1. Blok range yang akan diberi nama
2. Pilih Menu FORMULA. Pada bagian Defined Names Group, pilih Define Name. Lalu tuliskan nama yang kita inginkan di bagian Name.
3. OK

Untuk mengedit alamat sel dari nama range, Kita pilih menu FORMULA, Name Manager. Pilih nama yang akan diedit, pilih Edit.

Membuat Nama Range Dinamis.

Nah, kalau daftar siswa pada kasus seperti tabel di atas bertambah, dari yang awalnya hanya 6 lalu bertambah atau berkurang, maka kita kan harus mengedit nama range yang sudah kita buat. Repot khan?

Biar tidak repot, kita perlu membuat Nama Range yang bersifat dinamis, yang kalau datanya bertambah atau berkurang, alamat sel dari nama yang kita buat juga ikut menyesuaikan. Emang bisa? BISA….!

Yuk kita ikuti step by step.

1. Baca Basmalah, kalau perlu tawassul juga. Hehehehe
2. Pada menu FORMULAS, pilih Define Name. Muncullah kotak dialog.
3. Tuliskan nama dari Range yang akan kita buat
4. Inti dari langkah kita adalah pada bagian Refers To. Pada bagian ini kita tuliskan rumus berikut:

=OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$C$5:$C$100),7)
5. OK

Penjelasan Rumus:

Fungsi OFFSET adalah mengatur pergeseran alamat sel. Syntaxnya adalah OFFSET(reference, rows, cols, [height], [width])

Pada rumus di atas, Sheet!B5 sebagai patokan awal (reference).

rows-nya kita isi 0. Artinya barisnya tidak bergeser, tetap di baris 5

cols-nya kita isi 0 juga, artinya kolomnya tidak bergeser, tetap di kolom B

height / tinggi tabel / banyaknya baris. Kita isi dengan COUNTA(Sheet1!$C$5:$C$100). Artinya tergantung jumlah siswa pada kolom Nama Siswa.

Width / lebar kolom, karena yang kita inginkan adalah dari kolom B sampai kolom H, maka kita isi 7.

Untuk menguji apakah pembuatan nama Range kita berhasil, cobalah tambahkan beberapa nama siswa dan juga nilainya. Kemudian kita pilih menu FOMULAS, Name Manager. Pilih nama yang sudah kita buat, lalu pilih Edit. Klik rumus yang kita buat. Maka tabel di sheet akan dibatasi oleh garis putus-putus, seperti gambar.

Demikian, semoga bermanfaat..!

Artikel Terkait: