Phạm vi được đặt tên là một tính năng hữu ích, nhưng thường không được sử dụng hết của Microsoft Excel . Phạm vi được đặt tên có thể làm cho công thức dễ hiểu hơn (và gỡ lỗi), đơn giản hóa việc tạo bảng tính phức tạp và đơn giản hóa macro của bạn.

Dải ô đã đặt tên chỉ là một dải ô (một ô hoặc một dải ô) mà bạn chỉ định tên. Sau đó, bạn có thể sử dụng tên đó thay cho các tham chiếu ô bình thường trong công thức, trong macro và để xác định nguồn cho đồ thị hoặc xác thực dữ liệu.

Sử dụng tên phạm vi, như TaxRate , thay cho tham chiếu ô tiêu chuẩn, như Sheet2 ! $ C $ 11, có thể giúp bảng tính dễ hiểu hơn và gỡ lỗi / kiểm tra.

Sử dụng Dải ô được Đặt tên trong Excel

Ví dụ, hãy xem một mẫu đơn đặt hàng đơn giản.  Tệp của chúng tôi bao gồm một biểu mẫu đơn đặt hàng có thể điền với menu thả xuống để chọn phương thức vận chuyển, cùng với trang thứ hai có bảng chi phí vận chuyển và thuế suất.

Phiên bản 1 (không có phạm vi được đặt tên) sử dụng tham chiếu ô kiểu A1^(A1-style) bình thường trong các công thức của nó (được hiển thị trong thanh công thức bên dưới).

Phiên bản 2 sử dụng các phạm vi được đặt tên, làm cho các công thức của nó dễ hiểu hơn nhiều. Dải ô được đặt tên cũng giúp nhập công thức dễ dàng hơn, vì Excel sẽ hiển thị danh sách tên, bao gồm cả tên hàm, mà bạn có thể chọn bất cứ khi nào bạn bắt đầu nhập tên vào công thức.  Bấm đúp vào^{(Double-click)} tên trong danh sách chọn để thêm nó vào công thức của bạn.

Mở cửa sổ Trình quản lý Tên từ tab ^{(Name Manager)}Công thức^(Formulas) sẽ hiển thị danh sách tên phạm vi và phạm vi ô mà chúng tham chiếu.

Nhưng các phạm vi được đặt tên cũng có những lợi ích khác. Trong các tệp ví dụ của chúng tôi, phương thức vận chuyển được chọn bằng cách sử dụng trình đơn thả xuống (xác thực dữ liệu) trong ô B13 trên Trang tính1^(Sheet1) . Phương pháp đã chọn sau đó được sử dụng để tra cứu chi phí vận chuyển trên Sheet2 .

Nếu không có phạm vi được đặt tên, các lựa chọn thả xuống phải được nhập theo cách thủ công vì xác thực dữ liệu sẽ không cho phép bạn chọn danh sách nguồn trên một trang tính khác. Vì vậy, tất cả các lựa chọn phải được nhập hai lần: một lần trong danh sách thả xuống và một lần nữa trên Trang tính2^(Sheet2) . Ngoài ra, hai danh sách phải khớp nhau.

Nếu có lỗi xảy ra ở một trong các mục trong một trong hai danh sách, thì công thức chi phí vận chuyển sẽ tạo ra lỗi # N / A khi lựa chọn sai được chọn. Đặt tên danh sách trên Sheet2 là ShippingMethods giúp loại bỏ cả hai vấn đề.

Bạn có thể tham chiếu một dải ô đã đặt tên khi xác định xác thực dữ liệu cho danh sách thả xuống bằng cách chỉ cần nhập =ShippingMethods trong trường nguồn, chẳng hạn. Điều này cho phép bạn sử dụng danh sách các lựa chọn nằm trên một trang tính khác.

Và nếu danh sách thả xuống tham chiếu đến các ô thực tế được sử dụng trong tra cứu (đối với công thức chi phí vận chuyển), thì các lựa chọn thả xuống sẽ luôn khớp với danh sách tra cứu, tránh lỗi # N / A.

Tạo một phạm vi được đặt tên trong Excel

Để tạo một phạm vi đã đặt tên, chỉ cần chọn ô hoặc phạm vi ô bạn muốn đặt tên, sau đó nhấp vào Hộp Tên^{( Name Box)} (nơi địa chỉ ô đã chọn thường được hiển thị, ngay bên trái của Thanh Công thức^{(Formula Bar)} ), nhập tên bạn muốn sử dụng. và nhấn Enter .

Bạn cũng có thể tạo một phạm vi đã đặt tên bằng cách nhấp vào nút Mới^(New) trong cửa sổ Trình quản lý Tên. ^(Manager)Thao tác này sẽ mở ra cửa sổ Tên mới^{(New Name)}  , nơi bạn có thể nhập tên mới.

Theo mặc định, phạm vi được đặt tên được đặt thành bất kỳ phạm vi nào được chọn khi bạn nhấp vào nút Mới^(New) , nhưng bạn có thể chỉnh sửa phạm vi đó trước hoặc sau khi lưu tên mới.

Lưu ý rằng tên phạm vi không được bao gồm dấu cách, mặc dù chúng có thể bao gồm dấu gạch dưới và dấu chấm câu. Nói chung, tên phải bắt đầu bằng một chữ cái và sau đó chỉ chứa các chữ cái, số, dấu chấm hoặc dấu gạch dưới.

Tên không phân biệt chữ hoa chữ thường, nhưng việc sử dụng một chuỗi các từ viết hoa, chẳng hạn như TaxRate hoặc December2018Sales , làm cho tên dễ đọc và dễ nhận ra hơn. Bạn không thể sử dụng tên dải ô bắt chước tham chiếu ô hợp lệ, chẳng hạn như Dog26 .

Bạn có thể chỉnh sửa tên phạm vi của mình hoặc thay đổi phạm vi mà chúng tham chiếu đến bằng cách sử dụng cửa sổ Trình quản lý tên.^(Manager)

Cũng lưu ý rằng mỗi phạm vi được đặt tên có một phạm vi xác định. Thông thường, phạm vi sẽ được mặc định là Workbook , có nghĩa là tên phạm vi có thể được tham chiếu từ bất kỳ đâu trong sổ làm việc. Tuy nhiên, cũng có thể có hai hoặc nhiều phạm vi có cùng tên trên các trang tính riêng biệt, nhưng trong cùng một sổ làm việc.

Ví dụ: bạn có thể có tệp dữ liệu bán hàng với các trang tính riêng biệt cho tháng 1^(January) , tháng 2^(February) , tháng 3^(March) , v.v. Mỗi trang tính có thể có một ô (phạm vi được đặt tên) được gọi là MonthlySales , nhưng thông thường phạm vi của mỗi tên đó sẽ chỉ là trang tính chứa nó.

Do đó, công thức =ROUND(MonthlySales,0) sẽ cung cấp doanh số bán hàng tháng Hai^(February) , được làm tròn đến nguyên đô la gần nhất, nếu công thức nằm trên trang tính tháng hai^(February) , nhưng doanh số bán hàng tháng ba^(March) nếu trên trang tính tháng ba^(March) , v.v.

Để tránh nhầm lẫn trong các sổ làm việc có nhiều phạm vi trên các trang tính riêng biệt có cùng tên hoặc đơn giản là sổ làm việc phức tạp với hàng chục hoặc hàng trăm phạm vi được đặt tên, có thể hữu ích nếu bao gồm tên trang tính như một phần của mỗi tên phạm vi.

Điều này cũng làm cho mỗi tên phạm vi là duy nhất, để tất cả các tên có thể có phạm vi Sổ^(Workbook) làm việc . Ví dụ: January_MonthlySales , Tháng^{(February_MonthlySales)} Hai_MonthlySales , Budget_Date , Order_Date , v.v.

Hai lưu ý về phạm vi của phạm vi đã đặt tên:^{(Two cautions regarding the scope of named ranges:)} (1) Bạn không thể chỉnh sửa phạm vi của phạm vi đã đặt tên sau khi nó được tạo và (2) bạn chỉ có thể chỉ định phạm vi của phạm vi được đặt tên mới nếu bạn tạo phạm vi bằng nút Mới^(New) trong cửa sổ Trình quản lý tên .^{( Name Manager)}

Nếu bạn tạo tên phạm vi mới bằng cách nhập tên phạm vi đó vào Hộp^(Box) tên , phạm vi sẽ mặc định thành Sổ^(Workbook) làm việc (nếu không có phạm vi nào khác có cùng tên tồn tại) hoặc trang tính nơi tên đang được tạo. Do đó, để tạo một phạm vi được đặt tên mới có phạm vi giới hạn trong một trang tính cụ thể, hãy sử dụng nút "Mới" của Trình quản lý Tên.

Cuối cùng, đối với những người viết macro, tên phạm vi có thể được tham chiếu dễ dàng trong mã VBA bằng cách chỉ cần đặt tên phạm vi trong dấu ngoặc. Ví dụ: thay vì ThisWorkbook.Sheets (1) .Cells (2,3), bạn có thể chỉ cần sử dụng [ SalesTotal ] nếu tên đó tham chiếu đến ô đó.

Bắt đầu^(Start) sử dụng các phạm vi được đặt tên trong trang tính Excel của bạn và bạn sẽ nhanh chóng đánh giá cao những lợi ích! Vui thích!

Why You Should Be Using Named Ranges in Excel

Named ranges are a usеful, but often underutilized, feature of Microsoft Εxcel.  Named ranges can make formulas easier to υnderstand (and debug), simplify the creation of complicated spreadsheets, and simplify your macros.

A named range is just a range (either a single cell, or a range of cells) to which you assign a name.  You can then use that name in place of normal cell references in formulas, in macros, and for defining the source for graphs or data validation.

Using a range name, like TaxRate, in place of a standard cell reference, like Sheet2!$C$11, can make a spreadsheet easier to understand and debug/audit.

Using Named Ranges in Excel

For example, let’s look at a simple order form.  Our file includes a fillable order form with a dropdown to select the shipping method, plus a second sheet with a table of shipping costs, and the tax rate.

Version 1 (without named ranges) uses normal A1-style cell references in its formulas (shown in the formula bar below).

Version 2 uses named ranges, making its formulas much easier to understand.  Named ranges also make it easier to enter formulas, since Excel will display a list of names, including function names, that you can pick from, whenever you start to type a name in a formula.  Double-click the name in the pick list to add it to your formula.

Opening the Name Manager window from the Formulas tab displays a list of the range names and the cell ranges they reference.

But named ranges have other benefits also.  In our example files, the shipping method is selected using a dropdown (data validation) in cell B13 on Sheet1.  The selected method is then used to lookup the shipping costs on Sheet2.

Without named ranges, the dropdown choices must be manually entered since data validation will not allow you to select a source list on a different sheet.  So all of the choices must be entered twice: once in the dropdown list, and again on Sheet2.  In addition, the two lists must match.

If an error is made in one of the entries in either list, then the shipping cost formula will generate an #N/A error when the erroneous choice is selected.  Naming the list on Sheet2 as ShippingMethods eliminates both problems.

You can reference a named range when defining the data validation for a dropdown list by simply entering =ShippingMethods in the source field, for example.  This allows you to use a list of choices that are on another sheet.

And if the dropdown is referencing the actual cells used in the lookup (for the shipping cost formula), then the dropdown choices will always match the lookup list, avoiding #N/A errors.

Create a Named Range in Excel

To create a named range, simply select the cell or range of cells you want to name, then click in the Name Box (where the selected cell address is normally displayed, just left of the Formula Bar), type the name you want to use, and press Enter.

You can also create a named range by clicking the New button in the Name Manager window.  This opens a New Name window where you can enter the new name.

By default, the range to be named is set to whatever range is selected when you click the New button, but you can edit that range before or after saving the new name.

Note that range names cannot include spaces, although they can include underscores and periods.  Generally, names should begin with a letter and then contain only letters, numbers, periods, or underscores.

Names are not case-sensitive, but using a string of capitalized words, such as TaxRate or December2018Sales, makes the names easier to read and recognize.  You cannot use a range name that mimics a valid cell reference, such as Dog26.

You can edit your range names or change the ranges they refer to using the Name Manager window.

Note also that each named range has a defined scope.  Normally, the scope will default to Workbook, which means the range name can be referenced from anywhere within the workbook.  However, it is also possible to have two or more ranges with the same name on separate sheets, but within the same workbook.

For example, you might have a sales data file with separate sheets for January, February, March, etc.  Each sheet could have a cell (named range) called MonthlySales, but normally the scope of each of those names would only be the sheet containing it.

Thus, the formula =ROUND(MonthlySales,0) would give February sales, rounded to the nearest whole dollar, if the formula is on the February sheet, but March sales if on the March sheet, etc.

To avoid confusion in workbooks having multiple ranges on separate sheets with the same name or simply complicated workbooks with dozens or hundreds of named ranges, it can be helpful to include the sheet name as part of each range name.

This also makes each range name unique, so that all the names can have a Workbook scope.  For example, January_MonthlySales, February_MonthlySales, Budget_Date, Order_Date, etc.

Two cautions regarding the scope of named ranges: (1) You cannot edit the scope of a named range after it is created, and (2) you can only specify the scope of a new named range if you create it using the New button in the Name Manager window.

If you create a new range name by typing it in the Name Box, the scope will default to either Workbook (if no other range with the same name exists), or to the sheet where the name is being created.  Therefore, to create a new named range whose scope is limited to a particular sheet, use the Name Manager “New” button.

Finally, for those who write macros, range names can be easily referenced in VBA code by simply placing the range name within brackets.  For example, instead of ThisWorkbook.Sheets(1).Cells(2,3) you can simply use [SalesTotal] if that name refers to that cell.

Start using named ranges in your Excel worksheets and you will quickly being to appreciate the benefits! Enjoy!

Related posts

• Cách xóa dòng trống trong Excel

• Cách sử dụng Tính năng Nói ô trong Excel

• Cách Chèn Trang tính Excel vào Tài liệu Word

• Cách sử dụng Phân tích Điều gì xảy ra trong Excel

• Cách sửa một hàng trong Excel

• Hướng dẫn VBA nâng cao cho MS Excel

• Cách theo dõi người phụ thuộc trong Excel

• Cách sắp xếp thứ tự bảng chữ cái trong Excel

• Google Trang tính và Microsoft Excel - Sự khác biệt là gì?

• 3 cách để tách một ô trong Excel

• Cách tìm kiếm trong Excel

• Mảng VBA trong Excel là gì và cách lập trình một

• 10 mẹo và thủ thuật Excel cho năm 2019

• Cách chia sẻ tệp Excel để cộng tác dễ dàng

• Cách loại bỏ các hàng trùng lặp trong Excel

• Cách sử dụng câu lệnh If và câu lệnh If lồng nhau trong Excel

• Cách sửa lỗi # N / A trong công thức Excel như VLOOKUP

• Cách sử dụng tham chiếu tuyệt đối trong Excel

• Cách xóa đường lưới trong Excel

• Cách sắp xếp theo ngày trong Excel