Excel Data Analysis 简明教程

Working with Range Names

在进行数据分析时，如果引用的是名称，而不是单元格引用（无论是单个单元格还是一系列单元格），则引用不同数据将更有意义和更容易。例如，如果要根据贴现率和一系列现金流来计算净现值，则公式：

While doing Data Analysis, referring to various data will be more meaningful and easy if the reference is by Names rather than cell references – either a single cell or a range of cells. For example, if you are calculating Net Present Value based on a Discount Rate and a series of Cash Flows, the formula

@ Net_Present_Value = NPV (Discount_Rate, Cash_Flows)

Net_Present_Value = NPV (Discount_Rate, Cash_Flows)

比以下公式更有意义：

is more meaningful than

C10 = NPV (C2, C6:C8)

借助 Excel，您可为数据中的各个部分创建和使用有意义的名称。使用区域名称的优势包括：

With Excel, you can create and use meaningful names to various parts of your data. The advantages of using range names include −

A meaningful Range name (such as Cash_Flows) is much easier to remember than a Range address (such as C6:C8).
Entering a name is less error prone than entering a cell or range address.
If you type a name incorrectly in a formula, Excel will display a #NAME? error.
You can quickly move to areas of your worksheet by using the defined names.
With Names, your formulas will be more understandable and easier to use. For example, a formula Net_Income = Gross_Income – Deductions is more intuitive than C40 = C20 – B18.
Creating formulas with range names is easier than with cell or range addresses. You can copy a cell or range name into a formula by using formula Autocomplete.

在本章中，您将学习 −

In this chapter, you will learn −

Syntax rules for names.
Creating names for cell references.
Creating names for constants.
Managing the names.
Scope of your defined names.
Editing names.
Filtering names.
Deleting names.
Applying names.
Using names in a formula.
Viewing names in a workbook.
Using paste names and paste list.
Using names for range intersections.
Copying formulas with names.

Copying Name using Formula Autocomplete

在公式中键入名称的第一个字母。将出现一个下拉框，其中包含函数名称和范围名称。选择需要的名称。它将复制到您的公式中。

Type the first letter of the name in the formula. A drop-down box appears with function names and range names. Select the required name. It is copied into your formula.

Range Name Syntax Rules

Excel 的名称遵循以下语法规则 −

Excel has the following syntax rules for names −

You can use any combination of letters, numbers and the symbols - underscores, backslashes, and periods. Other symbols are not allowed.
A name can begin with a character, underscore or backslash.
A name cannot begin with a number (example - 1stQuarter) or resemble a cell address (example - QTR1).
If you prefer to use such names, precede the name with an underscore or a backslash (example - \1stQuarter, _QTR1).
Names cannot contain spaces. If you want to distinguish two words in a name, you can use underscore (example- Cash_Flows instead of Cash Flows)
Your defined names should not clash with Excel’s internally defined names, such as Print_Area, Print_Titles, Consolidate_Area, and Sheet_Title. If you define the same names, they will override the Excel’s internal names and you will not get any error message. However, it is advised not to do so.
Keep the names short but understandable, though you can use up to 255 characters

Creating Range Names

您可以通过两种方式创建范围名称 −

You can create Range Names in two ways −

Using the Name box.
Using the New Name dialog box.
Using the Selection dialog box.

Create a Range Name using the Name Box

要使用公式栏左侧的 Name 框创建范围名称，这是最快的方法。请按照下面给出的步骤操作−

To create a Range name, using the Name box that is to the left of formula bar is the fastest way. Follow the steps given below −

Step 1 − 选择要定义名称的范围。

Step 1 − Select the range for which you want to define a Name.

Step 2 − 单击“名称”框。

Step 2 − Click on the Name box.

Step 3 − 键入名称并按 Enter 以创建名称。

Step 3 − Type the name and press Enter to create the Name.

Create a Range Name using the New Name dialog box

还可以使用“公式”选项卡中的“新建名称”对话框创建范围名称。

You can also create Range Names using the New Name dialog box from Formulas tab.

Step 1 − 选择要定义名称的范围。

Step 1 − Select the range for which you want to define a name.

Step 2 − 单击“公式”选项卡。

Step 2 − Click the Formulas tab.

Step 3 − 在“已定义名称”组中单击“定义名称”。显示 New Name 对话框。

Step 3 − Click Define Name in the Defined Names group. The New Name dialog box appears.

Step 4 − 在“名称”旁边的框中键入名称

Step 4 − Type the name in the box next to Name

Step 5 − 检查“引用”框中选择并显示的范围是否正确。单击确定。

Step 5 − Check that the range that is selected and displayed in the Refers box is correct. Click OK.

Create a Range Name using the Create Names from Selection dialog box

如果范围内相邻的文本值，则还可以使用“公式”选项卡的“选择”对话框中的 Create Names 创建范围名称。

You can also create Range names using the Create Names from the Selection dialog box from Formulas tab, when you have Text values that are adjacent to your range.

Step 1 − 沿着包含名称的行/列选择要定义名称的范围。

Step 1 − Select the range for which you want to define a name along with the row / column that contains the name.

Step 2 − 单击“公式”选项卡。

Step 2 − Click the Formulas tab.

Step 3 − 在“已定义名称”组中单击 Create from Selection 。显示 Create Names from Selection 对话框。

Step 3 − Click Create from Selection in the Defined Names group. The Create Names from Selection dialog box appears.

Step 4 − 选择“顶部行”，因为文本显示在选择内容的顶部行中。

Step 4 − Select top row as the Text appears in the top row of the selection.

Step 5 − 检查“引用”旁边框中选择并显示的范围是否正确。单击“确定”。

Step 5 − Check the range that got selected and displayed in the box next to Refers to be correct. Click OK.

现在，您可以使用 = Sum (学生姓名) 查找范围中的最大值，如下所示−

Now, you can find the largest value in the range with =Sum(Student Name), as shown below −

您还可以创建具有多个选择的名称。在下面的示例中，您可以使用学生的姓名来命名每个学生的成绩行。

You can create names with multiple selection also. In the example given below, you can name the row of marks of each student with the student’s name.

现在，您可以使用 = Sum (学生姓名) 查找每个学生的总分，如下所示。

Now, you can find the total marks for each student with =Sum (student name), as shown below.

Creating Names for Constants

假设您有一个常量将在整个工作簿中使用。您可以直接为它指定一个名称，而不必将其放在单元格中。

Suppose you have a constant that will be used throughout your workbook. You can assign a name to it directly, without placing it in a cell.

在以下示例中，Savings Bank Interest Rate 被设置为 5%。

In the example below, Savings Bank Interest Rate is set to 5%.

Click Define Name.
In the New Name dialog box, type Savings_Bank_Interest_Rate in the Name box.
In Scope, select Workbook.
In Refers to box, clear the contents and type 5%.
Click OK.

名称 Savings_Bank_Interest_Rate 被设置为常量 5%。你可以在名称管理器中验证这一点。你可以看到该值被设置为 0.05，并且 Refers to = 0.05 被置于其中。

The Name Savings_Bank_Interest_Rate is set to a constant 5%. You can verify this in Name Manager. You can see that the value is set to 0.05 and in the Refers to =0.05 is placed.

Managing Names

Excel 工作簿可以包含任意数量的已命名单元格和区域。你可以使用名称管理器管理这些名称。

An Excel Workbook can have any number of named cells and ranges. You can manage these names with the Name Manager.

Click the Formulas tab.
Click Name Manager in the Defined Names group. The Name Manager dialog box appears. All the names defined in the current workbook are displayed.

Names 列表会显示与已定义 Values, Cell Reference （包括工作表名称）、 Scope 和 Comment 。

The List of Names are displayed with the defined Values, Cell Reference (including Sheet Name), Scope and Comment.

名称管理器具有以下选项：

The Name Manager has the options to −

Define a New Name with the New Button.
Edit a Defined Name.
Delete a Defined Name.
Filter the Defined Names by Category.
Modify the Range of a Defined Name that it Refers to.

Scope of a Name

默认情况下，名称的 Scope 是工作簿。你可以在 Name Manager 中的 Scope 列下的名称列表中找到已定义名称的 Scope 。

The Scope of a name by default is the workbook. You can find the Scope of a defined names from the list of names under the Scope column in the Name Manager.

在使用 New Name 对话框定义名称时，你可以定义 New Name 的 Scope 。例如，你在定义 Interest_Rate 名称。然后你可以看到 New Name Interest_Rate 的 Scope 是 Workbook 。

You can define the Scope of a New Name when you define the name using New Name dialog box. For example, you are defining the name Interest_Rate. Then you can see that the Scope of the New Name Interest_Rate is the Workbook.

假设你只想将这种利率的 Scope 限制在该 Worksheet 内。

Suppose you want the Scope of this interest rate restricted to this Worksheet only.

Step 1 - 单击范围框中的向下箭头。可用的范围选项会出现在下拉列表中。

Step 1 − Click the down-arrow in the Scope Box. The available Scope options appear in the drop-down list.

范围选项包括 Workbook 和工作簿中的工作表名称。

The Scope options include Workbook, and the sheet names in the workbook.

Step 2 - 单击当前工作表名称（在本例中为 NPV），然后单击确定。你可以在工作表选项卡中定义/查找工作表名称。

Step 2 − Click the current worksheet name, in this case NPV and click OK. You can define / find the sheet name in the worksheet tab.

Step 3 - 若要验证范围是否是工作表，请单击 Name Manager 。在范围列中，你会在 Interest_Rate 的后面找到 NPV。这意味着，你只能在 NPV 工作表中使用名称 Interest_Rate，而不能在其他工作表中使用。

Step 3 − To verify that Scope is worksheet, click Name Manager. In the Scope column, you will find NPV for Interest_Rate. This means you can use the Name Interest_Rate only in the Worksheet NPV, but not in the other Worksheets.

Note - 一旦定义了名称的范围，以后就不能再对其进行修改。

Note − Once you define the Scope of a Name, it cannot be modified later.

Deleting Names with Error Values

有时，出于各种原因，名称定义可能出错。你可以按如下方法删除此类名称 −

Sometimes, it may so happen that Name definition may have errors for various reasons. You can delete such names as follows −

Step 1 − 在 Name Manager 对话框中单击 Filter 。

Step 1 − Click Filter in the Name Manager dialog box.

将出现以下筛选选项 −

The following filtering options appear −

Clear Filter
Names Scoped to Worksheet
Names Scoped to Workbook
Names with Errors
Names without Errors
Defined Names
Table Names

你可以通过选择其中一个或多个选项，把 Filter 应用于 defined Names 。

You can apply Filter to the defined Names by selecting one or more of these options.

Step 2 − 选择 Names with Errors 。将显示包含错误值的名称。

Step 2 − Select Names with Errors. Names that contain error values will be displayed.

Step 3 − 从 Names 的获得的列表中，选择你想要删除的名称，然后单击 Delete 。

Step 3 − From the obtained list of Names, select the ones you want to delete and click Delete.

你将收到一条消息，确认删除。单击确定。

You will get a message, confirming delete. Click OK.

Editing Names

你可以在 Name Manager 对话框中使用 Edit 选项来 −

You can use the Edit option in the Name Manager dialog box to −

Change the Name.
Modify the Refers to range
Edit the Comment in a Name.

Change the Name

Step 1 − 单击包含函数 Large 的单元格。

Step 1 − Click the cell containing the function Large.

可以看到，在数组中添加了另外两个值，但由于它们不属于 Array1，因此未包括在函数中。

You can see, two more values are added in the array, but are not included in the function as they are not part of Array1.

Step 2 − 在 Name Manager 对话框中单击想要编辑的 Name 。在本例中，为 Array1 。

Step 2 − Click the Name you want to edit in the Name Manager dialog box. In this case, Array1.

Step 3 − 单击 Edit 。将出现 Edit Name 对话框。

Step 3 − Click Edit. The Edit Name dialog box appears.

Step 4 − 通过在 Name Box 中键入新的名称，来更改 Name 。

Step 4 − Change the Name by typing the new name that you want in the Name Box.

Step 5 − 单击 Refers to 框右边的 Range 按钮，并包括新的单元格引用。

Step 5 − Click the Range button to the right of Refers to Box and include the new cell references.

Step 6 − 添加 Comment （可选）

Step 6 − Add a Comment (Optional)

请注意， Scope 处于非激活状态，因此无法更改。

Notice that Scope is deactive and hence cannot be changed.

单击确定。你将观察到所做的更改。

Click OK. You will observe the changes made.

Applying Names

考虑以下示例 −

Consider the following example −

正如您所观察到的，在 PMT 函数中未定义和使用名称。如果您将此函数放在工作表的其他位置，您还需要记住参数值的具体位置。您知道使用名称是更好的选择。

As you observe, names are not defined and used in PMT function. If you place this function somewhere else in the worksheet, you also need to remember where exactly the parameter values are. You know that using names is a better option.

在这种情况下，该函数已经使用没有名称的单元格引用进行了定义。您仍可以定义名称并应用它们。

In this case, the function is already defined with cell references that do not have names. You can still define names and apply them.

Step 1 − 使用 Create from Selection ，定义名称。

Step 1 − Using Create from Selection, define the names.

Step 2 − 选择包含公式的单元格。在 Formulas 选项卡上的 Defined Names 组中，单击 Define Name 旁边的。从下拉列表中，单击 Apply Names 。

Step 2 − Select the cell containing the formula. Click next to Define Name in the Defined Names group on the Formulas tab. From the drop-down list, click Apply Names.

Step 3 − 将出现 Apply Names 对话框。选择要 Apply 的 Names ，然后单击确定。

Step 3 − The Apply Names dialog box appears. Select the Names that you want to Apply and click OK.

选定的名称将应用到选定的单元格中。

The selected names will be applied to the selected cells.

您还可以通过选择工作表并重复上述步骤，将 Apply Names 应用到整个工作表。

You can also Apply Names to an entire worksheet, by selecting the worksheet and repeating the above steps.

Using Names in a Formula

您可以在 Formula 中的 Name 中使用以下方法：

You can use a Name in a Formula in the following ways −

Typing the Name if you remember it, or
Typing first one or two letters and using the Excel Formula Autocomplete feature.
Clicking Use in Formula in the Defined Names group on the Formulas tab. Select the required Name from the drop-down list of defined names. Double-click on that name.

Using the Paste Name dialog box. Select the Paste Names option from the drop-down list of defined names. The Paste Name dialog box appears. Select the Name in the Paste Names dialog box and double-click it.

Viewing Names in a Workbook

您可以获取工作簿中的所有 Names 以及它们的 References 和 Save 它们或 Print 它们。

You can get all the Names in your workbook along with their References and Save them or Print them.

Click an empty Cell where you want to copy the Names in your workbook.
Click Use in Formula in the Defined Names group.
Click Paste Names from the drop-down list.
Click Paste List in the Paste Name dialog box that appears.

名称及其相应引用的列表将按以下给定的屏幕截图所示，复制到工作表上的指定位置 −

The list of names and their corresponding references are copied at the specified location on your worksheet as shown in the screen shot given below −

Using Names for Range Intersections

Range Intersections 是那些具有两个公共区域的单个单元格。

Range Intersections are those individual cells that have two Ranges in common.

例如，在下方的给定数据中，B6:F6 范围和 C3:C8 范围含有公共单元格 C6，它代表了学生 Kodeda，Adam 在考试 1 中的分数。

For example, in the data given below, the Range B6:F6 and the Range C3:C8 have Cell C6 in common, which actually represents the marks scored by the student Kodeda, Adam in Exam 1.

可以使用 Range Names 获得更有意义的结果。

You can make this more meaningful with the Range Names.

Create Names with Create from Selection for both Students and Exams.
Your Names will look as follows −

Type =Kodeda_Adam Exam_1 in B11.

此处，您正在使用范围交集运算，两个范围之间应留有空格。

Here, you are using the Range Intersection operation, space between the two ranges.

这将显示单元格 C6 中给出的 Kodeda，Adam 在考试 1 中的分数。

This will display marks of Kodeda, Adam in Exam 1, that are given in Cell C6.

Copying Formulas with Names

您可以在同一个工作表中通过 Copy*and *Paste 复制带有名称的公式。

You can copy a formula with names by Copy*and *Paste within the same worksheet.

还可以通过 copy 和 paste 将带有名称的公式复制到其他工作表，前提是 formula 中的所有 names 都以 workbook 作为 Scope 。否则，您会收到一个 #VALUE 错误。

You can also copy a formula with names to a different worksheet by copy and paste, provided all the names in the formula have workbook as Scope. Otherwise, you will get a #VALUE error.