优化Excel公式

本文介绍了优化Excel公式-SUMPRODUCT与SUMIFS/COUNTIFS的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

根据两个网站，SUMIFS和COUNTIFS比SUMPRODUCT更快(例如: exceluser/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: exceluser/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).

这是我所得到的例子:

=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.

推荐答案

第一个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部分可以用通配符处理，如图所示

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

沿同一行更改第二部分