Excel Sum函数基本及与if、OffSet、Match、Right组合使用方法

　　Sum函数是 Excel 中用于求和的函数，它最多可以有 256 个参数；参数既可以数字，又可以是单元格的引用、数组、逻辑与能转为数值的文本；如果引用单元格或数组中含用空单元格或不能转为数字的文本将被忽略。

　　Sum函数除对参数求和外，还可以与其它函数组合使用，例如与If函数、OffSet函数、Match函数、Left函数、Right函数、Index函数等组合使用；Sum函数与If函数组合最常见，用于求满足指定条件的和；Sum函数与OffSet函数、Match函数组合，可用于动态求和。

 

　　一、Excel Sum函数语法

　　1、表达式：SUM(Number1, [Number2], ...)

　　中文表达式：求和（数字1，[数字2]）

 

　　2、说明：

　　A、Sum函数至少要有一个数字，最多只能有 256 个数字。

　　B、参数 Number 可以是数字外，还可以是对单元格区域的引用、数组、逻辑值（True 或 False）。如果引用单元格区域或数组中有文本，能转为数值的自动把它转为数值并计入求和，不能转数值的忽略。如果引用单元格区域或数组中有逻辑值，它们将被忽略；如果直接把逻辑值作为独立参数写到Sum函数中，True 被计入 1，False 被计入 0。不能转为数值的文本不能作为独立的参数写到Sum函数中。

 

 

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

　　（一）参数为数字的实例

　　1、选中 A1 单元格，输入公式 =SUM(2,6,18)，按回车，返回求和结果 26；操作过程步骤，如图1所示：

图1

　　2、公式 =SUM(2,6,18) 共有三个参数，Sum函数把它们相加，即 2 + 6 + 18 = 26。

 

　　（二）参数为引用单元格区域的实例

　　1、选中 B1 单元格，把公式 =SUM(A1,A2,A3) 复制到 B1，按回车，返回 21；双击 B2，把公式改为 =SUM(A1:A3)，按回车，同样返回 21；再次双击 B1，把公式改为 =SUM(21,True)，按回车，返回 22；双击 B1，在 True 后添加 False，按回车，仍然返回 22；双击 B1，把公式改为 =SUM(21,True,"Sum函数")，按回车，返回错误值 #VALUE!；操作过程步骤，如图2所示：

图2

　　2、Sum函数引用单元格时，既可以一个个写，又可以合在一起写（前提是单元格连续）；一个个写的如 =SUM(A1,A2,A3)，连续写的如=SUM(A1:A3)。

　　3、Sum函数引用单元格中有逻辑值和文本时，逻辑值和文本都被忽略，如 =SUM(A1:A3) 返回求和结果 21；当把逻辑值写到 Sum 中作为独立参数时，True 算 1，如 =SUM(21,True) 返回求和结果 22，False 算 0，如 =SUM(21,True,False) 返回求和结果 22。当把文本写到 Sum 中作为独立参数时，直接返回值错误 #VALUE!。

 

　　（三）参数为数组的实例

　　1、选中 A1 单元格，把公式 =SUM({3,8,15}) 复制到 A1，按回车，返回 26；双击 A1，把公式改为 =SUM({3,8,15,True})，按回车，同样返回 26；再双击 A1，把公式改为 =SUM({3,8,15,True,false})，按回车，仍然返回 26；双击 A1，把公式改为 =SUM({3,8,15,True,"Sum函数"})，按回车，也返回 26；操作过程步骤，如图3所示：

图3

　　2、当Sum函数的参数为数组时，文本和逻辑值都被忽略，如演示中的 True、False 和 "Sum函数" 都没有计入求和。

 

 

　　三、Excel Sum函数与其它函数的组合使用实例

　　（一）Sum + If函数

　　1、假如要求所有女装的销量之和。选中 F11 单元格，把公式 =SUM(IF(C2:C10="女装",F2:F10,0)) 复制到 F11 单元格，按 Ctrl + Shift + 回车，返回求和结果 4284；操作过程步骤，如图4所示：

图4

 

　　2、公式说明：

　　A、公式 =SUM(IF(C2:C10="女装",F2:F10,0)) 是一个数组公式，因此要按 Ctrl + Shift + 回车。

　　B、IF(C2:C10="女装",F2:F10,0) 的条件 C2:C10="女装" 返回一个数组；执行时，第一次从 C2 到 C10 中取 C2，如果为“女装”，则返回 F2 至 F10 中的 F2（即返回 329），否则返回 0；第二次取 C3，如果为“女装”，返回 F3（即返回 569），否则返回 0；按照这样的顺序一直取到 C10，最返回数组 {329,569,638,0,528,563,0,765,892}。

　　C、则公式变为 =SUM({329,569,638,0,528,563,0,765,892})，最后用 Sum 求和，所以返回 4284。

　　提示：如果求和区域超过一列，只要它们连续，也可以把它们写在一起作为 If 的条件；假如要在 C 列到 E 列求分类为“女装”的销量之和，公式可以这样写： =SUM(IF(C2:E10="女装",F2:F10,0))。

 

　　（二）Sum + Right函数

　　1、假如要计算服装表中所有T恤的销量之和。选中 F11 单元格，把公式 =SUM((RIGHT(B$2:B$10,2)="T恤")*(F$2:F$10)) 复制到 F11，按Ctrl + Shift + 回车，返回求和结果 3482；操作过程步骤，如图5所示：

图5

 

　　2、公式说明：

　　A、公式中 B$2:B$10 用于以数组形式返回 B2 至 B10 的“产品名称”，B$2 表示对列相对引用、对行绝对引用，即往下拖时，列不变行变，如 B2 会变为 B3、B4、……；B$10 与 B$2 一样。F$2:F$10 用于以数组形式返回 F2 至 F10 的“销量”。

　　B、Right函数用于从右边截取字符，RIGHT(B$2:B$10,2) 用于从右边截取 B 列“产品名称”的两个字符；当公式执行时，第一次从 B2 至B10 中取出 B2（即“长袖白衬衫”），然后从右边开始截取两个字符，即截取“衬衫”；第二次取出 B2 并从右边截取两个字符，一直取到B10，最后返回数组{"衬衫";"T恤";"T恤";"衬衫";"T恤";"衬衫";"T恤";"T恤";""衬衫"}。

　　C、则 RIGHT(B$2:B$10,2)="T恤" 变为 {"衬衫";"T恤";"T恤";"衬衫";"T恤";"衬衫";"T恤";"T恤";""衬衫"}="T恤"，接着从数组左边开始位次取出每一个元素，并与“T恤”比较，如果等于“T恤”，返回 True，否则返回 False，最终返回数组 {False;True;True;False;True;False;True;True;"False}。

　　D、F$2:F$10 返回数组 {329,569,638,897,528,563,982,765,892}，则公式变为
=SUM(({False;True;True;False;True;False;True;True;False})*({329,569,638,897,528,563,982,765,892}))。

　　E、接着，把两个数组对应的元素相乘，相乘时 True 被转为 1，False 被转为 0，则公式变为 =SUM({0;569;638;0;528;0;982;765;0}),最后用 Sum 求和返回 3482。

 

　　（三）Sum + OffSet + Match函数

　　1、假如要根据任意月份计算各类服装当月的销量和。选中 B12 单元格，把公式 =SUM(OFFSET(B1,MATCH(A12,A$2:A$9,0),,1,4)) 复制到B12，按回车，返回“1月”各类服装的销量之和；双击 A12 单元格，把“1月”改为“2月”，单击 B12，返回“2月”各类服装的销量之和；再把“3月”改为“8月”，则返回“8月”的各类服装销量之和；操作过程步骤，如图6所示：

图6

 

　　2、公式说明：

　　A、MATCH(A12,A$2:A$9,0) 用于返回 A12 在 A2 至 A9 中的位置，例如 A12 为“1月”，则返回的位置为 1（以 A2 为起始），第三个参数 0 表示精确匹配，即 A2 至 A9 中数据要等于“1月”。

　　B、OFFSET(B1,MATCH(A12,A$2:A$9,0),,1,4) 变为 OFFSET(B1,1,,1,4)，OffSet 中，B1 为基准单元格，第二个参数 1 为行号，第三个参数为列号（省略了），第四个参数 1 为返回引用单元格区域的行高，第五个参数 4 为返回引用单元格区域的列宽；由于行号为正，因此返回B1 下面的单元格区域，又因为行号为 1，所以返回 B1 下边的一行（即第二行）；又因为省略了列号，且要返回的单元格区域行高为1、列宽为 4，因此返回 B2:E2。

　　C、则公式变为 =SUM(B2:E2)，再用 Sum 求和，最后返回 1614；当 A12 为其它月份时也是一样的道理。