Biasanya, Bernama Ranges sangat berguna bagi pengguna Excel, Anda dapat menentukan serangkaian nilai dalam kolom, memberi nama kolom itu, dan kemudian Anda dapat merujuk ke rentang tersebut berdasarkan namanya dan bukan referensi selnya. Namun, sebagian besar waktu, Anda perlu menambahkan data baru untuk memperluas nilai data rentang yang Anda rujuk di masa depan. Dalam kasus ini, Anda harus kembali ke Rumus > Manajer nama dan mendefinisikan ulang rentang untuk memasukkan nilai baru. Untuk menghindarinya, Anda dapat membuat rentang dinamik yang berarti Anda tidak perlu menyesuaikan referensi sel setiap saat ketika Anda menambahkan baris atau kolom baru ke dalam daftar.
Dynamic Named Range Di Excel Dengan Membuat Tabel
Menakjubkan! Menggunakan Tab Di Excel Seperti Firefox, Chrome, Internet Jelajahi 10!
Jika Anda menggunakan Excel 2007 atau versi yang lebih baru, cara termudah untuk membuat dynamic named range adalah dengan membuat tabel Excel yang bernama.
Katakanlah, Anda memiliki berbagai data berikut yang perlu menjadi dynamic named range.
1. Pertama, saya akan menentukan nama range untuk range ini. Pilih rentang A1: A6 dan masukkan namanya Tanggal ke dalam nama Box, Lalu tekanMemasukkan kunci. Untuk menentukan nama untuk rentang B1: B6 sebagai Saleprice dengan cara yang sama. Pada saat bersamaan, saya membuat formula = jumlah (Saleprice) di sel kosong, lihat tangkapan layar:
2. Pilih rentang dan klik Menyisipkan > tabel, lihat tangkapan layar:
3. di Buat tabel kotak prompt, periksa Meja saya memiliki header (jika rentang tidak memiliki header, hapus centang), klik OK tombol, dan rentang data telah dikonversi ke tabel. Lihat tangkapan layar:
 |  |  |
4. Dan ketika Anda memasukkan nilai baru setelah data, kisaran dinamik akan otomatis disesuaikan dan formula yang dibuat juga akan berubah juga. Lihat screenshot berikut:
 |  |  |
Catatan:
1. Data pemasukan baru Anda harus bersebelahan dengan data di atas, artinya tidak ada baris kosong atau kolom antara data baru dan data yang ada.
2. Dalam tabel, Anda dapat memasukkan data antara nilai yang ada.
Dynamic Named Range Di Excel Dengan Function
Di Excel 2003 atau versi sebelumnya, metode pertama tidak akan tersedia, jadi inilah cara lain untuk Anda. Pengikut OFFSET () fungsi bisa melakukan ini untuk Anda, tapi agak merepotkan. Misalkan saya memiliki sejumlah data yang berisi berbagai nama yang telah saya definisikan, misalnya,A1: A6 nama jangkauannya adalah Tanggal, dan B1: B6 nama jangkauan Harga penjualan, pada saat bersamaan, saya membuat formula untuk Harga penjualan. Lihat tangkapan layar
Anda dapat mengubah nama rentang ke nama rentang dinamis dengan langkah-langkah berikut:
1. Pergi ke klik Rumus > Manajer nama, lihat tangkapan layar:
2. di Manajer nama kotak dialog, pilih item yang ingin Anda gunakan, dan klik Ubah tombol.
3. Dalam muncul keluar Edit nama dialog, masukkan rumus ini = OFFSET (Sheet1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1) ke dalam Mengacu pada kotak teks, lihat tangkapan layar:
4. Lalu klik OK, lalu ulangi step2 dan step3 untuk menyalin formula ini = OFFSET (Sheet1! $ B $ 1, 0, 0, COUNTA ($ B: $ B), 1) ke dalam Mengacu pada kotak teks untuk Harga penjualan nama jangkauan
5. Dan dynamic named ranges telah dibuat. Bila Anda memasukkan nilai baru setelah data, kisaran dinamik akan otomatis disesuaikan dan formula yang dibuat juga akan berubah juga. Lihat tangkapan layar:
| | |
catatan: Jika ada sel kosong di tengah rentang Anda, hasil rumus Anda akan salah. Itu karena sel-sel yang tidak kosong tidak dihitung, jadi jangkauan Anda akan lebih pendek dari seharusnya, dan sel terakhir dalam kisaran akan tertinggal.
Tip: penjelasan untuk formula ini:
- = OFFSET (referensi, baris, cols, [height], [width])
- = OFFSET (Sheet1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1)
- referensi sesuai dengan posisi sel awal, dalam contoh ini Sheet1! $ A $ 1;
- baris mengacu pada jumlah baris yang akan Anda pindahkan ke bawah, relatif terhadap sel awal (atau ke atas, jika Anda menggunakan nilai negatif.), dalam contoh ini, 0 mengindikasikan daftarnya akan dimulai dari baris pertama ke bawah.
- kolom sesuai dengan jumlah kolom yang akan Anda pindahkan ke kanan, relatif terhadap sel awal (atau ke kiri, dengan menggunakan nilai negatif.), pada contoh contoh di atas, 0 mengindikasikan untuk memperluas kolom 0 ke kanan.
- [tinggi] sesuai dengan tinggi (atau jumlah baris) dari rentang mulai dari posisi yang disesuaikan. $ A: $ A, itu akan menghitung semua item yang dimasukkan di kolom A.
- [lebar] sesuai dengan lebar (atau jumlah kolom) dari range yang dimulai pada posisi yang disesuaikan. Pada rumus di atas, daftar kolom 1 akan lebar.
Anda dapat mengubah argumen ini sesuai kebutuhan Anda.
Dynamic Named Range Di Excel Dengan Kode VBA
Jika Anda memiliki banyak kolom, Anda bisa mengulang dan memasukkan formula individual untuk semua kolom yang tersisa, namun itu akan menjadi proses yang panjang dan berulang. Untuk mempermudah pencarian, Anda bisa menggunakan kode untuk membuat dynamic named range secara otomatis.
1. Aktifkan lembar kerja Anda.
2. Tahan ALT + F11 kunci, dan itu membuka Jendela Microsoft Visual Basic for Applications.
3. Klik Menyisipkan > Modul, dan paste kode berikut di Jendela Modul.
Kode Vba: buat dynamic named range
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| Sub CreateNamesxx()'Update 20131128Dim wb As Workbook, ws As WorksheetDim lrow As Long, lcol As Long, i As LongDim myName As String, Start As StringConst Rowno = 1Const Colno = 1Const Offset = 1On Error Resume NextSet wb = ActiveWorkbookSet ws = ActiveSheetlcol = ws.Cells(Rowno, 1).End(xlToRight).Columnlrow = ws.Cells(Rows.Count, Colno).End(xlUp).RowStart = Cells(Rowno, Colno).Addresswb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"For i = Colno To lcol myName = Replace(Cells(Rowno, i).Value, " ", "_") If myName <> "" Then wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)" End IfNextEnd Sub |
4. Lalu tekan F5 kunci untuk menjalankan kode, dan akan dihasilkan beberapa dynamic named ranges yang dinamai dengan nilai baris pertama dan juga menciptakan dynamic range yang disebut Data saya yang mencakup seluruh data.
5. Saat Anda memasukkan nilai baru setelah baris atau kolom, rentang akan diperluas juga. Lihat tangkapan layar:
 |
 |
 |
Catatan:
1. Dengan kode ini, nama jangkauan tidak ditampilkan di nama Box, untuk melihat dan menggunakan berbagai nama dengan mudah, saya telah menginstalKutools for Excel, Dengan yang Navigasi Pane, nama dynamic range yang dibuat dicantumkan.
2. Dengan kode ini, seluruh rentang data dapat diperluas secara vertikal atau horizontal, namun mengingat tidak boleh ada baris atau kolom kosong di antara data saat Anda memasukkan nilai baru.
3. Bila Anda menggunakan kode ini, rentang data Anda harus dimulai di sel A1.
Sumber : extendoffice.com
0 comments:
Post a Comment