Excel2007新知：对编辑公式功能的改进Part 4_名称

polFormula building improvements Part 4: Defined Names

对编辑公式功能的改进 Part 4：名称

Defined names are a very useful tool for authoring formulas. Defined names allow users to name cell ranges, formulas, and values and refer to those names in their formulas. Used in formulas, defined names make formulas easier to read and more robust.  Additionally, when writing formulas, names are less likely to get mis-typed than cell references, and they are easier to remember than cell references (“Tax_Rate” as opposed to “G36”). In this article, I would like to discuss some of the work we’ve done to defined names in Excel 12 – specifically, how we have added new management and creation UI, and how we have added comments to names.

在公式书写时，名称是一个很实用的工具。用户可将单元格引用、公式和常量定义成名称，并在创建公式时引用它们。应用名称可使公式变得比较容易理解和维护。此外，相对引用单元格区域而言，在写公式的时候使用名称比较不会出错，而且方便记忆（例如，用“Tax_Rate（税率）” 而不用“G36”）。本文讨论名称的改进——重点介绍新增的创建和管理名称的界面以及如何给名称添加注释。

The new Manage Names Dialog

新的名称管理对话框

Probably the most common piece of feedback we receive about defined names is that the user interface we provide to manage names is inadequate.  When we visit customers on site, we often see workbooks with dozens or hundreds and even thousands of defined names, which makes tasks like deleting multiple names, renaming names, and finding broken ones challenging. Enter the new Manage Names dialog, which is designed specifically for viewing and managing the defined names in a workbook.

最常见的关于名称的抱怨可能是名称管理界面的缺陷带来的。我们拜访客户时，常会见到客户的工作簿的名称达到成百上千之多。在执行删除名称（多个），重命名和查找错误结果值名称等任务时，这会带来许多麻烦。新的名称管理对话框，正是专门设计用来查看和管理工作簿中的名称的。

The entry point to the Manage Names dialog is in the centre of the new Formulas tab.  Here is a shot of the Formulas tab in current builds - you can see a big button titled "Name Manager".

名称管理对话框功能的按钮设在新的格式菜单中。目前版本中，只要点击“格式”标签，就可以看到一个叫“Name Manager (名称管理)”的大按钮。

When you click on the Name Manager button, you will see the Manage Names dialog.

再点击该按钮，你会看到一个名称管理对话框。

Using this dialog, you can:

这个对话框用来：

View existing Defined Names

查看已有的名称

• See the name’s reference (“Refers to” control), the name’s scope (“Scope” Column), and the name’s value (“Value” Column – note, this displays error values as well)
• 查看名称的引用范围（“Refers to” 控件）、适用范围（“Scope” 栏）和结果值（“Value” 栏——注：错误结果值也会显示出来）
• Confirm whether it is referenced in the grid or not (the “In Use” column)
• 确认该名称是否已在表格中使用（“In Use”栏）

Create New Names
创建名称

• Easily define the scope of a name in the New Name dialog (dialog discussed below)
• 在新的名称框对话框中，可以很容易地指定名称的适用范围（在之后的对话框的讨论中）
• Easily set the name reference
• 方便地设置名称引用范围

Edit existing names
编辑已有的名称

• You can now rename a name without having to redefine it from scratch
• 名称可以重命名，不必再为了改名字而重新去定义一个名称。
• You can quickly modify scope from the Edit Name dialog  (dialog discussed below)
• 可以很快地在编辑名称对话框中修改名称的适用范围（在之后的对话框的讨论中）

Delete Names quickly
快速删除名称

• Select and delete multiple names at once
• 一次性选择和删除多个名称

Sort the Name list
名称排序

• You can sort the name list by clicking on the column headers
• 单击栏标题可以对名称进行排序

Resize the Manage Names dialog
• 调整名称对话框的大小

• Make the refers-to box as wide as you need, so the number of names you can see is limited only by your monitor size
• 根据需要调整refers-to box（引用范围文本框）的宽度。这样，名称的可见程度仅仅取决于桌面窗口的大小

Filter the Name List
名称筛选

• A powerful filter drop down allows you to filter large name lists based on a number of common criteria including scope, in use, and if the name returns an error
• 强大的过滤器可以遵循几个常用的规则来对大量名称进行筛选。规则包括适用范围、是否在用和是否返回错误值等。
• You can set multiple filters by simply selecting whatever filters they want … for example, you can filter to see all defined names that are not in use an that return errors with two mouse clicks
• 可以通过简单的选择，任意设置多重的过滤器。例如，只需点击两次鼠标，就可以筛选出不在使用且返回错误值的所有名称。

One thing we would be interested in hearing is whether hidden names should be surfaced in this dialog.  The current design allows users to show hidden names using a control on the filter drop-down, but they are not displayed by default.  Our reasoning for this is that customers tell us hidden names cause many problems for users and generate helpdesk issues. At the same time, some solution providers use them as variables with the knowledge that you can't see them in the UI.  Our current design would allow savvy users to find the hidden names without writing code.  Note, there is a workaround for solution developers, which is to use very hidden names which can be created by using the hidden namespace in XLM (i.e. SET.NAME)

我们假想有用户需要将隐藏的名称也显示在对话框中。目前设计允许通过选择过滤器上的选项来显示隐藏名称，默认设置为不显示。之所以这么做，是因为用户反映隐藏名称会导致许多问题，并希望得到帮助。同时，一些方案提供商使用隐藏名称作为变量，但它们在用户界面看不到。目前的设计允许高级用户不用写代码就能查到隐藏名称。注：对方案开发者来说，有个变通的办法——可利用XLM中的隐藏namespace达到真正隐藏名称的目的。（例如，SET.NAME）

The New Name/Edit Name Dialog

新的名称创建/编辑对话框

While we were improving name management, we set another goal to simplify the experience of creating a name. To do this, we created a dialog that surfaces the UI needed to define a new name or edit an existing name (the title of the dialog changes between New Name and Edit Name depending on the context of how it was launched).为此， Of note is the Scope drop down which allows the user to easily set the scope of their name to a specific sheet or the entire workbook (no more secret knock needed).

对于名称管理的改进方面，我们还设定了另一个目标——简化创建名称的过程。我们新增了一个对话框作为用户定义和编辑名称的界面。（对话框的标题会随着新增或是编辑功能的选择而相应地发生变化）值得一提的是，可以通过适用范围（Scope）的下拉列表选择一个特定的工作表或整个工作簿，来设置名称的适用范围。

For mouse users, we’ve made it easier to get to the new name UI by adding a right-click menu option that will launch the dialog with the selected range in its refers-to box.

鼠标用户可以方便地选择一个范围后，通过右键快捷菜单打开创建名称对话框，refers-to box（引用范围文本框）会自动带入这个引用范围。

Other entry points exist on the Ribbon and Manage names dialog.
Excel 12 的新工具栏（Ribbon）和名称管理对话框中，也有打开新建/编辑名称对话框的地方。

Name Comments
Finally, I’d like to talk about an enhancement to the Defined Name object itself – we have added a name comment property. The comment property allows the user to document what a name refers to, what it should and should not be used for, etc. This field can be edited from both the UI (New/edit name dialogs) and the OM and is surfaced as the name’s tooltip in formula auto complete as well as in the Manage Names dialog.

最后，我想说一个名称对象的增强属性——名称注释。名称注释可以用来阐述这个名称指的是什么、可以做些什么或不可以做些什么等等。在用户界面（新增/编辑名称对话框）和OM中都可以编辑注释，它将出现在Formula AutoComplete中名称选项的提示和名称管理对话框中。

Some Interesting uses of this field might include
名称注释可能包括如下的一些内容：

• Detailed description of a Names purpose
• 名称用途的详细描述
• Name auditing (track changes, data refresh dates, etc)
• 名称审核（修改记录、数据更新日期，等等）
• General notes
一般性的注释
• Another thing we would be interested in hearing is how would you use the name comment feature.
• 希望您能喜欢名称注释这个功能。

That wraps up "Formula Editing Improvements Week"; I hope you found this interesting. Talk to you next week.
本周话题——“对编辑公式的改进”已近尾声。我希望您能对此感到兴趣。下周的事我们下周再说。