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

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

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

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


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


This behaviour is probably best explained with an example.  Let’s walk through the process of building the following formula with Formula 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.

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.


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.

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:

Excel functions – this includes all functions in the Excel library

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

Table Structure References – More on this in post coming soon about improved table support.

Published Wednesday, October 19, 2005 2:59 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号