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

2014-03-26  作者:ExcelHome  阅读:

译者:apolloh  来源http://blogs.msdn.com/excel

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)

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).

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.

Published Friday, October 21, 2005 4:38 PM by David Gainer
Filed Under: Formulas and functions

注:本文翻译自http://blogs.msdn.com/excel ,原文作者为David Gainer(a Microsoft employee),Excel Home 授权转载。严禁任何人以任何形式转载,违者必究。

Copyright 1999 - 2020 Excel Home.All Rights Reserved.
本站特聘法律顾问:徐怀玉律师 李志群律师   沪ICP备11019229号

沪公网安备 31011702000001号