根据几个网站，SUMIFS和COUNTIFS比SUMPRODUCT更快（例如：http://exceluser.com/blog/483/excels-sumifs-or-sumproduct-which-is-faster.html） . 我有一个行数未知（大约200 000）的工作表，我正在用数字计算性能报告 . 我有超过6000次几乎相同的SUMPRODUCT公式，每次都有几个不同（只有条件改变） .
这是我得到的一个例子：
=IF(AFO4>0,
(SUMPRODUCT((Sheet1!$N:$N=$A4)
*(LEFT(Sheet1!$H:$H,2)="1A")
*(Sheet1!$M:$M<>"service catalog")
*(Sheet1!$J:$J="incident")
*(Sheet1!$I:$I<>"self-serve")
*(Sheet1!$AK:$AK=AFM$1)
*(Sheet1!$E:$E>=$E$1)
*(Sheet1!$E:$E<$E$2))
+SUMPRODUCT((Sheet1!$AJ:$AJ=$C4)
*(LEFT(Sheet1!$H:$H,2)="1A")
*(Sheet1!$M:$M<>"service catalog")
*(Sheet1!$J:$J="incident")
*(Sheet1!$I:$I="self-serve")
*(Sheet1!$AK:$AK=AFM$1)
*(Sheet1!$E:$E>=$E$1)
*(Sheet1!$E:$E<$E$2)))/AFO4,0)
计算那件事需要1秒多一点 . 由于我有超过6000个公式，所以计算一切都需要一个多小时 .
所以，我在这里加起来是0和1，我只是计算我的数据源（ Sheet1 ）中满足条件集的行数 . 也许COUNTIFS会更好？
由于我们需要每个月执行一些公式，所以我希望获得一些执行时间 .
如果有帮助我可以使用VBA，但我总是听说Excel公式通常更快 .
2 回答
第一个
SUMPRODUCT可能成为=COUNTIFS(Sheet1!$N:$N,$A4,Sheet1!$H:$H,"1A*",Sheet1!$M:$M,"<>service catalog",Sheet1!$J:$J,"incident",Sheet1!$I:$I,"<>self-serve",Sheet1!$AK:$AK,AFM$1,Sheet1!$E:$E,">="&$E$1,Sheet1!$E:$E,"<"&$E$2)LEFT部分可以通过通配符处理，如图所示沿同一行改变第二部分
而不是公式，为什么不使用数据透视表来处理数字呢？您可能面临更长的一次性命中以将数据加载到PivotCache中，但在此之后，您应该发现数据透视表重新计算得更快，以响应过滤器更改，而不是这些计算昂贵的公式 . 你有没有理由不使用它？
这是我正在编写的一本书中的一些内容，我在其中比较了SUMPRODUCT，SUMIFS，DSUM，数据透视表，高级过滤器，以及称为范围切片（在排序数据上使用INDEX / MATCH的巧妙组合）来有条件地对记录进行求和 . 根据您从10个不同的下拉列表中做出的选择，包含超过100万条销售记录的表格：
这些下拉菜单允许您通过Store，Segment，Species，Gender，Payment，Cust的组合来过滤数据库 . 历史记录，订单状态，交货说明，会员类型和订单渠道列 . 所以有一些非常庞大的过滤和聚合正在进行，以便将这100万条记录减少到一个总和 . 该文件概述了实现此结果的六种不同方式，前三个显示在下面的屏幕截图中：
正如您所期望的那样，当所有这些下拉菜单设置为相同的设置时，您将从所有六种方法中得到完全相同的答案 . 但是，与其他方法相比，如果您更改其中一个下拉列表，那么您不会期望SUMPRODUCT计算新答案的速度有多慢 . 实际上，事实证明，SUMIFS方法比SUMPRODUCT方法快15倍，为这个庞大的数据集提供了答案 . 但这没什么：范围切片方法快了56倍！
范围切片方法通过对源数据进行排序，然后在辅助列中使用一系列聪明的公式来巧妙地识别感兴趣的记录在该排序数据中的位置 . 这意味着您可以直接将几个匹配的记录直接相加，而不必对数十万行（或者相对于一百万行，如此处的示例）进行复杂的条件匹配 .
以下是我的示例文件的外观 . 右侧的Rows helper列中的数字表明，通过一些巧妙的消除，底部的SUM函数只需要处理18行数据（行292996到293014）而不是所有100万行 . 换句话说，这是非常有效的 .
这是第二组替代方案：
是的，你可以很容易地在这里使用数据透视表 . 并且数据透视表方法似乎比SUMPRODUCT快6倍左右 - 尽管在调用过滤器时会出现少量额外延迟，并且第一次执行过滤操作时需要再长一点，因为Excel必须加载将PivotCache放入内存 . 但让我们面对现实：首先设置数据透视表是这些方法中最简单的方法，所以我有投票权 .
DSUM方法比SUMPRODUCT快12倍 . 这不如SUMIFS好，但它仍然是一个重大改进 . 高级过滤器方法只比SUMPRODUCT快4倍 - 这并不奇怪，因为它的作用是从源数据中获取符合条件的所有记录的摘录 . 该列表，将其转储到电子表格中，然后对结果求和 .