对编辑公式功能的改进Part 2_Formula AutoComple_Excel 2007新知

对编辑公式功能的改进Part 2_Formula AutoComple_Excel 2007新知

译者：apolloh  来源：http://blogs.msdn.com/excel 发表于：2006年7月7日

poFormula editing improvements Part 2: Formula AutoComplete 对编辑公式功能的改进 Part 2：Formula AutoComplete

Last post I covered improvements we made to a long-time fixture of the Excel UI – the formula bar.  Today I’d like to introduce a feature that is brand new for Excel 12. The feature is called Formula AutoComplete, and it is designed to make users more efficient.  Specifically, when we designed Formula AutoComplete, we had three goals: 上次介绍，在Excel用户界面中，长期以来保持一成不变的编辑栏有了新的变化。今天，我接着来谈谈Excel 12特有的一个新功能——Formula AutoComplete。（译者注：字面意思为“公式自动完成”）设计它是为了提高公式编辑的效率。 这里需要特别地说明，在设计AutoComplete时，我们设定了三个目标：

Help users build formulas faster Help users build formulas without needing to rely on external help Accomplish goals 1 and 2 in a way that is not intrusive or annoying 帮助用户更快地创建公式 用户不必依赖外部帮助来创建公式 达成1和2的方式不妨碍到正常使用

For guidance on the user experience we also looked to applications like Excel’s own VBE and Visual Studio, and we made sure to standardize where possible.    我们采用了与Excel自带的VBE和Visual Studio相同的用户交互方式，并尽可能地同它们的标准保持一致。

How it works Formula AutoComplete consists of a drop down list of items matching the customer’s keyboard input.  Each item type in the list has its own icon to visually distinguish it.  (The icons you see in the pictures below are works in progress and not final).  公式AutoComplete是如何工作的？ AutoComplete带有一个下拉列表。它可以根据用户的输入内容来自动筛选项目。列表中的每个选项都拥有一个图标，用来识别该选项的所属分类。（本文图中所示的图标不是最终产品，只是个在产品。）

This behaviour is probably best explained with an example.  Let’s walk through the process of building the following formula with Formula AutoComplete: 要说明AutoComplete，也许举例是最好的方式。让我们通过创建下面这个公式来体验一下AutoComplete的工作过程：

=SUBTOTAL(109, data)

To start, I will do what I always do when creating a formula – type an “=” sign.  For this example, I want to insert the SUBTOTAL function, so I begin with the letter “s”.  Upon typing the letter “s”, the Formula AutoComplete drop down immediately appears and displays a list of options that start with the letter “s”.   The first option in the list (in this case the SEARCH function) is selected and displays a description in a tooltip.  At this point, I could insert this function into the cell by hitting the TAB key or double clicking on the item. 开始创建公式时，我总是先输入一个“=”号。在此例中，我想要插入一个SUBTOTAL函数，因此接着输入“s”。即刻，在单元格下方，出现一个AutoComplete下拉列表，上面显示一列以字母“s”为首的选项。列表的第一个选项已被选中，而且还带有一个功能描述的提示。 这里，我只需按一下TAB键，或是双击该选项，即可完成函数的输入。

Instead, I keep typing.  By the time I have typed “sub”, the list is filtered down to contain only the items starting with “sub” – SUBSTITUTE and SUBTOTAL.  I can navigate down to the SUBTOTAL function by using the up and down ARROW keys.   The descriptive tooltip confirms that this is the function I’m looking for. 但我没有这么做，而是接着输入。当我输到“sub”时，列表中只剩下以“sub”为首的选项—— SUBSTITUTE 和SUBTOTAL 。可用上下光标键在选项中移动选择。此例中要使用向下键将光标移到SUBTOTAL的位置。提示中的功能描述提醒我，已找了SUBTOTAL函数。

To insert the SUBTOTAL function, I simply need to press the TAB.  AutoComplete inserts the opening parenthesis in addition to the function name, and puts my cursor in the first argument of the function.  In this case, the first argument (function_num) has a specific set of values to select from.   AutoComplete makes it easy to choose one by displaying a list of the valid arguments when my cursor enters the argument.  In this case, I want the SUM option (109), so I’ll select it and hit TAB to insert.   Without auto-complete, I would have to either commit this list to memory or else consult help to get the correct value. 再按一下TAB键，我便完成了SUBTOAL函数的输入。这时AutoComplete又给出了函数的语法提示，并将光标移到其第一个参数的位置。此例中，第一个参数（function_num）有一套预设的可选参数值。AutoComplete允许我们通过上下移动光标，轻松地从有效参数值列表中选择一个参数值。在此例中，我想要SUM选项（109），因此选中了它，并按下TAB键将其插入。如果没有AutoComplete，而想要获得正确的参数值，我就不得不去记这些列表，或是寻求其他帮助。

The final argument for the SUBTOTAL function is the reference to the data I want to subtotal.  For this example, I would like to subtotal a Named Range that I have already defined and called “data”.   As before, typing “d” displays the AutoComplete list filtered to items starting with the letter “d”.  Notice that the named range, “data” shows up at the top of my list with a different icon.  This icon indicates that it is a defined name. All items of the same type will share an icon.  Finally, you will notice that the selected item “data” is displaying a tooltip reading “this is the subtotal range”.  I added this comment to the name when I created it.  All name comments are displayed in auto complete tooltips making it easy to add and view useful metadata for defined names (more soon on name improvements). Subtotal函数的最后一个参数类型是区域，用来指定需要汇总的数据所在的单元格区域。此例中，我已将要汇总的数据预先定义了名称“DATA”。正如前述，当我键入一个字母“d”时 ，AutoComplete马上会显示出一个“d”开头的下拉列表。“data”名称就显示在列表的顶部，并带有一个新图标。这个图标表示该选项的类型是“名称”。所有同类型的选项都共用相同的图标。最后，您会发现选中的名称“data”旁有一个的提示，写着“汇总区域”。这是我在定义名称时给的注释。AutoComplete中所显示的名称注释，可以用来方便地插入名称，或查看名称相关的有用信息。（更多的内容会在之后的话题——对名称的改进中谈到）

After inserting the Named Range “data” and I just need to close the parenthesis. 插入名称“Data”后，我所要做的就是关闭函数语法的提示。（可以输入“）”或Eneter来关闭——译者补充）

I hope this simple example shows how helpful AutoComplete can be for building formulas.   In addition to requiring less typing, it puts more of the formula building knowledge directly into the product giving customers the luxury browsing their options rather than memorizing them. 我希望这个简单的例子，可以展示AutoComplete在创建公式过程中的作用。除了较少地使用键盘输入外，AutoComplete还内置了用来创建公式的信息，使用户能轻松惬意地浏览各个选项，这比去记住它们要强许多。

One important point to note is that AutoComplete is meant to be a non-disruptive tool for building formulas.  Users can write formulas the existing way (by typing them all in by hand) if they choose, and AutoComplete will not stop them.  The feature will simply display a list of options matching what they type until there is nothing matching (at which point it will disappear).  Also, Formula AutoComplete can be turned on & off from Excel Options or by keyboard shortcut. 需要说明一个的重点，AutoComplete并不影响公式的正常输入。用户依然可以采用过去的方式（手工输入），AutoComplete不会妨碍到它。这个功能仅是用来筛选并显示一个同键盘输入内容相匹配的选项列表，直到没有选项可配比为止。（此时AutoComplete便不显示）。而且AutoComplete可以通过Excel Options或键盘快捷键来开启或关闭。

What does it autocomplete? 哪些事情可以AutoComplete ？

Here is a complete list of the items that will appear in the Formula AutoComplete drop down: 以下是AutoComplete下拉列表可显示的全部内容：

Excel functions – this includes all functions in the Excel library Excel函数——涵盖Excel所有内置函数。

User defined functions – Functions defined by the user either through VBA, automation add-ins, or .XLL’s show up as functions 用户自定义函数——用户通过VBA、自动化加载宏、XLL’s show up as functions等建立的自定义函数

Defined Names – All defined names are surfaced 名称——所有非隐藏的名称

Enumerated Arguments – Some enumerated arguments (like the function_num argument in the example) will have auto complete support 枚举参数——AutoComplete也支持一些枚举参数（如例子中的function_num）

Table Structure References – More on this in post coming soon about improved table support. 列表的构成区域——（更多的内容会在之后的话题——对列表的改进中谈到）