|
今天继续介绍Excel公式的优化。今天的主题是求和公式。 示例数据如下：
这次的数据量并不大，只有20000行。 需要进行分类汇总统计：
对这个问题来说。最好的解决方案是数据透视表。 这也是我们在各种场合下首选推荐的方案。 但是，有些场景中不能使用数据透视表，比如，需要这样的结果作为中间数据。 这时，我们可以使用公式方案。 为了方便，我们添加两个辅助行数据：
常用的公式解决方案有4个： (1)=SUMIFS(数据!$F$3:$F$20002,数据!$E$3:$E$20002,Index!$B7,数据!$B$3:$B$20002,">="&Index!C$22,数据!$B$3:$B$20002,"<="&Index!C$23)(2)=SUMPRODUCT(数据!$F$3:$F$20002,--(数据!$E$3:$E$20002=Index!$B7),--(数据!$B$3:$B$20002>=Index!C$22),--(数据!$B$3:$B$20002<=Index!C$23))(3)=SUMPRODUCT(数据!$F$3:$F$20002*(--(数据!$E$3:$E$20002=Index!$B7))*(--(数据!$B$3:$B$20002>=Index!C$22))*(--(数据!$B$3:$B$20002<=Index!C$23)))(4)=SUM(IF((数据!$E$3:$E$20002=Index!$B7)*(数据!$B$3:$B$20002>=Index!C$22)*(数据!$B$3:$B$20002<=Index!C$23),数据!$F$3:$F$20002,0)) 这四个公式本质都是一样的：条件求和。 所以，最直接的方案就是公式(1)，用SUMIFS函数进行条件求和，这个公式计算时间最少，只要0.33秒。 公式(2)和公式(3)使用的是SUMPRODUCT函数，本质跟公式(1)一样，只不过SUMPRODUCT函数本身有两种写法，两者的计算速度有明显不同。其中公式(2)计算时间是1.02秒，公式(3)的计算时间是1.17秒。而且，二者的 计算速度都明显比公式(1)慢很多，可见SUMIFS函数还是做过很好的优化的。 公式(4)采用的是SUM+IF方案，在没有SUMIFS函数之前，人们经常采用这种方案。这是一个数组公式，如果你的Excel版本不支持动态数组，就需要通过CTRL+SHIFT+ENTER来完成输入。 公式(4)的计算速度最慢，需要1.26秒的时间。人们常说数组公式效率差，在这个公式中确实如此（当然，这个并不是规律，有些数组公式效率很好的）。 上述四个公式中，其实都是对整个数据区域的引用。因此引用的单元格数量是一样的：都是3n。它们之间的速度差异主要是不同的函数导致的。其中SUMIFS函数效率最好。但是，公式(1)的计算速度仍然不能令我们满意，因为这里的源数据并不多，只有20000行而已。 那么，我们还能怎么优化这个公式呢？ 考虑下面的简化情形，
这是三列数据，前两列是条件，后面一列是用于汇总的数量。假设求和条件是：
显然，上图中标黄色的两行才是我们需要的数据，因此，我们只需要对这两行进行求和。这样，求和涉及的单元格就从20000变成了2。 问题是，我们如何才能定位到这两行呢？ 而且这个定位要足够快，比计算20000行的求和要快得多！ 我们有方法，那就是通过MATCH函数的近似匹配来定位，或者通过XMATCH函数的二分法来定位。（这个方法我们上次介绍查找函数优化时介绍过了）。 注，由于这种方法涉及排序，所以需要用SORT函数。因此，下面的介绍就是用“新”版本的Excel来介绍。至于“旧”版本的Excel中的优化方法，详细看完下面的内容也就很明白了。 因此，我们可以通过XMATCH得到下表：
对于每一个交叉点，我们得到的是它在原始数据中的起始行号。比如，第一行第二列，表示“T恤Q2”，代表该条目在原始数据中从202行开始出现。 我们还需要知道这个条目总共出现了多少行，即需要得到下表：
还以“T恤Q2”为例，表示从202行开始，往下174行都是该条目的数据。 这个表如何计算出来呢？ 仔细观察上一个表，我们发现后项减前一项就可以得到本表。 注：实现这个过程需要一定的中间操作。而且还有最后一项需要特殊处理。详细解释请参见文末视频。 知道了这两个表，我们就可以进行最后的计算了。 根据刚才的分析，我们实现了下面的公式： =LET( start_date, EDATE(DATE(2017,1,1),SEQUENCE(1,12,0,3)), product_cat, SORT(B7:B20,1), data, SORT(数据!B3:F20002,{4,1}), dates, CHOOSECOLS(data,1), cats, CHOOSECOLS(data,4), values, CHOOSECOLS(data,5), dates_cats, cats & dates, matrix_base, product_cat & start_date, matrix_start, IFERROR(XMATCH(matrix_base, dates_cats,1,2),ROWS(cats)+1), row_start, TOCOL(matrix_start), row_end, VSTACK(DROP(row_start,1),ROWS(cats)+1), row_count, row_end-row_start, matrix_count, WRAPROWS(row_count, 12), MAP(matrix_start, matrix_count, LAMBDA(a,b, IF(b>0, SUM(CHOOSEROWS(values,SEQUENCE(b,1,a,1))),0) ) ))其中，
这个公式的计算时间只需要0.06秒。可以说是非常快了！ 详细解释请看视频  |
|
|
