优化 Excel 公式 - SUMPRODUCT 与 SUMIFS/COUNTIFS [英] Optimizing Excel formulas - SUMPRODUCT vs SUMIFS/COUNTIFS

问题描述

根据几个网站，SUMIFS 和 COUNTIFS 比 SUMPRODUCT 更快(例如:http://exceluser.com/blog/483/excels-sumifs-or-sumproduct-which-is-faster.html).我有一个行数未知(大约 20 万行)的工作表，我正在用这些数字计算性能报告.我有超过 6000 次几乎相同的 SUMPRODUCT 公式，每次都有一些不同(只有条件改变).

According to a couple of web sites, SUMIFS and COUNTIFS are faster than SUMPRODUCT (for example: http://exceluser.com/blog/483/excels-sumifs-or-sumproduct-which-is-faster.html). I have a worksheet with an unknown number of rows (around 200 000) and I'm calculating performance reports with the numbers. I have over 6000 times almost identical SUMPRODUCT formulas with a couple of difference each times (only the conditions change).

这是我得到的一个例子:

Here is an example of what I got:

=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 多个这样的公式，所以计算所有内容需要一个多小时.

Calculating that thing takes a little bit more than 1 second. Since I have more than 6000 of those formulas, it takes a little bit over an hour to calculate everything.

所以，我现在正在研究如何优化该公式.我可以将其转换为 SUMIFS 吗?会更快吗?我在这里加起来的只是 0 和 1，我只是计算数据源 (Sheet1) 中满足条件集的行数.也许 COUNTIFS 会更好用?

So, I'm now looking at how I could optimize that formula. Could I convert it to SUMIFS? Would it be faster? All I'm adding up here is 0s and 1s, I'm just counting the number of rows in my data source (Sheet1) where the set of conditions is met. Maybe COUNTIFS would work better?

如果您能帮助我们获得一些执行时间，我将不胜感激，因为我们每个月都需要执行这些公式.

I would appreciate any help to gain some execution time since we need to execute the formulas every month.

如果有帮助，我可以使用 VBA，但我总是听说 Excel 公式通常更快.

I can use VBA if that helps, but I always heard that Excel formulas were usually faster.

推荐答案

1st SUMPRODUCT 可以变成

=COUNTIFS(Sheet1!$N:$N,$A4,Sheet1!$H:$H,"1A*",Sheet1!$M:$M,"<>服务目录",Sheet1!$J:$J,"事件",Sheet1!$I:$I,"<>自助服务",Sheet1!$AK:$AK,AFM$‌ 1,Sheet1!$E:$E,">="&$E$1,Sheet1!$E:$E,"<"&$E$2)

LEFT 部分可以用通配符处理，如图

The LEFT part can be handled by a wildcard, as shown

沿同一行更改第二部分

这篇关于优化 Excel 公式 - SUMPRODUCT 与 SUMIFS/COUNTIFS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！