Excel电子表格能计算处理大量数据，并进行图形分析，是个强大的数据分析工具。你可以用它计算很多数据的总和。这篇文章将以简单的费用报表为例，讲述如何在微软Excel软件里制作一个简单的电子表格。 步骤......
2023-05-29 0 excel公式,Excel函数教程,excel技巧,excel教程,Excel表格,Excel教程
SUMPRODUCT函数详解(4)
Excel教程
2023-05-29 22:26:08
在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。
接下来，假设我们能扩展这种技术到上文中已讨论的多条件测试公式。例如，统计在六月份（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公式。
例如，下面是统计在五月（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函数的能力。
在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))
甚至两个COUNTif函数或sumif函数都比一个sumPRODUCT函数更快，因此下面的公式：
=COUNTif(A1:A10,>=10)-COUNTif(A1:A10,>20)
比下面的公式更有效率：
=sumPRODUCT((A1:A10>=10)*(A1:A10<=20))
大致提高20%。
标签： Excel函数 excel函数公式 excel表格制作 Excel教程 函数 rank函数怎么用 rank函数的使用方法 rank函数的用法 rank排名函数
上一篇：SUMPRODUCT函数详解(2) 下一篇：SUMPRODUCT函数详解
相关文章
- 详细阅读
-
使用SUMPRODUCT函数巧妙处理多条件判断详细阅读
下面是要使用公式解决的问题： 如果值处于0%-25%，则返回0；处于16%-50%，则返回0.1；处于51%-75%，则返回0.2；处于76%-100%，则返回0.3；大于100%则返回0.4。......
2023-05-29 0 Excel函数,excel函数公式,excel表格制作,Excel教程
-
如何在 Excel 中创建下拉列表详细阅读
在 Microsoft Excel 数据表中创建下拉列表通常可以提高数据输入效率，同时将数据输入的范围限定到特定项目的集合，或仅限于下拉列表中所提供的数据。按照以下步骤，学习如何在 Excel 201......
2023-05-29 0 excel公式,Excel函数教程,excel技巧,excel教程,Excel表格,Excel教程
-
一个简单的Excel VBA程序详细阅读
VBA是一种“寄生”在主应用程序（例如Excel）中的语言，正是因为这样的特殊性，所以我们先介绍了与主应用程序相关的对象模型和很多对象，以及如何引用这些对象及其属性和方法。 我的思路（当然也是笔者在......
2023-05-29 0 Excel函数,excel函数公式,excel表格制作,Excel教程
-
如何在Microsoft Excel中插入超链接详细阅读
本篇教你如何在Microsoft Excel中创建文件、文件夹、网页或新文档的链接。在Windows和Mac版本的Excel上都可以这样操作。 步骤 方法 1链接到新文件 打开Excel文档。......
2023-05-29 0 excel,excel 函数,excel入门教程,Excel教程