求和 – Excel22

Excel 应用SUMIFS函数对某一区域内满足多重条件的单元格求和

SUMIFS函数的功能是对某一区域内满足多重条件的单元格进行求和。SUMIFS和SUMIF的参数顺序不同。具体而言，sum_range参数在SUMIFS中是第一个参数，而在SUMIF中则是第三个参数。如果要复制和编辑这些相似函数，需要确保按正确顺序放置参数。SUMIFS函数的语法如下：

SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)

其中参数sum_range表示要求和的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。参数criteria_range1、criteria_range2、…表示计算关联条件的1~127个区域。参数criteria1、criteria2、…表示数字、表达式、单元格引用或文本形式的1~127个条件，用于定义要对哪些单元格进行求和。

【典型案例】现有某地区周一至周四的上、下午的雨水、平均温度和平均风速的测量值，本例中要对平均温度至少为20℃且平均风速小于10km/h的这些天的总降雨量求和。本例的原始数据如图13-81所示。

在A9单元格中输入公式“=SUMIFS（B2：E3，B4：E5，”>=20″，B6：E7，”<10″）”，对平均温度至少为20℃且平均风速小于10km/h的这些天的总降雨量求和，结果如图13-82所示。

【使用指南】只有当sum_range中的每一单元格满足为其指定的所有关联条件时，才对这些单元格进行求和。sum_range中包含TRUE的单元格计算为1；sum_range中包含FALSE的单元格计算为0。与SUMIF函数中的区域和条件参数不同的是，SUMIFS中每个criteria_range的大小和形状必须与sum_range相同。可以在条件中使用通配符问号（？）和星号（*）。问号匹配任一单个字符；星号匹配任一字符序列。如果要查找实际的问号或星号，则在字符前键入波形符（~）。

图13-81　原始数据

图13-82　计算结果

Excel 应用SUMIF函数按给定条件对指定单元格求和

SUMIF函数的功能是按照给定条件对指定的单元格进行求和。其语法如下：

SUMIF(range,criteria,sum_range)

参数range是要根据条件计算的单元格区域，每个区域中的单元格都必须是数字和名称、数组和包含数字的引用，空值和文本值将被忽略。参数criteria为确定对哪些单元格相加的条件，其形式可以为数字、表达式或文本。参数sum_range为要相加的实际单元格（如果区域内的相关单元格符合条件）。如果省略参数sum_range，则当区域中的单元格符合条件时，它们既按条件计算，也执行相加。

【背景知识】参数sum_range与区域的大小和形状可以不同。相加的实际单元格通过以下方法确定：使用sum_range中左上角的单元格作为起始单元格，然后包括与区域大小和形状相对应的单元格，如表13-3所示。

表13-3　确定相加的实际单元格

【典型案例】某班级六名男同学分成两组，进行一分钟定点投篮比赛。A组成员有张辉、徐鑫和郑明涛，B组成员有王明、毛志强和李卫卫。比赛结束后，又来两名同学，分别是李波和王赐，也进行了定点一分钟投篮。现在要计算A组和B组的进球总数及其他人员的进球总数。本例的原始数据如图13-79所示。

步骤1：在D2单元格中输入公式“=SUMIF（A2：A9，”A*”，B2：B9）”，计算A组同学的进球总数。

步骤2：在D3单元格中输入公式“=SUMIF（A2：A9，”B*”，B2：B9）”，计算B组同学的进球总数。

步骤3：在D4单元格中输入公式“=SUM（B2：B9）-SUMIF（A2：A9，”a*”，B2：B9）-SUMIF（A2：A9，”b*”，B2：B9）”，计算其他同学的进球总数。计算结果如图13-80所示。

图13-79　原始数据

图13-80　计算结果

【使用指南】SUMIF函数的主要进行有条件的求和，可以在条件中使用通配符问号（？）和星号（*）。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符（~）。

Excel 应用SUM函数求和

SUM函数的功能是计算某一单元格区域中所有数字之和。其语法如下：

SUM(number1,number2,...)

其中参数number，number2，…是要对其求和的1~255个参数。

【典型案例】某地发生水灾，当地一家公司为发生水灾的地方组织捐款，计算这个公司三个分公司的捐款数额，以及公司总的捐款数额。本例的原始数据如图13-77所示。

图13-77　原始数据

步骤1：在B6单元格中输入公式“=SUM（B2：B5）”，计算公司一部的捐款总额。

步骤2：在D6单元格中输入公式“=SUM（D2：D5）”，计算公司二部的捐款总额。

步骤3：在F6单元格中输入公式“=SUM（F2：F5）”，计算公司三部的捐款总额。

步骤4：在G2单元格中输入公式“=SUM（B6，D6，F6）”，计算公司总捐款额，最终结果如图13-78所示。

图13-78　计算捐款数额

【使用指南】SUM函数的用途比较广泛。在学校中可以求学生的总成绩，在会计部门可以求账务的总和等。对SUM函数来说，直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算。如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。如果参数为错误值或为不能转换为数字的文本，将会导致错误。

Excel 对24小时制时间求和：SUM函数

如果需要对24小时制的时间求和，可通过“SUM”函数实现，下面举例说明。

01　在“B2:B6”单元格区域中输入需要计算的时间值。

02　在单元格中输入公式：=SUM(B2:B6)，然后按下“Enter”键确认即可。

alt

Excel 一键自动求和

对于一些常用的函数，例如求和函数SUM、求平均值函数AVERAGE等，可利用“自动求和”按钮来快速输入。下面以求平均值函数AVERAGE为例，讲解“自动求和”按钮的使用方法。

01　选中要显示计算结果的单元格，切换到“公式”选项卡，在“函数库”组中单击“自动求和”按钮右侧的下拉按钮，在弹出的下拉列表中单击“平均值”命令。

02　此时，在工作表中可看到Excel会自动选择参加运算的参数。

03　如果确认当前选中的运算参数，按下“Enter”键即可显示运算结果。如果需要重新选择运算参数，可通过拖动鼠标的方式进行选择，本例为重新选择运算参数。

04　选择好运算参数后按下“Enter”键即可。

alt

Excel 能否对数据隔行求和？

图7.110中A1：F21区域包含了10个业务员在4个季度中的销量和退货数量，现要求计算所有人员的销量总和及退货量总和。

图7.110 销量与退货表

解题步骤

本例中数据的规律是：所有销量存放在偶数行，所有退货存放在奇数行。因此使用IF+MOD组合区分奇数行与偶数行，再用SUM函数求和即可，操作步骤如下。

1.在I1单元格输入以下公式：

2.在I2单元格输入以下公式：

以上两个公式都是数组公式，必须输入公式后按组合键＜Ctrl+Shift+Enter＞结束，否则无法得到正确结果。图7.111是以上两个公式的计算结果，偶数行的数据汇总后等于43623，奇数行的数据汇总结果为3293。

图7.111 统计总销量和退货数量

知识扩展

1.MOD函数用于计算一个数值除以另一个数值后的余数，当除数是2时，它用于判断被除数是奇数还是偶数。MOD函数的语法如下：

其中第一参数是被除数，第二参数是除数，计算结果是余数。

=MOD（154，3）——154/3的商为51，余数为1，因此公式的结果为1；

=MOD（154，2）——154/2的商为77，余数为0，因此公式的结果为0；

=IF（MOD（A1，2）=0，＂偶数＂，＂奇数＂）——当A1是偶数时，它除以2的余数为0，那么此时公式的计算结果为“偶数”，否则计算结果为“奇数”。

如果MOD的第一参数包含多个值，那么公式的计算结果也包含多个值。

2.公式“=SUM（IF（MOD（ROW（C2：F21），2）=0，C2：F21））”的含义是：如果C2：F21区域中某个单元格的行号是偶数，那么对这个单元格的值汇总。

3.假设要对偶数列的数据求和，那么将公式中的ROW函数替换成COLUMN函数即可。

Excel 能按单元格背景色分类求和吗？

基于某些原因，图 7.71 中的业绩使用多种颜色加以标示，现要求对不同颜色的区域分类汇总，Excel如何才能实现？

图7.71 用颜色标示的业绩表

解题步骤

Excel的所有工作表函数都无法识别颜色，只有早期版本的宏表函数get.cell可以做到，因此本例使用get.cell函数搭配SUMIF函数解题，具体步骤如下。

1.选择C2单元格，按组合键＜Ctrl+F3＞，弹出“名称管理器”对话框。

2.单击“新建”按钮弹出“新建名称”对话框，将名称设置为“颜色”，将引用位置设置为“=get.cell（63，B2）”，设置界面如图7.72所示。

图7.72 新建名称“颜色”

3.单击“确定”按钮，返回工作表界面。

4.在C1中输入“颜色”，在C2中输入公式“=颜色”，然后双击C2的填充柄从而取得B2：B11区域中每个单元格的颜色编码，效果如图7.73所示。

5.在F2单元格中输入公式“=SUMIF（$C$2：$C$11，颜色，$B$2：$B$11）”，公式的结果是无背景色的业绩之和。

6.将F2单元格的公式向下填充到F4，公式会分别计算出无色、红色和黄色三种颜色的业绩之和，效果如图7.74所示。

图7.73 识别数据源中的颜色编码

图7.74 对所有背景色的业绩分类汇总

知识扩展

1.只要使用了宏表函数，工作簿就不能保存为xlsx格式，否则定义的名称会自动丢失，从而导致公式计算出错。

2.get.cell函数的第一参数是63时表示计算单元格的颜色编码，0表示无色、3表示红色、6表示黄色、55表示蓝色、50表示绿色……get.cell函数的第二参数表示要在其中提取颜色的单元格，只能是单个单元格。

3.定义名称“颜色”前选择了 C2，名称的引用对象是“=get.cell（63，B2）”，公式的计算对象B2是相对引用，位于C2左方，因此名称“颜色”的含义就是提取左边一个单元格的颜色编码。

在C2单元格输入公式“=颜色”可以生成B2的颜色编码，在F2单元格输入公式“=颜色”则可以生成E2的颜色编码。

4.C 列作为公式“=SUMIF（$C$2：$C$11，颜色，$B$2：$B$11）”的辅助区域，它提供了参考数据，没有C列的值就无法计算出业绩汇总，因此C列的值不能删除。如果觉得C列多余，可以隐藏C列。

Excel 文本型数字能否参与求和？

SUM 函数用于对数值、数组或区域中的数值求和，如果求和对象是文本型数字，则会求和失败。例如，在图7.50中A1是数值格式，B1单元格是文本格式，因此C1的公式“=SUM（A1：B1）”求和时会忽略B1的值。

图7.50 sum函数对文本型数字求和失败

当数据源区域太大，无法确定区域中是否有文本时，如何才能确保公式求和的准确性呢？

解题步骤

SUM函数无法直接对文本型数字求和，通过乘以1将文本型数字转换成数值，然后用数组公式就可以正常求和。以汇总图7.51中的捐款数量为例，具体步骤如下。

图7.51 待汇总的捐款表

1.在A12单元格输入“求和”。

2.在B12单元格输入公式“=SUM（B2：B11*1）”，然后按组合键＜Ctrl+Shift+Enter＞结束，当编辑栏中的公式前后自动生成了“{}”符号时表示数组公式输入成功，此时不管B2：B11区域中是否存在文本型数字，公式都能确保运算结果一定准确，结果如图7.52所示。

图7.52 对包含文本型数字的区域求和

知识扩展

1.本例中B4和B8是文本格式，因此直接用公式“=SUM（B2：B11）”求和只能得到2785，较正确结果3475的差值刚好等于B4加B8之和，表明公式“=SUM（B2：B11）”运算结果出错是由文本格式所导致。

2.使用SUM函数对包含文本型数字的区域求和时，要得到正确的结果必须使用数组公式，即输入公式后按＜Ctrl+Shift+Enter＞组合键结束。组合键的使用方法是先同时按下Ctrl和Shift键，然后在不松开的情况下按Enter键，最后三者同时松开。

3.使用SUMPRODUCT函数代替SUM函数求和可以免按＜Ctrl+Shift+Enter＞组合键。由于工作中绝大多数公式都是普通公式，不需要按＜Ctrl+Shift+Enter＞组合键，因此不常用数组公式者及 Excel 新手会经常忘记按键方式，导致公式计算结果出错，此时用 SUMPRODUCT 函数代替SUM函数能防错。使用SUMPRODUCT函数的公式如下：