Bagaimana cara membuat rentang nama dinamis di Excel?
Biasanya, Bernama Ranges sangat berguna bagi pengguna Excel, Anda bisa menentukan rangkaian nilai dalam kolom, memberi nama kolom itu, lalu Anda bisa merujuk ke rentang tersebut dengan nama dan bukan referensi selnya. Namun sebagian besar waktu, Anda perlu menambahkan data baru untuk memperluas nilai data rentang rujukan Anda di masa mendatang. Dalam hal ini, Anda harus kembali ke Rumus > Manajer nama dan tentukan ulang rentang untuk menyertakan nilai baru. Untuk menghindari hal ini, Anda dapat membuat rentang bernama dinamis yang berarti Anda tidak perlu menyesuaikan referensi sel setiap kali Anda menambahkan baris atau kolom baru ke daftar.
Buat rentang nama dinamis di Excel dengan membuat tabel
Buat rentang nama dinamis di Excel dengan Fungsi
Buat rentang nama dinamis di Excel dengan kode VBA
Buat rentang nama dinamis di Excel dengan membuat tabel
Jika Anda menggunakan Excel 2007 atau versi yang lebih baru, cara termudah untuk membuat rentang bernama dinamis adalah dengan membuat tabel Excel bernama.
Katakanlah, Anda memiliki rentang data berikut yang perlu menjadi rentang bernama dinamis.
1. Pertama, saya akan menentukan nama rentang untuk rentang ini. Pilih rentang A1: A6 dan masukkan namanya Tanggal ke dalam nama Box, Lalu tekan Enter kunci. Untuk menentukan nama range B1: B6 sebagai Saleprice dengan cara yang sama. Pada saat yang sama, saya membuat rumus = jumlah (Harga Salep) di sel kosong, lihat tangkapan layar:
2. Pilih rentang dan klik Menyisipkan > tabel, lihat tangkapan layar:
3. di Buat tabel kotak prompt, centang Tabel saya memiliki header (jika rentang tidak memiliki tajuk, hapus centang), klik OK tombol, dan data rentang telah diubah ke tabel. Lihat tangkapan layar:
4. Dan ketika Anda memasukkan nilai baru setelah data, rentang bernama akan secara otomatis menyesuaikan dan rumus yang dibuat juga akan berubah. Lihat tangkapan layar berikut:
Catatan:
1. Data yang baru Anda masukkan harus berdekatan dengan data di atas, artinya tidak ada baris atau kolom yang kosong antara data baru dengan data yang sudah ada.
2. Di tabel, Anda dapat memasukkan data di antara nilai-nilai yang ada.
Buat rentang nama dinamis di Excel dengan Fungsi
Di Excel 2003 atau versi sebelumnya, metode pertama tidak akan tersedia, jadi inilah cara lain untuk Anda. Pengikut OFFSET () fungsi ini dapat membantu Anda, tetapi agak merepotkan. Misalkan saya memiliki berbagai data yang berisi nama rentang yang telah saya tentukan, misalnya, A1: A6 nama kisarannya adalah Tanggal, dan B1: B6 nama kisaran adalah Harga penjualan, pada saat yang sama, saya membuat rumus untuk Harga penjualan. Lihat tangkapan layar:
Anda dapat mengubah nama rentang menjadi 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 Edit .
3. Di muncul keluar Edit Nama dialog, masukkan rumus ini = OFFSET (Lembar1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1) ke dalam Mengacu pada kotak teks, lihat tangkapan layar:
4. Lalu klik OK, lalu ulangi langkah2 dan langkah3 untuk menyalin rumus ini = OFFSET (Lembar1! $ B $ 1, 0, 0, COUNTA ($ B: $ B), 1) ke dalam Mengacu pada kotak teks untuk Harga penjualan nama rentang.
5. Dan rentang bernama dinamis telah dibuat. Saat Anda memasukkan nilai baru setelah data, rentang bernama akan secara otomatis menyesuaikan dan rumus yang dibuat juga akan berubah. Lihat tangkapan layar:
Catatan: Jika ada sel kosong di tengah rentang Anda, hasil rumus Anda akan salah. Itu karena sel yang tidak kosong tidak dihitung, sehingga rentang Anda akan lebih pendek dari yang seharusnya, dan sel terakhir dalam rentang akan ditinggalkan.
Tip: penjelasan untuk rumus ini:
- = OFFSET (referensi, baris, kolom, [tinggi], [lebar])
- = OFFSET (Lembar1! $ 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 menunjukkan daftar 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, menggunakan nilai negatif.), dalam rumus contoh di atas, 0 menunjukkan perluas 0 kolom ke kanan.
- [tinggi] sesuai dengan tinggi (atau jumlah baris) rentang yang dimulai dari posisi yang disesuaikan. $ A: $ A, itu akan menghitung semua item yang dimasukkan di kolom A.
- [lebar] sesuai dengan lebar (atau jumlah kolom) rentang yang dimulai dari posisi yang disesuaikan. Dalam rumus di atas, daftar akan menjadi lebar 1 kolom.
Anda dapat mengubah argumen ini sesuai kebutuhan Anda.
Buat rentang nama dinamis di Excel dengan kode VBA
Jika Anda memiliki beberapa kolom, Anda dapat mengulangi dan memasukkan rumus individual untuk semua kolom yang tersisa, tetapi itu akan menjadi proses yang panjang dan berulang. Untuk mempermudah, Anda dapat menggunakan kode untuk membuat rentang bernama dinamis 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 tempel kode berikut di Jendela Modul.
Kode VBA: buat rentang bernama dinamis
Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.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 If
Next
End Sub
4. Lalu tekan F5 kunci untuk menjalankan kode, dan akan dihasilkan beberapa rentang bernama dinamis yang dinamai dengan nilai baris pertama dan juga menciptakan rentang dinamis yang disebut Data saya yang mencakup seluruh data.
5. Saat Anda memasukkan nilai baru setelah baris atau kolom, rentang juga akan diperluas. Lihat tangkapan layar:
Catatan:
1. Dengan kode ini, nama rentang tidak ditampilkan di nama Box, untuk melihat dan menggunakan nama rentang dengan nyaman, saya telah menginstal Kutools untuk Excel, Dengan yang Navigasi Pane, nama rentang dinamis yang dibuat dicantumkan.
2. Dengan kode ini, seluruh rentang data dapat diperluas secara vertikal atau horizontal, tetapi yang perlu diingat tidak boleh ada baris atau kolom kosong di antara data saat Anda memasukkan nilai baru.
3. Saat Anda menggunakan kode ini, rentang data Anda harus dimulai dari sel A1.
Artikel terkait:
Bagaimana cara memperbarui grafik secara otomatis setelah memasukkan data baru di Excel?
Alat Produktivitas Kantor Terbaik
Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu. Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...
Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah
- Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
- Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
- Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!