【摘要】Excel提供了两个功能强大的工作表功能，它们可以从任何类型的工作表数据库中仅返回所需的数据。导出到Excel是BI应用程序中最常见的第三个按

Excel提供了两个功能强大的工作表功能，它们可以从任何类型的工作表数据库中仅返回所需的数据。

“导出到Excel是BI应用程序中最常见的第三个按钮，单击“确定”和“取消”之后。” —

Rob Collie，Microsoft PowerPivot背后的创始人之一，现在是PowerPivotPro.com。

Rob的开玩笑是否正确（他说这很可能是正确的）说明了一个持续的挑战：Excel用户在Excel中组织和报告业务数据的最佳方法是什么？

我的文章“ 介绍Excel的三种电子表格数据库 ”介绍了将Export-to-Excel，CSV，数据透视表和其他此类数据存储为Excel数据库的三种通用方法。在“ 如何将数据透视表设置为电子表格数据库”中， 我展示了如何使用数据透视表进行操作。

现在该采取下一步了：将Excel Tables和其他Excel数据库中的数据返回到报表和分析中。

您将使用两个关键函数从Excel表和其他数据库返回值：SUMIFS和SUMPRODUCT。

SUMIFS工作表功能

Excel具有 SUMIF函数 已有很长时间了。但是SUMIF有一个严重的问题：它只能基于一个条件返回SUM。因此，在Excel 2007中，Microsoft引入了SUMIFS函数，该函数可以使用任何数量的条件。

这些函数以不同的方式排列其参数：

= SUMIF（条件范围，条件，和范围）

= SUMIFS（总和范围，标准范围，标准...）

该SUMIFS功能可以有许多的设置要criteria_ranges和标准。（可能会有上限，但是如果有上限，我怀疑您是否会达到上限。）

为避免混淆，即使您仅需要使用一个条件，我也建议您完全停止使用SUMIF函数。这样，您将习惯使用SUMIFS，并且将始终知道函数中参数的正确顺序。

SUMPRODUCT工作表功能

正式地， SUMPRODUCT函数具有以下参数：

= SUMPRODUCT（array1，array2，array3，...）

但是，您可能会发现使用它更容易：

= SUMPRODUCT（array1 * array2 * array3）

例如，如果您要查找数据中所有黑帽的总价值，则可以使用如下公式：

= SUMPRODUCT（数量*（产品=“帽子”）*（颜色=“黑色”））

（注意：如果将带引号的公式从此博客文章复制到工作表中，则会出现错误，因为HTML的引号通常与Excel使用不同的字符。因此，在Excel中，您需要替换引号您的公式（带有标准引号）。

在公式中，“金额”是产品和产品颜色表中的值列，“产品”是产品（包括帽子）列。公式的（Products =“ Hats”）部分首先解析为TRUE列，在某些情况下，乘积在某些单元格中等于大写字母；在FALSE中，乘积在其他单元格中不等于大写字母。

然后，当您将第二个数组乘以金额列时，TRUE和FALSE值切换为1（一个）和0（零）值。因此，相乘的结果是，您有一个值数组，其中乘积等于帽子，而值零则等于乘积不等于帽子。

接下来，当您乘以（Colors =“ Black”）数组时，最终数组中唯一的非零值是Amounts列中的原始值非零，而Product是一顶帽子，并且颜色为黑色。

最后，SUMPRODUCT函数添加该结果数组，为您提供所有黑帽的总值。

SUMIFS vs SUMPRODUCT，您应该使用哪个？

如果两个函数都可以在公式中使用，则建议您使用SUMIFS，这主要是因为它倾向于更快地进行计算。

但是使用SUMIFS的另一个原因是：它 在其标准值中接受 通配符。例如，条件“ h *”接受以字母“ h”开头的所有项目。（SUMIFS在其标准中忽略大小写。）

但是，SUMPRODUCT具有显着的好处：它可以使用计算。

例如，假设您有一个按日期划分的销售表，并且想要查找星期三的平均销售量。您不能使用SUMIFS进行此计算，因为您的表没有指定星期几的列。但是您可以像这样使用SUMPRODUCT：

= SUMPRODUCT（金额*（WEEKDAY（DateTime）= 4））

/ SUMPRODUCT（（WEEKDAY（DateTime）= 4）* 1）

（尽管我将此公式分为两行，但实际上您会在一行中输入它。）

公式的第一行返回星期三所有销售的总额。

第二行返回找到的星期三数。它是通过生成一列TRUE和FALSE值来实现的，当我们乘以1时，这些值将转换为1和0。（加0会做同样的事情。）然后，第二行通过将所有列中的那些。

最后，该公式将第一个结果（星期三的总销售额）除以第二个结果（星期三的销售额），得出星期三的平均销售额。

请注意，某些功能不能在SUMPRODUCT函数中使用，但可以使用其中的大多数功能。

SUMIFS和SUMPRODUCT示例

让我们从这个名为Sales的Excel表中返回数据。

对于前几个示例，我设置了四个单元，分别命名为Product，Color，StartDate和EndDate。

另外，如上所述，我会将大多数公式包装到几行中，以便它们适合页面。但是，您当然要在一行中输入它们。

假设“产品”单元格包含文本“领带”，而“颜色”单元格包含“黑色”。这两个公式都将返回9，即所有“黑色领带”的总数：

= SUMIFS（销售[商品]，销售[产品]，产品，销售[颜色]，颜色）

= SUMPRODUCT（Sales [Amts] *（Sales [Products] = Product）*（Sales [Colors == Color））

假设StartDate单元格包含日期2012-01-01，EndDate单元格包含日期2012-02-01。这两个公式都将返回24，即一月份所有销售额的总和：

= SUMIFS（Sales [Amts]，Sales [DateTimes]，”> =”＆StartDate，Sales [DateTimes]，” <”＆EndDate）

= SUMPRODUCT（Sales [Amts] *（Sales [DateTimes]> = StartDate）*（Sales [DateTimes] <EndDate））

现在，让我们结合这些公式集以返回一月份售出的所有黑色领带的总数：

= SUMIFS（Sales [Amts]，Sales [Products]，

Product，Sales [Colors]，Color，Sales [DateTimes]，“> =”＆StartDate，

Sales [DateTimes]，“ <”＆EndDate）

= SUMPRODUCT（Sales [Amts] *（Sales [Products] = Product）*

（Sales [Colors = Color）*（Sales [DateTimes]> = StartDate）*

（Sales [DateTimes]<EndDate））

（我再次包装了这些公式，但实际上您会在长行中输入每个公式。）

假设由于某种原因，我们希望所有名称以“ ts”结尾的产品的总数。

使用SUMIFS，我们可以使用通配符。在这里，名为Product2的单元格包含文本“ * ts”（即，我们有一个星号*，后跟“ ts”）：

= SUMIFS（Sales [Amts]，Sales [Products]，Product2）

使用SUMPRODUCT，我们可以使用字符串函数返回相同的结果。因此，假设名为Product3的单元格包含文本“ ts”（也就是说，我们没有星号，只有“ ts”）：

= SUMPRODUCT（Sales [Amts] *（RIGHT（Sales [Products]，2）= Product3））

或者，如果我们要使用通配符，则可以使用：

= SUMPRODUCT（Sales [Amts] *（NOT（ISERROR（SEARCH（Product2，Sales [Products]，2））））））

Excel的SEARCH函数可以使用通配符。但是如果什么也找不到，它将返回一个错误值。因此，如果找到了我们的搜索字符串，我们将使用NOT（ISERROR（SEARCH（[whatever]）））返回TRUE，否则返回FALSE。

您可以使用SUMIFS和SUMPRODUCT从Excel表返回许多类型的结果。但是，如果您以前从未使用过它们，也许这会让您入门。