SUMPRODUCT函数详解(4)-英雄云拓展知识分享

在VBA中的条件统计和求和

到至今为止为止，我们关于条件公式的所有讨论都是直接在Excel工作表中。有时，我们需要在VBA程序中对一些工作表单元格进行条件统计和求和。在这些情形下，编写简单的循环来遍历所有数据并检查它们是不是与条件相匹配，统计汇总相匹配的项。

Excel VBA允许在程序中调用内置的工作表函数，避免让我们再重复创建这些功能，极大地改良了VBA代码的能力。当在VBA中调用Excel函数时，如果不过度使用，那末任何的性能作用应当是最小的，优点也很明显。我们能够利用这类优点很容易地在VBA中获得条件统计和求和，但是要注意一些事项。

作为示例，斟酌下表中的数据（与上文中表相同）。

如果需要使用VBA程序求单元格区域A1:A10有多少“Ford”，程序代码以下：

Dim mModel As String

Dim mCount As Long

mModel = “Ford”

mCount =Application.WorksheetFunction.CountIf(Range(“A1:A10”), mModel)

此时，”Ford”的数量将被赋值给mCount变量，其值为4。

一样，我们可使用SUMIF来计算”Ford”所对应的价格和：

Dim mModel As String

Dim mValue As Long

mModel = “Ford”

mValue =Application.WorksheetFunction.SumIf(Range(“A1:A10”), mModel,Range(“C1:C10”))

此时，”Ford”相对应的价格之和被赋值给变量mValue，其值为33873。

接下来，假定我们能扩大这类技术到上文中已讨论的多条件测试公式。举例来看，统计在6月份（June）销售了多少Ford，代码以下：

Dim mModel As String

Dim mMonth As String

Dim mCount As Long

mModel = “Ford”

mMonth = “June”

mCount =Application.WorksheetFunction.CountIfs(Range(“A1:A10”), _

mModel, Range(“B1:B10”),mMonth)

代码将结果3赋值给变量mCount。令人遗憾的是，这项技术不能被扩大到数组公式或条件测试SUMPRODUCT公式。

举例来看，下面是统计在5月（May）有销售了多少Ford的公式：

=SUMPRODUCT((A2:A10=”Ford”)*(B2:B10=”Feb”))

你可能想使用下面的VBA代码得到相同的结果：

Dim mModel As String

Dim mMonth As String

Dim mCount As Long

mModel = “Ford”

mMonth = “May”

mCount =Application.WorksheetFunction.SumProduct( _

Range(“A1:A10”) = mModel,Range(“C1:C10”) = mMonth)

但是，在编译时会得到毛病消息。在这类情况下，VBA试图简单地调用工作表函数，但其实不会评估单元格区域并传递正确的数组信息到工作表函数中。

下面是该问题的解决方案。在VBA中使用Evaluate方法评估函数调用，转换Excel名称为值。代码以下：

Dim mModel As String

Dim mMonth As String

Dim mFormula As String

Dim mCount As Long

mModel = “Ford”

mMonth = “May”

mFormula =”SUMPRODUCT((A1:A10=””” & mModel & _

“””)*(B1:B10=””” & mMonth &”””))”

mCount = Application.Evaluate(mFormula)

虽然需要更多的努力来确保适合地构造函数调用的正确语法，并且适合地使用引号来确保字符串被引号括住，但是这依然是一项有用的技术，提供了在VBA中使用SUMPRODUCT函数的能力。

Excel 2007及以上版本中的SUMPRODUCT

在Microsoft引入Excel 2007时，主要集中在容易使用和改良商务分析功能。令人遗憾的是，工作表函数没有得到太多的关心，只是引入了一些新的函数，其中的两个新函数：COUNTIFS和SUMIFS支持多条件测试。

举例来看，在我们前面的示例中：

=SUMPRODUCT((A1:A10=”Ford”)*(B1:B10=”June”))

=SUMPRODUCT((A1:A10=”Ford”)*(B1:B10=”June”)*(C1:C10))

我们统计单元格区域A1:A10中的内容为“Ford”并且单元格区域B1:B10中的内容为June的项目数，和统计单元格区域A1:A10中的内容为“Ford”并且单元格区域B1:B10中的内容为June并将单元格区域C1:C10相应单元格求和。在Excel 2007及以上版本中，可使用COUNTIFS函数和SUMIFS函数代替SUMPRODUCT函数，相应的公式为：

=COUNTIFS(A1:A10,”Ford”,B1:B10,”June”)

=SUMIFS(C1:C10,A1:A10,”Ford”,B1:B10,”June”)

在Excel 2007中，SUMPRODUCT函数的改进是可以取整列的地址。在Excel开发者工具库中，SUMPRODUCT函数依然保存着其独特的位置，由于COUNTIFS函数和SUMIFS函数依然不能够计算已关闭工作簿中的值。

性能分析

双目运算符（–）与*运算符

在大多数情形下，可使用SUMPRODUCT函数的“*”或”–“版本，并且都能得到正确的功能。但是，也有一些例外。斟酌在单元格区域A1:B10中是一个包括姓名和数量的表，其中第一行是文本标题“Name”和“Amount”。公式：

=SUMPRODUCT(–(A1:A10=”Bob”),–(B1:B10)>0),B1:B10)

将正确地计算列A中姓名是“Bob”且列B中为正值的和。但是，公式：

=SUMPRODUCT((A1:A10=”Bob”)*(B1:B10>0)*(B1:B10))

将返回#VALUE!毛病。毛病的缘由是由于B1中是文本，乘以文本值致使毛病。为了解决毛病，单元格区域不能含标题单元格，应以单元格A2和B2开始。

类似地，如果公式中的一个或多个单元格区域包括多列，则必须使用“*”运算符，而下面的公式将不能运行：

=SUMPRODUCT(–(A1:A10=”Bob”),–(B1:C10>0),–(B1:C10))

下面的公式工作得很好：

=SUMPRODUCT((A1:A10=”Bob”)*(B1:C10>0)*(B1:C10))

事实上下面的公式也能够：

=SUMPRODUCT((A1:A10=”Bob”)*(B1:C10>0),B1:C10)

使用转置

如果在SUMPRODUCT函数中使用TRANSPOSE函数，那末必须使用“*”运算符。

公式效力

很多人都知道使用数组公式要付出高的代价，如果过量地使用将明显减弱工作表/工作簿的重新计算速度。

虽然SUMPRODUCT函数不是数组公式，但是它也面对一样的问题。虽然SUMPRODUCT函数通常比等价的数组公式更快，但与数组公式一样，SUMPRODUCT函数比COUNTIF函数和SUMIF函数更慢，因此如果适合的话使用这些函数会更好。

因此，这种情况下，在下面的情形下，不要使用SUMPRODUCT函数：

=SUMPRODUCT((A1:A10=”Ford”)*(C1:C10))

而是使用等价的SUMIF函数：

=SUMIF(A1:A10,”Ford”,C1:C10)

乃至两个COUNTIF函数或SUMIF函数都比一个SUMPRODUCT函数更快，因此下面的公式：

=COUNTIF(A1:A10,>=10)-COUNTIF(A1:A10,>20)

比下面的公式更行之有效的率：

=SUMPRODUCT((A1:A10>=10)*(A1:A10<=20))

大致提高20%。