Selasa, 25 Oktober 2011

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:

7 komentar:

Anonim mengatakan...

Trik yang bagus, ditunggu trik lainnya.

Anonim mengatakan...

Klo mau bikin daftar perolehan medali, seperti SEA GAMES atow PON ! bagaimna ya ? tapi yang urutannya selalu berubah klo ada data baru yng masuk... soalnya aku dah bikin atpi tiap ada data masuk harus di sort ulang !

solusinya ditunggu ya... znl_mustopa@yahoo.co.id

excell mengatakan...

Untuk masalah Sorting, sepertinya Link berikut bisa membantu:
http://www.excelheru.blogspot.com/2012/03/cara-membuat-daftar-berdasarkan.html.

Kalau mau lebih rinci lagi, Ulasan Mr.Kid tentang berbagai metode Sorting bisa dipelajari:
http://excel-mr-kid.blogspot.com/2012/05/pemeringkatan-data-ranking.html

Anonim mengatakan...

bermanfaat sekali.. ini masalah yang saya cari tahu dari dulu terima kasih

Anonim mengatakan...

kalo membuat name range kalo databasenya di lain sheet bagaimana rumusnya, saya coba gagal trus saya juga lagi membuat applikasi raport kaya siboos karena saya tertarik, karena raport yang boos punya ada kekurang kalo saya edit pada kacau rumusnya

NB :karena saya blom perna menggunakan rumus offset paling sering menggunakan vlookup

sebenernya masi banyak yg saya pertanyakan tetapi yang ini aja dl mohon pencerahannya dari siboos

Anonim mengatakan...

sangat bermanfaat sekali tx..

ILham Akbar mengatakan...

Assalamu 'alaikum,, mau tanya Mas Heru, saya melihat2 file "Master Raport Sekolah" buatannya Mas Heru, dan saya mencoba menambah data siswa baru di baris sheet Siswa dan saya melihat kolomnya jga ikut bertambah (kalau tutorial ini tidak demikian seperti pada File Master Raport Sekolah),,

Pertanyaan saya, bagaimana cara membuatnya menjadi seperti itu (ketika menambahkan 1 data, tabelnya juga ikut bertambah) ? Mohon bimbingannya Mas Heru,, Terimakasih sebelumnya,,