excel排序求和:如何统计前几名数据合计

  • 时间:
  • 浏览:5
  • 来源:安徽财经大学教务处_安徽大学教务系统_安徽工业大学教务处师范
阅读模式

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

*** *** ***

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

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

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

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

简单说一下这个公式吧,SUMIF(条件区域,条件,求和区域),关键是这里的条件,"

不过今天要说的重点不是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))公式就没有问题,至于为什么,咱们下篇接着聊。

****部落窝教育-excel排名数据合计****

原创:老菜鸟/部落窝教育(未经同意,请勿转载)

猜你喜欢