求和，没那么简单

标签：Excel公式练习

在数据分析中，经常会碰到求一组数据中最大的或最小的n个值之和，或者附加其他条件后，再来求最大的或最小的n个值之和。

示例数据如下图1所示，在单元格区域B10:E310中，有一组数据，要求营业收入排在前20位的收入之和，或者根据列E中是否可见求前20位的营业收入之和。（其中，单元格B4中指定要求和的数值个数，这里将其命名为n）

图1

注：本示例及数据来源于chandoo.org。

如果是全部数据，则可以使用下面的公式：

=SUMIFS(D11:D310,D11:D310,">="&LARGE(D11:D310,n))

如果根据列E中的“是否可见”隐藏了部分行，则使用下面的公式：

=SUMIFS(D11:D310,E11:E310,TRUE,D11:D310,">="&AGGREGATE(14,5,D11:D310,n))

实际上，列E是一个辅助列，其中使用了公式：

=SUBTOTAL(103,B11)=1

用来返回该行是否隐藏。如果可见，则返回TRUE；如果隐藏，则返回FALSE。

好好体会一下上面的公式，很简单吧！

好了！该我们做练习了。

基于上面的数据，求：

1.除0外营业收入排在后10位的收入之和？

2.筛选后，除0外营业收入排在后10位的收入之和？

如下图2所示。

图2

是不是有点难度了？

仿照上面的求营业收入排位在前面的收入之和的公式，可以分别得到下面的公式：

=SUMIFS(D11:D310,D11:D310,"<="&SMALL(D11:D310,n+COUNTIF(D11:D310,0)))

和

=SUMIFS(D11:D310,E11:E310,TRUE,D11:D310,"<="&AGGREGATE(15,5,D11:D310,n+COUNTIFS(E11:E310,TRUE,D11:D310,0)))

或者：

=SUMIFS(D11:D310,E11:E310,TRUE,D11:D310,"<="&SMALL(D11:D310,COUNTIF(D11:D310,0)+n))

那么，还有其他的公式吗？

使用SUMPRODUCT函数：

=SUMPRODUCT(SMALL(IF(D11:D310>0,D11:D310),ROW(INDIRECT("1:"&n))))

这是一个数组公式，输入完后要按Ctrl+Shift+Enter组合键。

和

=SUMPRODUCT(AGGREGATE(15,6,1/(1/(D11:D310*E11:E310)),ROW(INDIRECT("A1:A"&n))))

还有吗？欢迎留言。

注：可以在知识星球完美Excel社群下载本文配套示例工作簿。