编按：

哈喽，大家好！今天我们要说说，如何在excel中，统计前几名数据的合计。这个问题难倒了不少小伙伴，尤其是遇到数据是杂乱无序的情况，那更是要了老命。不过，这对于excel大神来说，还是非常简单的，分分钟列出一个公式，就完美解决了问题！今天我们就一起来破解一下面对这类问题时，大神都是怎么做的吧！

什么叫做统计前几名合计呢？先看看动画演示吧：

可以看到，数据按照销售数量从高往低排列，选择一个名次，就可以对前几名的销售数量求和。

这类问题在很多与数字排名有关的情况下都用得上，例如对前几名考试成绩求和，对前几名的客户订货量求和等等……

要实现动画1的这种求和非常容易，使用SUMIF函数就能得到结果，公式为：=SUMIF(D:D,"<="&H2,F:F)

简单说一下这个公式吧，SUMIF(条件区域，条件，求和区域)，关键是这里的条件，"<="&H2表示对序号这一列大于等于H2单元格数字的销售数量求和。因为条件中同时存在比较符号<=和单元格引用H2，所以需要&符号进行连接。

不过今天要说的重点不是SUMIF，因为接下来遇到的数据并不是这么规范的从高往低排序，而是杂乱无序的一列数字。

如图所示，AB两列才是原始的数据源，使用公式=SUM(LARGE(B:B,ROW(INDIRECT("1:"&H2))))可以得到与文章开头所示的SUMIF公式相同的结果。

我们今天的目的就是要搞清楚这个看起来有点奇怪的函数组合：SUM+LARGE+ROW+INDIRECT套路

在这个组合中，前三个函数大家并不陌生，SUM是求和函数，LARGE是获取第几个最大值的函数，ROW是得到行号的函数，唯一比较陌生或者说不太好理解的就是INDIRECT函数了。

下面结合示例来解释这个公式的原理。

其实这个公式的核心部分是SUM和LARGE，例如需要对前三名求和，公式就是=SUM(LARGE(B2:B24,{1,2,3}))。

重点需要理解LARGE(B2:B24,{1,2,3})这部分，LARGE(数据区域,第几名)，如果第二参数“第几名”只是一个数字，就表示指定的这个名次，如果是多个数字，就表示每个数字所对应的名次，因此公式LARGE(B2:B24,{1,2,3})会得到三个数，这一点我们可以使用F9功能键来理解，在编辑栏选中公式的这部分：

然后按一下F9键，就能看到LARGE得到的三个数字：

由SUM函数对这三个数字求和，就得到了前三名的合计。

在这种用法中，LARGE的第二参数使用了常量数组，也就是将多个值放在一组大括号中。

使用常量数组有一个弊端，例如要对前10名求和，就要将LARGE(B2:B24,{1,2,3})改成LARGE(B2:B24,{1,2,3,4,5,6,7,8,9,10})。如果要对前20名求和，公式就更啰嗦，因此就有了另一种写法，将常量数组改为ROW函数来实现，还是以前三名为例进行说明。

公式可以写为=SUM(LARGE(B2:B24,ROW(1:3)))

这个公式需要同时按下Ctrl+Shift+回车键完成输入，公式两边的大括号是自动生成的，并不是手工输入的。

可以自己试试，选中ROW(1:3)，使用F9键，可以得到与{1,2,3}相同的结果。对于多个常量的话，使用ROW就更方便了，例如前20名，公式就是=SUM(LARGE(B2:B24,ROW(1:20)))，只需要修改ROW函数中的结束行号就可以。

可以看到，使用了ROW函数后，公式的可扩展性要比使用常量数组方便很多，不过对于要计算的名次不是固定值的时候，公式就做不到了，或许你会认为这样的公式能达到效果：=SUM(LARGE(B2:B24,ROW(1:H2)))

但如果你真的使用的话，会得到这样的回应：

表面上看不出有什么问题，其实问题就出在ROW(1:H2)这部分。

ROW函数用法虽然简单，但是要求比较严格，参数只能使用单元格或单元格区域，也就是一个有效的引用。

1:H2显然不满足这个要求。

怎么办？

这就需要INDIRECT这个非常独特的函数出马了。

其实公式在一开始就有了，写成ROW(INDIRECT("1:"&H2))公式就没有问题，至于为什么，咱们下篇接着聊。

本文配套的练习课件请加入QQ群：747953401下载。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

扫下方二维码关注公众号，可随时随地学习Excel：

相关推荐：

万金油公式《Excel万金油公式INDEX-SMALL-IF-ROW筛选函数公式解读》

SUM函数的特殊用法《SUM函数特殊应用：序号填充、提成比例计算、打印标签制作》

SUMIF函数《无往而不利的SUMIF面对这种条件求和竟然傻眼了！》