前两天有人向我求助,要解决一个问题,下面就是他的求助:
【如何在B列中筛选出所有同名的人 然后将所有同名人的增值累加起来 比如在B列中筛选出叫张三的人,然后将张三下一列的增值累加起来,结果输出到E1。也就是在B列中筛选出符合D1条件的人,然后将对应的B列中下一行的值累加,也就是筛选与求和在同一列进行。】
首先对这个表要做个评价,这个表做的很好,给使用人自己都带来困惑,Excel表最好把姓名,分数等信息最好各自填一列,这样计算就会简单多,而且数据透视、公式计算都会方便很多:
姓名
均分
差值
均分
差值
增值
张三
李四
王五
下面针对这个朋友的求助,刚开始我是这么想的“我在不改变原有表格结构的情形下,怎么实现这个朋友的需求,首先看这个朋友的表格,名字都在数值的上方,而且都是紧挨着的,这个地方要表扬下这个朋友,表格做的还是有一定规律的,这个就成功了一大半;看到这里我想到了SUMIFS函数,但是条件和求和区域都在一列,咋弄呢,这时候就需要一个重量级选手登场了,这个重量级选手就是OFFSET函数,这个函数之前的文章中多次提到,这个函数呢,被叫做数据偏移函数,简单理解就是,数据的选取或引用区域可以按照要求进行变化,比如,将A1单元格的选取或引用调整到B2,或调整到一个区域单元格,比如A1单元格的选取或引用调整到B2:C5。”
这个朋友给的是图片,抄起来很麻烦,我就简化处理了下,同时在E2单元个输入:=SUMIFS(OFFSET(B6,1,0,ROW(B19)-ROW(B6)+1,1),B6:B19,D2),就得到了结果400,这个是不是显的有点复杂呢,是的我搞复杂了,我犯了一个错误,就是函数使用多了,脑子会把问题想复杂,其实用=SUMIFS(B7:B20,B6:B19,D2)就可以实现的,但我竟然想到了用OFFSET,刚开始还觉得自己很厉害,想到了这个函数,后来真正把这个函数输完后,我就开始骂我自己了,脑袋可能被驴踢了,我折腾了半天不就是错位引用了下数据么,OFFSET(B6,1,0,ROW(B19)-ROW(B6)+1,1)和B7:B20有区别么,根本没有嘛。
Sumifs这个函数想必很多人是很熟悉的,就是一个多条件求和的函数,一般要求是参数的区域范围是一样的,它的语法如下:
=sumifs(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...) sumifs(实际求和区域,第一个条件区域,第一个对应的求和条件,第二个条件区域,第二个对应的求和条件,第N个条件区域,第N个对应的求和条件)
我标记下划线的区域,要求范围大小是相同的,如果区域范围大小不同会报错。比如:
再回到之前的例子中,我们发现我们求和区域和条件区域都在一列里面,无法进行名字和数字的匹配,这时候我们需要让他们配一下,那就是把求和区域设为B7:B20,我只能模拟一下,公式上是看不到的。
其实错下引用位置就可以了:
我今天犯的这个错误,我反省了下,可能是我平时OFFSET函数使用的比较频繁,刚看到问题,脑袋里第一个就会闪现它,最后虽然也实现了目标,而且也没花多少时间,但是有更简单快捷的方式,而且能让被帮助的人更容易理解,而不是变得懵圈。在Excel的学习和使用中,一定要注意,不要把问题想复杂了,能有简单的方法就用简单的方法,比如加个辅助列能解决的,就不要想复杂的公式了,多理解各个函数、技巧的原理,使用起来才会更加顺手。
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请
点击举报。