EXCEL的SUM、SUMIF使用方法
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
4300
19600
如加IF函 数就可以 不用 NOT(NOT ())
提示：以 (C92:C10 5="女 ")+(D92:D 105>=150 0)这样的 形式表示 条件或的 关系，在 条件可能 同时满足 时要用 NOT(NOT ())转换
或 用IF函数 判别。否 则会多计 数量的。 统计偶数 行的工资 总和是多 少？
提示：我 们在使用 SUM函数 的数组公 式时，经 常只考虑 有1个参 数，其实 是忘了 SUM函数 最多可有 30个参数 。
D.以没有 打开的工 作薄的指 定表的指 定区域引 用作参数
公式为 =SUM('C :\excelho mefuncti on\[INDI RECT函 数的使 用.xls]Sh eet2'!$B: 600 $B)
数组公 式，不带 {、}号输 入，按 ctrl+shift +enter三 3500 键结束。
姓名 a1
a2 a3
#N/A
公式 {=SUM({" 姓名 ","a1","10 00",TRUE ,2000,FAL SE,1500}, H11:H14= "a2")} 错误值， 不管是在 引用、参 数、还是 在数组中 均返回错 误，此处 excel的帮 助中有错 误。
E.以(由多 个元素的 数组参数 产生的)单 元格区域 引用作 SUM函数 的参数
一般是
INDIRECT
函数和
OFFSET
函数才能
以多个数
组元素，
产生一系
列对多个
单元格区
域的引
用，返回
的是三维
的区域引
用，
SUM函数
只能对第1
个元素指
定的区域
求和，如
H64格的
公式。
61
用内嵌
SUMIF函
数代替就
正确了，
见H65格
。
130
F.以非数 字型文本 作参数的 方法 A中指出参 数或作为 参数的计 算表达式 为不是数 字型的文 本，返回 错误。
要解决直 接参数为 非数字的 问题，可 按图设置 。
G.SUM函 数在数组 公式中的 一些应用
#VALUE! 按图设置 后，就按 Lotus1-23的方式 忽略文本 。见H69 格。
基于：
FALSE*任
何数=0；
TRUE*任
何数=原来
的数
(B92:B10
5="A")*(C
92:C105= "女")为并 列条件， *D92:D10 5后就是满 足条件的 工资。 所有女性 员工的工 资和男性 员工工资 1500以上 的工资总 额是多 少？
NOT(TRU E)=FALS E； NOT(FAL SE)=TRU E； NOT(0)=T RUE； NOT(非0 数 值)=FALS 3000 E 900 1800 2500
中表现， 而不能继 续进行数 组运算的 。
Excelho me.net 中有很多 的相关帖 子，请大 家多看看 。
工资 1000
婚姻状况 TRUE
2000 FALSE 1500
#DIV/0!
B
C
D
10
20
30
11
21
31
12
22
32
13
23
33
14
24
34
15
25
35
102
102
如有2个 以上并列 条件，可 将几个条 件式相乘 。
引用中有 #N/A 错误值
数组中有 #VALUE! 错误值 参数或作 为参数的 计算表达 式为不是 数字型的 文本，返 回错误。 要不返回 错误用F中 提出的方 法。
直接用不 是数字的 文本作参 #VALUE! 数
作为参数 的计算表 达式的结 果为错误 #DIV/0! 值
以返回不 是数字的 文Leabharlann Baidu表达 式作为参 #VALUE! 数
如 果参数为 错误值或 为不能转 换成数字 的文本， 将会导致 错误。 下面作一 些详细的 分析：
A.对文本 、逻辑值 及错误值 的计算 对引用中 的文本、 数字型的 文本、逻 辑值忽略 不计算。
3500 公式 =SUM(H1 1:J14)， 只计单元 格中的数 值，不计 文本、逻 辑值
和I12格中 的文本 1000 对数组中 的文本、 数字型的 文本、逻 辑值忽略 不计。
和A部门工
资1500以
上的男性
员工总数
A8
A
男
1000 是多少？
A9
C
女
1000
3
因为
(C92:C10
5="女")和
(D92:D10
5>=1500)
可能同时
满足，所
以再用
NOT(NOT
())转换，
A10
D
男
2000 基于
A11
A
男
A12
B
男
A13
A
女
A14
A
女
多条件求
和
A部门女性
员工的工
资总额是
多少？
公式为 {=SUM((
H28:H34
,I34,J29:
K29),((H
29:K34
I:I)>12)*(
H29:K34
I:I),SUM:
SUMIF!H
191 29:H34)} 请注意上 面的数组 公式中 SUM函数 有3个参 数，第1个 是联合区 域引用， 第2个是交 叉区域引 用计算的 数组，第3 个是三维 区域引用 。 由于联合 区域和三 维引用区 域均没有 继续进行 数组运 算，所以 在SUM函 数的数组 公式中作 为单独的 参数是可 以的。
TRUE*FA LSE=0；
FALSE*F
1000 ALSE=0 所以此处 是逻辑与 的关系， 在excel的 IF函数的 条件中，0 表示 FALSE， 非0的数值 表示TRUE
1500 。
去除IF函
数可以简
A3
C
女
1000 化公式为
3
A、B两部
门的男性
员工有几
A4
D
女
800 人？
4
基于
TRUE+FA LSE=1；
H31:K32 和
I29:J34 相交的
B31:C32 区域。
A 1 2 3 4 5 6
公式为 {=SUM((( H29:K34 I:I)>12)*( H29:K34 42 I:I))} 实际计算 的是 I29:I34区 域大于12 的值的和
C.以三维 引用作参 数
公式为 =SUM(S UM:SUM IF!H29:H 34)，其 中 SUM:SU MIF!H29: H34为对 从SUM 工作表开 始至 SUMIF工 63 作表结束 的 H29:H34 的区域引 用。 象这样的 直接三维 引用不可 继续用于 数组计算 中。 象下面这 样的数组 公式为什 么是可以 正确运算 的？
参数或作 为参数的 计算表达 式为数字 型的文 本，转为 数值后计 算；
参数或作
为参数的
计算表达
式为逻辑
值时，
TRUE算
1，FALSE
算0。
公式为
=SUM(1
0,2>1,1>
2,TRUE,
FALSE,"
2","2"&"0
34 ")
B.以引用 的运算作 参数
其中的 2>1为逻 辑值 TRUE算 1，直接 参数 TRUE算 1，其他 的FALSE 算0，文 本2和表 达式文本 20转换后 计算。
实际相当 于 SUM(INDI RECT({"H 27:J31"})) ，即对 H27:J31区 域求和。 H54格是 将这样的 SUM函数 放在IF函 数中作为 参数，就 错误了，
102
0
因为 ROW()返 回的是一 个数组并 不是一个 数值，这 样在数组 公式中 INDIRECT 函数返回 的并不是 一个二维 的区域引 用， 而是三维 的区域引 用(第3维 的尺寸是 1)，所以 SUM的计 算出错。 可以将公 式改为I54 格的样子 (本论坛的 会员提出 的方法， 先用SUM 函数将 ROW函数 的数组变 为数值)， 或用 SUMIF代 替(见J54 格)。
引用了 C:\excelh omefunct ion\INDI RECT函 数的使 用.xls 工作薄 Sheet2 表的整个 B列。
只要路径 所指定的 文件存在 就不需要 打开文 件，如不 存在就返 回错误。
E.以(由一 个元素的 数组参数 产生的)单 元格区域 引用作 SUM函数 的参数 SUM函数 不作为其 他函数的 参数可以 使用，见 公G5式4格为， =SUM(IN DIRECT(" H"&ROW( )/2&":J"&R OW()/2+4) )
FALSE+F ALSE=0；
A5
B
女
TRUE+TR 2000 UE=2
A6
C
男
A7
D
男
而 (B92:B10 5="A")和
(B92:B10 5="B")不 可能同时 满足，所 以此处是 条件或的 2500 关系， 再乘以 (C92:C10 5="男")作 为并列条 1500 件。
A部门所有
女性员工
SUM函 数的使 用
语法：
SUM(参数 1,参数 2,...,参数
30) 结果：返 回所有参 数中的数 字之和。 说明：参 数最多只 能有30 个，并且 可以省略 (即,,间没 有参数或 最后有一 个,)；
参 数可以为 引用、返 回数值和 文本及逻 辑值的计 算表达式 、数组；
参 数如为引 用，可以 是区域联 合、区域 交叉、三 维区域引 用，只要 引用不再 参与数组 运算就可 以；
区域联合
区域交叉
联合区域 不能在数 组公式中 继续进行 计算。 交叉引用 在数组公 式中可以 可以继续 进行计算 。
请注意区 域联合运 算外的一 对括号， 那是不可 86 少的， 此运算在 SUM函 数中算1 个参数， 当SUM 中的参数 多于30个 时可用此 法来减少 参数。 注意括号 及2个引 用间的空 格，交叉 70 引用 在SUM 函数中也 只算1个 参数，此 处实际运 算的返是回
其中的 (MOD(RO W(D92:D1 05),2)=0) 就是判别 是否偶数 行。
19600 11300
特别提 示：SUM 函数在绝 大多数的 情况下用 于数组公 式中只能 返回一个 值，以后 会讲到在 某些特殊 的函数组 合中
在多单元 格数组公 式中，可 在不同的 单元格返 回不同的 值，好象 是返回了 一个数 组，但那 只能在单 元格
不利因素 是，所有 的公式均 按 Loutus12-3的方 式处理， 很多excel 的表达式 就会出错 。
多条件计 数
A部门的男
性员工有
几人？
3
姓名
部门
性别
工资
(B92:B10 5="A")*(C 92:C105= "男")返回2 个逻辑数 组的乘 积，基于
A1
A
男
A2
B
女
TRUE*TR UE=1；
19600
如加IF函 数就可以 不用 NOT(NOT ())
提示：以 (C92:C10 5="女 ")+(D92:D 105>=150 0)这样的 形式表示 条件或的 关系，在 条件可能 同时满足 时要用 NOT(NOT ())转换
或 用IF函数 判别。否 则会多计 数量的。 统计偶数 行的工资 总和是多 少？
提示：我 们在使用 SUM函数 的数组公 式时，经 常只考虑 有1个参 数，其实 是忘了 SUM函数 最多可有 30个参数 。
D.以没有 打开的工 作薄的指 定表的指 定区域引 用作参数
公式为 =SUM('C :\excelho mefuncti on\[INDI RECT函 数的使 用.xls]Sh eet2'!$B: 600 $B)
数组公 式，不带 {、}号输 入，按 ctrl+shift +enter三 3500 键结束。
姓名 a1
a2 a3
#N/A
公式 {=SUM({" 姓名 ","a1","10 00",TRUE ,2000,FAL SE,1500}, H11:H14= "a2")} 错误值， 不管是在 引用、参 数、还是 在数组中 均返回错 误，此处 excel的帮 助中有错 误。
E.以(由多 个元素的 数组参数 产生的)单 元格区域 引用作 SUM函数 的参数
一般是
INDIRECT
函数和
OFFSET
函数才能
以多个数
组元素，
产生一系
列对多个
单元格区
域的引
用，返回
的是三维
的区域引
用，
SUM函数
只能对第1
个元素指
定的区域
求和，如
H64格的
公式。
61
用内嵌
SUMIF函
数代替就
正确了，
见H65格
。
130
F.以非数 字型文本 作参数的 方法 A中指出参 数或作为 参数的计 算表达式 为不是数 字型的文 本，返回 错误。
要解决直 接参数为 非数字的 问题，可 按图设置 。
G.SUM函 数在数组 公式中的 一些应用
#VALUE! 按图设置 后，就按 Lotus1-23的方式 忽略文本 。见H69 格。
基于：
FALSE*任
何数=0；
TRUE*任
何数=原来
的数
(B92:B10
5="A")*(C
92:C105= "女")为并 列条件， *D92:D10 5后就是满 足条件的 工资。 所有女性 员工的工 资和男性 员工工资 1500以上 的工资总 额是多 少？
NOT(TRU E)=FALS E； NOT(FAL SE)=TRU E； NOT(0)=T RUE； NOT(非0 数 值)=FALS 3000 E 900 1800 2500
中表现， 而不能继 续进行数 组运算的 。
Excelho me.net 中有很多 的相关帖 子，请大 家多看看 。
工资 1000
婚姻状况 TRUE
2000 FALSE 1500
#DIV/0!
B
C
D
10
20
30
11
21
31
12
22
32
13
23
33
14
24
34
15
25
35
102
102
如有2个 以上并列 条件，可 将几个条 件式相乘 。
引用中有 #N/A 错误值
数组中有 #VALUE! 错误值 参数或作 为参数的 计算表达 式为不是 数字型的 文本，返 回错误。 要不返回 错误用F中 提出的方 法。
直接用不 是数字的 文本作参 #VALUE! 数
作为参数 的计算表 达式的结 果为错误 #DIV/0! 值
以返回不 是数字的 文Leabharlann Baidu表达 式作为参 #VALUE! 数
如 果参数为 错误值或 为不能转 换成数字 的文本， 将会导致 错误。 下面作一 些详细的 分析：
A.对文本 、逻辑值 及错误值 的计算 对引用中 的文本、 数字型的 文本、逻 辑值忽略 不计算。
3500 公式 =SUM(H1 1:J14)， 只计单元 格中的数 值，不计 文本、逻 辑值
和I12格中 的文本 1000 对数组中 的文本、 数字型的 文本、逻 辑值忽略 不计。
和A部门工
资1500以
上的男性
员工总数
A8
A
男
1000 是多少？
A9
C
女
1000
3
因为
(C92:C10
5="女")和
(D92:D10
5>=1500)
可能同时
满足，所
以再用
NOT(NOT
())转换，
A10
D
男
2000 基于
A11
A
男
A12
B
男
A13
A
女
A14
A
女
多条件求
和
A部门女性
员工的工
资总额是
多少？
公式为 {=SUM((
H28:H34
,I34,J29:
K29),((H
29:K34
I:I)>12)*(
H29:K34
I:I),SUM:
SUMIF!H
191 29:H34)} 请注意上 面的数组 公式中 SUM函数 有3个参 数，第1个 是联合区 域引用， 第2个是交 叉区域引 用计算的 数组，第3 个是三维 区域引用 。 由于联合 区域和三 维引用区 域均没有 继续进行 数组运 算，所以 在SUM函 数的数组 公式中作 为单独的 参数是可 以的。
TRUE*FA LSE=0；
FALSE*F
1000 ALSE=0 所以此处 是逻辑与 的关系， 在excel的 IF函数的 条件中，0 表示 FALSE， 非0的数值 表示TRUE
1500 。
去除IF函
数可以简
A3
C
女
1000 化公式为
3
A、B两部
门的男性
员工有几
A4
D
女
800 人？
4
基于
TRUE+FA LSE=1；
H31:K32 和
I29:J34 相交的
B31:C32 区域。
A 1 2 3 4 5 6
公式为 {=SUM((( H29:K34 I:I)>12)*( H29:K34 42 I:I))} 实际计算 的是 I29:I34区 域大于12 的值的和
C.以三维 引用作参 数
公式为 =SUM(S UM:SUM IF!H29:H 34)，其 中 SUM:SU MIF!H29: H34为对 从SUM 工作表开 始至 SUMIF工 63 作表结束 的 H29:H34 的区域引 用。 象这样的 直接三维 引用不可 继续用于 数组计算 中。 象下面这 样的数组 公式为什 么是可以 正确运算 的？
参数或作 为参数的 计算表达 式为数字 型的文 本，转为 数值后计 算；
参数或作
为参数的
计算表达
式为逻辑
值时，
TRUE算
1，FALSE
算0。
公式为
=SUM(1
0,2>1,1>
2,TRUE,
FALSE,"
2","2"&"0
34 ")
B.以引用 的运算作 参数
其中的 2>1为逻 辑值 TRUE算 1，直接 参数 TRUE算 1，其他 的FALSE 算0，文 本2和表 达式文本 20转换后 计算。
实际相当 于 SUM(INDI RECT({"H 27:J31"})) ，即对 H27:J31区 域求和。 H54格是 将这样的 SUM函数 放在IF函 数中作为 参数，就 错误了，
102
0
因为 ROW()返 回的是一 个数组并 不是一个 数值，这 样在数组 公式中 INDIRECT 函数返回 的并不是 一个二维 的区域引 用， 而是三维 的区域引 用(第3维 的尺寸是 1)，所以 SUM的计 算出错。 可以将公 式改为I54 格的样子 (本论坛的 会员提出 的方法， 先用SUM 函数将 ROW函数 的数组变 为数值)， 或用 SUMIF代 替(见J54 格)。
引用了 C:\excelh omefunct ion\INDI RECT函 数的使 用.xls 工作薄 Sheet2 表的整个 B列。
只要路径 所指定的 文件存在 就不需要 打开文 件，如不 存在就返 回错误。
E.以(由一 个元素的 数组参数 产生的)单 元格区域 引用作 SUM函数 的参数 SUM函数 不作为其 他函数的 参数可以 使用，见 公G5式4格为， =SUM(IN DIRECT(" H"&ROW( )/2&":J"&R OW()/2+4) )
FALSE+F ALSE=0；
A5
B
女
TRUE+TR 2000 UE=2
A6
C
男
A7
D
男
而 (B92:B10 5="A")和
(B92:B10 5="B")不 可能同时 满足，所 以此处是 条件或的 2500 关系， 再乘以 (C92:C10 5="男")作 为并列条 1500 件。
A部门所有
女性员工
SUM函 数的使 用
语法：
SUM(参数 1,参数 2,...,参数
30) 结果：返 回所有参 数中的数 字之和。 说明：参 数最多只 能有30 个，并且 可以省略 (即,,间没 有参数或 最后有一 个,)；
参 数可以为 引用、返 回数值和 文本及逻 辑值的计 算表达式 、数组；
参 数如为引 用，可以 是区域联 合、区域 交叉、三 维区域引 用，只要 引用不再 参与数组 运算就可 以；
区域联合
区域交叉
联合区域 不能在数 组公式中 继续进行 计算。 交叉引用 在数组公 式中可以 可以继续 进行计算 。
请注意区 域联合运 算外的一 对括号， 那是不可 86 少的， 此运算在 SUM函 数中算1 个参数， 当SUM 中的参数 多于30个 时可用此 法来减少 参数。 注意括号 及2个引 用间的空 格，交叉 70 引用 在SUM 函数中也 只算1个 参数，此 处实际运 算的返是回
其中的 (MOD(RO W(D92:D1 05),2)=0) 就是判别 是否偶数 行。
19600 11300
特别提 示：SUM 函数在绝 大多数的 情况下用 于数组公 式中只能 返回一个 值，以后 会讲到在 某些特殊 的函数组 合中
在多单元 格数组公 式中，可 在不同的 单元格返 回不同的 值，好象 是返回了 一个数 组，但那 只能在单 元格
不利因素 是，所有 的公式均 按 Loutus12-3的方 式处理， 很多excel 的表达式 就会出错 。
多条件计 数
A部门的男
性员工有
几人？
3
姓名
部门
性别
工资
(B92:B10 5="A")*(C 92:C105= "男")返回2 个逻辑数 组的乘 积，基于
A1
A
男
A2
B
女
TRUE*TR UE=1；