标签：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社群下载本文配套示例工作簿。
