SUMPRODUCT功能详解(4)

VBA的条件统计和求和。

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

Excel VBA允许在程序中调用内置的工作表函数，避免了重复创建这些函数，大大提高了VBA代码的能力。在VBA调用Excel函数时，如果没有被过度使用，任何性能影响都应该是最小的，其优势是显而易见的。在VBA，我们可以利用这一优势很容易地得到条件统计和求和，但要注意一些事项。

例如，考虑下表中的数据(与上表相同)。

如果需要用VBA程序找出A1:A10小区有多少个“福特”，程序代码如下：

将模型作为字符串变暗

昏暗的山一样长

mModel="福特"

计数=应用。工作站功能。计数(范围(“A1:A10”)，mModel)

此时，“福特”的数字将被分配给值为4的mCount变量。

同样，我们可以使用SUMIF来计算“福特”对应的价格，并且：

将模型作为字符串变暗

将值调暗为长

mModel="福特"

mValue=应用程序。WorksheetFunction.SumIf(范围(“A1:A10”)、mModel、Range(“c 1: C10”))

此时，对应于“福特”的价格总和被分配给变量mValue，其值为33873。

接下来，假设我们可以将该技术扩展到上面讨论的多条件测试公式。例如，要统计6月份福特售出了多少辆，代码如下：

将模型作为字符串变暗

作为字符串模糊常见

昏暗的山一样长

mModel="福特"

mMonth=" June "

计数=应用。WorksheetFunction.CountIfs(范围(“A1:A10”)，_

mModel，Range("B1:B10 ")，mMonth)

代码3被分配给变量mCount。不幸的是，这种技术不能扩展到数组公式或条件测试SUMPRODUCT公式。

例如，以下是计算5月份福特汽车销量的公式：

=SUMPRODUCT((A2:A10=“福特”)*(b 2: b 10=“Feb”))

您可能希望使用以下VBA代码获得相同的结果：

将模型作为字符串变暗

作为字符串模糊常见

昏暗的山一样长

mModel="福特"

“五月”

计数=应用。WorksheetFunction.SumProduct(_

范围(“a 1: a10”)=mModel，范围(“c 1: C10”)=mmuth)

但是，您将在编译时收到一条错误消息。在这种情况下，VBA试图简单地调用工作表函数，但它不会计算单元格区域，也不会将正确的数组信息传递给工作表函数。

下面是这个问题的解决方案。使用评估方法评估VBA的函数调用，并将Excel名称转换为值。代码如下：

将模型作为字符串变暗

作为字符串模糊常见

将公式作为字符串变暗

昏暗的山一样长

mModel="福特"

“五月”

mFormula=" SUMPRODUCT((a 1: a 10=" " mModel _

" " " " *(b 1: b 10=" " " Month " " " ")"

计数=应用。评估(公式)

尽管需要更多的努力来确保函数调用的正确语法的正确构造和引号的正确使用，以确保字符串包含在引号中，但它仍然是一种有用的技术，提供了在VBA使用SUMPRODUCT函数的能力。

Excel 2007及以上版本的SUMPRODUCT。

当微软推出Excel 2007时，它专注于易用性和改进业务分析功能。不幸的是，工作表函数并没有受到太多的关注，但是引入了一些新的函数，其中两个新的函数COUNTIFS和SUMIFS支持多条件测试。

例如，在我们前面的示例中：

=SUMPRODUCT((A1:A10="福特")*(B1:B10="六月"))

=SUMPRODUCT((A1:A10="福特")*(B1:B10="六月")*(C1:C10))

我们用“福特”计数单元格区域A1:A10中的项目数，用“六月”计数单元格区域B1:B10中的项目数，用“福特”计数单元格区域A1:A10中的项目数，用“六月”计数单元格区域B1:B10中的项目数，并将单元格区域C1:C10中的对应单元格相加。在Excel 2007及以上版本中，可以用COUNTIFS函数和SUMIFS函数代替SUMPRODUCT函数，对应公式为：

=COUNTIFS(A1:A10，“福特”，B1:B10，“六月”)

=SUMIFS(C1:C10，A1:A10，“福特”，B1:B10，“六月”)

在Excel 2007中，SUMPRODUCT函数的改进是可以取整列的地址。在Excel开发人员工具库中，SUMPRODUCT函数仍然保留其唯一的位置，因为COUNTIFS函数和SUMIFS函数仍然无法计算已关闭工作簿中的值。

技术性能分析

双目运算符()和*运算符。

在大多数情况下，您可以使用SUMPRODUCT函数的“*”或“-”版本，并获得正确的函数。然而，也有一些例外。考虑单元格区域A1:B10是包含名称和数量的表格，其中第一行是文本标题“名称”和“金额”。公式：

=SUMPRODUCT((a 1: a10=“Bob”)、(B1:B10)0、b 1: b10)

将正确计算a列中的“Bob”和B列中的正值之和.但是，公式：

=SUMPRODUCT((a 1: a10=“Bob”)*(b 1: b100)*(b 1: b10))

将返回#VALUE！错误。该错误是由B1是文本，乘以文本值引起的。为了解决这个错误，单元格区域不能包含标题单元格，应该从单元格A2和B2开始。

同样，如果公式中的一个或多个单元格区域包含多列，则必须使用“*”运算符，以下公式将不起作用：

=SUMPRODUCT((a 1: a10=“Bob”)、(b 1: c100)、(b 1: C10))

以下公式运行良好：

=SUMPRODUCT((a 1: a10=“Bob”)*(b 1: c100)*(b 1: C10))

事实上，也可以使用以下公式：

=SUMPRODUCT((a 1: a10=“Bob”)*(b 1: c100)，B1:C10)

使用转置

如果SUMPRODUCT函数中使用了转置函数，则必须使用“*”运算符。

配方效率

很多人都知道使用数组公式会付出很高的代价。如果使用过多，显然会降低工作表/工作簿的重新计算速度。

SUMPRODUCT函数虽然不是数组公式，但也面临同样的问题。虽然SUMPRODUCT函数通常比等价数组公式快，但是和数组公式一样，SUMPRODUCT函数比COUNTIF函数和SUMIF函数慢，所以如果合适的话还是用这些函数比较好。

因此，在以下情况下不要使用SUMPRODUCT功能：

=SUMPRODUCT((A1:A10=“福特”)*(C1:C10))

相反，使用等效的SUMIF函数：

=SUMIF(A1:A10，“福特”，C1:C10)

即使是两个COUNTIF函数或SUMIF函数也比一个SUMPRODUCT函数快，所以下面的公式：

=COUNTIF(A1:A10，=10)-COUNTIF(A1:A10，20)

比下面的公式更有效：

=SUMPRODUCT((a 1: a10=10)*(a 1: a10=20))

大概增长了20%。