ExcelXP函数运用实例四则??实例一：身份证中信息的提取??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单元格中)单元格中输入公式：=19&MID(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)&19&RIGHT(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单元格，输入公式：=MIN(C3:C47)，挑出语文学科最低分。4.各分数段学生人数的统计：分别选中C52和C57单元格，输入公式：=COUNTIF(C3:C47，">=90")和=COUNTIF(C3:C47，"<50")，就统计出了语文学科大于等于90分和低于50分的学生人数；分别选中C53、C54、C55、56单元格，依次输入公式=COUNTIF(C3:C47，">=80")-COUNTIF(C3:C47，">=90")、=COUNTIF(C3:C47，">=70")-COUNTIF(C3:C47，">=80")、=COUNTIF(C3:C47，">=60")-COUNTIF(C3:C47，">=70")、=COUNTIF(C3:C47，">=50")-COUNTIF(C3:C47，">=60")，即可统计出语文学科其他各分数段的学生人数。5.名次的排定：选中I3单元格，输入公式：=RANK(H3，$H$3:$H$47)，按下Enter键后，丁1同学总分的名次即排定，并填入I3单元格中。用填充柄就可以将I3单元格中的公式复制到I4—I47单元格中，用于排定其他学生的名次。对公式中引用的参数做适当的修改，还可以排出每位同学各个学科成绩的名次。2的表格制作好了以后，将“学号、姓名、各科成绩”等内容清空，再执行“文件→另存为”命令，打开“另存为”对话框，将“文件类型”选定为“模板”，并给定一个文件名，将该工作簿(成绩统计表)保存为模板，方便以后的使用。如果要增加学生或增加学科，只要插入空行(列)就行了，完全不必更改公式(系统会智能化地对公式进行调整)。对于没有成绩的学生，其对应的单元格一定要为空，千万不要填入数字“0”，以免造成统计出错。SUM──数学函数：返回单元格区域中所有数值的和。AVERAGE──统计函数：计算参数的算术平均数。MAX(MIN)──统计函数：返回一组数值中的最大(小)值，忽略逻辑值和文本字符。COUNTIF──统计函数：计算某个区域中满足给定条件单元格的数目。?实例四：多条件统计──数组公式的使用??图3是一张普通的工资表，现在我们要统计职称为“高工”、性别为“男”的职工基础工资之和(数据位于A1F102区域中)。用一般的函数公式难以达到这一目的，我们可用数组公式来实现。E103单元格(用于保存统计结果的单元格)，先输入公式：=SUM(IF(C3:C102="男"，IF(D3:D102="高工"，E3:E102)))，然后在按住“Ctrl+Shift”组合键(非常关键！！！)的同时按下Enter键即可。(按住Ctrl键，可以用鼠标选中不连续的单元格)，执行“格式→单元格”命令，打开“单元格格式”设置对话框，点击“保护”标签，将“锁定”选项前面的“”去掉，确定返回。再执行“工具→保护→保护工作表”命令，打开“保护工作表”对话框，两次输入密码后，确定返回即可。{}”，公式变成了：{=SUM(IF(C2:C20="男"，IF(D2:D20="高工"，E2:E20)))}，这个数组公式标志“{}”是不能直接用键盘输入的！
|
|
