对编辑公式功能的改进Part 3_Excel 2007新知

 Formula editing improvements Part 3: new functions

对编辑公式功能的改进 Part 3:新增函数

In addition to improving the formula editing UI in Excel 12, the team has spent some time adding to Excel’s function library.  Over the years, customers have found new ways to combine and leverage the functions in Excel to build all sorts of things, but there remain many areas where our customers would like to see need new capability. This release, we have targeted three areas in which to improve our function library – the Analysis ToolPak, SQL Server Analysis Services, and the most common requests we hear from customers.

除了改进编辑公式的界面外,我们还在Excel 12的内置函数库上花了些功夫。多年以来,用户挖掘出许多新方法,整合和发挥Excel函数的功能,创建各式各样的公式。虽然如此,用户还是期望拥有更多新的函数。此版本中,我们瞄准了三个方面来扩充Excel内置函数库,它们是——分析工具库,SQL Server Analysis Services和用户提出的最具代表性的需求。

First, we have fully integrated the Analysis ToolPak functions into the Excel function library, making these functions first-class citizens and eliminating issues relating to the fact that they had been delivered as an add-in in the past.  Users already find a great deal of value in these functions and, from Excel 12 on, they can rely on them to simply work the way the rest of the Excel function library works. This means users no longer have to run the add-in to use the functions, the functions will show up in Formula Autocomplete (see previous post), the functions will offer the same tooltips as other native Excel functions, etc.

首先,我们把分析工具库函数整合到Excel内置函数库中,使其成为“一等公民”,并取消了原来的加载宏。这些函数对用户十分有价值。从Excel 12起,它们就可以和其它Excel内置函数一样方便地使用。这意味着,用户不需要加载宏,可以直接使用这些函数。它们会出现在Formula AutoComplete功能提供的下拉列表中(见前面的帖子),且和其他内置函数一样也有相应的功能提示。

Second, we have added a new set of functions that allow users to extract information from SQL Server Analysis Services. For the benefit of readers that are not familiar with SQL Server Analysis Services, let me give you a really high-level overview.  In addition to its relational database product, SQL Server includes a feature named Analysis Services which provides business intelligence and data mining capabilities (for those interested, more information can be found here). In Excel 12, we have added a set of functions that give users the ability to retrieve SQL Server Analysis Services data directly into cells. There is a fair bit to cover in this area, so I will write a few posts on these formulas in a few weeks.

第二,我们还新增了一套函数,允许用户从SQL Server Analysis Services中获取数据。考虑到一些读者并不熟悉SQL Server Analysis Services,我先简单地概括一下。除了相关数据库产品外,SQL Server 还包括一个称为Analysis Services 的功能,提供商业智能和数据挖掘能力(有兴趣的读者可以在这里找到更多信息)。在Excel 12 中,这些新增的函数可从SQL Server Analysis Services 直接获取数据,存放到单元格里。由于涉及面较广,接下来的几周里,我会再写些帖子介绍这些函数。

Third, we’ve added five commonly requested functions to the Excel function library:







Here is more detail on each:


IFERROR(Value, value_if_error)

The most common request we hear in the area of functions is something to simplify error checking.  For example, if a user wants to catch errors in a VLOOKUP and use their own error text opposed to Excel’s error, they have to do something like this using the IF and ISERROR functions:


=IF(ISERROR(VLOOKUP("Dave", SalesTable, 3, FALSE)), " Value not found", VLOOKUP("Dave", SalesTable, 3, FALSE))

As you can see, users need to repeate the VLOOKUP formula twice.  This has a number of problems.  First, it is hard to read and hard to maintain – if you want to change a formula, you have to do it twice.  Second, it can affect performance, because formulas are quite often run twice. The IFERROR function solves these problems, enabling customers to easily trap and handle formula errors. Here is an example of how a user could use it in the same situation:


=IFERROR(VLOOKUP(“Bob”, SalesTable, 3, false), “Value not found”)

Less to author, less to maintain, faster performance.


AVERAGEIF(Range, Criteria, [Average  Range])

Another very common request is for a single function to conditionally average a range of numbers – a complement to SUMIF and COUNTIF.  Accordingly, we have added AVERAGEIF, allowing users to easily average a range based on a specific criteria.  Here is an example that returns the average of B2:B5 where the corresponding value in column A is greater than 250,000:


=AVERAGEIF(A2:A5, “>250000”, B2:B5)

SUMIFS(sum_range, criteria_range1, criteria1 [,criteria_range2, criteria2…])

COUNTIFS(criteria_range1, criteria1 [,criteria_range2, criteria2…])

AVERAGEIFS(average_range, criteria_range1, criteria1 [,criteria_range2, criteria2…])

A third very common question we hear is “how do I sum/count/average a range with multiple criteria”.  For example, if a user had the following range, how could they sum “Value” where Fruit = “Apple” and Value = “One”.  (译者注:Value = “One”应该改为Number= “One”)

第三个非常有代表性的问题是,“如何对一个范围的数据进行多条件的汇总/计数/平均”。 举例,如图所示,怎样对满足Fruit = “Apple” 且 Number= “One”的Value列的数值求和。

对编辑公式功能的改进Part 3_Excel 2007新知插图

There are a number of ways to do this in Excel 2003 – for example, our user could array-enter the following formula:


=SUM(IF(C2:C17="Apple", IF(D2:D17="One", B2:B17, 0), 0))

But the formula is hard to set up correctly, many users do not know about array formulas, and it is harder to read.


In Excel 12, this task will be much simpler using the SUMIFS formula:

Excel 12 中我们仅用SUMIFS一个函数就可方便地做到:

=SUMIFS(B3:B18, C3:C18,"=Apple", D3:D18, "=One")

The formula is much simpler to write, easier to read, and doesn’t require array entry. 


COUNTIFS and AVERAGEIFS, also new to Excel 12, work the same way with the same benefits.

COUNTIFS 和 AVERAGEIFS 也是Excel 12 的新函数,而且也有着一样的好处。

That sums up our new functions (more detail to come on the Analysis Server functions later though).  Next up… Formula Editing Part 4: Defined Names.

以上是Excel 12所有的新函数。(当然,更多的内容在之后的Analysis Server函数中还会涉及)下期预告: 编辑公式 Part 4:定义名称。

Published Thursday, October 20, 2005 2:28 PM by David Gainer

Filed Under: Formulas and functions


注:本文翻译自http://blogs.msdn.com/excel,原文作者为David Gainer(a Microsoft employee),

 收藏 (0) 打赏




分享到: 生成海报