Excel 能否根据采购数量和单价一步汇总所有金额?

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

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

图7.136 采购表

解题步骤

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

图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.138 利用SUMIF函数计算产品单价

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

图7.139 分别计算每个产品的单价与数量乘积

在图7.140中,公式“=SUM(E2:E7)”用于计算每个产品的金额之和。

图7.140 汇总所有产品的金额

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

Excel 可否将包含文本的复杂表达式转换成值?

图7.133中A列的值用于描述房屋面积或长宽高规格,其中文本都存放在【】中,数字和运算符在【】以外。是否有办法将这些包含数学表达式和文本的字符串转换成计算结果呢?

图7.133 包含字符描述的表达式

解题步骤

将这种复杂表达式转换成值必须借助宏表函数EVALUATE才能实现,具体操作步骤如下。

1.选择B2单元格,按组合键<Ctrl+F3>,弹出“名称管理器”对话框。

2.单击“新建”按钮弹出“新建名称”对话框,然后将名称设置为“结果”,将引用位置设置为以下公式:

设置界面如图7.134所示。

图7.134 定义名称

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

4.在B2单元格输入公式“=结果”,然后双击B2的填充柄,将公式向下填充到B4,公式的计算结果如图7.135所示。

图7.135 忽略文本将表达式转换成值

知识扩展

1.EVALUATE是宏表函数,不能在单元格中直接调用,必须在名称中调用此函数,然后在单元格中调用名称执行运算。

2.本例中要计算的表达式存在干扰符,.EVALUATE 函数无法直接将它转换成值,好在干扰符都有明显的规律,只要将干扰符替换成空文本即可执行后期的数据运算。

本例中所有干扰符都在【】中,【】也属于干扰符。本例的处理方法是使用 SUBSTITUTE函数将【替换成“*istext(""”,将】替换成“"")”,那么原本的“【客厅2个】”将会被转换成“*istext("客厅2个")”。

ISTEXT 函数用于判断参数是否为文本,由于本例【】中间的字符全是文本,因此 ISTEXT的计算结果为True,True参与数值运算时当作1处理,因此“*istext("客厅2个")”的运算结果其实是“*1”。同理,其他所有干扰符都会被转换成“*1”,因此“12【长】*5【宽】*9.5【高】”通过替换后相当于“12*1*5*1*9.5*1”,也就相当于“12*5*9.5”。

换言之,两个SUBSTITUTE函数的嵌套应用可以将干扰符转换成“*1”,又由于任何数乘以1都等于原值,因此最终结果是忽略了所有干扰符。

3.如果本例中的【】替换成其他符号,处理方法也一样,替换公式中对应的符号即可。

4.如果没有干扰符,只是“1*2+5”、“(12+8)*5-5/2”这种标准的表达式,那么在定义名称时可以改用“=EVALUATE(A2)”。

Excel 如何实现将中文翻译为英文?

Excel的审阅选项卡中有一个翻译工具,它一次只能翻译一个单词,如果要求批量翻译整句文字,Excel能否实现呢?

解题步骤

有个翻译软件有一个在线翻译工具,其网址为http://fanyi.youdao.com。

Excel提供了一个WEBSERVICE函数,可以读取该网页代码,因此借用WEBSERVICE函数可以执行中英翻译。

假设要翻译A2:A4区域的语句,具体操作步骤如下。

1.在B2单元格中输入以下公式:

2.双击B2单元格的填充柄,使公式自动向下填充,翻译结果如图7.132所示。

图7.132 批量翻译中文成英文

知识扩展

1."http://fanyi.youdao.com/translate?&i="&A2&"&doctype=json",这是一个名为“有道”的在线翻译网址,其中A2代表需要翻译的内容在A2单元格中,可以是中文也可以是英文。

2.WEBSERVICE函数用于读取网站内容,它本身没有翻译功能,本例利用有道软件的在线翻译工具执行翻译,然后使用WEBSERVICE函数读取翻译结果。

3.WEBSERVICE函数读取的网页内容包含翻译结果,但并不只有翻译结果,还存在其他的干扰符,需要使用函数去除干扰符。

以 “路 在 脚 下” 为 例,翻 译 后 产 生 的 网 页 代 码 是“{"type":"ZH_CN2EN","errorCode":0,"elapsedTime":0,"translateResult":[[{"src":" 路 在 脚 下","tgt":"At the foot of the road in"}]]}”,只有“At the foot of the road in”才是真正的翻译结果,必须使用函数去除干扰符。

观察以上字符串可以发现规律:目标在“""tgt"":""”与“""}]]}”之间,因此使用SUBSTITUTE函数将两者都替换成1000个空格,然后使用MID函数从第1000位开始提取长度为1000的字符串,最后删除空格,剩下的字符即为翻译结果。

以上算法和案例174中提取两个“-”之间的日期的思路完全一致。

Excel 可否根据职员编号计算职员能享受的年假天数?

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

图7.128 职工信息表

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

表7.4 编号与部门对应关系表

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

表7.5 年假计算办法

解题步骤

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

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

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

图7.129 提取进厂日期

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

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

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

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

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

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

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

图7.130 分四步计算出年假天数

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

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

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

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

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

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

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

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

图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函数限制这两个条件。

Excel 如何计算高于平均产量的人数占总人数的比例?

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

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

图7.126 日产量表

解题步骤

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

公式如下:

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

图7.127 统计结果

知识扩展

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

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

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

Excel 怎样根据身份证号码计算其男女性别?

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

解题步骤

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

图7.124 职工信息表

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

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

图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总是从里层括号向外开始运算,因此理解公式的含义时也应该从里向外。对于同一层级的表达式,则遵循四则混合运算的顺序,同级别的先左后右,加、减属于同级别运算,乘、除属于同级别运算,但高于加、减运算。

Excel 如何设计工作表目录?

要进入某个工作表,需要找到这个工作表并单击工作表名称。当工作簿中有太多的工作表时,查找工作表相当费时,因为Excel的工作表名称不支持搜索功能。

是否可以新建一个名为“目录”的工作表,然后在此表中创建所有工作表的目录呢?

解题步骤

Excel的一切工作表函数都无法生成活动工作簿中的所有工作表名称,而比较古老的宏表函数GET.WORKBOOK却可以,因此本例借用GET.WORKBOOK实现需求,具体操作步骤如下。

1.打开需要创建目录的工作簿,然后新建一个工作表,且将该表移到最前面,并命名为“目录”,此时工作表分布状态如图7.120所示。

图7.120 新建“目录”工作表

2.选择A1单元格,按下组合键<Ctrl+Shift+Enter>,打开“名称管理器”,然后单击“新建”按钮,弹出“新建名称”对话框。

3.将名称设置为“第N个工作表”,将引用位置设置为以下公式:

设置界面如图7.121所示。

图7.121 创建名称

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

5.在A1单元格输入以下公式:

6.将公式向下填充,有多少个工作表就填充多少个单元格,填充后会产生活动工作簿中的所有工作表目录,效果如图7.122所示。

图7.122 工作表目录

7.单击A6单元格,Excel会马上激活“财务表”,且选中“财务表”中的A1单元格。

8.返回“目录”工作表,单击A7单元格,Excel会马上激活“出货表”,且选中“出货表”中的A1单元格。

知识扩展

1.宏表函数GET.WORKBOOK的功能是获取与工作簿相关的约一百项信息,当参数的值为1时表示获取所有工作表的名称。

由于表达式“GET.WORKBOOK(1)”可以生成一组名称,包含所有工作表,因此要取得单个名称就需要借用INDEX函数。

本例中的公式“=INDEX(GET.WORKBOOK(1),ROW(A1))”在向下填充时,ROW的参数A1会变成A2、A3……ROW(A1)也因此会生成1、2、3这种序号,所以整个公式的结果就是第一个工作表名称、第二个工作表名称、第三个工作表名称……当填充到超过工作表数量时会产生错误值。

2.宏表函数不能直接写在单元格中,只能在名称中调用宏表函数,然后在单元格中调用名称。

3.当工作簿中使用了宏表函数后,此工作簿不能保存为xlsx格式。

4.HYPERLINK 是链接函数,用于打开指定的文件、网站或激活指定的单元格,本例中用它激活每个工作表的A1单元格,从而实现目录功能。HYPERLINK函数的语法如下:

HYPERLINK(link_location,friendly_name)

第一参数代表链接对象,必须遵循严格的命名方式;第二参数代表要显示的字符,可以随意指定。本例中名称“第N个工作表”产生的结果是“[疑难170.xlsm]生产表”这种形式的,不符合单元格引用规则,因此需要在后方添加叹号和单元格地址。

5.目录中显示了工作簿名称,如果只需要工作表名称,那么可以使用FIND函数查找“]”的位置,然后用MID函数提取该位置右边的所有字符,具体的公式如下:

=HYPERLINK(第N个工作表&"!a1",MID(第N个工作表,FIND("]",第N个工作表)+1,99))此公式生成的目录效果如图7.123所示。

图7.123 不包含工作簿名称的目录

Excel 可否对重复出现的姓名生成编号?

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

图7.117 参赛队员信息表

解题步骤

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

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

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

图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.119 按人数生成的编号

Excel 能用身份证号码计算出年龄吗?

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

图7.115 职员信息表

解题步骤

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

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

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

图7.116 根据身份证号码计算年龄

知识扩展

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

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

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

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

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

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

表7.3 unit参数说明

表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的作用在于计算身份证号中的日期与今天之间的年份差异。

Excel 能否按双条件对选手编号?

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

图7.112 参赛选手信息表

解题步骤

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

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

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

图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理解字母编号的生成规律。

图7.114 CHAR函数的参数变化过程

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