SUM函数在我们工作中应用是非常广泛的,很多人最开始接触的函数就是SUM,难道SUM函数只是用于单一的求和吗?一个看似最简单的函数,我们往往忽略了它最深层次的强大功能。 一、函数语法解析 1、函数定义:计算单元格区域中所有数值的和; 2、参数说明: ①、参数可以是单个值、数组、单元格引用或是单元格区域。 ②、如果参数为数组或引用,则只有数字被计算,数组或引用中的空白单元格、逻辑值、文本将被忽略。 ③、如果参数为错误值或为不能转换成数字的文本,将会显示错误。 二、应用实例 ▲ 01 经典展示
▲ 02 文本数字求和
输入公式:=SUM(--(B2:B8)),数组公式,按Ctrl Shift Enter三键结束。 解析:公式中两个负号,一个负号是把文本型数字转换成负数值,另一个负号是把负数值转换成需要的正数。 ▲ 03 单条件求个数
输入公式:=SUM((B2:D8>=80)*1),数组公式,按Ctrl Shift Enter三键结束。 解析:乘以1是将(B2:D8>=80)部分中的逻辑值TRUE转换成1,FALSE转换成0,然后用SUM函数求和,算出的就是满足条件的个数。当然转换的方法有很多种,可以用“--”、“-0”、“ 0”、“^1”、“/1”等等,关键看你自己怎么用。
▲ 04 单条件求和
输入公式:=SUM((B2:B8>=60)*B2:B8),数组公式,按Ctrl Shift Enter三键结束。 解析:(B2:B8>=60)部分是判断B2:B8单元格中的值是否大于等于60,是的返回逻辑值TRUE,否则返回逻辑值FALSE。发生四则运算时,TRUE相当于1,FALSE相当于0,当再乘以B2:B8时,为FALSE对应的值返回0,为TRUE的返回分数本身,所以求出的都是大于等于60的和。 ▲ 05 多条件求个数
输入公式:=SUM((A2:A8='一班')*(C2:E8>=80)),数组公式,按Ctrl Shift Enter三键结束。 解析:(A2:A8='一班')部分是指定条件为一班,(C2:E8>=80)部分是 指定条件为大于等于80,这两部分相乘就是要同时满足是一班且分数大于等于80。 ▲ 06 多条件求和
输入公式:=SUM((A2:A8='二班')*(C2:E8>=80)*C2:E8),数组公式,按Ctrl Shift Enter三键结束。 解析:(A2:A8='二班')*(C2:E8>=80)部分是两个条件都成立的返回1,不成立的返回0,再*C2:E8部分得到的都是既是二班又大于等于80的分数,因为是数组所以用SUM求和时要加花括号。
▲ 07 数组相乘
输入公式:=SUM(B2:B6*C2:C6),数组公式,按Ctrl Shift Enter三键结束。 解析:编写公式遇到不懂的时候可以抹黑按F9查看,B2:B6*C2:C6部分抹黑按F9得到的是{6204;5270;2255;4484;2337},这就是数量*单价对应的金额,返回的是数组,所以要加花括号。
▲ 08 生成序列号
▲ 09 小计求和
输入公式:=SUM(B2:B14)/2 解析:如图中小计已经把数据汇总了一遍,再用SUM函数求总计时就相当于汇总了两遍,所以/2。 ▲ 10 一条公式求小计与总计
选中区域A2:D15 按F5键定位空值 输入公式:=(SUM(B$2:B4)-SUMIF($A$2:$A4,$A5,B$2:B4)*2)/(($A5='总计') 1) 按Ctrl Enter键结束 ▲ 11 累计求和
普通公式:输入公式:=SUM(B$2:B2)-SUM(C$2:C2),向下填充 数组公式:输入公式:=SUM(B$2:B2-C$2:C2),按Ctrl Shift Enter三键结束。 ▲ 12 合并单元格求和
选中单元格区域E2:E10 输入公式:=SUM(D2:D10)-SUM(E3:E10) 按<Ctrl Enter>结束
▲ 13 多表求和 下图为各业务员1-12月的销售金额,在各表的结构一致的情况下,现要汇总各业务员全年的销售总额: 在总表B2单元格输入公式:=SUM('*'!B2),向下填充。 或者输入公式:=SUM('1月:12月'!B2),向下填充。 解析:公式=SUM('*'!B2)是表示除当前工作表之外的所有其他工作表B2单元格的和,在输完公式后,按下Enter键*会自动变成实际的工作表名,如:=SUM('1月:12月'!B2)。 公式=SUM('1月:12月'!B2),先输入“SUM(”,选中工作表1月,按住Shift键,选中工作表12月,再选中B2单元格,敲回车,搞定!
以上实例均由函数SUM独自完成,如果配上其他函数一起组合的话,它的功能将更强大。 作者:仰望~星空 |
|