Mengapa anda mesti menggunakan julat bernama di Excel

Rentang bernama adalah ciri Microsoft Excel yang berguna, tetapi sering kali dimanfaatkan.  Range bernama boleh membuat formula lebih mudah difahami (dan debug), memudahkan penciptaan spreadsheet rumit, dan memudahkan makro anda.

Julat bernama hanyalah julat (sama ada sel tunggal, atau pelbagai sel) yang mana anda memberikan nama.  Anda kemudian boleh menggunakan nama itu sebagai ganti rujukan sel biasa dalam formula, dalam makro, dan untuk menentukan sumber untuk graf atau pengesahan data.

Menggunakan nama julat, seperti taxrate, sebagai ganti rujukan sel standard, seperti lembaran2!$ C $ 11, boleh membuat spreadsheet lebih mudah difahami dan debug/audit.

Menggunakan julat bernama di Excel

Contohnya, mari kita lihat borang pesanan mudah.  Fail kami merangkumi borang pesanan yang boleh diisi dengan jatuh turun untuk memilih kaedah penghantaran, ditambah lembaran kedua dengan jadual kos penghantaran, dan kadar cukai.

Versi 1 (tanpa julat bernama) menggunakan rujukan sel gaya A1 biasa dalam formulasnya (ditunjukkan dalam bar formula di bawah).

Versi 2 menggunakan julat bernama, menjadikan formulasnya lebih mudah difahami.  Range bernama juga memudahkan untuk memasukkan formula, kerana Excel akan memaparkan senarai nama, termasuk nama fungsi, yang boleh anda pilih dari, setiap kali anda mula menaip nama dalam formula.  Klik dua kali nama dalam senarai pilih untuk menambahkannya ke formula anda.

Membuka Pengurus Nama tetingkap dari Formula tab memaparkan senarai nama julat dan julat sel yang mereka rujukan.

Tetapi julat bernama mempunyai manfaat lain juga.  Dalam fail contoh kami, kaedah penghantaran dipilih menggunakan dropdown (pengesahan data) dalam sel B13 pada lembaran1.  Kaedah yang dipilih kemudian digunakan untuk mencari kos penghantaran pada lembaran2.

Tanpa julat yang dinamakan, pilihan dropdown mesti dimasukkan secara manual kerana pengesahan data tidak akan membolehkan anda memilih senarai sumber pada lembaran yang berbeza.  Jadi semua pilihan mesti dimasukkan dua kali: sekali dalam senarai dropdown, dan sekali lagi pada lembaran2.  Di samping itu, kedua -dua senarai mesti sepadan.

Sekiranya ralat dibuat dalam salah satu penyertaan dalam kedua -dua senarai, maka formula kos penghantaran akan menghasilkan ralat #N/A apabila pilihan yang salah dipilih.  Menamakan senarai di Sheet2 sebagai PengirimanMethods menghapuskan kedua -dua masalah.

Anda boleh merujuk julat yang dinamakan apabila menentukan pengesahan data untuk senarai dropdown dengan hanya memasukkan = ShippingMethods Di medan sumber, sebagai contoh.  Ini membolehkan anda menggunakan senarai pilihan yang ada di helaian lain.

Dan jika dropdown merujuk sel -sel sebenar yang digunakan dalam carian (untuk formula kos penghantaran), maka pilihan dropdown akan selalu sepadan dengan senarai carian, mengelakkan #N/A kesilapan.

Buat julat bernama di Excel

Untuk membuat julat yang dinamakan, pilih sel atau julat sel yang anda ingin namakan, kemudian klik di Kotak nama (Di mana alamat sel yang dipilih biasanya dipaparkan, hanya kiri bar formula), taipkan nama yang anda mahu gunakan, dan tekan Masukkan.

Anda juga boleh membuat julat bernama dengan mengklik Baru butang di tetingkap Nama Pengurus.  Ini dibuka a Nama baru tetingkap di mana anda boleh memasukkan nama baru.

Secara lalai, julat yang akan dinamakan ditetapkan ke julat apa pun yang dipilih apabila anda mengklik butang baru, tetapi anda boleh mengedit julat itu sebelum atau selepas menyimpan nama baru.

Perhatikan bahawa nama jarak tidak boleh memasukkan ruang, walaupun mereka boleh termasuk garis bawah dan tempoh.  Umumnya, nama harus bermula dengan huruf dan kemudian hanya mengandungi huruf, nombor, tempoh, atau garis bawah.

Nama tidak sensitif kes, tetapi menggunakan rentetan kata-kata yang dipermodalkan, seperti taxrate atau Disember2018sales, menjadikan nama lebih mudah dibaca dan diiktiraf.  Anda tidak boleh menggunakan nama julat yang meniru rujukan sel yang sah, seperti anjing26.

Anda boleh mengedit nama julat anda atau menukar julat yang mereka rujuk menggunakan tetingkap Nama Pengurus.

Perhatikan juga bahawa setiap julat bernama mempunyai skop yang ditetapkan.  Biasanya, skop akan lalai untuk Buku kerja, yang bermaksud nama julat boleh dirujuk dari mana sahaja dalam buku kerja.  Walau bagaimanapun, ia juga mungkin mempunyai dua atau lebih julat dengan nama yang sama pada lembaran yang berasingan, tetapi dalam buku kerja yang sama.

Sebagai contoh, anda mungkin mempunyai fail data jualan dengan helaian berasingan untuk Januari, Februari, Mac, dll.  Setiap helaian boleh mempunyai sel (bernama julat) yang dipanggil bulanan, tetapi biasanya skop setiap nama tersebut hanya akan menjadi lembaran yang mengandunginya.

Oleh itu, formula = Bulat (bulanan, 0) akan memberikan jualan bulan Februari, dibulatkan kepada dolar keseluruhan terdekat, jika formula berada pada lembaran Februari, tetapi jualan Mac jika pada lembaran Mac, dll.

Untuk mengelakkan kekeliruan dalam buku kerja yang mempunyai pelbagai julat pada lembaran berasingan dengan nama yang sama atau hanya buku kerja yang rumit dengan berpuluh -puluh atau beratus -ratus julat bernama, dapat membantu memasukkan nama lembaran sebagai sebahagian dari setiap nama julat.

Ini juga menjadikan setiap nama julat unik, sehingga semua nama dapat memiliki skop buku kerja.  Contohnya, Januari_monthlysales, Februari_monthlysales, bajet_date, order_date, dll.

Dua amaran mengenai skop julat bernama: (1) anda tidak dapat mengedit skop julat bernama selepas ia dibuat, dan (2) anda hanya boleh menentukan skop julat bernama baru jika anda menciptanya menggunakan Baru butang di Pengurus Nama tetingkap.

Sekiranya anda membuat nama julat baru dengan menaipnya di dalam kotak nama, skop akan lalai sama ada buku kerja (jika tiada julat lain dengan nama yang sama ada), atau ke lembaran di mana nama itu dibuat.  Oleh itu, untuk membuat julat bernama baru yang skopnya terhad kepada lembaran tertentu, gunakan butang Pengurus Nama "Baru".

Akhirnya, bagi mereka yang menulis makro, nama julat boleh dirujuk dengan mudah dalam kod VBA dengan hanya meletakkan nama julat dalam kurungan.  Contohnya, bukannya buku ini.Lembaran (1).Sel (2,3) Anda hanya boleh menggunakan [Salestotal] jika nama itu merujuk kepada sel tersebut.

Mula menggunakan julat bernama di lembaran kerja Excel anda dan anda dengan cepat akan menghargai manfaatnya! Nikmati!