问题描述
根据几个网站,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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!