函数的用法和技巧

函数的用法和技巧

此函数是excel预义的嵌入式公式。您可以使用设置函数参数方法来解决实际工作中的数据计算和统计问题。Excel根据不同的应用提供了不同类型的函数,如财务函数、数学函数、统计函数、数据库函数、逻辑函数、文本函数和日期时间函数等。下面通过实例介绍几个常用函数的使用技巧。一、利用raken函数自动填充过程序在Excel中,填充名次最简单的方法是先按某一数值关键字(如均分)递减排序,然后利用填充柄在名次单元格中依次填充1、2、3……这种方法虽然简单,但如果有名次并列的情况则无法自动填充,还需要手动地修改相关的并列名次。而Excel中的RANK函数却可以真正地做到名次的自动填充。RANK函数的格式是RANK(数值,单元格区域),其功能是返回数值在单元格区域中的排位。在使用RANK函数时,要求单元格区域必须是绝对引用。在表1(学生成绩与等级评定表)中填充名次的方法是先将光标定位到D3结果单元格中,输入或设置函数:=RANK(D3,$D$3:$D$7),然后利用填充柄填充D列中其余同学的名次单元格,即可实现所有学生名次的自动填充。利用RANK函数无需对表格排序,即可自动填充名次。二、真的结果,条件为假的结果Excel中IF函数的格式是IF(条件,条件为真的结果,条件为假的结果),其作用是根据条件的真假,填充不同的结果。如表1(学生成绩与等级评定表)所示,可以根据均分的高低填充不同的等级。1.不同填充国先将光标定位到E3结果单元格中,输入或设置函数:=IF(C3>=60,“及格”,“不及格”),然后利用填充柄填充E列中其余同学的等级单元格,即可实现两种等级的填充。2.利用填充柄填充e列计算先将光标定位到E3结果单元格中,输入或设置函数:=IF(C3>=60,IF(C3>=80,“好”,“中”“差”),然后利用填充柄填充E列中其余同学的等级单元格,即可实现三种等级的填充。3.等级填充先将光标定位到E3结果单元格中,输入或设置函数:=IF(C3>=75,IF(C3>=85,“优秀”,“良好”),IF(C3>=60,“及格”,“不及格”),然后利用填充柄填充E列中其余同学的等级单元格,即可实现四种等级的填充。从上面我们不难发现,在Excel中一个IF可以实现两个分支,两个IF嵌套在一起可以实现三个分支,三个IF嵌套在一起可以实现四个分支,依次类推……Excel的IF函数最多可以嵌套七层。三、逻辑函数多进路如表2(全国计算机等级考试成绩表)所示,若笔试和机试均及格,合格否字段则填充“合格”,否则填充“不合格”。我们首先想到了用IF函数实现这种双分支的填充问题。但是,这个填充问题还有一个难点就是条件由两部分组成,一个是笔试及格,另一个是机试及格,而且这两个条件必须同时成立。因此,解决这个填充问题还需要逻辑函数的支持。在Excel中,逻辑函数有逻辑与AND()、逻辑或OR()、逻辑非NOT()三种。在表2(全国计算机等级考试成绩表)中填充合格否的方法是:先将光标定位到E3结果单元格中,输入或设置函数:=IF(AND(C3>=60,D3>=60),“合格”,“不合格”),然后利用填充柄填充E列中其余同学的合格否单元格,即可实现所有考生“合格否”列的自动填充。也可以使用函数逻辑或OR()实现,方法是先将光标定位到E3结果单元格中,输入或设置函数:=IF(OR(C3<60,D3<60),“不合格”,“合格”)然后利用填充柄填充E列中其余同学的合格否单元格,也可实现所有考生“合格否”列的自动填充。四、身份证号的编码身份证号是每个公民都拥有的能够唯一识别身份的编码。根据《中华人民共和国国家标准GB11643-1999》中有关公民身份号码的规定,公民身份证号是一组特征组合码,由十七位数字本体码和一位数字校验码组成。排列顺序从左至右依次为:六位数字的地址码、八位数字的出生日期码、三位数字的顺序码和一位数字的校验码。如表3(员工信息一览表)所示,由于身份证号中含有出生日期、性别和年龄等信息,因此每个员工的身份证号必须逐个输入,而每个员工的出生日期、性别和年龄等信息可以从身份证号中提取。1.指定字符子串从身份证号中提取出生日期,需要用到MID函数,其格式为MID(文本,开始位置,字符个数),其功能是返回文本字符串中从指定位置开始的指定字符数目的子串。从身份证号中提取出生日期方法是:从身份证号的第7位开始取8个字符的子串即可。提取出生日期的操作步骤是:先将光标定位到D3结果单元格中,输入或设置函数:=MID(C3,7,8),然后利用填充柄填充D列中其余员工的出生日期单元格,即可实现所有员工出生日期的自动填充。2.性别特征的提取从身份证号中提取性别,需要用到IF、MID和MOD函数,MOD函数的格式为MOD(x,y),其功能是返回x除以y的余数。由于身份证号的第17位和性别有关(若第17位为奇数,性别为男,否则为女),因此应先利用MID函数提取出身份证号的第17位,再利用MOD函数判断是否为奇数,最后用IF函数根据第17位数的奇偶填充不同的性别。提取性别的操作步骤是:先将光标定位到E3结果单元格中,输入或设置函数:=IF(MOD(MID(C3,17,1),2)=1,“男”,“女”)然后利用填充柄填充E列中其余员工的性别单元格,即可实现所有员工性别的自动填充。3.出生日期的提取由于身份证号中含有出生日期的年份信息,因此将当前日期的年份减去出生日期的年份,即可得到该员工的年龄。要得到当前日期的年份:需要用到两个函数,一个是NOW(),该函数返回当前的日期和时间;另一个是YEAR(日期),该函数返回日期时间参数所对应的年份。因此YEAR(NOW())返回的即是当前日期时间的年份。从身