转载自百家号作者:EXCEL学习微课堂 打开百度App,看更多美图 最实用Excel求和函数公式大全,小伙伴快来收藏备用! 一、快速求和:快捷键【ALT+=】快速求和 选取整个区域(B2:G14),点按快捷键【ALT+=】 二、含小计行的总计求和公式 如下图所示,要求在单元格设置累计求和公式,等于各小计之和。 B12=SUM(B2:B11)/2 三、隔列求和 隔列求和,如下图所示的计划与实际对比的表中,要计算全年的计划和实际完成情况,可以直接用sumif求和。 F3单元格公式为:=SUMIF($B$2:$E$2,F$2,$B3:$E3) 四、合并单元格求和 如下图所示,要求在D列对A列的类别求和。 D3=SUM(C3:C10)-SUM(D4:D10) 注:公式输入方法,选取D3:D10,在编辑栏中输入公式后按Ctrl+Enter完成输入。 五、含文本型数字求和 含文本型数字的求和,用sum得不到正确的结果,可以用Sumproduct函数进行求和,如下图所示,用Sum求和得到的结果明显不对。 六、忽略错误值求和 如下图所示,如果一个区域中含有错误值,那么在用sum求和时,结果也会显示错误。如果要忽略错误值求和,可用sumif函数解决。 =SUMIF(G2:C12,'<9e+307')或者=sumif(g2:g12,'>0')9e+307')或者=sumif(g2:g12,'> 公式解析: 9E+307是接近最大的数字,一种约定俗成的写法。在excel中,错误值比最大的数字大,因此'<9e+307'就是对小于最大值的数字求和,就实现了我们的目标:忽略错误值只对数字求和。反过来,excel忽略错误值求和,我们也可以这样写公式:=sumif(g2:g12,'>0')9e+307'就是对小于最大值的数字求和,就实现了我们的目标:忽略错误值只对数字求和。反过来,excel忽略错误值求和,我们也可以这样写公式:=sumif(g2:g12,'> 七、多条件求和 根据条件对数据分类求和也是常遇到的求和方式,如果是单条件用SUMIF函数,如果是多条件就用SUMIFS函数。 如下图,根据销售情况表,求出各销售部门2018年1月20日之前的销售总额 I2单元格公式为:=SUMIFS(F:F,C:C,H2,A:A,'<> 八、模糊条件求和 如果需要进行模糊求和,就需要掌握通配符的使用,其中星号是表示任意多个字符。 如下图,根据销售情况表分别计算出各销售部门销售各种产品(打印机、笔记本、台式机)的总数。H2=SUMIFS($E:$E,$C:$C,$G2,$D:$D,H$1&'*'),由于打印机、笔记本和台式机有多个型号,所有要用通配符“*”。 九、多个表格同一位置求和 1月至12月的12个工作表如果格式完全相同,可以用sum函数的多表求和功能。 如下图所示,要求在汇总表里设置合计公式,汇总12个月员工各产品的销售情况。 B2单元格公式为: =SUM('1月:12月'!B2) 十、多个表格多条件求和 多表多条件求和可以用SUM函数和DSUM函数嵌套使用。 如下图所示,有表1、表2和表3三个工作表,要统计三个表中,张1、张2、张3的销量和。 C2单元格输入公式= SUM(DSUM(INDIRECT({'表1';'表2';'表3'}&'!A1:D11'),4,A$1:B2))-SUM(C$1:C1) 公式解析: 1、数据区域,跨表引用了3个工作表,所以用工作表引用函数INDIRECT。 2、列数,第4列“销量”。 3、条件区域是从A1开始的活动区域,所以A1是绝对引用,但B2是相对引用。 4、由于是跨表引用,因此DSUM返回的是数组,不是单值,因此要外套sum。 5、因为不同的行是“或条件”所以在统计张2第一周的销量时,结果会包含张1第一周的销量,需要减去张1第一周销量,同样的道理,统计张3第一周的销售时要减去张1和张2第一周的销售和。 十一、按颜色求和 excel中还没提供按颜色求和的函数,需要借用宏表函数获取单元格的颜色值,然后再用sumif函数求和。 1、定义名称:公式 - 定义名称 ,在定义名称窗口中输入名称【颜色】,也可以自定义其它名称),然后在下面引用来源中输入公式:=GET.CELL(63,1月!B2)&T(NOW()) 公式解析: ①Get.cell(63,单元格) 可以获取单元格填充颜色值 ② &T(NOW) 实现表格在更新时定义名称取值也可以更新 2、获取颜色值:在C列和F列分别输入公式 =颜色,即可获取B列和D列的单元格填充色。 3、通过sumif函数求和。在F3单元格输入公式=SUMIF(C:C,E3,B:B) 我是EXCEL学习微课堂,百家号教育视频原创作者,如果我的分享对您有帮助,欢迎点赞、收藏、评论和转发!更多的EXCEL技能,可以关注百家号“EXCEL学习微课堂”。 与本教程有关的往期分享教程有: 1、Excel秒杀一切的求和函数SUMIFS, 多条件求和就靠它了! 2、EXCEL多条件求和、跨表多条件求和函数DSUM,让你求和效率更高! 本文由“EXCEL学习微课堂”原创,欢迎关注,带你一起长知识! |
|