一、SUM函数——最容易被小看的求和函数

1.函数技能

SUM函数的主要技能就是求和。这个函数在绝大多数读者朋友心目中，都戴着 Excel最简单的函数（没有之一）的帽子。但是，看似简单的东西，我们往往会忽略其更深层次的功能。所以，简单的求和我们就不介绍了，直奔其拓展应用。

2.拓展应用

在第一章第二节里，我们知道了在对同一工作簿下多个工作表的同一单元格进行求和时，可以使用类似“=SUM('1月:12月'!B2)”的公式。而同样的道理也适用于对多个工作表的多个单元格进行求和。比如，我们要对表Sheet1～表Sheet3中所有的A1:A3单元格、A6单元格以及B8:D11单元格（共计39个单元格）求和，就可以通过以下公式实现：

=SUM(Sheet1:Sheet3!A1:A3,Sheet1:Sheet3!A6,Sheet1:Sheet3!B8:D11)

二、SUMIF函数——专攻单条件求和

提问：在销售汇总表（见图4-8）中，如何统计“南区”和“北区”的合计金额？

图4-8 销售汇总表

1.函数技能

SUMIF函数是一个专门针对满足某（单一）条件的数据求和而生的函数。当用户依次指定条件区域、求和条件、求和区域后，即可计算满足条件的数据之和。

2.语法格式

SUMIF(条件区域,求和条件,求和区域)

（1）求和条件参数（以下简称“条件参数”）可以为文本、数值、单元格地址或公式等。但是，当条件参数为长度超过15位的文本型数字时，就会存在统计误差风险，相关原理将在下文的“注意事项”中举例说明。

（2）条件参数默认为等于状态。例如，当需要满足的条件为（等于）北区时，只需要在条件参数输入"北区"，而不能输入"=北区"或="北区"。

（3）条件区域和求和区域可以是同为纵向（列），也可以同为横向（行）。

3.提问解答

现在我们来看图4-8中南北分区汇总的汇总公式，根据SUMIF的技能及语法格式，我们可知（见图4-9）：

C13单元格的公式为：=SUMIF($B$4:$B$11,"北区",C4:C11)

C14单元格的公式为：=SUMIF($B$4:$B$11,"南区",C4:C11)

执行行填充后，可完成2012年数据的计算。

图4-9 SUMIF函数统计南北区合计

正如前面提到的，SUMIF函数的条件参数是默认等于状态。那么当我们需要满足的条件为不等于、大于、小于、大于等于以及小于等于的时候，该怎么处理呢？

方法是用连接符号“&”将不等于（“<>”）、大于（“>”）、小于（“<”）、大于等于（“>=”）以及小于等于（“<=”）与相关的比较值连接起来进行反映。

例如，我们以条件为“不是（不等于)南区”的逻辑来设置图4-9中C13单元格的公式，就可以表达为（见图4-10）：=SUMIF($B$4:$B$11,"< >"&"南区",C4:C11)

图4-10 SUMIF函数统计非南区合计

4.拓展应用

（1）条件参数使用通配符的应用。

SUMIF函数的条件参数可以使用通配符。即条件参数中，可以用“*”代表任意多个连续的字符，或者用“?”代表任意一个字符。但是，SUMIF函数对于数值无法使用通配符。

这样，当我们需要对图4-8中的所有重庆客户求和时，就很方便了。

C15单元格的公式为（见图4-11）：=SUMIF($A$4:$A$11,"重庆*",C4:C11)

除了SUMIF函数外，可以使用通配符的还有SUMIFS函数、VLOOKUP函数、 COUNTIF函数等。

图4-11 SUMIF与通配符组合

（2）使用常量数组函数进行多条件求和。

有人可能问：SUMIF函数不是专攻单条件求和的吗，怎么又可以多条件求和了？

首先说明的是，这里的多条件仅仅是条件区域内的多条件。或者说，是条件区域内对满足多条件之一的单元格匹配的求和区域单元格进行求和。例如我们要统计图4-8中天津D公司及贵阳F公司（即使有多条天津D公司及贵阳F公司的记录）的销售额合计，用SUMIF函数也是可以做到的。

当然，在这种情况下，单凭SUMIF函数是搞不定的，我们需要借助常量数组和SUM函数进行处理（见图4-12）。

C16单元格的公式为：

=SUM(SUMIF($A$4:$A$11,{"天津D公司","贵阳F公司"},C4:C11))

该函数实际上等价于：

=SUMIF($A$4:$A$11,"天津D公司",C4:C11)+SUMIF($A$4:$A$11,"贵阳F公司", C4:C11)

需要说明的是，此时常量数组中的“｛｝”是直接录入的，而不是通过【Ctrl+Shift+Enter】组合键输入。

图4-12 SUMIF函数的单区域多条件求和

5.注意事项

SUMIF函数还有一些容易忽略的特点，需要提醒大家予以关注。

（1）条件参数如果是长度超过15位的文本型数字时，超过15位的部分将被默认为0。例如，图4-13中我们对物料代码为“12345678901234512345”的数量进行求和，我们会发现其结果为25，而并不是10。这就是因为物料代码的长度超过15位的部分，被默认为0。此时，前两条记录的代码都被定义为“12345678901234500000”，所以B5单元格的公式实际上判定前两条记录均满足条件，故结果为25（=10+15）。

图4-13 条件为长度超过15位的文本型数字时的误读

解决方案是：改用SUMPRODUCT函数（稍后将介绍）进行求和。

（2）条件区域与求和区域的对应关系。

前面所有的案例都有一个特点，即条件区域的第一个单元格与求和区域的第一个单元格都是在同一行。这也是SUMIF函数最常用的模式。

其实，求和区域(第三参数单元格区域)真正起作用的就是其左上角那个单元格。例如，以下几个公式其实是等效的。

C13单元格的公式1：=SUMIF(B4:B11,"北区",C4:C11)

C13单元格的公式2：=SUMIF(B4:B11,"北区",C4)

C13单元格的公式3：=SUMIF(B4:B11,"北区",C4:C11000)

从上面的公式我们可以看出，求和区域（第三参数单元格区域）起作用的就是左上角那个单元格。此单元格的作用是定位定点，只要有此定位点，SUMIF会自动以此单元格为原点，按照第一参数区域符合条件（区域跨度一致）的单元格的坐标，找到同样坐标位置的单元格，并对其数值求和。如前所述C13单元格公式：已知条件区域（B4:B11单元格）的（纵向）跨度为8，当求和区域的第一个单元格（C4单元格）确定后，就已经注定了整个求和区域的终点是以C4单元格为起点，（纵向）跨度为8的C11单元格，且当B4单元格满足条件时，参与求和的为C4单元格。同理，如果我们将求和区域的第一个单元格设置为C5，那么，与条件区域匹配的求和区域就应该是C5:C12单元格，且当B4单元格满足条件时，参与求和的为C5单元格（而不再是C4单元格），以此类推。

例如，新版销售汇总表格式如图4-14所示，此时要统计2012年北区的销售额，应该如何处理？

乍一看，这貌似是个多条件求和的问题。但是，我们也可以根据表中的规律，将其转变为单条件求和。这个规律就是，2012年的数据始终是在对应的条件区域单元格的右下一位。或者说，当在条件区域中找到满足条件的单元格时，需要参与求和的是求和区域中行号比其大1的单元格（见图4-15中底色相同的单元格）。这样，与条件区域（B4:B18单元格）对应的求和区域，就不再是同行号的D4:D18单元格，而应为D5:D19单元格。

D21单元格的公式为：=SUMIF(B4:B18,"北区",D5)

该公式的实质可以表达为：=SUMIF(B4:B18,"北区",D5:D19)

图4-14 新版销售汇总表

图4-15 SUMIF求和区域与条件区域的错位应用

三、SUMIFS函数——SUMIF函数的加强版

提问：在费用明细表（见图4-16）中，如何统计营销部的业务招待费总额？

图4-16 费用明细表

1.函数技能

SUMIF函数解决了单条件求和的问题，但是当遇到上面这种需要多条件求和的问题时，就需要它的升级版SUMIFS函数了。在二维报表（例如第五章第二节案例中的各种费用报表）中，如果你不习惯使用数据透视表，那么SUMIFS函数就是公式搭建的最佳选择。

2.语法格式

SUMIFS(求和区域,条件1区域,条件1,条件2区域,条件2,……)

虽然是SUMIF函数的升级版，但是其格式却和SUMIF函数有些不同。它是把求和区域放在了第一个参数的位置，后面的各个条件区域和条件的设置规则则和SUMIF函数基本一致。

此外，SUMIFS函数的条件参数也可以在除数值或文本型数值之外的情形下，使用通配符。

3.提问解答

现在我们来看图4-16中需要计算的营销部业务招待费。

首先，求和区域肯定是C4:C11单元格区域。其次，需要满足的第一个条件是部门为营销部，则条件1区域为部门字段所在的A4:A11单元格区域，条件1为“营销部”；以此类推，条件2的区域为费用类别所在的B4:B11单元格区域，条件2为“业务招待费”。这样，我们就可以得出计算公式了（见图4-17）。

C13单元格的公式为：=SUMIFS(C4:C11,A4:A11,"营销部",B4:B11,"业务招待费")

图4-17 SUMIFS函数统计营销部的业务招待费

4.注意事项

（1）和SUMIF函数一样，当SUMIFS函数中的某个条件为长度超过15位的文本型数字时，同样会导致函数存在错误风险（参见本节SUMIF函数的“注意事项”）。

（2）SUMIFS函数只能在Excel2007及以后的版本中使用。如果读者朋友使用的是Excel2007以前的版本，可以使用一个也能进行多条件求和的函数——SUMPRODUCT函数。

四、SUMPRODUCT函数——业余求和高手

提问：在A产品标准成本计算单（见图4-18）中，如何直接计算该产品的成本合计？

图4-18 A产品标准成本计算单

1.函数技能

关于该函数的技能，我们引用Excel关于该函数帮助的说法是：在给定的几组数组中，将数组间对应的元素相乘，并返回各乘积之和。直白一点的解释就是：例如数组1为A1:A3单元格，数组2为B1:B3单元格，则该函数可以直接计算出A1*B1+A2*B2+A3*B3的值。

帮助文件中并没有提到其求和功能，实际上它还擅长求和与统计个数，几乎囊括了常用求和系列函数的功能。但是，求和不是它的专业，它只是临时客串求和函数这个角色，不过其强大的功能足以让SUMIFS和COUNTIFS下岗失业。

2.语法格式

SUMPRODUCT(数组区域1,数组区域2,数组区域3,……)

（1）各数组区域的“户型”必须保持一致。例如：数组区域1为A1:A10单元格，则数组区域2、数组区域3……也必须是行号为1～10区域的列（例如C1:C10单元格）。如果数组区域1为A1:B10单元格，则数组区域2、数组区域3……也必须为行号从1～10区域的相邻的两列。

（2）各参数均应独立配置“( )”。

（3）如果只有一个参数（区域1），则功能等同于SUM函数。

3.提问解答

现在我们来看图4-18中需要计算的成本合计。

很明显，合计金额其实就是B4:B8单元格与C4:C8单元格各同行号单元格对应乘积的和。所以，套用到SUMPRODUCT函数的格式中，数组区域1为B4:B8单元格，数组区域2为C4:C8单元格。这样，我们的公式就出来了（见图4-19）。

C9单元格的公式为：=SUMPRODUCT(B4:B8,C4:C8)

图4-19 SUMPRODUCT函数的对应区域乘积求和应用

4.拓展应用

SUMPRODUCT函数可以作为SUMIFS的超级替身进行多条件求和。其语法格式为：

SUMPRODUCT((条件1区域="条件1")* (条件2区域="条件2")*（……）* 求和区域)

SUMPRODUCT((条件1区域="条件1")* (条件2区域="条件2")*（……）,求和区域)

其中：

（1）条件区域和条件之间需要逻辑判断符号（例如“=”“<>”等）连接。

（2）属于条件判断的参数需要单独配上“( )”,而求和区域可以不使用“( )”。

据此，即使在使用Excel2003的情况下，我们仍然能完成图4-16中的营销部业务招待费的统计（见图4-20）。

C13单元格的公式为：

=SUMPRODUCT((A4:A11="营销部")*(B4:B11="业务招待费")*C4:C11)

或=SUMPRODUCT((A4:A11="营销部")*(B4:B11="业务招待费"),C4:C11)

图4-20 SUMPRODUCT函数的多条件求和应用

SUMPRODUCT函数多条件求和时，求和区域之前使用“，”和“*”的区别：当拟求和的区域中无文本时两者无区别；当有文本时，使用“*”时会出错，返回错误值#VALUE!，而使用“，”时SUMPRODUCT函数会将非数值型的数组元素作为0处理，不会报错，故使用“，”容错能力更高。

需要提醒大家注意的是，SUMPRODUCT函数不能使用通配符。

五、ROUND函数——从根源上控制小数位数

提问：在税金统计表（见图4-21）中，根据价税合计除以1.17乘以0.17计算的增值税（已经通过设置单元格格式控制为2位小数），为什么与合计数存在误差？

注：0.24+0.07+0.34+0.17+0.14=0.96

图4-21 税金计算表

1.函数技能

ROUND函数可将某个数字四舍五入为指定的位数。因为通过单元格格式设定的小数位数只是按设定的位数四舍五入保留来显示（见图4-22）。我们在D1:D3单元格分别录入0.123 45，虽然通过单元格格式设置将其小数点控制在2位，但是从编辑栏我们可以看到其本质仍然是0.123 45。所以在D4单元格对D1:D3单元格进行求和时参与计算的是3个0.123 45，其和为0.370 355。控制小数位数后，显示为0.37，而不是我们希望的0.36（=0.12×3）。如果要想让数据彻底地放弃被舍掉的小数位数，就得用ROUND函数了。

图4-22 单元格设置控制小数位数背后的真相

2.语法格式

ROUND(待控制小数位数的数据,控制的小数位数)

3.提问解答

我们对计算税额的公式稍加处理，就可得到没有误差的合计数了（见图4-23）。

C4单元格的公式为：=ROUND(B4/1.17*0.17,2)

执行列填充后，合计数就显示为0.96了。

图4-23 ROUND函数完美锁定小数位数

六、MOD函数——余数计算器

1.函数技能

MOD函数是计算两个数据相除的余数的工具。一般用于判断具有固定周期、间隔期等规律的信息。在第五章第四节的案例中，我们将看到该函数的具体应用。

2.语法格式

MOD(被除数,除数)

其中，被除数和除数均可以为数值、单元格地址或公式等。

示例：=MOD(12,5)，结果为2。

=MOD(3*4,6)，结果为0。

七、INT函数——整数切割机

1.函数技能

INT函数是一个以只保留数据整数为己任的工具。该函数瞧不起优柔寡断、和稀泥的四舍五入思想，而是采用彪悍的一刀切作风——只要不够整数，通通都会被切割掉。

2.语法格式

INT(待切割的数据)

其中，待切割的数据可以为数值、公式或单元格地址等。

示例：=INT(3.99)，结果为3。

=INT(20/3)，结果为6。

八、ABS函数——绝对值转换器

1.函数技能

ABS函数可以将任何数值转换为其绝对值。

2.语法格式

ABS(待转换为绝对值的数据)

其中，待转换为绝对值的数据可以为数值、单元格地址或公式等。

示例：=ABS(5.30)，结果为5.30。

=ABS(-5.30)，结果为5.30。

使用ABS函数后，本章第一节图4-6、图4-7中的公式可以更加简化。其中图4-6中的公式表达如下。

E3单元格公式为：=IF(D3="预算外费用",D3,IF(ABS(D3)<=2%,"正常",""))

九、SUM(IF)数组公式——单条件区域求和数组函数

首先需要说明的是，SUM(IF)并不是一个函数，而是一个数组公式。由于本书并不过多涉及数组公式，所以我们将其作为数组公式的代表，在数学函数章节做一个简单的介绍。

提问：在车辆燃油统计表（见图4-24）中，如何统计行政部一季度燃油费合计？

图4-24 车辆燃油统计表

如果我们需要对满足条件的一个连续区域进行求和，机械的办法是求和区域的各列（行）分别使用SUMIF函数求和，再汇总。但是如果要求和的区域达到一定数量时，这个叠加的方案就很费事了。这个时候，我们就需要用数组公式SUM（IF）了，其语法格式为：

｛=SUM(IF(条件区域=满足的条件,求和区域))｝

此公式等价于：｛=SUM((条件区域=满足的条件)*(求和区域))｝

当然，判断条件除了等于外，也可以是不等于、大于等于或小于等于。

这个公式的核心部分语法和IF函数雷同。唯一需要强调的是，数组公式最外面的“｛｝”不能手工录入，而是在设置完公式主体“=SUM(IF(条件区域=满足的条件,求和区域))”后，用鼠标选定（涂黑）该公式，再同时按下【Ctrl+Shift+Enter】键确定，“｛｝”外套就自动穿上了。这样，我们就可以解决前面的提问了（见图4-25）。

B10单元格的公式为：{=SUM(IF(B4:B9="行政部",C4:E9))}

图4-25 SUM（IF）数组公式的应用

在第五章第三节的案例中，我们将会看到这个数组公式的具体应用。