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

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

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:
第三，我们新增了5个在用户提出的需求中具有代表性的函数，作为Excel的内置函数：

IFERROR

AVERAGEIF

AVERAGEIFS

SUMIFS

COUNTIFS

Here is more detail on each:
下面是这5个函数的介绍：

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:
我们收到关于函数的最具普遍性的需求，是用户要求简化错误值的处理过程。例如，想要截获VLOOKUP计算结果中的错误值，并将其替换为用户自定义的错误提示，就不得不采用像IF和ISERROR这样的函数组合。

=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:
如您所见，这里两次运用了VLOOKUP公式。这样做会有几个问题。首先，维护公式比较麻烦，如果你想改动公式，就不得不修改两处。其次，它影响运算速度，公式往往会被多运算一遍。IFERROR函数解决了这些问题，它可以让用户方便地截获并处理公式算出的错误值。下例，我们采用IFERROR函数处理上述的情形：

=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:
另一个用户普遍关心的需求是，想要一个条件平均函数作为SUMIF和COUNTIF的补充。相应地，我们新增了AVERAGEIF函数，对在一定范围内满足某条件的数据进行求平均值。例如，在B2:B5单元格区域中按条件求平均值，条件是A列与之相对应的单元格的值大于250000。

=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列的数值求和。

There are a number of ways to do this in Excel 2003 – for example, our user could array-enter the following formula:
在Excel2003中，有许多方法可以做到这点——比如，我们可用数组公式的方式输入下面的公式：

=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)，Excel Home 授权转载。严禁任何人以任何形式转载，违者必究。