Pada artikel sebelumnya, kita sudah pernah membahas cara memberi nama range (named range) pada excel. Fasilitas Named range ini sangat membantu sekali dalam pembuatan rumus-rumus excel dimana terdapat range data, sehingga akan lebih mudah ketika data-data range mengalami perubahan baik bertambah atau berkurang, kita cukup edit range data saja tanpa perlu mengubah rumus-rumus satu-persatu.
Pada kesempatan ini admin akan membahas cara membuat named range dinamis, dengan menggunakan named range dinamis ini data-data range akan lebih fleksibel untuk menyesuaikan range jika ada perubahan, tanpa perlu lagi repot-repot harus mengubah name range karena data range secara otomatis menyesuaikan.
Named Range Dinamis
Untuk lebih jelasnya, simak contoh perbedaannya berikut ini
Pada contoh tabel diatas merupakan data nilai dengan vlookup, untuk menampilkan data nilai masing-masing siswa dengan patokan nama, nilai akan muncul otomatis.
- Penulisan tidak menggunakan named range
yaitu sebagai berikut
=VLOOKUP($G$3;$A$4:$D$9;2)
=VLOOKUP($G$3;$A$4:$D$9;3)
=VLOOKUP($G$3;$A$4:$D$9;4)
Rumusnya akan terlihat cukup ruwet dan ketika range berubah (data siswa bertambah atau berkurang) kita harus mengubah rumus range satu persatu, repot sekali bukan.
- Menggunakan named range
Cara membuat named renge
Select Rangenya A4:D9 kemudian>> Klik menu Formula >> pada group Difined Names >> Klik Difined Name
Named Range “Nilai”
Beri nama “Nilai”
Refers to : akan muncul =Sheet1!$A$4:$D$9, kemudian >> klik OK.
Kemudian pada rumusnya, Range diganti/ubah menjadi Nilai
=VLOOKUP($G$3;Nilai;2)
=VLOOKUP($G$3;Nilai;2)
=VLOOKUP($G$3;Nilai;2)
Kemudahan menggunakan name range yaitu, ketika data range berubah (ditambah atau berkurang) kita tidak perlu repot harus ubah rumus, cukup edit saja range pada nemed range caranya :
Klik Name Manager >> Klik Edit sesuaikan refers to : rangenya
- Named Range Dinamis
Named Range Dinamis sangat fleksibel dan canggih kita tidak perlu edit lagi named range dan rumus karena secara otomatis akan menyesuaikan data range ketika terjadi perubahan.
caranya yaitu kita gunaan kode refers to pada named range dibawah ini
=OFFSET(Sheet1!$A$4;0;0;COUNTA(Sheet1!$A:$A);4)
Ket :
OFFSET rumus excel yang digunakan untuk mengatur pergeseran cell
dengna sintak penulisan
OFFSET(reference, rows, cols, [height], [width])
- Sheet1!$A$4 (Reference) : kita sesuaikan nama sheet dan range sebagai patokan awal yaitu cell A4
- 0 (rows) : Artinya barisnya referensi awal yaitu tetap di baris 4 tidak bergeser
- 0 (cols) : Kolom referensi awal juga tetap tidak berubah/bergeser yaitu dikolom A
- COUNTA(Sheet1!$A:$A) (height) : tinggi merupakan bayaknya data pada baris range
- 4 (width) : sesuaikan berapa jumlah kolom referensinya yaitu ada 4 kolom.
Untuk hasil yang sudah jadi silahkan download disini silahkan sobat uji coba tambahkan datanya supaya lebih memahaminya.
Terimakasih semoga bermanfaaat.
SIMPLE AND AMAZING
rumus excel ini mantap untuk data2 yg berubah2.
Sangat Membantu pekerjaan…. terima kasih pak 😀
mas, ini maksudnya apa yah Sheet1!$A:$A
Maksudnya sheet 1 kolom A:A