当前位置:首页 > 办公 > Excel教程 > 正文内容

SUMPRODUCT函数详解(4)

酷网3周前 (10-08)Excel教程27

在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函数的能力。

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%。

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/39537.html

分享给朋友:

相关文章

Excel合并单元格无法筛选,怎么办?

Excel合并单元格无法筛选,怎么办?

你在对合并单元格进行筛选时,是不是只能筛选出第一项?如下动图: 解决方法 1、选择合并单元格,复制到另一列,备用; 2、选中原合并单元格,【开始】——【合并单元格】——【取消合并单元格】; 3、CTRL+G,打开定位窗...

学不完的办公技巧 ▏Excel篇

学不完的办公技巧 ▏Excel篇

说起office办公软件,大家应该都很熟悉吧,基本每个人都要用到它来办公和作业,office办公软件的使用还有很多小技巧,不知道你们知不知道? 下面就给大家介绍一些office办公软件的小技巧,希望对大家能有所帮助。 &...

excel数据透视表怎么计算工作日天数?

excel数据透视表怎么计算工作日天数?

本文提供了一个公式,能够计算多种情形下某个月的工作日天数,如下所示。 单元格C2中的公式为: =MAX(0,NETWORKDAYS(MAX($A2,C$1),MIN($B2,EOMONTH(C$1,0)))) 向右拖至...

超链接函数HYPERLINK的用法

超链接函数HYPERLINK的用法

HYPERLINK函数是一个很有用的超链接函数,可以设置超链接跳转到指定工作表的单元格、存储在硬盘驱动器上的文件或是用来打开存储在网络服务器、Intranet或internet中的文件。 今天给大家举例说明这个函数的用法...

Excel中的定位功能,你会用吗?

Excel中的定位功能,你会用吗?

1 Excel中的定位功能藏在哪里? Excel中的定位功能在开始选项卡下的查找和选择组中,调出定位功能的快捷键是F5或Ctrl+G。 2 应用1:快速选中表中所有数字 如果想把报表中所有手动填写的数字清除,同时不删除...

INDEX函数,给公式提供数组

INDEX函数,给公式提供数组

INDEX函数虽然可以生成数组,但如果不用数组公式,似乎只能返回其生成的数组中的第一个元素。然而,可以使用INDEX函数来给公式提供数组。例如: =SUM(INDEX(A1:A10,N(IF(1,{1,4,8}))))...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。