Excel - 如何创建一个Named Range (Name manager)

Create a named range from selected cells in a worksheet

You can quickly create a named range by using a selection of cells in the worksheet.

Note: Named ranges that are created from selecting cells have a workbook-level scope.

1. Select the range you want to name, including the row or column labels.

2. Click Formulas > Create from Selection.

3. In the Create Names from Selection dialog box, select the checkbox (es) depending on the location of your row/column header. If you have only a header row at the top of the table, then just select Top row. Suppose you have a top row and left column header, then select Top row and Left column options, and so on.

4. Click OK.

Excel names the cells based on the labels in the range you designated.

Name a cell

1, Select a cell.

2, In the right-click context menu, for the Formulas tab, click Define Name submenu, type a name. E.g. A1

3, Press Enter.

To reference this value in another table, type th equal sign (=) and the Name, then select Enter.

Manage names in your workbook with Name Manager

On the ribbon, go to Formulas > Name Manager. You can then create, edit, delete, and find all the names used in the workbook.

Define and use names in formulas

By using names, you can make your formulas much easier to understand and maintain. You can define a name for a cell range, function, constant, or table. Once you adopt the practice of using names in your workbook, you can easily update, audit, and manage these names.

Use names in formulas

1. Select a cell and enter a formula.

2. Place the cursor where you want to use the name in that formula.

3. Type the first letter of the name, and select the name from the list that appears.

   Or, select Formulas > Use in Formula and select the name you want to use.

4. Press Enter.

How to Reference a Named Range in VBA

To reference a named range in VBA, you simply need to use the Range() function with the named range in double quotes.

For example, you could create the following macro to assign a value of “Team” to every value in the named range called teams:

Sub ModifyNamedRange()

     Range("teams").Value = "Team"

End Sub

Or we could even use the following macro to make each cell in the named range teams to have a bold font and a green background color:

Sub ModifyNamedRange()

     Range("teams").Interior.Color = vbGreen

     Range("teams").Font.Bold = True

End Sub

Each cell in the named range teams now has a bold font and a green background color.

可以使用OFFSET函数引用NameRange的值，比如：

=OFFSET( RangeName,0,1)

就会把此NameRage的下一列的值都显示出来。

除了直接创建Named Range，还可以创建一个table，Ctrl + T，之后也能在Name Manager里管理并使用，还可以自动扩展数据。

注意：

选中一列创建一个Named Range，如果选中多列，会是每列一个Named Range。而不是一个二维表格是一个Named Range。

参考：

Create a named range from selected cells in a worksheet - Microsoft Support

VBA: How to Reference a Named Range