命名范围是Microsoft Excel的一个有用但通常未被充分利用的功能。命名范围可以使公式更易于理解（和调试），简化复杂电子表格的创建，并简化您的宏。
命名范围只是您为其分配名称的范围（单个单元格或单元格范围）。然后，您可以使用该名称代替公式、宏中的普通单元格引用，以及定义图形或数据验证的源。
使用范围名称（例如TaxRate）代替标准单元格引用（例如Sheet2 !$C$11）可以使电子表格更易于理解和调试/审核。
在 Excel 中使用命名范围
例如，让我们看一个简单的订单表格。 我们的文件包括一个可填写的订单表格，其中包含一个用于选择运输方式的下拉菜单，以及第二张带有运输成本表和税率的表格。
版本 1（没有命名范围）在其公式中使用普通A1 样式的(A1-style)单元格引用（如下面的公式栏中所示）。
版本 2 使用命名范围，使其公式更易于理解。命名范围还使输入公式变得更容易，因为每当您开始在公式中键入名称时， Excel都会显示一个名称列表，包括函数名称，您可以从中选择。 双击(Double-click)选择列表中的名称以将其添加到您的公式中。
从“公式(Formulas)”选项卡打开“名称管理器(Name Manager)”窗口会显示范围名称列表和它们引用的单元格范围。
但命名范围还有其他好处。在我们的示例文件中，使用Sheet1(Sheet1)上单元格B13中的下拉菜单（数据验证）选择运输方式。然后使用所选方法在Sheet2(Sheet2)上查找运费。
如果没有命名范围，则必须手动输入下拉选项，因为数据验证不允许您在不同的工作表上选择源列表。因此，所有选项都必须输入两次：一次在下拉列表中，一次在Sheet2中。此外，这两个列表必须匹配。
如果任一列表中的一个条目出现错误，则在选择错误选项时，运费公式将生成 #N/A 错误。将Sheet2(Sheet2)上的列表命名为ShippingMethods可以消除这两个问题。
例如，您可以在为下拉列表定义数据验证时引用命名范围，只需在源字段中输入=ShippingMethods。这允许您使用另一个工作表上的选项列表。
如果下拉列表引用查找中使用的实际单元格（用于运输成本公式），则下拉选项将始终与查找列表匹配，避免 #N/A 错误。
在 Excel 中创建命名区域
要创建命名范围，只需选择要命名的单元格或单元格范围，然后单击名称框( Name Box)（通常显示所选单元格地址的位置，就在公式栏(Formula Bar)的左侧），键入要使用的名称，然后按Enter。
您还可以通过单击名称管理器(Manager)窗口中的新建(New)按钮来创建命名范围。这将打开一个新名称(New Name) 窗口，您可以在其中输入新名称。
默认情况下，要命名的范围设置为单击“新建”(New)按钮时选择的任何范围，但您可以在保存新名称之前或之后编辑该范围。
请注意，范围名称不能包含空格，但可以包含下划线和句点。通常，名称应以字母开头，然后仅包含字母、数字、句点或下划线。
名称不区分大小写，但使用大写单词字符串（例如TaxRate或December2018Sales）使名称更易于阅读和识别。您不能使用模仿有效单元格引用的范围名称，例如Dog26。
您可以使用“名称管理器(Manager)”窗口编辑范围名称或更改它们引用的范围。
另请注意，每个命名范围都有定义的范围。通常，范围将默认为Workbook，这意味着可以从工作簿中的任何位置引用范围名称。但是，也可以在不同的工作表上，但在同一个工作簿中，有两个或多个同名的范围。
例如，您可能有一个销售数据文件，其中包含针对January、February、March等的单独工作表。每个工作表都可以有一个名为MonthlySales的单元格（命名范围） ，但通常每个名称的范围只会是包含的工作表它。
因此，公式=ROUND(MonthlySales,0)将给出2 月份(February)的销售额，四舍五入到最接近的整数，如果公式在2 月(February)的表格上，但3 月份(March)的销售额如果在3 月(March)的表格上，等等。
为避免在具有相同名称的不同工作表上具有多个范围的工作簿或具有数十或数百个命名范围的简单复杂工作簿混淆，将工作表名称作为每个范围名称的一部分包括在内会很有帮助。
这也使每个范围名称都是唯一的，因此所有名称都可以具有工作簿(Workbook)范围。例如，January_MonthlySales、February_MonthlySales、Budget_Date、Order_Date等。
关于命名范围范围的两个注意事项：(Two cautions regarding the scope of named ranges:) (1) 在创建命名范围后，您不能对其范围进行编辑，以及 (2) 如果使用中的“新建”(New)按钮创建新命名范围，则只能指定其范围名称管理器( Name Manager)窗口。
如果您通过在名称框中(Box)键入新的范围名称来创建新的范围名称，则范围将默认为工作簿(Workbook)（如果不存在具有相同名称的其他范围）或正在创建名称的工作表。因此，要创建一个范围仅限于特定工作表的新命名范围，请使用名称管理器的“新建”按钮。
最后，对于那些编写宏的人，只需将范围名称放在括号中，就可以在VBA代码中轻松引用范围名称。(VBA)例如，如果该名称引用该单元格，则可以简单地使用 [ SalesTotal ] 而不是(SalesTotal)ThisWorkbook.Sheets (1).Cells(2,3) 。
开始在(Start)Excel工作表中使用命名范围，您将很快体会到其中的好处！享受！
Why You Should Be Using Named Ranges in Excel
Nаmed ranges are a useful, but often underutilized, feature of Micrоsoft Excel. Named ranges can make formulas easier to understand (and debug), ѕimplify 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!
