Excel%20应用技巧.docVIP

　　 Excel XP函数运用实例六则 一：身份证中信息的提取 1.持证人性别的判断：我们知道，旧身份证(15位)号码的最后1位数值，表示持证人的性别，若为奇数则为男，若为偶数则为女。我们利用Excel函数，对身份证号码进行适当处理，即可自动判断持证人的性别，并将结果填入相应的单元格中(此处假定身份证号码保存在B2单元格中，结果填入C2单元格中)。 （1）利用函数的嵌套来判断。在C2单元格中输入公式：=IF(RIGHT(B2，1)=1，男， IF(RIGHT(B2，1)=3，男， IF(RIGHT(B2，1)=5，男， IF(RIGHT(B2，1)=7，男， IF(RIGHT(B2，1)=9，男，女)))))。 （2）综合运用函数来判断。在C2单元格中输入公式：=IF(OR(RIGHT(B2，1)=1，RIGHT(B2，1)=3，RIGHT(B2，1)=5，RIGHT(B2，1)=7，RIGHT(B2，1)=9)，男，女)。 在上面两个公式中，前一个是利用IF函数的嵌套来实现的，由于函数最多只能嵌套7层，因而有一定的局限性，且比较麻烦；后一个公式由于采用了OR函数，既没有层数的限制，也简单一些。 （3）运用其他函数来判断。我们在C2单元格中输入公式“=IF(RIGHT(B2，1)/2=INT(RIGHT(B2，1)/2)，女，男)”或“=IF(MOD(RIGHT(B2，1)，2)=0，女，男)”即可实现。 2.持证人出生时间的提取：同样，旧身份证号码中，第7—12位数字代表的是持证人的出生年月日，我们只要在D2(假定将出生时间存放在D2单元格中)单元格中输入公式：=19MID(B2，7，2)年MID(B2，9，2)月MID(B2，11，2)日，即可从B2单元格的身份证号码中将出生日期自动提取出来，并以中文习惯形式显示出来(如“1963年03月04日”等)。 小技巧：执行“视图→工具栏→公式审核”命令，打开“公式审核”工具条(图1)，按最右边的“公式求值”按钮，在随后弹出的“公式求值”对话框中，反复按“求值”按钮，即可逐项核查运算的结果。 注释：上述公式中所用的函数：①IF──逻辑函数：判断一个条件是否满足，如果满足返回一个值，如果不满足返回另一个值。②OR──逻辑函数：如果任一参数值为TRUE，则返回TRUE；只有当所有参数值均为FALSE，才返回FALSE。③RIGHT──文本函数：从一个字符串的最后一个字符开始返回指定个数的字符(也可以使用RIGHTB函数)。④MID──文本函数：从文本字符串中指定的起始位置起返回指定长度的字符。⑤MOD──数学函数：返回两数相除的余数。⑥INT──数学函数：将数值向下取整为最接近的整数。 二：批量插入固定字符 大家知道新的身份证号码(18位)将旧身份证号码的年份由2位改为4位。现在，我们要将年份的前两位(19)插入旧身份证号码中，如果一个一个地去插入，显然既麻烦又容易出错，如果利用Excel的函数来做，则既方便又准确。 此处假定旧身份号码保存在B列中，插入“19”后的号码暂时保存在C列中。我们在C2单元格中输入公式：=LEFT(B2，6)19RIGHT(B2，9)。再次选中C2，将鼠标移到右下角成“细十字”状(我们称之为“填充柄”)，按住左键向下拖拉，即可将上述公式复制到C列的以下单元格中(在复制过程中，系统会智能化地改变相应的单元格)。 小技巧：选中C列，按“复制”按钮，再选中B列，执行“编辑→选择性粘贴”命令，打开“选择性粘贴”对话框，选中“粘贴”下面的“数值”选项，然后按“确定”按钮，再将C列删除，就可以将C列的值正确地复制到B列中，从而不影响原有表格的结构。 注释：上述公式中用到一个新函数：LEFT──文本函数：从一个字符串的第一个字符开始返回指定个数的字符(也可以使用LEFTB函数)。 三：学生成绩的统计 图2 是一张教师(特别是班主任)非常熟悉的“学生成绩统计表”，以前在统计各项数据时，大家通常采用的是笔算或按计算器的办法来进行的。现在可以用Excel来帮我们快速、准确地完成这些繁杂的统计工作。 1.总分的统计：选中H3单元格(用于存放学生丁1总分的单元格)，输入公式：=SUM(C3:G3)，按下Enter键后，丁1的总分即计算出来，并填入H3单元格中。用填充柄将该公式复制到H4—H47单元格中(假定该班级有45名同学)，将其他同学的总分统计出来。 用类似的方法，可以将某一学科的总分统计出来，并填入第48行相应的单元格中。 2.平均分的计算：选中C49单元格，输入公式：=AVERAGE(C3:C47)，按下Enter键后，语文学科的平均分即计算出来。 3.最高(低)分的统计：选中C50单元格，输入公式=MAX(C3:C47)，挑出语文学科的最高分；选中C51单元格，输入公式：