分类：数据运算篇

图7.136中F1：G9区域包含所有产品的单价，A1：C7区域是近期的采购记录。

现要求一次性计算所有采购产品的金额之和，存放在C8单元格。

解题步骤

Excel中按条件求和的函数是SUMIF，按多条件求和的函数是SUMIFS。本例不需要多条件求和，而是同时对多个对象求和，因此采用 SUMPRODUCT+SUMIF 的方式实现，具体公式如下（见图7.137）：

知识扩展

1.SUMIF函数用于按条件求和，第一参数是用于条件计算的单元格区域；第二参数代表条件，当第二参数是单个值时 SUMIF 的计算结果也是单个值，第二参数包含多个条件时 SUMIF的计算结果也是多个值；第三参数代表实际参与求和的区域，它必须与第一参数的高度、宽度一致。第三参数是可选参数，当忽略第三参数时表示对第一参数求和。

本例中条件为B2：B7，代表同时对6个条件分别求和，产生6个求和结果。当F2：F9区域中的值等于B2：B7中的任意一个单元格的值时，那么就对G2：G9区域中对应位置的单价求和。由于F2：F9区域中每个产品都只出现一次，因此求和的结果其实就等于该产品的单价。

简言之，表达式“SUMIF（F2：F9，B2：B7，G2：G9）”的功能是查找B2：B7区域中每个产品对应的单价，B2：B7区域有多少个单元格，表达式“SUMIF（F2：F9，B2：B7，G2：G9）”就生成多少个单价。

2.SUMPRODUCT的功能是对数组参数中的每个元素相乘，最后再求和，简称计算乘积之和。当只有一个参数时，SUMPRODUCT的功能等同于SUM函数。

本例中表达式“SUMIF（F2：F9，B2：B7，G2：G9）”用于生成B2：B7区域中每个产品的对应单价，C2：C7则是对应的采购数量，SUMPRODUCT函数将两者逐一相乘，然后汇总即为所有产品的金额之和。

3.通过以下3个步骤可清晰地了解本例公式的运算过程。

在图7.138中，选择D2单元格后输入公式“=SUMIF（$F$2：$F$9，B2，$G$2：$G$9）”，然后将公式向下填充到D7，公式会生成6个产品的单价。

在图7.139中，E2的公式是“=D2*C2”，然后将公式向下填充到E8，公式的作用是计算每个产品的数量与单价之积，即每个产品的金额。

在图7.140中，公式“=SUM（E2：E7）”用于计算每个产品的金额之和。

以上3个运算过程等同于“=SUMPRODUCT（SUMIF（F2：F9，B2：B7，G2：G9），C2：C7）”的功能。其中SUMPRODUCT包含了单价乘以数量再汇总两项工作。

某公司的职员编号中包含部门名称缩写、进厂日期和序号，编号效果如图7.128所示。

其中，部门编号与部门名称的对应关系见表7.4。

现要求利用编号中的入厂日期计算每个职工的年假天数。年假天数的计算方法见表7.5。

解题步骤

本例的问题相当复杂，因此可以分多个步骤来书写公式，将一个公式拆分成多个公式书写更容易理解公式的计算过程，同时也不易出错。具体步骤如下。

1.在E1：H1区域分别输入“进厂日期”、“完善日期”、“进厂月数”、“年假天数”。

2.在E2单元格输入以下公式，此公式用于提取两个“-”之间的字符，提取结果见图7.129。

职员编号中的日期没有完整的年份，因此需要通过公式来完善日期。

3.在F2单元格输入以下公式：

有了完整的入职日期后就可以计算职工的入职时间了，单位为月。

4.在G2单元格输入以下公式：

有了入职时间，接下来就可以通过IF函数计算职工可享用年假的天数。

5.在H2单元格输入以下公式：

在以上4个公式的协同工作下，职工的年假天数会自动产生，效果如图7.130所示。

6.选择E2：H2，然后按下填充柄向下拖到第12行，从而让公式计算所有职工的年假天数。

假设只用一个公式计算年假天数，那么公式会相当长，不管是新手还是老手都不建议直接一步完成。如果要求只能在单列中完成，那么可以定义名称，通过名称简化公式，操作步骤如下。

1.删除E：H区域，然后在E1中输入“年假天数”。

2.选中E2单元格，按组合键＜Ctrl+F3＞打开“名称管理器”。

3.单击“新建”按钮，弹出“新建名称”对话框，然后将名称设置为“时间”，将引用位置设置为以下公式：

4.单击“确定”按钮保存设置，然后返回工作表界面。

5.在E2单元格输入以下公式：

6.双击E2单元格的填充柄，将公式向下填充到E12，最终效果如图7.131所示。

知识扩展

1.公式“=TRIM（MID（SUBSTITUTE（A2，＂-＂，REPT（＂ ＂，99）），99，99））”的含义是将职员编号中的“-”替换成99个空格，然后用MID函数从替换后的字符串中的第99位开始提取99个字符，从而得到空格与日期组成的字符串，最后用Trim函数去除空格，只保留日期。

公式中的REPT函数用于将指定的字符重复显示若干次，第一参数是要重复显示的字符，第二参数用于控制重复次数。例如“=REPT（＂AB＂，3）”的计算结果为“ABABAB”。

SUBSTITUTE函数用于将字符串中的部分字符替换成新字符，在本例中的作用是将“-”替换成99个空格。

由于替换后的日期前后各有99个空格，因此使用MID函数从替换后的字符串中第99位开始提取99个字符只能提取到空格和日期，不包含前面的部门编号和后面的序号，此时去除所有空格就只剩下日期了。

2.职工编号中的日期包含两种：一种是2000年以后的，第一个数字是0或1；另一种是2000年以前的，第一个数字是9，基于此规律，使用IF函数判断第一个数字是否等于9即可，如果等于9则在日期前添加19，否则添加20。

此处要注意的一点是LEFT函数提取出来的数字是文本格式的，因此和数字9比较时需要将数值9也转换成文本，在9的前后添加半角引号

3.DATEDIF函数计算两个日期的时间差时，两个日期都必须是日期格式的，“20150314”这种格式的数值不是日期格式，因此需要使用TEXT函数转换成“0000-00-00”这种格式后再参与计算。

4.当IF函数要求两个条件同时成立才算满足条件时，需要使用AND函数限制这两个条件。

生产线有15个职工，他们的今日日产量数据如图7.126所示。

领导要求计算产量高于平均值的员工占所有员工的比例，应如何计算？

解题步骤

本例可以使用COUNTIF函数计算高于平均值的人数，然后再用COUNTA函数计算总人数，两者相除即为最终结果。

公式如下：

公式结果为0.466，表示高于平均值的人数占46%左右，效果如图7.127所示。

知识扩展

1.AVERAGE函数用于计算一个或多个区域的平均值，它有1～255个参数，第一个参数是必选参数，其他所有参数都是可选参数。

2.COUNTIF函数用于计算符合条件的单元格数量，在本例中的条件是大于平均值，当平均值是一个数值时——如500——可以直接使用“＞500”作为条件，当平均值是通过表达式计算而来时，由于表达式不能放在引号中，因此本例只能使用“＂＞＂&AVERAGE（B2：B16）”作为条件，而不是“＂＞AVERAGE（B2：B16）＂”。

3.表达式“COUNTA（B2：B16）”用于计算B2：B16区域的非空单元格数量。

身份证号码中包含了持有人的性别信息，能否在输入身份证号码后让性别自动产生？

解题步骤

身份证号码的倒数第2位数字代表性别，该值是奇数时代表持有人的性别为男，否则为女。基于此原则，利用MID函数提取身份证号码中的第17位数，然后使用MOD函数判断该值的奇偶性即可。以图7.124为例，获取性别的操作步骤如下。

1.在C2单元格录入以下公式：

2.双击C2单元格，使公式自动向下填充，生成的性别如图7.125所示。

知识扩展

1.表达式“MID（B2，17，1）”用于提取身份证号码中的第 17 位数字，MOD 函数则用于计算此数字除以2的余数，余数只有0或1两种情况，当余数是1时，IF函数的第一参数返回True，表示IF函数的条件成立，因此公式“=IF（MOD（MID（B2，17，1），2），＂男＂，＂女＂）”的最终结果为“男”。

2.我国的身份证号码最先使用的是15位数字，然后15位和18位并存，现在已经只有18位的身份证号码，因此不需要再考虑15位的问题。

3.当多个函数嵌套时，公式中会有多级括号，Excel总是从里层括号向外开始运算，因此理解公式的含义时也应该从里向外。对于同一层级的表达式，则遵循四则混合运算的顺序，同级别的先左后右，加、减属于同级别运算，乘、除属于同级别运算，但高于加、减运算。

图7.117中部分队员参与了多项比赛，现要求对队员编号。当一个队员参与了多项比赛时，分别编号为1、2、3……第二个队员又重新从1开始编号。

解题步骤

本例宜用COUNTIF函数解题，具体操作步骤如下。

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

2.双击A2单元格，使公式向下填充到底，公式产生的编号如图7.118所示。

知识扩展

1.COUNTIF函数用于计算符合条件的单元格数量，此函数在以前的案例中多次用到。本例的重点在于相对引用的数据源区域“$B$2：B2”，将公式向下填充时引用对象在逐个增加，因此在数据源中符合条件的单元格数量也相应地增加。

当公式在A2时，数据源区域是B2：B2，因此符合条件的单元格只有一个；当公式在A3时，数据源区域是B2：B3，因此符合条件的单元格有两个；当公式在A4时，数据源区域是B2：B4，因此符合条件的单元格有3个；当公式在A5时，数据源区域是B2：B5，此时条件已经变成B5，符合条件的单元格只有B5一个单元格，因此编号重新从1开始，而不是4。

2.假设要求按人数生成编号，即同一人不管参加多少个项目都使用相同的编号，那么应该改用以下公式：

公式的含义在前面的案例中有详细说明，公式的结果如图7.119所示。

图7.115中B列是职员的身份证号码，由于身份证号码中包含了号码所有者的出生日期，因此要求利用身份证号码计算身份证持有人的年龄。

解题步骤

身份证号码中的第7位到第14位属于出生日期，因此使用MID函数从身份证号码中提取生日，然后用它与今天的日期比较就得到年龄了。具体操作步骤如下。

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

2.双击C2单元格的填充柄，从而使公式自动向下填充，公式的计算结果如图7.116所示。

知识扩展

1.身份证号码的前6位代表地区，地区右边的8位代表出生年月日，因此本例采用MID函数从身份证的第7位开始，一共提取7位数字出来，然后通过TEXT函数转换成日期样式。

例如，第一个身份证号的生日是19770316，使用TEXT函数转换后的结果为“1977-03-16”。如果不转换格式，DATEDIF函数无法识别此日期。

2.TODAY函数用于生成今天的日期，没有参数。

3.DATEDIF 函数用于计算两个日期之间的间隔时间，单位可以是年，也可以是月或天。DATEDIF函数的语法如下：

第一参数代表起始日期，第二参数代表结束日期，第三参数用于指定日期单位。

第三参数的取值范围与含义见表7.30

表7.3中说明了DATEDIF函数第三参数的6种用法，实际常用的只有第一种和第二种。

=DATEDIF（＂2012-3-1＂，＂2016-4-2＂，＂Y＂）——计算结果4，代表两个日期相差4年；

=DATEDIF（＂2012-3-1＂，＂2016-4-2＂，＂M＂）——计算结果49，代表两个日期相差49个月；

=DATEDIF（＂2012-3-1＂，＂2016-4-2＂，＂YD＂）——计算结果32，代表两个日期相差32天，忽略年，相当于只计算3月1日到4月2日之间的日期差

在本例中，DATEDIF的作用在于计算身份证号中的日期与今天之间的年份差异。

在图7.112中，省名所在单元格已经合并，现要求按省名对该省的参赛队员编号，其中第一个省编号A，第一个队员编号A1，然后向上递增。第二个省的第二个队员则编号为B2。

解题步骤

要根据省名数量产生升序的字母，重点在于COUNTA和CHAR两个函数的应用，而累加字母右边的数字编号则重点在于MID函数的应用。具体操作步骤如下。

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

2.将公式向下填充到A15，公式产生的编号如图7.113所示。

知识扩展

1.COUNTA函数用于计算区域中非空单元格的数量，以图7.113为例，“=COUNTA（B2：B2）”、“=COUNTA（B2：B3）”、“=COUNTA（B2：B4）”的计算结果都是 1，因为合并区域中只有左上角单元格才有数据。

“=COUNTA（B2：B6）”、“=COUNTA（B2：B7）”、“=COUNTA（B2：B8）”的计算结果则为2，因为它的计算对象包含两个合并区域。

2.CHAR 函数可以根据字符编码生成对应的字符，如 49886 代表“罗”，那么公式“=CHAR（49886）”的运算结果就是“罗”。

字母A的编码是65，字母B的编号是66，字母C的编码是6……基于此规律，只要使用65开头的自然数编码作参数，CHAR函数就可以生成A、B、C这种升序的序号。图7.114中展示了表达式“64+COUNTA（$B$2：B2）”在不同单元格的计算结果变化过程，读者可以根据图 7.114理解字母编号的生成规律。

3.由于公式从 A2 开始，因此表达式“IF（B2＜＞＂＂，1，MID（A1，2，2）+1）”的含义是：当公式所在单元格的右边单元格非空时，那么产生字符1，否则提取上方单元格字母以外的数字并累加1。由于本例的数据源少，不需要用到两位字母，因此在设置公式时可以取巧，用 MID 函数从第 2位开始取值，提取长度也等于2位。在实际工作中需要根据数据的变化修改公式。