Note: The other languages of the website are Google-translated. Back to English

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

Tab Office Mengaktifkan Pengeditan dan Penjelajahan Tab di Office, dan Jadikan Pekerjaan Anda Lebih Mudah ...
Kutools for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%
  • Gunakan Kembali Apa Pun: Tambahkan rumus, bagan, dan hal lain yang paling sering digunakan atau rumit ke favorit Anda, dan gunakan kembali dengan cepat di masa mendatang.
  • Lebih dari 20 fitur teks: Ekstrak Nomor dari String Teks; Ekstrak atau Hapus Bagian dari Teks; Ubah Angka dan Mata Uang ke Kata Bahasa Inggris.
  • Gabungkan Alat: Beberapa Buku Kerja dan Lembar menjadi Satu; Gabungkan Beberapa Sel / Baris / Kolom Tanpa Kehilangan Data; Gabungkan Baris dan Jumlah Duplikat.
  • Alat Pisah: Membagi Data Menjadi Beberapa Lembar Berdasarkan Nilai; Satu Buku Kerja ke Beberapa File Excel, PDF atau CSV; Satu Kolom ke Beberapa Kolom.
  • Tempel Melewati Baris Tersembunyi / Difilter; Hitung Dan Jumlah dengan Warna Latar Belakang; Kirim Email Hasil Personalisasi ke Beberapa Penerima secara Massal.
  • Filter Super: Buat skema filter tingkat lanjut dan terapkan ke setiap lembar; Jenis menurut minggu, hari, frekuensi, dan lainnya; SARING dengan tebal, rumus, komentar ...
  • Lebih dari 300 fitur canggih; Bekerja dengan Office 2007-2019 dan 365; Mendukung semua bahasa; Penerapan yang mudah di perusahaan atau organisasi Anda.

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 for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%

  • Reuse: Masukkan dengan cepat rumus, bagan yang kompleks dan apa pun yang pernah Anda gunakan sebelumnya; Enkripsi Sel dengan kata sandi; Buat Milis dan mengirim email ...
  • Bilah Formula Super (dengan mudah mengedit beberapa baris teks dan rumus); Membaca Tata Letak (membaca dan mengedit sel dalam jumlah besar dengan mudah); Tempel ke Rentang yang Difilter...
  • Gabungkan Sel / Baris / Kolom tanpa kehilangan Data; Pisahkan Konten Sel; Gabungkan Baris / Kolom Duplikat... Mencegah Sel Duplikat; Bandingkan Rentang...
  • Pilih Duplikat atau Unik Baris; Pilih Baris Kosong (semua sel kosong); Temukan Super dan Temukan Fuzzy di Banyak Buku Kerja; Pilih Acak ...
  • Salinan Tepat Beberapa Sel tanpa mengubah referensi rumus; Buat Referensi Otomatis ke Beberapa Lembar; Sisipkan Poin, Kotak Centang, dan lainnya ...
  • Ekstrak Teks, Tambahkan Teks, Hapus berdasarkan Posisi, Hapus Space; Membuat dan Mencetak Subtotal Paging; Konversi Konten Antar Sel dan Komentar...
  • Filter Super (simpan dan terapkan skema filter ke sheet lain); Penyortiran Lanjutan menurut bulan / minggu / hari, frekuensi dan lainnya; Filter Khusus dengan huruf tebal, miring ...
  • Gabungkan Workbooks dan WorkSheets; Gabungkan Tabel berdasarkan kolom kunci; Pisahkan Data menjadi Beberapa Lembar; Konversi Batch xls, xlsx dan PDF...
  • Lebih dari 300 fitur canggih. Mendukung Office / Excel 2007-2019 dan 365. Mendukung semua bahasa. Penerapan yang mudah di perusahaan atau organisasi Anda. Fitur lengkap uji coba gratis 30 hari. Jaminan uang kembali 60 hari.
tab kte 201905

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.
  • Tingkatkan produktivitas Anda hingga 50%, dan kurangi ratusan klik mouse untuk Anda setiap hari!
officetab bawah
Urutkan komentar berdasarkan
komentar (4)
Belum ada peringkat. Jadilah yang pertama memberi peringkat!
Komentar ini diminimalkan oleh moderator di situs
Terima kasih artikelnya bagus
Komentar ini diminimalkan oleh moderator di situs
Anda adalah guru yang sangat baik: 1) pendekatan langkah demi langkah; 2) Anda tidak membuat siswa bosan dengan materi atau kesimpulan yang jelas; 3) namun Anda memasukkan semua materi yang diperlukan. Saya menantikan lebih banyak tutorial dari Anda.
Komentar ini diminimalkan oleh moderator di situs
tolong bantu saya mencoba membuat rentang nama dinamis di excel 2016 mengetik "=offset(DATAENTRY!$B$6,,,counta(DATAENTRY!$B$6:$B$13))" tetapi masih memberi saya kesalahan saat mengatakannya bukan rumus.
Komentar ini diminimalkan oleh moderator di situs
sungguh, sangat tidak membantu
Belum ada komentar yang diposting di sini
Tinggalkan komentar anda
Posting sebagai Tamu
×
Beri peringkat pos ini:
0   Karakter
Lokasi yang Disarankan