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函数处理上述的情形：

上一篇：excel入门教程：IX 对SQL服务器分析服务的更强大支持（二） 下一篇：excel怎么做表格：Excel按笔划排序

郑重声明：本文版权归原作者所有，转载文章仅为传播更多信息之目的，如作者信息标记有误，请第一时间联系我们修改或删除，多谢。