Excel中函数的使用技巧与实例

赵俊峰

（太原大学外语师范学院，山西 太原 030012）

Excel中函数的使用技巧与实例

赵俊峰

（太原大学外语师范学院，山西 太原 030012）

文章通过学生成绩与等级评定表、全国计算机等级考试成绩表、员工信息一览表三个实例表格阐述了RANK、IF、MID、MOD、AND、YEAR和NOW等函数的用法和技巧。在利用Excel的函数解决较难的数据计算问题时，往往需要多个函数的联合使用才能实现。

Excel；函数；技巧；实例

函数是Excel预定义的内置公式，可以通过设置函数参数的方法利用函数解决实际工作中有关数据计算和统计等问题。Excel根据不同的应用提供了不同类型的函数，如财务函数、数学函数、统计函数、数据库函数、逻辑函数、文本函数和日期时间函数等。下面通过实例介绍几个常用函数的使用技巧。

一、利用RANK函数自动填充名次

在Excel中，填充名次最简单的方法是先按某一数值关键字（如均分）递减排序，然后利用填充柄在名次单元格中依次填充1、2、3……这种方法虽然简单，但如果有名次并列的情况则无法自动填充，还需要手动地修改相关的并列名次。而Excel中的RANK函数却可以真正地做到名次的自动填充。RANK函数的格式是RANK（数值，单元格区域），其功能是返回数值在单元格区域中的排位。在使用RANK函数时，要求单元格区域必须是绝对引用。在表1（学生成绩与等级评定表）中填充名次的方法是先将光标定位到D3结果单元格中，输入或设置函数：=RANK（D3,$D$3:$D$7），然后利用填充柄填充D列中其余同学的名次单元格，即可实现所有学生名次的自动填充。利用RANK函数无需对表格排序，即可自动填充名次。

表1 学生成绩与等级评定表

二、利用IF函数实现多分支填充

Excel中IF函数的格式是IF（条件，条件为真的结果，条件为假的结果），其作用是根据条件的真假，填充不同的结果。如表1（学生成绩与等级评定表）所示，可以根据均分的高低填充不同的等级。

1.双分支填充

先将光标定位到E3结果单元格中，输入或设置函数：=IF（C3＞=60,“及格”,“不及格”），然后利用填充柄填充E列中其余同学的等级单元格，即可实现两种等级的填充。

2.三分支填充

先将光标定位到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列中其余同学的合格否单元格，即可实现所有考生“合格否”列的自动填充。

表2 全国计算机等级考试成绩表

也可以使用函数逻辑或OR（）实现，方法是先将光标定位到E3结果单元格中，输入或设置函数：=IF(OR(C3＜60,D3＜60),“不合格”,“合格”)然后利用填充柄填充E列中其余同学的合格否单元格，也可实现所有考生“合格否”列的自动填充。

四、利用函数提取身份证号中的相关信息

身份证号是每个公民都拥有的能够唯一识别身份的编码。根据《中华人民共和国国家标准GB 11643-1999》中有关公民身份号码的规定，公民身份证号是一组特征组合码，由十七位数字本体码和一位数字校验码组成。排列顺序从左至右依次为：六位数字的地址码、八位数字的出生日期码、三位数字的顺序码和一位数字的校验码。如表3（员工信息一览表）所示，由于身份证号中含有出生日期、性别和年龄等信息，因此每个员工的身份证号必须逐个输入，而每个员工的出生日期、性别和年龄等信息可以从身份证号中提取。

表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（））返回的即是当前日期时间的年份。从身份证号中利用MID即可提取出的出生日期的年份。计算年龄的操作步骤是：先将光标定位到F3结果单元格中，输入或设置函数：=YEAR(NOW())-MID(C3,7,4)，然后利用填充柄填充F列中其余员工的年龄单元格，即可实现所有员工年龄的自动填充。

［1］薛学勤.全国计算机等级考试一级MS OFFICE教程［M］.天津：南开出版社，2004.

［2］赵俊峰.山西省中职学校通用教材《计算机》［M］.太原：山西人民出版社，2008.

G642

A

1673-0046（2010）7-0126-02

该论文为课题“基于《计算机公共基础》的网络课程设计与研究”的课题成果］