二维码 购物车
部落窝在线教育欢迎您!

用SUMIF错位求和多表求和与查找

 

作者:小窝来源:部落窝教育发布时间:2024-04-19 18:31:34点击:1356

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。
编按:

大家习惯性的认为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列求和区域中的900(空);第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

IMG_256

相关推荐:

多区域查找用INDIRECT

含单位和通配符的数字求和

按条件进行多列求和

为何根据颜色查找单元格查找不到?

版权申明:

本文作者小窝;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。