用SUMIF错位求和多表求和与查找
作者:小窝来源:部落窝教育发布时间:2024-04-19 18:31:34点击:1356
大家习惯性的认为SUMIF就是1列条件区域1列求和区域。实际上并非如此,SUMIF条件求和的规则或原理:可以有多列条件区域,它按条件区域的大小与求和区域进行成对的条件求和。利用这点可以很简单地搞定多表汇多表查找、并列表汇总、错位求和等等。
在《按条件进行多列求和》的教程中,小窝曾提到SUMIF的奇怪规则,由下面的一个故意为难人的要求引起。
小窝的解决方法就是增加两列姓名,让条件区域和求和区域大小一致:
以上用到了SUMIF鲜为人知的运算规则:
以条件区域大小为基础,将条件列(或行)和求和列(或行)按相同列序(或行序)成对,然后累加每对中符合条件的数据。
啥意思?下方的示意图有利于大家理解。
小窝把条件区域各列中“王静”的排序调整一下,大家看得更明白:
首先在第1列条件中查找“王静”,返回第1列求和区域中的“142”;接着在第2列条件中查找,返回第2列求和区域中的“109”;再在第3列条件中查找,返回第3列求和区域中的“60”;最后三者相加就等于311。
由于是以条件区域的大小为基础进行成对条件求和,所以求和区域可以简写,只引用区域中第一个单元格,SUMIF在实际运算中会自动补齐,让求和区域与条件区域大小相等。
了解了SUMIF按照条件区域大小成对进行条件求和的规则,那神秘的错位求和、多表查找就很简单了。
1.错位求和
所谓的错位求和,就是指条件区域和求和区域有重叠,或者位置有错行或者错列。
1)多表汇总、并列表汇总——条件区域和求和区域重叠
譬如求下方王文、刘新的总分,可以认为是按条件汇总英语、语文、数学三个并列表中的数据。
简单,公式=SUMIF($A$21:$E$26,H20,$B$21:$F$26),向下填充即可。
说明:
把姓名到姓名列A21:E26作为条件区域,分数到分数列B21:F26作为求和区域,然后进行成对条件求和,实质如下图。
在第1列条件中查“王文”,返回第1列求和区域中的69;在第2列条件中查找,没有符合条件的,返回0……以此类推,完成5对条件求和并累加69+0+99+0+61=229。
2)求所有列最后一个数据的和
譬如求下方5种产品最后报价之和。
也很简单,公式=SUMIF(B41:F47,"",B40:F46)
说明:
将第二次报价到最后一次报价下一行B41:F47作为条件区域,将第一次报价到最后一次报价B40:F46作为求和区域,条件是空。此处既有重叠——条件区域和求和区域存在重叠,又有位置错位——条件区域位与求和区域错开了一行(但是大小一致)。
第1列条件中等于空的,对应第1列求和区域中的90和0(空);第2列条件中等于空的,对应第2列求和区域中的94……以此类推得到各列最后一次报价并累加。
如果表格是横向的,也是一样的用SUMIF求和搞定。
2.多表查找并列表查找
譬如从下方并列的多个表中查找林菲的成绩和学号。小窝曾分享过用INDIRECT进行多表格查找,但太复杂了。今天借助SUMIF来查找。
因为并不知道林菲位于那一列,所以不管是VLOOKUP,还是XLOOKUP,都不好使。
但用SUMIF很简单就搞定。
公式=SUMIF($A$72:$D$80,$H$72,B72:E80),然后向右填充即可。
说明:
由于没有重名,成绩和学号都是数字,所以用SUMIF求和可以完美的搞定多表查找。
把姓名到姓名区域$A$72:$D$80作为条件区域,把成绩到成绩区域B72:E80作为求和区域,就可以获得成绩。
把姓名到姓名区域$A$72:$D$80作为条件区域,把学号到学号区域C72:F80作为求和区域,就可以获得学号。
本文配套的练习课件请添加客服微信buluowojiaoyu索取。
做Excel高手,快速提升工作效率,部落窝教育Excel精品好课任你选择!
扫下方二维码关注公众号,可随时随地学习Excel:
相关推荐:
版权申明:
本文作者小窝;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。