Mengapa Anda harus menggunakan rentang bernama di Excel

Rentang bernama adalah fitur Microsoft Excel yang berguna, tetapi sering kurang dimanfaatkan.  Rentang bernama dapat membuat rumus lebih mudah dipahami (dan debug), menyederhanakan penciptaan spreadsheet yang rumit, dan menyederhanakan makro Anda.

Kisaran bernama hanyalah rentang (baik sel tunggal, atau berbagai sel) yang Anda tetapkan.  Anda kemudian dapat menggunakan nama itu sebagai pengganti referensi sel normal dalam rumus, dalam makro, dan untuk mendefinisikan sumber untuk grafik atau validasi data.

Menggunakan nama jangkauan, seperti Taxrate, sebagai pengganti referensi sel standar, seperti Sheet2!$ C $ 11, dapat membuat spreadsheet lebih mudah dipahami dan men -debug/audit.

Menggunakan rentang bernama di Excel

Misalnya, mari kita lihat formulir pemesanan sederhana.  File kami mencakup formulir pesanan yang dapat diisi dengan dropdown untuk memilih metode pengiriman, ditambah lembar kedua dengan tabel biaya pengiriman, dan tarif pajak.

Versi 1 (tanpa rentang bernama) menggunakan referensi sel A1-gaya normal dalam rumusnya (ditunjukkan dalam bilah rumus di bawah).

Versi 2 menggunakan rentang bernama, membuat formulas lebih mudah dipahami.  Named Ranges juga memudahkan untuk memasukkan rumus, karena Excel akan menampilkan daftar nama, termasuk nama fungsi, yang dapat Anda pilih, setiap kali Anda mulai mengetik nama dalam formula.  Klik dua kali nama dalam daftar Pilih untuk menambahkannya ke formula Anda.

Membuka Manajer Nama jendela dari Rumus Tab menampilkan daftar nama rentang dan rentang sel yang mereka referensi.

Tetapi rentang bernama juga memiliki manfaat lain.  Dalam file contoh kami, metode pengiriman dipilih menggunakan dropdown (validasi data) di sel B13 di sheet1.  Metode yang dipilih kemudian digunakan untuk mencari biaya pengiriman pada lembar2.

Tanpa rentang bernama, pilihan dropdown harus dimasukkan secara manual karena validasi data tidak akan memungkinkan Anda untuk memilih daftar sumber pada lembar yang berbeda.  Jadi semua pilihan harus dimasukkan dua kali: sekali dalam daftar dropdown, dan sekali lagi di sheet2.  Selain itu, kedua daftar harus cocok.

Jika kesalahan dibuat di salah satu entri di kedua daftar, maka formula biaya pengiriman akan menghasilkan kesalahan #N/A ketika pilihan yang salah dipilih.  Menamai daftar di sheet2 sebagai Metode pengiriman menghilangkan kedua masalah.

Anda dapat merujuk rentang bernama saat mendefinisikan validasi data untuk daftar dropdown dengan hanya masuk = Metode pengiriman di bidang sumber, misalnya.  Ini memungkinkan Anda untuk menggunakan daftar pilihan yang ada di lembar lain.

Dan jika dropdown merujuk sel aktual yang digunakan dalam pencarian (untuk formula biaya pengiriman), maka pilihan dropdown akan selalu cocok dengan daftar pencarian, menghindari kesalahan #N/A.

Buat rentang bernama di Excel

Untuk membuat rentang bernama, cukup pilih sel atau rentang sel yang ingin Anda beri nama, lalu klik di Kotak Nama (Di mana alamat sel yang dipilih biasanya ditampilkan, tepat di sebelah kiri bilah formula), ketik nama yang ingin Anda gunakan, dan tekan Memasuki.

Anda juga dapat membuat rentang bernama dengan mengklik Baru Tombol di Jendela Manajer Nama.  Ini membuka a Nama baru jendela tempat Anda dapat memasukkan nama baru.

Secara default, rentang yang akan dinamai diatur ke rentang apa pun yang dipilih saat Anda mengklik tombol baru, tetapi Anda dapat mengedit rentang itu sebelum atau setelah menyimpan nama baru.

Perhatikan bahwa nama rentang tidak dapat menyertakan spasi, meskipun dapat mencakup garis bawah dan periode.  Umumnya, nama harus dimulai dengan surat dan kemudian hanya berisi huruf, angka, titik, atau garis bawah.

Nama tidak peka huruf besar-kecil, tetapi menggunakan serangkaian kata-kata yang dikapitalisasi, seperti Taxrate atau Desember2018Sales, membuat nama lebih mudah dibaca dan dikenali.  Anda tidak dapat menggunakan nama jangkauan yang meniru referensi sel yang valid, seperti Dog26.

Anda dapat mengedit nama jangkauan Anda atau mengubah rentang yang mereka rujuk untuk menggunakan jendela manajer nama.

Perhatikan juga bahwa setiap rentang bernama memiliki ruang lingkup yang ditentukan.  Biasanya, ruang lingkup akan default Buku kerja, yang berarti nama jangkauan dapat dirujuk dari mana saja dalam buku kerja.  Namun, juga dimungkinkan untuk memiliki dua atau lebih rentang dengan nama yang sama di lembar terpisah, tetapi dalam buku kerja yang sama.

Misalnya, Anda mungkin memiliki file data penjualan dengan lembar terpisah untuk Januari, Februari, Maret, dll.  Setiap lembar dapat memiliki sel (rentang bernama) yang disebut bulanan, tetapi biasanya ruang lingkup masing -masing nama itu hanya akan menjadi lembar yang berisinya.

Dengan demikian, formula = Bundar (bulanan, 0) akan memberikan penjualan Februari, dibulatkan ke seluruh dolar terdekat, jika formulanya ada di lembar Februari, tetapi penjualan Maret jika di lembar Maret, dll.

Untuk menghindari kebingungan dalam buku kerja yang memiliki beberapa rentang pada lembar terpisah dengan nama yang sama atau hanya buku kerja yang rumit dengan lusinan atau ratusan rentang bernama, dapat membantu untuk memasukkan nama lembar sebagai bagian dari setiap nama rentang.

Ini juga membuat setiap nama rentang unik, sehingga semua nama dapat memiliki ruang kerja buku kerja.  Misalnya, Januari_monthlysales, Februari_Monthlysales, Budget_Date, order_date, dll.

Dua peringatan mengenai ruang lingkup rentang bernama: (1) Anda tidak dapat mengedit ruang lingkup kisaran bernama setelah dibuat, dan (2) Anda hanya dapat menentukan ruang lingkup rentang bernama baru jika Anda membuatnya menggunakannya Baru tombol di Manajer Nama jendela.

Jika Anda membuat nama jangkauan baru dengan mengetiknya di kotak nama, ruang lingkup akan default ke salah satu buku kerja (jika tidak ada rentang lain dengan nama yang sama), atau ke lembar di mana nama sedang dibuat.  Oleh karena itu, untuk membuat kisaran bernama baru yang ruang lingkupnya terbatas pada lembar tertentu, gunakan tombol "baru" Manager Name Manager.

Akhirnya, bagi mereka yang menulis makro, nama jangkauan dapat dengan mudah direferensikan dalam kode VBA dengan hanya menempatkan nama jangkauan di dalam tanda kurung.  Misalnya, alih -alih buku ini.Lembar (1).Sel (2,3) Anda cukup menggunakan [salestotal] jika nama itu mengacu pada sel itu.

Mulailah menggunakan rentang bernama di lembar kerja Excel Anda dan Anda akan dengan cepat menghargai manfaatnya! Menikmati!