Excel Data Analysis 简明教程

Working with Range Names

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

@ Net_Present_Value = NPV (Discount_Rate, Cash_Flows)

比以下公式更有意义：

C10 = NPV (C2, C6:C8)

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

  1. 一个有意义的区域名称（例如 Cash_Flows）比区域地址（例如 C6:C8）更容易记住。

  2. 输入名称比输入单元格或区域地址出错的可能性更低。

  3. 如果您在公式中输入的名称不正确，Excel 会显示一个@ #NAME? 错误。

  4. 您可以使用已定义的名称快速移动到工作表的区域。

  5. 借助名称，您的公式将更易于理解且更易于使用。例如，公式 Net_Income = Gross_Income – Deductions 比 C40 = C20 – B18 更直观。

  6. 创建带有区域名称的公式比使用单元格或区域地址更简单。您可以使用公式自动完成功能，将单元格或区域名称复制到公式中。

在本章中，您将学习 −

  1. Syntax rules for names.

  2. 创建单元格引用名称。

  3. Creating names for constants.

  4. Managing the names.

  5. 定义名称的范围。

  6. Editing names.

  7. Filtering names.

  8. Deleting names.

  9. Applying names.

  10. 在公式中使用名称。

  11. 在工作簿中查看名称。

  12. 使用粘贴名称和粘贴列表。

  13. 使用名称进行范围交集。

  14. Copying formulas with names.

Copying Name using Formula Autocomplete

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

copying name

Range Name Syntax Rules

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

  1. 您可以使用任意字母、数字以及符号的组合 - 下划线、反斜杠和句点。不允许使用其他符号。

  2. 一个名称可以以字符、下划线或反斜杠开头。

  3. 一个名称不能以数字开头（例如 - 1stQuarter）或类似于单元格地址（例如 - QTR1）。

  4. 如果您希望使用此类名称，请在名称之前加上下划线或反斜杠（例如 - \1stQuarter、_QTR1）。

  5. 名称中不能包含空格。如果您希望区分名称中的两个单词，可以使用下划线（例如 - Cash_Flows 代替 Cash Flows）

  6. 您定义的名称不应与 Excel 内部定义的名称相冲突，例如 Print_Area, Print_Titles, Consolidate_Area, and Sheet_Title 。如果您定义相同的名称，它们将覆盖 Excel 的内部名称，并且您将不会收到任何错误消息。但是，建议不要这样做。

  7. 保持名称简洁但易于理解，尽管您可以使用最多 255 个字符

Creating Range Names

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

  1. Using the Name box.

  2. 使用 New Name 对话框。

  3. 使用 Selection 对话框。

Create a Range Name using the Name Box

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

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

Step 2 − 单击“名称”框。

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

creating range names

Create a Range Name using the New Name dialog box

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

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

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

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

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

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

creating range name using new name

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

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

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

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

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

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

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

create from selection

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

find largest value

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

create names with multiple selection

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

find total marks

Creating Names for Constants

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

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

  1. Click Define Name.

  2. 在“新建名称”对话框中，在“名称”框中输入 Savings_Bank_Interest_Rate。

  3. In Scope, select Workbook.

  4. 在“引用位置”框中，清除内容并输入 5%。

  5. Click OK.

creating names for constants

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

name manager

Managing Names

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

  1. Click the Formulas tab.

  2. 单击 Defined Names 组中的 Name Manager 。 Name Manager 对话框会出现。当前工作簿中定义的所有名称都会显示出来。

managing names

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

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

  1. 使用 New 按钮定义 New 名称。

  2. Edit a Defined Name.

  3. Delete a Defined Name.

  4. 以类别 Filter 已定义的名称。

  5. 修改已定义名称的区域以指派 Refers to 给它。

name manager options

Scope of a Name

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

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

scope of a name

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

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

scope

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

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

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

verify scope

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

Deleting Names with Error Values

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

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

将出现以下筛选选项 −

  1. Clear Filter

  2. Names Scoped to Worksheet

  3. Names Scoped to Workbook

  4. Names with Errors

  5. Names without Errors

  6. Defined Names

  7. Table Names

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

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

names with errors

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

names list

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

Editing Names

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

  1. Change the Name.

  2. 修改 Refers to 范围

  3. 在 Name 中编辑 Comment 。

Change the Name

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

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

change name

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

click name

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

edit name

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

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

Step 6 − 添加 Comment （可选）

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

scope deactivated

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

cell changed

Applying Names

考虑以下示例 −

applying names

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

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

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

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

select cell

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

apply names

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

selected cells

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

Using Names in a Formula

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

  1. 如果记得，则键入 Name ，或

  2. 先键入一两个字母，然后使用 Excel Formula Autocomplete 功能。

  3. 单击公式选项卡上的已定义名称组中的使用公式。从已定义名称的下拉列表中选择所需的名称。双击该名称。

using names in formula

  1. 使用 Paste Name 对话框。从已定义名称的下拉列表中选择粘贴名称选项。将出现粘贴名称对话框。在 Paste Names 对话框中选择 Name ，并双击它。

select name

Viewing Names in a Workbook

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

  1. 单击工作簿中要复制 Names 的空单元格。

  2. 在 Defined Names 组中单击 Use in Formula 。

  3. 从下拉列表中单击 Paste Names 。

  4. 在出现的 Paste Name 对话框中单击 Paste List 。

viewing names

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

worksheet

Using Names for Range Intersections

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

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

range intersections

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

  1. 使用 Create from Selection 创建 Names 以包含学生和考试。

  2. 您的 Names 将形如以下内容 −

range names

  1. 在 B11 中输入 Type = Kodeda_Adam Exam_1 。

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

range intersection operation

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

display marks

Copying Formulas with Names

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

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