Loncat ke daftar isi utama

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


panah gelembung kanan biru 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.

doc-dynamic-range1

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:

doc-dynamic-range2

2. Pilih rentang dan klik Menyisipkan > tabel, lihat tangkapan layar:

doc-dynamic-range3

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:

doc-dynamic-range4 -2 doc-dynamic-range5

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:

doc-dynamic-range6 -2 doc-dynamic-range7

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.


panah gelembung kanan biru 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:

doc-dynamic-range2

Anda dapat mengubah nama rentang menjadi nama rentang dinamis dengan langkah-langkah berikut:

1. Pergi ke klik Rumus > Manajer nama, lihat tangkapan layar:

doc-dynamic-range8

2. di Manajer nama kotak dialog, pilih item yang ingin Anda gunakan, dan klik Edit .

doc-dynamic-range9

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:

doc-dynamic-range10

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:

doc-dynamic-range6 -2 doc-dynamic-range7

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])
  • -1
  • = 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.


panah gelembung kanan biru 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:

doc-dynamic-range12
-1
doc-dynamic-range13

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

馃 Kutools AI Ajudan: Merevolusi analisis data berdasarkan: Eksekusi Cerdas   |  Hasilkan Kode  |  Buat Rumus Khusus  |  Analisis Data dan Hasilkan Grafik  |  Aktifkan Fungsi Kutools...
Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria    VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar    Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel    Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

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...

Deskripsi Produk


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!
Comments (4)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
really, really not helpful
This comment was minimized by the moderator on the site
please help i am trying to create a dynamic named range on excel 2016 typing "=offset(DATAENTRY!$B$6,,,counta(DATAENTRY!$B$6:$B$13))" but still it gives me an error saying it is not a formula.
This comment was minimized by the moderator on the site
You are a very good teacher: 1) step-by-step approach; 2) you do not bore the student with obvious material or conclusions; 3) yet you include all necessary material. I look forward to more tutorials from you.
This comment was minimized by the moderator on the site
Thanks for good article
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations