返回首页
当前位置: 主页 > 精通Office > Excel教程 >

Excel的SUM函数9种公式设置范例

时间:2011-03-13 11:17来源:知行网www.zhixing123.cn 编辑:麦田守望者

1、数组求和:{=SUM((G12:G21>100)*G12:G21)}

[公式说明]:本公式为数组公式,可以对G12:G21区域中大于100的数据进行求和,而排除小于等于100的数据。输入公式时必须按【Ctrl+Shift+Enter】组合键结束,否则无法得到正确结果。

[使用注意]:1、公式中"G12:G21>100"部分表示求和条件,后跟实际求和区域"G12:G21"。如果有多个条件,可一并罗列出来。例如求大于100且小于115的数据之和,公式如下:=SUM(G12:G21>100)*(G12:G21<115)*G12:G21)。2、此数组公式只适用于单个区域求和,如果有多个区域,只能用多个SUM求和,然后相加。例如对G12:G21和H12:H21区域中大于100的数汇总,公式如下:=SUM(SUM((G12:G21>100)*G12:G21),SUM((H12:H21>100)*H12:H21))。3、对于SUM函数的数组公式,可以用SUMPRODUCT函数来代替,从而将数组公式转换成普通公式。例如本案例的公式用SUMPRODUCT函数后,普通公式如下:=SUMPRODUCT((G12:G21>100)*G12:G21)。

2、数据类型转换求和:=SUM(VALUE(H5),H6:H10,J5:J10,L5:L10);=SUM(--(H5),H6:H10,J5:J10,L5:L10);=SUM((H5)*1,H6:H10,J5:J10,L5:L10);=SUM((H5)/1,H6:H10,J5:J10,L5:L10)

[公式说明]:SUM函数用于对单元格区域的数据或者逻辑值、表达式进行求和,它有1-255个参数。鉴于本题的特殊性,公式也可以改为"=SUM(区域1)",函数会忽略区域中的文本。

[使用注意]:1、SUM函数有1-255个参数。参数可以是区域,也可以是表达式。如:=SUM(G5,10*25,G6*8)。2、如果是文本型数字参数,SUM函数是可以直接求和的;如果是引用单元格中的文本型数字(单元格的数字前添加半角单引号“'”),则求和时将忽略。例如G5的值是“'95”,那么以下公式的结果大不同。=SUM(10,"95")—结果为105,=SUM(10,G5)—结果为10。3、如果一定要对单元格中文本型数字进行求和,可以将它转换成数值。转换方法包括多种:利用VALUE函数转换、利用"--"转换和"*1"、"/1"方式转换。例如:=SUM(10,VALUE(G5))、=SUM(10,--(G5))、=SUM(10,(G5)*1)

3、多条件求和1:{=SUM(((G23:G32<100)+(G23:G32>110))*G23:G32)}

[公式说明]:对于两个范围求和,即满足两个条件中任意一个条件都进行求和,这和同时满足两个条件才求和的公式思路不同。"案例2"的使用注意"中已提到,多个条件同时满足才求和需要用"*"连接所有条件,表示同时满足多条件,而本案例中对多个条件用"+"来连接,表示对于多条例中满足任意一个条件就可以进行求和。本公式为数组公式。

[使用注意]:1、多条件满足任意条件即求和,在设置公式时对于所有条件需要用"+"连接,如本例中"(G23:G32<100)+(G23:G32>110)",然后对条件用括号括起来再与实际求和区相乘。如果缺少条件的外括号将得到错误结果。
2、如果不只两个条件,也用同样方式将所有条件罗列出来并相加。例如求区域中小于100或者等于120的数据和,那么数组公式如下:=SUM(((G23:G32<100)+(G23:G32=110)+(G23:G32=120))*G23:G32)

4、多条件求和2:{=SUM((H34:H43="一车间")*(I34:I43="男")*J34:J43)}

[公式说明]:本公式以H、I两列数据作为限制条件,对J列数据汇总。“(H34:H43="一车间")*(I34:I43="男")”表示同时满足两个条件,如果还有更多条件,也可以同时罗列出来,利用“*”符号连接,然后再与求和区相乘,得到最后的汇总结果。

[使用注意]:1、前三个案例是以求和数据自身为条件对目标区域进行求和,本案例以不同的区域作为限制,条件区有两个,对第三区域进行求和。在设置公式时,三个区域的单元格个数必须一致。例如以下公式将产生错误结果:=SUM((H34:H43="一车间")*(I34:I44="男")*J34:J43)、=SUM((H34:H43="一车间")*(I35:I44="男")*J34:J43)。2、多条件求和时,条件录用区域与求和区域可以在不同列,三个区域只需要宽度、高度一致即可。它们所在的列可以不同,起止行也可以不同,甚至三个区域可以分别位于不同的工作表中,仍然可以得到需要的结果。例如条件区在Sheet2工作表,求和区在当前工作表,公式可以修改为:=SUM((Sheet2!H34:H43="一车间")*(Sheet2!I34:I43="男")*J34:J43)。3、本公式可以用另一种简化写法,仍然可以返回正确结果:=SUM((H34:H43&I34:I43="一车间男")*J34:J43)

5、多工作表求和时表名称快捷输入:=SUM(附表一:附表五!B3:B10)

[公式说明]:对多表相同区域求和,不需要录入每个工作表的求和地址,只需将第一个工作表名和最后一个表名用冒号连接,后跟"!"与单元格地址,以此作为SUM函数的参数即可。其中"!"表示前面的字符为工作表名称。

[使用注意]:1、本方法仅适用于每个工作表地址一致的情况,否则只能逐个输入地址再求和。2、多表求和,SUM函数的参数,工作表与单元格地址可以手动输入,也可以通过选择工作表中单元格地址来产生引用。具体步骤如下:输入"=SUM(",然后选择待汇总的第一个工作表B3:B10区域,按住【Shift】键再单击工作表标签中待求和的最后一个工作表名,最后按【Enter】键结果结束即可。3、如果工作表名中包含一些特殊字符,例如"!"、"@",公式中的工作表名需要用单引号括起来,例如:=SUM('A组:!E组'!B3:B10)

6、快速设置当前表以外的所有工作表相同区域的总和公式:=SUM('*'!B3)

[公式说明]1、公式中两个单引号中间加"*"表示除当前表以外的所有工作表。2、在输入公式后,程序会自动将"'*'"转换成实际的工作表名。当按下【Enter】键后,公式将变成:=SUM(附表一:附表五!B3)

[使用注意]1、"'*'"只能手动输入,而不能用鼠标选择工作表来产生引用。2、公式中B3必须使用相对引用。3、本公式用法和"案例7"是不同的。在本例中虽然也可以用"案例7"的方法按住【Shift】键来选择需要求和的工作表产生引用,但是操作烦琐,特别是总表不在最后或者最前面,而是在待求和的工作表中间时,用"案例7"的方法需要选择五次,而本例公式中的"*"可以自动的找到排除当前表之外的所有工作表数据。也就是说"总表"不管在任何位置,都可以用同样的公式完成求和,而不需手动修改。4、公式中"*"左右的单引号必须是半角符号。

7、用SUM函数计数:{=SUM((H94:H102="男")*(I94:I102="汉"))}

[公式说明]:SUM函数虽然是求和函数,在很多时候可以用来计数。本例中利用"H94:H101="男""这一表达式返回一串包含TRUE和FALSE的数组,最后用"*1"将逻辑值转换成数值,并用SUM函数汇总。

[使用注意]:1、表达式"H94:H101="男""得到的是包含逻辑值的数组。SUM函数可以对逻辑参数进行求和,但是包含于数组中的逻辑值值却会被忽略。例如以下公式:=SUM(TRUE,1)—结果为2,=SUM({TRUE,1})结果为1。但是通过"*1"将逻辑值转换成数值后就可以直接求和了。例如:=SUM({TRUE,1}*1)—结果为2。2、将逻辑值转换成数值,除了用"*1"之外,也可以改用"--"或者"/1"等手法,但是用VALUE函数却不行,它只能将文本型数字转换成数值。例如:=SUM(--({TRUE,1}))—结果等于2;=SUM(({TRUE,1})/1)—结果等于1;=SUM(VALUE({TRUE,1}))—结果为错误值,因为VALUE函数无法转换成TRUE。

8、求1累加到100之和:{=SUM(ROW(1:100))}

[公式说明]:本公式利用ROW函数返回1到100的序列,然后用SUM函数将这个序列汇总得到1—100的合计。

[使用注意]:1、ROW函数只能产生1到1048576之间的自然数(Excel2003版本为65536),且不带小数。但是通过换算也可以完成其他序列的求和。例如1/1、1/2、1/3、1/4……1/99、1/100之和。可以用以下公式:=SUM(1/ROW(1:100))。如果是求1开1次方,2开2次方,3开3次方至100开100次方的总和,那么可以用以下数组公式:{=SUM(ROW(1:100)^(1/ROW(1:100)))}。如果是对1—100之间的奇数进行求和,可以用以下数组公式:{=SUM((ROW(1:50)*2-1))}。2、ROW(1:100)产生的结果是一个包含1到100之间的自然数数组。用SUM函数对这种数组求和时必须用数组公式,否则只能对数组中的第一个数值进行求和。如果将"ROW(1:100)"写成"{1,2,3,4}"这种形式的数组则可以按照普通公式处理。

9、多个工作表相同区域求前三名产量之和:{=SUM(LARGE(CHOOSE({1,2,3,4,5},附表一!B3:B10,附表二!B3:B10,附表三!B3:B10,附表四!B3:B10,附表五!B3:B10),ROW(1:3)))}

[公式说明]:如以下公式:=SUM(LARGE(附表一:附表五!B3:B13,ROW(1:3)))。因为LARGE函数不支持多个工作表地址作为第一参数,即三维引用。所以本公式利用CHOOSE函数将三维引用转换成二维数组后,就可以作为LARGE函数的参数进行运算了。用LARGE函数提取前三最大值后再用SUM函数汇总。

[使用注意]:公式的常量数组"{1,2,3,4,5}"可以转换为"COLUMN(A:E)",而不用"ROW(1:5)"。因为"ROW(1:5)"等于"{1;2;3;4;5})。区别为一个是横向数组,一个是纵向数组。

顶一下
(0)
0%
踩一下
(0)
0%
标签(Tag):EXCEL
------分隔线----------------------------
------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
验证码:点击我更换图片