办公软件 > Excel > 正文

Excel SumIf函数条件带?/*、Average和数组多条件求和及两区域不相同的13个实例

亮术网 2019-05-04 本网原创

在 Excel 中,SumIf函数用于单条件求和,它共有三个参数,分别为条件区域、条件和实际求和区域;其中实际求和区域可以省略,若省略,则把条件区域作为求和区域;在条件中可以使用数学符号、通配符和函数;条件区域与实际求和区域既可以相等也可以不相等。

一般情况下,SumIf函数只能有一个条件,但可以用数组组合条件加Sum函数实现SumIf函数多条件求和;另外,还可以用SumIf函数求指定数值范围的和;此外,还能用SumIf函数只求到当前行的和。

 

一、Excel SumIf函数语法

1、表达式:SUMIF(Range, Criteria, [Sum_Range])

中文表达式:SUMIF(条件区域,条件,[求和区域])

 

2、说明:

A、如果省略 Sum_Range,则在 Range 中求和;如果有 Sum_Range,则在 Sum_Range 中求和。求和区域中若有空值、逻辑值和不能转为数字的文本,它们将被忽略。

B、条件 Criteria 可以是数字、字符(如“女装”)、表达式(如 ">=10"、"<>1")、单元格引用(A1)、函数(如 NOW());条件中有文本、逻辑值或数学符号都要用双引号括号起来,单独数字不需用双引号括起来。在条件中,可以使用通配符问号(?)和星号(*),问号表示一个字符,星号表示任意一个或多个字符;如果要查找问号或星号,要使用转义字符~,例如查找问号,需这样表示 ~?。

C、Sum_Range 与 Range 形状大小可以不相同,求和的实际单元格区域以 Sum_Range 左上角的单元格为起点一直到它右下角的单元格,Range 取与 Sum_Range 对应的单元格,具体见实例。另外,Sum_Range 与 Range 形状大小不相同时,执行速度比相同时慢。

D、当SumIf函数匹配的字符串超过 255 个字符时,将返回值错误 #VALUE!。

 

二、Excel SumIf函数基本使用实例

(一)省略实际求和区域的实例

1、假如要求服装销量大于 700 的所有服装销量之和。选中 F11 单元格,把公式 =SUMIF(F2:F10,">700") 复制到 F11,按回车,返回求和结果 3536;操作过程步骤,如图1所示:

Excel SumIf函数使用举例

图1

2、公式 =SUMIF(F2:F10,">700") 省略了实际求和区域 Sum_Range,因此把条件区域 Range(F2:F10)作为求和区域。">700" 是条件,意思是把 F2 至 F10 中每个数值与 700 比较,如果大于 700,则计入求和。

提示:条件 ">700" 也可以这样写:">"&"700",把大于号与数值分开写,然后用 & 连接。

 

(二)有实际求和区域的实例

1、假如要求所有男装的销量之和。选中 F11 单元格,把公式 =SUMIF(C2:C10,"男装",F2:F10) 复制到 F11,按回车,返回求和结果 1879;操作过程步骤,如图2所示:

Excel SumIf函有实际求和区域的实例

图2

2、公式 =SUMIF(C2:C10,"男装",F2:F10) 中,C2:C10 为条件区域,条件为“男装”,F2:F10 为实际求和区域,意思是:如果 C2:C10 中为“男装”,则把 F2:F10 与之对应数值计入求和;例如 C5 中为“男装”,则把 F5 计入求和。

 

(三)条件带通配符问号(?)或星号(*)的实例

(1)条件带通配符问号(?)

1、假如要求以“T恤”结尾并且“T恤”前只有两个字的服装销量之和。选中 F11 单元格,把公式 =SUMIF(B2:B10,"??T恤",F2:F10) 复制到 F11,按回车,返回求和结果 2844;操作过程步骤,如图3所示:

Excel SumIf函数条件带通配符问号(?)的实例

图3

2、公式 =SUMIF(B2:B10,"??T恤",F2:F10) 中的条件为 "??T恤",条件中共有两个问号,每个问号表示一个字符,意思是找出 B2:B10 中由四个字组成,前两个字可以为任意字符,后两个字为“T恤”的服装。

 

(2)条件带通配符星号(*)

1、假如要分别求以“T恤”结尾、以“粉红”开头、以“粉”开头且以“衬衫”结尾的服装销量之和。选中 F11 单元格,把公式 =SUMIF(B2:B10,"*T恤",F2:F10) 复制到 F11,按回车,返回求和结果 3482;双击 F11 单元格,把公式中的条件改为 "粉红*",按回车,返回求和结果 1784;再次双击 F11,把公式中的条件改为 "粉*衬衫",按回车,返回求和结果 1784;操作过程步骤,如图4所示:

Excel SumIf函数条件带通配符星号(*)的实例

图4

2、公式说明:

A、公式 =SUMIF(B2:B10,"*T恤",F2:F10) 中的条件为 "*T恤",条件中的 * 表示任意字符,可以是一个也可以是多个;条件的意思是找出以“T恤”结果的服装。

B、公式 =SUMIF(B2:B10,"粉红*",F2:F10) 中的条件为 "粉红*",意思是找出以“粉红”开头的服装。

C、公式 =SUMIF(B2:B10,""粉*衬衫",F2:F10) 中的条件为 ""粉*衬衫",意思是找出以“粉”开头且以“衬衫”结尾的服装。

 

(四)条件中带函数的实例

1、假如要求大于等于服装平均销量的所有服装销量之和。选中 F11 单元格,把公式 =SUMIF(F2:F10,">="&AVERAGE(F2:F10)) 复制到 F11,按回车,返回求和结果 3536;操作过程步骤,如图5所示:

Excel SumIf函数条件中带函数AVERAGE的实例

图5

2、公式 =SUMIF(F2:F10,">="&AVERAGE(F2:F10)) 中的条件为 ">="&AVERAGE(F2:F10),条件中的 AVERAGE(F2:F10) 用于求服装的平均销量。

 

 

三、Excel SumIf函数 Sum_Range 与 Range 大小形状不同的实例

〈一〉Sum_Range 与 Range 都为一列

(一)Sum_Range 在上,Range 在下

1、选中 F11 单元格,把公式 =SUMIF(C5:C10,"男装",F2:F5) 复制到 F11 单元格,按回车,返回求和结果 1226;操作过程步骤,如图6所示:

Excel SumIf函数 Sum_Range 与 Range 大小形状不同的实例

图6

2、公式说明:

实际求和区域 Sum_Range 为 F2:F5,条件区域 Range 为 C5:C10,两个区域大小形状不相同,F2:F5 在上,C5:C10 在下,求和时把 C5与 F2 对应,C6 与 F3 对应,其它的以此类推,相当于把 C5:C10 上移;C5:C10 中只有 C5 和 C8 为“男装”,它们对应 F2 和 F5,求和算式为 329 + 897 = 1226。

 

(二)Sum_Range 在下,Range 在上

1、选中 F11 单元格,把公式 =SUMIF(C2:C6,"男装",F5:F10) 复制到 F11,按回车,返回求和结果 982,操作过程步骤,如图7所示:

Excel SumIf函数 Sum_Range 在下,Range 在上的实例

图7

2、公式说明:

实际求和区域 Sum_Range 为 F5:F10,条件区域 Range 为 C2:C6,F5:F10 在下,C2:C6 在上,求和时把 C2 与 F5 对应,C3 与 F6 对应,其它的以此类推,相当于把 C5:C10 下移;C2:C6 中只有 C5 为“男装”,它对应 F8,因此返回 F8 的数值 897。

 

(三)Sum_Range 与 Range 起始位置相同

1、选中 F11 单元格,把公式 =SUMIF(C2:C6,"男装",F2:F10) 复制到 F11,按回车,返回求和结果 897,操作过程步骤,如图8所示:

Excel SumIf函数起始位置相同的实例

图8

2、公式说明:

实际求和区域 Sum_Range 为 F2:F10,条件区域 Range 为 C2:C6,两个区域起始位置相同,只是 F2:F10 比 C2:C6 多四个单元格,求和时多出的单元格被忽略,而 C2:C6 中只有 C5 为“男装”,C5 对应 F5,因此返回 F5 的数值 897。

 

〈二〉Sum_Range 与 Range 都为多列

(一)Sum_Range 在上,Range 在下

1、选中 E10 单元格,把公式 =SUMIF(B5:C9,">700",D2:E6) 复制到 E10,按回车,返回求和结果 582;操作过程步骤,如图9所示:

Excel SumIf函数 Sum_Range 与 Range 都为多区域

图9

2、公式说明:

实际求和区域 Sum_Range 为 D2:E6(在上),条件区域为 B5:C9(在下);它们的对应关系为:B5 对应 D2、C5 对应 E2,B6 对应 D3、C6 对应 E3,其它的以此类推,相当于把 B5:C9 上移;在 B5:C9 中,只有 B6 和 C9 大于 700,它们分别对应 D3 和 F6,求和算式为 253 + 329 = 582。

 

(二)Sum_Range 在下,Range 在上

1、选中 E10 单元格,把公式 =SUMIF(B2:C6,">700",D4:E8) 复制到 E10,按回车,返回求和结果 876;操作过程步骤,如图10所示:

Excel SumIf函数 Sum_Range 与 Range 不对应

图10

2、公式说明:
实际求和区域 Sum_Range 为 D4:E8(在下),条件区域为 B2:C6(在上);它们的对应关系为:B2 对应 D4、C2 对应 E4,B3 对应 D5、C3 对应 E5,其它的以此类推,相当于把 B2:C6 下移;在 B2:C6 中,只有 B6 大于 700,它对应 D8,而 D8 的数值为 876,所以求和结果返回 876。

 

四、Excel SumIf函数扩展使用实例

(一)SumIf函数多条件求和

1、假如要求服装名称以“白色和黑色”开头的所有服装销量之和。选中 F11 单元格,把公式 =SUMIF(B2:B10,{"白色*","黑色*"},F2:F10) 复制到 F11,按回车,返回求和结果 1535;双击 F11,把公式改为 =SUM(SUMIF(B2:B10,{"白色*","黑色*"},F2:F10)),按回车,返回求和结果 3282;操作过程步骤,如图11所示:

Excel SumIf函数多条件求和

图11

 

2、公式说明:

A、=SUMIF(B2:B10,{"白色*","黑色*"},F2:F10) 中的条件为{"白色*","黑色*"},它是一个数组,由两个元素组成,是要求服装名称以“白色和黑色”开头服装的销量之和,但公式执行后,只返回第一个元素 "白色*" 作为条件的求和值,第二个元素的求和值被忽略,因此,只能求出以服装名称“白色”开头的销量之和;如果要求满足两个条件的和,Excel2007 及以上版本可以用 SumIfs函数,Excel2007 以下版本可以用 Sum + SumIf,也就演示的第二个公式。

B、公式 =SUM(SUMIF(B2:B10,{"白色*","黑色*"},F2:F10)) 用Sum函数把 SumIF 返回的分别满足条件 "白色*" 和 "黑色*"的求和结果再次求和。

 

(二)SumIf函数求指范围的和

1、假如要求销量在 600 到 800 的所有服装销量之和。选中 F11 单元格,把公式 =SUMIF(F2:F10,">=600")-SUMIF(F2:F10,">800") 复制到 F11,按回车,返回求和结果 1403,操作过程步骤,如图12所示:

Excel SumIf函数求指范围的和

图12

2、公式 =SUMIF(F2:F10,">=600")-SUMIF(F2:F10,">800") 是把销量把大于等于 600 的求和结果减去销量大于 800 的求和结果,恰好求出销量在 600 到 800 之间的所有服装销量之和。

 

(三)SumIf函数求和到当前行

1、假如要求价格大于 55 的服装销量和且只求到当前行。选中 G2 单元格,把公式 =SUMIf(E$2:E2,">=55",F$2) 复制到 G2,按回车,返回求和结果 329;再次选中 G2,把鼠标移到 G2 左下角的单元格填充柄上,按住左键,往下拖,拖到第3行则求和到第3行,拖到第4行则求和第4行;操作过程步骤,如图13所示:

Excel SumIf函数求和到当前行

图13

2、公式说明:

A、公式 =SUMIF(E$2:E2,">=55",F$2) 中 E$2 表示对列相对引用对行绝对引用(2 前的 $ 表示绝对引用),即往下拖时列和行都不变,往右拖时,列变行不变;E2 表示对列和行都是相对引用,往下拖时,列不变行变,E2 变自动变为 E3、E4、……。

B、F$2 为实际求和区域,它后面其实省略了 F2,完整的应该是 F$2:F2;在引用方面 F$2 与 E$2 是一个意思,当往下拖到 F4 时,F$2 省略的是 F4。

恒星彩票网址